Статистика в Excel: разброс выборки, пакет анализа и частоты

Среднее значение без разброса вводит в заблуждение: две партии с одинаковой средней прочностью могут отличаться так, что одна годится в работу, а другая — нет. Справка Microsoft считает разброс на примере десяти образцов, и для них СТАНДОТКЛОН.В возвращает 27,46391572^[1]. Разницу между «стабильно» и «как повезёт» показывает именно стандартное отклонение.

Порядок работы со статистикой в Excel такой: найти нужную функцию или пакет анализа, ввести формулу с диапазоном, проверить результат на примере справки, исправить выбор функции, если данные — вся совокупность, а затем применить расчёт к своим данным. Дальше каждый шаг разобран отдельно, а базовая статистика в Excel сводится к четырём функциям и одной надстройке. Остальные разделы собраны в перечне тем и уроков тренажёра Excel.

Ключевые факты: статистика в Excel

• СТАНДОТКЛОН.В оценивает стандартное отклонение по выборке и вычисляет его методом n−1^[1].
• На данных справки СТАНДОТКЛОН.В возвращает 27,46391572, а ДИСП.В — 754,27^[1][2].
• Функция принимает число1 и необязательные аргументы со 2-го по 254-й^[1].
• В блоке «Применяется к» у СТАНДОТКЛОН.В пять редакций — от Excel 2016 до Microsoft 365, и у трёх новейших есть версии для Mac^[1].
• СТАНДОТКЛОН.Г в отличие от СТАНДОТКЛОН.В считает данные всей совокупностью и на тех же образцах даёт 26,05455814^[3].

Как считать статистику в Excel: функции разброса и их аргументы

Статистика в Excel начинается с функций разброса: СТАНДОТКЛОН.В и ДИСП.В считают отклонение и дисперсию выборки, а СТАНДОТКЛОН.Г и ДИСП.Г — всей генеральной совокупности. Синтаксис у них общий: =СТАНДОТКЛОН.В(число1;[число2];…), где первый аргумент обязателен, а следующие идут со 2-го по 254-й^[1]. Вместо перечня чисел почти всегда указывают диапазон.

Стандартное отклонение — квадратный корень из дисперсии, поэтому из одной функции легко получить другую: =КОРЕНЬ(ДИСП.В(A2:A11)) даёт то же число, что СТАНДОТКЛОН.В. Дисперсия выражена в квадратах единиц, а отклонение — в тех же единицах, что и данные, поэтому в отчётах чаще показывают отклонение.

Функция разброса по-разному обращается с текстом. Если аргумент — ссылка или массив, учитываются только числа: пустые ячейки, логические значения, текст и ошибки пропускаются^[1]. Числа-текст и логические значения, введённые прямо в список аргументов, наоборот, учитываются, а ошибки и непреобразуемый текст в аргументах дают ошибку^[1]. Если логические значения и текст в ссылке тоже нужно учесть, справка предлагает функцию СТАНДОТКЛОНА^[1]. На практике это редкость: обычно в столбце с замерами стоят только числа, и проблема возникает, когда часть чисел записана как текст.

Разобравшись с аргументами, переходим к примеру — на нём видно, сколько дают обе версии функции.

Пошаговый пример: прочность образцов по данным справки

СТАНДОТКЛОН.В в этом примере считает разброс прочности десяти образцов, которые справка Microsoft приводит в своём примере^[1]. Данные стоят в A2:A11.

1. Введите данные в A2:A11 и подписи показателей в столбец C.
2. В D2 введите =СТАНДОТКЛОН.В(A2:A11) и нажмите Enter.
3. Повторите для СТАНДОТКЛОН.Г, ДИСП.В и ДИСП.Г, меняя только имя функции.
4. Проверьте результат по справке: отклонение по выборке — 27,46391572, по совокупности — 26,05455814, дисперсии — 754,27 и 678,84^[2][3].
5. Добавьте минимум, максимум и размах: 1299, 1370 и 71^[8].

Если в D2 получилось другое число, проверьте диапазон: лишняя строка заголовка с текстом не испортит расчёт, а пропущенная строка с числом — испортит. Квадрат 27,46391572 совпадает с 754,27 — так ещё раз проверяется, что функции взяты из одной пары. Чтобы сравнить разброс партий с разной средней прочностью, отклонение делят на среднее: на этих данных выходит 0,0207, то есть около 2,1%^[8]. Такая относительная статистика в Excel удобна, когда партии измерены в разных единицах. Повторите расчёт в уроке тренажёра: там другие данные, и урок проверяет каждую формулу.

Однако выбор между .В и .Г зависит не от формулы, а от того, что представляют данные.

Выборка или генеральная совокупность: чем отличаются .В и .Г

СТАНДОТКЛОН.В отличается от СТАНДОТКЛОН.Г тем, что считает данные выборкой и делит на n−1, а .Г — всей совокупностью^[1]. На данных справки разница заметна: 27,46391572 против 26,05455814. Чем меньше выборка, тем сильнее расходятся две версии, потому что деление на n−1 вместо n заметнее при малом n. Для больших выборок, как указано в справке, обе функции дают примерно равные значения^[3].

В Google Таблицах функция СТАНДОТКЛОН вычисляет отклонение для выборки, для всего массива там служит СТАНДОТКЛОНП, а при меньше чем двух значениях функция возвращает #ДЕЛ/0!^[7]. В движке тренажёра СТАНДОТКЛОН.В от одного числа тоже даёт #ДЕЛ/0!^[8].

Именно поэтому для отчёта по многим показателям сразу удобнее отдельный инструмент — пакет анализа.

Пакет анализа на практике: описательная статистика и гистограмма

Пакет анализа строит описательную статистику и гистограмму одной командой, без набора формул вручную. Для отчёта по многим показателям это самая короткая дорога: статистика в Excel по столбцу собирается за один запуск. Надстройку Analysis ToolPak сначала загружают: File → Options → Add-Ins, в поле Manage выбрать Excel Add-ins, нажать Go и отметить флажок Analysis ToolPak^[4]. После этого на вкладке Data появляется команда Data Analysis; если её нет, надстройка не загружена^[5].

Частоты по интервалам можно получить и формулой. Для данных прочности с границами 1300, 1325, 1350 и 1375 формула =ЧАСТОТА(A2:A11;E2:E5) вернёт 1, 5, 2, 2 и 0 — последнее число относится к значениям выше последней границы^[8]. Гистограмму-диаграмму без надстройки строят командой Insert → Insert Statistic Chart → Histogram; ширину интервала задают полями Bin width и Number of bins, а по умолчанию её подбирает правило Скотта^[6].

Пакет анализа работает только с одним листом за раз: если листы сгруппированы, результат появится на первом^[4]. Справка не обещает, что выходная таблица обновится вслед за данными, поэтому после правки исходных чисел отчёт надёжнее запустить снова.

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

Когда статистика в Excel не подойдёт

Статистика в Excel не подойдёт, если данные или задача не совпадают с тем, что считают функции разброса. Проверьте условия ниже до того, как переносить число в отчёт.

• Если данные — вся генеральная совокупность, версия .В завысит разброс: нужна версия .Г^[1].
• Если чисел больше 254 перечнем, функция их не примет — укажите диапазон, а не отдельные значения^[1].
• Когда в наборе меньше двух чисел, отклонение по выборке не считается: Google Таблицы возвращают #ДЕЛ/0!^[7].
• При нескольких сгруппированных листах пакет анализа обработает только первый лист^[4].
• Если числа хранятся как текст в ячейках, функция их пропустит, и результат окажется посчитан не по всем данным^[1].

Функции разброса работают и на Mac: в блоке «Применяется к» перечислены версии для Mac у Microsoft 365, Excel 2024 и Excel 2021, а также Excel 2019 и Excel 2016^[1]. Загрузка пакета анализа описана в справке для Microsoft 365, Excel 2024 и Excel 2021, в том числе для Mac^[4]. Связь двух рядов между собой — отдельная задача, и для неё нужны другие функции: она разобрана на странице о связи двух рядов данных. Если отчёт строится для коллег со старой версией Excel, проверьте, что их редакция есть в этом списке.