Brojanje različitih vrijednosti u Excel zaokretnoj tablici (jednostavan korak-po-korak vodič)

Excel zaokretne tablice su nevjerojatne (znam da to spominjem svaki put kad pišem o zaokretnim tablicama, ali to je istina).

Uz osnovno razumijevanje i malo povlačenja i ispuštanja, možete obaviti hrpu posla u nekoliko sekundi.

Iako se u zaokretnim tablicama može učiniti mnogo s nekoliko klikova, postoje neke stvari koje bi trebale nekoliko dodatnih koraka ili malo zaobići.

Jedna od takvih stvari je brojanje različitih vrijednosti u zaokretnoj tablici.

U ovom vodiču pokazat ću vam kako brojati različite vrijednosti, kao i jedinstvene vrijednosti u Excel zaokretnoj tablici.

No, prije nego što skočim u brojanje različitih vrijednosti, važno je razumjeti razliku između "različitog broja" i "jedinstvenog brojanja"

Različiti brojevi vs jedinstveni brojevi

Iako se ovo može činiti kao ista stvar, nije.

Ispod je primjer gdje postoji skup imena, a ja sam zasebno navela jedinstvena i različita imena.

Jedinstvene vrijednosti/imena su one koje se javljaju samo jednom. To znači da svi nazivi koji se ponavljaju i imaju duplikate nisu jedinstveni. Jedinstveni nazivi navedeni su u stupcu C u gornjem skupu podataka

Različite vrijednosti/nazivi su one koje se pojavljuju barem jednom u skupu podataka. Dakle, ako se ime pojavi tri puta, i dalje se računa kao jedno posebno ime. To se može postići uklanjanjem dupliciranih vrijednosti/naziva i zadržavanjem svih različitih. Različiti nazivi navedeni su u stupcu B u gornjem skupu podataka.

Na temelju onoga što sam vidio, u većini slučajeva kada ljudi kažu da žele dobiti jedinstveni broj u zaokretnoj tablici, zapravo misle na različit broj, što ću i pokriti u ovom vodiču.

Prebrojite različite vrijednosti u Excel zaokretnoj tablici

Pretpostavimo da imate podatke o prodaji kako je prikazano u nastavku:

Kliknite ovdje za preuzimanje datoteke primjera i slijedite je

Uz gornji skup podataka recimo da želite pronaći odgovor na sljedeća pitanja:

1. Koliko prodajnih predstavnika ima u svakoj regiji (što nije ništa drugo nego različit broj prodajnih predstavnika u svakoj regiji)?
2. Koliko je prodajnih predstavnika prodalo pisač u razdoblju od 2021. do 2022. godine?

Iako zaokretne tablice mogu u trenu sažeti podatke s nekoliko klikova, da biste dobili broj različitih vrijednosti, morat ćete poduzeti još nekoliko koraka.

Ako koristite Excel 2013 ili verzije nakon toga, postoji ugrađena funkcija u zaokretnoj tablici koja vam brzo daje poseban broj. A ako koristite Excel 2010 ili inačice prije toga, morat ćete izmijeniti izvorne podatke dodavanjem stupca pomoćnika.

Sljedeće dvije metode obrađene su u ovom vodiču:

• Dodavanje stupca pomoćnika u izvorni skup podataka za brojanje jedinstvenih vrijednosti (radi u svim verzijama).
• Dodavanje podataka u model podataka i upotreba opcije Distinct Count (dostupno u Excelu 2013 i verzijama nakon toga).

Postoji i treća metoda koju Roger prikazuje u ovom članku (koju naziva metodom zaokretne zaokretne tablice).

Započnimo!

Dodavanje stupca pomoćnika u skup podataka

Napomena: Ako koristite Excel 2013 i novije verzije, preskočite ovu metodu i prijeđite na sljedeću (jer koristi ugrađenu funkciju zaokretne tablice - Različiti grof).

Ovo je jednostavan način za brojanje različitih vrijednosti u zaokretnoj tablici jer izvornim podacima trebate samo dodati stupac pomoćnik. Nakon što dodate pomoćni stupac, tada možete koristiti ovaj novi skup podataka za izračun različitog broja.

Iako je ovo jednostavno zaobilazno rješenje, postoje neki nedostaci ove metode (obrađeni kasnije u ovom vodiču).

Dopustite mi da vam prvo pokažem kako dodati pomoćni stupac i dobiti poseban broj.

Pretpostavimo da imam skup podataka kako je prikazano u nastavku:

Dodajte sljedeću formulu u stupac F i primijenite je za sve ćelije koje imaju podatke u susjednim stupcima.

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

Gornja formula koristi funkciju COUNTIFS za brojanje broja pojavljivanja imena u danoj regiji. Također imajte na umu da je raspon kriterija $ C $ 2: C2 i $ B $ 2: B2. To znači da se nastavlja širiti dok se spuštate niz stupac.

Na primjer, u ćeliji E2 rasponi kriterija su $ C $ 2: C2 i $ B $ 2: B2, a u ćeliji E3 ti se rasponi proširuju na $ C $ 2: C3 i $ B $ 2: B3.

Time se osigurava da funkcija COUNTIFS broji prvu instancu imena kao 1, drugu instancu imena kao 2 itd.

Budući da želimo samo dobiti različita imena, koristi se funkcija IF koja vraća 1 kad se naziv pojavi za regiju prvi put i vraća 0 kad se ponovno pojavi. Time se osigurava da se broje samo različita imena, a ne ponavljanja.

U nastavku je prikazano kako bi vaš skup podataka izgledao kad dodate stupac pomoćnika.

Sada kada smo izmijenili izvorne podatke, ovo možemo upotrijebiti za stvaranje zaokretne tablice i upotrijebiti pomoćni stupac za dobivanje različitog broja prodajnih predstavnika u svakoj regiji.

U nastavku su navedeni koraci za to:

1. Odaberite bilo koju ćeliju u skupu podataka.
2. Kliknite karticu Umetni.
3. Kliknite na zaokretnu tablicu (ili upotrijebite tipkovnički prečac - ALT + N + V)
4. U dijaloškom okviru Stvori zaokretnu tablicu provjerite je li tablica/raspon ispravna (i uključuje stupac pomoćnika) i je li odabran "Novi radni list".
5. Pritisnite U redu.

Gore navedeni koraci umetnuli bi novi list koji ima zaokretnu tablicu.

Povucite polje 'Regija' u području Redovi i 'D Broj' u području Vrijednosti.

Dobit ćete zaokretnu tablicu kao što je prikazano u nastavku:

Sada možete promijeniti zaglavlje stupca iz "Zbroj D broja" u "Sales Rep".

Nedostaci korištenja pomoćnog stupca:

Iako je ova metoda prilično jednostavna, moram istaknuti nekoliko nedostataka koji dolaze s izmjenom izvornih podataka u zaokretnoj tablici:

• Izvor podataka s pomoćnim stupcem nije tako dinamičan kao zaokretna tablica. Iako možete zarezati i izrezati podatke na bilo koji način pomoću zaokretne tablice, kada koristite pomoćni stupac, gubite dio te sposobnosti. Recimo da dodate stupac pomoćnik da biste dobili broj različitih prodajnih predstavnika u svakoj regiji. Što ako želite i dobiti jasan broj prodavača koji prodaju pisače. Morat ćete se vratiti na izvorne podatke i izmijeniti formulu stupca pomoćnika (ili dodati novi stupac pomoćnika).
• Budući da izvoru zaokretne tablice dodajete više podataka (koji se također dodaje zaokretnoj predmemoriji), to može dovesti do veće veličine Excel datoteke.
• Budući da koristimo Excel formulu, može usporiti radnu knjigu programa Excel u slučaju da imate tisuće redaka podataka.

Dodajte podatke u model podataka i sažmite pomoću različitog broja

Zaokretna tablica dodala je novu funkcionalnost u programu Excel 2013 koja vam omogućuje da dobijete različit broj dok sažimate skup podataka.

U slučaju da koristite prethodnu verziju, nećete moći koristiti ovu metodu (kao što biste trebali pokušati dodati stupac pomoćnika kako je prikazano u metodi iznad ove).

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dobiti broj jedinstvenih prodajnih predstavnika u svakoj regiji.

U nastavku su navedeni koraci za dobivanje različite vrijednosti broja u zaokretnoj tablici:

1. Odaberite bilo koju ćeliju u skupu podataka.
2. Kliknite karticu Umetni.
3. Kliknite na zaokretnu tablicu (ili upotrijebite tipkovnički prečac - ALT + N + V)
4. U dijaloškom okviru Stvaranje zaokretne tablice provjerite je li tablica/raspon ispravna, a novi radni list u odabranom.
5. Potvrdite okvir koji kaže - "Dodajte ove podatke u model podataka"
6. Pritisnite U redu.

Gore navedeni koraci umetnuli bi novi list koji ima novu zaokretnu tablicu.

Povucite regiju u području redova i prodajnog predstavnika u području vrijednosti. Dobit ćete zaokretnu tablicu kao što je prikazano u nastavku:

Gornja zaokretna tablica daje ukupan broj prodajnih predstavnika u svakoj regiji (a ne zaseban broj).

Da biste dobili različit broj u zaokretnoj tablici, slijedite korake u nastavku:

1. Desnom tipkom miša kliknite bilo koju ćeliju u stupcu "Broj prodajnog predstavnika".
2. Pritisnite Postavke polja vrijednosti
3. U dijaloškom okviru Postavke polja vrijednosti odaberite "Različit broj" kao vrstu izračuna (možda ćete se morati pomaknuti prema dolje po popisu da biste ga pronašli).
4. Pritisnite U redu.

Primijetit ćete da se naziv stupca mijenja iz "Broj prodajnog predstavnika" u "Različit broj prodajnih predstavnika". Možete ga promijeniti u što god želite.

Neke stvari znate kada dodate podatke u model podataka:

• Ako svoje podatke spremite u podatkovni model, a zatim otvorite u starijoj verziji programa Excel, prikazat će se upozorenje - ‘Neke funkcije zaokretne tablice neće biti spremljene’. Možda nećete vidjeti različit broj (i model podataka) kada se otvori u starijoj verziji koja ga ne podržava.
• Kada svoje podatke dodate u podatkovni model i napravite zaokretnu tablicu, neće se prikazati mogućnosti za dodavanje izračunatih polja i izračunatih stupaca.

Kliknite ovdje za preuzimanje datoteke primjera

Što ako želite brojati jedinstvene vrijednosti (a ne različite vrijednosti)?

Ako želite brojati jedinstvene vrijednosti, nemate nikakvu ugrađenu funkcionalnost u zaokretnoj tablici i morat ćete se osloniti samo na pomoćne stupce.

Upamtite - jedinstvene vrijednosti i različite vrijednosti nisu iste. Kliknite ovdje da biste saznali razliku.

Jedan primjer mogao bi biti kada imate dolje navedeni skup podataka i želite saznati koliko je predstavnika prodaje jedinstveno za svaku regiju. To znači da djeluju samo u jednoj određenoj regiji, a ne u drugim.

U takvim slučajevima morate stvoriti jedan od više stupaca pomoćnika.

U ovom slučaju sljedeća formula pomaže:

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

Gornja formula provjerava pojavljuje li se naziv prodajnog predstavnika samo u jednoj regiji ili u više regija. To čini tako što broji broj pojavljivanja imena u regiji i dijeli ga s ukupnim brojem pojavljivanja imena. Ako je vrijednost manja od 1, to znači da se ime pojavljuje u dvije ili više od dvije regije.

U slučaju da se ime pojavljuje u više od jedne regije, vraća 0, a vraća jedno.

Formula također provjerava ponavlja li se naziv u istoj regiji ili ne. Ako se ime ponovi, samo prva instanca imena vraća vrijednost 1, a sve ostale instance vraćaju 0.

Ovo može izgledati pomalo složeno, ali opet ovisi o tome što pokušavate postići.

Dakle, ako želite brojati jedinstvene vrijednosti u zaokretnoj tablici, upotrijebite pomoćne stupce, a ako želite brojati različite vrijednosti, možete upotrijebiti ugrađenu funkcionalnost (u Excelu 2013 i novijim) ili možete upotrijebiti pomoćni stupac.

Kliknite ovdje za preuzimanje datoteke primjera

Možda će vam se svidjeti i sljedeći vodiči za zaokretnu tablicu:

• Kako filtrirati podatke u zaokretnoj tablici u Excelu
• Kako grupirati datume u zaokretnim tablicama u Excelu
• Kako grupirati brojeve u zaokretnoj tablici u Excelu
• Kako primijeniti uvjetno oblikovanje u zaokretnoj tablici u Excelu
• Rezači u zaokretnoj tablici programa Excel
• Kako osvježiti zaokretnu tablicu u Excelu
• Brisanje zaokretne tablice u Excelu