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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видно из показателя среднего значения, в среднем температура у пациентов выше нормы, однако это не соответствует действительности. Медиана показывает, что как минимум у половины пациентов наблюдается нормальная температура тела, не превышающая показатель 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. Диапазон исследуемых значений может содержать:

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

Медиана (Me) – значение признака, приходящееся на середину ранжированного ряда, т.е. делящее ряд распределения на две равные части.

а) для ряда одиночных значений:

Если нечетное кол-во вариант, то серединное значение в ранжированном ряду

Если четное , то сред.арифмет. из 2х смежных серединных значений в ранжиров. ряду

б) В дискретном ряду распределения определяется номер медианы по формуле:

Номер медианы показывает то значение показателя, которое и является медианой.

в) В интервальном ряду распределения медиана рассчитывается по следующей формуле:

x - нижняя граница медианного интервала;

i - величина интервала;

f - численность медианного интервала;

S - сумма накопленных частот интервалов, предшествующих медианному.

31. Мода и ее практическое значение

Мода (Mo) – величина признака, наиболее часто встречающаяся в совокупности, т.е. имеющая наибольшую численность в ряду распределения.

а) В дискретном ряду распределения мода определяется визуально.

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

Мода будет равна:

x - нижняя граница модального интервала;

i - величина интервала;

f - численность модального интервала;

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

32. Показатели вариации и способы их расчета

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

Показатели вариации делятся на абсолютные и относительные.

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

Размах вариации – простейший показатель, разность между максимальным и минимальным значениями признака.

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

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

Простая
Взвешенная

Дисперсия – средний квадрат отклонений значений признака от их средней величины.

Простая:
Взвешенная:

Среднее квадратическое отклонение . Оно определяется как квадратный корень из дисперсии и имеет ту же размерность, что и изучаемый признак.

Простая:
Взвешенная:
.

Относительные показатели

В 1906 году великий ученый и известный специалист по евгенике Фрэнсис Гальтон посетил ежегодную выставку достижений животноводства и птицеводства в западной Англии, где совершенно случайно провел интересный эксперимент.

Как отмечает Джеймс Суровецки, автор книги «Мудрость толпы», на ярмарке Гальтона заинтересовало одно соревнование, в рамках которого люди должны были угадать вес забитого быка. Назвавший наиболее близкое к истинному число объявлялся победителем.

Гальтон был известен своим презрением к интеллектуальным способностям обычных людей. Он считал, что только настоящие эксперты смогут сделать точные утверждения о весе быка. А 787 участников соревнования не были экспертами.

Ученый собирался доказать некомпетентность толпы, вычислив среднее число из ответов участников. Каково же было его удивление, когда оказалось, что полученный им результат почти в точности соответствовал настоящему весу быка!

Среднее значение — позднее изобретение

Конечно, точность ответа поразила исследователя. Но еще более примечательным является тот факт, что Гальтон вообще догадался воспользоваться средним значением.

В сегодняшнем мире средние, и так называемые медианные показатели встречаются на каждом шагу: средняя температура в Нью-Йорке в апреле равняется 52 градусам по Фаренгейту; Стивен Карри в среднем зарабатывает 30 очков за игру; медианный семейный доход в США составляет $51 939/год.

Однако же идея о том, что множество различных результатов можно репрезентировать одним числом, довольна нова. До 17-ого века средние числа вообще не использовались.

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

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

Среднее и медианное значения

Представьте, что вы рассказываете историю о четырех людях, ужинавших прошлым вечером с вами в ресторане. Одному из них вы бы дали 20 лет, другому — 30, третьему — 40, а четвертому — 50. Что вы скажете об их возрасте в своей истории?

Скорее всего, вы назовете их средний возраст.

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

Хотя слово «среднее» (average) часто используется как синоним слова «медианное» (median), последним чаще обозначается середина чего-либо. Это слово происходит от латинского «medianus», что значит «середина».

Медианное значение в Древней Греции

История медианного значения берет свое начало с учения древнегреческого математика Пифагора. Для Пифагора и его школы медиана имела четкое определение и сильно отличалась от того, как мы понимаем среднее значение сегодня. Оно использовалось только в математике, а не в анализе данных.

В школе пифагорейцев медианное значение было средним числом в трехчленной последовательности чисел, находящемся в «равном» отношении с соседними членами. «Равное» отношение могло означать одинаково расстояние. Например, число 4 в ряду 2,4,6. Однако оно также могло выражать геометрическую прогрессию, например 10 в последовательности 1,10,100.

Статистик Черчилль Эйзенхарт объясняет, что в Древней Греции, медианное значение не использовалось в качестве репрезентирующего или заменяющего какой-либо набор чисел. Оно просто обозначало середину, и часто использовалось в математических доказательствах.

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

Современные исследователи основывают свои выводы на сборе больших объемов данных, как, например, биологи, изучающие человеческий геном. Древние ученые же могли провести несколько измерений, но выбирали лишь самое лучшее для построения своих теорий.

Как писал историк астрономии Отто Нойгебауэр, «это согласуется с осознанным стремлением античных людей минимизировать количество эмпирических данных в науке, потому что они не верили в точность непосредственных наблюдений».

Например, греческий математик и астроном Птолемей вычислил угловой диаметр Луны, используя метод наблюдения и теорию движения земли. Его результат был равен 31’20. Сегодня же мы знаем, что диаметр Луны колеблется от 29’20 до 34’6 в зависимости от расстояния от Земли. Птолемей в своих вычислениях использовал мало данных, но у него были все основания полагать, что они были точными.

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

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

Полусумма крайних значений

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

Интеллектуальный гигант 11-ого века Аль-Бируни известен как один из первых людей, использовавших методологию репрезентирующих значений. Аль-Бируни писал, что когда в его распоряжении было множество измерений, и он хотел найти лучшее среди них, он использовал следующее «правило»: нужно отыскать число, соответствующее середине между двумя крайними значениями. При вычислении полусуммы крайних значений не принимаются во внимание все числа между максимальным и минимальным значениями, а находится среднее только для этих двух чисел.

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

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

Переход к средним значениям

К началу 19-ого века использование медианного/среднего значения стало распространенным методом нахождения наиболее точно репрезентирующего значения из группы данных. Фридрих фон Гаусс, выдающийся математик своего времени, в 1809-ом году писал: «Считалось, что если некоторое число было определено несколькими прямыми наблюдениями, совершенными в одинаковых условиях, то среднее арифметическое значение является наиболее истинным значением. Если оно и не совсем строгое, то, по крайней мере, оно близко к действительности, и поэтому на него всегда можно положиться».

Почему произошел подобный сдвиг в методологии?

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

Эйзенхарт выяснил, что до конца 16-ого века большинство измерявших магнетическое отклонение ученых использовали метод ad hoc (от лат. «к этому, для данного случая, для этой цели») при выборе наиболее точного измерения.

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

До 1635-ого года вообще не было однозначных случаев использования среднего значения в качестве репрезентирующего числа. Однако именно тогда английский астроном Генри Геллибренд взял два различных результата измерения магнетического отклонения. Одно из них было сделано утром (11 градусов), а другое — днем (11 градусов и 32 минуты). Вычисляя наиболее истинное значение, он писал:

«Если мы найдем среднее арифметическое, мы с большой вероятностью можем утверждать, что результат точного измерения должен быть около 11 градусов 16 минут».

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

Слово «среднее» (average) применялось в английском языке в начале 16-ого века для обозначения финансовых потерь от ущерба, которое получило судно или перевозимый груз во время плавания. В течение следующих ста лет оно обозначало именно эти потери, которые высчитывались как среднее арифметическое. Например, если корабль во время плавания был поврежден, и команде приходилось выбрасывать за борт некоторые товары, чтобы сохранить вес судна, инвесторы несли финансовые потери, эквивалентные сумме их инвестиции — эти потери вычислялись так же, как среднее арифметическое. Так постепенно значения среднего (average) и среднего арифметического сближались.

Медианное значение

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

Френсис Гальтон был чемпионом медианного значения

Термин «медианное значение» (median) — средний член в ряде чисел, разделяющий этот ряд наполовину — появился примерно в то же время, что и среднее арифметическое. В 1599-ом году математик Эдвард Райт, работавший над проблемой нормального отклонения в компасе, впервые предложил использовать медианное значение.

«…Допустим, множество лучников стреляют в некоторую мишень. Цель впоследствии убирают. Каким образом можно узнать, где была цель? Нужно найти среднее место между всеми стрелами. Аналогично, среди множества результатов наблюдений ближе всего к истине будет то, которое находится посередине».

Медианное значение широко использовалось в девятнадцатом столетии, став обязательной частью любого анализа данных в то время. Им также пользовался и Френсис Гальтон, выдающийся аналитик девятнадцатого века. В истории о взвешивании быка, рассказанной вначале этой статьи, Гальтон изначально использовал медианное значение как представляющее мнение толпы.

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

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

Связь среднего значения и нормального распределения

Когда мы проводим множество измерений, их результаты, как говорят статистики, «нормально распределены». Это значит, что если эти данные нанести на график, то точки на нем будут изображать нечто похожее на колокол. Если их соединить, получится «колоколообразная» кривая. Нормальному распределению соответствуют многие статистические данные, например, рост людей, показатель интеллекта, а также показатель самой высокой годовой температуры.

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

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

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

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

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

Преимущества медианного значения

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

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

Представьте себе аналитика, желающего узнать репрезентативное значение для стоимости пяти домов. Четыре дома стоят $100,000, а пятый — $900,000. Среднее значение, таким образом, будет равняться $200,000, а медианное — $100,000. В этом, как и во многих других случаях, медианное значение дает лучшее понимание того, что можно назвать «стандартом».

Понимая, насколько сильно крайние значения могут сказаться на среднем, для отражения изменений в семейных доходах США используется медианное значение.

Медианные показатель также менее чувствителен к «грязным» данным, с которыми сегодня имеют дело аналитики. Многие статистики и аналитики собирают информацию, опрашивая людей в интернете. Если пользователь случайно добавит в ответ лишний ноль, который превратит 100 в 1000, то эта ошибка намного сильнее скажется на среднем значении, чем на медианном.

Среднее или медианное?

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

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

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

Для вычисления медианы в MS EXCEL существует специальная функция МЕДИАНА() . В этой статье дадим определение медианы и научимся вычислять ее для выборки и для заданного закона распределения случайной величины.

Начнем с медианы для выборок (т.е. для фиксированного набора значений).

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

Медиана (median) – это число, которое является серединой множества чисел: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана .

Для вычисления медианы необходимо сначала (значения в выборке ). Например, медианой для выборки (2; 3; 3; 4 ; 5; 7; 10) будет 4. Т.к. всего в выборке 7 значений, три из них меньше, чем 4 (т.е. 2; 3; 3), а три значения больше (т.е. 5; 7; 10).

Если множество содержит четное количество чисел, то вычисляется для двух чисел, находящихся в середине множества. Например, медианой для выборки (2; 3; 3 ; 6 ; 7; 10) будет 4,5, т.к. (3+6)/2=4,5.

Для определения медианы в MS EXCEL существует одноименная функция МЕДИАНА() , английский вариант MEDIAN().

Медиана не обязательно совпадает со . Совпадение имеет место только в том случае, если значения в выборке распределены симметрично относительно среднего . Например, для выборки (1; 2; 3 ; 4 ; 5; 6) медиана и среднее равны 3,5.

Если известна Функция распределения F(х) или функция плотности вероятности p (х) , то медиану можно найти из уравнения:

Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2), получим, что медиана вычисляется по формуле =EXP(μ). При μ=0, медиана равна 1.

Обратите внимание на точку Функции распределения , для которой F (х)=0,5 (см. картинку выше). Абсцисса этой точкиравна1. Это и есть значение медианы, что естественно совпадает с ранее вычисленным значением по формуле em.

В MS EXCEL медиану для логнормального распределения LnN(0;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,5;0;1) .

Примечание : Напомним, что интеграл от по всей области задания случайной величины равен единице.

Поэтому, линия медианы (х=Медиана) делит площадь под графиком функции плотности вероятности на две равные части.