V tomto kurzu se naučíte počítat jedinečné hodnoty v Excelu pomocí vzorců (funkce COUNTIF a SUMPRODUCT).
Jak počítat jedinečné hodnoty v aplikaci Excel
Řekněme, že máme sadu dat, jak je uvedeno níže:
Pro účely tohoto tutoriálu pojmenuji rozsah A2: A10 jako NÁZVY. Do budoucna budeme ve vzorcích používat tento pojmenovaný rozsah.
Viz také: Jak vytvořit pojmenované rozsahy v aplikaci Excel.
V této sadě dat dochází k opakování v rozsahu NÁZVY. Chcete -li získat počet jedinečných jmen z této datové sady (A2: A10), můžeme použít kombinaci funkcí COUNTIF a SUMPRODUCT, jak je uvedeno níže:
= SUMPRODUCT (1/COUNTIF (JMEN, JMEN))
Jak tento vzorec funguje?
Pojďme si tento vzorec rozebrat, abychom lépe porozuměli:
- COUNTIF (JMENA, JMENA)
- Tato část vzorce vrací pole. Ve výše uvedeném příkladu by to bylo {2; 2; 3; 1; 3; 1; 2; 3; 2}. Čísla zde udávají, kolikrát se hodnota vyskytuje v daném rozsahu buněk.
Například jméno je Bob, které se v seznamu vyskytuje dvakrát, a proto by vrátilo číslo 2 pro Boba. Podobně se Steve vyskytuje třikrát, a proto je pro Steva vrácena 3.
- Tato část vzorce vrací pole. Ve výše uvedeném příkladu by to bylo {2; 2; 3; 1; 3; 1; 2; 3; 2}. Čísla zde udávají, kolikrát se hodnota vyskytuje v daném rozsahu buněk.
- 1/COUNTIF (JMENA, JMENA)
- Tato část vzorce vrátí pole - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Protože jsme dělili 1 polem, vrací toto pole.
Například první prvek pole vrácený výše byl 2. Když je 1 děleno 2, vrátí 0,5.
- Tato část vzorce vrátí pole - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (JMEN, JMEN))
- SUMPRODUCT jednoduše sečte všechna tato čísla. Všimněte si toho, že pokud se Bob v seznamu objeví dvakrát, výše uvedené pole vrátí hodnotu 0,5, ať už se v seznamu objeví jakékoli jméno Bob. Podobně, protože se Steve objeví třikrát v seznamu, pole vrátí 0,3333333, kdykoli se objeví Steveovo jméno. Když přidáme čísla pro každé jméno, vždy se vrátí 1. A pokud sečteme všechna čísla, vrátí celkový počet jedinečných jmen v seznamu.
Tento vzorec funguje dobře, dokud v rozsahu nemáte žádné prázdné buňky. Pokud však máte prázdné buňky, vrátí #DIV/0! chyba.
Jak zacházet s prázdnými buňkami?
Nejprve pochopíme, proč vrací chybu, když je v rozsahu prázdná buňka. Předpokládejme, že máme sadu dat, jak je uvedeno níže (s buňkou A3 je prázdná):
Pokud nyní použijeme stejný vzorec, který jsme použili výše, část vzorce COUNTIF vrací pole {2; 0; 3; 1; 3; 1; 2; 3; 1}. Protože v buňce A3 není žádný text, je jeho počet vrácen jako 0.
A protože dělíme 1 tímto celým polem, vrací #DIV/0! chyba.
Chcete -li tuto chybu dělení v případě prázdných buněk zpracovat, použijte následující vzorec:
= SUMPRODUCT ((1/COUNTIF (JMEN, JMEN & ””)))
Jednou změnou, kterou jsme v tomto vzorci provedli, je část kritérií funkce COUNTIF. Místo NÁZVŮ jsme použili NÁZVY & ””. Tímto způsobem vzorec vrátí počet prázdných buněk (dříve vrátil 0, kde byla prázdná buňka).
POZNÁMKA: Tento vzorec by počítal prázdné buňky jako jedinečnou hodnotu a vrátil by ji do výsledku.
Ve výše uvedeném příkladu by měl být výsledek 5, ale vrátí 6, protože prázdná buňka se počítá jako jedna z jedinečných hodnot.
Zde je vzorec, který se stará o prázdné buňky a nezapočítává je do konečného výsledku:
= SUMPRODUCT ((JMEN ””)/COUNTIF (JMENA, JMEN & ””))
V tomto vzorci jsme místo 1 jako čitatele použili JMÉNA ””. Tím se vrátí pole PRAVDA a NEPRAVDA. Vrací FALSE vždy, když je prázdná buňka. Protože TRUE se rovná 1 a FALSE odpovídá 0 ve výpočtech, prázdné buňky se nepočítají, protože čitatel je 0 (FALSE).
Nyní, když máme základní kostru vzorce připravenou, můžeme jít o krok dále a počítat různé datové typy.
Jak počítat jedinečné hodnoty v aplikaci Excel, které jsou textové
Použijeme stejný koncept diskutovaný výše k vytvoření vzorce, který bude počítat pouze textové hodnoty, které jsou jedinečné.
Zde je vzorec, který bude počítat jedinečné textové hodnoty v aplikaci Excel:
= SUMPRODUCT ((ISTEXT (JMEN)/COUNTIF (JMEN, JMEN & ””))))
Jediné, co jsme udělali, je použít jako čitatele vzorec ISTEXT (NÁZVY). Pokud buňka obsahuje text, vrátí hodnotu TRUE, a pokud NE, NEPRAVDA. Nebude počítat prázdné buňky, ale bude počítat buňky, které mají prázdný řetězec (“”).
Jak počítat jedinečné hodnoty v aplikaci Excel, které jsou číselné
Zde je vzorec, který bude počítat jedinečné číselné hodnoty v aplikaci Excel
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))
Zde používáme jako čitatele ISNUMBER (NÁZVY). Pokud buňka obsahuje číselný datový typ, vrátí hodnotu PRAVDA, a pokud NE, NEPRAVDA. Nepočítá prázdné buňky.
