Статистические функции: AVERAGE, MEDIAN, STDEV

При работе с большими наборами данных простой просмотр строк с числами редко дает полезную информацию. Будь то анализ показателей продаж, оценка успеваемости студентов или проверка квартальных расходов, вам нужны надежные способы обобщения и интерпретации данных. Именно здесь на помощь приходят встроенные статистические функции Excel.

В этом подробном руководстве мы глубоко погрузимся в основные статистические функции Excel: AVERAGE, MEDIAN, MODE и STDEV. Освоив эти инструменты, вы перейдете от простого хранения данных к выполнению комплексного, практически применимого анализа данных.

Понимание мер центральной тенденции

Меры центральной тенденции — это статистические метрики, используемые для нахождения центра или «типичного» значения набора данных. Хотя в повседневной речи часто используется слово «среднее», статистический анализ делит центральную тенденцию на три различные концепции: среднее арифметическое (AVERAGE), середина (MEDIAN) и наиболее часто встречающееся (MODE).

Функция AVERAGE (Среднее значение)

Функция AVERAGE вычисляет среднее арифметическое группы чисел. Excel суммирует все числа в указанном диапазоне и делит результат на количество этих чисел.

Синтаксис: =AVERAGE(number1, [number2], ...)

• number1: Первое число, ссылка на ячейку или диапазон, который вы хотите усреднить (обязательно).
• number2: Дополнительные числа, ссылки на ячейки или диапазоны (необязательно).

Например, если ячейки с A1 по A5 содержат значения 10, 20, 30, 40 и 50, формула =AVERAGE(A1:A5) вернет 30. Функция AVERAGE автоматически игнорирует пустые ячейки и текстовые строки, гарантируя, что ваши вычисления не будут искажены нечисловыми данными.

Функция MEDIAN (Медиана)

Функция MEDIAN находит точное среднее число в отсортированном списке чисел. Половина чисел будет больше медианы, а половина — меньше.

Синтаксис: =MEDIAN(number1, [number2], ...)

Зачем использовать MEDIAN вместо AVERAGE? Функция AVERAGE очень чувствительна к выбросам — экстремальным значениям, которые аномально высоки или низки. Например, если вы рассчитываете средний доход в небольшом городке, куда переезжает миллиардер, показатель AVERAGE взлетит до небес, даже если уровень жизни остальных людей не изменился. Показатель MEDIAN, однако, остается стабильным, обеспечивая более точное отражение дохода «типичного» жителя.

Функция MODE (Мода)

Мода представляет собой наиболее часто встречающееся значение в вашем наборе данных. Современные версии Excel предлагают для этого две отдельные функции:

• MODE.SNGL: Возвращает одно наиболее часто встречающееся значение. Если есть несколько таких значений, возвращается первое найденное.
• MODE.MULT: Возвращает массив наиболее часто встречающихся значений (полезно, если у вас есть несколько чисел с одинаковой максимальной частотой).

Синтаксис: =MODE.SNGL(number1, [number2], ...)

Понимание мер разброса

В то время как центральная тенденция показывает, где находится центр ваших данных, меры разброса (дисперсии) показывают, насколько распределены ваши данные вокруг этого центра. Два набора данных могут иметь абсолютно одинаковое среднее значение, но выглядеть совершенно по-разному.

Функция STDEV (Стандартное отклонение)

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

Excel требует определить, представляют ли ваши данные всю генеральную совокупность или только выборку из нее:

• STDEV.S (Выборка): Используется, когда ваш набор данных является выборкой из более крупной генеральной совокупности. Это наиболее распространенный сценарий. Формула: =STDEV.S(range)
• STDEV.P (Генеральная совокупность): Используется, когда ваш набор данных включает каждого отдельного члена целевой группы. Формула: =STDEV.P(range)

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

Функции MIN и MAX (Размах)

Чтобы понять общий разброс ваших данных, вы можете использовать функции MAX и MIN для нахождения самого высокого и самого низкого значений соответственно. Вычитание значения MIN из MAX дает общий «Размах» (диапазон) вашего набора данных.

Пример: =MAX(B2:B100) - MIN(B2:B100)

Условная статистика: AVERAGEIF и AVERAGEIFS

Часто вам не нужно рассчитывать статистику для всего столбца; вы хотите проанализировать только те строки, которые соответствуют определенным критериям. Подобно тому, как вы можете использовать SUMIF и SUMIFS для суммирования, Excel предоставляет AVERAGEIF и AVERAGEIFS для условных средних значений.

Функция AVERAGEIFS позволяет усреднять ячейки, соответствующие нескольким критериям. Например, вычислять средний доход от продаж только для региона «Восток» в течение «Первого квартала».

Практическое руководство: Аналитика данных сотрудников

Чтобы увидеть эти статистические функции в действии, давайте выполним практическое упражнение. Этот сценарий невероятно распространен при использовании Excel для HR: Данные сотрудников и аналитика.

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

Ячейка	Имя сотрудника	Отдел	Зарплата
A2 / B2 / C2	Джон Доу	ИТ	$60,000
A3 / B3 / C3	Джейн Смит	Продажи	$85,000
A4 / B4 / C4	Боб Джонсон	ИТ	$55,000
A5 / B5 / C5	Алиса Уильямс	Руководство	$250,000
A6 / B6 / C6	Том Дэвис	Продажи	$62,000

Мы хотим понять распределение зарплат внутри компании. Давайте напишем формулы:

=AVERAGE(C2:C6)      // Returns $102,400
=MEDIAN(C2:C6)       // Returns $62,000
=STDEV.S(C2:C6)      // Returns $83,383
=MAX(C2:C6)          // Returns $250,000
=MIN(C2:C6)          // Returns $55,000

Анализ результатов:
Посмотрите на разницу между значениями AVERAGE ($102,400) и MEDIAN ($62,000). Почему среднее значение такое высокое? Потому что зарплата Алисы из руководства в размере 250 000 долларов является выбросом, который значительно завышает среднее значение. Если соискатель спросит: «Какова типичная зарплата здесь?», ответ «102 400 долларов» введет его в заблуждение. Медиана в размере 62 000 долларов является гораздо более честным отражением заработной платы типичного сотрудника.

Кроме того, стандартное отклонение очень велико (83 383 доллара), что математически подтверждает то, что мы видим своими глазами: существует огромная дисперсия в том, как оплачивается труд сотрудников.

Профессиональный совет: При создании дашбордов с помощью этих формул убедитесь, что вы понимаете ссылки на ячейки Excel (использование знака $ для фиксации диапазонов, таких как $C$2:$C$6), если вы планируете копировать эти статистические формулы в несколько столбцов.

Работа с пустыми ячейками, ошибками и нулями

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

• Пустые ячейки: Функции AVERAGE, MEDIAN и STDEV полностью игнорируют пустые ячейки. Они не будут учитываться в знаменателе.
• Нули: В отличие от пустых ячеек, число 0 учитывается. Если зарплата сотрудника введена как 0, а не оставлена пустой, это искусственно занизит ваше среднее значение. Если вы хотите усреднить только ненулевые числа, используйте =AVERAGEIF(range, ">0").
• Текст: Стандартные статистические функции игнорируют текстовые строки. Однако, если числа случайно отформатированы как текст (что часто можно распознать по зеленому треугольнику в углу ячейки), Excel их проигнорирует. Сначала вы должны преобразовать их в числа.
• Ошибки: Если одна ячейка в вашем диапазоне содержит ошибку (например, #N/A или #DIV/0!), то вся формула AVERAGE или STDEV выдаст ошибку. Используйте функцию AGGREGATE для обхода ошибок в диапазонах.

Использование ИИ для сложного статистического анализа

По мере роста ваших наборов данных статистический анализ может стать математически сложным. Объединение вычислений стандартного отклонения с условной логикой (например, «Найти стандартное отклонение зарплат только для ИТ-отдела, исключая нули и ошибки») традиционно требует сложных формул массива или запутанной вложенности.

Именно здесь проявляют себя современные инструменты. Использование ИИ для анализа данных в Excel меняет ваш подход к сложной логике данных. Вместо того чтобы пытаться вспомнить, использовать ли STDEV.P или STDEV.S, или как правильно вкладывать AVERAGEIFS, вы можете просто описать вашу потребность простым языком, и позволить GPTExcel мгновенно сгенерировать точную формулу. Он идеально справляется с синтаксисом, скобками и логикой.

Чтобы узнать, как искусственный интеллект меняет то, как мы пишем формулы и анализируем метрики, ознакомьтесь с нашим руководством ChatGPT для Excel: Написание формул с помощью ИИ.

Часто задаваемые вопросы

Почему моя функция AVERAGE возвращает ошибку #DIV/0!?

Ошибка #DIV/0! возникает в функции AVERAGE, когда диапазон, на который вы ссылаетесь, не содержит числовых значений. Excel пытается разделить сумму на ноль (количество чисел), что математически невозможно. Убедитесь, что ячейки, на которые вы ссылаетесь, содержат фактические числа, а не числа, сохраненные как текст.

Стоит ли мне использовать STDEV.S или STDEV.P?

В 95% реальных сценариев вам следует использовать STDEV.S (Выборка). Функция STDEV.P (Генеральная совокупность) используется только в том случае, если вы собрали данные абсолютно для каждого члена группы, которую вы анализируете. Если вы анализируете выборку из более крупной совокупности для того, чтобы сделать выводы, STDEV.S применяет правильную математическую поправку.

Может ли функция MEDIAN анализировать текстовые значения?

Нет, MEDIAN — это исключительно математическая функция, которая требует числовых данных. Если вы попытаетесь вычислить медиану диапазона, состоящего целиком из текста, Excel вернет ошибку #NUM! (или #ЧИСЛО!). Если вам нужно найти наиболее часто встречающуюся текстовую строку, вы можете использовать функции INDEX и MATCH в сочетании с MODE.

Как рассчитать среднее значение, игнорируя нули?

Поскольку стандартная функция AVERAGE включает нули в свои вычисления (в отличие от пустых ячеек), вы должны использовать функцию AVERAGEIF для их исключения. Формула выглядит так: =AVERAGEIF(A1:A100, "<>0"). Это указывает Excel усреднять только те ячейки в диапазоне, которые не равны нулю.