„Excel“ duomenų analizė: „Excel Pivot“ lentelės kūrimas

„Excel“ suvestinės lentelės yra labai universalios, nes leidžia lengvai analizuoti didelio duomenų kiekio santraukas naudojant įvairias suvestinės funkcijas (nors sumos, sukurtos naudojant SUM funkciją, tikriausiai liks jūsų senuoju budėjimo režimu). Nustatydami originalią Excel suvestinę lentelę, priimate kelis sprendimus: kokią suvestinės funkciją naudoti, kuriems stulpeliams (laukams) taikoma suvestinės funkcija ir su kokiais stulpeliais (laukais) šie skaičiavimai pateikiami lentelėse.

„Excel“ suvestinės lentelės puikiai tinka kryžminiam duomenų sąrašo duomenų rinkiniui sujungti su kitu. Pavyzdžiui, galite sukurti suvestinę lentelę iš darbuotojų duomenų bazės lentelės, kurioje sumuojami atlyginimai už kiekvieną darbo kategoriją, suskirstyti į kryžmines lenteles (suskirstytas) pagal skyrių arba darbo vietą.

„Excel“ suvestinių lentelių kūrimas pagal rekomendaciją

Jei naujos suvestinės lentelės kūrimas naudojant greitosios analizės įrankį yra per daug darbo, jas sugeneruosite akimirksniu naudodami komandos mygtuką Rekomenduojamos suvestinės lentelės. Norėdami naudoti šį metodą, atlikite šiuos paprastus veiksmus:

Duomenų sąraše pasirinkite langelį, kuriam norite sukurti naują suvestinę lentelę.
Jei jūsų duomenų sąraše yra stulpelių antraščių eilutė su gretimomis duomenų eilutėmis, tai gali būti bet kuris lentelės langelis.

Juostelės skirtuke Įterpimas pasirinkite komandos mygtuką Rekomenduojamos „PivotTables“ arba paspauskite „Alt“ + NSP.
„Excel“ rodo dialogo langą Rekomenduojamos „PivotTables“. Šiame dialogo lange yra kairėje pusėje esantis sąrašo laukelis, kuriame rodomi visų siūlomų suvestinių lentelių, kurias „Excel 2019“ gali sukurti iš sąraše esančių duomenų, pavyzdžiai.

Naujos suvestinės lentelės kūrimas iš pavyzdinių suvestinių lentelių, rodomų dialogo lange Rekomenduojamos suvestinės lentelės.

Kairėje esančiame sąrašo laukelyje pasirinkite suvestinės lentelės, kurią norite sukurti, pavyzdį ir spustelėkite Gerai.

Kai tik spustelėsite Gerai, „Excel“ sukuria naują suvestinę lentelę, vadovaudamasi pasirinktu pavyzdžiu savo darbalapyje (1 lapas), įterptą prieš kitus darbaknygėje. Ši suvestinė lentelė yra pasirinkta naujame lape, kad suvestinės lentelės laukų užduočių sritis būtų rodoma dešinėje Excel darbalapio lango pusėje, o kontekstinis skirtukas PivotTable įrankiai būtų rodomas juostelėje. Galite naudoti šios užduočių srities ir kontekstinio skirtuko parinktis, kad tinkintumėte naują „Excel“ suvestinę lentelę.

„Excel“ suvestinių lentelių kūrimas rankiniu būdu

Kartais nė viena iš suvestinių lentelių, kurias siūlo „Excel 2019“, kuriant naują lentelę su greitosios analizės įrankiu arba komandos mygtuku Rekomenduojamos „PivotTables“, neatitinka jūsų turimos duomenų suvestinės tipo. Tokiais atvejais galite pasirinkti siūlomą suvestinę lentelę, kurios išdėstymas yra artimiausias tam, ką galvojate, arba galite pasirinkti sukurti suvestinę lentelę nuo nulio (šis procesas nėra toks sudėtingas ar atimantis daug laiko).

Norėdami rankiniu būdu sukurti naują suvestinę lentelę iš darbalapio su analizuojamais duomenimis, nustatykite langelio žymeklį kur nors šio sąrašo langeliuose ir spustelėkite juostelės skirtuke Įterpti esantį komandos mygtuką PivotTable arba paspauskite Alt+NV.

Tada „Excel“ atidaro dialogo langą „Sukurti PivotTable“ ir parenka visus duomenis iš sąrašo, kuriame yra langelio žymeklis (nurodytas langeliu aplink langelių diapazoną). Tada galite koreguoti langelių diapazoną teksto laukelyje Lentelė/diapazonas, esantis po mygtuku Pasirinkti lentelę arba diapazoną, jei laukelyje nėra visų duomenų, kuriuos reikia apibendrinti suvestinės lentelėje.

Pagal numatytuosius nustatymus „Excel“ sukuria naują suvestinę lentelę naujame darbalapyje, kurį prideda prie darbaknygės. Tačiau jei norite, kad suvestinė lentelė būtų rodoma tame pačiame darbalapyje, spustelėkite mygtuką Esamas darbalapis ir teksto laukelyje Vieta nurodykite naujos lentelės pirmojo langelio vietą. (Tiesiog įsitikinkite, kad ši nauja suvestinė lentelė nepersidengs su esamomis duomenų lentelėmis.)

Dialogo lange Sukurti PivotTable nurodykite duomenų šaltinį ir suvestinės lentelės vietą.

Jei jūsų suvestinės lentelės duomenų šaltinis yra išorinė duomenų bazės lentelė, sukurta naudojant atskirą duomenų bazės valdymo programą, pvz., Access, turite spustelėti mygtuką Naudoti išorinį duomenų šaltinį, spustelėkite mygtuką Pasirinkti ryšį, tada spustelėkite ryšį dialogo lange Esami ryšiai. Be to, „Excel 2019“ palaiko duomenų analizę iš kelių susijusių lentelių darbalapyje (vadinama duomenų modeliu). Jei naujoje „Excel“ suvestinės lentelės duomenys, kuriuos kuriate, bus analizuojami kartu su kita esama suvestinės lentele, būtinai pažymėkite žymės langelį Pridėti šiuos duomenis prie duomenų modelio.

Jei kaip naujos suvestinės lentelės vietą dialogo lange Sukurti suvestinę lentelę nurodote naują darbalapį, spustelėjus Gerai, programa darbaknygės priekyje įterpia naują darbalapį su tuščiu naujos suvestinės lentelės tinkleliu. Jis taip pat atidaro užduočių sritį „PivotTable Fields“ dešinėje darbalapio srities pusėje ir prie juostelės prideda kontekstinį skirtuką „PivotTable Tools“. „PivotTable Fields“ užduočių sritis yra padalinta į dvi sritis: sąrašo laukelį „Pasirinkti laukus, kuriuos norite įtraukti į ataskaitą“ su visų duomenų sąrašo laukų pavadinimais, kuriuos galite pasirinkti kaip lentelės šaltinį, prieš kurį įrašomi tušti žymės langeliai, ir laukų vilkimas. tarp Žemiau esančių sričių, padalinta į keturias kritimo zonas (FILTRAI, Stulpeliai, EILUTĖS ir VERTĖS).

Užpildyta „Excel“ suvestinė lentelė, pridėjus laukus iš darbuotojų duomenų sąrašo į įvairias kritimo zonas.

Taip pat galite įterpti naują darbalapį su tuščia suvestinės lentelės tinkleliu, dialogo lange Recommended PivotTable arba greitosios analizės įrankio parinkčių paletėje pasirinkę mygtuką Tuščia suvestinės lentelė (rodoma tik tada, kai greitoji analizė negali pasiūlyti suvestinių lentelių jūsų duomenims). Tiesiog atminkite, kad kai šiame dialogo lange arba paletėje pasirenkate mygtuką Tuščia „PivotTable“, „Excel 2019“ pirmiausia neatveria dialogo lango „Sukurti PivotTable“. Jei jums reikia naudoti bet kurią iš šiame dialogo lange siūlomų parinkčių kuriant naują suvestinę lentelę, turite sukurti suvestinę lentelę naudodami komandos mygtuką „PivotTable“, o ne skirtuko „Įterpti“ komandos mygtuką Rekomenduojamos „PivotTables“.

Norėdami užbaigti naują „Excel“ suvestinę lentelę, tereikia užduočių srities „PivotTable Fields“ laukus priskirti įvairioms lentelės dalims. Tai galite padaryti nuvilkdami lauko pavadinimą iš sąrašo lango Pasirinkti laukus, kuriuos norite įtraukti į ataskaitą ir įmesdami jį į vieną iš keturių toliau nurodytų sričių, vadinamų nuleidimo zonomis:

• FILTRAI: šioje srityje yra laukai, leidžiantys peržiūrėti duomenų santraukas, rodomas faktinėje suvestinės lentelėje, filtruojant duomenų rinkinius – jie veikia kaip ataskaitos filtrai. Pavyzdžiui, jei duomenų sąrašo lauką Metai priskiriate kaip ataskaitos filtrą, suvestinės lentelėje galite rodyti atskirų metų arba visų duomenų sąraše nurodytų metų duomenų suvestines.
• Stulpeliai: šioje srityje yra laukai, nustatantys suvestinės lentelės stulpeliuose rodomų duomenų išdėstymą.
• EILTELĖS: šioje srityje yra laukai, nustatantys duomenų, rodomų suvestinės lentelės eilutėse, išdėstymą.
• VERTĖS: šioje srityje yra laukai, nustatantys, kurie duomenys pateikiami suvestinės lentelės langeliuose – tai reikšmės, kurios apibendrinamos paskutiniame jos stulpelyje (sumuojama pagal numatytuosius nustatymus).

Norėdami suprasti, kaip šios įvairios zonos yra susijusios su suvestinės lentele, pažiūrėkite į užpildytą „Excel“ suvestinę lentelę.

Šiai suvestinės lentelės lauką Lytis priskiriate iš duomenų sąrašo (laukas, kuriame yra F (moteriai) arba M (vyrui), kad nurodytumėte darbuotojo lytį FILTRAI. įvairių įmonės padalinių pavadinimai) į Stulpelių kritimo zoną, lauką Vieta (kuriame yra įvairių miestų, turinčių įmonių biurus, pavadinimai) į EILUČIŲ kritimo zoną, o lauką Atlyginimas į kritimo zoną VERTYBĖS. Rezultatas, ši suvestinė lentelė dabar rodo kiekvieno skyriaus darbuotojų vyrų ir moterų atlyginimų sumą (stulpeliais), o tada pateikia šias sumas pagal jų įmonės vietą (kiekvienoje eilutėje).

Kai tik įtraukiate laukus į naują suvestinę lentelę (arba pasirenkate esamos lentelės langelį darbalapyje), „Excel“ pasirenka kontekstinio skirtuko „PivotTable Tools“ skirtuką Analizė, kuris automatiškai rodomas juostelėje. Tarp daugelio šiame skirtuke esančių grupių pabaigoje rasite grupę Rodyti, kurioje yra šie naudingi komandų mygtukai:

• Laukų sąrašas, skirtas paslėpti ir iš naujo rodyti „PivotTable“ laukų sąrašo užduočių sritį dešinėje darbalapio srities pusėje
• +/– Mygtukai, skirti paslėpti ir iš naujo rodyti išplėtimo (+) ir sutraukimo (-) mygtukus prieš tam tikrus stulpelių laukus arba eilučių laukus, leidžiančius laikinai pašalinti ir iš naujo parodyti konkrečias suvestines vertes suvestinės lentelėje.
• Laukų antraštės, skirtos paslėpti ir iš naujo rodyti laukus, priskirtus stulpelių etiketėms ir eilučių etiketėms suvestinės lentelėje