Этот вопрос возникает раз в десять лет: можете ли вы вычислить медианное значение в сводной таблице. Традиционно ответ был отрицательным. Я помню, как в 2000 году я нанял Excel MVP Хуана Пабло Гонсалеса для написания потрясающего макроса, который создавал отчеты, похожие на сводные таблицы, но с медианами.
Поэтому, когда Алекс на моем семинаре по Power Excel в Хьюстоне спросил о создании медиан, я поспешил сказать «Нет». Но потом… я подумал, есть ли у DAX медианная функция. Быстрый поиск в Google и да! Для медианы есть функция DAX.
Что нужно для использования DAX в сводной таблице? Вам потребуется версия Excel для Windows с Excel 2013 или новее.
Вот мой очень простой набор данных, который я буду использовать, чтобы проверить, как рассчитываются медианы сводной таблицы. Вы можете видеть, что медиана для Чикаго должна быть 5000, а медиана для Кливленда должна быть 17000. В случае Чикаго существует пять значений, поэтому медиана будет 3-м по величине значением. Но для всего набора данных есть 12 значений. Это означает, что медиана находится где-то между 11000 и 13000. Excel усредняет эти два значения и возвращает 12000.
Для начала выделите одну ячейку в данных и нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.
Затем выберите «Вставка», «Сводная таблица». В диалоговом окне «Вставить сводную таблицу» выберите поле «Добавить эти данные в модель данных».
В полях сводной таблицы выберите регион и район. Но не выбирайте Продажи. Вместо этого щелкните правой кнопкой мыши заголовок таблицы и выберите «Новая мера». «Мера» - это необычное название вычисляемого поля. Меры более эффективны, чем вычисляемые поля в обычных сводных таблицах.
В диалоговом окне «Определение меры» заполните четыре записи, показанные ниже:
- Название меры: Медиана продаж
- Формула
=MEDIAN((Sales)) - Формат числа: Число
- Десятичные знаки: 0
После создания меры она добавляется в список полей, но вы должны выбрать запись, чтобы добавить ее в область значений сводной таблицы. Как вы можете видеть ниже, сводная таблица правильно вычисляет медианы.
Смотреть видео
Стенограмма видео
Изучите Excel из подкаста, эпизод 2197: медиана в сводной таблице.
Должен сказать, я очень взволнован по этому поводу. когда я был в Хьюстоне, проводя там семинар по Power Excel, и Алекс поднял руку и сказал: «Эй, а есть ли способ сделать медианное значение в сводной таблице?» Нет, вы не можете вычислить медианное значение в сводной таблице. Я помню, 25 лет назад мой хороший друг Хуан Пабло Гонсалес на самом деле принес макрос для выполнения эквивалента медианы, не используя сводную таблицу - это просто невозможно.
Но у меня была искра. Я сказал: «Подождите секунду», открываю браузер и ищу «DAX median», и, конечно же, в DAX есть функция MEDIAN, а это значит, что мы можем решить эту проблему.
Итак, чтобы использовать DAX, вы должны быть в Excel 2013, Excel 2016 или Excel 2010 и иметь надстройку Power Pivot; вам не обязательно иметь вкладку Power Pivot, нам просто нужна модель данных. Хорошо? Итак, я собираюсь выбрать эти данные, я собираюсь превратить их в таблицу с помощью Ctrl + T, и они дадут ей простое название «Таблица 4». В вашем случае это может быть «Таблица 1». И мы вставим сводную таблицу, добавим эти данные в модель данных, нажмем «ОК» и выберем «Региональные» слева, но не «Продажи». Вместо этого я хочу создать новое вычисленное измерение. Итак, я подхожу к столу, щелкаю правой кнопкой мыши и говорю «Добавить меру». И этот показатель будет называться «Медиана продаж», а формула будет иметь вид: = МЕДИАНА. Нажмите там вкладку и выберите Продажи,закрывающая скобка. Я могу выбрать это число с нулевым десятичным знаком, использовать разделитель тысяч и нажать OK. И, давайте посмотрим, здесь добавлено наше новое поле, мы должны отметить его, чтобы добавить, и оно говорит, что медиана для Среднего Запада составляет 12000.
А теперь проверим. Итак, здесь, весь Средний Запад, медиана всех наборов данных от 11000 до 13000. Таким образом, это среднее значение 11 и 13, что в точности соответствует среднему значению в обычном Excel. Теперь давайте добавим округ, и он говорит, что среднее значение 5000 в Чикаго, среднее значение для Кливленда - 17000; Всего на Среднем Западе и в целом 12000. Все это правильно. Как это круто? Наконец, медиана в сводной таблице благодаря вычисляемому полю или мере, как ее называют, и модели данных.
А теперь многие из моих любимых советов - советы для моего семинара по Power Excel - в этой книге LIVe, 54 величайших совета всех времен. Щелкните "I" в правом верхнем углу, чтобы узнать больше о книге.
Хорошо. Подведение итогов: можете ли вы вычислить медианное значение в сводной таблице? О нет, да, да, вы можете, благодаря модели данных. Вы можете создать медиану; Ctrl + T, чтобы превратить ваши данные в таблицу; Вставить сводную таблицу; и установите этот флажок, Добавить эти данные в модель данных; щелкните правой кнопкой мыши имя таблицы и выберите новую меру. Мера будет = МЕДИАНА ((Продажи)). Это круто.
Спасибо Алексу за то, что он появился на моем семинаре и задал этот вопрос, спасибо, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: pivot-table-median.xlsx
