Turite kelis tūkstančius pardavimų eilučių ir reikia atsakyti į paprastą klausimą: kiek pardavė kiekvienas vadybininkas kiekvieną mėnesį? Galima rašyti dešimtis SUMIFS formulių, bet suvestinė lentelė tą patį padaro keliais pelės tempimais.
Meniu pavadinimus rašau angliškai. Lietuviškame Excel jie išversti, bet yra tose pačiose vietose.
Kokių duomenų reikia
Suvestinė lentelė veikia tik su tvarkingais duomenimis, ir dažniausiai problemos kyla būtent čia:
- Viena antraščių eilutė, kiekvienas stulpelis turi pavadinimą.
- Viena eilutė yra vienas įrašas: vienas pardavimas, vienas užsakymas, viena sąskaita.
- Be tuščių eilučių ir sujungtų langelių duomenų viduje.
- Be tarpinių sumų tarp eilučių. Jas suskaičiuos pati suvestinė.
- Mėnuo yra duomenys, o ne stulpelis. Lentelė su stulpeliais „Sausis“, „Vasaris“, „Kovas“ suvestinei netinka. Reikia vieno stulpelio „Data“ ir vieno stulpelio „Suma“.
Prieš kurdami suvestinę paverskite duomenis Excel lentele (Ctrl+T). Tada į suvestinę pateks ir vėliau pridėtos eilutės.
Kaip sukurti
- Pažymėkite bet kurį langelį duomenyse.
- Pasirinkite Insert → PivotTable ir patvirtinkite, kad suvestinė būtų kuriama naujame lape.
- Dešinėje atsiras laukų sąrašas. Nutempkite „Vadybininkas“ į Rows, „Data“ į Columns, „Suma“ į Values.
Gausite lentelę, kurioje kiekvienas vadybininkas yra eilutė, kiekvienas laikotarpis stulpelis, o susikirtime suma. Norėdami pažiūrėti iš kito kampo, tiesiog sukeiskite laukus vietomis arba nutempkite „Miestas“ į Filters.
Grupavimas pagal mėnesius
Naujesnės Excel versijos datas dažnai sugrupuoja automatiškai. Jei ne, dešiniu pelės mygtuku spustelėkite bet kurią datą suvestinėje ir pasirinkite Group. Pažymėkite Months ir Years kartu: jei pasirinksite tik mėnesius, 2025 ir 2026 metų rugsėjis bus sudėti į vieną stulpelį.
Jei Group neaktyvus arba Excel rašo, kad negali sugrupuoti, bent viena data stulpelyje yra tekstas arba tuščias langelis. Kaip tai pataisyti, aprašiau straipsnyje kodėl Excel nesumuoja skaičių ir neatpažįsta datų.
Dažniausios problemos
Rodoma Count, o ne Sum. Jei sumų stulpelyje yra bent vienas tekstas ar tuščias langelis, Excel pagal nutylėjimą skaičiuoja kiekį. Spustelėkite lauką Values srityje, pasirinkite Value Field Settings ir Sum. Geriau surasti ir pataisyti tuos langelius, nes kitaip jų reikšmės į sumą nepateks.
Atsiranda eilutė „(blank)“. Duomenyse yra tuščių langelių tame stulpelyje. Arba užpildykite juos, arba išfiltruokite „(blank)“ suvestinėje.
Tas pats klientas rodomas du kartus. Pavadinimai skiriasi tarpu ar rašyba. Suvestinė sugrupuoja tik visiškai vienodas reikšmes. Apie tai plačiau straipsnyje kaip rasti ir pašalinti dublikatus.
Pasikeitė duomenys, bet suvestinė rodo senus skaičius. Suvestinė neatsinaujina automatiškai. Spauskite Data → Refresh All arba dešiniu pelės mygtuku ant suvestinės ir Refresh.
Kelios naudingos galimybės
- Procentai nuo viso. Value Field Settings → Show Values As → % of Grand Total parodys, kokią dalį sudaro kiekvienas vadybininkas ar prekė. Kaip procentus skaičiuoti formulėmis, rašiau straipsnyje procentai Excel.
- Slicers. Insert → Slicer prideda mygtukus filtravimui pagal miestą ar kategoriją. Jie patogūs, kai suvestinę naudoja žmonės, nedirbantys su Excel kasdien.
- Detalės. Dukart spustelėjus bet kurį skaičių suvestinėje, naujame lape atsidaro visos eilutės, iš kurių jis sudarytas. Taip greitai patikrinsite, iš kur atsirado netikėta suma.
- GETPIVOTDATA. Jei formulėje spustelite suvestinės langelį, Excel vietoje paprastos nuorodos įrašo GETPIVOTDATA funkciją. Ji grąžina reikšmę pagal pavadinimus, todėl nesugenda pasikeitus suvestinės išdėstymui.
Google Sheets suvestinės kuriamos per Insert → Pivot table ir veikia panašiai, tik atsinaujina automatiškai.
Skaičiavimo laukas
Jei suvestinėje reikia rodiklio, kurio duomenyse nėra, pavyzdžiui, maržos, jį galima apskaičiuoti pačioje suvestinėje: PivotTable Analyze → Fields, Items & Sets → Calculated Field. Įrašykite pavadinimą ir formulę, pavyzdžiui =(Suma-Savikaina)/Suma.
Skaičiavimo laukas visada skaičiuoja iš sumų, o ne iš kiekvienos eilutės atskirai. Marža iš sumų yra teisinga, bet vidutinė kaina ar procentas nuo eilutės gali skirtis nuo to, ko tikitės. Tokiu atveju paprasčiau pridėti stulpelį pačiuose duomenyse.
Unikalių reikšmių skaičius
Count suvestinėje skaičiuoja eilutes. Kad sužinotumėte, kiek buvo skirtingų klientų, kurdami suvestinę pažymėkite Add this data to the Data Model. Tada Value Field Settings atsiras Distinct Count.
Kada suvestinės nebeužtenka
Suvestinė lentelė puikiai tinka, kai duomenys yra vienoje lentelėje. Jei prieš kiekvieną atnaujinimą tenka kopijuoti duomenis iš kelių failų ar eksportų, problema yra ne suvestinėje, o paruošime. Tą žingsnį gali perimti Power Query: jis sujungia failus, sutvarko formatus, o suvestinė tada atnaujinama vienu paspaudimu. Daugiau apie tai straipsnyje Power Query: ataskaita, kuri atsinaujina vienu paspaudimu.