Kaip pridėti ir naudoti „Excel“ suvestinės lentelės apskaičiuotą lauką

Dažnai sukūrus „Pivot“ lentelę, reikia išplėsti analizę ir į ją įtraukti daugiau duomenų/skaičiavimų.

Jei jums reikia naujo duomenų taško, kurį galima gauti naudojant esamus duomenų taškus „Pivot“ lentelėje, jums nereikia grįžti ir pridėti jį prie šaltinio duomenų. Vietoj to galite naudoti a Suvestinės lentelės apskaičiuotas laukas Padaryti tai.

Atsisiųskite duomenų rinkinį ir sekite.

Kas yra „Pivot Table“ apskaičiuotas laukas?

Pradėkime nuo pagrindinio „Pivot Table“ pavyzdžio.

Tarkime, kad turite mažmenininkų duomenų rinkinį ir sukuriate „Pivot“ lentelę, kaip parodyta žemiau:

Aukščiau pateikta suvestinė lentelė apibendrina mažmenininkų pardavimo ir pelno vertes.

Ką daryti, jei taip pat norite sužinoti, kokia buvo šių mažmenininkų pelno marža (kai pelno marža yra „Pelnas“, padalytas iš „Pardavimų“).

Yra keli būdai tai padaryti:

1. Grįžkite prie pradinio duomenų rinkinio ir pridėkite šį naują duomenų tašką. Taigi į šaltinio duomenis galite įterpti naują stulpelį ir jame apskaičiuoti pelno maržą. Kai tai padarysite, turite atnaujinti „Pivot“ lentelės šaltinio duomenis, kad šį naują stulpelį gautumėte kaip jo dalį.
   • Nors šis metodas yra galimybė, jums reikės rankiniu būdu grįžti prie duomenų rinkinio ir atlikti skaičiavimus. Pvz., Gali reikėti pridėti kitą stulpelį, kad apskaičiuotumėte vidutinį vieneto pardavimą (pardavimai/kiekis). Vėl turėsite pridėti šį stulpelį prie šaltinio duomenų ir atnaujinti suvestinę lentelę.
   • Šis metodas taip pat padidina „Pivot“ lentelę, kai prie jos pridedate naujų duomenų.
2. Pridėkite skaičiavimus ne suvestinėje lentelėje. Tai gali būti pasirinkimas, jei mažai tikėtina, kad jūsų „Pivot Table“ struktūra pasikeis. Bet jei pakeisite „Pivot“ lentelę, skaičiavimas gali būti neatnaujintas ir gali būti pateikti neteisingi rezultatai arba klaidos. Kaip parodyta žemiau, aš apskaičiavau pelno maržą, kai eilėje buvo mažmenininkų. Bet kai pakeičiau jį iš klientų į regionus, formulė davė klaidą.
3. Pasukamos lentelės apskaičiuoto lauko naudojimas. Tai yra efektyviausias būdas naudoti esamus „Pivot Table“ duomenis ir apskaičiuoti norimą metriką. Apsvarstykite Apskaičiuotą lauką kaip virtualų stulpelį, kurį pridėjote naudodami esamus stulpelius iš „Pivot“ lentelės. „Pivot Table“ apskaičiuoto lauko naudojimas turi daug privalumų (kaip pamatysime po minutės):
   • Tam nereikia tvarkyti formulių ar atnaujinti šaltinio duomenų.
   • Jis yra keičiamo dydžio, nes automatiškai atsižvelgs į visus naujus duomenis, kuriuos galite pridėti prie „Pivot“ lentelės. Pridėję skaičiavimo lauką, galite jį naudoti kaip bet kurį kitą „Pivot“ lentelės lauką.
   • Tai lengva atnaujinti ir valdyti. Pvz., Jei metrika pasikeičia arba jums reikia pakeisti skaičiavimą, tai galite lengvai padaryti naudodami pačią „Pivot“ lentelę.

Apskaičiuoto lauko įtraukimas į „Pivot“ lentelę

Pažiūrėkime, kaip pridėti „Pivot Table“ apskaičiuotą lauką prie esamos „Pivot Table“.

Tarkime, kad turite „Pivot“ lentelę, kaip parodyta žemiau, ir norite apskaičiuoti kiekvieno mažmenininko pelno maržą:

Toliau pateikiami žingsniai, kaip pridėti „Pivot Table“ apskaičiuotą lauką:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į „Pivot Table Tools“ -> Analizė -> Skaičiavimai -> Laukai, elementai ir rinkiniai.
• Išskleidžiamajame meniu pasirinkite Apskaičiuotas laukas.
• Dialogo lange Įterpti apskaičiuotą bylą:
  • Suteikite jam pavadinimą, įvesdami jį lauke Pavadinimas.
  • Lauke Formula sukurkite norimą apskaičiuoto lauko formulę. Atminkite, kad galite pasirinkti iš žemiau išvardytų laukų pavadinimų. Šiuo atveju formulė yra „= pelnas/ pardavimai“. Galite rankiniu būdu įvesti laukų pavadinimus arba dukart spustelėti lauko pavadinimą, nurodytą lauke Laukai.
• Spustelėkite Pridėti ir uždarykite dialogo langą.

Kai tik pridėsite apskaičiuotą lauką, jis bus rodomas kaip vienas iš laukų „PivotTable“ laukų sąraše.

Dabar šį apskaičiuotą lauką galite naudoti kaip bet kurį kitą „Pivot Table“ lauką (atminkite, kad „Pivot Table“ apskaičiuoto lauko negalite naudoti kaip ataskaitos filtro ar pjaustyklės).

Kaip minėjau anksčiau, „Pivot Table Calculated Field“ naudojimo pranašumas yra tas, kad galite pakeisti „Pivot Table“ struktūrą ir ji bus automatiškai sureguliuota.

Pvz., Jei vilksiu ir nuvesiu sritį eilučių srityje, gausite rezultatą, kaip parodyta žemiau, kur mažmenininkams ir regionui pateikiama pelno maržos vertė.

Anksčiau pateiktame pavyzdyje apskaičiuotam laukui įterpti naudojau paprastą formulę (= Pelnas/Pardavimai). Tačiau taip pat galite naudoti kai kurias išplėstines formules.

Prieš parodydamas jums pavyzdį, kaip naudoti išplėstinę formulę „Pivot Table“ skaičiavimo laukui sukurti, čia yra keletas dalykų, kuriuos turite žinoti:

• Kurdami „Pivot Table“ apskaičiuotą lauką, NEGALIMA naudoti nuorodų ar pavadintų diapazonų. Tai pašalintų daugybę formulių, tokių kaip VLOOKUP, INDEX, OFFSET ir pan. Tačiau galite naudoti formules, kurios gali veikti be nuorodų (pvz., SUM, IF, COUNT ir pan.).
• Formulėje galite naudoti konstantą. Pavyzdžiui, jei norite sužinoti prognozuojamus pardavimus, kuriuose prognozuojama, kad jie augs 10%, galite naudoti formulę = Pardavimai*1,1 (kur 1,1 yra pastovus).
• Pirmenybės tvarka laikomasi formulėje, kuri sudaro apskaičiuotą lauką. Kaip geriausia praktika, naudokite skliaustus, kad įsitikintumėte, jog nereikia prisiminti eiliškumo.

Dabar pažiūrėkime pavyzdį, kaip naudojant išplėstinę formulę apskaičiuojamam laukui sukurti.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir „Pivot“ lentelėje turite parodyti numatomą pardavimo vertę.

Norėdami apskaičiuoti vertę, turite naudoti 5% pardavimų padidėjimą dideliems mažmenininkams (pardavimas didesnis nei 3 milijonai) ir 10% padidėjimą mažiems ir vidutiniams mažmenininkams (pardavimas mažesnis nei 3 milijonai).

Pastaba: čia pateikti pardavimų skaičiai yra suklastoti ir buvo naudojami iliustruoti šios pamokos pavyzdžius.

Štai kaip tai padaryti:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į „Pivot Table Tools“ -> Analizė -> Skaičiavimai -> Laukai, elementai ir rinkiniai.
• Išskleidžiamajame meniu pasirinkite Apskaičiuotas laukas.
• Dialogo lange Įterpti apskaičiuotą bylą:
  • Suteikite jam pavadinimą įvesdami jį lauke Pavadinimas.
  • Lauke formulė naudokite šią formulę: = IF (regionas = „pietai“, pardavimai *1,05, pardavimai *1,1)
• Spustelėkite Pridėti ir uždarykite dialogo langą.

Tai suvestinėje lentelėje prideda naują stulpelį su pardavimo prognozės verte.

Spustelėkite čia norėdami atsisiųsti duomenų rinkinį.

„Pivot Table“ apskaičiuotų laukų problema

Apskaičiuotas laukas yra nuostabi funkcija, kuri tikrai padidina jūsų „Pivot“ lentelės vertę, apskaičiuodama laukus, tuo pačiu išlaikydama, kad viskas būtų keičiama ir valdoma.

Tačiau su „Pivot Table“ apskaičiuotais laukais yra problema, kurią turite žinoti prieš naudodami.

Tarkime, turiu „Pivot“ lentelę, kaip parodyta žemiau, kur naudoju apskaičiuotą lauką, kad gaučiau prognozuojamus pardavimo skaičius.

Atminkite, kad tarpinė ir bendra sumos nėra teisingos.

Nors tai turėtų pridėti kiekvieno mažmenininko individualios pardavimo prognozės vertę, iš tikrųjų ji atitinka tą pačią apskaičiuotą lauko formulę, kurią sukūrėme.

Taigi „South Total“, nors vertė turėtų būti 22 824 000, „South Total“ klaidingai praneša apie 22 287 000. Taip atsitinka, kai vertei gauti naudojama formulė 21 225 800*1,05.

Deja, niekaip negalite to ištaisyti.

Geriausias būdas tai išspręsti būtų pašalinti tarpines ir bendrąsias sumas iš „Pivot“ lentelės.

Taip pat galite pereiti prie kai kurių novatoriškų „Debra“ sprendimų, kaip išspręsti šią problemą.

Kaip pakeisti arba ištrinti „Pivot Table“ apskaičiuotą lauką?

Sukūrę „Pivot Table“ apskaičiuotą lauką, galite pakeisti formulę arba ją ištrinti atlikdami šiuos veiksmus:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į „Pivot Table Tools“ -> Analizė -> Skaičiavimai -> Laukai, elementai ir rinkiniai.
• Išskleidžiamajame meniu pasirinkite Apskaičiuotas laukas.
• Lauke Pavadinimas spustelėkite išskleidžiamąją rodyklę (maža rodyklė žemyn lauko pabaigoje).
• Iš sąrašo pasirinkite apskaičiuotą lauką, kurį norite ištrinti arba keisti.
• Pakeiskite formulę, jei norite ją pakeisti, arba spustelėkite Ištrinti, jei norite ją ištrinti.

Kaip gauti visų apskaičiuotų laukų formulių sąrašą?

Jei sukuriate daug „Pivot Table Apskaičiuotas“ lauko, nesijaudinkite, kaip sekti kiekvienoje iš jų naudojamą formulę.

„Excel“ leidžia greitai sukurti visų formulių, naudojamų kuriant apskaičiuotus laukus, sąrašą.

Štai kaip greitai gauti visų apskaičiuotų laukų formulių sąrašą:

• Pasirinkite bet kurią langelį „Pivot Table“.
• Eikite į „Pivot Table Tools“ -> Analizė -> Laukai, elementai ir rinkiniai -> Sąrašo formulės.

Kai tik spustelėsite sąrašo formules, „Excel“ automatiškai įterps naują darbalapį, kuriame bus išsami informacija apie visus apskaičiuotus laukus/elementus, kuriuos naudojote „Pivot“ lentelėje.

Tai gali būti tikrai naudinga priemonė, jei turite nusiųsti savo darbą klientui arba pasidalyti juo su savo komanda.

Taip pat gali būti naudingos šios „Pivot Table“ pamokos:

• Šaltinio duomenų ruošimas „Pivot Table“.
• Pjaustyklių naudojimas „Excel“ suvestinėje lentelėje: pradedančiųjų vadovas.
• Kaip grupuoti datas „Excel“ suvestinėse lentelėse.
• Kaip grupuoti skaičius „Pivot“ lentelėje „Excel“.
• Kaip filtruoti duomenis „Pivot“ lentelėje „Excel“.
• Kaip pakeisti tuščias langelius nuliais „Excel“ suvestinėse lentelėse.
• Kaip taikyti sąlyginį formatavimą „Excel“ suvestinėje lentelėje.
• „Pivot“ talpykla „Excel“ - kas tai ir kaip geriausiai ją naudoti?