✅ Štejte edinstvene vrednosti v Excelu s funkcijo COUNTIF

Kazalo

Kako šteti edinstvene vrednosti v Excelu

V tej vadnici se boste naučili, kako šteti edinstvene vrednosti v Excelu s formulami (funkcije COUNTIF in SUMPRODUCT).

Kako šteti edinstvene vrednosti v Excelu

Recimo, da imamo nabor podatkov, kot je prikazano spodaj:

Za namene te vadnice bom obseg A2: A10 poimenoval NAMES. V prihodnje bomo to imenovano območje uporabljali v formulah.

Glejte tudi: Kako ustvariti imenovane obsege v Excelu.

V tem nizu podatkov se v območju NAMES ponovi. Če želite pridobiti število edinstvenih imen iz tega nabora podatkov (A2: A10), lahko uporabimo kombinacijo funkcij COUNTIF in SUMPRODUCT, kot je prikazano spodaj:

= SUMPRODUCT (1/COUNTIF (IMENA, IMENA))

Kako deluje ta formula?

Za boljše razumevanje razčlenimo to formulo:

COUNTIF (IMENA, IMENA)
- Ta del formule vrne matriko. V zgornjem primeru bi bilo to {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tu številke označujejo, kolikokrat se vrednost pojavi v danem obsegu celic.
  Na primer, ime je Bob, ki se na seznamu pojavi dvakrat, zato bi Bob vrnil številko 2. Podobno se Steve pojavi trikrat in zato se Steve vrne 3.
1/COUNTIF (IMENA, IMENA)
- Ta del formule bi vrnil matriko - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
  Ker smo 1 delili z matriko, vrne to matriko.
  Na primer, prvi element zgoraj vrnjene matrike je bil 2. Ko je 1 deljeno z 2, vrne .5.
PODROČJE (1/COUNTIF (IMENA, IMENA))
- SUMPRODUCT preprosto doda vse te številke. Upoštevajte, da če se Bob na seznamu pojavi dvakrat, zgornja matrika vrne .5 povsod, kjer se je ime Boba pojavilo na seznamu. Podobno, ker se Steve na seznamu pojavi trikrat, matrika vrne .3333333 vsakič, ko se pojavi ime Steve. Ko dodamo številke za vsako ime, bi se vedno vrnilo 1. In če dodamo vse številke, bi vrnilo skupno število edinstvenih imen na seznamu.

Ta formula deluje dobro, dokler v razponu nimate praznih celic. Če pa imate prazne celice, bi vrnilo #DIV/0! napaka.

Kako ravnati s celicami BLANK?

Najprej razumemo, zakaj vrne napako, če je v obsegu prazna celica. Recimo, da imamo nabor podatkov, kot je prikazano spodaj (celica A3 je prazna):

Če uporabimo isto formulo, ki smo jo uporabili zgoraj, del formule COUNTIF vrne matriko {2; 0; 3; 1; 3; 1; 2; 3; 1}. Ker v celici A3 ni besedila, se njegovo število vrne kot 0.

In ker delimo 1 s tem celotnim nizom, vrne #DIV/0! napaka.

Če želite odpraviti to napako pri delitvi v primeru praznih celic, uporabite naslednjo formulo:

= SUMPRODUCT ((1/COUNTIF (IMENA, IMENA & ””)))

Ena sprememba, ki smo jo naredili v tej formuli, je merilni del funkcije COUNTIF. Namesto NAMES smo uporabili NAMES & ””. S tem bi formula vrnila število praznih celic (prej je vrnila 0, kjer je bila prazna celica).

OPOMBA: Ta formula bi štela prazne celice kot edinstveno vrednost in jo vrnila v rezultat.

V zgornjem primeru bi moral biti rezultat 5, vendar vrne 6, saj se prazna celica šteje kot ena od edinstvenih vrednosti.

Tu je formula, ki skrbi za prazne celice in je ne šteje v končni rezultat:

= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))

V tej formuli smo namesto 1 kot števca uporabili NAMENE ””. To vrne niz TRUE in FALSEs. Vrne vrednost FALSE, kadar je prazna celica. Ker je TRUE v izračunih enak 1, FALSE pa 0, se prazne celice ne štejejo, saj je števec 0 (FALSE).

Zdaj, ko imamo pripravljeno osnovno okostje formule, lahko gremo še korak dlje in štejemo različne vrste podatkov.

Kako šteti edinstvene vrednosti v Excelu, ki so besedilo

Isti koncept, ki smo ga obravnavali zgoraj, bomo uporabili za ustvarjanje formule, ki bo štela samo edinstvene besedilne vrednosti.

Tu je formula, ki bo štela edinstvene besedilne vrednosti v Excelu:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))

Vse, kar smo naredili, je kot števec uporabljena formula ISTEXT (NAMES). Ko celica vsebuje besedilo, vrne TRUE, če pa ne. Ne bo štel praznih celic, ampak bo štel celice, ki imajo prazen niz (»«).