excel logotipas

„Pivot Tables“ yra nuostabus įtaisytasis „Excel“ ataskaitų teikimo įrankis. Nors paprastai jie naudojami duomenims apibendrinti su bendromis sumomis, taip pat galite juos naudoti norėdami apskaičiuoti reikšmių pokyčio procentą. Dar geriau: tai padaryti paprasta.



Šią techniką galite naudoti norėdami atlikti įvairius dalykus – beveik visur, kur norėtumėte pamatyti, kaip viena vertybė yra palyginama su kita. Šiame straipsnyje mes naudosime paprastą pavyzdį, kaip apskaičiuoti ir rodyti procentą, kuriuo bendra pardavimo vertė keičiasi kiekvieną mėnesį.

Štai lapas, kurį naudosime.

Dvejų metų „PivotTable“ pardavimo duomenys

Tai gana tipiškas pardavimo lapo pavyzdys, kuriame rodoma užsakymo data, kliento vardas, pardavimo atstovas, bendra pardavimo vertė ir keletas kitų dalykų.

Norėdami visa tai padaryti, pirmiausia suformatuosime savo reikšmių diapazoną kaip lentelę programoje „Excel“, o tada sukursime „Pivot“ lentelę, kad galėtume atlikti ir rodyti procentinio pokyčio skaičiavimus.

Diapazono formatavimas kaip lentelė

Jei jūsų duomenų diapazonas dar nesuformatuotas kaip lentelė, rekomenduojame tai padaryti. Duomenys, saugomi lentelėse, turi daug pranašumų, palyginti su duomenimis, esančiais darbalapio langelių diapazonuose, ypač naudojant „PivotTable“ ( skaitykite daugiau apie lentelių naudojimo naudą ).

Skelbimas

Norėdami formatuoti diapazoną kaip lentelę, pasirinkite langelių diapazoną ir spustelėkite Įterpti > Lentelė.

Dialogo langas Sukurti lentelę, kad nurodytumėte langelių diapazoną

Patikrinkite, ar diapazonas yra teisingas, ar pirmoje to diapazono eilutėje yra antraštės, tada spustelėkite Gerai.

Diapazonas dabar suformatuotas kaip lentelė. Lentelės pavadinimo suteikimas padės lengviau remtis ateityje kuriant „PivotTable“, diagramas ir formules.

Spustelėkite skirtuką Dizainas skiltyje Lentelės įrankiai ir įveskite pavadinimą laukelyje, esančiame juostelės pradžioje. Ši lentelė pavadinta Pardavimas.

Pavadinkite lentelę „Excel“.

Čia taip pat galite pakeisti stalo stilių, jei norite.

Sukurkite „PivotTable“, kad būtų rodomas procentinis pokytis

Dabar pradėkime kurti „PivotTable“. Naujoje lentelėje spustelėkite Įterpti > PivotTable.

Skelbimas

Pasirodo langas Sukurti PivotTable. Jis automatiškai aptiks jūsų lentelę. Tačiau šiuo metu galite pasirinkti lentelę arba diapazoną, kurį norite naudoti „PivotTable“.

„PivotTable“ kūrimo langas

Sugrupuokite datas į mėnesius

Tada nutempsime datos lauką, pagal kurį norime sugrupuoti, į PivotTable eilučių sritį. Šiame pavyzdyje laukas pavadintas Užsakymo data.

Nuo „Excel 2016“ datos reikšmės automatiškai grupuojamos į metus, ketvirčius ir mėnesius.

Jei jūsų „Excel“ versija to nedaro arba tiesiog norite pakeisti grupavimą, dešiniuoju pelės mygtuku spustelėkite langelį, kuriame yra datos reikšmė, ir pasirinkite komandą Grupė.

Sugrupuokite datas „PivotTable“.

Pasirinkite grupes, kurias norite naudoti. Šiame pavyzdyje pasirinkti tik metai ir mėnesiai.

Metų ir mėnesių nurodymas grupės dialogo lange

Metai ir mėnuo dabar yra laukai, kuriuos galime naudoti analizei. Mėnesiai vis dar vadinami užsakymo data.

Laukai Metai ir Užsakymo data eilutėse

Pridėkite verčių laukus prie „PivotTable“.

Perkelkite lauką Metai iš eilučių į filtro sritį. Tai leidžia vartotojui filtruoti „PivotTable“ vienerius metus, o ne užgriozdinti „PivotTable“ per daug informacijos.

Skelbimas

Nuvilkite lauką, kuriame yra reikšmės (šiame pavyzdyje bendra pardavimo vertė), kurias norite apskaičiuoti, ir pateikti pakeitimą į reikšmių sritį du kartus .

Tai dar gali neatrodyti. Bet tai labai greitai pasikeis.

Pardavimo vertės laukas du kartus pridėtas prie „PivotTable“.

Abiejuose reikšmės laukuose pagal numatytuosius nustatymus bus nustatyta suma ir šiuo metu nėra formatavimo.

Pirmame stulpelyje esančias reikšmes norėtume išlaikyti kaip sumas. Tačiau juos reikia formatuoti.

Dešiniuoju pelės mygtuku spustelėkite skaičių pirmame stulpelyje ir nuorodų meniu pasirinkite Skaičių formatavimas.

Skelbimas

Dialogo lange Format Cells pasirinkite apskaitos formatą su 0 kablelio.

„PivotTable“ dabar atrodo taip:

Pirmojo stulpelio formatavimas

Sukurkite procentinio pokyčio stulpelį

Dešiniuoju pelės mygtuku spustelėkite reikšmę antrame stulpelyje, nukreipkite žymiklį į Rodyti reikšmes, tada spustelėkite parinktį Skirtumas nuo %.

Rodyti reikšmes kaip procentinį skirtumą

Pasirinkite (Ankstesnis) kaip pagrindinį elementą. Tai reiškia, kad dabartinė mėnesio reikšmė visada lyginama su ankstesnių mėnesių (užsakymo datos laukas) reikšme.

Pasirinkite Ankstesnis kaip pagrindinį elementą, su kuriuo norite palyginti

„PivotTable“ dabar rodo ir vertes, ir procentinį pokytį.

Rodyti vertes ir procentinį pokytį

Spustelėkite langelį, kuriame yra eilučių etiketės, ir kaip to stulpelio antraštę įveskite Mėnuo. Tada spustelėkite antrojo verčių stulpelio antraštės langelį ir įveskite Variance.

Pervardykite „PivotTable“ antraštes

Pridėkite keletą dispersijos rodyklių

Norėdami iš tikrųjų nušlifuoti šią „PivotTable“, norėtume geriau įsivaizduoti procentinį pokytį pridėdami keletą žalių ir raudonų rodyklių.

Skelbimas

Tai suteiks mums puikų būdą pamatyti, ar pokytis buvo teigiamas, ar neigiamas.

Spustelėkite bet kurią iš reikšmių antrame stulpelyje, tada spustelėkite Pagrindinis > Sąlyginis formatavimas > Nauja taisyklė. Atsidariusiame lange Redaguoti formatavimo taisyklę atlikite šiuos veiksmus:

  1. Pasirinkite parinktį Visi langeliai, kuriuose rodomos užsakymo datos dispersijos reikšmės.
  2. Sąraše Format Style pasirinkite Piktogramų rinkiniai.
  3. Pasirinkite raudoną, gintarinį ir žalią trikampius iš piktogramų stiliaus sąrašo.
  4. Stulpelyje Tipas pakeiskite sąrašo parinktį ir nurodykite Skaičius, o ne procentas. Tai pakeis stulpelį Reikšmė į 0. Būtent tai, ko norime.

Spustelėkite Gerai ir „PivotTable“ bus pritaikytas sąlyginis formatavimas.

Užbaigta dispersijos „PivotTable“.

„PivotTables“ yra neįtikėtinas įrankis ir vienas iš paprasčiausių būdų parodyti verčių procentinį pokytį laikui bėgant.

SKAITYTI KITAS