Pivoti tabeli üksused. Mida me saadud materjaliga teeme. Kuva ja peida detailandmed

Pöördtabelite peamised elemendid. Enne selle töö jätkamist pole minu arvates üleliigne öelda, millistest elementidest pivot-tabelid koosnevad, ja selle jaoks kaaluge joonisel 2 toodud näidet. Leheväli on väli algses loendis või tabelis, mis on paigutatud lehe orientatsiooni ala pöördlaud.

Selles näites on ulatus ulatus, mida saab kasutada piirkonniti kokkuvõtete tegemiseks. Kui määrate mõne muu lehevälja elemendi, arvutatakse pöördtabel ümber, et kuvada selle elemendiga seotud kogusummad. Lehevälja väärtus - lehevälja elemendid ühendavad tabeli algloendis välja või veeru kirjed või väärtused. Teisisõnu toimub see selle välja vahetamine, tegelikult iga kord, kui toimub üleminek uue piirkonna uuele andmeallikale.

Näiteks saate määratleda vormingu mitme andmetaseme jaoks, pöördtabelis on mitu vahesummade taset jne. Nagu ka tabelistiilid, saate ka oma stiilid luua vastavalt oma konkreetsetele vajadustele, olgu see siis ettevõtte juhised või individuaalsed eelistused. Pivot-tabelid on aga keerukamad kui tabelid, seega on vorminduse määratlemiseks saadaval rohkem tabeli elemente.

Näiteks saate määratleda vormingute arvu vahesummade mitmele tasemele, saate määratleda liigendtabelis vaheldumise erinevatel tasanditel. Tuletatud atribuudid - saab kasutada olemasolevatest tuletatud algsele andmekogumile uute atribuutide lisamiseks. Selle väärtus võib olla kas atribuudi võti või loodud atribuudi nimi. Kui see on määratud arvuks, siis kui atribuutide nimede pikkus tähemärkides ületab seda arvu, kuvatakse atribuudid vertikaalselt.

  • Ainult muutumatu pöördlaua jaoks.
  • Ainult redigeeritava pöördtabeli jaoks.
  • Kui kirjete arv ületatakse, kuvatakse vastav teade.
PivotTable-aruande abil saate kokkuvõtlikke andmeid analüüsida, uurida ja esitada.

Riduväljad on algses loendis või tabelis olevad väljad, mis paigutatakse PivotTable-liigendtabeli reaalale. Selles näites on tooted ja hankija stringiväljad. Rea siseväljad, nt Müügimees, vastavad täpselt andmealale. Rida välised väljad, nt Tooted, rühmitavad sisemised väljad. Seega jälgitakse siin kindlat reeglit, kõrgema taseme välja iga element täiustab justkui kõiki madalama taseme välju, toote parameeter piirab kõiki selle all olevaid andmeid - tootemüüjaid, andmeid nende müüjate toodete müügi kohta. Veerguväli on allikasloendi või tabeli väli, mis asetatakse veerualale.

Kui teil on erinev versioon, võib teie välimus olla veidi erinev, kuid kui pole teisiti öeldud, on funktsionaalsus sama. PivotTable-liigendtabel on interaktiivne viis suurte andmemahtude kiireks kokkuvõtmiseks. Pöördtabeli abil saate arvandmeid üksikasjalikult analüüsida ja oma andmete kohta ootamatutele küsimustele vastata. Pöördlaud on spetsiaalselt loodud.

Alamtüüp ja arvandmete liitmine, andmete kokkuvõte kategooriate ja alamkategooriate kaupa ning kohandatud arvutuste ja valemite loomine. Laiendage ja ahendage andmekihte, et keskenduda oma tulemustele ja uurida kokkuvõtlikest andmetest huvipakkuvaid üksikasju. Algandmete erinevate kokkuvõtete nägemiseks teisaldage read veergudesse või veerud ridadesse. Filtreerimine, sortimine, rühmitamine ja tingimuslik vormindamine kõige kasulikum ja huvitavam andmete alamhulk, mis võimaldab teil keskenduda ainult soovitud teabele. Lühikeste, köitvate ja märkustega veebiaruannete või trükitud aruannete esitamine. Suure hulga andmete küsimine mitmel kasutajasõbralikul viisil. ... Näiteks on siin vasakul majapidamiskulude lihtne loetelu ja paremal oleva loendi põhjal pöördtabel.

Selles näites on kvartalid veeruvälja, mis sisaldab kahte väljaelementi KB2 ja KB3. Veergude siseväljad sisaldavad elemente, vastavad andmepiirkonnad on siseväljade kohal asuvad veergude välised väljad. Näites on näidatud ainult üks veeruvälja.

Andmeala on PivotTable-i osa, mis sisaldab kokkuvõtlikke andmeid. Andmeala lahtrites kuvatakse ridade või veergude väljade üksuste kogusumma. Andmeala igas lahtris olevad väärtused vastavad algandmetele. Selles näites võtab lahter C6 kokku kõik toorandmete kirjed, mis sisaldavad sama tootenime, turustajat ja kindlat kvartalit Meat, Myastorg LLP ja KB2. Väljaelemendid on PivotTable-välja välja alamkategooriad. Selles näites on väärtused Liha ja mereannid välja üksused väljal Tooted.

Pöördlauaga töötamise viisid. Pärast PivotTable-liigendtabeli loomist, selle andmeallika valimist, PivotTable-liigendtabeli väljade loendi väljade korrastamist ja esialgse paigutuse valimist saate PivotTable-liigendtabeli aruandega töötades teha järgmisi ülesandeid. Uurige andmeid, järgides neid samme.

Andmete laiendamine ja ahendamine ning väärtustega seotud põhiteabe kuvamine.

  • Väljade ja üksuste sortimine, filtreerimine ja rühmitamine.
  • Kokkuvõtte funktsioonide muutmine ja kohandatud arvutuste ja valemite lisamine.
Muutke vormi paigutust ja väljade paigutust, järgides neid samme.

Väljaüksused tähistavad lähteandmete välja või veeru kirjeid. Väljad kuvatakse rea või veeru pealkirjadena ja leheväljade rippmenüüs. Andmeväli on andmeallika väljade loend või loend. Selles näites on väli Tellimuse summa andmeväli, mis võtab kokku algsed andmed väljal või veerus Tellimuse summa. Andmeväli võtab tavaliselt kokku arvude rühma, näiteks statistika või müügikogused, kuigi praegused andmed võivad olla ka tekstilised.

  • Väljade lisamine, muutmine ja eemaldamine.
  • Muutke väljade või üksuste järjekorda.
Veergude, ridade ja vahesummade paigutuse muutmiseks toimige järgmiselt. Veergude ja ridade pealkirjade sisse- või väljalülitamine või tühjade ridade kuvamine või peitmine.
  • Näita vahesummasid nende ridade kohal või all.
  • Korrigeerige värskendamisel veeru laiusi.
  • Teisaldage veeruvälja reaalale või reaväli veerualale.
  • Lahtrite ühendamine või laiendamine väliste ridade ja veergude jaoks.
Muutke tühikute ja vigade kuvamist, järgides neid samme.

Vaikimisi võtab PivotTable-liigendtabel kokku tekstiandmed, kasutades funktsiooni Koguväärtused kokku, ja arvandmed, kasutades funktsiooni Kokkuvõte. Pöördtabeli struktuuri muutmine Välimus Pivot-tabelit saate redigeerida otse lehel, lohistades väljanuppude või väljaüksuste nimesid. Samuti saate muuta väljal elementide järjestust, mille jaoks peate valima elemendi nime ja seejärel määrama kursori lahtri piirile.

  • Muutke vigade ja tühjade lahtrite kuvamise viisi.
  • Muutke üksuste ja siltide ilma andmeteta kuvamise viisi.
Muutke vormingut, järgides neid samme.
  • Vormindage lahtrid ja vahemikud käsitsi ja tingimuslikult.
  • Muutke pöördtabeli vormingu üldist stiili.
  • Muutke väljade numbrivormingut.
Samuti saate muuta diagrammi tüüpi ja muid parameetreid, nagu pealkirjad, legendide paigutus, andmesildid, diagrammi asukoht ja palju muud. Siiski on mõningaid erinevusi. Trendijooni, andmesilte, vearibasid ja muid andmekogumite muudatusi ei salvestata.

Kui kursor muutub nooleks, lohistage väli lahter uude asukohta. Välja eemaldamiseks lohistage välja nuppu üksikasjade alast väljapoole. Välja kustutamine peidab liigendtabelis kõik sõltuvad väärtused, kuid ei mõjuta algseid andmeid. Kui peate pöördtabeli struktureerimiseks kasutama kõiki pakutavaid tööriistu või kui praegune tabel ei ole varem kõiki lähteandmete välju sisaldanud, peaksite kasutama pöördetabeli viisardit.

Standarddiagrammid ei kaota seda vormingut pärast selle rakendamist. Iga järgnevate ridade lahter peab sisaldama andmeid, mis vastavad veeru päisele, ja te ei tohi segada andmetüüpe samas veerus. Näiteks ei tohiks ühes veerus valuuta väärtusi ja kuupäevi segada. Lisaks ei tohi andmevahemikus olla tühje ridu ega veerge. Kui nimetatud vahemik laieneb ja sisaldab rohkem andmeid, sisaldab pivot-tabeli värskendus uusi andmeid. Kui teie lähteandmed sisaldavad automaatseid vahesummasid ja põhisummasid, mille olete loonud vahekaardi Andmed rühmas Kontuur käsu Kokku abil, eemaldage enne pivot-tabeli loomist sama käsu vahesummade ja põhisummade eemaldamiseks.

Kui pöördtabel sisaldab suurt rühma lehevälju, saab need paigutada ridadesse või veergudesse. Lisaks tuleb märkida, et PivotTable-liigendtabeli struktuuri muutmine ei mõjuta algandmeid.

Töö lõpp -

See teema kuulub jaotisse:

Tabeliteabe töötlemine pöördtabelite abil Microsoft Exceli abil

Ridade ja veergude vahetamisega saate kuvades luua algandmetest uued kogusummad erinevatel lehtedel saate filtreerida andmeid ja ... Teisisõnu, need tabelid võimaldavad teil kombineerida andmeid erinevatest allikatest, ... Pivot-tabeli loomine Enne pivot-tabeli loomist peate kõigepealt määrama selle tabeli andmed, .. .

Andmeid saate hankida välisest andmeallikast, näiteks andmebaasist, veebianalüüsi kuubist või tekstifail... Näiteks saate salvestada müügikirjete andmebaasi, mida soovite kokku võtta ja analüüsida. Lisateavet leiate jaotisest PivotTable-liigendtabeli üksuste teisendamine töölehe valemiteks. Näiteks andmed relatsioonide andmebaasidest või tekstifailidest.

Teise pöördtabeli kasutamine andmeallikana

Iga uus pöördetabel nõuab täiendavat mälu ja kettaruumi. Kui kasutate samas töövihikus uue allikana olemasolevat pöördtabelit, kasutavad mõlemad sama vahemälu. Vahemälu taaskasutamisel väheneb töövihiku maht ja mällu jääb vähem andmeid. Nõuded asukohale Ühe PivotTable-liigendtabeli kasutamiseks teise allikana peavad mõlemad olema samas töövihikus. Kui algne PivotTable-liigendtabel on teises töövihikus, kopeerige allikas töövihiku asukohta, kuhu soovite uue ilmuda.

Kui vajate selle teema kohta lisamaterjali või kui te ei leidnud otsitut, soovitame kasutada otsingut meie tööde baasis:

Mida me saadud materjaliga teeme:

Kui see materjal osutus teie jaoks kasulikuks, saate selle oma suhtlusvõrgustike lehele salvestada:

Kõik selles jaotises olevad teemad:

Pöördtabeli loomine
Looge pöördtabel. Enne pöördtabeli loomist peate kõigepealt määrama selle tabeli andmed, valides osa raamatust, lehed ja kui see on andmebaas või loend, siis n

Muudatused mõjutavad pöördtabeleid. Liikmete rühmitamisel või grupist vabastamisel või arvutatud väljade või arvutatud liikmete loomisel ühes on see mõlema mõjutatud. Kui vajate teisest sõltumatut pöördtabelit, saate algse pöördetabeli kopeerimise asemel uue luua algse andmeallika põhjal.

Olemasoleva PivotTable-liigendtabeli aruande lähteandmete muutmine

Lihtsalt teadke sellega seotud potentsiaalsetest mäluprobleemidest liiga sageli. Lähteandmete muutmise tulemuseks võib olla analüüsimiseks saadaval olevate andmete kättesaadavus. Näiteks saate hõlpsalt prooviandmebaasilt tootmisandmebaasile üle minna.

PivotTable-liigendtabeli üksikasjad ja toimingud
Pivot-tabeli andmete ja sellega tehtud toimingute üksikasjad. Üksikasjad on pöördtabeli alamkategooria. Need pöördtabeli üksused on unikaalsed tabeli üksused või koos

PivotTable-liigendtabeli üksikasjalike andmete kuvamine või peitmine
PivotTable-liigendtabeli üksikasjalike andmete kuvamine või peitmine. Varem peidetud üksikasjalike andmete kuvamiseks või olemasolevate andmete peitmiseks peate valima väljaelemendi üksikasjalikud andmed

Kuvab värskenduse käivitatud uued andmed. Pöördtabeli värskendamine võib muuta ka kuvamiseks saadaolevaid andmeid. Saate vaadata kõiki uusi väljad väljade loendis ja lisada aruandesse väljad. Probleem on selles, et stardiinfos ei tehta vahet kahe aasta vahel, vaid seal on lihtsalt kuupäevaga väli. Lahendame selle kahel viisil.

Esiteks kasutame maatriksivalemeid ja teiseks teeme seda, luues pöördtabelisse arvutatud üksuse. Saate vaadata postitust, millest rääkisime. Alustame väikese kaheveergulise andmebaasiga.

  • Kokku 24 kirjet Summa.
  • See on mingi majandusliku väärtuse väärtus.
  • See võib olla näiteks ettevõtte arveldamine.
Resolutsioon maatriksvalemitega.

PivotTable-liigendtabeli väljaüksuste kuvamine või peitmine
PivotTable-liigendtabeli välja elementide kuvamine või peitmine. Kui on vaja väljaelementi kuvada või peita, siis selleks tuleb kõigepealt valida väli, kus seda on vaja kuvada või

Maksimaalse või minimaalse üksuse kuvamine PivotTable-väljal
Kuvab PivotTable-väljal maksimaalse või minimaalse üksuse. Maksimaalsete või minimaalsete elementide kuvamiseks on vaja valida väli, kus elemente tulevikus näidatakse.

Samal lehel 1 lahendame oma juhtumi maatriksi funktsiooni abil. Selliste funktsioonidega töötamise kohta saate teada järgmisest postitusest. Valem on suletud lokkidega, mis näitab, et see on maatriksvalem. See kopeeritakse ja iga kuu kohta saadakse variatsiooniprotsent.

Lisasime aasta ja kuu veerud. Uute väljade lisamine andmebaasi, mis arvutatakse juba andmebaasis endas sisalduva teabega, pole eriti õigeusklik. Need kehtiva kuupäeva suhtes valemid annavad meile täpselt selle kuupäeva kuu ja aasta.

Keela pöördtabeli üksikasjadele juurdepääs
Keela pöördtabeli üksikasjadele juurdepääs. Kui paigutate kursori PivotTable-liigendtabeli andmealal asuva lahtri kohale ja topeltklõpsate, kuvatakse lähteandmete kokkuvõtlik loend

Andmete rühmitamine ja grupeerimise tühistamine pöördtabelis
Andmete rühmitamine ja grupeerimise tühistamine pöördtabelis. Pöördtabelis saate rühmitada kuupäevad, kellaajad, numbrid ja valitud üksused. Näiteks võite ruutude kokkuvõtmiseks kombineerida kuid.

Sel põhjusel vajasime veergu Kuupäev kehtivaks kuupäevaks ja pidime võtma iga kõnealuse kuu esimese päeva. Kehtiv kuupäev peaks näitama päeva, kuud ja aastat, kuid siis küsime meie antud vormingus ainult kuud ja aastat. Need kehtivad, sest kuigi me ei näe päeva, on see vorming, kuid kuupäev on sisestatud päev-kuu-aasta.

Kõigepealt loome pöördtabeli, nagu allpool näidatud. Kuu sildid paneme rea siltidesse, aasta veergude veergudesse ja summade andmed väärtustesse. Nüüd saame arvutatud üksuse sisestada. Ilmub aken, kuhu saame oma disainielemendi ehitada.

Rühmitage ja grupeerige üksused PivotTable-väljal
Rühmitage ja grupeerige üksused PivotTable-väljal. Elementide rühmitamise ja rühmitamise lõpetamiseks peate valima rühmitatud elemendid. Seejärel klõpsake paneelil nuppu Rühm

Rühmitage ja grupeerige numbrid pöördtabelis
Rühmitage ja grupeerige liigendtabelis numbrid. Numbrite rühmitamiseks ja rühmitamise lõpetamiseks valige kast kastis, kuhu soovite üksused rühmitada. Seejärel vajutage nuppu

Kuid me ei pea valemit kirjutama aastaid tippides, vaid elemente hiirega valides. Uus veerg on vormindatud protsentidena ja oleme juba loonud oma pöördtabeli koos arvutatud üksusega. Saame luua nimekirja kõigi valemitega, mis on loodud pöördtabelis nii arvutatud üksuste kui ka arvutatud väljade kohta. Selleks asetage kursor PivotTable-liigendtabelisse ja valige PivotTable-liigendtabeli tööriistad, valemid ja valemite loomise loend.

See loob uue lehe, kuhu saame soovitud loendi. Andmesegmenteerimine pakub nuppe, millel saate PivotTable-liigendtabeli andmete filtreerimiseks klõpsata. Lisaks kiirele filtreerimisele näitavad andmesegmendid ka praegust filtreerimise olekut, mis muudab filtreeritud PivotTable-liigendtabeli aruandes täpsemalt kuvamise lihtsamaks. Üksus, kui see on valitud, lisatakse filtrisse ja selle üksuse andmed kuvatakse aruandes.

Kuupäevad või kellaajad rühmitage ja grupeerimine pöördtabelisse
Kuupäevad või kellaajad rühmitage ja grupeerimine pöördtabelisse. Kuupäeva või kellaaja rühmitamiseks ja rühmitamise lõpetamiseks valige väljal kuupäev või kellaaeg, kuhu üksus tuleks grupeerida.

Pivot-tabeli lehe väljaüksuste rühmitamine ja grupeerimise lõpetamine
PivotTable-liigendtabeli lehevälja elementide rühmitamine ja rühmitamise lõpetamine. Kui pöördtabel loodi välisest andmeallikast, kontrollige, kas leheväli tõmbab väliseid andmeid


Värskendage või muutke pöördtabeli andmeallikat. Töö käigus tekib mõnikord olukord, kui ilmuvad uued andmed, mis tuleb loodud dokumenti sisestada, ja

Andmete värskendamine pöördtabelis
Andmete värskendamine pöördtabelis. Pivot-tabeli andmete värskendamiseks peate tegema järgmised toimingud. Pivot-tabelis on vaja valida lahter, mille sisu on vajalik

Keela töövihiku faili avamisel pöördtabeli värskendamine
Keela töövihiku faili avamisel pöördtabeli värskendamine. Pivot-tabeli värskendamise keelamiseks faili avamisel valige liigendtabelis lahter, mille värskendamist ei toimu

Pöördtabeli andmete sortimine
Pöördtabeli andmete sortimine. PivotTable-liigendtabeli väljaüksused sorteeritakse automaatselt kasvavas järjekorras vastavalt nende nimele. Üksuste sortimine nime järgi võimaldab teil originaali taastada

Pivot-tabeli andmete kokkuvõtmine ja töötlemine
Liigendtabeli andmete kokku võtmine ja töötlemine. Liigendtabeli andmevälju saab konfigureerida muude arvutuste jaoks kui vaikefunktsioonis Vaikimisi. Et täita keeruline

Põhisummade ja vahesummade kasutamine pöördtabelis
Põhisummade ja vahesummade kasutamine pöördtabelis. Liigendtabeli saab konfigureerida arvutuste tegemiseks kokkuvõtte funktsiooni abil. Kokkuvõtte funktsioon on arvutuse tüüp, mille teostab

Sisestage või eemaldage vahesummad pöördtabelisse
Sisestage või eemaldage vahesummad pöördtabelisse. Vahesummade sisestamiseks või kustutamiseks peate määrama kursori selle välja nupule, kuhu soovite

Looge pöördtabeli diagramm
Looge pöördtabeli diagramm. Pivot-tabeli diagrammi loomiseks valige PivotTables-tööriistaribal PivotTable-menüüst käsk Select. Kontrollige seda

Selles postituses esitatakse Exceli lihtsate ja vahva PivotTable-tööriistade kogu. Mida inglise keeles nimetatakse näpunäideteks Võtke natuke aega ja lugege nõuandeid siit. Kes teab, äkki leiad lõpuks vastuse küsimusele, mis on teid pikka aega piinanud?

Nõuanne 1. Pivot-tabelite automaatne värskendamine

Mõnikord soovite, et pöördtabeleid värskendataks automaatselt. Oletame, et olete halduri jaoks loonud pöördtabeli. On ebatõenäoline, et saate seda regulaarselt uuendada, välja arvatud juhul, kui haldur laseb teid sülearvuti juurde. Saate lubada automaatne värskendamine pöördtabel, mida käivitada töövihiku avamisel:

  1. Paremklõpsake pöördtabelis ja valige Liigtabeli valikud.
  2. Ilmuvas dialoogiboksis Liigtabeli valikud valige vaheleht Andmed.
  3. Märkige ruut Värskenda, kui fail on avatud.

Joon. 1. Lubage suvand Värskenda, kui fail on avatud

Märkeruut Värskenda, kui fail on avatud peaks olema paigaldatud iga pöördlaua jaoks eraldi.

Laadige märkus alla vormingus või näited vormingus (fail sisaldab VBA-koodi).

Vihje 2: Värskendage samaaegselt kõiki töövihiku pöördetabeleid

Kui töövihik sisaldab mitut pöördtabelit, võib nende samaaegne värskendamine olla problemaatiline. Nende raskuste ületamiseks on mitu võimalust:

Meetod 1. Iga töövihikusse kuuluva pöördtabeli jaoks saate valida sätte, mida värskendatakse töövihiku avamisel automaatselt (lisateavet vt 1. näpunäide).

3. meetod: VBA-koodi abil värskendage töövihikus kõiki pöördetabeleid nõudmisel. See lähenemine kasutab töövihiku objekti RefreshAll meetodit. Selle tehnika kasutamiseks looge uus moodul ja sisestage järgmine kood:

Alamvärskenda_Kõik ()

ThisWorkbook.RefreshAll

Vihje 3. Sorteeri üksused juhuslikus järjekorras

Joonisel fig. Joonisel 2 on näidatud vaikekorraldus, milles piirkonnad kuvatakse PivotTable-liigendtabelis. Piirkonnad on järjestatud tähestiku järgi: lääne, põhja, kesk-lääne ja lõuna. Kui teie ettevõtte reeglid nõuavad, et kuvaksite esmalt läänepiirkonna ning seejärel kesk-, põhja- ja lõunapiirkonnad, tehke käsitsi sortimine. Sisestage lahtrisse C4 lihtsalt Midwest ja vajutage klahvi Sisenema... Piirkondade sortimisjärjestus muutub.


Vihje 4: Teisendage PivotTable-vorming kõvakooditud väärtusteks

Pöördtabeli loomise eesmärk on andmete kokkuvõte ja kuvamine sobiv formaat... PivotTable-liigendtabeli algandmed salvestatakse eraldi, mis toob sisse mõned "üldkulud". Pöördtabeli teisendamine väärtusteks võimaldab teil selles saadud tulemusi kasutada ilma algandmetele või pöördtabeli vahemälule juurde pääsemata. Pivot-tabeli teisendamine sõltub sellest, kas see mõjutab kogu tabelit või ainult selle osa.

PivotTable-liigendtabeli osa teisendamiseks toimige järgmiselt.

  1. Valige kopeeritud PivotTable-liigendtabeli andmed, paremklõpsake ja valige Kopeeri (või tippige klaviatuuril Ctrl + C).
  2. Paremklõpsake töölehe suvalises kohas ja valige käsk Kleepige (või sisestage Ctrl + V).

Kui peate teisendama kogu PivotTable-liigendtabeli, toimige järgmiselt.

  1. Valige kogu pöördtabel, paremklõpsake ja valige Kopeeri... Kui PivotTable-liigendtabel ei sisalda ala FILTRID, saate PivotTable-liigendtabeli valimiseks kasutada klaviatuuri otseteed Ctrl + Shift + *.
  2. Paremklõpsake lehel suvalises kohas ja valige kontekstimenüüst suvand Kleepige spetsiaalseks.
  3. Valige suvand Väärtused ja klõpsake nuppu Okei.

Enne PivotTable-liigendtabeli teisendamist on mõistlik kustutada vahesummad, kuna need pole võrguühenduseta andmekogumis eriti kasulikud. Kõigi vahesummade kustutamiseks minge menüüs Kujundus -\u003e Vahesummad -\u003e Ära näita vahesummasid. Konkreetsete vahesummade kustutamiseks paremklõpsake lahtrit, milles kogusummad arvutatakse. Valige üksus kontekstimenüüst Välja parameetrid ja dialoogis Välja parameetrid jaotises Tulemused valige raadionupp Mitte... Pärast nupule klõpsamist Okei vahesummad kustutatakse.

Vihje 5. Tühjade lahtrite täitmine väljadel LINE

Pärast PivotTable-faili teisendamist kuvab leht mitte ainult PivotTable-liigendtabeli väärtused, vaid kogu andmestruktuuri. Näiteks joonisel fig. 3 tuletati tabelpaigutusega pöördtabelist.


Joon. 3. Probleemne on selle teisendatud pöördtabeli kasutamine vasakul küljel tühje lahtreid täitmata

Pange tähele, et väljad Piirkond ja Müügiturg säilitab sama reastruktuuri nagu siis, kui need andmed leitakse pöördtabeli ROWS alalt. Excel 2013 on kiire viis rakkude täitmine piirkonnas LINE väärtustega. Klõpsake pöördtabeli alal ja seejärel menüüst läbi Ehitaja -> Aruande paigutus -> (joonis 4). Seejärel saate pöördtabeli teisendada väärtusteks, mis annab teile tühikuteta andmetabeli.


Joon. 4. Pärast käsu rakendamist Korrake kõiki üksuste silte kõik tühjad lahtrid on täidetud

Vihje 6: PivotTable-liigendtabeli numbriväljade järjestamine

Suurt hulka andmeüksusi sisaldavate väljade sorteerimisel ja järjestamisel ei ole alati lihtne kindlaks määrata analüüsitava andmeüksuse numbrilist järjestust. Pealegi, kui pöördtabel teisendatakse väärtusteks, hõlbustab igale andmeelemendile täisarvuväljal kuvatava numbrilise järjestuse määramine genereeritud andmekogumi analüüsi. Avage joonisel fig. 5. Pange tähele, et sama näitaja - Välja Müügimaht summa - kuvatakse kaks korda. Paremklõpsake mõõdiku teisel eksemplaril ja valige Täiendavad arvutused -> Sorteerimine maksimumist miinimumini (joonis 6.)

Pärast auastme loomist saate kohandada väljade silte ja vormingut (joonis 14.9). Tulemuseks on kena järjestatud aruanne.



Vihje 7: PivotTable-liigendtabeli aruande suuruse vähendamine

PivotTable-liigendtabeli aruande loomisel teeb Excel andmetest hetktõmmise ja salvestab need PivotTable-liigendtabeli vahemällu. Pöördtabeli vahemälu on spetsiaalne mälupiirkond, mis salvestab juurdepääsu kiirendamiseks andmeallika koopia. Teisisõnu loob Excel koopia andmetest ja salvestab need siis töövihikuga seotud vahemällu. Pöördtabeli vahemälu pakub töövoo optimeerimist. PivotTable-liigendtabelis tehtud muudatused, näiteks väljade paigutuse muutmine, uute väljade lisamine või üksuste peitmine, on kiiremad ja süsteemiressursinõuded on palju tagasihoidlikumad. PivotTable-i vahemälu peamine puudus on see, et see peaaegu kahekordistab töövihiku faili suuruse iga kord, kui PivotTable-liigendtabeli nullist luuakse.

Kustutage algandmed.Kui töövihik sisaldab originaalset andmekogumit ja pöördtabelit, kahekordistatakse selle faili suurus. Seetõttu saate algandmed turvaliselt kustutada ja see ei mõjuta teie pöördtabeli funktsionaalsust üldse. Pärast algandmete kustutamist tuleb kindlasti salvestada töövihiku faili tihendatud versioon. Pärast algandmete kustutamist saate pöördtabelit kasutada nagu tavaliselt. Ainus probleem on algteabe puudumise tõttu suutmatus pöördtabelit värskendada. Kui vajate taustandmeid, topeltklõpsake rea ja veeru ristumiskohas üldkogude piirkonnas (joonisel 7 on see lahter B18). Seejuures puistab Excel pöördtabeli vahemälu sisu uude töölehele.

Vihje 8: looge automaatselt laiendatav andmevahemik

Kindlasti olete korduvalt kokku puutunud olukordadega, kui pidite PivotTable-i aruandeid igapäevaselt värskendama. Seda on kõige sagedamini vaja, kui andmeallikasse lisatakse pidevalt uusi kirjeid. Sellistel juhtudel peate enne uute kirjete lisamist uude PivotTable-liigendtabelisse varem kasutatud vahemiku uuesti määratlema. PivotTable-liigendtabeli algse andmevahemiku ümbermääramine on lihtne, kuid sageli seda tehes muutub see üsna tüütuks.

Probleemi lahenduseks on algse andmevahemiku teisendamine tabeliks juba enne pöördtabeli loomist. Tänu exceli arvutustabelid saate luua nimelise vahemiku, mis võib automaatselt laieneda või kokku tõmmata, sõltuvalt selles olevast andmemahust. Samuti saate linkida mis tahes komponendi, diagrammi, liigendtabeli või valemi vahemikuga, et saaksite jälgida andmekogumi muudatusi.

Kirjeldatud tehnika juurutamiseks valige lähteandmed ja klõpsake siis vahekaardil asuvat tabeliikooni Sisesta (joonis 8) või vajutage Ctrl + T (inglise T). Klõpsake nuppu Okei avanevas aknas. Pange tähele, et kuigi te ei pea liigendtabelis lähteandmete vahemikku uuesti määratlema, peate klõpsama nuppu, kui lisate lähteandmed pöördtabelis vahemikku. Värskenda.

9. näpunäide: võrrelge tavalisi tabeleid pöördlauaga

Kui võrdlete kahte erinevat tabelit, on mugav kasutada pöördtabelit, mis säästab palju aega. Oletame, et teil on kaks tabelit, mis näitavad kliendi üksikasju 2011. ja 2012. aasta kohta (joonis 9). Nende tabelite väikesed mõõtmed on siin toodud ainult näidetena. Praktikas kasutatakse palju suuremaid tabeleid.


Võrdlus loob ühe tabeli, millest luuakse pöördtabel. Veenduge, et teil oleks võimalus nende tabelitega seotud andmed märgistada. Selles näites kasutab see veergu Eelarveaasta (joonis 10). Pärast kahe tabeli ühendamist kasutage saadud kombineeritud andmekogumit uue PivotTable-liigendtabeli loomiseks. Vormige PivotTable-liigendtabel nii, et PivotTable-veeru ala kasutataks tabeli märgendina (identifikaator, mis näitab tabeli päritolu). Nagu on näidatud joonisel fig. 11, aastad on veerualal ja kliendi üksikasjad reaalal. Andmevaldkond sisaldab iga kliendi müügimahte.


Nõuanne 10: pöördtabeli automaatne filtreerimine

Nagu teate, ei saa PivotTablesis kasutada automaatfiltreid. Siiski on nipp, mis lubab Pilt-tabelis automaatfiltreid. Selle tehnika taga on hiirekursori paigutamine PivotTable-i viimasest päisest paremale (lahter D3 joonisel 12), seejärel minge lindile ja valige käsk Andmed -> Filtreeri... Nüüdsest kuvatakse teie pöördtabelisse automaatfilter! Näiteks saate valida kõik kliendid, kelle tehingumäär on keskmisest kõrgem. Autofiltrid lisavad PivotTable-liigendtabelisse täiendava analüütikihi.


11. näpunäide. Teisendage liigendtabelites kuvatud andmekogumid

PivotTable-liigendtabeliks teisendatud algandmete parim paigutus on tabelikujundus. Seda tüüpi paigutusel on järgmised omadused: pole tühje ridu ega veerge, igal veerul on päis, igal väljal on igas reas väärtus ja veerud ei sisalda korduvaid andmegruppe. Praktikas on sageli andmekogumeid, mis sarnanevad joonisel fig. 13. Nagu näete, kuvatakse kuunimed tabeli ülaosas reas, mis täidab veerusiltide ja tegelike andmete kahesugust funktsiooni. Sellisest tabelist loodud PivotTable-liigendtabelis oleks tulemuseks 12 hallatavat välja, millest igaüks tähistab eraldi kuud.


Selle probleemi kõrvaldamiseks võite vaheetapina kasutada mitme konsolideeritud vahemikuga liigendtabelit (vt üksikasju). Maatriksistiiliga andmekogumi teisendamiseks liigendtabelite loomiseks sobivamaks andmekogumiks toimige järgmiselt.

Samm 1. Ühendage kõik veeruvälised väljad ühte veergu.Mitme konsolideeritud vahemikuga pöördtabelite loomiseks looge üks dimensiooni veerg. Selles näites loetakse dimensiooniks kõike, mis pole kuu väljaga seotud. Seetõttu väljad Müügiturg ja Teenuse kirjeldus tuleks liita ühte veergu. Väljade ühte veergu ühendamiseks sisestage lihtsalt valem, mis liidab need kaks välja, kasutades eraldajana semikoolonit. Pange uuele veerule nimi. Sisestatud valem kuvatakse valemiribal (joonis 14).


Joon. 14. Kolonni liitmise tulemus Müügiturg ja Teenuse kirjeldus

Pärast liitunud veeru loomist teisendage valemid väärtusteks. Selleks valige vastloodud veerg, vajutage Ctrl + C ja käivitage käsk Kleepige -> Kleepige spetsiaalseks -> Väärtused... Veerusid saab nüüd eemaldada Müügiturg ja Teenuse kirjeldus (joonis 15).


Joon. 15. Veerud eemaldatud Müügiturg ja Teenuse kirjeldus

Samm 2. Looge mitme konsolideerimisvahemikuga pöördtabel.Nüüd peate helistama PivotTable-liigendtabeli ja diagrammi viisardile, mis on paljudele kasutajatele tuttav Exceli eelmistest versioonidest. Selle viisardi käivitamiseks vajutage klahvikombinatsiooni Alt + D + P. Kahjuks on see klaviatuuri otsetee Exceli ingliskeelse versiooni jaoks. Venekeelses versioonis vastab see klaviatuuri otseteele Alt + D + N. Kuid mulle teadmata põhjustel see ei toimi. Kiire juurdepääsu tööriistaribale saate siiski tuua vana hea PivotTable-viisardi, vt. Pärast viisardi käivitamist valige raadionupp Mitmes konsolideerimise vahemikus... Klõpsake nuppu Edasi... Seadke lüliti Looge leheväljad ja klõpsake nuppu Edasi... Määrake tööulatus ja klõpsake Valmis (Vaata detaile). Loote pöördtabeli (joonis 16).


Samm 3. Topeltklõpsake rea ja veeru ristumiskohta kogu rea real.Sel hetkel on teil pöördtabel (joonis 16), mis sisaldab mitut konsolideerimisvahemikku, mis on peaaegu kasutu. Valige lahter, mis asub rea ja veeru kogusumma ristumiskohas, ja topeltklõpsake sellel (meie näites on see lahter N88). Saate uue lehe, mille struktuur sarnaneb joonisel fig. 17. Tegelikult on see leht algandmete üle võetud versioon.


Samm 4. Veerg Rida jagatakse eraldi väljadeks.Veeru jagamine jääb alles Rida eraldi väljadele (tagasi algsele struktuurile). Lisage üks tühi veerg kohe pärast veergu Rida... Tõstke esile veerg A ja minge siis lindilehele Andmed ja klõpsake nuppu Veeru tekst... Ekraanile ilmub dialoogiboks. Teksti levitamise viisard... Esimeses etapis valige raadionupp Eraldajategaja klõpsake nuppu Järgmine. Järgmises etapis valige raadionupp semikoolon ja klõpsake nuppu Valmis... Vormindage tekst, lisage pealkiri ja muutke algandmed tabeliks, vajutades Ctrl + T (joonis 18).


Joon. 18. See andmekogum sobib ideaalselt pöördtabeli loomiseks (võrrelge joonist 13)

Nõuanne 12: lisage pöördtabelisse kaks numbrivormingut

Mõelge nüüd olukorrale, kus normaliseeritud andmekogum muudab raskesti hõlpsasti analüüsitava pöördtabeli koostamise. Näide on toodud joonisel fig. 19 on tabel, mis sisaldab kahte erinevat näitajat iga müügipinna kohta. Teatise veerg D, mis näitab mõõdikut.


Kuigi see tabel on näide mõnest päris heast vormindusest, pole see kõik veel nii hea. Pange tähele, et mõned mõõdikud tuleks kuvada numbrilises vormingus ja teised protsentides. Kuid algses andmebaasis väli Väärtus on tüüpi Double. Andmekogumist pöördtabeli loomisel ei saa te samale väljale määrata kahte erinevat numbrivormingut Väärtus... Siin on lihtne rusikareegel: üks väli vastab ühele numbrivormingule. Kui proovite väljale, millele on määratud protsendivorming, määrata numbrivorming, muutuvad protsendiväärtused tavalisteks numbriteks, mis lõpevad protsendimärgiga (joonis 20).


Selle probleemi lahendamiseks kasutatakse kohandatud numbrivormingut, mis vormindab arvuna kõik väärtused, mis on suuremad kui 1,5. Kui väärtus on väiksem kui 1,5, vormindatakse see protsentides. Dialoogiboksis Lahtrivorming valige vaheleht (kõik vormingud) ja põllul Tüüp sisestage järgmine vormindusstring (joonis 21): [\u003e \u003d 1,5] $ # ## 0; [<1,5]0,0%


Joon. 21. Rakendage kohandatud arvuvorming, kus kõik arvud, mis on väiksemad kui 1,5, on vormindatud protsentidena

Tulemus on näidatud joonisel fig. 22. Nagu näete, on nüüd iga indikaator vormindatud õigesti. Muidugi ei ole selle näpunäite retsept universaalne. Pigem näitab see katsetamise suunda.


Vihje 13: Looge liigendtabeli jaoks sageduse jaotamine

Kui olete kunagi Exceli funktsiooni abil genereerinud sagedusjaotusi Sagedus, ilmselt teate, et see on väga raske ülesanne. Pealegi tuleb pärast andmevahemike muutmist kõike otsast peale alustada. Selles jaotises saate teada, kuidas lihtsa liigendtabeli abil luua lihtsaid sagedusjaotusi. Kõigepealt looge liigendtabel, mis sisaldab andmeid reaalal. Pöörake tähelepanu joon. 23, kus väli asub reaalal Köide müük.

Paremklõpsake ükskõik millisel väärtusel piirkonnas Rows ja valige suvand kontekstimenüüst Grupp... Dialoogiboksis Grupeerimine (Joonis 24) määravad parameetrite väärtused, mis määravad sagedusjaotuse alguse, lõpu ja sammu. Klõpsake nuppu OK.

Joon. 24. Dialoogiboksis Grupeerimine reguleerida sageduse jaotuse parameetreid

Kui lisate pöördtabelisse välja Klient (Joonis 25), saame klienditehingute sageduse jaotuse tellimuste suuruse suhtes (dollarites).

Joon. 25. Nüüd on teie käsutuses klientide tehingute jaotamine vastavalt tellimuste suurusele (dollarites)

Selle tehnika eeliseks on see, et PivotTable-aruande filtrit saab kasutada andmete interaktiivseks filtreerimiseks muude veergude, näiteks Piirkond ja Müügiturg... Kasutajal on ka võimalus sageduse jaotuse intervalli kiiresti reguleerida, paremklõpsates reaala mis tahes numbril ja valides seejärel suvandi. Grupp... Esitamise selguse huvides saab lisada pöörddiagrammi (joonis 26).


Nõuanne 14. Pöördtabeli kasutamine andmekogumi levitamiseks töövihiku lehtede vahel

Analüütikud peavad sageli looma iga piirkonna, müügipiirkonna, juhi jne jaoks erinevad PivotTable-liigendtabeli aruanded. See ülesanne hõlmab tavaliselt pikka protsessi PivotTable-liigendtabeli kopeerimiseks uuele lehele ja seejärel filtri välja muutmist, et see kajastaks sobivat piirkonda ja haldurit. See protsess toimub käsitsi ja seda korratakse igat tüüpi analüüsi jaoks. Kuid tegelikult saate üksikute pöördtabelite loomise Excelisse tellida. Parameetri rakendamise tulemusena filtriväljade piirkonnas luuakse iga üksuse jaoks automaatselt eraldi pöördtabel. Selle funktsiooni kasutamiseks looge lihtsalt liigendtabel, mis sisaldab filtrivälja (joonis 27). Asetage kursor pöördtabelisse ja vahekaardile Analüüs võistkondade rühmas Pöördlaud klõpsake rippmenüül Valikud (joonis 28). Seejärel klõpsake nuppu Kuva aruandefiltri lehed.


Joon. 28. Klõpsake nuppu Kuva aruandefiltri lehed

Ilmuvas dialoogiboksis (joonis 29) saate valida filtrivälja, mille jaoks luuakse eraldi pöördtabelid. Valige sobiv filtriväli ja klõpsake nuppu Okei.

Joon. 29. Dialoogiboks Aruandefiltri lehtede kuvamine

Filtrivälja iga elemendi jaoks luuakse liigendtabel, mis asetatakse eraldi lehele (joonis 30). Pange tähele, et lehekaartide nimed on samad mis filtri välja üksustel. Pange tähele, et parameeter Kuva filtrilehed saab filtriväljadele ükshaaval rakendada.


Vihje 15: PivotTable-liigendtabeli kasutamine andmekogumi levitamiseks üksikutele töövihikutele

Näpunäites 14 kasutasime jagamiseks spetsiaalset võimalust kokkuvõtlikud tabelid müügiturgude kaupa töövihiku erinevatel lehtedel. Kui peate jagama algandmed erinevatel müügiturgudel eraldi raamatutes saate kasutada väikest VBA-koodi. Esiteks asetage väli, millele soovite filtreerida, väljade filtriväljadele. Asetage väli Müügi maht väärtuste vahemikku (joonis 31). Soovitatud VBA-kood valib iga FILTER-i üksuse järjest ja kutsub funktsiooni Näita detaileuue andmelehe loomine. Seejärel salvestatakse see leht uude töövihikusse

Kood VBA.

Alam ExplodeTable ()

Dim PvtItem PivotItemina

Dim PvtTable kui PivotTable

Hämar väli PivotFieldina

'Muutke muutujaid vastavalt skriptile

ConststrFieldName \u003d "Turg"<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

'Muutke pöördtabeli nime (vajadusel)

SetPvtTable \u003d ActiveSheet.PivotTables ("PivotTable1") "<—Изменение названия сводной

‘Sirvige valitud välja kõiki elemente

Iga PvtItem'i jaoks PvtTable.PivotFields (strFieldName) .PivotItems

PvtTable.PivotFields (strFieldName). CurrentPage \u003d PvtItem.Name

Vahemik (strTriggerRange). ShowDetail \u003d True

'Nime määramine ajutisele lehele

ActiveSheet.Name \u003d "TempSheet"

'Andmete kopeerimine uude töövihikusse ja ajutise lehe kustutamine

ActiveSheet.Cells.Copy

ActiveSheet. Kleebi

Cells.EntireColumn.AutoFit

Application.DisplayAlerts \u003d Vale

ActiveWorkbook.SaveAs _

Faili nimi: \u003d ThisWorkbook.Path & "\\" & PvtItem.Name & ".xlsx"

ActiveWorkbook. Sule

Sheets ("Tempsheet"). Kustuta

Application.DisplayAlerts \u003d Tõsi

Sisestage see kood uude VBA moodulisse. Kontrollige järgmiste konstantide ja muutujate väärtusi ning vajadusel muutke neid:

  • Const strFieldName. Andmete eraldamiseks kasutatava välja nimi. Teisisõnu, see on väli, mis sobib PivotTable-liigendtabeli filtri / lehtede alasse.
  • Const strTriggerRange. Käivitusrakk, mis salvestab PivotTable-liigendtabeli andmealalt ühe numbri. Meie puhul on päästikrakk A4 (vt joonis 31).

VBA-koodi käivitamise tulemusena salvestatakse iga müügipiirkonna andmed eraldi töövihikusse.

Märkus, mis põhineb Jelen raamatul Aleksander. ... 14. peatükk.



Soovitatav lugeda