Как вычислить моду в excel

Функция МОДА ее модификации МОДА.НСК и МОДА.ОДН в Excel

Функция МОДА в Excel выполняет поиск повторяющихся либо наиболее часто встречающихся элементов в массиве или значений в диапазоне данных и возвращает эти значения.

Функция МОДА.НСК выполняет поиск наиболее встречающихся значений среди диапазона данных или элементов массива и возвращает вертикальный массив этих значений.

Функция МОДА.ОДН находит наиболее встречающееся значение в массиве или диапазоне данных и возвращает данное значение.

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

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

Для определения наиболее часто встречаемого значения используем формулу:

B3:B12 – массив значений, в котором необходимо определить наиболее повторяющееся значение.

Пример использования функции МОДА.НСК в Excel

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

Заполним столбец «Случайные числа» с использованием функции СЛУЧМЕЖДУ(1;100), то есть случайными числами из диапазона от 1 до 100:

Примечание: функция СЛУЧМЕЖДУ выполняет пересчет полученных случайных значений при каждом вводе нового значения в любую ячейку, поэтому значения в столбце A2 на разных изображениях могут отличаться.

Выделим диапазон ячеек B2:B23 и введем формулу:

Для ввода формулы используем комбинацию клавиш Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. В результате получим:

В указанном диапазоне случайных чисел повторяются значения 48, 47 и 53. Поскольку остальные числа являются уникальными, для ячеек B5:B23 сгенерирован код ошибки #Н/Д (то есть, формула не нашла запрашиваемое значение).

Пример работы с функцией МОДА.ОДН в Excel

Пример 3. Поставщик обувного магазина поинтересовался у владельца, какой размер обуви пользуется наибольшим спросом. Экономист просмотрел данные из таблицы покупок и практически сразу дал ответ. Как ему это удалось?

Таблица данных о покупках:

Как вычислить моду в Excel? Для определения размера, который пользуется наибольшим спросом, использована формула моды:

Примечание: в отличие от среднего арифметического значения (для данного примера – примерно 41), мода определяет наиболее часто встречаемое событие в диапазоне событий. Ее рационально использовать для решения статистических задач, связанных с анализом нормально распределенных данных.

Функции МОДА, МОДА.НСК и МОДА.ОДН в Excel и особенности их использования

Функция МОДА имеет следующие аргументы:

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

Аргументы модификации функции имеют одинаковый смысл.

1. Максимальное количество аргументов в рассматриваемых функциях – не более 255.
2. Вместо аргументов типа число 1; число 2;…;число n можно указывать массив значений или ссылку на диапазон ячеек.
3. В качестве аргументов принимаются объекты данных следующих типов: имена, которые содержат числа, массивы числовых значений и ссылки.
4. Все рассматриваемые функции для определения моды игнорируют пустые, логические значения и текстовые строки, содержащиеся в диапазоне значений, переданном в качестве аргумента.
5. Если все элементы массива или диапазона чисел, переданных в качестве аргументов для всех трех функций являются уникальными (повторяющиеся значения отсутствуют), результатом работы данных функций будет являться код ошибки #Н/Д.
6. Если функция МОДА.НСК была использована в качестве обычной функции, будет возвращено единственное значение моды. Для отображения нескольких мод необходимо выделить диапазон ячеек, ввести формулу и ее аргументы, использовать сочетание клавиш Ctrl+Shift+Enter для вывода массива вычисленных результатов.

Примечание 2: функция МОДА была разработана для ранних версий Excel и пока поддерживается новыми версиями программы, однако в последующих версиях поддержка данной функции может быть отменена. Вместо данной функции предлагается использование двух ее аналогов, которые будут рассмотрены ниже.

1. Для возврата горизонтального массива наиболее встречающихся значений следует использовать запись вида ТРАНСП(МОДА.НСК(число 1; число 2;…;число n).
2. МОДА.НСК принадлежит к классу формул массива и может возвращать как одну, так и несколько мод. Для записи в качестве формулы массива необходимо использовать сочетание клавиш Ctrl+Shift+Enter.

Примечание 4: функции МОДА и МОДА.ОДН определяют центральную тенденцию множества чисел в статическом распределении способом определения моды (существуют еще два распространенных способа: поиск среднего значения и медианы), то есть путем поиска элемента, значение которого наиболее часто встречается в определенном наборе чисел.

Как вычислить моду с помощью Excel

wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали, в том числе анонимно, 9 человек(а).

Количество просмотров этой статьи: 32 871.

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

Как вычислить моду в excel

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

Чаще всего дата-журналисты анализируют данные, чтобы найти в них новые тенденции и ответы на вопросы:

• Какие масштабы у явления?
• Какую часть целого составляет то или иное явление?
• Насколько изменилась ситуация по сравнению с предыдущим периодом?
• Ситуация ухудшилась или улучшилась, показали выросли или упали?

Получить ответы на эти вопросы помогают математические и статистические функции Excel.

• Для примера будем использовать набор данных по количеству заболевших коронавирусом в России, собранный Медиазоной на основе данных федерального Роспотребнадзора и его региональных штабов. Исходные данные в формате json можно сказать здесь, а сводные данные по России, переведенные нами в формат xlsx, удобный для работы в Excel, здесь.

Процент от целого

Для того, чтобы получить представление о масштабах явления, принято считать, какую долю целого оно составляет. Например, в исследовании «Важных историй» о насилии над пожилыми говорится о том, что 82,5% таких преступлений совершаются родственниками пострадавших.

С помощью вычисления процента можно посчитать, какая доля выявленных заболевших выздоровела на сегодня в России, согласно официальным данным. Произвести такие расчеты позволяют Google Spreadsheets. Формула для подсчета процента выглядит так: =Часть / Целое * 100. В нашем примере: =Число выздоровевших / Число заболевших * 100.

Прирост или падение. Процентное изменение

Чтобы показать, как ситуация меняется со временем, считают изменение. Например, согласно официальным данным, 7 мая в России выявили на 702 заболевших больше, чем днем ранее – рост продолжается.

Прийти к такому выводу помогает простая формула вычитания: =Новое значение – Старое значение. Например: =Значение за этот год – Значение за предыдущий год. В нашем случае: =Значение за сегодня – значение за вчера. Если число получилось положительным, это указывает на прирост, если отрицательным – на падение.

Чаще всего абсолютные величины не дают нам представления о ситуации: 702 человека – это много или мало? А если днем ранее было выявлено на 471 человека больше, чем до этого, то темпы прироста увеличились или снизились?

В таких случаях показывают процентное изменение, которое тоже может быть положительным или отрицательным – сообщающем о росте или падении. Оно покажет, что 7 мая прирост составил 6,8%, и этот показатель остался на уровне предыдущего дня. Значит темпы прироста не изменились, несмотря на то, что в абсолютных числах в эти дни было выявлено разное количество заболевших людей.

Процентное изменение рассчитывается по формуле: =(Новое значение – Старое значение) / Старое значение * 100. В нашем случае: =(Количество заболевших на сегодня – Количество заболевших на вчера) / Количество заболевших на вчера * 100.

Среднее арифметическое

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

Формула среднего арифметического выглядит так: =Сумма всех значений / Количество значений. В нашем случае: = Сумма всех новых выявленных случаев заражения за неделю / 7. Чтобы не вводить формулу, можно воспользоваться функцией СРЗНАЧ, которая считает среднее арифметическое. В скобках после функции надо указать диапазон значений, среднее которых мы ищем: =СРЗНАЧ(диапазон).

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

Медиана

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

В таких случаях лучше считать медиану. Медиана показывает число в середине упорядоченного набора чисел. Это похоже на границу, которая делит данные пополам: половина данных находится выше нее, а половина – ниже. Рассчитывается она так: =МЕДИАНА(диапазон). В случае с количеством заболевших по дням медиана полезной не будет, но если бы мы работали с данными по возрастам заболевших, можно было бы посчитать не среднее, а медиану. Она показала бы возраст, ниже и выше которого находится равное количество заболевших. Исходя из медианы, можно было бы сказать, что половина заболевших моложе (или старше), например, 45 лет.

Мода в статистике – это еще один вид среднего, она показывает цифру, которая встречается в наборе данных чаще других. Она рассчитывается с помощью соответствующей функции, после которой указывается диапазон значений =МОДА(диапазон).

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

Максимум и минимум

Часто журналистов интересует, когда какое-либо явление достигало своего пика или наоборот оказывалось наименее заметным. В прошлом выпуске мы уже рассказывали, как быстро найти минимум и максимум с помощью сортировки. То же самое можно сделать и с помощью функций МИН и МАКС, после которых в скобках необходимо указать диапазон значений. Например: = МАКС(диапазон). Так можно быстро узнать, что рекорд по выявлению новых случаев заболевания за сутки был поставлен 7 мая.

На душу населения

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

Формула для подсчета количества случаев в пересчете на душу населения такая: = Количество выявленных заболевших / Численность населения * 100 000. В таком случае полученный результат будет показывать количество выявленных случаев на 100 тыс. населения (иногда считают на 10 тыс. населения, тогда последняя цифра в формуле меняется на 10 000).

Мода в статистике

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

Что подразумевается под характером данных? Прежде всего, мы говорим о количественных данных, которые выражены в числах. Но набор числовых данных может иметь разное распределение. Под распределением понимаются частоты отдельных значений. К примеру, в классе из 23 человек 2 школьника написали контрольную работу на двойку, 5 – на тройку, 10 – на четверку и 6 – на пятерку. Это и есть распределение оценок. Распределение очень наглядно можно представить с помощью специальной диаграммы – гистограммы. Для данного примера получится следующая гистограмма.

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

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

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

На диаграмме оба значения центральной тенденции совпадают и равны 10.

Но такое распределение встречается далеко не всегда, а при малом числе данных – совсем редко. Чаще бывает так, что частоты распределяются асимметрично. Тогда мода и среднее арифметическое не будут совпадать.

На рисунке выше среднее арифметическое по-прежнему составляет 10, а вот мода уже равна 9. Что в таком случае считать значением центральной тенденции? Ответ зависит от поставленных целей анализа. Если интересует уровень, сумма отклонений от которого равна нулю со всеми вытекающим отсюда свойствами и последствиями, то это средняя арифметическая. Если нужно максимально частое значение, то это мода.

Итак, зачем нужна мода? Приведу пару примеров. Экономист планово-экономического отдела обувной фабрики интересуется, какой размер обуви пользуется наибольшим спросом. Средний размер обуви, скорее всего, здесь не подойдет, тем более, что число может получится дробным. А вот мода – как раз нужный показатель.

Расчет моды

Теперь посмотрим, как рассчитать моду. Мода – это то значение в анализируемой совокупности данных, которое встречается чаще других, поэтому нужно посмотреть на частоты значений и отыскать максимальное из них. Например, в наборе данных 3, 4, 6, 7, 3, 5, 3, 4 модой будет значение 3 – повторяется чаще остальных. Это в дискретном ряду, и здесь все просто. Если данных много, то моду легче всего найти с помощью соответствующей гистограммы. Бывает так, что совокупность данных имеет бимодальное распределение.

Без диаграммы очень трудно понять, что в данных не один, а два центра. К примеру, на президентских выборах предпочтения сельских и городских жителей могут отличаться. Поэтому распределение доли отданных голосов за конкретного кандидата может быть «двугорбым». Первый «горб» – выбор городского населения, второй – сельского.

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

Для наглядности изобразим соответствующую диаграмму.

Требуется найти модальное значение цены.

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

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

Формула моды имеет следующий вид.

x₀ – значение начала модального интервала,

h – размер модального интервала,

f_Мо – частота модального интервала,

f_Мо-1 – частота интервала, находящего перед модальным,

f_Мо1 – частота интервала, находящего после модального.

Второе слагаемое формулы моды соответствует длине красной линии на рисунке выше.

Рассчитаем моду для нашего примера.

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

Расчет моды в Excel

В настоящее время большинство вычислений делается в MS Excel, где для расчета моды также предусмотрена специальная функция. В Excel 2013 я таких нашел ажно 3 штуки.

МОДА – пережиток старых изданий Excel. Функция оставлена для совмещения со старыми версиями.

МОДА.ОДН – рассчитывает моду по заданным значениям. Здесь все просто. Вставили функцию, указали диапазон данных и «Ок».

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

Моду для интервальных данных одной функцией в Excel рассчитать нельзя. То есть такая функция в готовом виде не предусмотрена. Придется прописывать вручную.