Ok, takže v mojich počiatkoch analýzy údajov som potreboval automaticky získavať jedinečné položky v programe Excel zo zoznamu, a nie vždy odstraňovať duplikáty. A prišiel som na to, ako to urobiť. Potom bol môj excelový prístrojový panel ešte dynamickejší než predtým. Pozrime sa teda, ako to dokážeme…
Na extrahovanie zoznamu jedinečných hodnôt zo zoznamu použijeme INDEX, MATCH a COUNTIF. Budem tiež používať IFERROR, len aby som mal čisté výsledky, je voliteľný.
A áno, bude to maticový vzorec … Takže poďme na to …
Generický vzorec na extrahovanie jedinečných hodnôt v programe Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Zoznam odkazov: Zoznam, z ktorého chcete extrahovať jedinečné hodnoty
Expanding_output_range: Teraz je to veľmi dôležité. Toto je rozsah, v ktorom chcete vidieť svoj extrahovaný zoznam. Tento rozsah musí mať zreteľný nadpis, ktorý nie je súčasťou zoznamu, a váš vzorec bude pod nadpisom (ak je nadpis v E1, potom vzorec bude v E2).
Teraz rozšírenie znamená, že keď potiahnete nadol vzorec, mal by sa rozšíriť nad výstupný rozsah. Aby ste to urobili, musíte uviesť odkaz na nadpis ako $ E $ 1: E1 (Môj nadpis je v E1). Keď ho potiahnem nadol, roztiahne sa. V E2 to bude $ E $ 1: E1. V E3 to bude $ E $ 1: E2. V E2 to bude $ E $ 1: E3 a tak ďalej.
Teraz sa pozrime na príklad. Bude to jasné.
Extrahovanie jedinečných hodnôt Príklad programu Excel
Takže tu mám tento zoznam zákazníkov v stĺpci A v dosahu A2: A16. Teraz v stĺpci E chcem získať jedinečné hodnoty iba od zákazníkov. Teraz sa môže zvýšiť aj tento rozsah A2: A16, takže chcem, aby môj vzorec načítal zo zoznamu akékoľvek nové meno zákazníka, kedykoľvek sa zoznam zvýši.
Ok, teraz, aby ste získali jedinečné hodnoty zo stĺpca A, zadajte tento vzorec Bunka E2, a udrieť CTRL+SHIFT+ENTER aby to bol vzorec poľa.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
2 A $: A16: Očakávam, že sa tento zoznam rozšíri a môže mať nové jedinečné hodnoty, ktoré budem chcieť extrahovať. Preto som ho nechal otvorený zdola nie absolútnym odkazom na A16. Umožní mu to rozšíriť sa, kedykoľvek skopírujete nižšie uvedený vzorec.
Takže vieme, ako funguje funkcia INDEX a MATCH. Hlavná časť tu je:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Tento vzorec vráti pole 1 s a 0 s. Kedykoľvek je hodnota v rozsahu $ E $ 1: E1 sa nachádza v zozname kritérií $ A $ 2: A16, hodnota sa prevedie na 1 na svojej pozícii v rozsahu $ A $ 2: A16.
Teraz pomocou funkcie MATCH hľadáme hodnoty 0. Zhoda vráti pozíciu prvých 0 nájdených v poli vrátenom funkciou COUNTIF. Potom sa na to pozrie INDEX 2 doláre: A16 na návratovú hodnotu nájdenú v indexe vrátenom funkciou MATCH.
Je to možno trochu ťažké na pochopenie, ale funguje to. 0 na konci zoznamu znamená, že neexistujú žiadne ďalšie jedinečné hodnoty. Ak na konci nevidíte 0, mali by ste skopírovať vzorec do buniek nižšie.
Teraz sa vyhnúť #NA v môžete použiť funkciu IFERROR programu Excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Áno, tento vzorec môžete použiť na získanie jedinečných hodnôt zo zoznamu. V Exceli 2019 s predplatným Office 365 ponúka spoločnosť Microsoft funkciu s názvom UNIQUE. Jednoducho vezme ako argument rozsah a vráti pole jedinečných hodnôt. Nie je k dispozícii v programe Microsoft Excel 2016 pri jednorazovom nákupe.
Stiahnuť súbor:
Excel vzorec na extrahovanie jedinečných hodnôt zo zoznamuExcel vzorec na extrahovanie jedinečných hodnôt zo zoznamu
Ako počítať jedinečné hodnoty v programe Excel
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity
Ako používať funkciu VLOOKUP v programe Excel
Ako používať funkciu COUNTIF v programe Excel
Ako používať funkciu SUMIF v programe Excel