"PivotTables" galite naudoti suvestinės funkcijas reikšmių laukuose, kad sujungtumėte reikšmes iš esamų šaltinio duomenų. Jei suvestinės funkcijos ir pasirinktiniai skaičiavimai nepateikia norimų rezultatų, galite sukurti savo formules apskaičiuotuosiuose laukuose ir apskaičiuotuosiuose elementuose. Pavyzdžiui, galite įtraukti apskaičiuotąjį elementą su pardavimo komisinių, kurie gali skirtis kiekviename regione, formule. Tada „PivotTable“ į tarpines ir bendrąsias sumas automatiškai įtrauktų komisinius.
Kitas būdas apskaičiuoti – naudoti matus "Power Pivot", kurią sukuriate naudodami duomenų analizės išraiškų (DAX) formulę. Daugiau informacijos rasite "Power Pivot" priemonės kūrimas.
„PivotTable“ teikia duomenų skaičiavimo būdus. Sužinokite apie galimus skaičiavimo būdus, kaip šaltinio duomenų tipas daro įtaką skaičiavimams ir kaip naudoti „PivotTable“ bei „PivotChart“ formules.
Galimi skaičiavimo būdai
Norėdami apskaičiuoti „PivotTable“ reikšmes, galite naudoti vieną arba visus iš šių skaičiavimo būdų tipų:
Suvestinės funkcijos reikšmių laukuose Duomenys reikšmių srityje apibendrina esamus "PivotTable" šaltinio duomenis. Pavyzdžiui, šie šaltinio duomenys:
Sukuria šiuos „PivotTable“ ir „PivotChart“. Jei sukuriate "PivotChart" iš "PivotTable" duomenų, tos "PivotChart" reikšmės atspindi skaičiavimus susietoje "PivotTable" ataskaitoje.
„PivotTable“ stulpelio laukas Mėnuo pateikia elementus Kovas ir Balandis. Eilutės laukas Regionas pateikia elementus Šiaurė, Pietūs, Rytai ir Vakarai. Reikšmė stulpelio Balandis ir eilutės Šiaurė sankirtoje yra bendros pardavimo pajamos iš šaltinio duomenų įrašų, kuriuose yra Mėnuo reikšmė Balandis ir Regionas reikšmė Šiaurė.
„PivotChart“ laukas Regionas gali būti kategorijos laukas, rodantis Šiaurė, Pietūs, Rytai ir Vakarai kaip kategorijas. Laukas Mėnuo gali būti sekos laukas, rodantis elementus Kovas, Balandis ir Gegužė kaip seka, pateikta legendoje. Lauke Reikšmės pavadinimu Pardavimo suma gali būti duomenų žymeklių, nurodančių bendras kiekvieno regiono pajamas už kiekvieną mėnesį. Pavyzdžiui, vienas duomenų žymeklis, pagal savo poziciją vertikaliojoje (reikšmių) srityje, galėtų nurodyti balandžio pardavimo sumą šiaurės regione.
Norint apskaičiuoti reikšmių laukus, galimos toliau nurodytos suvestinės funkcijos visiems šaltinio duomenų tipams, išskyrus analitinio apdorojimo (OLAP) šaltinio duomenis.
Funkcija Apibendrinamas dalykas Sum Reikšmių suma. Tai yra numatytoji skaitinių duomenų funkcija. Count Duomenų reikšmių skaičių. Count suvestinės funkcija veikia taip pat, kaip funkcija COUNTA. Count yra numatytoji ne skaitinių duomenų funkcija. Vidurkis Reikšmių vidurkis. Maks. Didžiausia reikšmė. Min. Mažiausia reikšmė. Sandauga Reikšmių sandauga. Count Nums Duomenų reikšmių, kurios yra skaičiai, skaičius. Count Nums suvestinės funkcija veikia taip pat, kaip funkcija COUNT. StDev Standartinio aibės nuokrypio apytikris apskaičiavimas, kur pavyzdys yra visos aibės pogrupis. StDevp Standartinis aibės nuokrypis, kai aibė yra visi į suvestinę sudedami duomenys. Var Aibės dispersijos apytikris skaičiavimas, kur pavyzdys yra visos aibės pogrupis. Varp Aibės dispersija, kai aibė yra visi į suvestinę sudedami duomenys.
Pasirinktiniai skaičiavimai Pasirinktinis skaičiavimas rodo reikšmes, pagrįstas kitais duomenų srityje esančiais elementais arba langeliais. Pavyzdžiui, galite rodyti reikšmes duomenų lauke Pardavimo suma kaip kovo pardavimo procentą arba kaip bendrą elementų lauke Mėnuo sumą.
Šios funkcijos galimos naudoti pasirinktiniuose skaičiavimuose reikšmių laukuose.Funkcija Rezultatas Skaičiavimo nėra Rodo lauke įvestą reikšmę. Bendrosios sumos procentas Rodo reikšmes kaip visų reikšmių arba duomenų elementų, esančių ataskaitoje, bendrosios sumos išraišką procentais. Stulpelių sumos procentas Rodo, kiek procentų stulpelio arba sekos sumos sudaro kiekvieno stulpelio arba sekos reikšmės. Eilučių sumos procentas Rodo, kiek procentų eilutės arba kategorijos sumos sudaro kiekvienos eilutės arba kategorijos reikšmės. Pagrindo procentas Rodo reikšmes kaip pagrindo laukopagrindinio elemento reikšmės procentą. Pirminės eilutės sumos procentas Apskaičiuoja reikšmes taip:
(elemento reikšmė) / (pirminio elemento reikšmė eilutėse)Pirminio stulpelio sumos procentas Apskaičiuoja reikšmes taip:
(elemento reikšmė) / (pirminio elemento reikšmė stulpeliuose)Pirminės sumos procentas Apskaičiuoja reikšmes taip:
(elemento reikšmė) / (pasirinkto pagrindinio lauko pirminio elemento reikšmė)Skirtumas nuo Rodo reikšmes kaip skirtumą nuo pagrindo laukopagrindinio elemento reikšmės. Procentinis skirtumas nuo Rodo reikšmes kaip procentinį skirtumą nuo pagrindo laukopagrindinio elemento reikšmės. Bendroji suma Rodo vieno po kito einančių pagrindinio lauko elementų bendrąją sumą. Procentinė bendroji suma Apskaičiuoja vieno po kito einančių pagrindinio lauko elementų reikšmę, rodomą kaip procentinė bendroji suma. Klasifikacija nuo mažiausio iki didžiausio Rodo konkretaus lauko pasirinktų reikšmių klasifikaciją, kai mažiausias elementas lauke žymimas kaip 1, o kiekviena paskesnė reikšmė gauna aukštesnę klasifikacijos reikšmę. Klasifikacija nuo didžiausio iki mažiausio Rodo konkretaus lauko pasirinktų reikšmių klasifikaciją, kai didžiausias elementas lauke žymimas kaip 1, o kiekviena paskesnė reikšmė gauna aukštesnę klasifikacijos reikšmę. Indeksas Apskaičiuoja reikšmes taip:
((reikšmė langelyje) x (bendrųjų sumų bendroji suma)) / ((bendra eilutės suma) x (bendra stulpelio suma))
- Formulės Jei suvestinės funkcijos ir pasirinktiniai skaičiavimai nepateikia norimų rezultatų, galite sukurti savo formules apskaičiuotuosiuose laukuose ir apskaičiuotuosiuose elementuose. Pavyzdžiui, galite įtraukti apskaičiuotąjį elementą su pardavimo komisinių, kurie gali skirtis kiekviename regione, formule. Tada ataskaita į tarpines ir bendrąsias sumas automatiškai įtrauktų komisinius.
Kaip šaltinio duomenų tipas daro įtaką skaičiavimams
Skaičiavimai ir parinktys, kurios yra galimos ataskaitoje, priklauso nuo to, ar šaltinio duomenys yra iš OLAP duomenų bazės, ar ne OLAP duomenų šaltinio.
-
Skaičiavimai, pagrįsti OLAP šaltinio duomenimis Kuriant "PivotTable" iš OLAP kubų, apibendrintos reikšmės iš anksto apskaičiuojamos OLAP serveryje prieš jas rodant "Excel" programoje. Negalite pakeisti, kaip šios iš anksto apskaičiuotos reikšmės yra apskaičiuojamos „PivotTable“. Pavyzdžiui, negalite pakeisti suvestinės funkcijos, kuri naudojama duomenų laukams arba tarpinėms sumoms apskaičiuoti, arba įtraukti apskaičiuotųjų laukų arba apskaičiuotųjų elementų.
Taip pat, jei OLAP serveris pateikia apskaičiuotuosius laukus (apskaičiuotuosius narius), matysite šiuos laukus „PivotTable“ laukų sąraše. Taip pat matysite visus apskaičiuotuosius laukus ir apskaičiuotuosius elementus, kuriuos sukūrė makrokomandos, parašytos „Visual Basic for Applications“ (VBA) ir saugomos darbaknygėje, bet negalėsite keisti šių laukų arba elementų. Jei reikia papildomų skaičiavimų tipų, kreipkitės į savo OLAP duomenų bazės administratorių.
Naudojant OLAP šaltinio duomenis, galima įtraukti arba neįtraukti paslėptų elementų reikšmių, kai skaičiuojamos tarpinės ir bendrosios sumos. - Skaičiavimai, pagrįsti ne OLAP šaltinio duomenimis Kuriant "PivotTable", kurios pagrįstos kitais išorinių duomenų tipais arba darbalapio duomenimis, "Excel" naudoja suvestinės funkciją Sum, kad apskaičiuotų reikšmių laukus, kuriuose yra skaitinių duomenų, ir suvestinės funkciją Count, kad apskaičiuotų duomenų laukus, kuriuose yra tekstas. Galite pasirinkti kitą suvestinės funkciją, pvz., Average, Max arba Min, norėdami išsamiau analizuoti arba tinkinti duomenis. Taip pat galite kurti savo formules, kurios naudoja ataskaitos arba kitų darbalapio duomenų elementus, lauke sukurdami apskaičiuotąjį lauką arba apskaičiuotąjį elementą.
Formulių naudojimas „PivotTable“ lentelėse
Formules galite kurti tik tose ataskaitose, kurios pagrįstos ne OLAP šaltinio duomenimis. Negalite naudoti formulių ataskaitose, kurios pagrįstos OLAP duomenų baze. Kai naudojate formules „PivotTable“ lentelėse, turėtumėte žinoti apie toliau nurodytas formulių sintaksės taisykles ir formulių veikimą:
"PivotTable" formulių elementai Formulėse, skirtose apskaičiuotiesiems laukams ir apskaičiuotiesiems elementams, galite naudoti operatorius ir reiškinius kaip ir kitose darbalapio formulėse. Galite naudoti konstantas ir nurodyti duomenis iš ataskaitos, bet negalite naudoti langelių koordinačių arba apibrėžtųjų pavadinimų. Negalite naudoti darbalapio funkcijų, kurios reikalauja langelių koordinačių arba apibrėžtųjų pavadinimų kaip argumentų, ir negalite naudoti masyvo funkcijų.
Laukų ir elementų pavadinimai "Excel" naudoja laukų ir elementų pavadinimus, kad identifikuotų tuos ataskaitų elementus formulėse. Šiame pavyzdyje diapazono C3:C9 duomenys naudoja lauko pavadinimą Pieno produktai. Lauke Tipas esantis apskaičiuotasis elementas, kuris naujo produkto pardavimą apskaičiuoja pagal pieno produktų pardavimą, galėtų naudoti tokią formulę kaip =PienoProduktai * 115%.
Pastaba
„PivotChart“ laukų pavadinimai rodomi „PivotTable“ laukų sąraše, o elementų pavadinimai matomi kiekvieno lauko išplečiamajame sąraše. Nepainiokite šių pavadinimų su tais, kurie naudojami patarimuose, kurie vietoj to nurodo sekų ir duomenų elementų pavadinimus.
Formulės atlieka veiksmus su bendromis sumomis, ne atskirais įrašais Apskaičiuotųjų laukų formulės atlieka veiksmus su formulės laukų esamų duomenų bendra suma. Pavyzdžiui, apskaičiuotojo lauko formulė =Pardavimas * 1,2 kiekvieno tipo ir regiono pardavimo sumą padaugina iš 1,2; ji nepadaugina kiekvieno atskiro pardavimo iš 1,2 ir tada nesudeda sudaugintų skaičių.
Apskaičiuotųjų elementų formulės atlieka veiksmus su atskirais įrašais. Pavyzdžiui, apskaičiuotojo elemento formulė =PienoProduktai *115% kiekvieną atskirą pardavimą padaugina iš 115 % ir gautos sandaugos sumuojamos srityje Reikšmės.Tarpai, skaičiai ir simboliai pavadinimuose Pavadinime, kuriame yra daugiau nei vienas laukas, laukai gali būti bet kokia tvarka. Aukščiau pateiktame pavyzdyje langeliai C6:D6 gali būti „Balandis Šiaurė“ arba „Šiaurė Balandis“. Pavadinimus, kurie sudaryti iš daugiau nei vieno žodžio arba kuriuose yra skaičių arba simbolių, rašykite viengubose kabutėse.
Sumos Formulės negali nurodyti sumų (tokių kaip pavyzdyje pateiktos Kovo suma, Balandžio suma ir Bendroji suma).
Laukų pavadinimai elementų nuorodose Nuorodoje į elementą galite naudoti lauko pavadinimą. Elemento pavadinimas turi būti laužtiniuose skliaustuose, pvz., Regionas[Šiaurė]. Naudokite šį formatą, kad išvengtumėte #NAME? klaidų, kai du elementai dviejuose skirtinguose ataskaitos laukuose turi tą patį pavadinimą. Pavyzdžiui, jei ataskaitos lauke Tipas yra elementas pavadinimu Mėsa, o lauke Kategorija yra kitas elementas pavadinimu Mėsa, galite #NAME išvengti? klaidų nurodydami elementus kaip Tipas[Mėsa] ir Kategorija[Mėsa].
Elementų nurodymas pagal vietą Galite nurodyti elementą pagal jo vietą ataskaitoje (vietą, kurioje jis šiuo metu surūšiuotas ir rodomas). Tipas[1] yra Pieno produktai, o Tipas[2] yra Jūros gėrybės. Tokiu būdu nurodytas elementas gali būti pakeistas, kai pakeičiamos elementų vietos arba skirtingi elementai rodomi arba paslepiami. Paslėpti elementai šiame indekse neskaičiuojami.
Elementams nurodyti galite naudoti santykinę vietą. Vietos nustatomos pagal apskaičiuotąjį elementą, kuriame yra formulė. Jei dabartinis regionas yra Pietūs, tai Regionas[-1] yra Šiaurė; jei dabartinis regionas yra Šiaurė, tai Regionas[+1] yra Pietūs. Pavyzdžiui, apskaičiuotasis elementas galėtų naudoti formulę =Regionas[-1] * 3%. Jei vieta, kurią suteikiate, yra prieš pirmą lauko elementą arba po paskutinio elemento, formulės rezultatas yra #REF! klaida.
Formulių naudojimas „PivotChart“ diagramose
Norint naudoti formules „PivotChart“, sukuriamos formulės susietoje „PivotTable“, kur galima matyti atskiras reikšmes, sudarančias duomenis, tada galima grafiškai peržiūrėti rezultatus „PivotChart“.
Pavyzdžiui, šioje „PivotChart“ rodomas kiekvieno pardavėjo pardavimas atskiruose regionuose.
Norėdami pamatyti, kaip pardavimas atrodytų, jei jis būtų padidintas 10 procentų, susietoje „PivotTable“ galite sukurti apskaičiuotąjį lauką, kuris naudoja formulę, tokią kaip =Pardavimas * 110%.
Rezultatas iš karto bus rodomas „PivotChart“, kaip parodyta šioje diagramoje:
Norėdami matyti atskirą duomenų žymeklį, skirtą pardavimui regione Šiaurė, atėmus 8 procentų dydžio išlaidas, galite sukurti apskaičiuotąjį lauką lauke Regionas ir formulę, tokią kaip =Šiaurė – (Šiaurė * 8%).
Gauta diagrama turėtų atrodyti taip:
Tačiau apskaičiuotasis elementas, sukurtas lauke Pardavėjas, atsirastų kaip seka, pateikiama legendoje, ir diagramoje kaip duomenų elementas kiekvienoje kategorijoje.
Reikia daugiau pagalbos?
Visada galite kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.