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

Обновлено: 20.11.2024

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

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

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

Изменить сводную функцию в сводной таблице

Если вы нажмете «Дополнительные параметры…», вы сможете выбрать другие функции, такие как стандартное отклонение и числовые значения. Но что, если вам нужно что-то другое, например, медиана? Вот когда в игру вступает Power Pivot.

Power Pivot — это встроенная функция Excel (для Excel 2013 и более поздних версий), которая позволяет значительно расширить возможности обычных сводных таблиц. Например, с помощью Power Pivot вы можете использовать информацию из нескольких таблиц, не объединяя ее в одну таблицу. Кроме того, вы можете использовать множество сводных функций, недоступных в обычных сводных таблицах (например, медиана). Примечание. Функция медианы доступна только в Power Pivot для Excel 2016.

Посмотрите этот 5-минутный видеоролик ниже, чтобы получить дополнительную информацию о Power Pivot

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

  • Номер заказа: идентификатор заказа.
  • Центр распределения: место, откуда отправляется заказ.
  • Количество: количество отправленных товаров в заказе.
  • Дата заказа: дата размещения заказа покупателем.
  • Дата доставки: дата, когда заказ был доставлен покупателю.

Загрузить данные

Загрузите необработанные данные по этой ссылке.

Как добавить медиану в сводную таблицу

Процесс вычисления медианы (или любой другой функции) в сводных таблицах выглядит следующим образом:

Создание показателя в Power Pivot

  1. Перейдите на вкладку Power Pivot → нажмите "Меры" → "Новая мера".
  2. Укажите название меры (например, "Медиана количества").
  3. Введите формулу показателя. Например, чтобы вычислить медиану столбца КОЛИЧЕСТВО, введите формулу =МЕДИАНА([КОЛИЧЕСТВО]). Обратите внимание на использование квадратных скобок для обозначения столбцов.
    Создание показателя с медианой
    1. Вы можете перетащить нужные поля и меру в сводную таблицу.

    Например, на изображении ниже показана сводная таблица со столбцом Центр распределения в области строк и мерой Медианное количество в области значений.

    Добавить медиану в сводную таблицу

    Вычислить медиану из операций по строкам

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

    Есть два способа сделать это:

    1. Создайте в таблице столбец "Время выполнения", а затем рассчитайте медиану столбца "Время выполнения".
    2. Используйте функцию MEDIANX, чтобы рассчитать время выполнения заказа и медиану в одной формуле.

    Я объясню, как использовать МЕДИАНКС:

    Все функции Power Pivot, оканчивающиеся на X, являются итераторами. Функции итератора могут выполнять операцию для каждой строки таблицы.

    В общем, синтаксис итератора таков:

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

    Поэтому синтаксис будет таким:

    Вызов

    Вот вам сложная задача:

    Допустим, есть цель, чтобы время выполнения заказа (дата доставки – дата заказа) не превышало 15 дней. Как бы вы рассчитали процент заказов со сроком выполнения более 15 дней для каждого из распределительных центров?

    Сводная таблица должна выглядеть так:

    Сводная таблица с долей просроченных заказов

    Если мы выберем распределительный центр Б, 94,8 % заказов имели время выполнения более 15 дней.

    СОВЕТ. Прочтите об этих функциях Power Pivot: СЧЕТЧИК, СРЗНАЧ, ЕСЛИ, СЧЕТЧИК, ФИЛЬТР, ПУСТО. Вам необязательно использовать их все, чтобы ответить на вопрос.

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

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

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

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

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

    Альтернатива сводной таблицы Excel для расчета медианы

    Таблица "Средний цикл продаж (дней)" была создана с помощью сводной таблицы. Таблица «Медиана цикла продаж (дни)» была создана следующим образом:

    1) Создайте столбец с шестью возможными вариантами «сотрудников»: от 1 до 5, от 6 до 10, от 11 до 15 и т. д.

    2) В ячейке справа от значения "от 1 до 5" введите следующее: =МЕДИАНА(ЕСЛИ($A$2:$A$20=D2, $B$2:$B$20))

    3) Когда вы закрыли последнюю скобку и все еще находитесь в ячейке, нажмите Control+Shift+Enter (на ПК) или Command+Shift+Enter (на Mac), чтобы заполнить медиану. Вот как вы сообщаете Excel, что хотите создать массив.

    Примечание. После этого вы увидите фигурные скобки < > вокруг вашей формулы. Если вы сами введете фигурные скобки или скопируете/вставите приведенную выше формулу в ячейку, Excel не поймет, что они означают.

    4) Скопируйте медианное значение из первой ячейки (в примере G2) в остальные пустые ячейки таблицы (в примере G3–G7).

    Вот что означают вводимые формулы:

    Когда использовать медиану для анализа данных

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

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

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

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

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

    Однако среднее значение может быть не лучшим показателем для анализа размера каждой продажи в этом году. Если организация продавала продукты по цене от 10 до 25 000 долларов, то средний размер продажи мог быть искажен самой низкой и самой высокой ценой. Вместо этого медиана дала бы организации представление о том, были ли обычно продажи выше или ниже по цене. Видите разницу?

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

    Этот вопрос возникает раз в десятилетие: можно ли вычислить медиану в сводной таблице. Традиционно ответом было «Нет». Я помню, как в 2000 году я нанял MVP Excel Хуана Пабло Гонсалеса для написания замечательного макроса, который создавал отчеты, похожие на сводные таблицы, но с медианами.

    Поэтому, когда Алекс на моем семинаре по Power Excel в Хьюстоне спросил о создании медиан, я сразу же ответил "Нет". Но потом. Мне было интересно, есть ли у DAX медианная функция. Быстрый поиск в Google и да! Для медианы есть функция DAX.

    Что нужно для использования DAX в сводной таблице? Вам потребуется версия Excel для Windows, работающая под управлением Excel 2013 или более поздней версии.

    Вот мой очень простой набор данных, который я буду использовать для проверки того, как рассчитываются медианы сводной таблицы. Вы можете видеть, что медиана для Чикаго должна быть 5000, а медиана для Кливленда должна быть 17000. В случае Чикаго есть пять значений, поэтому медиана будет 3-м по величине значением. Но для всего набора данных есть 12 значений. Это означает, что медиана находится где-то между 11 000 и 13 000. Excel усредняет эти два значения и возвращает 12 000.

    Рисунок 1

    Для начала выберите одну ячейку в ваших данных и нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.

    Затем выберите «Вставка», «Сводная таблица». В диалоговом окне "Вставка сводной таблицы" установите флажок "Добавить эти данные в модель данных".

    Рисунок 2

    В полях сводной таблицы выберите «Регион» и «Район». Но не выбирайте Продажи. Вместо этого щелкните правой кнопкой мыши заголовок таблицы и выберите «Новая мера». «Мера» — это причудливое название для вычисляемого поля. Меры более эффективны, чем вычисляемые поля в обычных сводных таблицах.

    Рисунок 3

    В диалоговом окне "Определить меру" заполните четыре поля, показанные ниже:

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

    Рисунок 5

    Посмотреть видео

    Расшифровка видео

    Изучите Excel из подкаста MrExcel, выпуск 2197: медиана в сводной таблице.

    Должен сказать вам, я очень взволнован этим. когда я был в Хьюстоне, проводил там семинар по Power Excel, и Алекс поднял руку и сказал: «Эй, а можно как-нибудь вычислить медиану в сводной таблице?» Нет, вы не можете сделать медиану в сводной таблице. Я помню, как 25 лет назад мой хороший друг Хуан Пабло Гонсалес принес макрос для вычисления медианы без использования сводной таблицы — это просто невозможно.

    Но у меня была искра. Я сказал: "Подождите секунду", и я открываю браузер и ищу "медиану DAX", и, конечно же, в DAX есть функция МЕДИАНА, что означает, что мы можем решить эту проблему.

    Хорошо, чтобы использовать DAX, вы должны быть в Excel 2013, Excel 2016 или Excel 2010 и иметь надстройку Power Pivot; вам не обязательно иметь вкладку Power Pivot, нам просто нужна модель данных. Хорошо? Итак, я собираюсь выбрать эти данные, я собираюсь превратить их в таблицу с помощью Ctrl+T, и они дадут ей невообразимое имя «Таблица 4». В вашем случае это может быть «Таблица 1».И мы вставим сводную таблицу, добавим эти данные в модель данных, нажмем «ОК», и мы собираемся выбрать «Региональный» слева, но не «Продажи». Вместо этого я хочу создать новое расчетное измерение. Итак, я подхожу к столу, щелкаю правой кнопкой мыши и говорю: «Добавить меру». И этот показатель будет называться «Медиана продаж», а формула будет выглядеть так: =МЕДИАНА. Нажмите там вкладку и выберите «Продажи», закрыв скобки. Я могу выбрать это как число без десятичных знаков, использовать разделитель тысяч и нажать OK. И давайте посмотрим, наше новое поле добавлено сюда, мы должны отметить его, чтобы добавить, и оно говорит, что медиана для Среднего Запада составляет 12 000.

    Теперь давайте проверим это. Итак, здесь весь Средний Запад, медиана всех наборов данных между 11 000 и 13 000. Таким образом, это усреднение 11 и 13, что в точности соответствует медиане в обычном Excel. Теперь давайте добавим округ, и получится, что медиана Чикаго — 5000, медиана Кливленда — 17000; Всего на Среднем Западе и в целом 12 000 человек. Все это правильно. Насколько это круто? Наконец, медиана в сводной таблице благодаря вычисляемому полю, или мере, как его называют, и модели данных.

    Теперь многие из моих любимых советов — советы для моего живого семинара по Power Excel — в этой книге MrExcel LIVe, 54 величайших совета всех времен. Нажмите на "I" в правом верхнем углу, чтобы узнать больше о книге.

    Хорошо. Подведение итогов: можете ли вы сделать медиану в сводной таблице? О нет, да, да, вы можете, благодаря модели данных. Вы можете создать медиану; Ctrl+T, чтобы преобразовать ваши данные в таблицу; Вставить сводную таблицу; и установите флажок Добавить эти данные в модель данных; щелкните правой кнопкой мыши имя таблицы и выберите новую меру, и мерой будет = МЕДИАНА ([Продажи]). Это потрясающе.

    Спасибо Алексу за то, что он появился на моем семинаре и задал этот вопрос, спасибо, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от MrExcel.

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

    Однако средние значения не всегда являются лучшим способом анализа данных. В нашем примере показаны пожертвования, собранные региональной благотворительной организацией. Большинство пожертвований небольшие, несколько более значительных, а одно или два очень крупных. Среднее пожертвование за год составляет 1380,50 долларов США, но это не точно показывает нам, сколько жертвует большинство наших жертвователей. Вычисление медианы показывает, что большая часть наших пожертвований составляет около 50 долларов США, причем половина из них больше, а половина меньше. Это число более реалистично отражает наши модели пожертвований доноров.

    Изображения в этой статье были сделаны с помощью Excel 2013 в ОС Windows 7. Чтобы использовать приведенный ниже пример, загрузите файл Рассчитать медиану в сводных таблицах.xlsx

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

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

    Использовать функцию массива

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

    <р>1. Сначала создайте сводную таблицу с данными, организованными так, как вы хотите, за исключением вычисления среднего значения вместо медианы в области значений.

    <р>2. Затем выберите ячейки сводной таблицы (включая заголовки) и нажмите Ctrl+C, чтобы скопировать таблицу в буфер обмена.

    <р>3. Щелкните правой кнопкой мыши открытую ячейку и выберите "Специальная вставка" > "Вставить значения", чтобы вставить копию заголовков и значений таблицы на лист.

    <р>4. Добавьте в новую таблицу столбец для расчета медианы.

    <р>5. Для каждой метки строки объедините функцию ЕСЛИ с функцией МЕДИАНА, чтобы получить только те данные, которые соответствуют полям в сводной таблице. Введите формулу в столбце Медиана. В нашем примере формула будет выглядеть так:

    =MEDIAN(IF($H$2:$H$65=J19, $C$2:$C$65))
    Чтобы разбить формулу:

      • =MEDIAN(указывает Excel вычислить медиану чисел, возвращаемых оператором IF
      • IF(($H$2:$H$65=J19, указывает Excel возвращать значения сумм доноров только в том случае, если значение в столбце H — Состояние — совпадает со значением в столбце J (наша метка строки состояния сводной таблицы). Используйте символ $ для указания абсолютных значений ячеек.
      • $C$2:$C$65)) предписывает Excel вернуть значения в столбце C, которые соответствуют указанным выше критериям для функции МЕДИАНА. Используйте символ $, чтобы указать абсолютные значения ячеек.
      • Нажмите Ctrl+Shift+EnterВажно! Не нажимайте Enter, чтобы завершить формулу. Вы должны одновременно удерживать Ctrl+Shift+Enter, чтобы завершить формулу и сообщить Excel, что это формула массива. Excel добавит фигурные скобки вокруг формулы, если это сделано правильно:

    <р>6. Теперь вы можете перетащить формулу в другие ячейки «фальшивой» сводной таблицы и быстро увидеть медианное пожертвование для всех штатов, охваченных благотворительной организацией.

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

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