Obecný vzorec
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
souhrn
Chcete-li ze seznamu nebo sloupce extrahovat pouze jedinečné hodnoty, můžete použít maticový vzorec založený na INDEX, MATCH a COUNTIF. V zobrazeném příkladu je vzorec v D5, zkopírovaný dolů, následující:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kde „list“ je pojmenovaný rozsah B5: B11.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Jádrem tohoto vzorce je základní vyhledávání pomocí INDEXU:
=INDEX(list,row)
Jinými slovy, uveďte INDEX seznam a číslo řádku a INDEX načte hodnotu, kterou přidá do jedinečného seznamu.
Tvrdá práce je zjistit číslo ROW, které dá INDEX, abychom dostali pouze jedinečné hodnoty. To se provádí pomocí MATCH a COUNTIF a hlavní trik je zde:
COUNTIF($D$4:D4,list)
Zde COUNTIF spočítá, kolikrát se položky, které se již nacházejí v jedinečném seznamu, objeví v hlavním seznamu, pomocí rozšiřující reference rozsahu, $ D $ 4: D4.
Rozšiřující se reference je na jedné straně absolutní, na druhé relativní. V tomto případě, jak je vzorec zkopírován dolů, se odkaz rozbalí a zahrne více řádků do jedinečného seznamu.
Všimněte si, že reference začíná v D4, jeden řádek nad první jedinečnou položkou, v jedinečném seznamu. To je záměrné - chceme počítat položky * již * do jedinečného seznamu a nemůžeme zahrnout aktuální buňku bez vytvoření kruhového odkazu. Začneme tedy na řádku výše.
Důležité: ujistěte se, že nadpis jedinečného seznamu se neobjeví v hlavním seznamu.
Pro kritéria v COUNTIF používáme samotný hlavní seznam. Pokud je zadáno více kritérií, vrátí funkce COUNTIF více výsledků v poli. Na každém novém řádku máme jiné pole, jako je toto:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Poznámka: COUNTIF zpracovává více kritérií se vztahem „NEBO“ (tj. COUNTIF (rozsah, („červená“, „modrá“, „zelená“)) počítá červenou, modrou nebo zelenou.
Nyní máme pole, která potřebujeme k nalezení pozic (čísel řádků). K tomu používáme MATCH, nastavený na přesnou shodu, k nalezení nulových hodnot. Pokud dáme pole vytvořená výše COUNTIF do MATCH, dostaneme toto:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH vyhledává položky hledáním počtu nula (tj. Hledáním položek, které se dosud neobjevují v jedinečném seznamu). To funguje, protože MATCH vždy vrátí první shodu, když existují duplikáty.
Nakonec jsou pozice přiváděny do INDEXU jako čísla řádků a INDEX vrací název na dané pozici.
Verze bez pole s VYHLEDÁVÁNÍM
Pomocí flexibilní funkce VYHLEDÁVÁNÍ můžete vytvořit vzorec bez pole, pomocí kterého můžete extrahovat jedinečné položky:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konstrukce vzorce je podobná výše uvedenému vzorci INDEX MATCH, ale LOOKUP může operaci pole zpracovat nativně.
- Funkce COUNTIF vrátí počty jednotlivých hodnot ze seznamu v rozšiřujícím se rozsahu $ D $ 4: D4
- Porovnáním s nulou se vytvoří pole TRUE a FALSE hodnot
- Číslo 1 je vyděleno polem, čímž se vytvoří pole 1 s a # DIV / 0 chyb
- Toto pole se stává vyhledávacím vektorem uvnitř LOOKUP
- Vyhledávací hodnota 2 je větší než jakékoli hodnoty v lookup_vector
- LOOKUP bude odpovídat poslední bezchybné hodnotě ve vyhledávacím poli
- LOOKUP vrací odpovídající hodnotu v result_vector, pojmenovaný rozsah "list"
Extrahujte položky, které se objeví jen jednou
Výše uvedený vzorec LOOKUP lze snadno rozšířit pomocí logické logiky. Chcete-li extrahovat seznam jedinečných položek, které se ve zdrojových datech objeví jen jednou, můžete použít vzorec takto:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Jediným doplňkem je druhý výraz COUNTIF:
COUNTIF(list,list)=1
Zde COUNTIF vrací řadu počtů položek takto:
(2;2;2;2;2;1;2)
které jsou porovnány s 1, což má za následek pole hodnot TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
které fungují jako „filtr“ k omezení výstupu na položky, které se ve zdrojových datech vyskytnou pouze jednou.
