Pjaustyklių naudojimas „Excel“ suvestinėje lentelėje - pradedančiųjų vadovas

„Pivot Table“ „Excel 2010“ versija buvo sužavėta įvedus naują labai šaunią funkciją - „Slicers“.

„Pivot Table Slicer“ leidžia filtruoti duomenis, kai Pjaustyklės laukelyje pasirenkate vieną ar daugiau parinkčių (kaip parodyta žemiau).

Anksčiau pateiktame pavyzdyje „Slicer“ yra oranžinis langelis dešinėje, o „Pivot“ lentelę galite lengvai filtruoti tiesiog spustelėdami „Slicer“ srities mygtuką.

Pradėkime.

Spustelėkite čia ir atsisiųskite duomenų pavyzdžius ir sekite toliau.

Pjaustyklės įterpimas į „Excel“ suvestinę lentelę

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Tai netikras duomenų rinkinys (JAV mažmeninė prekyba) ir apima 1000 eilučių. Naudodamiesi šiais duomenimis, sukūrėme „Pivot“ lentelę, kurioje rodomi visi keturių regionų pardavimai.

Skaityti daugiau: Kaip sukurti „Pivot“ lentelę nuo nulio.

Įdėję „Pivot“ lentelę, galite įterpti pjaustytuvus.

Galima paklausti - kodėl man reikia pjaustytuvų?

Pjaustyklių jums gali prireikti, kai nenorite visos „Pivot“ lentelės, bet tik jos dalies. Pvz., Jei nenorite matyti visų regionų pardavimų, o tik pietų, pietų ir vakarų, galite įterpti pjaustytuvą ir greitai pasirinkti norimą (-us) regioną (-us), kuriam norite gauti pardavimo duomenys.

Pjaustyklės yra labiau vizualus būdas, leidžiantis filtruoti „Pivot Table“ duomenis pagal pasirinkimą.

Štai šios pivotinės lentelės pjaustytuvo įterpimo veiksmai:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į Įterpti -> Filtras -> Pjaustyklė.
• Dialogo lange Įterpti pjaustytuvus pasirinkite aspektą, kurio duomenis galite filtruoti. Pjaustyklėje būtų išvardyti visi galimi matmenys ir vienu metu galite pasirinkti vieną ar daugiau matmenų. Pvz., Jei pasirinksiu tik regioną, jis įterps tik regiono pjaustytuvo laukelį, o jei pasirinksiu regioną ir mažmenininko tipą abu, įterps du pjaustytuvus.
• Spustelėkite Gerai. Į darbalapį įterpsite pjaustytuvą (-us).

Atminkite, kad „Slicer“ automatiškai atpažins visus unikalius pasirinkto aspekto elementus ir išvardys juos pjaustyklės laukelyje.

Įdėję pjaustyklę, galite filtruoti duomenis tiesiog spustelėję elementą. Pavyzdžiui, jei norite parduoti tik Pietų regioną, spustelėkite Pietų. Pastebėsite, kad pasirinktas elementas įgauna kitokį spalvų atspalvį, palyginti su kitais sąrašo elementais.

Taip pat galite pasirinkti kelis elementus vienu metu. Norėdami tai padaryti, laikykite nuspaudę valdymo klavišą ir spustelėkite tuos, kuriuos norite pasirinkti.

Jei norite išvalyti pasirinkimą, spustelėkite filtro piktogramą (su raudonu kryžiumi) viršuje dešinėje.

Kelių pjaustytuvų įterpimas į suvestinę lentelę

Taip pat galite įterpti kelis pjaustytuvus, pasirinkdami daugiau nei vieną aspektą dialogo lange Įterpti pjaustytuvus.

Norėdami įdėti kelis pjaustytuvus:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į Įterpti -> Filtras -> Pjaustyklė.
• Dialogo lange Įterpti pjaustytuvus pasirinkite visus matmenis, kurių pjaustytuvus norite gauti.
• Spustelėkite Gerai.

Į darbalapį bus įterpti visi pasirinkti pjaustytuvai.

Atminkite, kad šie pjaustytuvai yra susieti vienas su kitu. Pvz., Jei regiono filtre pasirinksiu „Viduriniai Vakarai“, o mažmenininko tipo filtre - „Daugialypė linija“, tada bus rodomi tik visų Vidurio Vakarų regiono mažmenininkų pardavimai.

Be to, jei pasirinksiu Vidurio vakarus, atkreipkite dėmesį, kad antrojo filtro specialumo parinktis įgauna šviesesnį mėlyną atspalvį (kaip parodyta žemiau). Tai rodo, kad Vidurio Vakarų regione nėra specialių mažmenininkų duomenų.

Pjaustyklės vs. Ataskaitų filtrai

Kuo skiriasi pjaustytuvai ir ataskaitų filtrai?

Pjaustyklės atrodo labai šauniai ir jas lengva naudoti. „Pivot Table“ stiprybė slypi tame, kad norint juo naudotis nereikia daug įgūdžių. Viskas, ką jums reikia padaryti, tai nuvilkti ir spustelėti čia ir ten, ir per kelias sekundes turėsite puikią ataskaitą.

Nors ataskaitų filtrai puikiai atlieka šį darbą, pjaustytuvai leidžia dar lengviau filtruoti suvestinę lentelę ir (arba) ją perduoti visiems, neturintiems žinių apie „Excel“ ar „Pivot“ lenteles. Kadangi tai toks intuityvus, net ir tas asmuo gali pats naudotis šiomis pjaustyklėmis spustelėdamas jį ir filtruodamas duomenis. Kadangi tai yra vizualiniai filtrai, kiekvienas gali lengvai tai įsisavinti, net kai pirmą kartą jį naudoja.

Štai keletas esminių skirtumų tarp pjaustytuvų ir ataskaitų filtrų:

• Pjaustyklės neužima fiksuoto langelio darbalapyje. Galite perkelti juos kaip bet kurį kitą objektą ar formą. Ataskaitų filtrai susieti su langeliu.
• Ataskaitų filtrai susieti su konkrečia „Pivot“ lentele. Kita vertus, pjaustytuvus galima susieti su keliomis „Pivot“ lentelėmis (kaip pamatysime vėliau šioje pamokoje).
• Kadangi ataskaitų filtras užima fiksuotą langelį, jį lengviau automatizuoti naudojant VBA. Kita vertus, pjaustyklė yra objektas ir jam reikia sudėtingesnio kodo.

Slicer formatavimas

„Slicer“ yra labai lankstus formuojant.

Štai dalykai, kuriuos galite pritaikyti pjaustyklėje.

Pjaustyklės spalvų keitimas

Jei jums nepatinka numatytosios pjaustyklės spalvos, galite lengvai ją pakeisti.

• Pasirinkite pjaustytuvą.
• Eikite į Pjaustyklės įrankiai -> Parinktys -> Pjaustyklės stiliai. Čia rasite daugybę skirtingų variantų. Pasirinkite tą, kuris jums patinka, ir jūsų pjaustyklė iškart suformuos tą formatą.

Jei jums nepatinka numatytieji stiliai, galite sukurti savo. Norėdami tai padaryti, pasirinkite parinktį Naujas pjaustytuvo stilius ir nurodykite savo formatą.

Kelių stulpelių gavimas pjaustytuvo laukelyje

Pagal numatytuosius nustatymus pjaustyklė turi vieną stulpelį ir jame yra išvardyti visi pasirinkto aspekto elementai. Jei turite daug elementų, „Slicer“ rodo slinkties juostą, kurią galite naudoti norėdami peržiūrėti visus elementus.

Galbūt norėsite, kad visi elementai būtų matomi be vargo. Tai galite padaryti sukūrę kelių stulpelių pjaustytuvą.

Padaryti tai:

• Pasirinkite pjaustytuvą.
• Eikite į Pjaustyklės įrankiai -> Parinktys -> Mygtukai.
• Pakeiskite stulpelių reikšmę į 2.

Tai akimirksniu padalins elementus į pjaustytuvą į du stulpelius. Tačiau gali atrodyti taip baisiai, kaip parodyta žemiau:

Tai atrodo netvarkinga, o visi vardai nerodomi. Kad atrodytų geriau, pakeiskite pjaustyklės dydį ir net joje esančius mygtukus.

Padaryti tai:

• Pasirinkite pjaustytuvą.
• Eikite į Slicer Tools -> Options.
• Pakeiskite mygtukų ir pjaustyklės aukštį ir plotį. (Atminkite, kad pjaustyklės dydį taip pat galite pakeisti tiesiog pasirinkdami jį ir naudodami pelę, kad sureguliuotumėte kraštus. Tačiau, norėdami pakeisti mygtuko dydį, turite atlikti pakeitimus tik parinktyse).

Pjaustyklės antraštės keitimas/pašalinimas

Pagal numatytuosius nustatymus pjaustyklė iš duomenų renka lauko pavadinimą. Pvz., Jei sukuriu regionų pjaustytuvą, antraštė automatiškai bus „Regionas“.

Galbūt norėsite pakeisti antraštę arba visiškai ją pašalinti.

Štai šie veiksmai:

• Dešiniuoju pelės mygtuku spustelėkite pjaustyklę ir pasirinkite Pjaustyklės nustatymai.
• Dialogo lange „Pjaustyklės nustatymai“ pakeiskite antraštės norimą norimą.
• Spustelėkite Gerai.

Tai pakeistų pjaustyklės antraštę.

Jei nenorite matyti antraštės, panaikinkite parinktį Rodyti antraštę dialogo lange.

Elementų rūšiavimas pjaustytuve

Pagal numatytuosius nustatymus elementai pjaustytuve yra surūšiuoti didėjančia tvarka teksto atveju, o nuo senesnių iki naujesnių - skaičių/datų atveju.

Galite pakeisti numatytąjį nustatymą ir netgi naudoti savo pasirinktinius rūšiavimo kriterijus.

Štai kaip tai padaryti:

• Dešiniuoju pelės mygtuku spustelėkite pjaustyklę ir pasirinkite Pjaustyklės nustatymai.
• Dialogo lange Pjaustyklės nustatymai galite pakeisti rūšiavimo kriterijus arba naudoti savo pasirinktinius rūšiavimo kriterijus.
• Spustelėkite Gerai.

Skaityti daugiau: Kaip sukurti pasirinktinius sąrašus „Excel“ (sukurti savo rūšiavimo kriterijus)

Elementų slėpimas be duomenų iš pjaustytuvo dėžutės

Gali atsitikti taip, kad kai kuriuose „Pivot“ lentelės elementuose nėra duomenų. Tokiais atvejais galite priversti pjaustytuvus paslėpti tą elementą.

Pavyzdžiui, žemiau esančiame paveikslėlyje turiu du pjaustytuvus (vieną regionui ir kitą mažmenininko tipui). Kai pasirenku vidurio vakarus, antrojo filtro specialusis elementas gauna šviesiai mėlyną atspalvį, rodantį, kad jame nėra duomenų.

Tokiais atvejais galite pasirinkti jo visai nerodyti.

Štai žingsniai, kaip tai padaryti:

• Dešiniuoju pelės mygtuku spustelėkite pjaustyklę, kurioje norite paslėpti duomenis, ir pasirinkite Pjaustyklės nustatymai.
• Dialogo lange Pjaustyklės nustatymai su parinktimis „Elementų rūšiavimas ir filtravimas“ pažymėkite parinktį „Slėpti elementus be duomenų“.
• Spustelėkite Gerai.

Pjaustyklės prijungimas prie kelių „Pivot“ lentelių

Pjaustyklę galima prijungti prie kelių „Pivot“ lentelių. Prisijungę galite naudoti vieną pjaustytuvą, kad vienu metu filtruotumėte visas prijungtas „Pivot“ lenteles.

Atminkite, kad norint sujungti skirtingas „Pivot“ lenteles su pjaustytuvu, „Pivot“ lentelėse turi būti naudojama ta pati „Pivot“ talpykla. Tai reiškia, kad jos sukurtos naudojant tuos pačius duomenis, arba viena iš „Pivot“ lentelių buvo nukopijuota ir įklijuota kaip atskira „Pivot“ lentelė.

Skaityti daugiau: Kas yra „Pivot Table Cache“ ir kaip ją naudoti?

Žemiau pateikiamas dviejų skirtingų „Pivot“ lentelių pavyzdys. Atminkite, kad pjaustyklė šiuo atveju veikia tik kairėje esančioje „Pivot“ lentelėje (ir nedaro įtakos dešinėje esančiai lentelėje).

Norėdami prijungti šią pjaustyklę prie abiejų „Pivot“ lentelių:

• Dešiniuoju pelės mygtuku spustelėkite pjaustyklę ir pasirinkite Pranešti apie ryšius. (Arba taip pat galite pasirinkti pjaustytuvą ir eiti į Pjaustyklės įrankiai -> Parinktys -> Pjaustyklė -> Ataskaitų jungtys).
• Dialogo lange Ataskaitų ryšiai pamatysite visus „Pivot Table“ pavadinimus, kurie turi tą pačią „Pivot“ talpyklą. Pasirinkite tuos, kuriuos norite prijungti prie pjaustyklės. Šiuo atveju turiu tik dvi „Pivot“ lenteles ir abi sujungiau su „Slicer“.
• Spustelėkite Gerai.

Dabar jūsų pjaustyklė prijungta prie abiejų „Pivot“ lentelių. Kai pasirenkate pjaustyklę, filtravimas bus atliekamas abiejose „Pivot“ lentelėse (kaip parodyta žemiau).

Dinaminių suvestinių diagramų kūrimas naudojant pjaustytuvus

Kaip naudojate pjaustytuvą su „Pivot Table“, taip pat galite jį naudoti su „Pivot“ diagramomis.

Kažkas, kaip parodyta žemiau:

Štai kaip galite sukurti šią dinaminę diagramą:

• Pasirinkite duomenis ir eikite į Įterpti -> Diagramos -> Pivot Diagrama.
• Dialogo lange Sukurti suvestinę diagramą įsitikinkite, kad diapazonas yra teisingas, ir spustelėkite Gerai. Tai įterps Pivot diagramą į naują lapą.
• Pasirinkite laukus (arba nuvilkite laukus į srities skyrių), kad gautumėte norimą „Pivot“ diagramą. Šiame pavyzdyje turime diagramą, kurioje rodomi keturių ketvirčių pardavimai pagal regionus. (Skaitykite čia, kaip sugrupuoti datas kaip ketvirčius).
• Kai paruošite „Pivot“ diagramą, eikite į Įterpti -> Pjaustyklė.
• Diagramoje pasirinkite norimą pjaustyklės dimensiją. Šiuo atveju noriu mažmenininkų tipų, todėl tikrinu tą matmenį.
• Suformatuokite diagramą ir pjaustytuvą ir baigsite.

Atminkite, kad prie tos pačios „Pivot“ diagramos galite prijungti kelis pjaustytuvus, taip pat galite prijungti kelias diagramas prie tos pačios pjaustyklės (taip pat, kaip mes sujungėme kelias „Pivot“ lenteles prie tos pačios pjaustyklės).

Spustelėkite čia, norėdami atsisiųsti pavyzdinius duomenis ir išbandyti patys.

Jums taip pat gali patikti šios „Pivot Table“ pamokos:

• Kaip grupuoti datas „Excel“ suvestinėje lentelėje.
• Kaip grupuoti skaičius „Pivot“ lentelėje „Excel“.
• Kaip atnaujinti „Pivot“ lentelę „Excel“.
• Šaltinio duomenų paruošimas „Pivot“ lentelei.
• Kaip pridėti ir naudoti „Excel“ suvestinės lentelės apskaičiuotą lauką.
• Kaip taikyti sąlyginį formatavimą „Excel“ suvestinėje lentelėje.
• Kaip pakeisti tuščias langelius nuliais „Excel“ suvestinėse lentelėse.