See küsimus kerkib esile iga kümne aasta tagant: kas saate meediumit teha pöördtabelis. Traditsiooniliselt oli vastus Ei. Mäletan juba 2000. aastal, kui palkasin Exceli MVP Juan Pablo Gonzalezi kirjutama ägeda makro, mis lõi aruandeid, mis nägid välja nagu pöördtabelid, kuid millel olid mediaanid.
Nii et kui Alex oma Houstoni Power Exceli seminaril mediaanide loomise kohta küsis, ütlesin kiiresti "ei". Aga siis … mõtlesin, kas DAX-il on mediaanfunktsioon. Kiire Google'i otsing ja Jah! Mediaani jaoks on olemas DAX-funktsioon.
Mida on vaja DAX-i kasutamiseks pöördtabelis? Teil on vaja Exceli Windowsi versiooni, milles töötab Excel 2013 või uuem.
Siin on minu väga lihtne andmekogum, mida kasutan pöördtabeli mediaanide arvutamise testimiseks. Näete, et Chicago mediaan peaks olema 5000 ja Clevelandi mediaan 17000. Chicago puhul on viis väärtust, nii et mediaan on suuruselt 3. väärtus. Kuid kogu andmekogumi jaoks on 12 väärtust. See tähendab, et mediaan on kuskil 11000–13000. Exceli keskmisena tagastab nende kahe väärtuse väärtus 12000.
Alustuseks valige oma andmetes üks lahter ja andmete tabelina vormindamiseks vajutage klahvikombinatsiooni Ctrl + T.
Seejärel valige Lisa, Pivot Table. Dialoogis Lisa PivotTable-liigendtabel lisage ruut Nende andmete lisamine andmemudelisse.
Valige liigendtabeli väljadel Piirkond ja piirkond. Kuid ärge valige Müük. Selle asemel paremklõpsake tabeli pealkirja ja valige Uus mõõt. "Mõõde" on arvutatud välja väljamõeldud nimi. Mõõdud on võimsamad kui tavalistes pöördtabelites arvutatud väljad.
Täitke dialoogis Määra määratlemine neli allpool näidatud kirjet:
- Mõõdu nimi: müügi mediaan
- Valem
=MEDIAN((Sales)) - Numbrivorming: arv
- Kümnendkohad: 0
Pärast mõõtmise loomist lisatakse see väljade loendisse, kuid peate valima kirje, et lisada see pöördtabeli alale Väärtused. Nagu allpool näete, arvutab pöördtabel mediaanid õigesti.
Vaata videot
Video ärakiri
Õppige Exceli jaotist Podcast, Episode 2197: mediaan liigendtabelis.
Ma pean teile ütlema, ma olen sellest tohutult põnevil. kui ma olin Houstonis ja tegin seal Power Exceli seminari, siis Alex tõstis käe ja ütles: "Hei, kas on mingit võimalust teha pöördtabelis mediaani?" Ei, pöördtabelis ei saa mediaani teha. Mäletan, et 25 aastat tagasi tõi mu hea sõber Juan Pablo Gonzales makro, et teha samaväärne mediaaniga, mitte kasutada pöördtabelit - see pole lihtsalt võimalik.
Kuid mul oli säde. Ütlesin: "Oota üks hetk" ja avan brauseri ning otsin sõna "DAX mediaan" ja kindlasti on DAX-is funktsioon MEDIAN, mis tähendab, et saame selle probleemi lahendada.
Olgu, DAX-i kasutamiseks peate olema Excel 2013 või Excel 2016 või Excel 2010 ja teil peab olema Power Pivoti lisandmoodul; teil ei pea olema vahekaarti Power Pivot, meil peab olema lihtsalt andmemudel. Hästi? Nii et ma valin need andmed, ma teen neist tabeli koos klahvikombinatsiooniga Ctrl + T ja nad annavad sellele kujuteldamatu nime "Tabel 4". Teie puhul võib see olla "tabel 1". Ja me lisame Pivoti tabeli, lisame need andmed andmemudelisse, klõpsame nuppu OK ja valime vasakpoolsest piirkonnast piirkondliku, kuid mitte müügi. Selle asemel soovin luua uue arvutatud mõõtmise. Nii et tulen siia laua juurde ja klõpsan hiire parema nupuga ning ütlen: Lisa mõõt. Ja seda meedet hakatakse nimetama "Müügi mediaaniks" ja valem on järgmine: = KESKMINE. Vajutage seal vahelehte ja valige Müük,sulgev sulg. Ma saan selle valida nullarvudeta pärast koma, kasutada tuhat eraldajat ja klõpsata nuppu OK Ja vaatame, et meie uus väli lisatakse siia, selle lisamiseks peame selle märkima ja see ütleb, et Midwesti mediaan on 12 000.
Nüüd kontrollime seda. Nii et siin, kogu Kesk-Läänes, on kogu andmekogumi mediaan vahemikus 11 000–13 000. Seega on keskmiselt 11 ja 13, mida täpselt mediaan tavalises Excelis teeks. Lisame nüüd linnaosa sisse ja seal on kirjas Chicago mediaani mediaan, Clevelandi mediaan on 17 000; Kesk-lääne koguarv ja koguarv 12 000. Kõik see on õige. Kui vinge see on? Lõpuks mediaan pöördtabelis tänu arvutatud väljale või mõõdule, nagu seda nimetatakse, ja andmemudelile.
Nüüd on selles raamatus LIVe, 54 kõigi aegade suurimat näpunäidet, minu näpunäiteid Power Exeli otseülekande jaoks. Raamatu kohta lisateabe saamiseks klõpsake paremas ülanurgas oleval nupul "I".
Hästi. Kokkuvõte: kas saate teha pöördtabelis mediaani? Oh ei, jah, jah, saate tänu andmemudelile. Võite luua mediaani; Ctrl + T, et muuta teie andmed tabeliks; Sisesta pöördtabel; ja märkige see ruut, lisage need andmed andmemudelisse; paremklõpsake tabeli nimel ja valige uus mõõt ja mõõduks saab = MEDIAN ((Müük)). See on fantastiline.
Täname Alexit minu seminaril ilmumise ja selle küsimuse esitamise eest, aitäh teile, et peatusite. Järgmisel korral näeme järgmise Netcast'i jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: pivot-table-median.xlsx
