„Pivot“ lentelės yra viena iš galingiausių „Excel“ funkcijų. Net jei esate naujokas, galite susmulkinti didelius duomenų kiekius į naudingą informaciją. „Pivot Table“ gali padėti sukurti ataskaitas per kelias minutes. Lengvai analizuokite švarius duomenis, o jei duomenys nėra švarūs, tai gali padėti išvalyti duomenis. Nenoriu tavęs nuobodžiauti, todėl leiskimės į tai ir tyrinėkime.
Kaip sukurti „Pivot“ lentelę
Tai paprasta. Tiesiog pasirinkite savo duomenis. Eikite į Įterpti. Spustelėkite „Pivot Table“ ir viskas baigta.
Bet palauk. Prieš kurdami suvestinę lentelę įsitikinkite, kad visuose stulpeliuose yra antraštė.
Jei kuri nors stulpelio antraštė paliekama tuščia, suvestinė lentelė nebus sukurta ir pateiks klaidos pranešimą.
1 reikalavimas: visuose stulpeliuose turi būti antraštė, kad galėtumėte pradėti naudotis „Pivot“ lentelėmis „Excel“
Turėtumėte sutvarkyti savo duomenis su tinkama antrašte. Kai jį turėsite, galėsite įterpti suvestinę lentelę.
Įdėkite sukamąją lentelę iš juostos
Norėdami įterpti suvestinę lentelę iš meniu, atlikite šiuos veiksmus:
1. Pasirinkite savo duomenų diapazoną
2. Eikite į įterpimo skirtuką
3. Spustelėkite „Pivot Table“ piktogramą.
4. Pasirodys parinktis „Sukurti lentelę“.
5. Čia galite pamatyti pasirinktą duomenų diapazoną. Jei manote, kad tai nėra diapazonas, kurį norite pasirinkti, tiesiogiai pakeiskite diapazono dydį iš čia, o ne grįžkite atgal ir vėl pasirinkite duomenis.
6. Tada galite pasirinkti, kur norite pasukti lentelę. Aš rekomenduoju naudoti naują darbalapį, bet taip pat galite naudoti dabartinį darbalapį. Tiesiog apibrėžkite vietą laukelyje Vieta.
7. Dabar, kai baigsite su nustatymais, paspauskite mygtuką Gerai. Sukamąją lentelę turėsite naujame lape. Tiesiog pasirinkite suvestinių laukus. Pamatysime, kaip sukuriame duomenų santrauką naudodami suvestinę lentelę, bet pirmiausia išsiaiškinkime pagrindus. Šioje „Excel“ suvestinės lentelės pamokoje sužinosite daugiau, nei tikitės.
Įterpti „Pivot Table“ nuorodą (Alt> N> V)
Tai nuoseklus spartusis klavišas, skirtas atidaryti „Sukurti“ Suvestinės lentelės parinkčių langelis.
Paspauskite „Alt“ mygtuką ir atleiskite. Rezultatas N ir paleisk jį. Rezultatas V ir paleisk jį. Bus atidarytas parinkties langas Sukurti suvestinę lentelę.
Dabar tiesiog atlikite aukščiau aprašytą procedūrą, kad sukurtumėte „Excel“ suvestinę lentelę.
Įdėkite „Pivot Table“ nuorodą naudodami seną „Excel“ nuorodą (Alt> D> P)
Vienas dalykas, kuris man labiausiai patinka „Microsoft Excel“, yra tai, kad kiekvienoje naujoje „Excel“ versijoje jie pristato naujas funkcijas neatmeskite senų savybių (kaip MS padarė su pergale 8. Tai buvo apgailėtina). Tai leidžia seniems vartotojams normaliai dirbti su naujomis versijomis, kaip ir anksčiau.
Jei nuosekliai paspausite ALT, D ir P. klaviatūroje atsidarys „Excel“, sukuriant suvestinės lentelės vedlį.
Pasirinkite tinkamą parinktį. Pasirinkta parinktis aukščiau esančioje ekrano kopijoje paskatins mus sukurti suvestinę lentelę, kaip mes sukūrėme anksčiau.
Paspauskite „Enter“ arba spustelėkite „Kitas“, jei norite patikrinti pasirinktą diapazoną.
Dar kartą paspauskite „Enter“.
Pasirinkite naują darbalapį arba ten, kur norite, kad suvestinė lentelė paspaustų „Enter“. Ir padaryta.
Kurkite ataskaitas naudodami „Pivot“ lenteles
Dabar jūs žinote, kaip įterpti sukamąją lentelę. Pradėkime rengti ataskaitas naudodami suvestines lenteles per kelias minutes.
Mes turime stacionaraus užsakymo duomenis.
Stulpelių laukai yra šie:
Užsakymo data: Užsakymo data (aišku)
Regionas: Tvarkos regionas šalyje
Klientas: Kliento vardas (kas dar gali būti)
Prekė: Užsakyta prekė
Vienetas: Užsakytos prekės vienetų skaičius
Vieneto kaina: Vieneto kaina
Iš viso: Bendra užsakymo kaina (vienetas*vieneto kaina).
Norėdami sukurti ataskaitas naudodami suvestines lenteles, mes jas naudosime
Suvestinės lentelės laukai: Yra duomenų stulpelių pavadinimų sąrašas.
Pagrindinės sritys: Šios 4 sritys naudojamos tam, kad jūsų duomenys būtų rodomi manieringai.
FILTRAI: Įdėkite čia laukus, iš kurių norite taikyti filtrus ataskaitoje.
SKILTYS: Įveskite norimus laukus į ataskaitos stulpelius: (geriau parodyti, nei paaiškinti)
EILUTĖS: Vilkite laukus, kuriuos norite parodyti išmintingai, kaip parodyta aukščiau esančiame paveikslėlyje, aš parodžiau regioną ROWS.
VERTYBĖS: Pasirinkite lauką, kad gautumėte skaičių, sumą, vidurkį, procentą (ir daug daugiau) ir pan., Kuriuos norite matyti.
Dabar, naudodamiesi aukščiau pateikta informacija, mes parengėme šią greito suvestinės ataskaitą, kurioje nurodoma, iš kokio regiono užsakymų pateikiama kiekvienai prekei.
Dabar, kai suprantate savo duomenis ir „Pivot“ laukus (galų gale, esate protingas), greitai atsakysime į kai kuriuos su šiais duomenimis susijusius klausimus naudodami suvestinę lentelę.
Q1. Kiek užsakymų yra?
Suvestinė lentelė iš pradžių tuščia, kaip parodyta paveikslėlyje žemiau.
Norėdami pamatyti to lauko santrauką ar išsamią informaciją, atitinkamose srityse turite pasirinkti laukus (stulpelių pavadinimus).
Dabar, norėdamas atsakyti į aukščiau pateiktą klausimą, reikšmės laukuose pasirinksiu elementą (pasirinkite bet kurį stulpelį, tik įsitikinkite, kad jame nėra tuščių langelių).
Laukų sąraše pasirinkite elementą ir vilkite jį į reikšmės lauką.
Mes turime savo atsakymą. „Pivot“ lentelės sako, kad iš viso yra 43 užsakymai. Tai yra teisinga.
Dabar, kai pasirinkau Užsakymo data Vertybių srityje tai rodo 42. Tai reiškia Užsakymo data turi tuščią langelį nuo žinome, kad bendras užsakymų skaičius yra 43.
Naudodami „Pivot“ lenteles nustatykite nereguliarius duomenis ir išvalykite.
„Pivot Table“ gali padėti surasti neteisingą informaciją duomenyse.
Dažniausiai mūsų duomenis rengia duomenų įvedimo operatoriai arba vartotojai, kurie paprastai yra nereguliarūs ir kuriuos reikia šiek tiek išvalyti, kad būtų galima parengti tikslią ataskaitą ir analizę.
Prieš rengdami bet kokias ataskaitas, visada turėtumėte išvalyti ir tvarkingai paruošti savo duomenis. Tačiau kartais tik parengę ataskaitą sužinome, kad mūsų duomenys turi tam tikrų pažeidimų. Ateik, aš tau parodysiu, kaip…
2 klausimas: nurodykite kiekvieno regiono užsakymų skaičių
Dabar atsakykite į šį klausimą:
Pasirinkite Regionas ir vilkite jį į Eilutės Plotas ir Prekė į Vertybės Plotas.
Gausime „Region-Wise“ padalintus duomenis. Galime atsakyti iš kurio regiono, kiek užsakymų.
Pagal suvestinę lentelę mūsų duomenyse iš viso yra 4 regionai. Bet palaukite, atkreipkite dėmesį Centrinė ir Centrle regione. Mes žinome, kad „Centrle“ turėtų būti Centrinis. Yra nelygumas. Turime pereiti prie savo duomenų ir išvalyti duomenis.
Norėdami išvalyti duomenis regiono lauke, mes filtruojame neteisingą regiono pavadinimą (Centrle) ir pataisome (Central).
Dabar grįžkite prie pagrindinių duomenų.
Dešiniuoju pelės mygtuku spustelėkite bet kurią suvestinės lentelės vietą ir spustelėkite Atnaujinti.
Jūsų ataskaita dabar atnaujinta.
Dabar matote, kad iš tikrųjų yra tik 3 regionai.
„Pivot“ ataskaitos formatavimas naudojant suskirstytas eilutes.
Kartais jums reikės ataskaitų, tokių kaip aukščiau pateiktas vaizdas. Tai leidžia lengvai matyti savo duomenis struktūrizuotai. Galite lengvai pasakyti, iš kokio regiono užsakyta prekių. Pažiūrėkime, kaip tai galite padaryti.
Perkelti Regionas ir Prekė į EILUTĖS srityje. Įsitikinkite, kad regionas yra viršuje, o elementai - apačioje, kaip parodyta paveikslėlyje.
Vilkite Prekė dėl Vertė Plotas.
Dėl to gausite šią ataskaitą.
Tiks. Tačiau kartais jūsų viršininkas nori pranešti lentelėmis be tarpinių sumų. Norėdami tai padaryti, turime suformatuoti „Pivot“ lentelę.
Pašalinkite tarpines sumas iš „Pivot“ lentelės
Atlikite šiuos veiksmus:
1. Spustelėkite bet kurią savo suvestinės lentelės vietą.
2. Eikite į Dizainas Skirtukas
3. Spustelėkite tarpinės sumos Meniu.
4. Spustelėkite Nerodyti tarpinių sumų.
Matote, kad dabar nėra tarpinių sumų.
Puiku. Tačiau jis vis dar nėra lentelės pavidalu. Regionai ir elementai rodomi viename stulpelyje. Parodykite juos atskirai.
Sukurkite suvestinę lentelę
Dabar norėdami rodyti regionus ir elementus skirtinguose stulpeliuose, atlikite šiuos veiksmus:
1. Spustelėkite bet kurią „Pivot Table“ vietą
2. Eikite į skirtuką Dizainas
3. Spustelėkite Ataskaitos išdėstymas.
4. Spustelėkite Rodyti parinktį Lentelės forma. Galiausiai turėsite tokį sudėtingą savo ataskaitos vaizdą.
Dabar mes žinome bendrą užsakymų skaičių kiekvienai prekei iš kiekvieno regiono. Tai parodyta grafike elementų stulpelių.
Pakeiskite stulpelio pavadinimą į Užsakymai.
Dabar atrodo geriau.
3 klausimas: kiek vienetų kiekvienos prekės užsakoma?
Norėdami atsakyti į šį klausimą, mums reikia vienetų sumos. Norėdami tai padaryti, tiesiog perkelkite lauką Vienetai į Reikšmės. Jis automatiškai susumuoja kiekvieno elemento vienetų skaičių. Jei „Pivot“ lentelės stulpelyje yra tik reikšmės, pagal numatytuosius nustatymus „Pivot“ lentelėje rodoma šių verčių suma. Tačiau jį galima pakeisti iš vertės lauko nustatymo. Kaip? Aš jums parodysiu pastarąjį.
„Pivot Table“ vertės lauko nustatymai
4 klausimas: vidutinė kiekvienos prekės kaina?
Mūsų pavyzdiniuose duomenyse vienos prekės kaina skirtingiems užsakymams skiriasi. Pavyzdžiui, žr.
Noriu sužinoti vidutines kiekvieno elemento išlaidas „Pivot Table“. Norėdami tai sužinoti, vilkite vieneto kainą į vertės lauką. Tai parodys vieneto išlaidų sumą.
Mes nenorime Vieneto išlaidų suma, Mes norime Vieneto kainos vidurkis. Norėdami tai padaryti…
1. Dešiniuoju pelės mygtuku spustelėkite bet kurią „Pivot Table“ stulpelio „Vieneto kaina“ sumą
2. Spustelėkite Vertės lauko nustatymai
3. Remiantis galimomis parinktimis, pasirinkite Vidutinis ir paspauskite Gerai.
Galiausiai turėsite šią „Pivot“ ataskaitą:
Suvestinės lentelės apskaičiuoti laukai
Viena iš naudingiausių „Pivot“ lentelės funkcijų yra jos apskaičiuoti laukai. Apskaičiuoti laukai yra laukai, kurie gaunami atliekant tam tikras operacijas galimuose stulpeliuose.
Supraskime, kaip įterpti apskaičiuotus laukus į „Pivot“ lentelę, pateikdami vieną pavyzdį:
Remdamiesi savo duomenimis, mes parengėme šią ataskaitą.
Štai, mes turime Vienetų suma ir Iš viso išlaidų. Aš ką tik perkėliau stulpelį į lauką Vertės ir pervadinau jį į Iš viso išlaidų. Dabar noriu sužinoti vidutinę kiekvieno elemento vieneto kainą kiekviename regione. Ir tai būtų:
Vidutinė kaina = bendra kaina / bendri vienetai
Įterpiame lauką į suvestinę lentelę, kurioje rodoma vidutinė kiekvieno elemento regiono kaina:
Norėdami įterpti apskaičiuotą lauką į „Pivot“ lentelę, atlikite šiuos veiksmus
1. Spustelėkite bet kurią „Pivot“ lentelės vietą ir eikite į skirtuką „Analizuoti“
2. Skaičiavimo grupėje spustelėkite Laukai, elementai ir rinkiniai.
3. Spustelėkite Apskaičiuoti laukai.
Pamatysite šį skaičiavimo lauko įvesties laukelį:
4. Vardo įvedimo laukelyje įrašykite vidutines išlaidas ar ką nors, kas jums patinka, „Excel“ neprieštaraus. Formulės įvesties laukelyje parašykite ir paspauskite Gerai.
= Iš viso / vienetai
Tai galite parašyti rankiniu būdu iš klaviatūros arba dukart spustelėję laukų pavadinimus, nurodytus srityje Laukai, kad atliktumėte operacijas.
5. Dabar apskaičiuotas laukas pridėtas prie suvestinės lentelės. Jis įvardijamas kaip vidutinių išlaidų suma, tačiau tai nėra suma. „Excel“ tiesiog vykdo numatytąją funkciją stulpeliui pavadinti (kaip ritualas). Pervardykite šį stulpelį ir apribokite rodomus dešimtainius skaitmenis.
Ir ten jūs turite apskaičiuotą lauką. Galite padaryti tai taip sudėtingai, kaip norite. Pavyzdžiui, ėmiausi šios paprastos vidutinės operacijos.
Grupavimas „Pivot“ lentelėje
Jūs parengėte šią pagrindinę ataskaitą.
Dabar noriu, kad ši ataskaita būtų dalijama kasmet. Žiūrėkite momentinį vaizdą žemiau.
Turime stulpelį apie užsakymo datą. Perkelkite lauką „OrderDate“ į eilutes viršuje.
Tai neatrodo kaip reikalaujama ataskaita. Mes turime turėti grupines datas kiekvienais metais.
Dabar, norėdami sugrupuoti lauką „Excel“ suvestinėje lentelėje, atlikite šiuos veiksmus:
1. Dešiniuoju pelės mygtuku spustelėkite lauką, kurį norite grupuoti.
2. Spustelėkite Grupė. Turėsite šį parinkčių langelį pritaikymui. Kadangi tai yra duomenų stulpelis, „Excel“ mums atitinkamai parodo grupavimą. Galite pasirinkti pradžios ir pabaigos datą.
3. Pasirinkite metus ir paspauskite Gerai. Jūs atlikote kasmetinį grupavimą „Excel“ suvestinėje lentelėje.
„Pivot Table“ pjaustytuvai
Pjaustyklės buvo įtrauktos į „Excel 2010“ kaip priedas. „Excel 2013“ ir „2016“ ji pagal numatytuosius nustatymus pasiekiama kaip ir filtrai.
Pjaustyklės yra ne kas kita, kaip filtrai. Skirtingai nuo filtrų, „Slicers“ rodo visas galimas parinktis priešais jus. Tai daro jūsų prietaisų skydelį interaktyvesnį.
Kaip pridėti pjaustytuvus „Pivot Table“, „Excel 2016“ ir „2013“
„Pivot Table Slicers“ lengva pridėti. Atlikite šiuos veiksmus:
1. Spustelėkite bet kurią suvestinės lentelės vietą ir eikite į skirtuką Analizuoti.
2. Spustelėkite Įterpti pjaustytuvą. Turėsite savo duomenų laukų sąrašą. Pasirinkite tiek, kiek norite.
3. Šiame pavyzdyje pasirinkite Regionas ir paspauskite Gerai.
Prie ataskaitos pridėjote „Slicer“. Dabar pritaikykite filtrą tik vienu paspaudimu.
Įterpkite laiko juostą „Excel 2016“ ir „2013“
Tai yra viena iš mano mėgstamiausių „Excel Pivot“ lentelių funkcijų. Ši nauja funkcija veikia tik su datomis. Naudodami tai galite vizualiai pasirinkti duomenų filtravimo laikotarpį.
Norėdami įterpti laiko juostos žymeklį, atlikite šiuos veiksmus:
Kaip pridėti laiko juostą „Pivot“ lentelėje, „Excel 2016“ ir „2013“
1. Spustelėkite bet kurią „Pivot Table“ vietą ir eikite į skirtuką „Analizuoti“.
2. Spustelėkite Įterpti laiko juostą iš filtrų grupės. Visi stulpeliai, kuriuose yra laiko reikšmių šaltiniuose, bus išvardyti pasirinkimo laukelyje, iš kurio galima pasirinkti. Čia mes turime tik vieną. Taigi…
2. Pasirinkite parinktis ir paspauskite „Enter“ arba spustelėkite Gerai. Viskas padaryta ir priešais jus yra suvestinės lentelės laiko juosta.
Galite pasirinkti rodyti kasdien, kas mėnesį, kas ketvirtį ar kasmet. Aš čia pasirinkau „Monthly“.
Šiame straipsnyje apžvelgiau svarbiausias ir naudingiausias „Pivot Table“ funkcijas. Išnagrinėjome naujas „Pivot Table“ funkcijas „Excel 2016“ ir „2013.“ Jie vis dar naudingi. Jei čia neradote atsakymo, susijusio su jūsų suvestine lentele, paklauskite komentarų skiltyje.
Yra daug kitų funkcijų, kurios dar turi būti paaiškintos. Kitame straipsnyje sužinosime apie išplėstinę „Pivot Table“ funkciją. Iki tol „Excel“ viską.
