Exceli tabelitega dokumentide näited

PivotTable-liigendtabel on võib-olla kõige kasulikum tööriist Excelis. Selle abiga on palju võimalusi suure hulga andmete analüüsimiseks ja kiireks arvutamiseks.

Mis on Exceli liigendtabelid? Exceli mannekeenide liigendtabelid

PivotTables on Exceli tööriist suurte andmete kogumiseks ja analüüsimiseks.

Oletame, et meil on 1000-realine tabel müügiks klientide kaupa aastaks:

Tabel näitab:

  • Tellimuste kuupäevad;
  • Piirkond, kus klient asub;
  • Kliendi tüüp;
  • Klient;
  • Müügiarv;
  • Tulud;
  • Kasum.

Kujutame nüüd ette, et meie juht on seadnud ülesanded arvutamiseks:

  • TOP viis klienti tulude järgi;

Nendele küsimustele vastuse leidmiseks võite kasutada erinevaid funktsioone ja valemeid. Aga mis siis, kui nende andmete jaoks pole kolm, vaid kolmkümmend ülesannet? Iga kord, kui peate valemeid ja funktsioone muutma ning kohandama igat tüüpi arvutust.

See on just juhtum, kus PivotTable-tööriist muutub teie jaoks hädavajalikuks abistajaks. Selle abiga saate tabeli andmete kohaselt vastata mis tahes küsimusele mõne sekundiga.

Loodan, et mõistsite ülaltoodud näites, kui kasulik ja vajalik on pöördtabel. Mõelgem välja, kuidas neid kasutada.

Kuidas teha Excelis pöördtabelit

Excelis pöördtabeli loomiseks toimige järgmiselt.

  • Valige tabelist ükskõik milline lahter koos andmetega, mille põhjal soovite pöördtabeli teha;
  • Klõpsake vahekaarti „Sisesta” \u003d\u003e „Pivot Table”:

  • Hüpikdialoogis määrab süsteem automaatselt andmepiirid, mille põhjal saate luua liigendtabeli. See meetod töötab enamikul juhtudel vaikimisi. Soovitan iga kord pöördtabeli loomisel veenduda, et süsteem on selle loomise parameetrid õigesti määranud:
    - Tabel või vahemik: süsteem tuvastab pöördtabeli loomiseks automaatselt andmepiirid. Need on korrektsed tingimusel, et tabeli päistes ja ridades pole tühikuid. Andmevahemikku saate vastavalt vajadusele reguleerida.
  • Süsteem loob vaikimisi tabeli Exceli faili uuel vahelehel. Kui soovite selle luua kindlas kohas konkreetsel lehel, saate veerus „Olemasoleval lehel” määrata loodavad piirid.



  • Klõpsake nuppu "OK".

Pärast nupul „OK” klõpsamist luuakse pöördtabel.

Kui pöördtabel on loodud, ei näe te lehel andmeid. Saadaval on ainult pöördtabeli nimi ja menüü kuvatavate andmete valimiseks.



Nüüd, enne kui hakkame andmeid analüüsima, teen ettepaneku mõista, mida pivot-tabeli iga väli ja ala tähendavad.

Pivoti tabeli piirkonnad Excelis

Pöördtabelite tõhusaks kasutamiseks on oluline põhjalikult teada, kuidas need töötavad ja millest see koosneb.

Lisateave allpool olevate piirkondade kohta:

  • Pöördtabeli vahemälu
  • Väärtuste ala
  • Stringide ala
  • Veergude ala
  • Filtrite ala

Mis on pöördtabeli vahemälu

PivotTable-liigendtabeli loomisel loob Excel vahemälu (kiire juurdepääsuga vahepealne andmepuhver, mis sisaldab teavet, mida kõige tõenäolisemalt nõutakse) andmetest, millest tabel ehitatakse.

Loodud tabeli arvutuste tegemisel ei viita Excel iga kord algandmetele, vaid kasutab vahemälu teavet. See funktsioon vähendab märkimisväärselt andmete töötlemiseks ja arvutamiseks kulutatud süsteemiressursside hulka.

Oluline on meeles pidada, et andmete vahemälu suurendab Exceli faili suurust.

Väärtuste ala

Väärtuste ala sisaldab kõiki tabeli väärtusi ja seda saab kuvada pöördtabeli põhikomponendina. Kujutage ette, et soovime piirkondade müüki kuvada kuude kaupa (artikli alguses toodud näite järgi) pöördtabeli abil. Varjutatud väärtused kollane alloleval pildil ja on väärtused, mille me täpsustame pöördlaud piirkonnas Väärtused.



Ülalolevas näites oleme loonud pöördtabeli, mis kuvab müügiandmeid regioonide kaupa kuude kaupa.

Stringide ala

Väärtustest vasakul asuvad tabeli päised on read. Meie näites on need piirkondade nimed. Alloleval ekraanipildil on read punase värviga esile tõstetud:



Veergude ala

Tabeli väärtuste ülaosas olevaid pealkirju nimetatakse veergudeks.

Allpool toodud näites on väljad “Veerud” esile tõstetud punasega, meie puhul on need kuude väärtused.



Filtrite ala

Piirkond "Filtrid" on valikuline ja võimaldab teil määrata pöördetabeli andmete üksikasjalikkuse taseme. Näiteks võime filtrina määrata andmed „Kliendi tüüp” - „Toidupood” ja Excel kuvab tabelis andmeid, mis puudutavad ainult toidupoode.



Exceli Exceli tabelid. Näited

Allpool toodud näidetes uurime, kuidas kasutada pöördtabeleid, et vastata kolmele küsimusele selle artikli algusest peale:

  • Kui suur on Põhja piirkonna 2017. aasta tulude maht?
  • TOP viis klienti tulude järgi;
  • Milline on Ludnikovi IP-kliendi koht Vostoki piirkonnas tulude osas?

Enne andmete analüüsimist on oluline otsustada, kuidas peaksid tabeli andmed välja nägema (milliseid andmeid märkida veergudesse, ridadesse, väärtustesse, filtritesse). Näiteks kui peame kuvama klientide müügiandmeid regioonide kaupa, siis peaksime panema piirkondade nimed ridadesse, kuud veergudesse, müügiväärtused väljale „Väärtused“. Kui teil on idee lõpliku pöördetabeli nägemise kohta, alustage selle loomist.

Aken "PivotTable-liigendtabeli väljad" sisaldab pivot-tabelis paigutamise väärtustega alasid ja välju:

Väljad luuakse pöördtabeli jaoks kasutatud algandmete põhjal. Jaotises Pindalad saate väljad paigutada ja vastavalt sellele, millisele alale väli pannakse, värskendatakse teie andmeid pöördetabelis.

Väljade liikumine piirkonnast piirkonda on mugav liides, kus liikumisel värskendatakse pöördtabelis olevaid andmeid automaatselt.



Nüüd proovime vastata juhi küsimustele selle artikli algusest allpool toodud näidete abil.

Näide 1. Kui palju on Põhja piirkonnas tulu?

Põhja piirkonna müügimahu arvutamiseks soovitan paigutada pöördetabelisse kõigi piirkondade müügiandmed. Selleks vajame:

  • looge pöördtabel ja viige väli "Piirkond" alale "Read";
  • asetage väli “Tulud” alale “Väärtused”

Saame vastuse: Põhja piirkonna müük on 1 233 006 966 ₽:



Näide 2. Müügiklientide viis parimat

  • liigutage pöördtabelis väli „Klient” alale „Read”;
  • asetage väli „Tulud“ alale „Väärtused“;
  • määrake väärtusnumbrivorming tabeli lahtrite pööramiseks väärtustega.

Meil on järgmine pöördtabel:



Vaikimisi sorteerib Excel tabelis olevad andmed tähestikulises järjekorras. Andmete sortimiseks müügimahu järgi toimige järgmiselt.

  • minge menüüsse “Sorteeri” \u003d\u003e “Sorteeri kahanevalt”:



Selle tulemusel saame sorditud klientide nimekirja tulude järgi.



Näide 3. Millise koha võtab tulude osas Ludnikovi IP-klient Vostoki piirkonnas?

Koha arvutamiseks Vostoki piirkonnas asuva kliendi Ludnikovi IP tulu mahu järgi teen ettepaneku luua pöördtabel, mis kuvaks selle piirkonna piirkondade ja klientide tulude andmed.

Selle jaoks:

  • asetage pöördtabeli väli „Piirkond” alale „Read”;
  • pane väli „Klient“ väljale „Stringid“ välja „Piirkond“ alla;
  • määrake rahalise numbri vorming väärtustega lahtritele.

Niipea kui panete väljad „Piirkond” ja „Klient” üksteise alla alale „Read”, saab süsteem aru, kuidas soovite andmeid kuvada, ja soovitab sobiva valiku.

  • väli “Tulu” paigutatakse alale “Väärtused”.

Selle tulemusena saime pöördtabeli, mis kajastab klienditulu andmeid igas piirkonnas.



Andmete sortimiseks toimige järgmiselt.

  • paremklõpsake pöördtabeli mis tahes tulude andmereal;
  • minge menüüsse “Sorteeri” \u003d\u003e “Sorteeri kahanevalt”:



Saadud tabelis saame kindlaks teha, millise koha võtab Ludnikovi IP klient kõigi Vostoki piirkonna klientide seas.



Selle probleemi lahendamiseks on mitu võimalust. Saate välja „Region” teisaldada alale „Filtrid” ja paigutada klientide müügiandmed ridadesse, kajastades seega andmeid ainult Ida regiooni klientide tulude kohta.

Kui te pole kunagi varem arvutustabelit dokumentide loomiseks kasutanud, soovitame lugeda meie Exceli juhendit mannekeenide jaoks.

Seejärel saate luua oma esimese tabeli, graafikute, matemaatikavalemite ja vormindusega arvutustabeli.

Üksikasjalik teave põhifunktsioonide ja -võimaluste kohta lauaprotsessor MS Excel. Dokumendi põhielementide kirjeldus ja juhised nendega töötamiseks meie materjalis.



Rakkudega töötamine. Polsterdus ja vormindamine

Enne konkreetsete toimingute jätkamist peate sellest aru saama põhielement mis tahes Exceli dokument. Exceli fail koosneb ühest või mitmest väikesteks lahtriteks jagatud lehest.

Lahter on mis tahes Exceli aruande, arvutustabeli või graafiku põhikomponent. Iga lahter sisaldab ühte teabeplokki. See võib olla number, kuupäev, valuuta, mõõtühik või muu andmevorming.

Lahtri täitmiseks klõpsake sellel lihtsalt kursorit ja sisestage vajalik teave. Varem täidetud lahtri muutmiseks topeltklõpsake sellel.

Joonis: 1 - näide rakkude täitmisest

Igal lehe lahtril on oma ainulaadne aadress. Seega saate sellega teha arvutusi või muid toiminguid. Lahtrile klõpsamisel ilmub akna ülaossa väli selle aadressi, nime ja valemiga (kui lahter on seotud arvutustega).

Valige lahter "Aktsiate osakaal". Selle asukoha aadress on A3. See teave on näidatud avaneval atribuutide paneelil. Näeme ka sisu. Sellel lahtril pole valemeid, nii et neid ei kuvata.

Rohkem lahtrite omadusi ja funktsioone, mida sellega seoses saab kasutada, on saadaval kontekstimenüüs. Klõpsake manipulaatori parema klahviga lahtril. Avaneb menüü, millega saate lahtrit vormindada, sisu analüüsida, erineva väärtuse määrata ja muid toiminguid teha.

Joonis: 2 - lahtri kontekstimenüü ja selle peamised omadused

Andmete sortimine

Sageli seisavad kasutajad silmitsi Exceli lehel andmete sorteerimisega. See funktsioon aitab teil kogu tabelist kiiresti valida ja vaadata ainult soovitud andmeid.

Enne kui olete juba täidetud tabel (me loeme selle artiklis hiljem välja, kuidas seda luua). Kujutage ette, et soovite sortida jaanuari andmed kasvavas järjekorras. Kuidas sa seda teeksid? Laua banaalne ümbertüüpimine on lisatöö, pealegi, kui see on mahukas, ei tee seda keegi.

Excelis on sortimiseks spetsiaalne funktsioon. Kasutaja peab ainult:

  • Valige tabel või teabeplokk;
  • Avage vahekaart "Andmed";
  • Klõpsake ikooni "Sort";

Joonis: 3 - vahekaart "Andmed"

  • Valige avanevas aknas tabeli veerg, mille kohal teeme toiminguid (jaanuar).
  • Edasi sorteerimistüüp (rühmitame väärtuse järgi) ja lõpuks järjestus kasvab.
  • Kinnitage toiming, klõpsates nuppu "OK".

Joonis: 4 - sortimisparameetrite seadmine

Andmed sorteeritakse automaatselt:

Joonis: 5 - veerus "Jaanuar" olevate numbrite sorteerimise tulemus

Sarnaselt saate sortida värvi, fondi ja muude parameetrite järgi.

Matemaatilised arvutused

Exceli peamine eelis on võime tabeli täitmise käigus automaatselt arvutada. Näiteks on meil kaks lahtrit väärtustega 2 ja 17. Kuidas saaksime nende tulemuse sisestada kolmandasse lahtrisse, ilma et ise arvutusi teeksime?

Selleks peate klõpsama kolmandal lahtril, kuhu sisestatakse arvutuste lõpptulemus. Seejärel klõpsake funktsiooniikoonil f (x), nagu on näidatud alloleval joonisel. Valige avanevas aknas toiming, mida soovite rakendada. SUM on summa, AVERAGE on keskmine jne. Täielik nimekiri funktsioonid ja nende nimed Exceli redaktoris leiate Microsofti ametlikust veebisaidilt.

Peame leidma kahe lahtri summa, nii et klõpsame nuppu "SUM".

Joonis: 6 - funktsiooni "SUM" valik

Funktsiooni argumentide aknal on kaks välja: "Number 1" ja "Number 2". Valige esimene väli ja klõpsake lahtril numbriga "2". Selle aadress kirjutatakse argumentide stringi. Klõpsake nuppu "Number 2" ja klõpsake lahtrit numbriga "17". Seejärel kinnitage toiming ja sulgege aken. Kui peate tegema matemaatikat kolme või enama kastiga, sisestage argumentide väärtused lihtsalt numbritesse 3, 4 ja nii edasi.

Kui tulevikus summeeritud lahtrite väärtus muutub, värskendatakse nende summat automaatselt.

Joonis: 7 - arvutuste tulemus

Tabelite loomine

Mis tahes andmeid saab Exceli tabelitesse salvestada. Kiire seadistamise ja vormindamise funktsiooni abil on redaktoris väga lihtne organiseerida isikliku eelarve juhtimissüsteemi, kulude loetelu, digitaalsed andmed aruandluse jaoks ja nii edasi.

Exceli tabelid on Wordi ja muude kontoriprogrammide sarnase valiku ees ülimuslikud. Siin on teil võimalus luua mis tahes mõõtmetega tabel. Andmed täidetakse lihtsalt. Sisu redigeerimiseks on funktsioonipaneel. Lisaks, valmis laud saab integreerida docx-faili tavalise copy-paste funktsiooni abil.

Tabeli loomiseks järgige juhiseid.

  • Klõpsake vahekaarti Lisa. Valige suvandite vasakul küljel tabel. Kui teil on vaja mingeid andmeid kokku võtta, valige üksus "Pivot table";
  • Valige lehel hiirega koht, mis tabeli jaoks eraldatakse. Samuti saate elemendi loomise aknasse sisestada andmete asukoha;
  • Toimingu kinnitamiseks klõpsake nuppu OK.

Joonis: 8 - standardtabeli loomine

Vormindamiseks välimus tulemuseks oleva plaadi avage konstruktori sisu ja klõpsake väljal "Stiil" soovitud malli. Soovi korral saate luua oma välimuse erineva värviskeemi ja lahtrivalikuga.

Joonis: 9 - tabeli vormindamine

Tabeli andmetega täitmise tulemus:

Joonis: 10 - täidetud laud

Iga tabeli lahtri jaoks saate kohandada ka andmetüüpi, vormingut ja teabe kuvamise režiimi. Kujundusaken sisaldab kõiki vajalikke võimalusi plaadi edasiseks konfigureerimiseks vastavalt teie vajadustele.

Graafikute / diagrammide lisamine

Diagrammi või graafiku koostamiseks on vaja valmis plaati, sest graafilised andmed põhinevad täpselt üksikutelt joontelt või lahtritelt saadud informatsioonil.

Diagrammi / graafiku loomiseks peate:

  • Valige tabel täielikult. Kui peate looma graafilise elemendi ainult andmete kuvamiseks teatud rakud, valige ainult need;
  • Avage sisestamiskaart;
  • Valige soovitatud diagrammikastis ikoon, mis teie arvates kirjeldab tabeliteavet visuaalselt kõige paremini. Meie puhul on see 3-D sektordiagramm. Viige kursor ikooni juurde ja valige elemendi välimus;

    • Samamoodi saate luua hajutatavaid jooniseid, joondiagramme ja tabeli elementide sõltuvusi. Kõiki saabunud graafilisi elemente saab lisada ka tekstidokumendid Sõna.

    Exceli arvutustabeli redaktoris on palju muid funktsioone, kuid esialgse töö jaoks piisab selles artiklis kirjeldatud tehnikatest. Dokumendi loomise käigus valdavad paljud kasutajad iseseisvalt täpsemaid valikuid. Selle põhjuseks on kasutajasõbralik ja intuitiivne liides. uusimad versioonid programmid.

    Temaatilised videod:

Exceli pöördtabel on võimas andmeanalüüsi tööriist, mis aitab teil kiiresti:

  • Andmete ettevalmistamine aruannete jaoks;
  • Arvutage erinevad näitajad;
  • Grupi andmed;
  • Filtreerige ja analüüsige huvipakkuvaid mõõdikuid.

Ja säästate ka palju aega.

Selles artiklis saate teada:

  • Kuidas teha pöördlauda;
  • Pöördtabeli kasutamine grupi aegrida ja hinnata andmeid dünaamikas aastate, kvartalite, kuude, päevade kaupa ...
  • Kuidas arvutada prognoosi pöördtabeli ja Forecast4AC PRO abil;

Kõigepealt õpime pöördtabelite koostamist.

Pöördtabeli koostamiseks peame andmed ehitama lihtsa tabeli kujul. Igas veerus peab olema 1 parsitud parameeter. Näiteks on meil 3 veergu:

  • Toode
  • Müük rublades

Ja sisse iga rida 3. parameetrid on seotud, s.t. näiteks 01.01.2010 müüdi toodet 1 hinnaga 422 656 rubla.

Kui olete pöördtabeli andmed ette valmistanud, määrake kursor esimesse veergu lihtsa tabeli esimesse lahtrisse, seejärel minge menüüsse "Lisa" ja vajutage nuppu "Pivot table"

Ilmub dialoogiboks, milles:

  • sa saad vajutage kohe nuppu "OK", ja pöördtabelit kuvatakse eraldi lehel.
  • või saate konfigureerida pöördtabeli andmete väljundi parameetrid:
  1. Vahemik koos pöördtabelis kuvatavate andmetega;
  2. Kus pöördtabelit kuvada (uue või olemasoleva lehe jaoks (kui valite olemasoleva, peate määrama lahtri, kuhu soovite pöördtabeli paigutada)).


Klõpsake nuppu "OK", pöördtabel on valmis ja kuvatakse uuel lehel. Helistame lehele Pivot.

  • Lehe paremal küljel näete väljad ja alad, millega saate töötada. Võite lohistada väljad aladele ja need kuvatakse lehe pöördtabelis.
  • Lehe vasakul küljel on pöördlaud.


Nüüd hoidke hiire vasaku nupuga välja "Toode" all - lohistage see väljale "Ridade nimed", "Müük rublades". - pöördtabeli jaotises "Väärtused". Seega saime kogu perioodi kaupade kogumüügi:


Aegridade grupeerimine ja filtreerimine pöördtabelis

Kui tahame nüüd analüüsida ja võrrelda toodete müüki aastate, kvartalite, kuude, päevade kaupa, siis peame seda tegema lisage pöördtabelisse asjakohased väljad.

Selleks minge lehele "Andmed" ja sisestage pärast kuupäeva 3 tühja veergu. Valige veerg "Toode" ja klõpsake nuppu "Sisesta".

Tähtisnii et äsja lisatud veerud jäävad olemasoleva andmetega tabeli vahemikku, siis ei pea me uute väljade lisamiseks liigendit ümber tegema, piisab selle värskendamisest.

Lisatud veerud on "Aasta", "Kuu", "Aasta-Kuu".

Nüüd lisage igas veerus huvipakkuva ajaparameetri saamiseks sobiv valem:

  • Lisage valem veergu "Aasta" \u003d AASTA (viitega kuupäevale);
  • Lisage valem veergu "Kuu" \u003d KUU (viitega kuupäevale);
  • Lisage valem veergu „Aasta - kuu“ \u003d CONCATENATE (link aastale; ""; link kuule).

Saame 3 veergu aasta, kuu ning aasta ja kuuga:


Nüüd minge lehele "Kokkuvõte", asetage kursor pöördlauale, paremklõpsake menüül ja vajutage nuppu "Uuenda"... Pärast väljade loendis värskendamist oleme uued pöördtabeli väljad "Aasta", "Kuu", "Aasta - kuu", millele lisasime lihtne laud andmetega:


Nüüd analüüsime müüki aasta kaupa.

Selleks lohistame välja "Aasta" pöördtabeli "veerunimedesse". Saame tabeli müügiga toodete kaupa aastate kaupa:


Nüüd tahame "langeda" veelgi sügavamale kuude tasemele ja analüüsida müüki aastate ja kuude kaupa. Selleks lohistage aasta all olev veerg "kuu" veergude nimedesse:


Analüüsida kuude dünaamikat aastate kaupa, saame kuud liigutada pöördealale "Ridade nimed" ja saada järgmine pöördetabeli vaade:


Selles pöördtabeli vaates näeme:

  • iga toote müük kogu aasta ulatuses (vastavalt toote nimele);
  • üksikasjalikumalt iga kuu iga toote müük dünaamikas 4 aasta jooksul.

Järgmine väljakutse oleme meie soovime analüüsist välja jätta ühe kuu müügi (näiteks oktoober 2012), sest meil pole veel terve kuu müügiandmeid.
Selleks lohistage "Aasta - kuu" pöördealale "Aruandefilter"

Klõpsake ülaltoodud kokkuvõtte filtril ja märkige ruut "Vali mitu elementi"... Seejärel tühjendage aastate ja kuude arvuga loendis märkeruut 2012 10 ja klõpsake nuppu OK.


Seega saate aja muutmiseks lisada uusi parameetreid ja analüüsida neid teile huvipakkuvaid ajaintervalle ja vormis, milles seda vajate. Pöördtabel arvutab nende väljade ja filtrite näitajad, mille olete määranud ja lisate sellele huviväljana.

Pronosi arvutamine pöördtabeli ja Forecast4AC PRO abil

Ehitame müüki pöördtabeli abil toodete, aastate ja kuude kaupa. Lülitame ka põhisummad välja, et neid arvestuses ei arvestataks.

Pöördtabelis olevate kogusummade väljalülitamiseks asetage kursor veergu "Kokku" ja klõpsake nuppu "Kustuta summa kokku". Kokkuvõte kaob kokkuvõttest.



ja vajutage menüüs Forecast4AC PRO nuppu "Prognoosimudeli diagramm"

Saame prognoosi arvutamise 12 kuuks ja ilusa graafiku koos prognoosimudeli (trend, hooajalisus ja mudel) analüüsiga tegelike andmete suhtes. Forecast4AC PRO saab arvutada teie jaoks prognoose, hooajalisuse määra, trendi ja muid näitajaid ning koostada pöördtabelis kuvatud andmete põhjal graafikuid.


Exceli PivotTable-liigendtabel on võimas andmeanalüüsi tööriist, mis võimaldab teil kiiresti ja lihtsalt arvutada mõõdikuid ning koostada andmeid teid huvitavas vormis.



Soovitame lugeda