Skaičiuokite skirtingas vertes „Excel“ suvestinėje lentelėje (paprastas žingsnis po žingsnio vadovas)

„Excel“ suvestinės lentelės yra nuostabios (žinau, kad tai paminėju kiekvieną kartą rašydamas apie „Pivot“ lenteles, bet tai tiesa).

Turėdami pagrindinį supratimą ir šiek tiek vilkite ir numeskite, per kelias sekundes galite atlikti kibirą darbų.

Nors daug ką galima nuveikti keliais paspaudimais „Pivot“ lentelėse, yra keletas dalykų, kuriems reikėtų atlikti keletą papildomų veiksmų arba šiek tiek pasistengti.

Ir vienas iš tokių dalykų yra skaičiuoti skirtingas vertes suvestinėje lentelėje.

Šioje pamokoje aš parodysiu, kaip skaičiuoti skirtingas vertes ir unikalias vertes „Excel Pivot“ lentelėje.

Tačiau prieš pradėdamas skaičiuoti skirtingas vertes, svarbu suprasti skirtumą tarp „skirtingo skaičiaus“ ir „unikalių skaičių“

Skirtingas skaičius prieš unikalų skaičių

Nors tai gali atrodyti kaip tas pats, tai ne.

Žemiau yra pavyzdys, kai yra vardų duomenų rinkinys, o unikalius ir skirtingus vardus išvardijau atskirai.

Unikalios vertės/vardai yra tie, kurie pasitaiko tik vieną kartą. Tai reiškia, kad visi pasikartojantys ir pasikartojantys vardai nėra unikalūs. Unikalūs pavadinimai išvardyti aukščiau pateikto duomenų rinkinio C stulpelyje

Skirtingos vertės/pavadinimai yra tie, kurie bent kartą pasitaiko duomenų rinkinyje. Taigi, jei vardas rodomas tris kartus, jis vis tiek laikomas vienu atskiru vardu. Tai galima pasiekti pašalinus pasikartojančias reikšmes/pavadinimus ir pasiliekant visas atskiras. Skirtingi pavadinimai išvardyti aukščiau pateikto duomenų rinkinio B stulpelyje.

Remdamiesi tuo, ką mačiau, dažniausiai žmonės sakydami, kad nori gauti unikalų skaičių „Pivot“ lentelėje, jie iš tikrųjų reiškia skirtingą skaičių, ką ir apžvelgiu šioje pamokoje.

Skaičiuokite skirtingas vertes „Excel“ suvestinėje lentelėje

Tarkime, kad turite pardavimo duomenis, kaip parodyta žemiau:

Spustelėkite čia, norėdami atsisiųsti pavyzdinį failą ir sekti toliau

Naudodami aukščiau pateiktą duomenų rinkinį, tarkime, kad norite rasti atsakymą į šiuos klausimus:

1. Kiek pardavimo atstovų yra kiekviename regione (tai yra ne kas kita, kaip atskiras pardavimo atstovų skaičius kiekviename regione)?
2. Kiek pardavėjų pardavė spausdintuvą 2021–2022 m.?

Nors „Pivot“ lentelės gali akimirksniu apibendrinti duomenis keliais paspaudimais, tačiau norėdami gauti skirtingų verčių skaičių, turėsite atlikti dar kelis veiksmus.

Jei naudojate „Excel 2013“ ar versijos po jos, „Pivot Table“ yra integruota funkcija, kuri greitai suteikia aiškų skaičių. Ir jei jūs naudojate „Excel 2010“ arba ankstesnės versijos, turėsite modifikuoti šaltinio duomenis, pridėdami pagalbinį stulpelį.

Šioje pamokoje aptariami šie du metodai:

• Pagalbinio stulpelio pridėjimas prie pradinio duomenų rinkinio, kad būtų skaičiuojamos unikalios vertės (veikia visose versijose).
• Duomenų pridėjimas prie duomenų modelio ir parinkties „Skirtingas skaičius“ naudojimas (pasiekiama „Excel 2013“ ir versijose po jos).

Yra trečias metodas, kurį Rodžeris parodo šiame straipsnyje (kurį jis vadina „Pivot the Pivot Table“ metodu).

Pradėkime!

Pagalbos stulpelio pridėjimas duomenų rinkinyje

Pastaba: jei naudojate „Excel 2013“ ir naujesnes versijas, praleiskite šį metodą ir pereikite prie kito (nes jame naudojama integruota „Pivot Table“ funkcija - Išskirtinis grafas).

Tai paprastas būdas skaičiuoti skirtingas vertes „Pivot“ lentelėje, nes prie šaltinio duomenų reikia pridėti tik pagalbinį stulpelį. Pridėję pagalbinį stulpelį, galite naudoti šį naują duomenų rinkinį, kad apskaičiuotumėte skirtingą skaičių.

Nors tai yra paprastas sprendimas, yra keletas šio metodo trūkumų (aptarti vėliau šioje pamokoje).

Pirmiausia parodysiu, kaip pridėti pagalbinį stulpelį ir gauti aiškų skaičių.

Tarkime, kad turiu duomenų rinkinį, kaip parodyta žemiau:

F stulpelyje pridėkite šią formulę ir pritaikykite ją visoms ląstelėms, kuriose yra duomenų gretimuose stulpeliuose.

= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Aukščiau pateikta formulė naudoja funkciją COUNTIFS, kad suskaičiuotų, kiek kartų vardas rodomas tam tikrame regione. Taip pat atkreipkite dėmesį, kad kriterijų diapazonas yra $ C $ 2: C2 ir $ B $ 2: B2. Tai reiškia, kad einant žemyn stulpeliu jis nuolat plečiasi.

Pavyzdžiui, E2 langelyje kriterijų diapazonai yra $ C $ 2: C2 ir $ B $ 2: B2, o E3 langelyje šie diapazonai išplečiami iki $ C $ 2: C3 ir $ B $ 2: B3.

Tai užtikrina, kad funkcija COUNTIFS pirmąjį vardo egzempliorių skaičiuoja kaip 1, antrąjį vardo egzempliorių kaip 2 ir pan.

Kadangi norime gauti tik atskirus pavadinimus, naudojama IF funkcija, kuri grąžina 1, kai regiono pavadinimas pirmą kartą pasirodo, ir grąžina 0, kai jis vėl pasirodo. Tai užtikrina, kad būtų skaičiuojami tik atskiri pavadinimai, o ne kartojimai.

Žemiau pateikiama, kaip atrodytų jūsų duomenų rinkinys, kai pridėsite pagalbinį stulpelį.

Dabar, kai pakeitėme šaltinio duomenis, galime juos panaudoti kurdami „Pivot“ lentelę ir naudodami pagalbinį stulpelį, kad gautume skirtingą kiekvieno regiono pardavimų atstovų skaičių.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite bet kurį duomenų rinkinio langelį.
2. Spustelėkite skirtuką Įterpti.
3. Spustelėkite „Pivot Table“ (arba naudokite sparčiuosius klavišus - ALT + N + V)
4. Dialogo lange Sukurti suvestinę lentelę įsitikinkite, kad lentelė/diapazonas yra teisingi (ir apima pagalbinį stulpelį) ir „Naujas darbalapis“.
5. Spustelėkite Gerai.

Pirmiau minėti veiksmai įterptų naują lapą, kuriame yra „Pivot Table“.

Vilkite lauką „Regionas“ eilučių srityje ir lauką „D Count“ srityje „Vertės“.

Gausite „Pivot“ lentelę, kaip parodyta žemiau:

Dabar galite pakeisti stulpelio antraštę iš „D skaičiaus suma“ į „Pardavimų atstovas“.

Pagalbos stulpelio naudojimo trūkumai:

Nors šis metodas yra gana paprastas, turiu pabrėžti keletą trūkumų, susijusių su šaltinio duomenų keitimu suvestinėje lentelėje:

• Duomenų šaltinis su pagalbiniu stulpeliu nėra toks dinamiškas kaip „Pivot Table“. Nors naudodami „Pivot“ lentelę galite pjaustyti ir supjaustyti duomenis bet kokiu būdu, kai naudojate pagalbinį stulpelį, prarandate dalį šių galimybių. Tarkime, kad pridėjote pagalbinį stulpelį, kad gautumėte atskirų pardavimų atstovų kiekviename regione skaičių. Ką daryti, jei taip pat norite gauti aiškų pardavėjų, parduodančių spausdintuvus, skaičių. Turėsite grįžti prie šaltinio duomenų ir pakeisti pagalbinio stulpelio formulę (arba pridėti naują pagalbinį stulpelį).
• Kadangi pridedate daugiau duomenų prie „Pivot Table“ šaltinio (kuris taip pat įtraukiamas į „Pivot Cache“), tai gali lemti didesnį „Excel“ failo dydį.
• Kadangi naudojame „Excel“ formulę, „Excel“ darbaknygė gali sulėtėti, jei turite tūkstančius duomenų eilučių.

Pridėkite duomenų prie duomenų modelio ir apibendrinkite naudodami skirtingą skaičių

„Pivot Table“ pridėjo naujų „Excel 2013“ funkcijų, leidžiančių gauti aiškų skaičių, apibendrinant duomenų rinkinį.

Jei naudojate ankstesnę versiją, negalėsite naudoti šio metodo (kaip ir pabandykite pridėti pagalbinį stulpelį, kaip parodyta aukščiau esančiame metode).

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti unikalių pardavimo atstovų kiekviename regione skaičių.

Toliau pateikiami veiksmai, kaip gauti skirtingą skaičiavimo vertę suvestinėje lentelėje:

1. Pasirinkite bet kurį duomenų rinkinio langelį.
2. Spustelėkite skirtuką Įterpti.
3. Spustelėkite „Pivot Table“ (arba naudokite sparčiuosius klavišus - ALT + N + V)
4. Dialogo lange Sukurti suvestinę lentelę įsitikinkite, kad lentelė/diapazonas yra teisingi ir naujas darbalapis pasirinktame.
5. Pažymėkite langelį „Pridėti šiuos duomenis prie duomenų modelio“
6. Spustelėkite Gerai.

Pirmiau minėti veiksmai įterptų naują lapą, kuriame yra nauja „Pivot Table“.

Vilkite regioną eilučių srityje, o pardavimų atstovą - vertybių srityje. Gausite „Pivot“ lentelę, kaip parodyta žemiau:

Aukščiau esančioje „Pivot“ lentelėje pateikiamas bendras pardavimo atstovų skaičius kiekviename regione (o ne atskiras skaičius).

Norėdami gauti aiškų skaičių „Pivot“ lentelėje, atlikite šiuos veiksmus:

1. Dešiniuoju pelės mygtuku spustelėkite bet kurį stulpelio „Pardavimų atstovų skaičius“ langelį.
2. Spustelėkite Vertės lauko nustatymai
3. Dialogo lange „Vertės lauko nustatymai“ kaip skaičiavimo tipą pasirinkite „Skirtingas skaičius“ (gali tekti slinkti sąrašu žemyn).
4. Spustelėkite Gerai.

Pastebėsite, kad stulpelio pavadinimas pasikeičia iš „Pardavimų atstovų skaičius“ į „Skirtingas pardavimų atstovų skaičius“. Galite jį pakeisti į ką tik norite.

Kai kuriuos dalykus, kuriuos žinote pridėdami duomenis prie duomenų modelio:

• Jei išsaugosite duomenis duomenų modelyje ir atidarysite senesnėje „Excel“ versijoje, bus parodytas įspėjimas - „Kai kurios suvestinės lentelės funkcijos nebus išsaugotos“. Gali būti, kad nematysite skirtingo skaičiaus (ir duomenų modelio), kai atidarysite senesnę versiją, kuri to nepalaiko.
• Kai pridedate duomenis prie duomenų modelio ir sukuriate „Pivot“ lentelę, joje nebus rodomos parinktys pridėti apskaičiuotus laukus ir apskaičiuotus stulpelius.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Ką daryti, jei norite suskaičiuoti unikalias vertybes (o ne atskiras vertybes)?

Jei norite skaičiuoti unikalias vertes, „Pivot“ lentelėje neturite jokių integruotų funkcijų ir turėsite pasikliauti tik pagalbiniais stulpeliais.

Atminkite - unikalios ir skirtingos vertės nėra tas pats. Spustelėkite čia, kad sužinotumėte skirtumą.

Vienas iš pavyzdžių galėtų būti tada, kai turite žemiau pateiktą duomenų rinkinį ir norite sužinoti, kiek pardavimo atstovų yra unikalūs kiekvienam regionui. Tai reiškia, kad jie veikia tik viename konkrečiame regione, o ne kituose.

Tokiais atvejais turite sukurti vieną iš daugiau nei vieno pagalbinio stulpelio.

Šiuo atveju ši formulė yra apgaulinga:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

Aukščiau pateikta formulė patikrina, ar pardavimo atstovo vardas yra tik viename regione ar daugiau nei viename regione. Tai daroma suskaičiuojant vardo atsiradimo regione skaičių ir padalijant jį iš bendro vardo įvykių skaičiaus. Jei reikšmė yra mažesnė nei 1, tai reiškia, kad pavadinimas yra dviejuose ar daugiau nei dviejuose regionuose.

Jei pavadinimas yra daugiau nei viename regione, jis grąžina 0, o grąžina vieną.

Formulė taip pat patikrina, ar vardas kartojamas tame pačiame regione, ar ne. Jei vardas kartojamas, tik pirmasis vardo egzempliorius grąžina reikšmę 1, o visi kiti - 0.

Tai gali atrodyti šiek tiek sudėtinga, tačiau tai vėlgi priklauso nuo to, ko siekiate.

Taigi, jei norite skaičiuoti unikalias vertes „Pivot“ lentelėje, naudokite pagalbinius stulpelius, o jei norite skaičiuoti skirtingas vertes, galite naudoti integruotą funkciją („Excel 2013“ ir naujesnėse versijose) arba naudoti pagalbinį stulpelį.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Jums taip pat gali patikti šios „Pivot Table“ pamokos:

• Kaip filtruoti duomenis „Pivot“ lentelėje „Excel“
• Kaip grupuoti datas „Excel“ suvestinėse lentelėse
• Kaip sugrupuoti skaičius „Pivot“ lentelėje „Excel“
• Kaip taikyti sąlyginį formatavimą „Excel“ suvestinėje lentelėje
• Pjaustyklės „Excel“ suvestinėje lentelėje
• Kaip atnaujinti „Pivot“ lentelę „Excel“
• Ištrinkite „Pivot“ lentelę „Excel“