Štetje različnih vrednosti v vrtilni tabeli Excel (enostaven vodnik po korakih)

Vrtilne tabele Excel so neverjetne (vem, da to omenjam vsakič, ko pišem o vrtilnih tabelah, vendar je res).

Z osnovnim razumevanjem in rahlim vlečenjem in spuščanjem lahko v nekaj sekundah opravite veliko dela.

Medtem ko je v vrtilnih tabelah z nekaj kliki mogoče narediti veliko, obstaja nekaj stvari, ki bi potrebovale nekaj dodatnih korakov ali pa se malo potrudile.

Ena takih stvari je štetje različnih vrednosti v vrtilni tabeli.

V tej vadnici vam bom pokazal, kako šteti različne vrednosti in edinstvene vrednosti v vrtilni tabeli Excel.

Toda preden skočim v to, kako šteti različne vrednosti, je pomembno razumeti razliko med "ločenim štetjem" in "edinstvenim štetjem"

Različno število proti edinstveno število

Čeprav se zdi, da je to isto, ni.

Spodaj je primer, kjer je nabor podatkov imen in sem ločeno navedel edinstvena in ločena imena.

Edinstvene vrednosti/imena so tiste, ki se pojavijo samo enkrat. To pomeni, da vsa imena, ki se ponavljajo in imajo podvojene, niso edinstvena. Edinstvena imena so navedena v stolpcu C v zgornjem naboru podatkov

Različne vrednosti/imena so tiste, ki se vsaj enkrat pojavijo v naboru podatkov. Torej, če se ime pojavi trikrat, se še vedno šteje kot eno ločeno ime. To lahko dosežete tako, da odstranite podvojene vrednosti/imena in ohranite vse različne. Različna imena so navedena v stolpcu B zgornjega niza podatkov.

Na podlagi tega, kar sem videl, v večini primerov, ko ljudje pravijo, da želijo dobiti edinstveno število v vrtilni tabeli, dejansko mislijo na različno štetje, kar obravnavam v tej vadnici.

Štejte različne vrednosti v vrtilni tabeli Excel

Recimo, da imate podatke o prodaji, kot je prikazano spodaj:

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Z zgornjim nizom podatkov recimo, da želite najti odgovor na naslednja vprašanja:

1. Koliko prodajnih predstavnikov je v vsaki regiji (kar ni nič drugega kot ločeno število prodajnih predstavnikov v vsaki regiji)?
2. Koliko prodajnih predstavnikov je prodalo tiskalnik v letih 2021–2022?

Medtem ko lahko vrtilne tabele v nekaj trenutkih povzamejo podatke, boste za število različnih vrednosti morali narediti še nekaj korakov.

Če uporabljate Excel 2013 ali različice po tem, v vrtilni tabeli je vgrajena funkcionalnost, ki vam hitro da natančno število. In če uporabljate Excel 2010 ali različice pred tem, boste morali izvorne podatke spremeniti tako, da dodate pomožni stolpec.

V tej vadnici sta obravnavani naslednji dve metodi:

• Dodajanje stolpca pomočnika v izvirni nabor podatkov za štetje edinstvenih vrednosti (deluje v vseh različicah).
• Dodajanje podatkov v podatkovni model in uporaba možnosti Distinct Count (na voljo v Excelu 2013 in različicah za tem).

Obstaja tretja metoda, ki jo Roger prikazuje v tem članku (ki jo imenuje metoda Pivot the Pivot Table).

Začnimo!

Dodajanje stolpca pomočnika v nabor podatkov

Opomba: Če uporabljate Excel 2013 in novejše različice, preskočite to metodo in se pomaknite na naslednjo (saj uporablja vgrajeno funkcijo vrtilne tabele - Različni grof).

To je preprost način za štetje različnih vrednosti v vrtilni tabeli, saj morate izvornim podatkom dodati le stolpec pomočnik. Ko dodate pomožni stolpec, lahko nato uporabite nov nabor podatkov za izračun ločenega števila.

Čeprav je to enostavno rešitev, obstaja nekaj pomanjkljivosti te metode (obravnavane kasneje v tej vadnici).

Naj vam najprej pokažem, kako dodate stolpec pomočnikov in dobite ločeno število.

Recimo, da imam niz podatkov, kot je prikazano spodaj:

V stolpec F dodajte naslednjo formulo in jo uporabite za vse celice, ki imajo podatke v sosednjih stolpcih.

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

Zgornja formula uporablja funkcijo COUNTIFS za štetje, kolikokrat se ime pojavi v danem območju. Upoštevajte tudi, da je obseg meril $ C $ 2: C2 in $ B $ 2: B2. To pomeni, da se med spuščanjem po stolpcu nenehno širi.

V celici E2 so na primer obsegi meril $ C $ 2: C2 in $ B $ 2: B2, v celici E3 pa se ti obsegi razširijo na $ C $ 2: C3 in $ B $ 2: B3.

To zagotavlja, da funkcija COUNTIFS šteje prvi primerek imena kot 1, drugi primerek imena kot 2 itd.

Ker želimo dobiti samo ločena imena, se uporablja funkcija IF, ki vrne 1, ko se ime prvič pojavi za regijo, in vrne 0, ko se ponovno pojavi. To zagotavlja, da se štejejo samo različna imena in ne ponavljanja.

Spodaj je prikazano, kako bi izgledal vaš nabor podatkov, ko dodate stolpec za pomoč.

Zdaj, ko smo spremenili izvorne podatke, lahko to uporabimo za ustvarjanje vrtilne tabele in s pomožnim stolpcem dobimo ločeno število prodajnih predstavnikov v vsaki regiji.

Spodaj so navedeni koraki za to:

1. Izberite katero koli celico v naboru podatkov.
2. Kliknite zavihek Vstavi.
3. Kliknite na vrtilno tabelo (ali uporabite bližnjico na tipkovnici - ALT + N + V)
4. V pogovornem oknu Ustvari vrtilno tabelo se prepričajte, da je tabela/obseg pravilna (in vključuje stolpec pomočnika) in da je izbran »Nov delovni list«.
5. Kliknite V redu.

Zgornji koraki bi vstavili nov list, ki ima vrtilno tabelo.

Povlecite polje »Regija« v območju Vrstice in polje »Štetje D« v območju Vrednosti.

Dobili boste vrtilno tabelo, kot je prikazano spodaj:

Zdaj lahko naslov stolpca spremenite iz „Vsota števila D“ v „Predstavnik prodaje“.

Slabosti uporabe stolpca za pomoč:

Čeprav je ta metoda precej preprosta, moram poudariti nekaj pomanjkljivosti, ki nastanejo pri spreminjanju izvornih podatkov v vrtilni tabeli:

• Vir podatkov s pomožnim stolpcem ni tako dinamičen kot vrtilna tabela. Medtem ko lahko z vrtilno tabelo razrežete in narežete podatke, ko uporabite stolpec za pomoč, izgubite del te sposobnosti. Recimo, da dodate stolpec za pomoč, da dobite število različnih prodajnih predstavnikov v vsaki regiji. Kaj pa, če želite tudi natančno prešteti število prodajalcev, ki prodajajo tiskalnike. Vrniti se boste morali k izvornim podatkom in spremeniti formulo stolpca pomočnika (ali dodati nov stolpec pomočnika).
• Ker v vir vrtilne tabele dodajate več podatkov (ki se dodajo tudi v vrtilni predpomnilnik), lahko to privede do večje velikosti datoteke Excel.
• Ker uporabljamo formulo Excel, lahko vaš Excelov delovni zvezek upočasni, če imate na tisoče vrstic podatkov.

Dodajte podatke v podatkovni model in povzemite z uporabo ločenega štetja

Vrtilna tabela je v Excelu 2013 dodala novo funkcionalnost, ki vam omogoča, da med povzemom nabora podatkov dobite ločeno število.

Če uporabljate prejšnjo različico, te metode ne boste mogli uporabiti (poskusite dodati stolpec za pomoč, kot je prikazano v metodi nad to).

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite pridobiti število edinstvenih prodajnih predstavnikov v vsaki regiji.

Spodaj so navedeni koraki za pridobitev posebne vrednosti štetja v vrtilni tabeli:

1. Izberite katero koli celico v naboru podatkov.
2. Kliknite zavihek Vstavi.
3. Kliknite na vrtilno tabelo (ali uporabite bližnjico na tipkovnici - ALT + N + V)
4. V pogovornem oknu Ustvari vrtilno tabelo se prepričajte, da je tabela/obseg pravilna in da je nov delovni list v izbranem.
5. Potrdite polje z napisom »Dodaj te podatke v podatkovni model«
6. Kliknite V redu.

Zgornji koraki bi vstavili nov list z novo vrtilno tabelo.

Povlecite regijo v območju vrstic in prodajnega predstavnika v območju vrednosti. Dobili boste vrtilno tabelo, kot je prikazano spodaj:

Zgornja vrtilna tabela prikazuje skupno število prodajnih predstavnikov v vsaki regiji (in ne ločeno število).

Če želite dobiti natančno število v vrtilni tabeli, sledite spodnjim korakom:

1. Z desno miškino tipko kliknite katero koli celico v stolpcu »Število prodajnih predstavnikov«.
2. Kliknite na Nastavitve polja vrednosti
3. V pogovornem oknu Nastavitve polja vrednosti kot vrsto izračuna izberite „Različno število“ (morda se boste morali pomakniti po seznamu, da ga najdete).
4. Kliknite V redu.

Opazili boste, da se ime stolpca spremeni iz „Število prodajnih predstavnikov“ v „Različno število prodajnih predstavnikov“. Lahko ga spremenite v karkoli želite.

Ko dodate podatke v podatkovni model, veste nekaj:

• Če podatke shranite v podatkovni model in jih nato odprete v starejši različici Excela, se prikaže opozorilo - "Nekatere funkcije vrtilne tabele ne bodo shranjene". Ko odprete v starejši različici, ki ga ne podpira, morda ne boste videli ločenega števila (in podatkovnega modela).
• Ko dodate podatke v podatkovni model in naredite vrtilno tabelo, ne bodo prikazane možnosti za dodajanje izračunanih polj in izračunanih stolpcev.

Kliknite tukaj, če želite prenesti primer datoteke

Kaj, če želite šteti edinstvene vrednosti (in ne ločene vrednosti)?

Če želite šteti edinstvene vrednosti, v vrtilni tabeli nimate vgrajenih funkcij in se boste morali zanašati samo na pomožne stolpce.

Ne pozabite - edinstvene vrednosti in različne vrednosti niso enake. Kliknite tukaj, če želite izvedeti razliko.

Primer je lahko, če imate spodnji niz podatkov in želite izvedeti, koliko prodajnih predstavnikov je edinstvenih za vsako regijo. To pomeni, da delujejo samo v eni določeni regiji in ne v drugih.

V takih primerih morate ustvariti enega ali več pomožnih stolpcev.

V tem primeru spodnja formula zmore:

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

Zgornja formula preverja, ali se ime prodajnega predstavnika pojavi samo v eni ali v več regijah. To stori tako, da šteje število pojavitev imena v regiji in ga deli s skupnim številom pojavitev imena. Če je vrednost manjša od 1, pomeni, da se ime pojavlja v dveh ali več kot dveh regijah.

Če se ime pojavi v več regijah, vrne 0, sicer vrne eno.

Formula preverja tudi, ali se ime ponavlja v istem območju ali ne. Če se ime ponovi, vrne vrednost samo prvi primerek imena, vsi drugi primerki pa 0.

To se morda zdi nekoliko zapleteno, vendar je spet odvisno od tega, kaj poskušate doseči.

Torej, če želite šteti edinstvene vrednosti v vrtilni tabeli, uporabite pomožne stolpce in če želite šteti različne vrednosti, lahko uporabite vgrajeno funkcionalnost (v Excelu 2013 in novejšem) ali pa uporabite stolpec pomočnik.

Kliknite tukaj, če želite prenesti primer datoteke

Morda vam bodo všeč tudi naslednje vaje za vrtilno tabelo:

• Kako filtrirati podatke v vrtilni tabeli v Excelu
• Kako združiti datume v vrtilnih tabelah v Excelu
• Kako združiti številke v vrtilni tabeli v Excelu
• Kako uporabiti pogojno oblikovanje v vrtilni tabeli v Excelu
• Rezalniki v vrtilni tabeli Excel
• Kako osvežiti vrtilno tabelo v Excelu
• Izbrišite vrtilno tabelo v Excelu