Статистические функции в excel медиана

Функция МЕДИАНА в Excel используется для анализа диапазона числовых значений и возвращает число, которое является серединой исследуемого множества (медианой). То есть, данная функция условно разделяет множество чисел на два подмножества, первое из которых содержит числа меньше медианы, а второе – больше. Медиана является одним из нескольких методов определения центральной тенденции исследуемого диапазона.

Примеры использования функции МЕДИАНА в Excel

Пример 1. При исследовании возрастных групп студентов использовались данные случайно выбранной группы учащихся в ВУЗе. Задача – определить срединный возраст студентов.

Исходные данные:

Формула для расчета:

Описание аргумента:

• B3:B15 – диапазон исследуемых возрастов.

Полученный результат:

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



Сравнение функций МЕДИАНА и СРЗНАЧ для вычисления среднего значения

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

Исходные данные:

Формула для нахождения среднего значения:

Формула для нахождения медианы:

Как видно из показателя среднего значения, в среднем температура у пациентов выше нормы, однако это не соответствует действительности. Медиана показывает, что как минимум у половины пациентов наблюдается нормальная температура тела, не превышающая показатель 36,6.

Внимание! Еще одним методом определения центральной тенденции является мода (наиболее часто встречающееся значение в исследуемом диапазоне). Чтобы определить центральную тенденцию в Excel следует использовать функцию МОДА. Обратите внимание: в данном примере значения медианы и моды совпадают:

То есть срединная величина, делящая одно множество на подмножества меньших и больших значений также является и наиболее часто встречающимся значением в множестве. Как видно, у большинства пациентов температура составляет 36,6.

Пример расчета медианы при статистическом анализе в Excel

Пример 3. В магазине работают 3 продавца. По результатам последних 10 дней необходимо определить работника, которому будет выдана премия. При выборе лучшего работника учитывается степень эффективности его работы, а не число проданных товаров.

Исходная таблица данных:

Для характеристики эффективности будем использовать сразу три показателя: среднее значение, медиана и мода. Определим их для каждого работника с использованием формул СРЗНАЧ, МЕДИАНА и МОДА соответственно:

Для определения степени разброса данных используем величину, которая является суммарным значением модуля разницы среднего значения и моды, среднего значения и медианы соответственно. То есть коэффициент x=|av-med|+|av-mod|, где:

• av – среднее значение;
• med – медиана;
• mod – мода.

Рассчитаем значение коэффициента x для первого продавца:

Аналогично проведем расчеты для остальных продавцов. Полученные результаты:

Определим продавца, которому будет выдана премия:

Примечание: функция НАИМЕНЬШИЙ возвращает первое минимальное значение из рассматриваемого диапазона значений коэффициента x.

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

Особенности использования функции МЕДИАНА в Excel

Функция имеет следующий синтаксис:

=МЕДИАНА(число1;[число2];…)

Описание аргументов:

• число1 – обязательный аргумент, характеризующий первое числовое значение, содержащееся в исследуемом диапазоне;
• [число2] – необязательный второй (и последующие аргументы, всего до 255 аргументов), характеризующий второе и последующие значения исследуемого диапазона.

Примечания 1:

1. При расчетах удобнее передавать сразу весь диапазон исследуемых значений вместо последовательного ввода аргументов.
2. В качестве аргументов принимаются данные числового типа, имена, содержащие числа, данные ссылочного типа и массивы (например, =МЕДИАНА({1;2;3;5;7;10})).
3. При расчете медианы учитываются ячейки, содержащие пустые значения или логические ИСТИНА, ЛОЖЬ, которые будут интерпретированы как числовые значения 1 и 0 соответственно. Например, результат выполнения функции с логическими значениями в аргументах (ИСТИНА;ЛОЖЬ) эквивалентен результату выполнения с аргументами (1;0) и равен 0,5.
4. Если один или несколько аргументов функции принимают текстовые значения, которые не могут быть преобразованы в числовые, или содержат коды ошибок, результатом выполнения функции будет код ошибки #ЗНАЧ!.
5. Для определения медианы выборки могут быть использованы другие функции Excel: ПРОЦЕНТИЛЬ.ВКЛ, КВАРТИЛЬ.ВКЛ, НАИБОЛЬШИЙ Примеры использования:

• =ПРОЦЕНТИЛЬ.ВКЛ(A1:A10;0,5), поскольку по определению медиана – 50-я процентиль.
• =КВАРТИЛЬ.ВКЛ(A1:A10;2), так как медиана – 2-я квартиль.
• =НАИБОЛЬШИЙ(A1:A9;СЧЁТ(A1:A9)/2), но только если количество чисел в диапазоне является нечетным числом.

Примечания 2:

1. Если в исследуемом диапазоне все числа распределены симметрично относительно среднего значения, среднее арифметическое и медиана для данного диапазона будут эквивалентны.
2. При больших отклонениях данных в диапазоне («разбросе» значений) медиана лучше отражает тенденцию распределения значений, чем среднее арифметическое. Отличным примером является использование медианы для определения реального уровня зарплат у населения государства, в котором чиновники получают на порядок больше обычных граждан.
3. Диапазон исследуемых значений может содержать:

Скачать примеры функции МЕДИАНА для статистического анализа в Excel

• Нечетное количество чисел. В этом случае медианой будет являться единственное число, разделяющее диапазон на два подмножества больших и меньших значений соответственно;
• Четное количество чисел. Тогда медиана вычисляется как среднее арифметическое для двух числовых значений, разделяющих множество на два указанных выше подмножества.

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

Статистические функции

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

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

Запустить Мастер функций можно тремя способами:

1. Кликнуть по пиктограмме «Вставить функцию» слева от строки формул.



2. Находясь во вкладке «Формулы», кликнуть на ленте по кнопке «Вставить функцию» в блоке инструментов «Библиотека функций».



3. Набрать на клавиатуре сочетание клавиш Shift+F3.

При выполнении любого из вышеперечисленных вариантов откроется окно «Мастера функций».

Затем нужно кликнуть по полю «Категория» и выбрать значение «Статистические».

После этого откроется список статистических выражений. Всего их насчитывается более сотни. Чтобы перейти в окно аргументов любого из них, нужно просто выделить его и нажать на кнопку «OK».

Для того, чтобы перейти к нужным нам элементам через ленту, перемещаемся во вкладку «Формулы». В группе инструментов на ленте «Библиотека функций» кликаем по кнопке «Другие функции». В открывшемся списке выбираем категорию «Статистические». Откроется перечень доступных элементов нужной нам направленности. Для перехода в окно аргументов достаточно кликнуть по одному из них.

Урок: Мастер функций в Excel

МАКС

Оператор МАКС предназначен для определения максимального числа из выборки. Он имеет следующий синтаксис:

=МАКС(число1;число2;…)

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

МИН

По названию функции МИН понятно, что её задачи прямо противоположны предыдущей формуле – она ищет из множества чисел наименьшее и выводит его в заданную ячейку. Имеет такой синтаксис:

=МИН(число1;число2;…)

СРЗНАЧ

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

=СРЗНАЧ(число1;число2;…)

СРЗНАЧЕСЛИ

Функция СРЗНАЧЕСЛИ имеет те же задачи, что и предыдущая, но в ней существует возможность задать дополнительное условие. Например, больше, меньше, не равно определенному числу. Оно задается в отдельном поле для аргумента. Кроме того, в качестве необязательного аргумента может быть добавлен диапазон усреднения. Синтаксис следующий:

=СРЗНАЧЕСЛИ(число1;число2;…;условие;[диапазон_усреднения])

МОДА.ОДН

Формула МОДА.ОДН выводит в ячейку то число из набора, которое встречается чаще всего. В старых версиях Эксель существовала функция МОДА, но в более поздних она была разбита на две: МОДА.ОДН (для отдельных чисел) и МОДА.НСК(для массивов). Впрочем, старый вариант тоже остался в отдельной группе, в которой собраны элементы из прошлых версий программы для обеспечения совместимости документов.

=МОДА.ОДН(число1;число2;…)

=МОДА.НСК(число1;число2;…)

МЕДИАНА

Оператор МЕДИАНА определяет среднее значение в диапазоне чисел. То есть, устанавливает не среднее арифметическое, а просто среднюю величину между наибольшим и наименьшим числом области значений. Синтаксис выглядит так:

=МЕДИАНА(число1;число2;…)

СТАНДОТКЛОН

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

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

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

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

НАИБОЛЬШИЙ

Данный оператор показывает в выбранной ячейке указанное в порядке убывания число из совокупности. То есть, если мы имеем совокупность 12,97,89,65, а аргументом позиции укажем 3, то функция в ячейку вернет третье по величине число. В данном случае, это 65. Синтаксис оператора такой:

=НАИБОЛЬШИЙ(массив;k)

В данном случае, k — это порядковый номер величины.

НАИМЕНЬШИЙ

Данная функция является зеркальным отражением предыдущего оператора. В ней также вторым аргументом является порядковый номер числа. Вот только в данном случае порядок считается от меньшего. Синтаксис такой:

=НАИМЕНЬШИЙ(массив;k)

РАНГ.СР

Эта функция имеет действие, обратное предыдущим. В указанную ячейку она выдает порядковый номер конкретного числа в выборке по условию, которое указано в отдельном аргументе. Это может быть порядок по возрастанию или по убыванию. Последний установлен по умолчанию, если поле «Порядок» оставить пустым или поставить туда цифру 0. Синтаксис этого выражения выглядит следующим образом:

=РАНГ.СР(число;массив;порядок)

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

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

Медиана выборки

Следовательно, медиана — это число, которое выражает центр набора определенной серии чисел. Следовательно, половина чисел в указанном ряду будет больше, чем рассчитанное медианное значение, а вторая половина числового ряда будет меньше. Для определения медианы необходимо отсортировать числовые значения выборки. Следовательно, медиана выборки цифр 2, 2, 3, 5, 6, 7, 8 будет 5. В этом случае выборка состоит из семи значений: три цифры больше, чем полученная медиана, и три менее.

Важно! Если выборка состоит из четного числа целых цифровых значений, медиана будет представлена ​​в виде десятичной дроби. Следовательно, медиана выборки из серий 2, 2, 3, 5, 6, 7, 8, 10 составляет 5,5.

Расчет медианы для выборки с использованием функций Excel выполняется с помощью соответствующей функции МЕДИАНА. В скобках формулы можно ввести до 255 значений, из которых нужно рассчитать медиану.

Функция МЕДИАНА с выборкой из семи чисел

Медиана не всегда совпадает со средним числовым значением представленного ряда. Как правило, совпадение происходит только тогда, когда есть тождество, симметричное относительно среднего.

Примечание! Медиана не равна среднему. Выборочное среднее чисел 2, 2, 3, 5, 6, 7, 300 соответствует значению 46,42857, в то время как медиана по-прежнему равна 5, поскольку соотношение между числами больше 5 и меньше не изменилось.

Разница между медианой и средним значением ряда чисел

Медиана непрерывного распределения

Теперь рассмотрим вычисление медианы в случае непрерывного распределения. Медианная экспонента является результатом решения функции распределения случайных непрерывных числовых значений. Если известен показатель степени функции распределения (то есть функция плотности), то среднее значение можно вычислить по следующей формуле:

Формула медианы

При решении этого уравнения аналитическим методом с логнормальным распределением LnN (µ; σ; 2) можно вычислить медианное значение по формуле = EXP (µ). Учитывая, что µ = 0, медиана будет равна 1. В Excel результат медианы для логнормального распределения, представленный формулой LnN (0; 1), вычисляется с помощью функции = LOGNORM.OBR (0,5; 0; 1).

Вычисление медианы с помощью функции ЛОГНОРМ.ОБР

Функция МЕДИАНА

Используя функцию МЕДИАНА в Excel, вы можете вычислить среднюю точку набора чисел. Синтаксическим выражением является формула = МЕДИАНА (число 1; [число 2]…). Аргументами являются число 1, число 2 и т.д. До числа 255. Первое значение является обязательным, следующие — необязательными, но они помогают вычислить медиану.

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

Среднее значение ряда чисел

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

1. Медиана — это среднее значение ряда чисел. Кроме того, половина чисел больше медианы, а другая половина меньше. Например, медиана ряда чисел 2, 2, 3, 5, 6, 7, 9 равна 5.
2. Среднее значение — это среднее арифметическое, которое рассчитывается путем сложения всех чисел заданного ряда и деления этой суммы на количество показателей, то есть 2 + 2 + 3 + 5 + 6 + 7 + 9 = 34/7. В этом случае среднее значение соответствует 4,857143.
3. Индикаторный режим — это выбор числа, которое встречается чаще других в серии чисел. В представленном ряду чисел 2, 2, 3, 5, 6, 7, 9 этому показателю соответствует цифра 2.

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

Вычисление значений непрерывного ряда

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

1. Вы должны активировать ячейку справа или под строкой или строкой, в которой представлены значения.
2. На вкладке «Основные» выберите параметр «Автосумма», который находится в группе инструментов «Редактировать».
3. Также эта функция находится на вкладке «Формулы».
4. Рядом с кнопкой Автосумма, обозначенной значком Σ, находится стрелка со встроенным меню. Выбираем показатель «Средний». Это значение также можно получить, выполнив функцию СРЕДНЕЕ.

Использование автосуммы для нахождения среднего непрерывного ряда значений

Среднее взвешенное значение

В этом случае вам придется использовать несколько функций: СУММ и СУММПРОИЗВ. Попробуем посчитать среднюю себестоимость единицы товара, если товаров три и количество осуществленных продаж. Синтаксис формулы выглядит следующим образом: = СУММПРОИЗВ (R [-3] C: R [-3] C [1]: R [-1] C [1]) / СУММ (R [-3] C [1]: R [-1] C [1]). После выполнения данной функции получаем среднюю себестоимость единицы товара 184,5238095

Формула для определения средней стоимости товаров с разной ценой и разным объемом продаж

Вычисление без учета нулевых значений

Чтобы исключить нулевые значения в процессе расчета, нужно использовать две функции: ЕСЛИ и СРЕДНИЙ. Давайте посмотрим на пример. В представленных числовых рядах 4, 6, 8, 0, 5, 8 необходимо найти среднее значение, но с условием исключения нулевого значения. Функция будет иметь следующий синтаксис: = СРЗНАЧЕСЛИ (A2: A7; « 0»). Следовательно, результат функции без нуля будет 6.2.

Функция СРЗНАЧЕСЛИ

Полезные видео

Изучите основы вычисления медианы в Excel, посмотрев некоторые руководства на YouTube