Dobře, takže v mých počátcích analýzy dat jsem dostal potřebu automaticky získat jedinečné položky v Excelu ze seznamu, místo abych pokaždé odstraňoval duplikáty. A vymyslel jsem způsob, jak to udělat. Poté byl můj řídicí panel Excelu ještě dynamičtější než dříve. Pojďme se tedy podívat, jak to můžeme udělat…
K extrahování seznamu jedinečných hodnot ze seznamu použijeme INDEX, MATCH a COUNTIF. Budu také používat IFERROR, jen aby měl čisté výsledky, jeho volitelný.
A ano, bude to maticový vzorec … Takže pojďme na to…
Obecný vzorec pro extrahování jedinečných hodnot v aplikaci Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Seznam odkazů: Seznam, ze kterého chcete extrahovat jedinečné hodnoty
Expanding_output_range: Nyní je to velmi důležité. Toto je rozsah, ve kterém chcete vidět svůj extrahovaný seznam. Tento rozsah musí mít odlišný nadpis, který není součástí seznamu, a váš vzorec bude pod nadpisem (pokud je nadpis v E1, bude vzorec v E2).
Nyní rozšíření znamená, že když přetáhnete vzorec dolů, měl by se rozšířit nad výstupní rozsah. Chcete -li to provést, musíte uvést odkaz na nadpis jako $ E $ 1: E1 (Můj nadpis je v E1). Když ho stáhnu dolů, roztáhne se. V E2 to bude $ E $ 1: E1. V E3 to bude $ E $ 1: E2. V E2 to bude $ E $ 1: E3 a tak dále.
Nyní se podívejme na příklad. Bude to jasné.
Extrahování jedinečných hodnot Příklad aplikace Excel
Takže tady mám tento seznam zákazníků ve Sloupci A v dosahu A2: A16. Nyní ve sloupci E chci získat jedinečné hodnoty pouze od zákazníků. Nyní se může zvýšit i tento rozsah A2: A16, takže chci, aby můj vzorec načítal jakékoli nové jméno zákazníka ze seznamu, kdykoli se seznam zvětší.
Dobře, nyní pro načtení jedinečných hodnot ze sloupce A napište tento vzorec Buňka E2, a udeřil CTRL+SHIFT+ENTER aby to byl vzorec pole.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Očekávám, že se tento seznam rozšíří a může mít nové jedinečné hodnoty, které budu chtít extrahovat. To je důvod, proč jsem to nechal otevřený zdola ne absolutním odkazem na A16. Umožní mu rozšířit se, kdykoli zkopírujete níže uvedený vzorec.
Víme tedy, jak funguje funkce INDEX a MATCH. Hlavní část zde je:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Tento vzorec vrátí pole 1 s a 0 s. Kdykoli je hodnota v rozsahu $ E $ 1: E1 se nachází v seznamu kritérií $ A $ 2: A16, hodnota se převede na 1 na své pozici v rozsahu $ A $ 2: A16.
Nyní pomocí funkce MATCH hledáme hodnoty 0. Match vrátí pozici první 0 nalezené v poli vráceném funkcí COUNTIF. Poté se podívá INDEX A $ 2: A16 na návratovou hodnotu nalezenou v indexu vráceném funkcí MATCH.
Je to možná trochu těžké pochopit, ale funguje to. 0 na konci seznamu znamená, že neexistují žádné další jedinečné hodnoty. Pokud na konci nevidíte 0, měli byste vzorec zkopírovat do níže uvedených buněk.
Nyní se vyhnout #NA v můžete použít funkci IFERROR v Excelu.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Takže ano, tento vzorec můžete použít k získání jedinečných hodnot ze seznamu. V Excelu 2019 s předplatným Office 365 nabízí Microsoft funkci s názvem UNIQUE. Jednoduše vezme rozsah jako argument a vrátí řadu jedinečných hodnot. Není k dispozici v aplikaci Microsoft Excel 2016 při jednorázovém nákupu.
Stáhnout soubor:
Vzorec aplikace Excel k extrahování jedinečných hodnot ze seznamuVzorec aplikace Excel k extrahování jedinečných hodnot ze seznamu
Jak počítat jedinečné hodnoty v aplikaci Excel
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity
Jak používat funkci VLOOKUP v Excelu
Jak používat funkci COUNTIF v aplikaci Excel
Jak používat funkci SUMIF v aplikaci Excel