Väärtuste arvutamine PivotTable-liigendtabelis

PivotTable-liigendtabelites väärtuste arvutamiseks saate kasutada mis tahes järgmist või kõiki järgmisi arvutusmeetodeid.

• Kokkuvõttefunktsioonid väärtuseväljadel    Väärtuste alal olevad andmed summeerivad aluseks olevad lähteandmed PivotTable-liigendtabelisse. Näiteks lähteandmed

• annavad järgmised PivotTable-​​liigendtabeli ja PivotChart-liigenddiagrammid. Kui loote PivotChart-liigenddiagramm PivotTable-​​liigendtabeli andmete põhjal, kajastavad selle PivotChart-liigenddiagrammi väärtused seostuva PivotTable-liigendtabeli arvutusi.

• PivotTable-​​liigendtabelis annab veerg Kuu üksused Märts ja Aprill. Reaväli Piirkond annab üksused Põhi, Lõuna, Ida ja Lääs. Veeru Aprill ja rea Põhi ristumiskohal olev väärtus on lähteandmete selliste kirjete, mille välja Kuu väärtus on Aprill ja välja Piirkond väärtus Põhi, müügi kogutulu.

• PivotChart-liigenddiagrammis võib väli Piirkond olla kategooriaväli, mis näitab väärtusi Põhi, Lõuna, Ida ja Lääs kategooriatena. Väli Kuu võib olla sarjaväli, mis näitab üksusi Märts, Aprill ja Mai legendis kujutatud sarjadena. Väärtuse väli nimega Müügi summa saab sisaldada andmemarkereid, mis väljendavad iga piirkonna iga kuu kogu müügitulu. Näiteks saab üks andmemarker oma asukoha põhjal vertikaalteljel (väärtuse teljel) väljendada Aprilli kogumüüki piirkonnas Põhi.

• Väärtuseväljade arvutamiseks on kõigile lähteandmete tüüpidele, välja arvatud analüütilise töötlemise (OLAP) lähteandmetele, saadaval järgmised kokkuvõttefunktsioonid.

  Funktsioon	Summeerib
  Sum (Summa)	Väärtuste summa. See on arvandmete vaikefunktsioon.
  Count (Loenda)	Andmeväärtuste arv. Kokkuvõttefunktsioon Count toimib nagu funktsioon COUNTA. Funktsioon Count on mittearvuliste andmete vaikefunktsioon.
  Average (Keskmine)	Väärtuste keskmine.
  Max (Maksimum)	Suurim väärtus.
  Min (Miinimum)	Väikseim väärtus.
  Product (Korrutis)	Väärtuste korrutis.
  Count Nums (Loenda arvulised)	Arvuliste andmeväärtuste arv. Kokkuvõttefunktsioon Count Nums toimib nagu funktsioon COUNT.
  StDev (Standardhälve)	Populatsiooni hinnanguline standardhälve, kui valim on terve populatsiooni alamhulk.
  StDevp (Populatsiooni standardhälve)	Populatsiooni standardhälve, kui populatsioon on kõik kokkuvõetavad andmed.
  Var (Dispersioon)	Populatsiooni hinnanguline dispersioon, kui valim on terve populatsiooni alamhulk.
  Varp (Populatsiooni dispersioon)	Populatsiooni dispersioon, kui populatsioon on kõik kokkuvõetavad andmed.

• Kohandatud arvutused    Meetod kohandatud arvutused näitab teistel selle andmepiirkonna üksustel või lahtritel põhinevaid väärtusi. Näiteks saate kuvada andmevälja Müügi summa väärtused protsendina Märtsi müügist või välja Kuu üksuste jooksva kogusummana.

  Järgmised funktsioonid on saadaval kohandatud arvutusteks väärtuseväljadel.

  Funktsioon	Tulem
  Ära arvuta	Kuvab väljale sisestatud väärtust.
  % üldkokkuvõttest	Kuvab väärtused protsendina aruande kõikide väärtuste või andmepunktide kokkuvõttest.
  % veerusummast	Kuvab iga veeru või sarja väärtused protsendina veeru- või sarjasummast.
  % reasummast	Kuvab iga rea või kategooria väärtused protsendina rea- või kategooriasummast.
  % järgmisest	Kuvab väärtused protsendina baasüksuse väärtusest baasväljal.
  % emarea summast	Arvutab väärtused kujul: (üksuse väärtus) / (emaüksuse väärtus ridadel)
  % emaveeru summast	Arvutab väärtused kujul: (üksuse väärtus) / (emaüksuse väärtus veergudel)
  % ema kogusummast	Arvutab väärtused kujul: (üksuse väärtus) / (valitud välja Baasväli emaüksuse väärtus).
  Erinevus järgmisest	Kuvab väärtused erinevusena baasüksuse väärtusest baasväljal.
  % erinevust järgmisest	Kuvab väärtused protsendierinevusena baasüksuse väärtusest baasväljal.
  Jooksev kokkuvõte	Kuvab väärtused baasvälja üksteisele järgnevate üksuste jooksva kokkuvõttena.
  % jooksvast kokkuvõttest	Arvutab väärtuse protsendina baasvälja üksteisele järgnevate üksuste jooksvast kokkuvõttest.
  Järjesta väikseimast suurimani	Kuvab valitud väärtuste reitingu kindlal väljal, kus välja väikseim üksus määratletakse reitinguga 1 ja iga järjest suurem väärtus kõrgema reitinguga.
  Järjesta suurimast väikseimani	Kuvab valitud väärtuste reitingu kindlal väljal, kus välja suurim üksus määratletakse reitinguga 1 ja iga järjest väiksem väärtus kõrgema reitinguga.
  Register	Arvutab väärtused kujul: ((väärtus lahtris) x (üldkokkuvõte üldkokkuvõtetest)) / ((rea üldkokkuvõte) x (veeru üldkokkuvõte)).

• Valemid    Kui kokkuvõttefunktsioonid ja kohandatud arvutused ei anna soovitud tulemusi, saate arvutuslikel väljadel ja arvutuslikes üksustes luua oma valemid. Näiteks saate müügi komisjonitasule lisada arvutusliku üksuse koos valemiga, mis võib iga piirkonna jaoks isesugune olla. Aruanne kaasab siis automaatselt komisjonitasu vahesummadesse ja kogusummadesse.

Arvutused ja suvandid, mis aruandes saadaval on, olenevad sellest, kas lähteandmed tulevad OLAP-andmebaasist või muud tüüpi andmeallikast.

• OLAP-lähteandmetel põhinevad arvutused    OLAP-kuubist loodud PivotTable-​​liigendtabelite korral tehakse summeeritud väärtustele OLAP-serveris eelarvutused, enne kui Excel tulemid kuvab. Te ei saa muuta seda, kuidas need eelarvutatud väärtused PivotTable-​​liigendtabelis arvutatakse. Näiteks ei saa te muuta kokkuvõttefunktsiooni, mida kasutatakse andmeväljade või vahesummade arvutamiseks, ega lisada arvutuslikke välju arvutuslikele üksustele.

  Samuti kui OLAP-server pakub arvutuslikke välju, mida nimetatakse arvutuslikeks liikmeteks, näete neid välju PivotTable-liigendtabeli väljaloendis. Samuti näete kõiki makrode loodud arvutuslikke välju ja arvutuslikke üksusi, mis kirjutati keeles Visual Basic for Applications (VBA) ja salvestati teie töövihikusse, kuid te ei saa neid välju ega üksusi muuta. Kui vajate arvutuste lisatüüpe, pöörduge OLAP-andmebaasi administraatori poole.

  OLAP-andmeallika korral saate vahesummade ja kogusummade arvutamisel peidetud üksused kaasata või välistada.

• Muud tüüpi lähteandmetel põhinevad arvutused    PivotTable-liigendtabelites, mis põhinevad muud tüüpi välistel andmetel või töölehe andmetel, kasutab Excel arvandmeid sisaldavate väärtuseväljade arvutamiseks kokkuvõttefunktsiooni Summa ja teksti sisaldavate andmeväljade arvutamiseks kokkuvõttefunktsiooni Loenda. Andmete täpsemaks analüüsimiseks ja kohandamiseks saate valida mõne muu kokkuvõttefunktsiooni, näiteks Keskmine, Max või Min. Samuti saate luua oma valemeid, mis kasutavad aruande või teise töölehe andmete elemente, luues väljal arvutusliku välja või arvutusliku üksuse.

Valemeid saate luua ainult aruannetes, mis põhinevad muud tüüpi lähteandmetel. OLAP-andmebaasil põhinevates aruannetes valemeid kasutada ei saa. Kui kasutate valemeid PivotTable-liigendtabelites, peaksite teadma järgmisi valemite süntaksireegleid ja käitumist.

• PivotTable-liigendtabeli valemi elemendid    Valemites, mille loote arvutuslike väljade ja arvutuslike üksuste jaoks, saate kasutada tehtemärke ja avaldisi nagu teistegi töölehevalemite puhul. Saate kasutada konstante ja viidata aruande andmetele, kuid te ei saa kasutada lahtriviiteid ega määratud nimesid. Te ei saa kasutada töölehefunktsioone, mis nõuavad argumentidena lahtriviiteid või määratud nimesid, samuti ei saa te kasutada massiivifunktsioone.

• Välja- ja üksusenimed    Excel kasutab valemites aruandeelementide identifitseerimiseks välja- ja üksusenimesid. Järgmises näites kasutavad andmed vahemikus C3:C9 väljanime Piimatooted. Arvutuslik üksus väljal Tüüp, mis eeldab uue toote müüki piimatoodete müügi põhjal, saab kasutada näiteks valemit =Piimatooted * 115%.

  

  Märkus.: PivotChart-liigenddiagrammis kuvatakse väljad PivotTable-liigendtabeli väljaloendis ja üksusenimed iga välja ripploendis. Ärge ajage neid nimesid segi diagrammispikrites kuvatavate nimedega, mis näitavad sarjade ja andmepunktide nimesid.

• Valemid tegelevad kogusummadega, mitte üksikute kirjetega    Arvutuslike väljade valemid tegelevad valemi väljade aluseks olevate andmete summadega. Näiteks korrutab arvutusliku välja valem =Sales * 1.2 iga tüübi ja piirkonna müügi teguriga 1,2; see ei korruta iga üksikut müüki teguriga 1,2 ega liida seejärel korrutisi.

  Arvutuslike üksuste valemid tegelevad üksikute kirjetega. Näiteks korrutab arvutusliku üksuse valem =Piimatooted *115% iga üksiku Piimatoodete müügi teguriga 115% ja seejärel liidetakse korrutised alal Väärtused kokku.

• Tühikud, arvud ja sümbolid nimedes    Nimes, mis sisaldab mitut välja, saavad väljad olla suvalises järjestuses. Näiteks ülaltoodud näites saab vahemik C6:D6 olla nimega ’Aprill Põhi’ või ’Põhi Aprill’. Ümbritsege nimed, mis koosnevad rohkem kui ühest sõnast või sisaldavad arve või sümboleid, ülakomadega.

• Kogusummad    Valemid ei saa viidata kogusummadele (siinse näite korral näiteks Märts Kogusumma, Aprill Kogusumma ja Üldkogusumma).

• Väljanimed üksuseviidetes    Üksusele viitamisel saate kaasata välja nime. Üksuse nimi peab olema nurksulgudes: näiteks Piirkond[Põhi]. Selle vormingu kasutamine aitab vältida vigu #NAME? olukordades, kui aruande kahel eri väljal on üksustel sama nimi. Kui aruandes on väljal Tüüp näiteks üksus Liha ja väljal Kategooria samuti üksus Liha, saate veateadet #NAME? vältida nii, et viitate nendele üksustele kui Tüüp[Liha] ja Kategooria[Liha].

• Üksustele viitamine asukoha järgi    Saate viidata üksusele asukoha järgi, kus see praegu sorditud aruandes kuvatakse. Tüüp[1] on Piimatooted ja Tüüp[2] on Mereannid. Üksus, millele on sel viisil viidatud, saab muutuda, kui üksuste asukoht muutub või kuvatakse või peidetakse muid üksusi. Peidetud üksusi selles registris ei loendata.

  Üksustele viitamiseks saab kasutada suhtelisi asukohti. Asukohad määratletakse valemit sisaldava arvutusliku üksuse suhtes. Kui Lõuna on praegune piirkond, siis Piirkond[-1] on Põhi; kui Põhi on praegune piirkond, siis Piirkond[+1] on Lõuna. Näiteks saab arvutuslik üksus kasutada valemit =Piirkond[-1] * 3%. Kui teie antud asukoht on esimesest üksusest eespool või viimasest üksusest tagapool, annab valem tulemiks vea #REF!.

PivotChart-liigenddiagrammides valemite kasutamiseks looge valemid seotud PivotTable-liigendtabelites, kus saate vaadata üksikuid väärtusi, millest teie andmed koosnevad, ja seejärel saate tulemid graafiliselt kuvada PivotChart-liigenddiagrammil.

Näiteks kuvab järgmine PivotChart-liigenddiagramm iga müügiesindaja müüginäitajaid piirkonna kohta:

Kui soovite vaadata, millised oleksid müügitulemused siis, kui neid suurendada 10%, saate seotud PivotTable-​​liigendtabelis luua arvutusliku välja, mis kasutab valemit =Müük * 110%.

Tulem kuvatakse otsekohe PivotChart-liigenddiagrammil, nagu on näha alloleval diagrammil:

Kui soovite näha eraldi andmemarkerit piirkonna Põhi müügi kohta, millest on lahutatud transpordikulud 8%, saate väljale Piirkond luua arvutusliku üksuse valemiga =Põhi – (Põhi * 8%).

Tulemiks saate järgmise diagrammi:

Väljal Müügiesindaja loodud arvutuslik üksus kuvatakse legendis esindatud sarjana ja diagrammis kuvatakse see iga kategooria andmepunktina.

Enne alustamist otsustage, kas soovite arvutuslikku välja või arvutuslikku üksust väljal. Arvutuslikku välja kasutage siis, kui soovite kasutada oma valemi mõne muu välja andmeid. Arvutuslikku üksust kasutage siis, kui soovite, et teie valem kasutaks välja kindla üksuse või üksuste andmeid.

Arvutuslike üksuste korral saate sisestada eri valemid lahtrikaupa. Näiteks kui arvutuslikul üksusel nimega ApelsinArv on läbi kõigi kuude valem =Apelsinid * .25, saate valemi juuni, juuli ja augusti jaoks muuta kujule =Apelsinid * .5.

Kui teil on mitu arvutuslikku üksust või valemit, saate arvutamise järjestust täpsustada.

Arvutusliku välja lisamine

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Arvutuslik väli.

   

3. Tippige välja nimi väljale Nimi.

4. Sisestage lahtrisse Valem välja valem.

   Valemi mõne muu välja andmete kasutamiseks klõpsake väljal Väljad välja ning seejärel valikut Lisa väli. Näiteks välja Müük igale väärtusele 15% komisjonitasu arvutamiseks saate sisestada valemi = Müük * 15%.

5. Klõpsake nuppu Lisa.

Arvutusliku üksuse lisamine väljale

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ning lisatakse menüüd Analüüs ja Kujundus.

2. Kui välja üksused on rühmitatud, klõpsake menüü Analüüs jaotises Rühm nuppu Tühista rühmitamine.

   

3. Klõpsake välja, kuhu soovite arvutusliku üksuse lisada.

4. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Arvutuslik üksus.

   

5. Tippige väljale Nimi arvutusliku üksuse nimi.

6. Sisestage lahtrisse Valem üksuse valem.

   Valemis üksuse andmete kaustamiseks klõpsake üksust loendis Üksused ja seejärel klõpsake valikut Lisa üksus (üksus peab olema arvutusliku üksusega samalt väljalt).

7. Klõpsake nuppu Lisa.

Eri valemite lahtrikaupa sisestamine arvutuslike üksuste korral

1. Klõpsake lahtrit, mille valemit soovite muuta.

   Mitme lahtri valemi muutmiseks hoidke klahvi CTRL allavajutatuna ja klõpsake lisalahtreid.

2. Tippige valemiribale valemi muudatused.

Mitme arvutusliku üksuse või valemi arvutamise järjestuse täpsustamine

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Lahendamisjärjekord.

   

3. Klõpsake valemit ja seejärel valikut Nihuta üles või Nihuta alla.

4. Jätkake, kuni valemid on selles järjestuses, kuidas soovite neid arvutada.

Saate kuvada kõigi praeguses PivotTable-liigendtabelis kasutatavate valemite loendi.

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Loetle valemid.

   

Enne valemi redigeerimist tehke kindlaks, kas see valem on arvutuslikul väljal või arvutuslikus üksuses. Kui valem on arvutuslikus üksuses, tehke kindlaks ka see, kas valem on selles arvutuslikus üksuses ainus.

Arvutuslikes üksustes saate redigeerida arvutusliku üksuse kindlate lahtrite üksikuid valemeid. Näiteks kui arvutuslikul üksusel nimega ApelsinArvuta on läbi kõigi kuude valem =Apelsinid * .25, saate valemi juuni, juuli ja augusti jaoks muuta kujule =Apelsinid * .5.

Kindlaks tegemine, kas valem on arvutuslikul väljal või arvutuslikus üksuses

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Loetle valemid.

   

3. Otsige valemite loendi jaotisest „Arvutuslik väli“ või „Arvutuslik üksus“ üles valem, mida soovite muuta.
   
   Kui arvutuslikul üksusel on mitu valemit, on üksus loomisel sisestatud vaikevalemi veerus B arvutusliku üksuse nimi. Arvutusliku üksuse täiendavate valemite puhul sisaldab veerg B nii arvutusliku üksuse nime kui ka lõikuvate üksuste nimesid.

   Näiteks võib teil arvutusliku üksuse jaoks olla vaikevalem nimega MinuÜksus ja teine valem nimega MinuÜksus Jaanuar Müük. PivotTable-liigendtabelis leiate selle valemi rea MinuÜksus ja veeru Jaanuar lahtrist Müük.

4. Jätkamiseks tehke ühte järgmistest.

Arvutusliku välja valemi redigeerimine

1. Klõpsake PivotTable-liigendtabelit.

   Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Arvutuslik väli.

   

3. Valige väljal Nimi arvutuslik väli, mille valemit soovite muuta.

4. Redigeerige valemit dialoogiboksis Valem.

5. Klõpsake nuppu Muuda.

Arvutusliku üksuse ühe valemi muutmine

1. Klõpsake välja, mis sisaldab arvutuslikku üksust.

2. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Arvutuslik üksus.

   

3. Valige väljal Nimi arvutuslik üksus.

4. Redigeerige valemit dialoogiboksis Valem.

5. Klõpsake nuppu Muuda.

Arvutusliku üksuse kindla lahtri eri valemi muutmine

1. Klõpsake lahtrit, mille valemit soovite muuta.

   Mitme lahtri valemi muutmiseks hoidke klahvi CTRL allavajutatuna ja klõpsake lisalahtreid.

2. Tippige valemiribale valemi muudatused.

   Näpunäide.: Kui teil on mitu arvutuslikku üksust või valemit, saate arvutamise järjestust täpsustada. Lisateavet leiate jaotisest Mitme arvutusliku üksuse või valemi arvutamise järjestuse täpsustamine.

1. Tehke kindlaks, kas valem on arvutuslikul väljal või arvutuslikus üksuses.

   Arvutuslikud väljad kuvatakse PivotTable-liigendtabeli väljaloendis. Arvutuslikud üksused kuvatakse teiste väljade sees olevate üksustena.

2. Tehke ühte järgmistest.

   • Arvutusliku välja kustutamiseks klõpsake PivotTable-liigendtabelit.

   • Arvutusliku üksuse kustutamiseks klõpsake PivotTable-​​liigendtabelis välja, mis sisaldab üksust, mille soovite kustutada.

     Kuvatakse PivotTable-liigendtabeli tööriistade menüü ja lisatakse menüüd Analüüs ja Kujundus.

3. Klõpsake menüü Analüüs jaotises Arvutused valikut Väljad, üksused ja komplektid ning seejärel valikut Arvutuslik väli või Arvutuslik üksus.

   

4. Valige väljal Nimi väli või üksus, mille soovite kustutada.

5. Klõpsake käsku Kustuta.