Darbas su „Pivot Tables“ „Microsoft Excel“

„PivotTables“ yra viena iš galingiausių „Microsoft Excel“ funkcijų. Jie leidžia analizuoti ir apibendrinti didelius duomenų kiekius tik keliais pelės paspaudimais. Šiame straipsnyje mes tiriame „PivotTables“, suprantame, kas jie yra, ir sužinokite, kaip juos kurti ir pritaikyti.

Pastaba: Šis straipsnis parašytas naudojant „Excel 2010“ (Beta). PivotTable koncepcija per keletą metų pasikeitė, tačiau jos sukūrimo metodas pasikeitė beveik visose „Excel“ iteracijose. Jei naudojate „Excel“ versiją, kuri nėra 2010 m., Tikėtis skirtingų ekranų iš tų, kuriuos matote šiame straipsnyje.

Maža istorija

Pirmosiomis skaičiuoklės programų dienomis „Lotus 1-2-3“ valdė stovą. Jos dominavimas buvo toks pilnas, kad žmonės manė, kad „Microsoft“ laiko švaistymas trukdo kurti savo skaičiuoklės programinę įrangą („Excel“), kad galėtų konkuruoti su „Lotus“. „Flash“ į priekį iki 2010 m. Ir „Excel“ dominavimo skaičiuoklės rinka yra didesnė nei „Lotus“, o vartotojų, vis dar naudojančių „Lotus 1-2-3“, skaičius artėja prie nulio. Kaip tai nutiko? Kas sukėlė tokį dramatišką turto atkūrimą?

Pramonės analitikai nurodė du veiksnius: pirma, „Lotus“ nusprendė, kad ši nauja išgalvota „GUI“ platforma, vadinama „Windows“, buvo praeinantis šlamštas, kuris niekada neužsikabintų. Jie atsisakė sukurti „Windows“ versiją „Lotus 1-2-3“ (vos kelerius metus), prognozuodami, kad jų programinės įrangos „DOS“ versija buvo bet kas, kas kada nors reikėjo. „Microsoft“, žinoma, sukūrė „Excel“ išskirtinai „Windows“. Antra, „Microsoft“ sukūrė „Excel“ funkciją, kurią „Lotus“ neteikė „1-2-3“ PivotTables.  „Excel“ išskirtinė „PivotTables“ funkcija buvo laikoma labai stulbinančia naudinga, kad žmonės norėjo išmokti visą naują programinės įrangos paketą („Excel“), o ne laikytis programos (1-2-3), kurios neturėjo. Ši viena funkcija, kartu su netinkamu „Windows“ sėkmės įvertinimu, buvo „Lotus 1-2-3“ mirties žiedas ir „Microsoft Excel“ sėkmės pradžia.

„PivotTables“ supratimas

Taigi, kas yra PivotTable??

Paprasčiau tariant, „PivotTable“ yra kai kurių duomenų, sukurtų leidžiant lengvai analizuoti minėtus duomenis, santrauka. Tačiau, skirtingai nei rankiniu būdu sukurta suvestinė, „Excel PivotTables“ yra interaktyvios. Sukūrę vieną, jūs galite lengvai ją pakeisti, jei jis nepateikia tikslių įžvalgų apie jūsų duomenis, kuriuos tikėjotės. Pora paspaudimų santrauka gali būti „pasukta“ - pasukta taip, kad stulpelių antraštės tampa eilutės antraštėmis, ir atvirkščiai. Taip pat yra daug daugiau, ką galima padaryti. Užuot bandę apibūdinti visas „PivotTables“ funkcijas, mes paprasčiausiai parodysime juos ...

Duomenys, kuriuos analizuojate naudodami „PivotTable“, negali būti teisingi bet duomenys - jis turi būti neapdorotas duomenys, anksčiau neapdoroti (nepanaudoti) - paprastai tam tikras sąrašas. To pavyzdys galėtų būti pardavimo sandorių sąrašas įmonėje per pastaruosius šešis mėnesius.

Patikrinkite toliau pateiktus duomenis:

Atkreipkite dėmesį, kad tai yra ne Neapdoroti duomenys. Tiesą sakant, tai jau yra tam tikros rūšies santrauka. B3 ląstelėje matome 30 000 dolerių, kurie, matyt, yra „James Cook“ pardavimų sausio mėn. Taigi, kur yra neapdoroti duomenys? Kaip mes pasiekėme 30 000 dolerių skaičių? Kur yra originalus pardavimo sandorių sąrašas, iš kurio šis skaičius buvo gautas? Aišku, kad kažkur, kažkas turi patirti problemų, susijusių su visomis pardavimo operacijomis per pastaruosius šešis mėnesius į pirmiau pateiktą suvestinę. Kiek laiko manote, kad tai buvo? Valanda? Dešimt?

Tikriausiai taip. Matote, aukščiau pateikta skaičiuoklė iš tikrųjų yra ne „PivotTable“. Ji buvo sukurta rankiniu būdu iš kitur saugomų neapdorotų duomenų, ir ji iš tikrųjų užtruko keletą valandų. Tačiau tai būtent tokia santrauka gali būti sukurta naudojant „PivotTables“, tokiu atveju ji būtų užtrukusi tik kelias sekundes. Sužinokite, kaip…

Jei norėtume surasti originalų pardavimo sandorių sąrašą, tai gali atrodyti taip:

Jums gali būti nustebęs, sužinojęs, kad naudojant „Excel“ „PivotTable“ funkciją, per kelias sekundes galime sukurti mėnesinę pardavimo suvestinę, panašią į aukščiau pateiktą, tik keliais pelės paspaudimais. Tai galime padaryti - ir daug daugiau!

Kaip sukurti „PivotTable“

Pirma, įsitikinkite, kad Excel programoje yra tam tikrų neapdorotų duomenų. Finansinių sandorių sąrašas yra tipiškas, tačiau jis gali būti beveik bet kokio sąrašo sąrašas: Darbuotojo kontaktiniai duomenys, jūsų CD rinkinys arba jūsų įmonės automobilių parko degalų sąnaudos.

Taigi mes pradedame „Excel“ ... ir įkeliame tokį sąrašą ...

Kai mes turime sąrašą „Excel“, esame pasiruošę pradėti kurti „PivotTable“.

Spustelėkite bet kurį vieno langelio sąrašą:

Tada iš Įdėti spustelėkite skirtuką Suvestinės lentelės piktograma:

The Sukurti „PivotTable“ langas, kuriame užduodami du klausimai: kokie duomenys turėtų būti pagrįsti jūsų naujuoju „PivotTable“ ir kur jis turėtų būti sukurtas? Kadangi mes jau spustelėjome sąraše esančią ląstelę (aukščiau pateiktame žingsnyje), visas sąrašas, kuriame yra šis langelis, jau yra pasirinktas mums ($ A $ 1: $ G $ 88 ant Mokėjimai šiame pavyzdyje). Atkreipkite dėmesį, kad galėtume pasirinkti sąrašą bet kuriame kitame bet kurio kito darbo lapo regione arba net kai kurio išorinio duomenų šaltinio, pvz., „Access“ duomenų bazės lentelės ar net „MS-SQL Server“ duomenų bazės lentelės. Taip pat turime pasirinkti, ar norime, kad naujasis „PivotTable“ būtų sukurtas „a“ naujas darbo lape arba ant esamas vienas. Šiame pavyzdyje pasirinksime a naujas vienas:

Naujasis darbalapis sukurtas mums, o tame darbalapyje sukuriamas tuščias „PivotTable“:

Taip pat pasirodo kitas langelis: „PivotTable“ laukų sąrašas.  Šis laukų sąrašas bus rodomas, kai spustelėsime bet kurį „PivotTable“ (aukščiau) langelį:

Laukų, esančių viršutinėje langelio dalyje, sąrašas iš tikrųjų yra stulpelių antraštės iš pradinio neapdoroto duomenų lapo. Keturios tuščios dėžės apatinėje ekrano dalyje leidžia pasirinkti būdą, kuriuo mes norėtume, kad „PivotTable“ apibendrintų neapdorotus duomenis. Iki šiol šiose dėžutėse nėra nieko, todėl „PivotTable“ yra tuščias. Viskas, ką mums reikia padaryti, yra ištraukti laukus žemiau esančiame sąraše ir nuleisti juos į apatines dėžutes. Tada „PivotTable“ automatiškai sukuriamas, kad atitiktų mūsų instrukcijas. Jei mes tai padarysime neteisingai, laukus reikia tik vilkti atgal į ten, kur jie atvyko ir (arba) nuvilkė naujas laukus, kad juos pakeistumėte.

The Vertybės langelis yra svarbiausia iš keturių. Lauke, kuris yra įtrauktas į šį langelį, pateikiami duomenys, kuriuos reikia apibendrinti (apibendrinant, apskaičiuojant, nustatant maksimalų, minimalų ir tt). Tai beveik visada skaitmeninis duomenis. Puikus kandidatas į šį langelį mūsų mėginių duomenyse yra laukelis „Kiekis“. Vilkime tą lauką į Vertybės dėžė:

Atkreipkite dėmesį, kad (a) lauko lauke esantis laukelis „Suma“ yra pažymėtas, o „sumos suma“ buvo pridėta prie Vertybės langelyje, nurodant, kad sumos stulpelis buvo sumokėtas.

Jei ištirsime pačią „PivotTable“ lentelę, mes iš tikrųjų surasime visų „sumos“ verčių sumą iš neapdoroto duomenų lapo:

Mes sukūrėme savo pirmąjį „PivotTable“! Patogus, bet ne itin įspūdingas. Tikėtina, kad mums reikia šiek tiek daugiau informacijos apie mūsų duomenis.

Remdamiesi mūsų mėginių duomenimis, turime nustatyti vieną ar daugiau stulpelių antraštių, kurias galėtume naudoti norint padalinti šį bendrą. Pvz., Galime nuspręsti, kad norėtume matyti mūsų duomenų santrauką, kurioje turime eilutės antraštė kiekvienam mūsų įmonės pardavėjui ir kiekvienam iš jų. Norėdami tai pasiekti, viskas, ką mums reikia padaryti, yra vilkti „Pardavėjo“ lauką į Eilutės etiketės dėžė:

Dabar, pagaliau viskas tampa įdomu! Mūsų „PivotTable“ pradeda formuotis ... .

Su keliais paspaudimais sukūrėme lentelę, kuri būtų užtrukusi ilgai, kad būtų galima atlikti rankiniu būdu.

Taigi ką dar galime padaryti? Na, tam tikra prasme mūsų „PivotTable“ yra baigtas. Sukūrėme naudingą mūsų šaltinių duomenų santrauką. Svarbus dalykas jau išmoktas! Likusiam straipsnio straipsniui mes išnagrinėsime kelis būdus, kaip galima sukurti sudėtingesnes „PivotTables“, ir būdus, kaip šie tinklai gali būti pritaikyti.

Pirma, galime sukurti du-matmenų lentelė. Tai darysime naudodami „Mokėjimo būdą“ kaip stulpelio antraštę. Tiesiog vilkite „Mokėjimo būdas“ antraštės Stulpelio etiketės dėžė:

Kuris atrodo taip:

Pradedama gauti labai Saunus!

Padarykime tai a trys-matmenų lentelė. Ką galėtų atrodyti tokia lentelė? Na, pažiūrėkime ...

Vilkite „Paketą“ stulpelį / antraštę į Ataskaitų filtras dėžė:

Atkreipkite dėmesį, kur jis baigiasi ... .

Tai leidžia mums filtruoti mūsų ataskaitą, pagal kurią buvo perkamas „atostogų paketas“. Pavyzdžiui, matome pardavėjo ir mokėjimo metodo suskirstymą visi paketų, arba, paspaudus porą paspaudimų, pakeiskite ją, kad būtų rodomas tas pats „Sunseekers“ paketo suskirstymas:

Taigi, jei manote apie tai teisingai, mūsų „PivotTable“ dabar yra trimatis. Toliau pritaikome ...

Jei paaiškėja, kad norime tik pamatyti čekis ir kredito kortelė sandoriai (t. y. be grynųjų pinigų operacijų), tada iš stulpelių antraštių galime panaikinti „Pinigų“ elementą. Spustelėkite šalia esančią išskleidžiamąją rodyklę Stulpelio etiketės, ir panaikinkite „Pinigai“:

Pažiūrėkime, kas tai atrodo ... Kaip matote, „Pinigai“ dingo.

Formatavimas

Akivaizdu, kad tai labai galinga sistema, tačiau iki šiol rezultatai atrodo labai paprasti ir nuobodu. Pradžioje skaičiai, kuriuos mes apibendriname, neatrodo kaip dolerio sumos - tiesiog seni skaičiai. Ištaisykime.

Gundymas gali būti tai, ką mes įpratome daryti tokiomis aplinkybėmis, ir tiesiog pasirinkite visą lentelę (arba visą darbalapį) ir naudokite standartinius numerių formatavimo mygtukus įrankių juostoje, kad užbaigtumėte formatavimą. Šio požiūrio problema yra ta, kad jei ateityje pakeisite „PivotTable“ struktūrą (kuri yra 99% tikėtina), tuomet šie skaitmenų formatai bus prarasti. Mums reikia būdų, kad jie taptų (pusiau) nuolatiniai.

Pirma, mes randame įrašą „Suma“ Vertybės langelį ir spustelėkite jį. Pasirodo meniu. Mes pasirenkame Vertės lauko nustatymai ... iš meniu:

The Reikšmės lauko nustatymai pasirodo langelis.

Spustelėkite Skaičių formatas mygtukas ir standartas Formatuoti langelių langelį pasirodo:

Nuo Kategorija sąraše, pasirinkite (pasakyti) Apskaita, ir sumažinkite dešimtainių skaičių iki 0. Spustelėkite Gerai kelis kartus grįžti į „PivotTable“…

Kaip matote, numeriai teisingai suformatuoti kaip dolerio sumos.

Nors formatuojame, formuokite visą „PivotTable“. Yra keletas būdų tai padaryti. Naudokime paprastą…

Spustelėkite PivotTable įrankiai / dizainas skirtukas:

Tada nusileiskite rodyklę apatiniame dešiniajame Pasukamieji stalai sąrašą, kad pamatytumėte didžiulę įmontuotų stilių kolekciją:

Pasirinkite bet kurį, kuris skundžiasi, ir peržiūrėkite savo „PivotTable“ rezultatą:

Kitos parinktys

Mes taip pat galime dirbti su datomis. Dabar paprastai yra daug, daugelis datų sandorių sąraše, pavyzdžiui, nuo kurio mes pradėjome. Tačiau „Excel“ suteikia galimybę grupuoti duomenų elementus kartu su dieną, savaitę, mėnesį, metus ir pan. Pažiūrėkime, kaip tai daroma.

Pirma, pašalinkime „Mokėjimo metodo“ stulpelį iš Stulpelio etiketės langelyje (tiesiog vilkite jį atgal į laukų sąrašą) ir pakeiskite stulpelį „Rezervuota data“:

Kaip matote, tai daro mūsų „PivotTable“ akimirksniu nenaudingą, suteikiant mums vieną stulpelį už kiekvieną sandorio įvykdymo datą - labai platų stalą!

Norėdami išspręsti šią problemą, dešiniuoju pelės mygtuku spustelėkite bet kurią datą ir pasirinkite Grupė ... iš kontekstinio meniu:

Rodomas grupavimo langelis. Mes pasirenkame Mėnesių ir spustelėkite Gerai:

Voila! A daug naudingesnis stalas:

(Beje, ši lentelė yra beveik identiška šio straipsnio pradžioje nurodytai pradinei pardavimo santraukai, kuri buvo sukurta rankiniu būdu.)

Kitas įdomus dalykas, kurį reikia žinoti, yra tai, kad galite turėti daugiau nei vieną eilutės antraštių (arba stulpelių antraštių) rinkinį:

… Kuris atrodo taip… .

Panašų dalyką galite padaryti su stulpelių antraštėmis (arba netgi pranešti apie filtrus).

Vėlgi viskas paprasta, pažiūrėkime, kaip sklypas vidurkis vertės, o ne sumos.

Pirmiausia spustelėkite „Sumos suma“ ir pasirinkite Vertės lauko nustatymai ... iš rodomo kontekstinio meniu:

Viduje konors Apibendrinkite vertės lauką sąraše Reikšmės lauko nustatymai langelyje pasirinkite Vidutinis:

Kol mes čia, pakeiskime Priskirtas pavadinimas, nuo „Kiekio vidurkio“ iki kažko šiek tiek glaustesnio. Įveskite kažką panašaus į „Vid.“:

Spustelėkite Gerai, ir pažiūrėkite, kaip atrodo. Atkreipkite dėmesį, kad visos vertės pasikeičia iš sumuotų sumų į vidurkius, o lentelės pavadinimas (viršutinis kairysis langelis) pasikeitė į „Vid.“:

Jei norime, mes galime turėti net sumas, vidurkius ir skaičių (skaičiuoja = kiek pardavimų ten buvo) tame pačiame „PivotTable“!

Toliau pateikiami veiksmai, kaip gauti kažką panašaus (pradedant nuo tuščio „PivotTable“):

1. Vilkite „Pardavėjas“ į Stulpelio etiketės
2. Vilkite „Sumažinti“ lauką žemyn Vertybės tris kartus
3. Pirmajame lauke „Kiekis“ pakeiskite savo pasirinktą pavadinimą į „Iš viso“ ir jo numerį Apskaita (0 skaitmenų po kablelio)
4. Antrajam laukui „Kiekis“ pakeiskite savo pasirinktą pavadinimą į „Vidutinis“, jo funkciją Vidutinis ir jo numerio formatas Apskaita (0 skaitmenų po kablelio)
5. Trečiame lauke „Kiekis“ pakeiskite jo pavadinimą į „Count“ ir jo funkciją Skaičius
6. Vilkite automatiškai sukurtą laukas iš Stulpelio etiketės į Eilutės etiketės

Štai ką galime padaryti su:

Iš viso, vidutinis ir suskaičiuokite tą patį „PivotTable“!

Išvada

Yra daug „Microsoft Excel“ sukurtų „PivotTables“ funkcijų ir galimybių - per daug, kad būtų įtrauktas į tokį straipsnį. Norint visiškai padengti „PivotTables“ potencialą, reikės mažos knygos (arba didelės svetainės). Drąsūs ir (arba) geeky skaitytojai gali lengvai ištirti „PivotTables“: tiesiog spustelėkite dešinįjį pelės klavišą ant visko, ir pažiūrėkite, kokios parinktys jums prieinamos. Taip pat yra dvi juostelės: PivotTable įrankiai / parinktys ir Dizainas.  Nesvarbu, ar padarote klaidą - lengva ištrinti „PivotTable“ ir vėl pradėti - tai galimybė, kad „Lotus 1-2-3“ seni „DOS“ naudotojai niekada neturėjo.

Jei dirbate „Office 2007“, galbūt norėsite peržiūrėti mūsų straipsnį apie tai, kaip „Excel 2007“ sukurti „PivotTable“.

Įtraukėme „Excel“ darbaknygę, kurią galite atsisiųsti, kad galėtumėte naudotis savo „PivotTable“ įgūdžiais. Ji turėtų veikti su visomis „Excel“ versijomis nuo 97 metų.

Atsisiųskite mūsų praktikos Excel darbaknygę