Jei skaitote šią pamoką, yra didelė tikimybė, kad girdėjote apie „Excel“ suvestinę lentelę (ar netgi ją naudojote). Tai viena iš galingiausių „Excel“ funkcijų (nejuokaukite).
Geriausia „Pivot“ lentelės naudojimo dalis yra ta, kad net jei nieko nežinote „Excel“, vis tiek galite su ja nuveikti nuostabių dalykų, labai gerai ją suprasdami.
Pradėkime.
Paspauskite čia atsisiųsti pavyzdinius duomenis ir sekti toliau.
Kas yra „Pivot Table“ ir kodėl jums tai turėtų rūpėti?
„Pivot Table“ yra „Microsoft Excel“ įrankis, leidžiantis greitai apibendrinti didžiulius duomenų rinkinius (keliais paspaudimais).
Net jei esate visiškai naujas „Excel“ pasaulyje, galite lengvai naudoti „Pivot Table“. Sukurti ataskaitas taip paprasta, kaip vilkti ir numesti eilučių/stulpelių antraštes.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Tai yra pardavimo duomenys, kuriuos sudaro ~ 1000 eilučių.
Jame yra pardavimo duomenys pagal regioną, mažmenininko tipą ir klientą.
Dabar jūsų viršininkas gali norėti sužinoti keletą dalykų iš šių duomenų:
- Kokie buvo pardavimai Pietų regione 2016 m.?
- Kokie yra penki mažmenininkai pagal pardavimus?
- Kaip „The Home Depot“ našumas buvo lyginamas su kitais mažmenininkais pietuose?
Galite atsakyti į šiuos klausimus naudodami „Excel“ funkcijas, bet ką daryti, jei staiga jūsų viršininkas pateikia dar penkių klausimų sąrašą.
Turėsite grįžti prie duomenų ir kurti naujas formules kiekvieną kartą, kai pasikeičia.
Čia „Excel Pivot“ lentelės yra tikrai naudingos.
Per kelias sekundes „Pivot Table“ atsakys į visus šiuos klausimus (kaip sužinosite toliau).
Tačiau tikroji nauda yra ta, kad ji gali sutalpinti jūsų smulkų duomenų valdomą viršininką, nedelsdama atsakydama į jo klausimus.
Tai taip paprasta, taip pat galite skirti kelias minutes ir parodyti savo viršininkui, kaip tai padaryti patiems.
Tikimės, kad dabar turite idėją, kodėl „Pivot“ lentelės yra tokios nuostabios. Eikime į priekį ir sukurkite „Pivot“ lentelę naudodami duomenų rinkinį (parodyta aukščiau).
„Pivot“ lentelės įterpimas į „Excel“
Štai veiksmai, kaip sukurti suvestinę lentelę naudojant aukščiau pateiktus duomenis:
- Spustelėkite bet kurią duomenų rinkinio vietą.
- Eikite į Įterpti -> Lentelės -> Pivot Table.
- Dialogo lange Sukurti suvestinę lentelę daugeliu atvejų numatytosios parinktys veikia gerai. Štai keletas dalykų, kuriuos reikia patikrinti:
- Lentelė/diapazonas: Pagal numatytuosius nustatymus jis užpildomas pagal jūsų duomenų rinkinį. Jei jūsų duomenyse nėra tuščių eilučių/stulpelių, „Excel“ automatiškai nustatys teisingą diapazoną. Jei reikia, tai galite pakeisti rankiniu būdu.
- Jei norite sukurti „Pivot“ lentelę konkrečioje vietoje, parinktyje „Pasirinkite, kur norite įdėti„ PivotTable “ataskaitą“, nurodykite vietą. Priešingu atveju sukuriamas naujas darbalapis su „Pivot Table“.
- Spustelėkite Gerai.
Kai tik spustelėsite Gerai, sukuriamas naujas darbalapis su „Pivot Table“.
Nors sukurta „Pivot“ lentelė, joje nematysite jokių duomenų. Viskas, ką matytumėte, yra „Pivot Table“ pavadinimas ir vienos eilutės instrukcija kairėje, o „Pivot Table“ laukai - dešinėje.
Prieš pradėdami analizuoti duomenis naudodami šią „Pivot“ lentelę, suprasime, kas yra veržlės ir varžtai, kurie sudaro „Excel“ suvestinę lentelę.
„Excel“ suvestinės lentelės veržlės ir varžtai
Norint efektyviai naudoti „Pivot Table“, svarbu žinoti komponentus, kurie sukuria suvestinę lentelę.
Šiame skyriuje sužinosite apie:
- „Pivot Cache“
- Vertybių sritis
- Eilučių sritis
- Stulpelių sritis
- Filtrų sritis
„Pivot Cache“
Kai tik sukuriate „Pivot“ lentelę naudodami duomenis, kažkas atsitinka užpakalinėje sistemoje. „Excel“ fotografuoja duomenis ir saugo juos savo atmintyje. Ši nuotrauka vadinama „Pivot Cache“.
Kai kuriate skirtingus rodinius naudodami „Pivot Table“, „Excel“ negrįžta prie duomenų šaltinio, o naudoja „Pivot Cache“, kad greitai analizuotų duomenis ir pateiktų suvestinę/rezultatus.
Pagrindinė suvestinės talpyklos generavimo priežastis yra optimizuoti suvestinės lentelės veikimą. Net jei turite tūkstančius duomenų eilučių, suvestinė lentelė labai greitai apibendrina duomenis. Galite vilkti ir numesti elementus į eilučių/stulpelių/verčių/filtrų laukus ir rezultatai bus nedelsiant atnaujinti.
Pastaba: vienas „Pivot“ talpyklos trūkumas yra tai, kad jis padidina jūsų darbaknygės dydį. Kadangi tai yra šaltinio duomenų kopija, kuriant suvestinę lentelę, tų duomenų kopija saugoma „Pivot“ talpykloje.
Skaityti daugiau: Kas yra „Pivot“ talpykla ir kaip ją geriausiai naudoti.
Vertybių sritis
Vertybių sritis yra tai, kas saugo skaičiavimus/vertes.
Remdamiesi pamokos pradžioje rodomu duomenų rinkiniu, jei norite greitai apskaičiuoti bendrą kiekvieno mėnesio pardavimą pagal regioną, galite gauti suvestinę lentelę, kaip parodyta žemiau (kaip tai sukurti, pamatysime vėliau pamokoje) .
Oranžine spalva paryškinta sritis yra vertybių sritis.
Šiame pavyzdyje jis turi bendrą kiekvieno mėnesio pardavimus keturiuose regionuose.
Eilučių sritis
Antraštės, esančios vertybių srities kairėje, sudaro eilučių sritį.
Žemiau esančiame pavyzdyje eilučių srityje yra regionai (paryškinti raudonai):
Stulpelių sritis
Sritys „Vertės“ viršuje esančios antraštės sudaro stulpelių sritį.
Žemiau esančiame pavyzdyje stulpelių srityje yra mėnesiai (paryškinti raudonai):
Filtrų sritis
Filtrų sritis yra pasirenkamas filtras, kurį galite naudoti norėdami išsamiau išnagrinėti duomenų rinkinį.
Pvz., Jei norite matyti tik „Multiline“ mažmenininkų pardavimus, galite pasirinkti šią parinktį išskleidžiamajame meniu (paryškinti žemiau esančiame paveikslėlyje), o „Pivot“ lentelė bus atnaujinta tik su „Multiline“ mažmenininkų duomenimis.
Duomenų analizė naudojant „Pivot Table“
Dabar pabandykime atsakyti į klausimus naudodami mūsų sukurtą „Pivot“ lentelę.
Paspauskite čia atsisiųsti pavyzdinius duomenis ir sekti toliau.
Norėdami analizuoti duomenis naudodami „Pivot“ lentelę, turite nuspręsti, kaip norite, kad duomenų santrauka atrodytų galutiniame rezultate. Pvz., Galbūt norėsite visų kairėje esančių regionų ir visų pardavimų šalia jo. Turėdami omenyje šį aiškumą, galite tiesiog nuvilkti atitinkamus „Pivot“ lentelės laukus.
Skiltyje „Pivot Tabe Fields“ turite laukus ir sritis (paryškintas žemiau):
Laukai kuriami remiantis „Pivot“ lentelėje naudojamais užpakalinės sistemos duomenimis. Skiltyje „Sritys“ galite įdėti laukus, o atsižvelgiant į tai, kur yra laukas, jūsų duomenys atnaujinami „Pivot“ lentelėje.
Tai paprastas vilkimo ir nuleidimo mechanizmas, kuriame galite tiesiog vilkti lauką ir įdėti jį į vieną iš keturių sričių. Kai tik tai padarysite, jis bus rodomas darbalapio suvestinėje lentelėje.
Dabar pabandykime atsakyti į jūsų vadovo klausimus naudodami šią „Pivot Table“.
1 klausimas: koks buvo bendras pardavimas Pietų regione?
Vilkite lauką Regionas srityje Eilutės ir lauką Pajamos srityje Vertės. Jis automatiškai atnaujintų darbalapio „Pivot“ lentelę.
Atminkite, kad kai tik išmesite lauką Pajamos srities Vertės, jis taps pajamų suma. Pagal numatytuosius nustatymus „Excel“ sumuoja visas tam tikro regiono reikšmes ir parodo bendrą sumą. Jei norite, galite tai pakeisti į skaičių, vidurkį ar kitą statistikos metriką. Šiuo atveju suma yra tai, ko mums reikėjo.
Atsakymas į šį klausimą būtų 21225800.
K2 Kokie yra penki geriausi mažmenininkai pagal pardavimus?
Vilkite lauką Klientas eilutės srityje ir lauką Pajamos reikšmių srityje. Jei srities skiltyje yra kitų laukų ir norite jį pašalinti, tiesiog pasirinkite jį ir vilkite iš jo.
Gausite „Pivot“ lentelę, kaip parodyta žemiau:
Atminkite, kad pagal numatytuosius nustatymus prekės (šiuo atveju klientai) yra surūšiuotos abėcėlės tvarka.
Norėdami gauti penkių mažmenininkų sąrašą, galite tiesiog surūšiuoti šį sąrašą ir naudoti penkis populiariausius klientų vardus. Padaryti tai:
- Dešiniuoju pelės mygtuku spustelėkite bet kurį langelį srities Vertės.
- Eikite į Rūšiuoti -> Rūšiuoti nuo didžiausio iki mažiausio.
Tai suteiks jums surūšiuotą sąrašą, pagrįstą visais pardavimais.
3 klausimas: kaip „Home Depot“ našumas buvo lyginamas su kitais mažmenininkais pietuose?
Galite atlikti daug šio klausimo analizės, tačiau pabandykime palyginti pardavimus.
Vilkite regiono lauką eilučių srityje. Dabar vilkite lauką Klientas srityje Eilutės po lauku Regionas. Kai tai padarysite, „Excel“ suprastų, kad pirmiausia norite suskirstyti duomenis į kategorijas pagal regioną, o paskui į regionų klientus. Turėsite kažką, kaip parodyta žemiau:
Dabar nuvilkite lauką Pajamos į vertybių sritį ir turėsite kiekvieno kliento (taip pat ir viso regiono) pardavimus.
Galite rūšiuoti mažmenininkus pagal pardavimo duomenis atlikdami šiuos veiksmus:
- Dešiniuoju pelės mygtuku spustelėkite langelį, kuriame yra bet kurio mažmenininko pardavimo vertė.
- Eikite į Rūšiuoti -> Rūšiuoti nuo didžiausio iki mažiausio.
Tai iš karto surūšiuotų visus mažmenininkus pagal pardavimo vertę.
Dabar galite greitai nuskaityti Pietų regioną ir nustatyti, kad „The Home Depot“ pardavimai buvo 3004600 ir tai sekėsi geriau nei keturi mažmenininkai Pietų regione.
Dabar yra daugiau nei vienas būdas kačiuką nulupti. Regioną taip pat galite įdėti į filtravimo sritį ir tada pasirinkti tik pietinį regioną.
Paspauskite čia atsisiųsti pavyzdinius duomenis.
Tikiuosi, kad ši pamoka suteiks jums pagrindinę „Excel“ suvestinių lentelių apžvalgą ir padės jums pradėti ją naudoti.
Čia yra dar keletas „Pivot Table“ vadovėlių, kurie jums gali patikti:
- Šaltinio duomenų ruošimas „Pivot Table“.
- Kaip taikyti sąlyginį formatavimą „Excel“ suvestinėje lentelėje.
- Kaip grupuoti datas „Excel“ suvestinėse lentelėse.
- Kaip grupuoti skaičius „Pivot“ lentelėje „Excel“.
- Kaip filtruoti duomenis „Pivot“ lentelėje „Excel“.
- Pjaustyklių naudojimas „Excel“ suvestinėje lentelėje.
- Kaip pakeisti tuščias langelius nuliais „Excel“ suvestinėse lentelėse.
- Kaip pridėti ir naudoti „Excel“ suvestinės lentelės apskaičiuotus laukus.
- Kaip atnaujinti „Pivot“ lentelę „Excel“.
