Този въпрос възниква веднъж на всеки десетилетие: Можете ли да направите медиана в обобщена таблица. Традиционно отговорът беше Не. Спомням си още през 2000 г., когато наех MVP на Excel Хуан Пабло Гонсалес да напише страхотен макрос, който създаваше отчети, които изглеждаха като обобщени таблици, но имаха медиани.
И така, когато Алекс в моя семинар в Хюстън Power Excel попита за създаване на медиани, аз бързах да кажа „Не“. Но тогава … Чудех се дали DAX има медианна функция. Бързо търсене в Google и да! Има функция DAX за медиана.
Какво е необходимо, за да се използва DAX в обобщена таблица? Нуждаете се от версията на Windows на Excel с Excel 2013 или по-нова.
Ето моя много прост набор от данни, който ще използвам, за да тествам как се изчисляват медианите на обобщената таблица. Можете да видите, че медианата за Чикаго трябва да бъде 5000, а медианата за Кливланд трябва да бъде 17000. В случая с Чикаго има пет стойности, така че медианата ще бъде третата най-висока стойност. Но за целия набор от данни има 12 стойности. Това означава, че медианата е някъде между 11000 и 13000. Excel усреднява тези две стойности, за да върне 12000.
За да започнете, изберете една клетка във вашите данни и натиснете Ctrl + T, за да форматирате данните като таблица.
След това изберете Insert, Pivot Table. В диалоговия прозорец Вмъкване на обобщена таблица изберете полето за Добавяне на тези данни към модела на данни.
В полетата на обобщената таблица изберете Регион и Област. Но не избирайте Продажби. Вместо това щракнете с десния бутон върху заглавието на таблицата и изберете Нова мярка. "Мярка" е изискано име за изчислено поле. Мерките са по-мощни от изчислените полета в обикновените обобщени таблици.
В диалоговия прозорец Дефиниране на мярка попълнете четирите елемента, показани по-долу:
- Име на мярката: Медиана на продажбите
- Формула
=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 и ще изберем Регионално отляво, но не и Продажби. Вместо това искам да създам ново изчислено измерване. Затова се качвам тук до масата и щраквам с десния бутон и казвам „Добавяне на мярка“. И тази мярка ще се нарича "Медиана на продажбите", а формулата ще бъде: = MEDIAN. Натиснете раздела там и изберете Продажби,затваряща скоба. Мога да избера това като число с нула десетични знаци, да използвам разделител на хиляди и да натисна OK. И да видим, нашето ново поле е добавено тук, трябва да го поставим, за да го добавим и казва, че медианата за Средния Запад е 12 000.
Сега нека проверим това. И така, тук, на целия Среден Запад, медианата на всички данни е между 11 000 и 13 000. Така че е средно 11 и 13, което е точно това, което медианата би направила в обикновения Excel. Сега нека добавим окръг в и той казва, че медианата на 5000 от Чикаго, медианата на Кливланд е 17 000; Общо за Средния Запад и общо 12 000. Всичко това е правилно. Колко страхотно е това? И накрая, медиана в обобщена таблица, благодарение на изчислено поле или мярка, както се нарича, и Модел на данни.
Сега, много от любимите ми съвети - съветите за моя семинар на Power Excel - в тази книга LIVe, 54-те най-добри съвета за всички времена. Щракнете върху това „I“ в горния десен ъгъл, за да прочетете повече за книгата.
Добре. Обобщение: Можете ли да направите медиана в обобщена таблица? О, не, да, да, можете, благодарение на модела за данни. Можете да създадете медиана; Ctrl + T, за да превърнете данните си в таблица; Вмъкване на обобщена таблица; и поставете отметка в това квадратче, Добавете тези данни към модела на данни; щракнете с десния бутон върху името на таблицата и изберете нова мярка и мярката ще бъде = MEDIAN ((Продажби)). Това е страхотно.
Благодаря на Алекс, че се появи в моя семинар и зададе този въпрос, благодаря на това, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: pivot-table-median.xlsx
