Как посчитать медиану в сводной таблице в excel

Обновлено: 03.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если у вас есть исходные данные в строках 10:1000, обозначения категорий исходных данных в столбце A, значения исходных данных в столбце C и категория, рассматриваемая в G3, следующая формула массива найдет медиану:

Зафиксируйте запись с помощью Ctrl + Shift + Enter и скопируйте вниз для категорий в G4, G5 и т.д.

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

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

Самый быстрый и простой способ получить медиану в сводных таблицах - это импортировать файл Excel в Google Sheets. Там вы можете создать сводную таблицу и использовать медиану.

Вы можете фактически использовать функцию iserror чтобы исключить общее количество строк из медиан. Например, если метки Total row находятся в row A а данные, медиана которых вы хотите, находятся в row I :

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

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

Жаль, не могу включить вложения . Я сделаю снимок моего примера:

это конечный результат - см. изображение здесь:

ПРИМЕЧАНИЕ: вы не можете поместить ячейку формулы медианы / смещения в тот же столбец, из которого вы получаете медианное значение. ТАКЖЕ, смещение позволяет вам получить медиану из значения столбца за вычетом итоговой строки.

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

В этой статье описаны синтаксис формулы и использование функции МЕДИАНА в Microsoft Excel.

Описание

Возвращает медиану заданных чисел. Медиана — это число, которое является серединой множества чисел.

Синтаксис

Аргументы функции МЕДИАНА описаны ниже.

Число1, число2. Аргумент "число1" является обязательным, последующие числа необязательные. От 1 до 255 чисел, для которых требуется определить медиану.

Замечания

Если в наборе имеется ряду чисел, медиана вычисляет среднее значение двух чисел в середине. См. вторую формулу в примере.

Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.

Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.

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

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

Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

Пример

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

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

Дополнительные сведения о вычислении значений в сводной таблице

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

Доступные методы вычислений

Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.

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

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

В сводной таблице поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север, Юг, Восток и Запад. Значение на пересечении столбца Апрель и строки Север — это общая выручка от продаж, определенная по исходным данным, для которых столбец Месяц содержит значение Апрель, а столбец Регион — значение Север.

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

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

Сведение данных

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Значение, введенное в данное поле.

Значения в процентах от общей суммы всех значений или точек данных в отчете.

% от суммы по столбцу

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

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

С нарастающим итогом в поле

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.

Сортировка от минимального к максимальному

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

Сортировка от максимального к минимальному

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

Рассчитывает значения следующим образом:

((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Влияние типа источника данных на вычисления

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

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

Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые "вычисляемыми элементами", вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

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

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

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

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

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

Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Например, формула вычисляемого поля =Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2, а не умножает каждое отдельное значение продаж на 1,2 с последующим суммированием полученных величин.

Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области "Значения".

Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться 'Апрель Север' или 'Север Апрель'. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.

Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).

Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

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

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе "Север" за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле "Регион" вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

Читайте также: