Как добавить медиану на график excel

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

Понятие среднее значение за частую подразумевает среднее арифметическое число. То есть результат после разделения суммы чисел на их количество. Так работает функция СРЗНАЧ. Но в программе Excel можно получить еже 2 вида средних значений: МЕДИАНА и МОДА – самое популярное и часто встречаемое значение в списке данных.

Примеры как найти медиальное, популярное и арифметическое среднее

Ниже на рисунке представлен список из 20 студентов с оценками их контрольных работ. Допустим, что мы хотим проанализировать все их оценки по всем трем видам средних значений в Excel:

1. Арифметическое.
2. Медиана.
3. Мода — наиболее популярное значение.

На основе полученных значений сделаем соответствующие выводы.

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

1. =СРЗНАЧ(B2:B21)
2. =МЕДИАНА(B2:B21)
3. =МОДА(B2:B21)

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

Как найти среднее значение в Excel с помощью функции СРЗНАЧ

Среднее арифметическое число оценок равно 85,1 медиальное – 90,5 и наиболее часто встречаемая оценка 93 балла. Среднеарифметическое число рассчитано с помощью функции СРЗНАЧ, которая суммирует все значения в указанном диапазоне и делит их сумму на их же количество. Сравнение медианы и наиболее часто встречающегося числа со средним арифметическим позволяет получить дополнительную информацию после такого анализа данных.

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

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

Соответственно функция МЕДИАНА возвращает в итоговом результате своих вычислений число 90,5. Что из этого следует?

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

Формула расчета среднего значения в Excel с функцией МОДА

Наиболее популярное значение находится с помощью функции с соответствующем названием МОДА. Данная функция возвращает оценку, которую получило наибольшее количество студентов. На первом рисунке на против оценок отображается их количество в списке. Как видно оценка в 93 балла появляется 4 раза в одном и том же списке – наиболее чаще других оценок. Если в наборе данных каждое значение появляется такое же количество раз (например, все значения уникальны – значит появляются в списке только 1 раз или же в столбце «Кол-во оценок» все значения одинаковы), тогда функция МОДА возвращает код ошибки недоступных данных #Н/Д! Если больше чем одно значение встречается такое же количество раз (например, если бы 91 балл также встречался 4 раза, как и 93 балла), тогда функция возвращает первый найденный результат.

Функция МОДА.ОДН возвращает массив средних значений в Excel

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

Ниже на рисунке представлен список чисел, в котором есть сращу 2 самых популярных числа (2 и 4). Числа 2 и 4 появляются в общем списке двукратно. Так как функция МОДА.ОДН возвращает целый массив значений, следует для нее соответствующий диапазон ячеей для заполнения этими всеми значениями. На рисунке показано как приготовлены 3 ячейки, несмотря на то, что в данном случае функция МОДА.ОДН возвращает только 2 значения. Если диапазон ячеек больше чем возвращаемый массив с результатами лишние ячейки будут заполнены кодом ошибки недоступных данных #Н/Д!

Формула, которая содержит функцию МОДА.ОДН выполняется в массиве с помощью нажатия комбинации клавиш CTRL+SHIFT+Enter (а не просто Enter как обычно выполняется ввод формул). Если все сделано правильно в строке формул по краям появятся фигурные скобки (<>), которые информируют пользователя о том, что введена формула массива.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как построить диаграмму по таблице в Excel: пошаговая инструкция

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

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

Как построить диаграмму по таблице в Excel?

1. Создаем таблицу с данными.
2. Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
3. Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
4. После выбора определенного вида гистограммы автоматически получаем результат.
5. Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
6. Сделаем подпись для вертикальной оси. Вкладка «Макет» — «Подписи» — «Названия осей». Выбираем вертикальную ось и вид названия для нее.
7. Вводим «Сумма».
8. Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
9. Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
10. Изменим цвет и стиль.

Выберем другой стиль диаграммы (вкладка «Конструктор» — «Стили диаграмм»).

Как добавить данные в диаграмму в Excel?

1. Добавляем в таблицу новые значения — План.
2. Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
3. Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» — «Легенда» — «Добавить легенду справа» (внизу, слева и т.д.). Получаем:

Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).

Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.

Как поменять местами оси в диаграмме Excel?

1. Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
2. В открывшемся меню нажимаем кнопку «Строка/столбец».
3. Значения для рядов и категорий поменяются местами автоматически.

Как закрепить элементы управления на диаграмме Excel?

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

1. Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
2. В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
3. Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:

Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» — «Таблица».

Как сделать диаграмму в процентах в Excel?

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

Исходные данные для примера:

1. Выделяем данные A1:B8. «Вставка» — «Круговая» — «Объемная круговая».
2. Вкладка «Конструктор» — «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
3. Выбираем подходящий.
4. Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» — «Изменить тип диаграммы». Выбираем круговую с вторичной.
5. Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
6. Задаем следующие параметры ряда:
7. Получаем нужный вариант:

Диаграмма Ганта в Excel

Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.

1. У нас есть таблица (учебная) со сроками сдачи отчетов.
2. Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
3. Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
4. Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» — «Использовать формулу для определения форматируемых ячеек».
5. Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:

Дашборд CSAT расчет индекса удовлетворенности клиентов в Excel.
Пример как сделать шаблон дашборда для формирования отчета по индексу удовлетворенности клиентов CSAT. Скачать готовый дашборд C-SAT для анализа индексов и показателей.

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

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Медиана в EXCEL

Для вычисления медианы в 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().

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

Чтобы в этом убедиться — построим гистограмму для симметричной выборки, состоящую из 36 значений, и вычислим среднее и медиану (см. файл примера лист Медиана-выборка ).

В чем же ценность медианы ? Почему ее используют зачастую наравне со средним значением ?

Оба параметра используются для определения «центральной тенденции» выборки . Для выборки с несимметричным распределением, медиана будет отличаться от среднего . Например, для (1; 2; 3 ; 4 ; 5; 600) медиана равна 3,5, а вот среднее равно 103,5 (смещено в сторону б о льшего значения).

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

Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у половины сотрудников зарплата меньше или равна 15 тыс. руб.

Примечание : Так как медиана является 50-й процентилью и 2-й квартилью , ее также можно вычислить с помощью формул =ПРОЦЕНТИЛЬ.ВКЛ( Выборка;0,5 ) и =КВАРТИЛЬ.ВКЛ( Выборка;2 ) , где Выборка – это ссылка на диапазон, содержащий значения выборки.

Если выборка содержит нечетное количество чисел, то для вычисления медианы можно также воспользоваться формулой: НАИБОЛЬШИЙ(Выборка;СЧЁТ(Выборка)/2) .

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

Если Функция распределения F (х) случайной величины х непрерывна, то медиана является решением уравнения F(х) =0,5.

Примечание : подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .

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

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

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

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

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

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

Примечание : В статье о распределениях MS EXCEL приведены ссылки на распределения для которых в MS EXCEL существуют специальные функции ( нормальное распределение , гамма-распределение , Экспоненциальное и др.). Используя эти функции можно вычислить медиану соответствующего распределения.