Tato otázka se objevuje jednou za deset let: Můžete udělat medián v kontingenční tabulce. Tradičně byla odpověď ne. Vzpomínám si už v roce 2000, když jsem najal Excel MVP Juana Pabla Gonzaleze, aby napsal úžasné makro, které vytvářelo sestavy, které vypadaly jako kontingenční tabulky, ale měly Medians.
Když se tedy Alex na mém semináři v Houstonu Power Excel zeptal na vytvoření mediánů, rychle jsem řekl „Ne“. Ale pak … napadlo mě, jestli má DAX střední funkci. Rychlé vyhledávání Google a Ano! Pro Median existuje funkce DAX.
Co je potřeba k použití jazyka DAX v kontingenční tabulce? Potřebujete verzi systému Windows pro Windows se systémem Excel 2013 nebo novější.
Tady je můj velmi jednoduchý datový soubor, který použiji k otestování výpočtu mediánů kontingenční tabulky. Můžete vidět, že medián pro Chicago by měl být 5 000 a medián pro Cleveland by měl být 17 000. V případě Chicaga existuje pět hodnot, takže medián bude 3. nejvyšší hodnotou. Ale pro celý soubor dat existuje 12 hodnot. To znamená, že medián je někde mezi 11000 a 13000. Excel zprůměruje tyto dvě hodnoty a vrátí 12000.
Chcete-li začít, vyberte jednu buňku ve svých datech a stisknutím Ctrl + T naformátujte data jako tabulku.
Poté zvolte Vložit, Kontingenční tabulka. V dialogu Vložit kontingenční tabulku zaškrtněte políčko Přidat tato data do datového modelu.
V polích kontingenční tabulky vyberte oblast a okres. Ale nevybírejte Prodej. Místo toho klikněte pravým tlačítkem na záhlaví tabulky a zvolte Nové měřítko. „Míra“ je ozdobný název pro vypočítané pole. Míry jsou výkonnější než vypočítaná pole v běžných kontingenčních tabulkách.
V dialogovém okně Definovat měření vyplňte čtyři níže uvedené položky:
- Název opatření: Medián prodeje
- Vzorec
=MEDIAN((Sales)) - Formát čísla: Číslo
- Desetinná místa: 0
Po vytvoření míry se přidá do seznamu polí, ale musíte vybrat položku a přidat ji do oblasti Hodnoty kontingenční tabulky. Jak vidíte níže, kontingenční tabulka správně počítá mediány.
Sledovat video
Přepis videa
Naučte se Excel z Podcastu, epizoda 2197: Medián v kontingenční tabulce.
Musím vám to říct, jsem z toho nesmírně nadšený. když jsem byl v Houstonu na semináři Power Excel a Alex zvedl ruku a řekl: „Hej, existuje nějaký způsob, jak udělat medián v kontingenční tabulce?“ Ne, v kontingenční tabulce nemůžete udělat medián. Vzpomínám si, že před 25 lety můj dobrý přítel Juan Pablo Gonzales přinesl makro, které dělá ekvivalent mediánu, nepoužívá kontingenční tabulku - to prostě není možné.
Ale měl jsem jiskru. Řekl jsem: „Počkej chvilku,“ a otevřu prohlížeč a vyhledám „medián DAX“. V DAX je jistě funkce MEDIAN, což znamená, že můžeme tento problém vyřešit.
Dobře, takže abyste mohli používat DAX, musíte být v aplikaci Excel 2013 nebo Excel 2016 nebo v aplikaci Excel 2010 a mít doplněk Power Pivot; nemusíte mít kartu Power Pivot, musíme mít pouze datový model. V pořádku? Takže si vyberu tato data, udělám z nich tabulku s Ctrl + T a dají jí nepředstavitelný název „Tabulka 4“. Ve vašem případě to může být „Tabulka 1“. Vložíme kontingenční tabulku, přidáme tato data do datového modelu, klikneme na OK a na levé straně vybereme Regionální, ale ne Prodej. Místo toho chci vytvořit nové vypočítané měření. Přijdu tedy sem ke stolu a kliknu pravým tlačítkem a řeknu: Přidat míru. A toto opatření se bude jmenovat „Medián prodeje“ a vzorec bude: = STŘEDNÍ. Stiskněte kartu a zvolte Prodej,uzavírací závorka. Mohu to vybrat jako číslo s nulovými desetinnými místy, použít oddělovač tisíců a kliknout na OK. A podívejme se, že je zde přidáno naše nové pole, abychom jej mohli přidat, musíme to zaškrtnout a medián pro Midwest je 12 000.
Pojďme to zkontrolovat. Takže tady, na celém Středozápadě, je medián celých datových sad mezi 11 000 a 13 000. Je to v průměru 11 a 13, což je přesně to, co by udělal medián v běžném Excelu. Nyní přidejme okres a říkáme, že medián 5 000 Chicaga, medián Clevelandu je 17 000; Celkem na Středozápadě a celkem 12 000. To vše je správné. Jak úžasné je to? Nakonec medián v kontingenční tabulce díky vypočítanému poli nebo míře, jak se říká, a datovému modelu.
Nyní mnoho z mých oblíbených tipů - tipy na můj živý seminář Power Excel - v této knize LIVe, 54 největších tipů všech dob. Kliknutím na „I“ v pravém horním rohu si přečtete více o knize.
V pořádku. Shrnutí: Můžete udělat medián v kontingenční tabulce? Ach ne, ano, ano, můžete, díky datovému modelu. Můžete vytvořit medián; Ctrl + T vytvoří vaše data do tabulky; Vložit kontingenční tabulku; a zaškrtněte toto políčko, Přidat tato data do datového modelu; klikněte pravým tlačítkem na název tabulky a vyberte novou míru a míra bude = MEDIAN ((Prodej)). Je to úžasné.
Děkuji Alexovi, že se objevil na mém semináři a položil tu otázku, díky vám, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: pivot-table-median.xlsx
