Excel -pivot -taulukon lasketun kentän lisääminen ja käyttäminen

Usein, kun luot Pivot -taulukon, sinun on laajennettava analyysi ja sisällytettävä siihen enemmän dataa/laskelmia.

Jos tarvitset uuden datapisteen, joka voidaan saada käyttämällä pivot -taulukon olemassa olevia datapisteitä, sinun ei tarvitse palata takaisin ja lisätä sitä lähdetietoihin. Sen sijaan voit käyttää a Pivot -taulukon laskettu kenttä tehdä tämä.

Lataa tietojoukko ja seuraa sitä.

Mikä on pivot -taulukon laskettu kenttä?

Aloitetaan perusesimerkillä pivot -taulukosta.

Oletetaan, että sinulla on jälleenmyyjien tietojoukko ja luot pivot -taulukon alla kuvatulla tavalla:

Yllä oleva pivot -taulukko tiivistää vähittäiskauppiaiden myynti- ja voittoarvot.

Entä jos haluat myös tietää, mikä oli näiden vähittäiskauppiaiden voittomarginaali (jossa voittomarginaali on "Voitto" jaettuna "Myynnillä").

Voit tehdä tämän kahdella tavalla:

1. Palaa alkuperäiseen tietojoukkoon ja lisää tämä uusi datapiste. Joten voit lisätä uuden sarakkeen lähdetietoihin ja laskea sen voittomarginaalin. Kun olet tehnyt tämän, sinun on päivitettävä pivot -taulukon lähdetiedot, jotta tämä uusi sarake saadaan osaksi sitä.
   • Vaikka tämä menetelmä on mahdollinen, sinun on palattava manuaalisesti tietojoukkoon ja tehtävä laskelmat. Saatat joutua esimerkiksi lisäämään toisen sarakkeen, jotta voit laskea keskimääräisen myynnin yksikköä kohti (myynti/määrä). Sinun on jälleen lisättävä tämä sarake lähdetietoihisi ja päivitettävä sitten pivot -taulukko.
   • Tämä menetelmä turvottaa myös pivot -taulukon, kun lisäät siihen uusia tietoja.
2. Lisää laskutoimituksia pivot -taulukon ulkopuolelle. Tämä voi olla vaihtoehto, jos pivot -taulukkorakenne ei todennäköisesti muutu. Mutta jos muutat Pivot -taulukkoa, laskenta ei ehkä päivity vastaavasti ja saattaa antaa sinulle vääriä tuloksia tai virheitä. Kuten alla on esitetty, laskin voittomarginaalin, kun rivillä oli jälleenmyyjiä. Mutta kun muutin sen asiakkaista alueiksi, kaava antoi virheen.
3. Pivot -taulukon lasketun kentän käyttäminen. Tämä on tehokkain tapa käyttää olemassa olevia pivot -taulukon tietoja ja laskea haluttu tieto. Tarkastele Laskettua kenttää virtuaalisena sarakkeena, jonka olet lisännyt pivot -taulukon olemassa olevien sarakkeiden avulla. Pivot -taulukon lasketun kentän käytöstä on monia etuja (kuten näemme minuutin kuluttua):
   • Se ei vaadi kaavojen käsittelyä tai lähdetietojen päivittämistä.
   • Se on skaalautuva, koska se ottaa automaattisesti huomioon kaikki uudet tiedot, jotka voit lisätä pivot -taulukkoosi. Kun olet lisännyt Laske -kentän, voit käyttää sitä kuten mitä tahansa muuta pivot -taulukon kenttää.
   • Se on helppo päivittää ja hallita. Jos esimerkiksi mittarit muuttuvat tai sinun on muutettava laskutoimitusta, voit tehdä sen helposti itse pivot -taulukosta.

Lasketun kentän lisääminen pivot -taulukkoon

Katsotaanpa, miten pivot -taulukon laskettu kenttä lisätään olemassa olevaan pivot -taulukkoon.

Oletetaan, että sinulla on pivot -taulukko alla olevan kuvan mukaisesti ja haluat laskea voittomarginaalin kullekin jälleenmyyjälle:

Voit lisätä pivot -taulukon lasketun kentän seuraavasti:

• Valitse mikä tahansa pivot -taulukon solu.
• Valitse Pivot -taulukon työkalut -> Analysoi -> Laskut -> Kentät, kohteet ja sarjat.
• Valitse avattavasta valikosta Laskettu kenttä.
• Lisää laskettu arkistoitu -valintaikkunassa:
  • Anna sille nimi kirjoittamalla se Nimi -kenttään.
  • Luo Kaava -kenttään haluamasi kaava lasketulle kentälle. Huomaa, että voit valita alla olevista kenttänimistä. Tässä tapauksessa kaava on "= Voitto/ myynti". Voit joko syöttää kenttien nimet manuaalisesti tai kaksoisnapsauttaa Kentät -ruudussa näkyvää kentän nimeä.
• Napsauta Lisää ja sulje valintaikkuna.

Heti kun lisäät lasketun kentän, se näkyy yhtenä Pivot -taulukon kentät -luettelon kentistä.

Nyt voit käyttää tätä laskettua kenttää kuten mitä tahansa muuta pivot -taulukon kenttää (huomaa, että et voi käyttää pivot -taulukon laskettua kenttää raporttisuodattimena tai -leikkurina).

Kuten aiemmin mainitsin, pivot -taulukon lasketun kentän käytön etu on, että voit muuttaa pivot -taulukon rakennetta ja se mukautuu automaattisesti.

Jos esimerkiksi vedän ja pudotan alueen rivialueella, saat alla olevan tuloksen, jossa voittomarginaalin arvo ilmoitetaan sekä jälleenmyyjille että alueelle.

Yllä olevassa esimerkissä olen lisännyt lasketun kentän yksinkertaisella kaavalla (= Voitto/myynti). Voit kuitenkin käyttää myös joitain edistyneitä kaavoja.

Ennen kuin näytän sinulle esimerkin kehittyneen kaavan käyttämisestä pivot -taulukon laskukentän luomiseen, tässä on muutamia asioita, jotka sinun on tiedettävä:

• Et voi käyttää viittauksia tai nimettyjä alueita, kun luot pivot -taulukon laskettua kenttää. Tämä sulkee pois monia kaavoja, kuten VLOOKUP, INDEX, OFFSET ja niin edelleen. Voit kuitenkin käyttää kaavoja, jotka voivat toimia ilman viittauksia (kuten SUM, IF, COUNT jne.).
• Voit käyttää vakiota kaavassa. Jos esimerkiksi haluat tietää ennustetun myynnin, jossa sen ennustetaan kasvavan 10%, voit käyttää kaavaa = Myynti*1.1 (jossa 1.1 on vakio).
• Ensisijaisuusjärjestystä noudatetaan lasketun kentän muodostavassa kaavassa. Paras käytäntö on käyttää sulkeita, jotta sinun ei tarvitse muistaa tärkeysjärjestystä.

Katsotaanpa nyt esimerkkiä kehittyneen kaavan käyttämisestä lasketun kentän luomiseen.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja sinun on näytettävä ennustettu myyntiarvo pivot -taulukossa.

Ennustetun arvon saamiseksi sinun on käytettävä 5%: n myynnin lisäystä suurille vähittäiskauppiaille (myynti yli 3 miljoonaa) ja 10%: n myyntiä pienille ja keskisuurille vähittäismyyjille (myynti alle 3 miljoonaa).

Huomautus: Myyntinumerot ovat väärennettyjä, ja niitä on käytetty havainnollistamaan tämän opetusohjelman esimerkkejä.

Voit tehdä tämän seuraavasti:

• Valitse mikä tahansa pivot -taulukon solu.
• Siirry pivot -taulukkotyökaluihin -> Analysoi -> Laskelmat -> Kentät, kohteet ja sarjat.
• Valitse avattavasta valikosta Laskettu kenttä.
• Lisää laskettu arkistoitu -valintaikkunassa:
  • Anna sille nimi kirjoittamalla se Nimi -kenttään.
  • Käytä Kaava -kentässä seuraavaa kaavaa: = IF (alue = ”etelä”, myynti *1,05, myynti *1,1)
• Napsauta Lisää ja sulje valintaikkuna.

Tämä lisää pivot -taulukkoon uuden sarakkeen, jossa on myynnin ennustearvo.

Lataa tietojoukko napsauttamalla tätä.

Pivot -taulukon laskettujen kenttien ongelma

Laskettu kenttä on hämmästyttävä ominaisuus, joka todella parantaa pivot -taulukon arvoa kenttälaskelmilla, mutta pitää kaiken skaalattavana ja hallittavana.

Pivot -taulukon laskettuissa kentissä on kuitenkin ongelma, joka sinun on tiedettävä ennen sen käyttöä.

Oletetaan, että minulla on pivot -taulukko, kuten alla on esitetty, jossa käytin laskettua kenttää saadakseni ennustetut myyntiluvut.

Huomaa, että välisumma ja kokonaissumma eivät ole oikein.

Vaikka näiden pitäisi lisätä jokaisen vähittäiskauppiaan yksilöllinen myyntiennustearvo, todellisuudessa se noudattaa samaa laskettua kenttäkaavaa, jonka loimme.

Joten South Totalille, vaikka arvon pitäisi olla 22 824 000, South Total ilmoittaa sen virheellisesti 22 287 000: ksi. Tämä tapahtuu, kun se käyttää kaavaa 21 225 800*1,05 arvon saamiseksi.

Valitettavasti et voi mitenkään korjata tätä.

Paras tapa hoitaa tämä on poistaa välisummat ja kokonaissummat pivot -taulukosta.

Voit myös käydä läpi joitakin innovatiivisia ratkaisuja, joita Debra on osoittanut käsittelevän tätä ongelmaa.

Kuinka muuttaa tai poistaa pivot -taulukon laskettua kenttää?

Kun olet luonut pivot -taulukon lasketun kentän, voit muokata kaavaa tai poistaa sen seuraavasti:

• Valitse mikä tahansa pivot -taulukon solu.
• Siirry pivot -taulukkotyökaluihin -> Analysoi -> Laskelmat -> Kentät, kohteet ja sarjat.
• Valitse avattavasta valikosta Laskettu kenttä.
• Napsauta Nimi-kentässä olevaa avattavaa nuolta (pieni alas osoittava nuoli kentän lopussa).
• Valitse luettelosta laskettu kenttä, jonka haluat poistaa tai muokata.
• Muuta kaavaa, jos haluat muokata sitä, tai napsauta Poista, jos haluat poistaa sen.

Kuinka saada luettelo kaikista lasketuista kenttäkaavoista?

Jos luot paljon pivot -taulukon laskettu -kenttää, älä huolehdi siitä, että seuraat kussakin kentässä käytettyä kaavaa.

Excelin avulla voit nopeasti luoda luettelon kaikista lasketuista kentistä luomisessa käytetyistä kaavoista.

Tässä on vaiheet, joilla saat nopeasti luettelon kaikista lasketuista kentistä:

• Valitse mikä tahansa pivot -taulukon solu.
• Valitse Pivot -taulukon työkalut -> Analysoi -> Kentät, kohteet ja sarjat -> Listakaavat.

Heti kun napsautat Listakaavat, Excel lisää automaattisesti uuden laskentataulukon, joka sisältää kaikkien laskettujen kenttien/kohteiden tiedot, joita olet käyttänyt pivot -taulukossa.

Tämä voi olla todella hyödyllinen työkalu, jos sinun on lähetettävä työsi asiakkaalle tai jaettava se tiimillesi.

Saatat myös löytää hyödyllisiä seuraavia pivot -taulukon oppaita:

• Lähdetietojen valmistelu pivot -taulukkoa varten.
• Viipaloiden käyttäminen Excel -pivot -taulukossa: Aloittelijan opas.
• Päivämäärien ryhmittely Excelin pivot -taulukoissa.
• Numeroiden ryhmittely Excelin pivot -taulukossa.
• Tietojen suodattaminen pivot -taulukossa Excelissä.
• Tyhjien solujen korvaaminen nollilla Excelin pivot -taulukoissa.
• Ehdollisen muotoilun käyttäminen Excel -pivot -taulukossa.
• Pivot -välimuisti Excelissä - mikä se on ja miten sitä käytetään parhaiten?