PivotTables yra viena iš galingiausių "Microsoft Excel" funkcijų. Jie leidžia analizuoti ir apibendrinti didelius duomenų kiekius tik keliais pelės paspaudimais. Šiame straipsnyje ieškome PivotTables, suprantame, ką jie yra, ir sužinome, kaip juos kurti ir pritaikyti.
Pastaba: šis straipsnis parašytas naudojant "Excel 2010" (beta versija). PivotTable sąvoka per keletą metų pasikeitė, bet jos sukūrimo metodas pasikeitė beveik kiekvienoje "Excel" iteracijoje. Jei naudojate "Excel" versiją, kuri nėra 2010 m., Tikitės skirtingų ekranų nei tie, kuriuos matote šiame straipsnyje.
Maža istorija
Skaičiuoklių programų pradžioje Lotus 1-2-3 valdė grotelę. Jos dominavimas buvo toks pilnas, kad žmonės manė, kad "Microsoft" laiko netinkamai susidoroti su programine įranga ("Excel"), kad galėtų konkuruoti su "Lotus". "Flash" - iki 2010 m., O "Excel" dominavimas skaičiuoklių rinkoje yra didesnis nei "Lotus" visada buvo, o vartotojų, kurie vis dar dirba "Lotus 1-2-3", skaičius artėja prie nulio. Kaip tai nutiko? Kas sukėlė tokį dramatišką likimo pasikeitimą?
Pramonės analitikai teigia, kad šie veiksniai yra du veiksniai: pirma, "Lotus" nusprendė, kad ši išgalvota nauja "GUI" platforma, vadinama "Windows", buvo praeitis, kuri niekada nepasileis. Jie atsisakė sukurti "Windows" versiją "Lotus 1-2-3" (bet kuriuo atveju kelerius metus), prognozuojant, kad jų DOS versijos programinė įranga buvo viskas, ko kada nors reikės. "Microsoft", žinoma, "Excel" sukūrė tik "Windows" programinę įrangą. Antra, "Microsoft" sukūrė Excel funkciją, kurią "Lotus" nepateikė 1-2-3, būtent PivotTables. "PivotTables", išskirtinis "Excel", buvo toks įdomus dalykas, kad žmonės norėjo išmokti visą naują programinę įrangą ("Excel"), o ne laikytis programos (1-2-3), kuri to neturėjo. Ši viena funkcija kartu su neteisingu "Windows" sėkmės įvertinimu buvo "Lotus 1-2-3" mirties konsole ir "Microsoft Excel" sėkmės pradžia.
Suprasti PivotTables
Taigi, kas yra PivotTable, tiksliai?
Paprasčiausiai PivotTable yra kai kurių duomenų santrauka, sukurta siekiant lengviau analizuoti minėtus duomenis. Tačiau skirtingai nei rankiniu būdu sukurta santrauka, Excel PivotTables yra interaktyvios. Sukūrę vieną, galite lengvai jį pakeisti, jei jis nesuteiks tikslių duomenų apie jūsų tikėtis. Per keletą paspaudimų santrauka gali būti "pasukta" - pasukama taip, kad stulpelių antraštės tampa eilučių antraštėmis ir atvirkščiai. Taip pat galima padaryti dar daugiau. Užuot bandę apibūdinti visas "PivotTables" savybes, mes tiesiog juos demonstruosime …
Duomenys, kuriuos analizuojate naudodami PivotTable, negali būti tik bet koks duomenys - tai turi būti žaliavinis duomenys, anksčiau neperdirbti (nepakankami), paprastai tam tikros rūšies sąrašas. Pavyzdys gali būti pardavimo sandorių sąrašas 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 santraukos. B3 korpuse mes matome 30 000 JAV dolerių, o tai, matyt, yra Jameso Kuko pardavimai sausio mėnesį. Taigi, kur yra neapdoroti duomenys? Kaip atvykome 30 000 dolerių? Kur yra pirminis prekybos sandorių, į kuriuos buvo gautas šis skaičius, sąrašas? Akivaizdu, kad kažkur kažkiene, per pastaruosius šešis mėnesius, reikia aptikti visus pardavimo sandorius, kuriuos mes matome aukščiau. Kaip ilgai jūs manote, kad tai įvyko? Valanda? Dešimt?
Labai tikėtina, taip. Matote, pirmiau pateikta lentelė yra iš tikrųjų ne PivotTable. Jis buvo sukurtas rankiniu būdu iš kitur saugomų neapdorotų duomenų, ir kompiliavimui iš tikrųjų reikėjo kelias valandas. Tačiau tai būtent tokia santrauka galėjo būti sukurta naudojant PivotTables, tokiu atveju tai užtruks tik keletą sekundžių. Paimkime, kaip …
Jei norėjome atskleisti pradinį pardavimo sandorių sąrašą, jis gali atrodyti taip:
Kaip sukurti PivotTable
Pirmiausia įsitikinkite, kad turite kokių nors neapdorotų duomenų "Excel" darbalapyje. Tipiškas yra finansinių sandorių sąrašas, tačiau tai gali būti beveik nieko: kontaktinių duomenų apie darbuotojus, jūsų CD rinkinio ar kuro suvartojimo skaičių jūsų įmonės automobilių parke.
Taigi mes pradedame "Excel" … ir mes įkelkime tokį sąrašą …
Spustelėkite bet kurią vieną langelį sąraše:
Tada nuo Įdėti skirtuką spustelėkite mygtuką Suvestinės lentelės piktograma:
The Sukurkite PivotTable atsiranda langas, kuriame pateikiami du klausimai: kokie duomenys turėtų būti pagrįsti jūsų naujais PivotTable duomenimis ir kur jie turėtų būti sukurti? Kadangi mes jau spustelėjome sąraše esančią ląstelę (anksčiau pateiktame veiksme), visas mūsų ląstelės aplinkraštis jau yra pasirinktas ($ A $ 1: $ G $ 88 ant Mokėjimai lape, šiame pavyzdyje). Atkreipkite dėmesį, kad galėtume pasirinkti sąrašą bet kuriame kitame bet kurio kito darbo lapo regione ar netgi kai kuriuose išorės duomenų šaltiniuose, pvz., Prieigos duomenų bazės lentelėje ar net MS-SQL Server duomenų bazės lentelėje. Mes taip pat turime pasirinkti, ar mes norime, kad mūsų naujas PivotTable būtų sukurtas a naujas darbalapyje arba ant esamos vienas. Šiame pavyzdyje mes pasirinkome a naujas vienas:
Taip pat pasirodo kitas laukelis: The PivotTable lauko sąrašas. Šis laukų sąrašas bus rodomas kaskart spustelėjus bet kurią langelį PivotTable (aukščiau):
Laukelio viršuje esančių laukų sąrašas iš tikrųjų yra stulpelių antraščių rinkinys iš pirminio žaliavinio duomenų lapo. Apatinėje ekrano dalyje esančios keturios tuščios dėžutės leidžia mums pasirinkti, kaip mes norėtume, kad mūsų PivotTable apibendrintų neapdorotus duomenis. Kol kas šiose dėžėse nėra nieko, taigi PivotTable yra tuščia. Viskas, ką turime padaryti, tai vilkite laukus žemiau esančio sąrašo ir palikite juos žemesnėse dėžutėse. Tuomet automatiškai sukuria PivotTable, kad atitiktų mūsų instrukcijas. Jei mes neteksime, mes turime tik vilkti laukus atgal į tai, iš kur jie atvyko ir / arba vilkite naujas laukai, kad juos pakeisti.
The Vertybės Dėžutė, be abejo, yra svarbiausia iš keturių. Lauke, kuris įtraukiamas į šį langelį, pateikiami duomenys, kuriuos reikia apibendrinti kokiu nors būdu (sumuojant, vidurkinant, nustatant didžiausią, minimalų ir tt). Tai beveik visada skaitmeninis duomenys. Puikus kandidatas į šį langelį mūsų pavyzdinių duomenų lauke / stulpelyje "Suma". Perkelkime šį lauką į Vertybės dėžė:
Atkreipkite dėmesį, kad a) laukelyje "Suma" laukas yra pažymėtas, o "Sumos suma" buvo pridėta prie Vertybės dėžutė, nurodanti, kad sumos stulpelis buvo sumotas.
Jei mes išnagrinėsime PivotTable pati, mes iš tiesų surasime visų "Suma" verčių sumą iš neapdorotų duomenų lapo:
Mes sukūrėme mūsų pirmąjį PivotTable! Patogus, bet ne itin įspūdingas. Tikėtina, kad turime šiek tiek daugiau sužinoti apie mūsų duomenis.
Remdamiesi atrankos duomenimis, turime nurodyti vieną ar daugiau stulpelių antraščių, kurias mes galbūt galėtume panaudoti, kad padalintų šią sumą. Pvz., Galime nuspręsti, kad mes norėtume pamatyti mūsų duomenų santrauką, kur mes turime eilutės antraštė už kiekvieną iš skirtingų mūsų kompanijos pardavėjų ir iš viso už kiekvieną. Norėdami tai pasiekti, viską, ką turime padaryti, tai vilkite "Pardavėjo" lauką į Eilučių etiketės dėžė:
Dabar, pagaliau viskas pradeda pasirodyti įdomus! Mūsų PivotTable pradeda formuotis …
Ką dar galime padaryti? Na, viena vertus, mūsų PivotTable yra baigta. Mes sukūrėme naudingą šaltinių duomenų santrauką. Svarbūs dalykai jau išmokti! Kitame straipsnyje mes išnagrinėsime keletą būdų, kaip sukurti sudėtingesnes PivotTables, taip pat būdus, kaip šiuos PivotTables galima pritaikyti.
Pirma, mes galime sukurti dviejųmatmenų lentelė. Darome tai, naudodami "Mokėjimo metodą" kaip stulpelio antraštę. Tiesiog vilkite "Mokėjimo metodo" antraštę į Stulpelių etiketės dėžė:
Pradedant gauti labai Saunus!
Padarykime a trys-dimensinis stalas. Kokia galėjo atrodyti tokia lentelė? Na, pažiūrėkime …
Vilkite "Paketo" stulpelį / antraštę į Pranešti apie filtrą dėžė:
Tai leidžia mums filtruoti mūsų ataskaitą, pagal kurią buvo nupirktas "atostogų paketas". Pavyzdžiui, mes galime pamatyti pardavėjo ir mokėjimo metodo suskirstymą visi paketai arba keletą paspaudimų pakeiskite, kad parodytumėte tą patį "Sunseekers" paketo suskirstymą:
Jei pasirodys, pasake, kad mes tik norime pamatyti čekiu ir kreditine kortele sandoriai (t. y. be grynųjų pinigų operacijų), tada galime panaikinti "Cash" elemento iš stulpelių antraščių. Spustelėkite šalia esančią išskleidžiamąją rodyklę Stulpelių etiketės, ir nuneškite "Pinigai":
Formatavimas
Tai, žinoma, yra labai galinga sistema, tačiau iki šiol rezultatai yra labai aiškūs ir nuobodūs. Pradžioje skaičiai, kuriuos mes sumuojame, nepanašūs į dolerio sumas - tiesiog seni numeriai. Pataisykime tai.
Tokiomis aplinkybėmis gali būti pagunda padaryti tai, ką mes naudojamės, ir paprasčiausiai pasirinkite visą lentelę (arba visą darbalapį) ir naudokitės standartiniais skaičių formatavimo mygtukais įrankių juostoje, kad užbaigtumėte formatavimą. Problema su šiuo požiūriu yra ta, kad jei kada nors pakeisite PivotTable struktūrą ateityje (tai yra 99% tikimybė), tuomet numerių formatai bus prarasti. Mums reikia tokio būdo, kad jie (pusiau) būtų nuolatiniai.
Pirma, mes nustatome įrašą "Sumos suma" Vertybės dėžutėje ir spustelėkite ją. Pasirodo meniu. Mes pasirenkame Vertės lauko nustatymai … iš meniu:
The Vertės lauko nustatymai pasirodys langelis.
Spustelėkite Skaičiaus formatas mygtukas ir standartas Formatuoti ląstelių dėžutę pasirodo:
Nuo Kategorija sąraše pasirinkite (pasakyti) Apskaita, ir nukreips dešimtainių skaičių iki 0. Paspauskite Gerai keletą kartų grįžti į PivotTable …
Nors mes esame dėl formatavimo, mes formatuodami visą PivotTable. Yra keletas būdų tai padaryti. Leiskite naudoti paprastą …
Spustelėkite PivotTable Tools / Dizainas skirtukas:
Tada išskleiskite rodyklę apačioje dešinėje PivotTable stiliai kad pamatytumėte didžiulę integruotų stilių rinkinį:
Kitos parinktys
Galime dirbti ir su datomis. Dabar paprastai yra daugybė, daugybė datas operacijų sąraše, pvz., Su kuriuo mes pradėjome. Tačiau "Excel" suteikia galimybę grupuoti duomenų elementus pagal dieną, savaitę, mėnesį, metus ir pan. Pažiūrėkime, kaip tai daroma.
Pirmiausia išbraukite stulpelį "Mokėjimo būdas" iš Stulpelių etiketės dėžutė (tiesiog vilkite ją atgal į laukų sąrašą) ir pakeiskite ją stulpeliu "Data rezervuota":
Norėdami išspręsti šią problemą, dešiniuoju pelės mygtuku spustelėkite bet kurią datą ir pasirinkite Grupė … iš kontekstinio meniu:
Parodomas grupavimo dėžutė. Mes pasirenkame Mėnesių ir spustelėkite Gerai:
Voila! A daug daugiau naudingos lentelės:
Kitas dalykas, apie kurį reikia žinoti, yra tai, kad galite turėti daugiau nei vieną eilučių antraščių rinkinį (arba stulpelių pavadinimus):
Ką dar kartą sugrįžkime, pažiūrėkime, kaip planuoti vidutiniškai vertybes, o ne sumines vertes.
Pirmiausia spustelėkite "Sumos suma" ir pasirinkite Vertės lauko nustatymai … iš pasirodžiusio kontekstinio meniu:
Viduje konors Apibendrinti vertės lauką pagal sąraše Vertės lauko nustatymai laukelyje pasirinkite Vidurkis:
Nors mes čia, pakeiskime Tinkintas pavadinimas, nuo "Vidutinė suma" iki kažko truputį trumpesnio. Įveskite kažką panašaus į "Avg":
Spustelėkite Geraiir pamatysite, kaip atrodo. Atkreipkite dėmesį, kad visos vertės pasikeičia nuo suminių sumų iki vidutinių, o lentelės pavadinimas (viršutinė kairioji ląstelė) pakeičiamas į "Avg":
Štai veiksmai, skirti gauti kažką panašaus į tą vietą (pradedant tuščia PivotTable):
- Vilkite "Pardavėjas" į Stulpelių etiketės
- Vilkite laukelį "Kiekis" žemyn į Vertybės dėžutė tris kartus
- Pirmojo laukelio "Kiekis" pakeiskite jo tinkintą pavadinimą į "Iš viso" ir jo skaičiaus formatą Apskaita (0 skaitmenų po kablelio)
- Antrame lauke "Suma" pakeiskite jo tinkintą pavadinimą į "Vidutiniškai", jo funkciją - " Vidurkis ir tai numerio formatas Apskaita (0 skaitmenų po kablelio)
- Trečiajame lauke "Suma" pakeiskite savo vardą į "Count" ir jo funkciją į Grafas
-
Vilkite automatiškai sukurtą
Image laukas nuo Stulpelių etiketės į Eilučių etiketės
Štai ką mes baigsime:
Išvada
"Microsoft Excel" sukurtose "PivotTables" yra daugybė daug daugiau funkcijų ir parinkčių. Tai yra per daug įtraukta į tokį straipsnį. Siekiant visiškai padengti PivotTables potencialą, reikės nedidelės knygos (arba didelės svetainės). Drąsūs ir (arba) geeky skaitytojai gali dar gana lengvai išnagrinėti PivotTables: tiesiog dešiniuoju pelės klavišu spustelėkite beveik viską ir sužinokite, kokios galimybės tampa prieinamos. Taip pat yra dviejų juostų kortelių: PivotTable įrankiai / parinktys ir Dizainas. Nesvarbu, ar padarysite klaidą - lengva ištrinti PivotTable ir pradėti iš naujo - galimybė, kad seni "Lotus 1-2-3" DOS vartotojai niekada neturėjo.
Jei dirbate "Office 2007", galbūt norėsite sužinoti mūsų straipsnį, kaip sukurti "PivotTable" programoje "Excel 2007".
Mes įtraukėme "Excel" darbaknygę, kurią galite atsisiųsti, kad galėtumėte naudotis savo "PivotTable" įgūdžiais. Jis turėtų dirbti su visomis "Excel" versijomis nuo 97 metų.
Atsisiųskite mūsų praktikos Excel darbaknygę
