To vprašanje se pojavlja enkrat na desetletje: ali lahko v pivot tabeli naredite mediano? Tradicionalno je bil odgovor ne. Spomnim se že leta 2000, ko sem najel Excelovega MVP Juana Pabla Gonzaleza, da je napisal čudovit makro, ki je ustvarjal poročila, ki so bila videti kot vrtilne tabele, vendar so imela Mediane.
Ko me je Alex na seminarju v Houston Power Excelu vprašal o ustvarjanju mediane, sem hitro rekel "Ne". Potem pa … Spraševal sem se, ali ima DAX srednjo funkcijo. Hitro iskanje v Googlu in da! Za mediano obstaja funkcija DAX.
Kaj je potrebno za uporabo DAX-a v vrtilni tabeli? Potrebujete različico Excela za Windows s sistemom Excel 2013 ali novejšo.
Tu je moj zelo preprost nabor podatkov, s katerim bom preizkusil, kako se izračunajo mediane vrtilne tabele. Lahko vidite, da mora biti mediana za Chicago 5000, mediana za Cleveland pa 17000. V primeru Chicaga obstaja pet vrednosti, tako da bo mediana tretja najvišja vrednost. Toda za celoten nabor podatkov obstaja 12 vrednosti. To pomeni, da je mediana nekje med 11000 in 13000. Excel v povprečju ti dve vrednosti vrne 12000.
Za začetek izberite eno celico v podatkih in pritisnite Ctrl + T, če želite podatke oblikovati kot tabelo.
Nato izberite Vstavi, vrtilna tabela. V pogovornem oknu Vstavi vrtilno tabelo izberite polje Dodaj te podatke v podatkovni model.
V polji vrtilne tabele izberite Regija in Okrožje. Vendar ne izberite prodaje. Namesto tega z desno miškino tipko kliknite naslov tabele in izberite Nov ukrep. "Measure" je modno ime za izračunano polje. Ukrepi so močnejši od izračunanih polj v običajnih vrtilnih tabelah.
V pogovornem oknu Določi ukrep izpolnite štiri spodaj prikazane vnose:
- Ime ukrepa: Mediana prodaje
- Formula
=MEDIAN((Sales)) - Oblika številke: številka
- Decimalna mesta: 0
Po izdelavi mere se doda na seznam polj, vendar morate izbrati vnos, da jo dodate v območje Vrednosti vrtilne tabele. Kot lahko vidite spodaj, vrtilna tabela pravilno izračunava mediane.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2197: Mediana v vrtilni tabeli.
Moram vam povedati, da sem nad tem zelo navdušen. ko sem bil v Houstonu na seminarju Power Excela in je Alex dvignil roko in rekel: "Hej, ali lahko kako naredim mediano v vrtilni tabeli?" Ne, mediane v vrtilni tabeli ne morete narediti. Spomnim se, da je pred 25 leti moj dobri prijatelj Juan Pablo Gonzales dejansko prinesel makro, ki je naredil ekvivalent mediano, ne da bi uporabil vrtilno tabelo - preprosto ni mogoče.
Ampak imel sem iskrico. Rekel sem: "Počakajte sekundo", odprl sem brskalnik in poiskal "DAX mediana" in, seveda, v DAX-u je funkcija MEDIAN, kar pomeni, da lahko to težavo rešimo.
Torej, če želite uporabljati DAX, morate biti v programu Excel 2013 ali Excel 2016 ali v programu Excel 2010 in imeti dodatek Power Pivot; vam ni treba imeti zavihka Power Pivot, mi moramo imeti le podatkovni model. Vredu? Torej bom izbral te podatke, naredil jih bom v tabeli s kombinacijo tipk Ctrl + T, ki jim bodo dali neizmišljeno ime "Tabela 4". V vašem primeru je to morda "Tabela 1". In vstavili bomo vrtilno tabelo, dodali te podatke v podatkovni model, kliknili V redu in na levi strani bomo izbrali Regional, ne pa tudi prodaje. Namesto tega želim ustvariti novo izračunano meritev. Torej pridem gor do mize in z desno miškino tipko kliknem in rečem: Dodaj ukrep. In ta ukrep se bo imenoval "Mediana prodaje", formula pa bo: = MEDIAN. Pritisnite zavihek tam in izberite Prodaja,zaključna oklepaja. To lahko izberem kot število z nič decimalnimi mesti, uporabim ločilo tisoč in kliknem V redu. In poglejmo, tukaj je dodano naše novo polje, če ga želimo dodati, ga označimo in pravi, da je srednja vrednost za Srednji zahod 12.000.
Zdaj pa preverimo to. Torej, tukaj, na celotnem Srednjem zahodu, je mediana celotnih podatkovnih nizov med 11.000 in 13.000. Torej je v povprečju 11 in 13, kar je točno tisto, kar bi mediana storila v običajnem Excelu. Zdaj pa dodajte okrožje in piše, da je srednja vrednost Chicaga 5000, mediana Clevelanda pa 17.000; Skupaj na srednjem zahodu in skupno 12.000. Vse to je pravilno. Kako super je to? Končno, mediana v vrtilni tabeli, zahvaljujoč izračunanemu polju ali meri, kot se imenuje, in podatkovnemu modelu.
Zdaj je v tej knjigi LIVe, 54 največjih nasvetov vseh časov veliko mojih najljubših nasvetov - nasvetov za moj seminar Power Excel v živo. Če želite prebrati več o knjigi, kliknite ta »I« v zgornjem desnem kotu.
Vredu. Zaključek: Ali lahko naredite mediano v vrtilni tabeli? Oh ne, da, da, lahko, zahvaljujoč podatkovnemu modelu. Ustvarite lahko mediano; Ctrl + T, če želite podatke pretvoriti v tabelo; Vstavi vrtilno tabelo; in potrdite to polje Dodaj te podatke v podatkovni model; z desno miškino tipko kliknite ime tabele in izberite novo mero in merilo bo = MEDIAN ((Prodaja)). To je super.
Hvala Alexu, ker se je pojavil na mojem seminarju in postavil to vprašanje, hvala tebi, da si se ustavil. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: pivot-table-median.xlsx
