Excel формула медиана: Медиана в EXCEL. Примеры и описание

Сводная статистика в Excel

Вычисление суммарной статистики в Excel

Вычисление суммарной статистики в Excel

Введение

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

Пошаговое руководство по расчету сводной статистики в Excel

Анализ данных в Excel

Открытие электронной таблицы

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

Выбор данных

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

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

Использование пакета инструментов анализа данных

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

Альтернативные методы расчета суммарной статистики в Excel

Вычисление суммарной статистики в Excel

Среднее

Среднее значение набора данных можно рассчитать с помощью функции AVERAGE в Excel. Эта функция принимает диапазон ячеек и вычисляет среднее арифметическое (среднее) значений в этом диапазоне.

Медиана

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

Режим

Режим набора данных можно рассчитать с помощью функции MODE в Excel. Эта функция принимает диапазон ячеек и вычисляет наиболее часто встречающееся значение в этом диапазоне.

Стандартное отклонение

Стандартное отклонение набора данных можно рассчитать с помощью функций STDEV.S или STDEV.P в Excel. Функция STDEV.S принимает диапазон ячеек и вычисляет стандартное отклонение выборки, а функция STDEV.P принимает диапазон ячеек и вычисляет стандартное отклонение популяции.

Разница

Дисперсию набора данных можно рассчитать с помощью функций VAR.S или VAR.P в Excel. Функция VAR.S принимает диапазон ячеек и вычисляет дисперсию выборки, а функция VAR.P принимает диапазон ячеек и вычисляет дисперсию популяции.

Ассортимент

Диапазон набора данных можно рассчитать с помощью функций MIN и MAX в Excel. Функция MIN принимает диапазон ячеек и вычисляет минимальное значение в этом диапазоне, а функция MAX принимает диапазон ячеек и вычисляет максимальное значение в этом диапазоне. Тогда диапазон — это просто разница между ними.

Заключение

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

Гид по статистическому пакету Excel

Содержание статьи

• 1 СРЗНАЧ() и СРЗНАЧА()
  • 1.1 Замечание
• 2 СРЗНАЧЕСЛИ()
• 3 МИН()/МАКС() и НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ()
• 4 МЕДИАНА() и МОДА()
• 5 Заключение

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

Если вы ищете работу и она хоть как-то связана с математикой/экономикой/финансами, то вы очень часто будете встречать такие требования к кандидату:
— Отличное знание статистики;
— Знание и умение Python/R, чтобы эту статистику применять.

Но что делать, если никаких знаний по языкам программирования у вас нет, а встречаться со статистическими моделями так или иначе придется? А работу-то найти нужно срочно…

К счастью, в версии Microsoft Excel выше 2010 вшит целый статистический пакет. О нем мало кто знает, а его реально можно использовать, если нет навыков программирования или доступного компилятора под рукой.

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

Список статистических функций

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

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

СРЗНАЧ() и СРЗНАЧА()

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

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

Пусть дан диапазон А1:С2 и мы ищем среднее значение по всем 6 ячейкам диапазона:

Применение функции СРЗНАЧ()

Однако, результат функции СРЗНАЧ(А1:С2) будет не 8,7, а 13. Почему? (4+15+11+22)/6 = 8,7 ведь?

Да, это правильно, но функция СРЗНАЧ() берет в расчет только те ячейки, где «встречает» числа. Текстовая информация и пустые ячейки просто игнорируются. Поэтому в данном примере СРЗНАЧ() усредняет по 4 ячейкам и выдает правильный ответ – 13.

А вот если нужно произвести усреднение по всему диапазону, вне зависимости от типа данных, нужно использовать функцию СРЗНАЧА().

Принцип работы такой же, как и у СРЗНАЧ(), только на вход будут поступать абсолютно все ячейки. Результат в нашем примере будет уже ожидаемый – 8,7.

Применение функции СРЗНАЧА()

Замечание

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

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

1. В эти дни не было ни одной продажи. Тогда эти дни должны принимать участие в расчете среднего значения и менеджеру нужно использовать СРЗНАЧА() – так он исключит игнорирование пустых ячеек.

2. Эти дни были выходными. Тогда пропуски сами по себе никакой информации не несут и их надо игнорировать: фактически, эти дни не принимают участие в статистической выборке и функция СРЗНАЧ() поможет их пропустить.

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

СРЗНАЧЕСЛИ()

Очевидно, что функция СРЗНАЧЕСЛИ() возвращает среднее тех значений, который удовлетворяют каким-то условиям. Помимо этого, условия можно накладывать не только на сами значения, но и на другие ячейки. Проиллюстрируем.

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

Применение функции СРЗНАЧЕСЛИ() с условием на аргумент

Мы выделили диапазон А1:С3 и наложили на него условие – «>0». А можно сделать по-другому.

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

Формула записывается так:

=СРЗНАЧЕСЛИ(Диапазон_на_который_накладываем_условия; “Условие”; Диапазон_по_которому_считаем_среднее_значение)

В нашем случае это примет вид:

Применение функции СРЗНАЧЕСЛИ() с условием на другой диапазон

Кстати говоря, условия можно комбинировать с помощью функции СРЗНАЧЕСЛИМН().

Предположим, что в аптеке Зеленый Крест продается несколько видов Анальгина и в нашу таблицу они все занесены как Анальгин.

Тогда, чтобы усреднить цену всех Анальгинов в аптеке Зеленый Крест, нужно просто использовать формулу:

=СРЗНАЧЕСЛИМН(С2:С13; A2:A13; “зеленый крест”;B2:B13; “анальгин”)

Обратите внимание: диапазон усреднения указывается в конце только при использовании функции СРЗНАЧЕСЛИ() с дополнительным условием. В остальных случаях диапазон ячеек, по которым вычисляется среднее значение, стоит первым.

МИН()/МАКС() и НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ()

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

Функция МИН() просто принимает массив аргументов и находит самое маленькое число. МАКС() – самое большое. Все просто.

Функция НАИМЕНЬШИЙ() же находит n-ое наименьшее число в массиве. НАИБОЛЬШИЙ(), наоборот, находит n-ое наибольшее число.

Например, нужно найти пятое по величине число. Вводим:

=НАИБОЛЬШИЙ(диапазон; 5).

Фактически, получается, что результат работы НАИБОЛЬШИЙ(массив;1) и МАКС(массив) – одно и то же. Аналогичная ситуация с НАИМЕНЬШИЙ(массив;1) и МИН(массив).

Рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel», если вы хотите научиться выполнять рутинную работу быстрее.

МЕДИАНА() и МОДА()

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

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

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

Поиск медианы для «четного» массива

Вот если бы выборка начиналась не с единицы, а с двойки, то ответ был бы ровно 6.

Поиск медианы для «нечетного» массива

Теперь перейдем к моде. Мода – самое часто встречающееся число в выборке.

У функции нахождения моды есть целых три модификации в Excel старшее версии 2010 года: МОДА(), МОДА.ОДН() и МОДА.НСК().

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

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

Применение функций МОДА() и МОДА.ОДН()

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

Работает МОДА.НСК() следующим образом: выделяем побольше ячеек (если заранее не знаем, сколько мод у нас получится), в строке формул прописываем =МОДА. НСК(диапазон) и нажимаем Ctrl+Shift+Enter. Получили все моды в столбик.

«Слепой» метод применения функции МОДА.НСК()

Значения #Н/Д появляются, просто потому что мод у нас всего 2. Такой метод поиска мод называется «слепым» — мы просто берем побольше ячеек, чтобы наверняка хватило.

Если Вы не любите подобный «мусор» и Вам нравится, когда все красиво, можно сначала оценить: а сколько же у нас вообще будет мод? А потом просто выделить нужное количество ячеек.

Делается это так: сначала применяем функцию СЧЁТ() к нашей МОДА.НСК() – получили количество мод. А теперь выделяем только две ячейки и делаем все также, как написано выше.

Модификация применения МОДА.НСК()

Заключение

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

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

Автор: Андрон Алексанян, СОО «Аптека-Центр», эксперт SF Education

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

excel, статистика, формулы Excel, функции excel

Функция Excel Median() — javatpoint

следующий → ← предыдущая Простыми математическими терминами медиана определяется как среднее значение в заданном наборе данных, отделяющее большую половину значений данных от нижней половины . В частности, это среднее значение набора данных, в котором элементы расположены по порядку величины. Аналогично, » Функция Excel Median() возвращает число, которое находится в центре всех чисел в заданном наборе данных «. Формула вычисления медианы отличается для четной и нечетной группы чисел. Например, в наборе данных, содержащем нечетных значений, медианой будет среднее значение всех чисел . Напротив, для набора данных, содержащего четных групп чисел, медиана извлекается путем вычисления среднего значения двух средних чисел . ПРИМЕЧАНИЕ. Медиана рассчитывается после упорядочивания набора данных в порядке возрастания или убывания. В Excel упорядоченное расположение набора данных не будет видно, поскольку это делается в бэкэнде. Люди всегда путают среднее с медианой и иногда используют эти слова взаимозаменяемо. Но оба они разные, поскольку означают среднее значение группы чисел. Напротив, медиана является средним значением, когда набор данных расположен в порядке возрастания или убывания. Медиана менее чувствительна к выбросам (чрезвычайно большим или малым значениям данных). Поэтому рекомендуется использовать МЕДИАНУ в расчетах для асимметричного распределения. Прекрасным примером является медианная заработная плата, поскольку она дает лучшее представление о том, сколько конкретно зарабатывают сотрудники. Напротив, если вы рассчитали среднюю зарплату, есть вероятность, что цифры могут быть искажены из-за очень низких или высоких цифр зарплаты. Синтаксис =МЕДИАНА([число1],[число2] и т. д..) Параметры номер 1 (обязательно): Этот параметр представляет собой первый номер списка. В функцию медианы необходимо ввести хотя бы одно число. [число2] и т. д. (необязательно) : Этот параметр представляет собой список чисел, для которых вы хотите найти медиану. Они могут содержать любые значения, включая числа, даты (где даты упоминаются в общем формате), именованные диапазоны, массивы или даже ссылки на ячейки. ПРИМЕЧАНИЕ. В Excel 2003 и всех его более ранних версиях функция МЕДИАНА может содержать только до 30 аргументов, только в Excel 2007 она была расширена до 255 аргументов. Теперь в последних версиях Excel 365, Excel 2016, 2013, 2010 и 2007 функция медианы может принимать до 255 аргументов. Если набор данных содержит нечетное количество значений, медианой будет среднее значение всех чисел. Напротив, для набора данных, содержащего четную группу чисел, медиана извлекается путем вычисления среднего значения двух средних чисел. Функция Median() также включает в расчеты ячейки с нулевыми значениями (0). Хотя функция Median() не включает пустые ячейки и ячейки, содержащие текст и логические значения. Функция Median() подсчитывает логические значения в наборе данных. Если пользователь передает логическое значение ЛОЖЬ в параметрах, оно считывается как 0, а если пользователь передает логическое значение ИСТИНА, оно считывается как 1. Например, формула МЕДИАНА (1, 4, ИСТИНА, ЛОЖЬ, 3) возвращает 0, который является медианой набора данных {1, 4, 0, 1, 3}. Примеры Пример 1: Рассчитать медиану для следующих чисел. а) Таблица А Решение: Выберите ячейку и введите приведенную ниже формулу, чтобы вычислить медиану приведенной выше таблицы. Используемая формула: =МЕДИАНА(A15:A35) Если вы отсортируете значения по возрастанию и подсчитаете количество наблюдений здесь, у нас будет 21 (в таблице A), а среднее наблюдение будет 11-м наблюдением со значением 84. б) Таблица В Решение: Выберите ячейку и введите приведенную ниже формулу, чтобы вычислить медиану приведенной выше таблицы. Используемая формула: =МЕДИАНА(G15:G35) Если число наблюдений было бы 20 (как в случае с таблицей B), то медиана была бы 10-м + 11-м наблюдением, деленным на 2, что и будет нашей медианой. Как показано в приведенном выше примере, формула медианы Excel работает одинаково для значений чисел и дат, поскольку даты считаются числовыми значениями в Excel. Идите вперед и попробуйте формулу МЕДИАНА для значений дат! Пример 2: Найдите медиану в таблице ниже на основе критерия. Вычислите медиану, где Части равны Рабочему столу. Хотя в Microsoft Excel нет специальных функций для вычисления медианы на основе условий, как мы знаем, Excel позволяет нам создавать настраиваемые формулы путем объединения двух или более функций. Тот же трюк мы можем использовать для построения нашей собственной формулы МЕДИАНЫ, интегрируя ее с функцией ЕСЛИ. Используемая формула = МЕДИАНА (ЕСЛИ (table_range = критерии, median_range)) Пояснение к формуле: Чтобы найти медиану, мы использовали формулу МЕДИАНЫ. Внутри формулы МЕДИАНА мы включили функцию ЕСЛИ. Внутри IF мы проверим, равен ли диапазон таблицы значению критерия (Range (A45: A55) = Desktop) или нет. Если функция «ЕСЛИ» возвращает значение «истина», она вернет медианный_диапазон (D45: D55). После этого в игру вступит функция Median, чтобы найти медиану для возвращаемого диапазона. На приведенном выше снимке экрана, чтобы найти среднюю сумму для рабочего стола, мы создали еще одну таблицу с названием частей, медианой и формулой, и с помощью следующей формулы мы получили медиану определенного элемента на основе этого условия: =МЕДИАНА(ЕСЛИ(B45:B55=F46,D45:D55)) В приведенном выше примере мы нашли медиану на основе одного критерия, но вы также можете позволить себе больше и использовать формулу медианы для нескольких критериев. Давай, попробуй! Следующая тема# ← предыдущая следующий →

Excel Формула массива МЕДИАНА ЕСЛИ

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

Основы

Синтаксис и аргументы для формулы МЕДИАНА ЕСЛИ следующие:

{=МЕДИАНА(ЕСЛИ(логическая_проверка,значение_если_истина,значение_если_ложь)}

Назначение каждой части формулы:

9013 функция находит среднее значение из набора чисел

Функция ЕСЛИ позволяет нам установить условия для значений, которые мы хотим проверить.

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

Чтобы завершить формулу массива, введите формулу (без фигурных скобок), затем одновременно нажмите клавиши Control, Shift и Enter. Это завершит формулу массива в Excel и вставит фигурные скобки в начало и конец формулы. Не вводите в формулу фигурные скобки. Формулы массива, созданные с помощью клавиш Control, Shift и Enter, также называются формулами CSE.

Пример

Формула MEDIAN IF полезна для расчета медианных сумм грантов/стипендий, присуждаемых учащимся, соответствующим различным критериям проживания, жилья и предполагаемого семейного вклада (EFC), которые необходимы для заполнения шаблона калькулятора чистых цен, который оценивает чистые затраты на студентов.

Метод

В этом примере мы работаем с двумя рабочими листами в нашей рабочей книге Excel:

1. Набор данных — этот рабочий лист содержит фиктивные данные, которые мы будем использовать для расчета средней суммы вознаграждения (см. рис. 1). Он включает в себя студенческий билет, статус проживания, статус жилья, диапазон EFC и суммы присужденных грантов / стипендий. Обратите внимание, что на рис. 1 показана только часть набора данных, чтобы проиллюстрировать, какие элементы данных включены.
2. Сводка — этот рабочий лист содержит сводную сетку, в которую можно извлечь информацию из рабочего листа набора данных (см. рис. 2)

Figure 1. Dataset Worksheet (only a portion of the dataset is shown)

Figure 2. Summary Worksheet — Net Price Calculator Grid

Для начала используйте формулу массива MEDIAN IF, чтобы найти медианную сумму вознаграждения для студентов, которые проживают в округе, живут на территории кампуса и имеют EFC, равный 0 долларов (см. красную рамку на рис. 2, это ячейка B4). Светло-голубые, темно-синие и фиолетовые прямоугольники являются критериями формулы.

В ячейку B4 итогового рабочего листа введите следующую формулу:

Следующие ссылки на столбцы и ячейки представляют наши критерии:

из следующего столбца:

• Набор данных!

Символ $ в ссылке на расположение замораживает указанную ссылку на столбец или строку, поэтому при копировании формулы и вставке в новые ячейки закрепленные ссылки остаются прежними.

Чтобы завершить формулу массива, одновременно нажмите клавиши Control, Shift и Enter (формула будет автоматически вставлена ​​в фигурные скобки).

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

В описательной форме эта формула гласит: «Вернуть медиану значений сумм грантов/стипендий для студентов, чей статус проживания соответствует статусу проживания, выбранному в сводной таблице, И чей статус проживания соответствует статусу проживания, выбранному в сводной таблице, И чьи Диапазон EFC соответствует диапазону EFC, выбранному в сводной сетке».