Turinys
„Pivot“ lentelės yra vienas iš galingiausių Excel įrankių. Jie leidžia analizuoti ir apibendrinti įvairias didelių duomenų kiekių suvestines vos keliais pelės paspaudimais. Šiame straipsnyje mes susipažinsime su suvestinės lentelėmis, suprasime, kas tai yra, išmoksime jas kurti ir pritaikyti.
Šis straipsnis buvo parašytas naudojant „Excel 2010“. „PivotTable“ sąvoka bėgant metams beveik nepasikeitė, tačiau kiekvienoje naujoje „Excel“ versijoje jų kūrimo būdas šiek tiek skiriasi. Jei turite „Excel“ versiją, o ne 2010, būkite pasiruošę, kad šiame straipsnyje pateiktos ekrano kopijos skirsis nuo to, ką matote ekrane.
Šiek tiek istorijos
Pirmosiomis skaičiuoklių programinės įrangos dienomis „Lotus 1-2-3“ taisyklė. Jos dominavimas buvo toks visiškas, kad Microsoft pastangos sukurti savo programinę įrangą (Excel) kaip alternatyvą Lotus atrodė laiko švaistymas. Dabar greitai pirmyn į 2010 m.! „Excel“ skaičiuoklėse dominuoja labiau nei „Lotus“ kodas kada nors padarė per savo istoriją, o „Lotus“ vis dar naudojančių žmonių skaičius yra artimas nuliui. Kaip tai galėjo atsitikti? Kokia buvo tokio dramatiško įvykių posūkio priežastis?
Analitikai išskiria du pagrindinius veiksnius:
- Pirma, „Lotus“ nusprendė, kad ši nauja GUI platforma, vadinama „Windows“, tėra trumpalaikė mada. Jie atsisakė kurti Windows „Lotus 1-2-3“ versiją (bet tik kelerius metus), numatydami, kad jų programinės įrangos DOS versija bus viskas, ko vartotojams kada nors prireiks. „Microsoft“ natūraliai sukūrė „Excel“ specialiai „Windows“.
- Antra, „Microsoft“ programoje „Excel“ pristatė įrankį „PivotTables“, kurio nebuvo „Lotus 1-2-3“. „PivotTables“, išskirtinai „Excel“, pasirodė esąs tokios nepaprastai naudingos, kad žmonės buvo linkę laikytis naujojo „Excel“ programinės įrangos rinkinio, o ne tęsti „Lotus 1-2-3“, kuriame jų nebuvo.
„PivotTables“ kartu su „Windows“ sėkmės apskritai neįvertinimu suvaidino „Lotus 1-2-3“ mirties žygį ir paskatino „Microsoft Excel“ sėkmę.
Kas yra suvestinės lentelės?
Taigi, koks yra geriausias būdas apibūdinti, kas yra „PivotTable“?
Paprastai tariant, suvestinės lentelės yra kai kurių duomenų santraukos, sukurtos siekiant palengvinti šių duomenų analizę. Skirtingai nuo rankiniu būdu sukurtų sumų, „Excel PivotTables“ yra interaktyvios. Sukūrę galite lengvai juos modifikuoti, jei jie nesuteikia tokio vaizdo, kokio tikėjotės. Vos keliais pelės paspaudimais sumos gali būti apverstos taip, kad stulpelių antraštės taptų eilučių antraštėmis ir atvirkščiai. Naudodami sukimosi lenteles galite atlikti daugybę dalykų. Užuot bandę žodžiais apibūdinti visas suvestinių lentelių ypatybes, lengviau tai pademonstruoti praktiškai...
Duomenys, kuriuos analizuojate naudodami „PivotTable“, negali būti atsitiktiniai. Tai turėtų būti neapdoroti neapdoroti duomenys, pavyzdžiui, tam tikras sąrašas. Pavyzdžiui, tai gali būti įmonės pardavimų sąrašas per pastaruosius šešis mėnesius.
Pažvelkite į toliau esančiame paveikslėlyje pateiktus duomenis:
Atminkite, kad tai nėra neapdoroti neapdoroti duomenys, nes jie jau buvo apibendrinti. B3 langelyje matome 30000 30000 USD, o tai tikriausiai yra bendras Jameso Cooko rezultatas sausio mėnesį. Kur tada pirminiai duomenys? Iš kur atsirado XNUMX XNUMX USD suma? Kur yra pradinis pardavimų, iš kurių buvo gauta ši mėnesio suma, sąrašas? Akivaizdu, kad kažkas puikiai sutvarkė ir surūšiavo visus paskutinio šešių mėnesių pardavimų duomenis ir pavertė juos į mūsų matomą sumų lentelę. Kaip manote, kiek laiko užtruko? Valanda? Dešimta valanda?
Faktas yra tas, kad aukščiau pateikta lentelė nėra sukimosi lentelė. Jis buvo sukurtas rankomis iš kitur saugomų neapdorotų duomenų ir užtruko mažiausiai porą valandų. Tiesiog tokią suvestinę lentelę galima sukurti naudojant suvestinės lenteles vos per kelias sekundes. Išsiaiškinkime, kaip…
Jei grįžtume prie pradinio pardavimo sąrašo, jis atrodytų maždaug taip:
Galbūt nustebsite, kad iš šio sandorių sąrašo su pivot lentelių pagalba ir vos per kelias sekundes galime sukurti mėnesinę pardavimų ataskaitą Excel programoje, kurią analizavome aukščiau. Taip, mes galime tai ir dar daugiau!
Kaip sukurti suvestinę lentelę?
Pirmiausia įsitikinkite, kad „Excel“ lape turite šaltinio duomenų. Finansinių operacijų sąrašas yra tipiškiausias pasitaikantis. Tiesą sakant, tai gali būti bet ko sąrašas: darbuotojo kontaktiniai duomenys, kompaktinių diskų rinkinys arba jūsų įmonės degalų sąnaudų duomenys.
Taigi, paleidžiame Excel… ir įkeliame tokį sąrašą…
Atidarę šį sąrašą programoje „Excel“, galime pradėti kurti suvestinę lentelę.
Pasirinkite bet kurį langelį iš šio sąrašo:
Tada skirtuke Įterpimas (Įterpti) pasirinkite komandą Suvestinės lentelės (Suvestinės lentelės):
Atsiras dialogo langas Sukurkite „PivotTable“ (Sukurti suvestinę lentelę) su dviem klausimais:
- Kokius duomenis naudoti kuriant naują suvestinę lentelę?
- Kur dėti sukimosi lentelę?
Kadangi ankstesniame žingsnyje mes jau pasirinkome vieną iš sąrašo langelių, visas sąrašas bus automatiškai pasirinktas, kad būtų sukurta suvestinė lentelė. Atminkite, kad galime pasirinkti kitą diapazoną, kitą lentelę ir net kai kuriuos išorinius duomenų šaltinius, pvz., Access arba MS-SQL duomenų bazės lentelę. Be to, turime pasirinkti, kur dėti naują sukamąją lentelę: ant naujo lapo ar ant vieno iš esamų. Šiame pavyzdyje pasirinksime parinktį – Naujas darbalapis (į naują lapą):
„Excel“ sukurs naują lapą ir įdės į jį tuščią suvestinę lentelę:
Kai tik spustelėsime bet kurį suvestinės lentelės langelį, pasirodys kitas dialogo langas: „PivotTable“ laukų sąrašas (Suvestinės lentelės laukai).
Dialogo lango viršuje esantis laukų sąrašas yra visų pradinio sąrašo pavadinimų sąrašas. Keturios tuščios sritys ekrano apačioje leidžia „PivotTable“ nurodyti, kaip norite apibendrinti duomenis. Kol šios zonos tuščios, lentelėje taip pat nieko nėra. Viskas, ką turime padaryti, tai vilkti antraštes iš viršutinės srities į tuščias sritis žemiau. Tuo pačiu metu pagal mūsų nurodymus automatiškai sugeneruojama suvestinė lentelė. Jei padarome klaidą, galime pašalinti antraštes iš apatinės srities arba vilkti kitas, kad jas pakeistų.
Plotas Vertybės (reikšmės) yra bene svarbiausias iš keturių. Kuri antraštė dedama šioje srityje, lemia, kokie duomenys bus apibendrinti (suma, vidurkis, maksimumas, minimumas ir kt.) Tai beveik visada skaitinės reikšmės. Puikus kandidatas į vietą šioje srityje yra antraštėje esantys duomenys suma (Kaina) mūsų originalios lentelės. Vilkite šį pavadinimą į sritį Vertybės (Vertės):
Atkreipkite dėmesį, kad pavadinimas suma dabar pažymėtas varnele, ir srityje Vertybės (Vertės) pasirodė įrašas Sumos suma (Suma lauke Suma), nurodant, kad stulpelis suma apibendrino.
Jei pažvelgsime į pačią suvestinę lentelę, pamatysime visų stulpelio reikšmių sumą suma originalus stalas.
Taigi, mūsų pirmoji suvestinė lentelė sukurta! Patogu, bet ne itin įspūdinga. Tikriausiai norime gauti daugiau informacijos apie savo duomenis, nei turime šiuo metu.
Pereikime prie pradinių duomenų ir pabandykime nustatyti vieną ar daugiau stulpelių, kuriuos galima panaudoti šiai sumai padalyti. Pavyzdžiui, savo pivot lentelę galime suformuoti taip, kad bendra pardavimų suma būtų skaičiuojama kiekvienam pardavėjui atskirai. Tie. į mūsų suvestinę lentelę bus įtrauktos eilutės su kiekvieno įmonės pardavėjo pavarde ir bendra pardavimo suma. Norėdami pasiekti šį rezultatą, tiesiog vilkite pavadinimą pardavėjas (pardavimo atstovas) į regioną Eilučių etiketės (Stygos):
Pasidaro įdomiau! Mūsų „PivotTable“ pradeda formuotis…
Matote naudą? Keliais paspaudimais sukūrėme lentelę, kurios kūrimas rankiniu būdu būtų užtrukęs labai ilgai.
Ką dar galime padaryti? Na, tam tikra prasme mūsų suvestinė lentelė yra paruošta. Sukūrėme naudingą pirminių duomenų santrauką. Svarbi informacija jau gauta! Likusioje šio straipsnio dalyje apžvelgsime keletą sudėtingesnių „PivotTable“ kūrimo būdų ir sužinosime, kaip jas tinkinti.
PivotTable sąranka
Pirma, galime sukurti dvimatę sukimosi lentelę. Padarykime tai naudodami stulpelio antraštę Mokėjimo būdas (Mokėjimo būdas). Tiesiog vilkite pavadinimą Mokėjimo būdas į sritį Stulpelių etiketės (Stulpeliai):
Gauname rezultatą:
Labai šauniai atrodo!
Dabar padarykime trimatę lentelę. Kaip atrodytų toks stalas? Pažiūrėkime…
Vilkite antraštę Paketas (Kompleksas) į sritį ataskaitų filtrai (Filtrai):
Atkreipkite dėmesį, kur jis yra…
Tai suteikia mums galimybę filtruoti ataskaitą pagal „Už kurį atostogų kompleksą buvo sumokėta“. Pavyzdžiui, galime pamatyti visų kompleksų suskirstymą pagal pardavėjus ir mokėjimo būdus arba keliais pelės paspaudimais pakeisti suvestinės lentelės vaizdą ir rodyti tą patį suskirstymą tik tiems, kurie užsakė kompleksą. Saulės ieškotojai.
Taigi, jei tai teisingai supratote, mūsų sukimosi lentelė gali būti vadinama trimate. Tęskime sąranką…
Jei staiga paaiškėja, kad suvestinėje lentelėje turėtų būti rodomas tik mokėjimas čekiu ir kredito kortele (ty mokėjimas be grynųjų pinigų), tada galime išjungti pavadinimo rodymą Pinigai (Grynaisiais pinigais). Už tai šalia Stulpelių etiketės spustelėkite rodyklę žemyn ir išskleidžiamajame meniu panaikinkite laukelio žymėjimą Pinigai:
Pažiūrėkime, kaip dabar atrodo mūsų suvestinė lentelė. Kaip matote, stulpelis Pinigai nuo jos dingo.
„PivotTable“ formatavimas programoje „Excel“.
Akivaizdu, kad PivotTables yra labai galingas įrankis, tačiau kol kas rezultatai atrodo šiek tiek paprasti ir nuobodūs. Pavyzdžiui, mūsų sudėti skaičiai neatrodo kaip sumos doleriais – tai tik skaičiai. Pataisykime tai.
Kyla pagunda tokioje situacijoje daryti tai, prie ko esate įpratę ir tiesiog pasirinkti visą lentelę (arba visą lapą) ir standartiniais skaičių formatavimo mygtukais įrankių juostoje nustatyti norimą formatą. Šio metodo problema yra ta, kad jei kada nors ateityje pakeisite suvestinės lentelės struktūrą (tai atsitiks su 99 % tikimybe), formatavimas bus prarastas. Mums reikia būdo, kaip tai padaryti (beveik) nuolatiniu.
Pirmiausia suraskime įrašą Sumos suma in Vertybės (Vertės) ir spustelėkite jį. Pasirodžiusiame meniu pasirinkite elementą Reikšmės lauko nustatymai (Vertės lauko parinktys):
Atsiras dialogo langas Reikšmės lauko nustatymai (Vertės lauko parinktys).
Spustelėkite mygtuką Taškų Formatas (Skaičių formatas), atsidarys dialogo langas. Langelių formatavimas (ląstelių formatas):
Iš sąrašo Kategorija (Skaičių formatai) pasirinkite apskaita (Finansinis) ir skaičių po kablelio nustatykite į nulį. Dabar paspauskite keletą kartų OKkad grįžtume prie mūsų suvestinės lentelės.
Kaip matote, skaičiai suformatuoti kaip sumos doleriais.
Kol dirbame su formatavimu, nustatykime visos „PivotTable“ formatą. Yra keletas būdų tai padaryti. Mes naudojame paprastesnį…
Spauskite „PivotTable“ įrankiai: dizainas (Darbas su „PivotTable“: konstruktorius):
Tada išplėskite meniu spustelėdami rodyklę apatiniame dešiniajame skyriaus kampe „PivotTable“ stiliai (PivotTable Styles), kad pamatytumėte didelę eilutinių stilių kolekciją:
Pasirinkite bet kokį tinkamą stilių ir pažiūrėkite į rezultatą savo suvestinės lentelėje:
Kiti „PivotTable“ nustatymai programoje „Excel“.
Kartais reikia filtruoti duomenis pagal datas. Pavyzdžiui, mūsų sandorių sąraše yra daug, daug datų. „Excel“ suteikia įrankį duomenims grupuoti pagal dieną, mėnesį, metus ir pan. Pažiūrėkime, kaip tai daroma.
Pirmiausia pašalinkite įrašą. Mokėjimo būdas iš regiono Stulpelių etiketės (Stulpeliai). Norėdami tai padaryti, vilkite jį atgal į pavadinimų sąrašą ir vietoje jo perkelkite pavadinimą Užsakyta data (užsakymo data):
Kaip matote, dėl to mūsų suvestinė lentelė laikinai tapo nenaudinga. „Excel“ sukūrė atskirą stulpelį kiekvienai datai, kurią buvo sudarytas sandoris. Dėl to gavome labai platų stalą!
Norėdami tai išspręsti, dešiniuoju pelės mygtuku spustelėkite bet kurią datą ir kontekstiniame meniu pasirinkite GRUPĖ (Grupė):
Atsiras grupavimo dialogo langas. Mes renkamės Mėnesių (mėnesiai) ir spustelėkite OK:
Voila! Ši lentelė yra daug naudingesnė:
Beje, ši lentelė yra beveik identiška tai, kas parodyta straipsnio pradžioje, kur pardavimų sumos buvo sudarytos rankiniu būdu.
Yra dar vienas labai svarbus dalykas, kurį turite žinoti! Galite sukurti ne vieną, o kelis eilučių (ar stulpelių) antraščių lygius:
ir atrodys taip...
Tą patį galima padaryti su stulpelių antraštėmis (ar net filtrais).
Grįžkime prie pradinės lentelės formos ir pažiūrėkime, kaip vietoj sumų rodyti vidurkius.
Norėdami pradėti, spustelėkite Sumos suma ir pasirodžiusiame meniu pasirinkite Reikšmės lauko nustatymai (Vertės lauko parinktys):
Sąrašas Apibendrinkite vertės lauką pagal (Operacija) dialogo lange Reikšmės lauko nustatymai (Vertės lauko parinktys) pasirinkite vidutinis (Vidutinis):
Tuo pačiu, kol esame čia, keiskimės CustomName (Pasirinktinis pavadinimas) su Sumos vidurkis (Sumos laukas Suma) į trumpesnį. Šiame lauke įveskite kažką panašaus į Vidur:
spauda OK ir pažiūrėk, kas atsitiks. Atminkite, kad visos reikšmės pasikeitė iš sumų į vidurkius, o lentelės antraštė (viršutiniame kairiajame langelyje) pasikeitė į Vidur:
Jei norite, galite iš karto gauti sumą, vidurkį ir skaičių (pardavimų) į vieną pivot lentelę.
Štai žingsnis po žingsnio vadovas, kaip tai padaryti, pradedant nuo tuščios suvestinės lentelės:
- Vilkite antraštę pardavėjas (pardavimo atstovas) į regioną Stulpelių etiketės (Stulpeliai).
- Vilkite pavadinimą tris kartus suma (Kaina) į plotą Vertybės (Vertės).
- Pirmam laukui suma pakeisti pavadinimą į VISO (Suma), o skaičių formatas šiame lauke yra apskaita (Finansinis). Po kablelio skaičius yra nulis.
- Antrasis laukas suma pavadinimas Vidutinise, nustatykite jai skirtą operaciją vidutinis (Vidutinis) ir skaičių formatas šiame lauke taip pat pasikeičia į apskaita (Finansinis) su nuliu skaitmenų po kablelio.
- Dėl trečiojo lauko suma nustatyti pavadinimą Skaičiuoti ir operacija jam - Skaičiuoti (Kiekis)
- Lauke Stulpelių etiketės (Stulpeliai) laukas sukuriamas automatiškai Σ Vertybės (Σ reikšmės) – vilkite jį į sritį Eilučių etiketės (Linijos)
Štai kuo mes baigsime:
Bendra suma, vidutinė vertė ir pardavimų skaičius – viskas vienoje suvestinės lentelėje!
Išvada
„Microsoft Excel“ suvestinėse lentelėse yra daug funkcijų ir nustatymų. Tokiame mažame straipsnyje jie nė iš tolo neprieina, kad apimtų juos visus. Reikėtų nedidelės knygos arba didelės svetainės, kad būtų galima išsamiai aprašyti visas sukaminių lentelių galimybes. Drąsūs ir smalsūs skaitytojai gali toliau tyrinėti suvestinės lenteles. Norėdami tai padaryti, tiesiog dešiniuoju pelės mygtuku spustelėkite beveik bet kurį suvestinės lentelės elementą ir pažiūrėkite, kokios funkcijos ir nustatymai atidaromi. Juostelėje rasite du skirtukus: „PivotTable“ įrankiai: parinktys (analizė) ir dizainas (Konstruktorius). Nebijokite suklysti, visada galite ištrinti „PivotTable“ ir pradėti iš naujo. Jūs turite galimybę, kurios niekada neturėjo ilgamečiai DOS ir Lotus 1-2-3 vartotojai.