=СТАНДОТКЛОН.В(B2:B9) возвращает стандартное отклонение значений в B2:B9, если считать их выборкой, а =СТАНДОТКЛОН.Г(B2:B9) считает их всей генеральной совокупностью. По-английски эти функции называются STDEV.S и STDEV.P. Стандартное отклонение показывает, насколько далеко значения обычно отстоят от своего среднего: маленькое значит, что значения лежат близко друг к другу. В таблицах ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Result | |
| 2 | Ana | 72 | STDEV.S | 12.82853961 | |
| 3 | Ben | 84 | STDEV.P | 12 | |
| 4 | Cleo | 84 | Average | 90 | |
| 5 | Dan | 84 | |||
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
=СТАНДОТКЛОН.В(B2:B9)Средний балл 90. СТАНДОТКЛОН.Г даёт ровно 12, а СТАНДОТКЛОН.В около 12,83. Замените балл Hal на 90, и оба резко упадут: одно значение, далёкое от остальных, сильно сдвигает стандартное отклонение.
СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г: что выбрать
Они различаются одним шагом. СТАНДОТКЛОН.Г делит сумму квадратов отклонений на число значений, n. СТАНДОТКЛОН.В делит на n минус 1, и результат получается немного больше. Причина: разброс выборки измеряется вокруг её собственного среднего, а оно лежит ближе к её значениям, чем настоящее среднее, поэтому деление на n занизило бы разброс всей группы.
- СТАНДОТКЛОН.Г (генеральная совокупность): в диапазоне есть все значения, которые вы хотите описать. Баллы всех 8 учеников этого класса, когда вопрос именно об этом классе.
- СТАНДОТКЛОН.В (выборка): диапазон это часть чего-то большего. 8 учеников, выбранных из школы на 600 человек, чтобы оценить разброс по всей школе.
Если сомневаетесь, используйте СТАНДОТКЛОН.В. Большинство данных в таблицах это выборки, и статистические инструменты (t-тесты, доверительные интервалы) ожидают вариант для выборки. При сотнях значений результаты почти одинаковы; при 8 значениях разница около 7%.
Старые функции СТАНДОТКЛОН и STDEVP дают те же результаты, что СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г, и по-прежнему работают в любой версии Excel. STDEVA и STDEVPA к тому же считают текст за 0, а ИСТИНА за 1, а это редко то, что нужно.
Как Excel считает его, по шагам
Эта таблица вручную делает то, что СТАНДОТКЛОН.В делает за один вызов: вычитает среднее из каждого значения, возводит разности в квадрат, складывает их, делит на n минус 1 и извлекает квадратный корень.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Value | Difference | Squared | Result | ||
| 2 | 4 | -2 | 4 | Sum of squares | 34 | |
| 3 | 8 | 2 | 4 | n | 6 | |
| 4 | 6 | 0 | 0 | Variance (sample) | 6.8 | |
| 5 | 5 | -1 | 1 | Std dev (sample) | 2.607680962 | |
| 6 | 3 | -3 | 9 | STDEV.S | 2.607680962 | |
| 7 | 10 | 4 | 16 |
=КОРЕНЬ(F4)Сумма квадратов 34, дисперсия выборки 6,8, а квадратный корень из неё (около 2,61) совпадает со СТАНДОТКЛОН.В в F6. Замените F4 на =F2/F3, и получится дисперсия генеральной совокупности; квадратный корень из неё и возвращает СТАНДОТКЛОН.Г.
Дисперсия: ДИСП.В и ДИСП.Г
Дисперсия это стандартное отклонение до извлечения корня: =ДИСП.В(A2:A7) даёт 6,8 для данных выше, а =ДИСП.Г(A2:A7) делит на n, а не на n минус 1. Дисперсия измеряется в квадратах единиц (баллы в квадрате, доллары в квадрате), поэтому в отчётах стандартное отклонение читать проще. ДИСП и VARP это старые имена.
Среднее плюс минус одно стандартное отклонение
Распространённый способ показать разброс это «среднее ± СО», например 90 ± 12,8. Оба конца этого интервала считаются простыми формулами, а правило условного форматирования может отметить значения за его пределами.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Value | |
| 2 | Ana | 72 | Mean | 90.0 | |
| 3 | Ben | 84 | SD | 12.8 | |
| 4 | Cleo | 84 | Low | 77.2 | |
| 5 | Dan | 84 | High | 102.8 | |
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
=СТАНДОТКЛОН.В(B2:B9)Правило подсвечивает Ana и Hal, два балла вне интервала примерно от 77,2 до 102,8. В нормально распределённых данных около двух третей значений лежат в пределах одного стандартного отклонения от среднего и около 95% в пределах двух. Чтобы получить в ячейке текст вида «90.0 ± 12.8», используйте =ТЕКСТ(E2;"0,0")&" ± "&ТЕКСТ(E3;"0,0").
Стандартное отклонение с условием
Функции СТАНДОТКЛОНЕСЛИ нет. Поставьте ЕСЛИ внутрь СТАНДОТКЛОН.В: ЕСЛИ возвращает продажи там, где совпадает регион, и ЛОЖЬ в остальных строках, а СТАНДОТКЛОН.В пропускает значения ЛОЖЬ.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Region | STDEV.S | |
| 2 | North | 120 | North | 25.61737691 | |
| 3 | South | 95 | South | 3.872983346 | |
| 4 | North | 150 | |||
| 5 | South | 101 | |||
| 6 | North | 90 | |||
| 7 | South | 98 | |||
| 8 | North | 135 | |||
| 9 | South | 104 |
=СТАНДОТКЛОН.В(ЕСЛИ(A2:A9=D2;B2:B9))Продажи North колеблются гораздо сильнее, чем продажи South. В Excel 365 и 2021 эта формула работает в том виде, как введена: =СТАНДОТКЛОН.В(ЕСЛИ(A2:A9=D2;B2:B9)). В Excel 2019 и более ранних завершайте её сочетанием Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac), иначе она вернёт неверный результат или #ЗНАЧ! (по-английски #VALUE!). В Excel 365 можно также написать =СТАНДОТКЛОН.В(ФИЛЬТР(B2:B9;A2:A9=D2)).
Попробуйте: разброс сроков доставки
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Days | Std dev | ||
| 2 | A1 | 3 | |||
| 3 | A2 | 5 | |||
| 4 | A3 | 4 | |||
| 5 | A4 | 9 | |||
| 6 | A5 | 3 | |||
| 7 | A6 | 4 | |||
| 8 | A7 | 6 |
Ваша очередь: Заказы в B2:B8 это выборка из всех заказов. В E2 посчитайте их стандартное отклонение.
Подсказка: выборка значит функция, которая заканчивается на .В (в английском Excel .S).
Частая ошибка: строка итога в диапазоне
Диапазон вроде B2:B10, который захватывает итог или среднее внизу столбца, считает эту сводку ещё одним значением, и стандартное отклонение получается намного больше. Выделяйте только строки с данными или держите сводки в другом столбце, как в таблицах на этой странице. Пустые ячейки и текст в диапазоне пропускаются, но 0 это значение, и оно учитывается: пропущенный балл, введённый как 0, расширяет разброс так же, как настоящий 0. Чтобы проверить, сколько значений учтено, поставьте рядом с результатом =СЧЁТ(B2:B9).
Часто задаваемые вопросы
Какая формула считает стандартное отклонение в Excel?
=СТАНДОТКЛОН.В(B2:B9) для выборки и =СТАНДОТКЛОН.Г(B2:B9) для всей генеральной совокупности. Обе пропускают текст и пустые ячейки в диапазоне.
Что использовать: СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г?
Используйте СТАНДОТКЛОН.Г, только когда в диапазоне есть все члены группы, которую вы описываете, например все 8 человек в команде. Когда данные это выборка, по которой судят о чём-то большем (часть клиентов, часть тестовых запусков), используйте СТАНДОТКЛОН.В. При большом числе значений результаты близки; при малом СТАНДОТКЛОН.В заметно больше.
Чем СТАНДОТКЛОН отличается от СТАНДОТКЛОН.В?
Результатом ничем. СТАНДОТКЛОН и STDEVP это имена из версий до 2010 года, оставленные для совместимости; СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г современные. Google Таблицы принимают оба набора имён.
Как посчитать дисперсию в Excel?
Используйте =ДИСП.В(B2:B9) для выборки и =ДИСП.Г(B2:B9) для генеральной совокупности. Дисперсия это квадрат стандартного отклонения, поэтому =СТАНДОТКЛОН.В(B2:B9)^2 даёт то же число, что и ДИСП.В.
Как посчитать стандартную ошибку в Excel?
Функции для стандартной ошибки среднего в Excel нет. Разделите стандартное отклонение выборки на квадратный корень из количества: =СТАНДОТКЛОН.В(B2:B9)/КОРЕНЬ(СЧЁТ(B2:B9)).
