Šioje pamokos dalyje pateikta žingsnis po žingsnio instrukcija Kaip sukurti išplėstinę „PivotTable“ šiuolaikinėse „Excel“ versijose (2007 m. ir naujesnėse). Tiems, kurie dirba su ankstesnėmis „Excel“ versijomis, rekomenduojame šį straipsnį: Kaip sukurti išplėstinę suvestinę lentelę programoje „Excel 2003“?
Kaip pradinius duomenis kurdami suvestinę lentelę naudojame 2016 m. pirmojo ketvirčio įmonės pardavimų duomenų lentelę.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data | Sąskaitos faktūros Nr | Suma | Pardavimų konsultantas. | Regionas |
| 2 | 01/01/2016 | 2016-0001 | $819 | Barnesas | Šiaurė |
| 3 | 01/01/2016 | 2016-0002 | $456 | Ruda | Pietų |
| 4 | 01/01/2016 | 2016-0003 | $538 | Jonesas | Pietų |
| 5 | 01/01/2016 | 2016-0004 | $1,009 | Barnesas | Šiaurė |
| 6 | 01/02/2016 | 2016-0005 | $486 | Jonesas | Pietų |
| 7 | 01/02/2016 | 2016-0006 | $948 | Kalvis | Šiaurė |
| 8 | 01/02/2016 | 2016-0007 | $740 | Barnesas | Šiaurė |
| 9 | 01/03/2016 | 2016-0008 | $543 | Kalvis | Šiaurė |
| 10 | 01/03/2016 | 2016-0009 | $820 | Ruda | Pietų |
| 11 | ... | ... | ... | ... | ... |
Toliau pateiktame pavyzdyje sukursime „PivotTable“, kurioje bus rodomas bendras metų pardavimas per mėnesį, suskirstytas pagal regioną ir pardavėją. Šios suvestinės lentelės kūrimo procesas aprašytas toliau.
Jei reikia, mūsų suvestinė lentelė (žr. paveikslėlį žemiau) dabar rodo pardavimų sumą pagal mėnesį, suskirstytą pagal regioną ir pardavėją.
Norėdami pagerinti „PivotTable“ išvaizdą, turėtumėte tinkinti formatavimą. Pavyzdžiui, jei vertės stulpeliuose B – G pakoreguokite piniginį formatą, tada suvestinė lentelė taps daug lengviau skaitoma.
Filtrai sukamojoje lentelėje
„PivotTable“ filtrai leidžia rodyti informaciją apie vieną reikšmę arba pasirinktinai kelioms reikšmėms iš turimų duomenų laukų. Pavyzdžiui, pirmiau pateiktoje suvestinės lentelėje galėsime peržiūrėti tik pardavimo regiono duomenis. Šiaurė arba tik regionui Pietų.
Kad būtų rodomi tik pardavimo regiono duomenys Šiaurė, skydelyje Suvestinės lentelės laukai(Pivot Table Field List) vilkite lauką Regionasį regioną Filtrai(Ataskaitų filtrai).
Laukas Regionas pasirodo suvestinės lentelės viršuje. Šiame lauke atidarykite išskleidžiamąjį sąrašą ir pasirinkite jame regioną Šiaurė... Sukamojoje lentelėje (kaip parodyta paveikslėlyje žemiau) bus rodomos tik regiono reikšmės Šiaurė.
Galite greitai perjungti į tik regiono duomenų rodinį Pietų- Tam jums reikia išskleidžiamojo sąrašo lauke Regionas pasirinkite Pietų.
Pivot lentelės yra viena iš labiausiai veiksmingomis priemonėmis programoje MS Excel. Jų pagalba per kelias sekundes galite konvertuoti milijoną duomenų eilučių į glaustą ataskaitą. Be greito rezultatų apibendrinimo, suvestinės lentelės leidžia tiesiogine prasme pakeisti analizės atlikimo būdą, vilkdami laukus iš vienos ataskaitos srities į kitą.
Pivot lentelės taip pat yra vienas iš labiausiai neįvertintų „Excel“ įrankių. Dauguma vartotojų nežino, kokios galimybės yra jų rankose. Įsivaizduokime, kad sukimosi lentelės dar nebuvo išrastos. Dirbate įmonėje, kuri parduoda savo produktus įvairiems klientams. Paprastumo dėlei asortimente yra tik 4 prekės. Produktus reguliariai perka pora dešimčių klientų, kurie yra skirtingi regionai... Kiekviena operacija įvedama į duomenų bazę ir reiškia atskirą eilutę.
Jūsų direktorius paveda sudaryti trumpą visų produktų pardavimo ataskaitą pagal regioną (vietovę). Problemą galima išspręsti taip.
Pirmiausia sukurkime lentelės išdėstymą, ty antraštę, kurią sudaro unikalios produkto ir regiono reikšmės. Padarykite produkto stulpelio kopiją ir pašalinkime dublikatus. Tada naudokite specialų įklijavimą, kad perkeltumėte stulpelį į eilutę. Tą patį darome su sritimis, tik be perkėlimo. Gauname ataskaitos antraštę.
Ši lentelė turi būti užpildyta, t.y. apibendrinti atitinkamų prekių ir regionų pajamas. Tai nesunku padaryti naudojant SUMIF funkciją. Taip pat pridėsime sumas. Jūs gausite suvestinę ataskaitą apie pardavimus gamybos srityje.
Atlikote užduotį ir rodote ataskaitą direktoriui. Žvelgdamas į skaičiuoklę, jis generuoja keletą puikių idėjų vienu metu.
– Ar galima daryti ataskaitą ne apie pajamas, o apie pelną?
– Ar galima produktus rodyti pagal eilutes, o regionus – pagal stulpelius?
– Ar tokias lenteles galima daryti kiekvienam vadovui atskirai?
Net jei tu patyręs vartotojas Excel, naujų ataskaitų kūrimas užtruks daug laiko. Jau nekalbant apie galimas klaidas. Tačiau, jei turite suvestines lenteles, atsakymas yra taip, man reikia 5 minučių, o gal ir mažiau.
Štai kaip tai padaryti. Atidarome pradinius duomenis. Pivot lentelę galima sukurti pagal įprastą diapazoną, tačiau teisingiau būtų ją konvertuoti į Excel lentelę. Tai iš karto išspręs automatinio naujų duomenų fiksavimo problemą. Pasirinkite bet kurį langelį ir eikite į skirtuką Įdėti... Kairėje juostelės pusėje yra du mygtukai: ir Rekomenduojamos sukamosios lentelės.
Jei nežinote, kaip tvarkyti turimus duomenis, galite naudoti komandą Rekomenduojamos sukamosios lentelės... „Excel“ rodys galimų maketų miniatiūras pagal jūsų duomenis.
Spustelėkite atitinkamą parinktį ir suvestinė lentelė yra paruošta. Belieka tik priminti tai, tk. vargu ar standartinis ruošinys visiškai atitiks jūsų norus. Jei jums reikia sukurti suvestinę lentelę nuo nulio arba turite seną programos versiją, paspauskite mygtuką. Atsiras langas, kuriame turėsite nurodyti šaltinio diapazoną (jei suaktyvinsite bet kurį Excel lentelės langelį, jis bus nustatytas pats) ir būsimos suvestinės lentelės vietą (pagal numatytuosius nustatymus ji bus pasirinkta naujas lapas).
Labai dažnai čia nieko nereikia keisti. Spustelėjus Gerai, bus sukurtas naujas „Excel“ lapas su tuščios suvestinės lentelės išdėstymu.
Prieš pereidami prie nustatymų, susipažinkime su sąsaja ir pagrindinėmis sąvokomis. Lentelės išdėstymą galima konfigūruoti skydelyje Suvestinės lentelės laukai, kuris yra dešinėje lapo pusėje.
Skydelio viršuje yra visų galimų laukų, ty šaltinio duomenų stulpelių, sąrašas. Jei į maketą reikia įtraukti naują lauką, priešais galite įdėti žymimąjį laukelį – „Excel“ pati nustatys, kur šis laukas turi būti dedamas. Tačiau toli gražu ne visada įmanoma atspėti, todėl geriau nutempti jį pele į reikiamą maketo vietą. Taip pat ištrinami laukai: panaikinkite laukelio žymėjimą arba vilkite atgal.
Suvestinę lentelę sudaro 4 sritys, esančios skydelio apačioje: reikšmės, eilutės, stulpeliai, filtrai. Pažvelkime atidžiau į jų paskirtį.
Vertybių diapazonas- tai centrinė suvestinės lentelės dalis su reikšmėmis, gautomis pasirinktu būdu sujungus šaltinio duomenis.
Daugeliu atvejų agregacija vyksta pagal Sumavimas... Jei visi duomenys pasirinktame lauke yra skaičių formatu, „Excel“ priskirs numatytąjį sumavimą. Jei pirminiuose duomenyse yra bent vienas tekstas arba tuščias langelis, tada vietoj sumos ji bus apskaičiuojama Kiekis ląstelės. Mūsų pavyzdyje kiekvienas langelis yra visų atitinkančių elementų atitinkamame regione suma.
„PivotTable“ langeliuose galite naudoti kitus skaičiavimo metodus. Jų yra apie 20 rūšių (vidutinė, minimali vertė, proporcija ir kt.). Yra keletas būdų, kaip pakeisti skaičiavimo metodą. Paprasčiausia yra dešiniuoju pelės mygtuku spustelėti bet kurį norimo lauko langelį pačioje suvestinės lentelėje ir pasirinkti kitą agregavimo metodą.
Linijos sritis- eilučių, esančių kairiajame stulpelyje, pavadinimai. Tai visos unikalios pasirinkto lauko (stulpelio) reikšmės. Eilučių srityje gali būti keli laukai, tada lentelė pasirodo daugiapakopė. Čia dažniausiai pateikiami kokybiniai kintamieji, tokie kaip produktų pavadinimai, mėnesiai, regionai ir kt.
Stulpelio plotas- panašus į eilutes, rodo unikalias pasirinkto lauko reikšmes, tik pagal stulpelius. Stulpelių pavadinimai taip pat dažniausiai būna kokybiniai. Pavyzdžiui, metai ir mėnesiai, prekių grupės.
Filtro sritis- naudojamas, kaip rodo pavadinimas, filtravimui. Pavyzdžiui, pačioje ataskaitoje pateikiami produktai pagal regioną. Turite apriboti suvestinę lentelę iki konkrečios pramonės šakos, tam tikro laikotarpio arba vadovo. Tada filtravimo srityje įdedamas filtravimo laukas ir išskleidžiamajame sąraše pasirenkama reikiama reikšmė.
Pridėję ir pašalinę laukus nurodytose srityse, per kelias sekundes galite tinkinti bet kurią duomenų dalį.
Pažiūrėkime, kaip tai veikia. Kol kas sukurkime tą pačią lentelę, kuri jau buvo sukurta naudojant funkciją SUMIFS. Norėdami tai padaryti, vilkite į sritį Vertybės lauke „Pajamos“, regione Stygos vilkite lauką „Regionas“ (pardavimo regionas). Stulpeliai- "Produktas".
Dėl to gauname tikrą sukimosi lentelę.
Jai sukurti prireikė 5–10 sekundžių. Taip pat lengva keisti esamą suvestinę lentelę. Pažiūrėkime, kaip režisieriaus norai nesunkiai paverčiami realybe.
Pajamas pakeiskime pelnu.
Produktai ir sritys taip pat keičiami vilkdami pelę.
Yra keletas suvestinių lentelių filtravimo įrankių. Tokiu atveju lauką „Valdytojas“ tiesiog įdėsime į filtrų sritį.
Viskas užtruko kelias sekundes. Štai kaip iš tikrųjų lengva dirbti su suvestinės lentelėmis. Žinoma, ne visos užduotys yra tokios nereikšmingos. Taip pat kartais reikia naudoti sudėtingesnį agregavimo metodą, pridėti apskaičiuotų laukų, sąlyginio formatavimo ir pan. Bet tai jau yra pažangesnis suvestinių lentelių naudojimas.
Pradiniai duomenys
Norint sėkmingai dirbti su suvestinės lentelėmis, šaltinio duomenys turi atitikti tam tikrus reikalavimus. Būtina sąlyga yra pavadinimų buvimas virš kiekvieno lauko (stulpelio), pagal kurį šie laukai bus identifikuoti. Dabar keletas naudingų patarimų.
1. Geriausias duomenų formatas yra Excel lentelė. Gerai tuo, kad kiekvienas laukas turi pavadinimą ir pridedant naujų eilučių jos automatiškai įtraukiamos į suvestinę lentelę.
2. Venkite kartoti grupes stulpeliuose. Pavyzdžiui, visos datos turi būti viename lauke, o ne suskirstytos, pavyzdžiui, pagal mėnesius į atskirus stulpelius.
3. Pašalinkite tarpus ir tuščius langelius, kitaip ši eilutė gali iškristi iš analizės.
4. Taikykite teisingą laukų formatavimą. Skaičiai turi būti skaitmeninio formato, datos turi būti datos. Priešingu atveju kils problemų su grupavimu ir matematiniu apdorojimu. Bet čia Excel jums padės, tk. jis gana gerai apibrėžia patį duomenų formatą.
Apskritai reikalavimų yra nedaug, tačiau turėtumėte juos žinoti.
Jei pakeisite šaltinį (pavyzdžiui, pridėsite naujų eilučių), suvestinė lentelė nepasikeis, kol jos neatnaujinsite dešiniuoju pelės mygtuku
arba
per komandą skirtuke Duomenys – Atnaujinti viską.
Tai daroma tyčia dėl to, kad sukamoji lentelė užima daug vietos RAM. Todėl norint ekonomiškiau naudoti kompiuterio išteklius, darbas vyksta ne tiesiogiai su šaltiniu, o su talpykla, kurioje yra šaltinio duomenų momentinė nuotrauka.
Naudodami suvestines lenteles, net ir tokiu pagrindiniu lygiu, galite žymiai padidinti didelių duomenų kiekių apdorojimo greitį ir kokybę. Jei kas nors lieka neaišku, parašykite komentaruose.
Žemiau yra vaizdo įrašo pamoka, kaip sukurti paprastą „PivotTable“ programoje „Excel“.
P.S. Išsamias žinias apie sukimosi lenteles galite gauti adresu
Norėdami geriau suprasti, kaip programoje kurti suvestines lenteles, turime išmokti jas sukurti programoje Excel.
Suvestinės lentelės programoje „Excel“.
Suvestinės lentelės formoje yra trys sritys, skirtos laukams vilkti ir nuleisti: stulpeliams, eilutėms ir duomenims.
Iš dialogo PivotTable laukų sąrašas vilkite langelį Pavardėį regioną Vilkite eilučių laukus čia... Sukamojo stalo forma šiek tiek pasikeitė.
„Excel“ stulpelyje „Pavardė“ pasirinko visas unikalias reikšmes ir įdėjo jas į būsimos ataskaitos eilutes.
Dabar vilkite lauką Suma v duomenų sritis.
„Excel“ apskaičiavo bendrą kiekvieno pardavėjo pardavimą. Svarbiausia yra greita ir be klaidų. Ataskaita gali būti laikoma parengta.
Tačiau norėdami geriau suprasti suvestinės lentelės galimybes, nurodysime ir stulpelio sritį. Dabar ši sritis atrodo paslėpta, bet iš tikrųjų ji matoma. Perkelti lauką data ant viršutiniame dešiniajame ataskaitos kampe, čia:
Kai tik atleisite pelės mygtuką, ataskaita iškart pasikeis, parodydama kiekvieno pardavėjo pardavimų dinamiką pagal dieną.
Ataskaita pasirodė pernelyg išsami. Sugrupuokime jį pagal mėnesį.
- Dešiniuoju pelės mygtuku spustelėkite ataskaitos lauką data, pasirodžiusiame meniu pasirinkite Grupė ir struktūra, tada Grupė...
- Kitame dialogo lange „Excel“ prašo tiksliai nurodyti, kaip grupuoti, ir iškart pasiūlo pagal mėnesį (paryškinta). Nieko nekeiskite ir paspauskite Gerai.
Pardavimo data dabar ataskaitoje sugrupuota pagal mėnesį.
Pakeiskite „Excel Pivot“ ataskaitą
Norėdami pakeisti ataskaitą, pavyzdžiui, pakeisti eilutės lauką, tiesiog vilkite lauką už ataskaitos ribų. Tada perkelkite ten kitą lauką, pavyzdžiui, lauką Produktas.
Gavo prekių pardavimo ataskaitą pagal mėnesį.
Bet kas atsitiks, jei pakeisime lauką data ant Pavardė.
Sukamųjų lentelių galimybės atrodo begalinės. Kaip tau tai patinka?
Santrauka
Kaip pradinius duomenis rengdami ataskaitą pasirinkome trumpų pardavimo lentelę. Bet tai gali būti tūkstančiai eilučių ir dešimtys stulpelių. Ataskaitos sudarymo būdas išliks toks pat: nurodėte stulpelius, eilutes ir duomenis.
Tu gavai pagrindinės žinios apie „Excel“ suvestinės lenteles. To pakanka, kad suprastumėte jų nustatymų principą ir naudą. Dabar pažiūrėkime
Suvestinės lentelės „Excel“ yra specialus konstrukcijos tipas, kuris reiškia, kad yra funkcija akimirksniu generuoti ataskaitą apie dokumentą. Su jų pagalba galite lengvai apibendrinti kai kuriuos to paties tipo duomenis.
Programoje Excel 2007 (MS Excel 2010 | 2013) suvestinė lentelė pirmiausia naudojama matematinei arba ekonominei duomenų analizei sudaryti.
Kaip sukurti suvestinę lentelę „Excel“.
Dokumentų duomenų analizė prisideda prie greitesnio ir teisingas sprendimas paskirtas užduotis. „Excel“ skaičiuoklių procesorius gali tvarkyti net pačius didžiausius ir sudėtingiausius dokumentus. Programai nebus sunku sudaryti ataskaitą ir apibendrinti duomenis.
Norėdami sukurti paprastą suvestinę lentelę, atlikite toliau nurodytus veiksmus.
- Naudodami skirtuką „įterpti“, esantį pagrindiniame programos įrankių juostos meniu, pasirinkite rekomenduojamų suvestinių lentelių kūrimo mygtuką;
- Atsidariusiame programos dialogo lange spustelėkite jums patinkantį plokštės rėmelį. Tai leis naudotojui peržiūrėti maketą.
Pasirinkite tinkamiausią variantą, kuris gali pilnai parodyti visą informaciją;
Patarimas! Papildomus „PivotTable“ išdėstymus galima atsisiųsti iš oficialios „Microsoft“ svetainės.».
- Paspauskite mygtuką OK ir programa iškart įtrauks pasirinktą lentelę (arba tuščią maketą) į atvirą dokumento lapą. Taip pat programa automatiškai nustatys eilučių eiliškumą, pagal pateiktą informaciją;
- Norėdami pasirinkti lentelės elementus ir sutvarkyti juos rankiniu būdu, rūšiuokite turinį. Taip pat galite filtruoti duomenis. Iš esmės suvestinė lentelė yra nedidelės duomenų bazės prototipas.
Filtravimas yra būtinas, kai reikia greitai peržiūrėti tik tam tikrus stulpelius ir eilutes. Toliau pateikiamas pardavimo suvestinės lentelės pavyzdys po turinio filtravimo.
Taigi galite greitai peržiūrėti pardavimų apimtis atskiruose regionuose (mūsų atveju Vakarų ir Pietų);
Patartina naudoti jau sukurtus maketus, tačiau vartotojas gali pasirinkti tuščią lentelę ir ją užpildyti pats. Į tuščią šabloną reikia pridėti laukų, skaičiavimo formulių, filtrų.
Taip pat galite kurti susietas suvestinės lenteles keliuose dokumento lapuose vienu metu. Taigi galite analizuoti viso dokumento arba kelių dokumentų / lapų duomenis vienu metu.
Taip pat galite analizuoti išorinius duomenis naudodami suvestinės lenteles.
„Microsoft Excel“ suvestiniai skaičiavimai – formulės
Teikdami ataskaitas apie sugeneruotą suvestinę lentelę galite naudoti didelis skaičius statistines funkcijas ir formules, kad būtų galima išanalizuoti gautas reikšmes ir jas pagrindžiančius įvesties duomenis.
Į lentelę galite įtraukti, pavyzdžiui, komisinio mokesčio už mokėjimą apskaičiavimo elementą arba bet kokį kitą atsiskaitymo elementą. Visos formulės stulpeliuose ir eilutėse pridedamos naudojant lauką „Įterpti“.
Naudodami šį skirtuką galite sukurti grafiką, apskaičiuoti elementus pagal formulę ir sudaryti priklausomybės histogramą.
Suvestinės lentelėsExcel- nepakeičiamas įrankis dirbant su dideliais duomenų kiekiais. Naudodami suvestinę lentelę galite išskirti ir dirbti su tik konkrečiais duomenimis iš viso masyvo. Pavyzdžiui, konkrečios prekės pardavimo lygis iš viso asortimento, kiek prekių pardavė konkretus vadybininkas, ar kurioje parduotuvėje daugiausia buvo nupirkta tos ar kitos prekės ir pan.
Suvestinių lentelių kūrimasExcel
1 žingsnis. Su pele pasirinkite bendrąją lentelę, kad ji „pasidarytų mėlyna“.
2 žingsnis. Nepašalindami pasirinkimo, pasirinkite meniu „Įterpti“, jame spustelėkite mygtuką „Pivot Table“.
3 veiksmas. Prieš jus atsidarys naujas langas „Sukurti suvestinę lentelę“, kuriame turėsite spustelėti mygtuką „Gerai“.
4 veiksmas. Jūsų lape atsiras suvestinės lentelės rengyklė, su kuria galėsite sukurti savo suvestinės lentelę su tik jus dominančiais duomenimis. Tiesiog nuvilkite reikiamus laukus į vieną iš keturių sričių.
Nepaisant to, kad daugelis iš mūsų mano, kad „Excel“ yra labai sudėtinga ir nesuprantama programa, ji turi daugybę naudingų funkcijų ir galimybes kiekvienam vartotojui. „PivotTable“ programoje „Excel“ labai palengvins jūsų darbą su duomenimis, nes greitai surūšiuosite ir pasirinksite tik jums reikalingas reikšmes. Dėl mūsų patarimų dabar žinote, kaip vos keliais paspaudimais sukurti „Excel“ suvestinę lentelę. Sėkmės!