„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ą vietos laukelyje.
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 Altbutton 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 ir bet jie neatmeta senų funkcijų (kaip MS padarė su pergale 8. Tai buvo apgailėtina). Tai leidžia vyresniam vartotojui normaliai dirbti su naujomis versijomis, kaip ir anksčiau.
Jei nuosekliai paspausite ALT, D ir P. klaviatūroje atsidarys „Excel“, kad sukurtumėte 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 (Akivaizdu)
Regionas: Tvarkos regionas šalyje
Klientas: Kliento vardas (kas dar gali but)
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).
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.
STulpeliai: Įdėkite laukus, kuriuos norite į 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ą, aš pasirinksiu Prekė (pasirinkite bet kurį stulpelį, tik įsitikinkite, kad tarp jo nėra tuščių langelių) vertės laukuose.
Pasirinkite Prekė iš laukų sąrašo ir vilkite jį į lauką Vertė.
Mes turime savo atsakymą. „Pivot“ lentelės sako, kad iš viso yra 43 užsakymai. Tai yra teisinga.
Pro patarimas: Turėtumėte patikrinti savo duomenis, ar jie teisingi, ar ne. Jei šis skaičius nesutampa su duomenimis, tai reiškia, kad pasirinkote neteisingą diapazoną arba tame lauke yra tuščių langelių.
Informacija: Vertės lauke pagal numatytuosius nustatymus skaičiuojamas stulpelio įrašų skaičius, jei jame yra teksto, ir sumos, jei lauke yra tik reikšmės. Tai galite pakeisti vertės lauko nustatymuose. Kaip? Susitiksime vėliau šioje „Pivot Table“ pamokoje.
Dabar, kai pasirinkau Užsakymo data Valuesarea, 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į Centrinis 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.
INFORMACIJA: Nesvarbu, kokius pakeitimus atliksite šaltinio duomenyse, suvestinė lentelė ir toliau dirbs su senais duomenimis kol neatnaujinsite. „Excel“ sukuria „pivot“ talpyklą ir suvestinė lentelė veikia šioje talpykloje. Atnaujinus, senoji talpykla pakeičiama naujais duomenimis.
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 meniu Tarpinės sumos.
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 dėl lentelės formos variantas. 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 kainos suma, Mes norime Vieneto kainos vidurkis. Norėdami tai padaryti…
- Dešiniuoju pelės mygtuku spustelėkite bet kurią „Pivot Table“ stulpelio „Vieneto kaina“ sumą
- Spustelėkite Vertės lauko nustatymai
- Remiantis galimomis parinktimis, pasirinkti 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 Table“ vietą ir eikite į Analizuoti skirtuką
2. Spustelėkite Laukai, elementai ir rinkiniai skaičiavimo grupėje.
3. Spustelėkite Apskaičiuoti laukai.
Pamatysite šį skaičiavimo lauko įvesties laukelį:
4. Vardo įvedimo laukelyje įrašykite vidurkį Kaina ar kas jums patinka, „Excel“ neprieštaraus. Viduje konors formulė įvesties laukelį, 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.