✅ Počítajte jedinečné hodnoty v programe Excel pomocou funkcie COUNTIF

Obsah

Ako počítať jedinečné hodnoty v programe Excel

V tomto návode sa naučíte počítať jedinečné hodnoty v Exceli pomocou vzorcov (funkcie COUNTIF a SUMPRODUCT).

Ako počítať jedinečné hodnoty v programe Excel

Povedzme, že máme súbor údajov, ako je uvedené nižšie:

Na účely tohto tutoriálu pomenujem rozsah A2: A10 ako NÁZVY. V budúcnosti budeme tento pomenovaný rozsah používať vo vzorcoch.

Pozri tiež: Ako vytvoriť pomenované rozsahy v programe Excel.

V tomto súbore údajov dochádza k opakovaniu v rozsahu NÁZVY. Na získanie počtu jedinečných mien z tohto súboru údajov (A2: A10) môžeme použiť kombináciu funkcií COUNTIF a SUMPRODUCT, ako je uvedené nižšie:

= SUMPRODUCT (1/COUNTIF (NÁZVY, MENÁ))

Ako tento vzorec funguje?

Poďme rozobrať tento vzorec, aby sme lepšie porozumeli:

COUNTIF (NÁZVY, MENÁ)
- Táto časť vzorca vracia pole. Vo vyššie uvedenom príklade by to bolo {2; 2; 3; 1; 3; 1; 2; 3; 2}. Čísla tu uvádzajú, koľkokrát sa hodnota vyskytuje v danom rozsahu buniek.
  Napríklad meno je Bob, ktoré sa v zozname vyskytuje dvakrát, a preto by pre Bob vrátilo číslo 2. Podobne sa Steve vyskytne trikrát, a preto sa Steveovi vráti 3.
1/COUNTIF (MENÁ, MENÁ)
- Táto časť vzorca vráti pole - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
  Pretože sme 1 delili poľom, vráti toto pole.
  Napríklad prvý prvok vyššie uvedeného poľa bol 2. Keď je 1 delené 2, vráti 0,5.
SUMPRODUCT (1/COUNTIF (NÁZVY, MENÁ))
- SUMPRODUCT všetky tieto čísla jednoducho sčíta. Všimnite si toho, že ak sa Bob vyskytne v zozname dvakrát, vyššie uvedené pole vráti hodnotu 0,5, kdekoľvek sa meno Bob v zozname objaví. Podobne, pretože Steve sa v zozname objaví trikrát, pole vráti 0,3333333 vždy, keď sa objaví meno Steve. Keď pripočítame čísla pre každé meno, vždy sa vráti 1. A ak spočítame všetky čísla, vráti celkový počet jedinečných mien v zozname.

Tento vzorec funguje, kým v rozsahu nemáte žiadne prázdne bunky. Ak však máte prázdne bunky, vráti sa #DIV/0! chyba.

Ako zaobchádzať s prázdnymi bunkami?

Poďme najskôr pochopiť, prečo vracia chybu, keď je v rozsahu prázdna bunka. Predpokladajme, že máme súbor údajov, ako je uvedené nižšie (s bunkou A3 je prázdna):

Teraz, ak použijeme rovnaký vzorec, ktorý sme použili vyššie, časť vzorca COUNTIF vráti pole {2; 0; 3; 1; 3; 1; 2; 3; 1}. Pretože v bunke A3 nie je žiadny text, jeho počet sa vráti ako 0.

A keďže delíme 1 na celé toto pole, vráti #DIV/0! chyba.

Na zvládnutie tejto chyby delenia v prípade prázdnych buniek použite nasledujúci vzorec:

= SUMPRODUCT ((1/COUNTIF (NÁZVY, NÁZVY & ””)))

Jednou zmenou, ktorú sme v tomto vzorci urobili, je časť s kritériami funkcie COUNTIF. Namiesto NÁZOV sme použili NAMES & ””. Ak to urobíte, vzorec vráti počet prázdnych buniek (predtým vrátil 0 tam, kde bola prázdna bunka).

POZNÁMKA: Tento vzorec by započítal prázdne bunky ako jedinečnú hodnotu a vrátil by ich do výsledku.

Vo vyššie uvedenom príklade by mal byť výsledok 5, ale vráti 6, pretože prázdna bunka sa počíta ako jedna z jedinečných hodnôt.

Tu je vzorec, ktorý sa stará o prázdne bunky a nezapočítava ich do konečného výsledku:

= SUMPRODUCT ((NÁZVY ””)/COUNTIF (NÁZVY, NÁZVY & ””))

V tomto vzorci sme namiesto 1 ako čitateľa použili NAME “”. Vráti sa tým pole PRAVDA a NEPRAVDA. Vždy, keď je prázdna bunka, vráti hodnotu FALSE. Pretože TRUE sa vo výpočtoch rovná 1 a FALSE sa rovná 0, prázdne bunky sa nepočítajú, pretože čitateľ má hodnotu 0 (FALSE).

Teraz, keď máme základnú kostru vzorca pripravenú, môžeme ísť o krok ďalej a počítať rôzne typy údajov.

Ako počítať jedinečné hodnoty v programe Excel, ktoré sú textové

Na vytvorenie vzorca, ktorý bude počítať iba textové hodnoty, ktoré sú jedinečné, použijeme rovnaký koncept, o ktorom sme hovorili vyššie.

Tu je vzorec, ktorý bude počítať jedinečné textové hodnoty v programe Excel:

= SUMPRODUCT ((ISTEXT (NÁZVY)/COUNTIF (MENÁ, NÁZVY & ””))))

Jediné, čo sme urobili, je použiť ako čitateľa vzorec ISTEXT (NÁZVY). Vráti hodnotu TRUE, ak bunka obsahuje text, a FALSE, ak nie je. Nebude počítať prázdne bunky, ale bude počítať bunky, ktoré majú prázdny reťazec („“).