Laske eri arvot Excel-pivot-taulukossa (helppo vaiheittainen opas)

Excel -pivot -taulukot ovat hämmästyttäviä (tiedän, että mainitsen tämän joka kerta, kun kirjoitan pivot -taulukoista, mutta se on totta).

Perustaidolla ja hieman vetämällä ja pudottamalla voit saada ämpäri kuorman töitä muutamassa sekunnissa.

Vaikka paljon voidaan tehdä muutamalla napsautuksella pivot -taulukoissa, on joitakin asioita, jotka vaativat muutamia lisävaiheita tai pientä työskentelyä.

Yksi tällainen asia on laskea eri arvot pivot -taulukkoon.

Tässä opetusohjelmassa näytän sinulle, kuinka voit laskea eri arvot ja ainutlaatuiset arvot Excel -pivot -taulukossa.

Mutta ennen kuin ryhdyn eri arvojen laskemiseen, on tärkeää ymmärtää ero "erillisen määrän" ja "ainutlaatuisen laskun" välillä

Erillinen Count Vs Ainutlaatuinen Count

Vaikka nämä voivat vaikuttaa samalta, se ei ole.

Alla on esimerkki, jossa on nimitiedosto ja olen luetellut yksilölliset ja erilliset nimet erikseen.

Ainutlaatuiset arvot/nimet ovat niitä, jotka tapahtuvat vain kerran. Tämä tarkoittaa, että kaikki nimet, jotka toistuvat ja joissa on päällekkäisyyksiä, eivät ole ainutlaatuisia. Ainutlaatuiset nimet on lueteltu yllä olevan tietojoukon sarakkeessa C.

Erilliset arvot/nimet ovat niitä, joita esiintyy vähintään kerran tietojoukossa. Joten jos nimi esiintyy kolme kertaa, se lasketaan edelleen yhdeksi erilliseksi nimeksi. Tämä voidaan saavuttaa poistamalla päällekkäiset arvot/nimet ja säilyttämällä kaikki erilliset. Erilliset nimet on lueteltu yllä olevan tietojoukon sarakkeessa B.

Sen perusteella, mitä olen nähnyt, useimmiten ihmiset sanovat haluavansa saada ainutlaatuisen lukumäärän pivot -taulukosta, he tarkoittavat itse asiassa erillistä määrää, jota käsittelen tässä opetusohjelmassa.

Laske eri arvot Excelin pivot -taulukossa

Oletetaan, että sinulla on myyntitiedot alla esitetyllä tavalla:

Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä

Oletetaan yllä olevan tietojoukon avulla, että haluat löytää vastauksen seuraaviin kysymyksiin:

1. Kuinka monta myyntiedustajaa on kullakin alueella (mikä on vain eri myyntiedustajien määrä kullakin alueella)?
2. Kuinka moni myyjä myy tulostimen vuosina 2021-2022?

Vaikka pivot -taulukot voivat tehdä yhteenvedon tiedoista heti muutamalla napsautuksella, sinun on suoritettava vielä muutama vaihe, jotta voit saada eri arvot.

Jos käytät Excel 2013 tai sen jälkeiset versiot, Pivot -taulukossa on sisäänrakennettu toiminto, joka antaa sinulle nopeasti eri määrän. Ja jos käytät Excel 2010 tai sitä edeltävät versiot, sinun on muokattava lähdetietoja lisäämällä auttajasarake.

Tässä opetusohjelmassa käsitellään seuraavia kahta menetelmää:

• Helper -sarakkeen lisääminen alkuperäiseen tietojoukkoon yksilöllisten arvojen laskemiseksi (toimii kaikissa versioissa).
• Tietojen lisääminen tietomalliin ja erillisen laskutoiminnon käyttäminen (saatavana Excel 2013: ssa ja sen jälkeisissä versioissa).

On olemassa kolmas menetelmä, jonka Roger esittää tässä artikkelissa (jota hän kutsuu Pivot -pivot -taulukon menetelmäksi).

Aloitetaan!

Helper -sarakkeen lisääminen tietojoukkoon

Huomautus: Jos käytät Excel 2013 tai uudempaa versiota, ohita tämä menetelmä ja siirry seuraavaan (koska se käyttää sisäänrakennettua Pivot Table -toimintoa - Erillinen kreivi).

Tämä on helppo tapa laskea pivot -taulukon eri arvot, koska sinun tarvitsee vain lisätä auttajasarake lähdetietoihin. Kun olet lisännyt auttajasarakkeen, voit käyttää tätä uutta tietojoukkoa laskemaan eri määrän.

Vaikka tämä on helppo kiertotapa, tällä menetelmällä on joitain haittoja (joita käsitellään myöhemmin tässä opetusohjelmassa).

Näytän ensin, kuinka voit lisätä auttajasarakkeen ja saada selvän lukumäärän.

Oletetaan, että minulla on alla esitetyt tiedot:

Lisää seuraava kaava sarakkeeseen F ja käytä sitä kaikissa soluissa, joissa on tietoja viereisissä sarakkeissa.

= JOS (LASKUT ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Yllä oleva kaava laskee COUNTIFS -funktion avulla, kuinka monta kertaa nimi esiintyy tietyllä alueella. Huomaa myös, että ehtoalue on $ C $ 2: C2 ja $ B $ 2: B2. Tämä tarkoittaa sitä, että se laajenee jatkuvasti, kun siirryt alas sarakkeeseen.

Esimerkiksi solussa E2 ehtoalueet ovat $ C $ 2: C2 ja $ B $ 2: B2 ja solussa E3 nämä alueet laajenevat $ C $ 2: C3 ja $ B $ 2: B3.

Tämä varmistaa, että COUNTIFS -funktio laskee nimen ensimmäisen esiintymän 1: ksi, nimen toisen esiintymän 2: ksi ja niin edelleen.

Koska haluamme vain saada erilliset nimet, käytetään IF -funktiota, joka palauttaa arvon 1, kun alueen nimi ilmestyy ensimmäisen kerran ja palauttaa 0, kun se ilmestyy uudelleen. Tämä varmistaa, että vain erilliset nimet lasketaan eikä toistoja.

Alla on, miltä tietokokonaisuutesi näyttäisi, kun olet lisännyt auttaja -sarakkeen.

Nyt kun lähdetietoja on muokattu, voimme käyttää tätä pivot -taulukon luomiseen ja apuohjelma -sarakkeen avulla saadaksesi myyntiedustajan määrän eri alueilla.

Alla on vaiheet tämän tekemiseen:

1. Valitse mikä tahansa solu tietojoukosta.
2. Napsauta Lisää -välilehteä.
3. Napsauta pivot -taulukkoa (tai käytä pikanäppäintä - ALT + N + V)
4. Varmista Luo pivot -taulukko -valintaikkunassa, että taulukko/alue on oikein (ja sisältää apusarakkeen) ja "Uusi laskentataulukko" valittuna.
5. Napsauta OK.

Yllä olevat vaiheet lisäisivät uuden taulukon, jossa on pivot -taulukko.

Vedä Rivi -alueen Alue -kenttä ja Arvot -alueen D -kenttä.

Saat pivot -taulukon alla olevan kuvan mukaisesti:

Nyt voit muuttaa sarakkeen otsikon D -summan summasta arvoksi Myyntiedustaja.

Helper -sarakkeen käytön haitat:

Vaikka tämä menetelmä on melko suoraviivainen, minun on korostettava muutamia haittoja, jotka liittyvät lähdetietojen muokkaamiseen pivot -taulukossa:

• Tietolähde apuohjelman sarakkeen kanssa ei ole yhtä dynaaminen kuin pivot -taulukko. Pivot -taulukon avulla voit leikata ja leikata tiedot haluamallasi tavalla, mutta kun käytät apuohjelman saraketta, menetät osan tästä kyvystä. Oletetaan, että lisäät auttaja -sarakkeen saadaksesi selvän myyntiedustajan kullakin alueella. Entä jos haluat myös saada selvän määrän myyntiedustajia, jotka myyvät tulostimia. Sinun on palattava lähdetietoihin ja muokattava auttajasarakkeen kaavaa (tai lisättävä uusi auttajasarake).
• Koska lisäät lisää tietoja pivot -taulukon lähteeseen (joka lisätään myös pivot -välimuistiin), tämä voi johtaa suurempiin Excel -tiedostoihin.
• Koska käytämme Excel -kaavaa, se saattaa hidastaa Excel -työkirjaasi, jos sinulla on tuhansia rivejä tietoja.

Lisää tietoja tietomalliin ja tee yhteenveto käyttämällä erillistä lukua

Pivot -taulukko lisäsi uusia toimintoja Excel 2013: een, jonka avulla voit saada selkeän laskelman samalla, kun tiivistät tietojoukon.

Jos käytät aiempaa versiota, et voi käyttää tätä menetelmää (kuten pitäisi yrittää lisätä auttajasarake tämän menetelmän mukaisesti).

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada yksilöllisen myyntiedustajan määrän kullakin alueella.

Alla on vaiheet, joilla saat erillisen laskenta -arvon pivot -taulukkoon:

1. Valitse mikä tahansa solu tietojoukosta.
2. Napsauta Lisää -välilehteä.
3. Napsauta pivot -taulukkoa (tai käytä pikanäppäintä - ALT + N + V)
4. Varmista Luo pivot -taulukko -valintaikkunassa, että taulukko/alue on oikea ja Uusi laskentataulukko -vaihtoehto on valittu.
5. Valitse valintaruutu "Lisää nämä tiedot tietomalliin"
6. Napsauta OK.

Yllä olevat vaiheet lisäisivät uuden taulukon, jossa on uusi pivot -taulukko.

Vedä Alue Rivit -alueella ja Myyntiedustaja Arvot -alueella. Saat pivot -taulukon alla olevan kuvan mukaisesti:

Yllä oleva pivot -taulukko antaa myyntiedustajan kokonaismäärän kullakin alueella (eikä erillistä lukua).

Saat pivot -taulukon eri määrän noudattamalla seuraavia vaiheita:

1. Napsauta hiiren kakkospainikkeella mitä tahansa myyntiedustajien määrä -sarakkeen solua.
2. Napsauta Arvokenttäasetukset
3. Valitse Arvokentän asetukset -valintaikkunassa laskutyypiksi "Erillinen määrä" (saatat joutua vierittämään luetteloa alaspäin löytääksesi sen).
4. Napsauta OK.

Huomaat, että sarakkeen nimi vaihtuu "myyntiedustajien määrästä" "erilliseksi myyntiedustajien määräksi". Voit muuttaa sen haluamaksesi.

Jotkut asiat, jotka tiedät, kun lisäät tiedot tietomalliin:

• Jos tallennat tietosi tietomalliin ja avaat sen sitten vanhemmassa Excel -versiossa, näyttöön tulee varoitus: "Joitakin pivot -taulukon toimintoja ei tallenneta". Et ehkä näe erillistä määrää (ja tietomallia), kun se avataan vanhemmassa versiossa, joka ei tue sitä.
• Kun lisäät tiedot tietomalliin ja teet pivot -taulukon, siinä ei näy vaihtoehtoja laskettujen kenttien ja laskettujen sarakkeiden lisäämiseksi.

Lataa esimerkkitiedosto napsauttamalla tätä

Entä jos haluat laskea ainutlaatuisia arvoja (ei erillisiä arvoja)?

Jos haluat laskea ainutlaatuisia arvoja, sinulla ei ole sisäänrakennettuja toimintoja pivot -taulukossa ja sinun on luotettava vain apusarakkeisiin.

Muista - Ainutlaatuiset arvot ja eri arvot eivät ole sama asia. Klikkaa tästä tietääksesi eron.

Yksi esimerkki voisi olla, kun sinulla on alla oleva tietojoukko ja haluat selvittää, kuinka monta myyntiedustajaa on yksilöllinen kullakin alueella. Tämä tarkoittaa, että ne toimivat vain yhdellä tietyllä alueella eivätkä muut.

Tällaisissa tapauksissa sinun on luotava yksi useammasta kuin yhdestä apusarakkeesta.

Tässä tapauksessa alla oleva kaava tekee tempun:

= JOS (JOS (LASKUT ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

Yllä oleva kaava tarkistaa, esiintyykö myyntiedustajan nimi vain yhdellä alueella vai useammalla kuin yhdellä alueella. Se tekee sen laskemalla alueen esiintymisten määrän alueella ja jakamalla sen nimen esiintymien kokonaismäärällä. Jos arvo on pienempi kuin 1, se osoittaa, että nimi esiintyy kahdella tai useammalla alueella.

Jos nimi esiintyy useammalla kuin yhdellä alueella, se palauttaa arvon 0 ja palauttaa yhden.

Kaava tarkistaa myös, toistetaanko nimi samalla alueella vai ei. Jos nimi toistetaan, vain nimen ensimmäinen esiintymä palauttaa arvon 1 ja kaikki muut esiintymät 0.

Tämä voi tuntua hieman monimutkaiselta, mutta se riippuu jälleen siitä, mitä yrität saavuttaa.

Joten jos haluat laskea ainutlaatuisia arvoja pivot -taulukkoon, käytä apusarakkeita ja jos haluat laskea erillisiä arvoja, voit käyttää sisäänrakennettua toimintoa (Excel 2013: ssa ja uudemmissa) tai käyttää apusaraketta.

Lataa esimerkkitiedosto napsauttamalla tätä

Saatat pitää myös seuraavista pivot -taulukon opetusohjelmista:

• Tietojen suodattaminen pivot -taulukossa Excelissä
• Päivämäärien ryhmittely Excelin pivot -taulukoissa
• Numeroiden ryhmittely Excelin pivot -taulukossa
• Ehdollisen muotoilun käyttäminen Excel -pivot -taulukossa
• Viipaloijat Excel -pivot -taulukossa
• Pivot -taulukon päivittäminen Excelissä
• Poista pivot -taulukko Excelistä