Tämä kysymys tulee esiin kerran vuosikymmenessä: Voitko tehdä mediaanin pivot-taulukossa. Perinteisesti vastaus oli Ei. Muistan jo vuonna 2000, kun palkasin Excel MVP: n Juan Pablo Gonzalezin kirjoittamaan mahtavan makron, joka loi raportteja, jotka näyttivät pivot-taulukoilta, mutta joilla oli mediaaneja.
Joten kun Alex kysyi Houston Power Excel -seminaarissani mediaanien luomisesta, sanoin nopeasti "ei". Mutta sitten… mietin, onko DAX: lla mediaanitoiminto. Nopea Google-haku ja Kyllä! Mediaanille on DAX-toiminto.
Mitä DAX: n käyttäminen pivot-taulukossa kestää? Tarvitset Excel-version Windows-version, jossa on Excel 2013 tai uudempi.
Tässä on hyvin yksinkertainen tietojoukkoni, jota käytän testaamaan, kuinka pivot-taulukon mediaanit lasketaan. Voit nähdä, että Chicagon mediaanin tulisi olla 5000 ja Clevelandin mediaanin 17000. Chicagon tapauksessa arvoja on viisi, joten mediaani on kolmanneksi suurin arvo. Mutta koko tietojoukolle on 12 arvoa. Tämä tarkoittaa, että mediaani on jonnekin välillä 11000 ja 13000. Excel laskee näiden kahden arvon keskiarvoksi palauttamaan arvon 12000.
Aloita valitsemalla yksi solu tiedoistasi ja painamalla Ctrl + T muotoile tiedot taulukkona.
Valitse sitten Lisää, pivot-taulukko. Valitse Lisää pivot-taulukko -valintaikkunassa Lisää nämä tiedot tietomalliin -ruutu.
Valitse pivot-taulukon kentissä alue ja piiri. Mutta älä valitse myyntiä. Napsauta sen sijaan hiiren kakkospainikkeella Taulukko-otsikkoa ja valitse Uusi mitta. "Mitta" on hieno nimi lasketulle kentälle. Mitat ovat tehokkaampia kuin tavallisten pivot-taulukoiden lasketut kentät.
Täytä Määritä-valintaikkunassa neljä alla olevaa merkintää:
- Mitan nimi: Myynnin mediaani
- Kaava
=MEDIAN((Sales)) - Numeromuoto: Numero
- Desimaalipaikat: 0
Kun olet luonut mitan, se lisätään kenttäluetteloon, mutta sinun on valittava merkintä lisätäksesi sen pivot-taulukon Arvot-alueelle. Kuten alla voit nähdä, kääntötaulukko laskee mediaanit oikein.
Katso video
Videon transkriptio
Opi Excel Podcastista, Jakso 2197: Mediaani pivot-taulukossa.
Minun täytyy kertoa sinulle, olen erittäin innoissani tästä. kun olin Houstonissa tekemässä siellä Power Excel -seminaaria, ja Alex nosti kätensä ja sanoi: "Hei, onko mitään keinoa tehdä mediaani kääntöpöydässä?" Ei, et voi tehdä mediaania pivot-taulukossa. Muistan 25 vuotta sitten, hyvä ystäväni Juan Pablo Gonzales toi oikeastaan makron vastaamaan mediaania käyttämättä kääntötaulukkoa - se ei vain ole mahdollista.
Mutta minulla oli kipinä. Sanoin: "Odota hetki" ja avaan selaimen ja etsin sanaa "DAX mediaani", ja todellakin, DAX: ssa on MEDIAN-toiminto, mikä tarkoittaa, että voimme ratkaista tämän ongelman.
Selvä, joten DAX: n käyttämiseksi sinun on oltava Excel 2013: ssa tai Excel 2016: ssa tai Excel 2010: ssä ja sinulla on Power Pivot -apuohjelma; sinun ei tarvitse olla Power Pivot -välilehteä, meillä on oltava vain tietomalli. Hyvä on? Joten aion valita nämä tiedot, aion tehdä niistä taulukon, jossa on Ctrl + T, ja he antavat sille kekseliäisen nimen "Taulukko 4". Sinun tapauksessasi se voi olla "Taulukko 1". Ja lisätään pivot-taulukko, lisätään nämä tiedot tietomalliin, napsautetaan OK ja valitsemme vasemmasta alueesta Alue, mutta ei Myynti. Sen sijaan haluan luoda uuden lasketun mittauksen. Joten nousen tänne pöydän ääreen ja napsautan hiiren kakkospainikkeella ja sanon, Lisää mitta. Ja tätä toimenpidettä kutsutaan "myynnin mediaaniksi", ja kaava on: = MEDIAN. Paina välilehteä ja valitse Myynti,sulkumerkki. Voin valita tämän numeroksi ilman nollaa desimaalia, käyttää tuhat erottinta ja napsauttaa OK. Ja katsotaanpa, uusi kenttämme lisätään tähän, meidän on merkittävä se lisäämään se, ja siinä sanotaan, että keskilännen mediaani on 12 000.
Tarkistetaan nyt se. Joten täällä, koko Keskilännessä, koko datajoukon mediaani on välillä 11000 ja 13000. Joten se on keskimäärin 11 ja 13, mikä on tarkalleen mitä mediaani tekisi tavallisessa Excelissä. Lisätään nyt piiri piiriin ja siinä sanotaan Chicagon 5000 mediaani, Clevelandin mediaani on 17 000; Keskilännessä yhteensä 12 000. Kaikki tämä on oikein. Kuinka mahtavaa se on? Lopuksi mediaani pivot-taulukossa lasketun kentän tai mittauksen, kuten sitä kutsutaan, ja tietomallin ansiosta.
Nyt monet suosikkivihjeistäni - vinkkejä live-Power Excel -seminaariini - tässä kirjassa LIVe, Kaikkien aikojen 54 suurinta vinkkiä. Klikkaa kirjainta oikeassa yläkulmassa saadaksesi lisätietoja kirjasta.
Hyvä on. Kokoonpano: Voitko tehdä mediaanin kääntöpöydässä? Voi ei, kyllä, kyllä voit, tietomallin ansiosta. Voit luoda mediaanin; Ctrl + T tehdäksesi tiedoistasi taulukon; Lisää kääntötaulukko; ja valitse valintaruutu, Lisää nämä tiedot tietomalliin; Napsauta taulukon nimeä hiiren kakkospainikkeella ja valitse uusi mittayksikkö, ja mitta on = MEDIAN ((Myynti)). Se on mahtava.
Kiitos Alexille siitä, että hän esiintyi seminaarissani ja esitti kysymyksen, kiitos, että kävit ohi. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: pivot-table-median.xlsx
