Как сделать отклонение в excel

Обновлено: 07.07.2024

В своей работе я часто строю контрольные карты Шухарта. Напомню, что контрольные карты Шухарта – один из инструментов менеджмента качества. Используется для контроля над ходом процесса. Пока значения остаются в пределах контрольных границ, вмешательство в процесс не требуется. Процесс статистически управляем. Если значения выходят за контрольные границы, необходимо вмешательство менеджмента для выявления причин отклонений.

Для построения контрольной карты я использую исходные данные, среднее значение (μ) и стандартное отклонение (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)

Сама контрольная карта включает: исходные данные, среднее значение (μ), нижнюю контрольную границу (μ – 2σ) и верхнюю контрольную границу (μ + 2σ):

Посмотрев на представленную карту, я заметил, что исходные данные демонстрируют вполне различимую линейную тенденцию к снижению доли накладных расходов:

Если исходные данные не разбросаны в соответствии с нормальным распределением вокруг среднего значения, то описывать их параметрами μ и σ не вполне корректно. Для описания вместо среднего значения лучше подойдет прямая линейного тренда и контрольные границы, равноудаленные от этой линии тренда.

Линию тренда Excel позволяет построить с помощью функции ПРЕДСКАЗ. Нам потребуется дополнительный ряд А3:А15, чтобы известные значения Х были непрерывным рядом (номера кварталов такой непрерывный ряд не образуют). Вместо среднего значения в столбце Н вводим функцию ПРЕДСКАЗ:

Стандартное отклонение σ (функция СТАНДОТКЛОН в Excel) вычисляется по формуле:

где – среднее значение, а n – размер выборки.

Если мы определяем отклонение не от среднего, а от линии тренда, то в этой формуле вместо следует использовать значения точек тренда. Например:

К сожалению, я не нашел в Excel функции для такого определения стандартного отклонения (по отношению к тренду). Задачу можно решить с помощью формулы массива. Кто не знаком с формулами массива, предлагаю сначала почитать здесь.

Формула массива может возвращать одно значение или массив. В нашем случае формула массива вернет одно значение:

Давайте подробнее изучим, как работает формула массива в ячейке G3

СУММ(($F$3:$F$15-$H$3:$H$15)^2) определяет сумму квадратов разностей; фактически формула считает следующую сумму = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2

СЧЁТЗ($F$3:$F$15) – число значений в диапазоне F3:F15

Значение 6,2% есть точка нижней контрольной границы = 8,3% – 2 σ

Фигурные кавычки с обеих сторон формулы означают, что это формула массива. Для того, чтобы создать формулу массива, после ввода формулы в ячейку G3:

необходимо нажать не Enter, а Ctrl + Shift + Enter. Не пытайтесь ввести фигурные скобки с клавиатуры – формула массива не заработает. Если требуется отредактировать формулу массива, сделайте это так же, как и с обычной формулой, но опять же по окончании редактирования нажмите не Enter, а Ctrl + Shift + Enter.

В результате получили контрольную карту, построенную для данных, имеющих тенденцию к понижению

P.S. После того, как заметка была написана, я смог усовершенствовать формулы, используемые для вычисления стандартного отклонения для данных с тенденцией. Ознакомиться с ними вы можете в Excel-файле Усовершенствованный вариант Стандартное отклонение для данных с тенденцией

3 комментария для “Excel. Вычисление стандартного отклонения для данных с тенденцией”

помоему в строке СУММ(($F$3:$F$15-$H$3:$H$15)^2) определяет сумму квадратов разностей; фактически формула считает следующую сумму = (F3 – H3)2 + (F3 – H3)2 + … + (F15 – H15)2 ошибка
почему задвоено (F3 – H3)2 .

В программе эксель можно посчитать среднеквадратичное отклонение двумя способами: использовать стандартные формулы или воспользоваться специальной функцией. Рассмотрим оба метода расчета и сравним их результаты.

Перед нами таблица, состоящая из двух строк и шести столбцов, на основании этих данных и будем делать расчет.

Первый способ.

В итоге получили цифру равную 1,483

Второй способ.

В итоге результаты обоих вариантов расчета среднеквадратичного отклонения совпали, а вы выбирайте метод, который наиболее подходит к вам.

Стандартное отклонение – это статистический инструмент, который приблизительно показывает, насколько в среднем каждое число в списке значений данных отличается от среднего значения или среднего арифметического самого списка.

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010, 2007; Excel для Mac, Excel для Office 365, Excel Online, Excel для iPad, Excel для iPhone и Excel для Android.

Практическое использование функции STDEV

В Excel функция STDEV обеспечивает оценку набора стандартных отклонений данных. Функция предполагает, что введенные числа представляют только небольшую часть или выборку из всей изучаемой популяции. В результате функция STDEV не возвращает точное стандартное отклонение. Например, для чисел 1 и 2 функция STDEV в Excel возвращает приблизительное значение 0,71, а не точное стандартное отклонение 0,5.

Несмотря на то, что функция STDEV оценивает только стандартное отклонение, функция полезна, когда тестируется только небольшая часть совокупности. Например, при тестировании готовой продукции на соответствие среднему значению (для таких мер, как размер или долговечность) тестируется не каждая единица, и это дает оценку того, насколько каждая единица во всей совокупности отличается от средней.

Чтобы показать, насколько близки результаты для STDEV к фактическому стандартному отклонению (с использованием приведенного выше примера), размер выборки, использованный для функции, был менее одной трети от общего объема данных. Разница между расчетным и фактическим стандартным отклонением составляет 0,02.

STDEV в синтаксис и аргументы Excel

Синтаксис функции относится к макету функции и включает имя функции, скобки, разделители запятых и аргументы. Синтаксис для функции стандартного отклонения:

Number1 (обязательно). Это число может быть фактическим числом, именованным диапазоном или ссылкой на ячейку для расположения данных на листе. Если используются ссылки на ячейки, пустые ячейки, логические значения, текстовые данные или значения ошибок в диапазоне ссылок на ячейки игнорируются.

Number2, … Number255 (необязательно): можно ввести до 255 номеров.

Пример функции STDEV

В этом руководстве образец данных, используемый для аргумента Number функции, находится в ячейках с A5 по D7. Стандартное отклонение для этих данных будет рассчитано. Для сравнения включены стандартное отклонение и среднее значение для всего диапазона данных от A1 до D10.

В Excel 2010 и Excel 2007 формула должна быть введена вручную.

Выполните следующие шаги, чтобы выполнить задачу и рассчитать информацию с помощью встроенной функции:

Выберите ячейку D12 , чтобы сделать ее активной. Здесь будут отображаться результаты функции STDEV.

Введите функцию = STDEV (A5: D7) и нажмите Enter .

Значение в D12 изменяется до 2,37. Это новое значение представляет собой расчетное стандартное отклонение каждого числа в списке от среднего значения 4,5

Для более старых версий Excel введите формулу вручную или выберите ячейку D12 и откройте селектор визуальных данных с помощью Формулы > Дополнительные функции > STDEV .

Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.

Вычисление коэффициента вариации

Этот показатель представляет собой отношение стандартного отклонения к среднему арифметическому. Полученный результат выражается в процентах.

В Экселе не существует отдельно функции для вычисления этого показателя, но имеются формулы для расчета стандартного отклонения и среднего арифметического ряда чисел, а именно они используются для нахождения коэффициента вариации.

Шаг 1: расчет стандартного отклонения

Стандартное отклонение, или, как его называют по-другому, среднеквадратичное отклонение, представляет собой квадратный корень из дисперсии. Для расчета стандартного отклонения используется функция СТАНДОТКЛОН. Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…) = СТАНДОТКЛОН.В(Число1;Число2;…)

Урок: Формула среднего квадратичного отклонения в Excel

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция — СРЗНАЧ. Вычислим её значение на конкретном примере.

Урок: Как посчитать среднее значение в Excel

Шаг 3: нахождение коэффициента вариации

Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.

Таким образом мы произвели вычисление коэффициента вариации, ссылаясь на ячейки, в которых уже были рассчитаны стандартное отклонение и среднее арифметическое. Но можно поступить и несколько по-иному, не рассчитывая отдельно данные значения.

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.

Читайте также: