Це питання виникає один раз на десятиліття: чи можете ви зробити медіану у зведеній таблиці? Традиційно, відповідь була ні. Я пам’ятаю ще у 2000 році, коли найняв MVP Excel Хуана Пабло Гонсалеса для написання чудового макросу, який створював звіти, схожі на зведені таблиці, але маючи медіани.
Тож коли Алекс на моєму семінарі в Х'юстоні Power Excel запитав про створення медіан, я швидко сказав "Ні". Але потім … Я задався питанням, чи DAX виконував медіанну функцію. Швидкий пошук в Google і так! Існує функція DAX для медіани.
Що потрібно для використання DAX у зведеній таблиці? Вам потрібна версія Excel для Windows під керуванням Excel 2013 або новішої версії.
Ось мій дуже простий набір даних, який я буду використовувати, щоб перевірити, як обчислюються медіани зведеної таблиці. Ви можете бачити, що медіана для Чикаго повинна бути 5000, а медіана для Клівленда повинна бути 17000. У випадку Чикаго існує п'ять значень, тому медіана буде третьою за величиною. Але для всього набору даних існує 12 значень. Це означає, що медіана становить десь між 11000 і 13000. Excel усереднює ці два значення, щоб повернути 12000.
Для початку виберіть одну клітинку у ваших даних та натисніть Ctrl + T, щоб відформатувати дані як таблицю.
Потім виберіть Вставити, зведену таблицю. У діалоговому вікні Вставити зведену таблицю встановіть прапорець Додати ці дані до моделі даних.
У полях зведеної таблиці виберіть Регіон та Район. Але не вибирайте Продажі. Натомість клацніть правою кнопкою миші заголовок таблиці та оберіть Новий захід. "Виміряти" - це вигадна назва для обчисленого поля. Міри ефективніші, ніж обчислювані поля в звичайних зведених таблицях.
У діалоговому вікні Визначити міру заповніть чотири записи, показані нижче:
- Назва міри: Медіана продажів
- Формула
=MEDIAN((Sales)) - Формат числа: Число
- Десяткові місця: 0
Після створення міри вона додається до списку полів, але потрібно вибрати запис, щоб додати її до області Значення зведеної таблиці. Як ви можете бачити нижче, зведена таблиця правильно обчислює медіани.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2197: медіана у зведеній таблиці.
Я повинен вам сказати, я надзвичайно схвильований цим. коли я був у Х'юстоні, проводячи там семінар Power Excel, і Алекс підняв руку і сказав: "Гей, чи є спосіб зробити медіану в зведеній таблиці?" Ні, ви не можете зробити медіану в зведеній таблиці. Пам’ятаю, 25 років тому мій добрий друг Хуан Пабло Гонсалес насправді приніс макрос, який робить еквівалент медіани, не використовуючи зведену таблицю - це просто неможливо.
Але у мене була іскра. Я сказав: "Зачекайте секунду", і я відкриваю браузер і шукаю "DAX медіана", і, звичайно, у 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. Все це правильно. Наскільки це чудово? Нарешті, медіана в зведеній таблиці завдяки обчислюваному полю або мірі, як її ще називають, та моделі даних.
Зараз у цій книзі LIVe, 54 найкращі поради всіх часів, є багато моїх улюблених порад - підказок мого живого семінару Power Excel. Клацніть на "Я" у верхньому правому куті, щоб прочитати більше про книгу.
Гаразд Підсумок: Чи можете ви зробити медіану в зведеній таблиці? О ні, так, так ви можете, завдяки моделі даних. Ви можете створити медіану; Ctrl + T, щоб зробити ваші дані таблицею; Вставити зведену таблицю; і встановіть цей прапорець, Додати ці дані до моделі даних; клацніть правою кнопкою миші назву таблиці та оберіть нову міру, і міра буде = MEDIAN ((Продажі)). Це приголомшливо.
Дякую Алексу за те, що він з’явився на моєму семінарі та задав це запитання, дякую тобі, що ти завітав. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: pivot-table-median.xlsx
