Táto otázka sa objavuje raz za desať rokov: Môžete urobiť Medián v kontingenčnej tabuľke. Tradične bola odpoveď nie. Pamätám si ešte v roku 2000, keď som si najal Excel MVP Juana Pabla Gonzaleza, aby napísal úžasné makro, ktoré vytváralo správy, ktoré vyzerali ako kontingenčné tabuľky, ale mali mediány.
Keď sa teda Alex na mojom seminári Houston Power Excel pýtal na vytváranie mediánov, rýchlo som povedal „Nie“. Ale potom … Zaujímalo by ma, či má DAX strednú funkciu. Rýchle vyhľadávanie Google a Áno! Pre Median existuje funkcia DAX.
Čo je potrebné na použitie jazyka DAX v kontingenčnej tabuľke? Potrebujete verziu programu Excel pre Windows so systémom Excel 2013 alebo novším.
Tu je môj veľmi jednoduchý súbor údajov, ktorý použijem na otestovanie toho, ako sa počítajú mediány kontingenčných tabuliek. Môžete vidieť, že medián pre Chicago by mal byť 5 000 a medián pre Cleveland by mal byť 17 000. V prípade Chicaga je to päť hodnôt, takže medián bude 3. najvyššia hodnota. Ale pre celú množinu údajov existuje 12 hodnôt. To znamená, že stredná hodnota je niekde medzi 11000 a 13000. Excel vytvorí z týchto dvoch hodnôt priemerne hodnotu 12000.
Ak chcete začať, vyberte jednu bunku vo svojich údajoch a stlačte klávesy Ctrl + T a naformátujte údaje ako tabuľku.
Potom zvoľte Vložiť, Kontingenčná tabuľka. V dialógovom okne Vložiť kontingenčnú tabuľku začiarknite políčko Pridať tieto údaje do údajového modelu.
V poliach kontingenčnej tabuľky vyberte región a okres. Ale nevyberajte si Predaj. Namiesto toho kliknite pravým tlačidlom myši na nadpis tabuľky a vyberte možnosť Nové opatrenie. „Miesto“ je vymyslený názov pre vypočítané pole. Miery sú výkonnejšie ako vypočítané polia v bežných kontingenčných tabuľkách.
V dialógovom okne Definovanie merania vyplňte štyri položky zobrazené nižšie:
- Názov opatrenia: Medián predaja
- Vzorec
=MEDIAN((Sales)) - Formát čísla: Číslo
- Desatinné miesta: 0
Po vytvorení miery sa pridá do zoznamu polí, ale musíte zvoliť položku a pridať ju do oblasti Hodnoty kontingenčnej tabuľky. Ako vidíte nižšie, kontingenčná tabuľka správne počíta mediány.
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2197: Medián v kontingenčnej tabuľke.
Musím vám povedať, že som z toho nesmierne nadšený. keď som bol v Houstone na seminári Power Excel a Alex zdvihol ruku a povedal: „Hej, existuje nejaký spôsob, ako urobiť medián v kontingenčnej tabuľke?“ Nie, v kontingenčnej tabuľke nemôžete urobiť medián. Pamätám si, že pred 25 rokmi, môj dobrý priateľ, Juan Pablo Gonzales, priniesol makro na ekvivalent mediánu, nepoužíval kontingenčnú tabuľku - to jednoducho nejde.
Mal som však iskru. Povedal som: „Počkajte chvíľu,“ a otvorím prehľadávač, vyhľadám výraz „DAX median“ a v DAX určite existuje funkcia MEDIAN, čo znamená, že môžeme tento problém vyriešiť.
Dobre, takže ak chcete používať jazyk DAX, musíte byť v programe Excel 2013 alebo Excel 2016 alebo Excel 2010 a mať doplnok Power Pivot; nemusíte mať kartu Power Pivot, musíme mať iba dátový model. Dobre? Idem teda zvoliť tieto údaje, urobím z nich tabuľku pomocou Ctrl + T a dajú im nepredstaviteľný názov „Tabuľka 4“. Vo vašom prípade to môže byť „tabuľka 1“. A vložíme kontingenčnú tabuľku, pridáme tieto údaje do dátového modelu, klikneme na OK a na ľavej strane vyberieme možnosť Regionálne, ale nie Predaj. Namiesto toho chcem vytvoriť nové vypočítané meranie. Takže prídem sem k stolu a kliknem pravým tlačidlom myši a poviem: Pridajte mieru. A toto opatrenie sa bude nazývať „Medián predaja“ a vzorec bude: = MEDIAN. Stlačte kartu a vyberte možnosť Predaj,zatváracia zátvorka. Môžem to zvoliť ako číslo s nulovými desatinnými miestami, použiť oddeľovač tisíc a kliknúť na OK. A uvidíme, naše nové pole je tu pridané, musíme ho zaškrtnúť, aby sme ho pridali, a hovorí, že medián pre Midwest je 12 000.
Teraz to skontrolujme. Takže tu, na celom Stredozápade, je stredná hodnota celých súborov údajov medzi 11 000 a 13 000. Priemer je teda 11 a 13, čo je presne to, čo by urobil medián v bežnom programe Excel. Teraz pridajme okres a je v ňom medián 5 000 Chicaga, medián Clevelandu 17 000; Celkom na stredozápade a spolu 12 000. Všetko je správne. Aké úžasné je to? Nakoniec medián v kontingenčnej tabuľke vďaka vypočítanému poľu alebo meraniu, ako sa nazýva, a dátovému modelu.
Teraz veľa mojich obľúbených tipov - tipov na môj živý seminár Power Excel - v tejto knihe LIVe, 54 najväčších tipov všetkých čias. Kliknutím na toto „I“ v pravom hornom rohu si prečítate viac o knihe.
Dobre. Zbalenie: Môžete urobiť medián v kontingenčnej tabuľke? Ach nie, áno, áno, môžete, vďaka dátovému modelu. Môžete vytvoriť medián; Ctrl + T urobte z vašich údajov tabuľku; Vložte kontingenčnú tabuľku; a začiarknite toto políčko, Pridať tieto údaje do dátového modelu; kliknite pravým tlačidlom myši na názov tabuľky a vyberte novú mieru, ktorá bude mať hodnotu = MEDIAN ((Sales)). Je to úžasné.
Ďakujem Alexovi, že sa objavil na mojom seminári a položil túto otázku, ďakujem ti, že si sa zastavil. Uvidíme sa nabudúce na ďalšom netcaste od.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: pivot-table-median.xlsx
