Kui loete seda õpetust, on suur tõenäosus, et olete kuulnud (või isegi kasutanud) Exceli pöördtabelit. See on Exceli üks võimsamaid funktsioone (ilma naljata).
Pivot -tabeli kasutamise parim osa on see, et isegi kui te ei tea Excelist midagi, saate selle abil väga häid asju teha, kui teil on sellest väga lihtne arusaam.
Alustame.
Kliki siia prooviandmete allalaadimiseks ja järgimiseks.
Mis on pöördlaud ja miks peaksite sellest hoolima?
Pivot tabel on Microsoft Exceli tööriist, mis võimaldab teil kiiresti (mõne klõpsuga) tohutuid andmekogumeid kiiresti kokku võtta.
Isegi kui te pole Exceli maailmas absoluutselt uus, saate hõlpsalt liigendtabelit kasutada. Aruannete loomine on sama lihtne kui ridade/veergude päiste lohistamine.
Oletame, et teil on allpool näidatud andmekogum:
See on müügiandmed, mis koosnevad ~ 1000 reast.
Sellel on müügiandmed piirkonna, jaemüüja tüübi ja kliendi järgi.
Nüüd soovib teie ülemus nendest andmetest teada saada mõnda asja:
- Milline oli Lõuna -piirkonna kogumüük 2016. aastal?
- Millised on müügi järgi viis parimat jaemüüjat?
- Kuidas võrreldi The Home Depot'i toimivust teiste Lõuna -jaemüüjatega?
Võite neile küsimustele vastuste saamiseks Exceli funktsioone kasutada, aga mis siis, kui teie ülemus esitab ootamatult veel viie küsimuse loendi.
Iga kord, kui muudatusi tehakse, peate andmete juurde naasma ja uusi valemeid looma.
Siin on Excel Pivot tabelid tõesti kasulikud.
Pivot -tabel vastab mõne sekundi jooksul kõigile neile küsimustele (nagu saate teada allpool).
Kuid tegelik kasu on see, et see mahutab teie peene andmepõhise ülemuse, vastates kohe tema küsimustele.
See on nii lihtne, võite võtta mõne minuti ja näidata oma ülemusele, kuidas seda ise teha.
Loodetavasti on teil nüüd idee, miks Pivot -tabelid on nii vinged. Jätkame ja loome andmekogumi abil Pivot tabeli (näidatud ülal).
Pivot tabeli lisamine Excelisse
Pivot -tabeli loomiseks ülaltoodud andmete abil toimige järgmiselt.
- Klõpsake andmekogus suvalises kohas.
- Avage Lisa -> Tabelid -> Pivot Table.
- Dialoogiboksis Pivot Tabeli loomine toimivad vaikesuvandid enamikul juhtudel hästi. Siin on paar asja, mida kontrollida.
- Tabel/vahemik: See on vaikimisi täidetud teie andmekogumi põhjal. Kui teie andmetel pole tühje ridu/veerge, tuvastab Excel automaatselt õige vahemiku. Vajadusel saate seda käsitsi muuta.
- Kui soovite luua liigendtabeli kindlas asukohas, määrake suvandi „Valige, kuhu soovite PivotTable -liigendtabeli aruanne paigutada” all asukoht. Muidu luuakse liigendtabeliga uus tööleht.
- Klõpsake nuppu OK.
Niipea kui klõpsate nuppu OK, luuakse uus tööleht, millel on liigendtabel.
Pivot -tabeli loomise ajal ei näe te selles andmeid. Näete ainult Pivot Tabeli nime ja ühe rea juhiseid vasakul ning Pivot Tabeli väljad paremal.
Nüüd, enne kui alustame andmete analüüsimist selle pöördetabeli abil, mõistame, millised on mutrid ja poldid, mis teevad Exceli pöördtabeli.
Exceli pöördtabeli mutrid ja poldid
Pöördtabeli tõhusaks kasutamiseks on oluline teada komponente, mis loovad liigendtabeli.
Selles jaotises saate teada järgmist.
- Pöördmälu
- Väärtuste piirkond
- Ridade ala
- Veergude ala
- Filtrite ala
Pöördmälu
Niipea, kui loote andmete abil pöördetabeli, juhtub taustprogrammis midagi. Excel teeb andmetest hetktõmmise ja salvestab need oma mällu. Seda hetktõmmist nimetatakse Pivot -vahemäluks.
Pivot -tabeli abil erinevate vaadete loomisel ei naase Excel andmeallika juurde, vaid kasutab andmete kiireks analüüsimiseks ja kokkuvõtte/tulemuste esitamiseks Pivot -vahemälu.
Pöördvahemälu genereerimise põhjus on pöördtabeli toimimise optimeerimine. Isegi kui teil on tuhandeid ridu andmeid, on pöördtabel andmete kokkuvõtte tegemisel ülikiire. Saate lohistada üksusi ridade/veergude/väärtuste/filtrite kastidesse ja see värskendab tulemusi kohe.
Märkus. Pivot -vahemälu üks negatiivne külg on see, et see suurendab teie töövihiku mahtu. Kuna see on lähteandmete koopia, salvestatakse pöördtabeli loomisel nende andmete koopia Pivot -vahemällu.
Loe rohkem: Mis on Pivot -vahemälu ja kuidas seda kõige paremini kasutada.
Väärtuste piirkond
Väärtuste ala on see, mis hoiab arvutusi/väärtusi.
Kui soovite õpetuse alguses näidatud andmekogumi põhjal kiiresti välja arvutada iga kuu kogumüügi piirkondade kaupa, saate pöördetabeli, nagu allpool näidatud (näeme, kuidas seda hiljem õpetuses luua) .
Oranžiga esile tõstetud ala on väärtuste ala.
Selles näites on selle nelja piirkonna kogumüük iga kuu.
Ridade ala
Alamväärtustest vasakul asuvad pealkirjad moodustavad ala Ridad.
Allolevas näites sisaldab ridade ala piirkondi (punasega esile tõstetud):
Veergude ala
Väärtuste ala ülaosas asuvad pealkirjad moodustavad ala Veerud.
Allolevas näites sisaldab veergude ala kuud (punasega esile tõstetud):
Filtrite ala
Filtrite ala on valikuline filter, mille abil saate andmekogumit põhjalikumalt uurida.
Näiteks kui soovite näha ainult Multiline jaemüüjate müüki, saate selle valiku teha rippmenüüst (mis on esile tõstetud alloleval pildil) ja pöördetabel värskendatakse ainult mitmerealiste jaemüüjate andmetega.
Andmete analüüsimine liigendtabeli abil
Proovime nüüd küsimustele vastata, kasutades meie loodud pöördtabelit.
Kliki siia prooviandmete allalaadimiseks ja järgimiseks.
Pivot -tabeli abil andmete analüüsimiseks peate otsustama, kuidas soovite andmete kokkuvõtet lõpptulemuses välja näha. Näiteks võite soovida kõiki piirkondi vasakul ja kogumüüki kohe selle kõrval. Kui olete seda selgust silmas pidanud, saate lihtsalt pivot -tabeli vastavad väljad lohistada.
Jaotises Pivot Tabe väljad on väljad ja alad (nagu allpool esile tõstetud):
Väljad luuakse pöördtabeli jaoks kasutatud taustaandmete põhjal. Jaotises Valdkonnad asetate väljad ja vastavalt sellele, kuhu väli läheb, värskendatakse teie andmeid pöördetabelis.
See on lihtne pukseerimismehhanism, mille abil saate lihtsalt lohistada välja ja panna selle ühte neljast alast. Niipea kui seda teete, kuvatakse see töölehe pöördtabelis.
Proovime nüüd vastata küsimustele, mis teie juhil selle pöördetabeli abil tekkisid.
Q1: Mis oli lõunapiirkonna kogumüük?
Lohistage väljal Piirkond välja Ridad ja väljal Tulud välja Väärtused. See värskendaks automaatselt töölehe pöördtabelit.
Pange tähele, et niipea, kui loobute väljalt Tulud väljalt Väärtused, muutub see tulude summaks. Vaikimisi liidab Excel kõik antud piirkonna väärtused ja näitab nende koguväärtust. Soovi korral saate selle muuta arve, keskmise või muude statistiliste näitajateks. Sel juhul on summa see, mida me vajasime.
Vastus sellele küsimusele oleks 21225800.
Q2 Millised on müügi järgi viis parimat jaemüüjat?
Lohistage väljal Klient välja Rida ja väärtuste väljal Tulu. Juhul, kui piirkonna jaotises on muid välju ja soovite selle eemaldada, valige see lihtsalt ja lohistage see sealt välja.
Saate pöördtabeli, nagu allpool näidatud:
Pange tähele, et vaikimisi on üksused (antud juhul kliendid) sorteeritud tähestikulises järjekorras.
Viie parima jaemüüja saamiseks võite selle loendi lihtsalt sortida ja kasutada viit parimat kliendinime. Selleks tehke järgmist.
- Paremklõpsake ala väärtuste mis tahes lahtrit.
- Minge Sorteeri -> Sorteeri suurimast väikseimani.
See annab teile sorteeritud nimekirja, mis põhineb kogumüügil.
Q3: Kuidas võrreldi The Home Depot'i toimivust teiste Lõuna -jaemüüjatega?
Selle küsimuse jaoks saate palju analüüsida, kuid proovime siin lihtsalt müüki võrrelda.
Lohistage piirkonna rida piirkonnas Ridad. Nüüd lohistage välja Kliendi väljal Piirkond välja all Klient. Kui te seda teete, mõistab Excel, et soovite oma andmed kõigepealt piirkondade ja seejärel piirkondade klientide järgi liigitada. Teil on midagi, nagu allpool näidatud:
Nüüd lohistage väljal Tulud välja Väärtused ja näete iga kliendi (ja ka kogu piirkonna) müüki.
Saate jaemüüjaid sorteerida müüginäitajate järgi, järgides alltoodud samme.
- Paremklõpsake lahtrit, millel on mis tahes jaemüüja müügiväärtus.
- Minge Sorteeri -> Sorteeri suurimast väikseimani.
See sorteeriks koheselt kõik jaemüüjad müügiväärtuse järgi.
Nüüd saate kiiresti lõunapiirkonda skaneerida ja tuvastada, et The Home Depot müük oli 3004600 ja see läks paremini kui neli Lõuna piirkonna jaemüüjat.
Nüüd on kassi koorimiseks rohkem kui üks viis. Piirkonna saate paigutada ka filtreerimispiirkonda ja seejärel valida ainult lõunapiirkonna.
Kliki siia prooviandmete allalaadimiseks.
Loodan, et see õpetus annab teile põhilise ülevaate Exceli liigendtabelitest ja aitab teil sellega alustada.
Siin on veel mõned Pivot Tabeli õpetused, mis võivad teile meeldida:
- Lähteandmete ettevalmistamine pöördetabeli jaoks.
- Tingimusvormingu rakendamine Exceli pöördtabelis.
- Kuupäevade rühmitamine Exceli liigendtabelites.
- Kuidas rühmitada numbreid Exceli liigendtabelis.
- Kuidas Excelis liigendtabelis andmeid filtreerida.
- Viilutajate kasutamine Exceli liigendtabelis.
- Kuidas tühjad lahtrid nullidega asendada Exceli liigendtabelites.
- Exceli liigendtabeli arvutatud väljade lisamine ja kasutamine.
- Pivot -tabeli värskendamine Excelis.
