Chcete -li ze seznamu nebo sloupce extrahovat pouze jedinečné hodnoty, můžete použít vzorec pole na základě INDEX, MATCH a COUNTIF. V uvedeném příkladu vzorec v D5, zkopírovaný dolů, je:
{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}
kde 'seznam' je pojmenovaný rozsah B5: B11.
Poznámka: toto je maticový vzorec a musí být zadáno pomocí Ctrl + Shift + Enter.
jak přidat celkový řádek v aplikaci ExcelVysvětlení
Jádrem tohoto vzorce je základní vyhledávání pomocí INDEX:
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}
Jinými slovy, dejte INDEX seznam a číslo řádku a INDEX načte hodnotu, kterou chcete přidat do jedinečného seznamu.
Těžká práce je zjistit číslo ŘÁDKU pro INDEX, abychom získali pouze jedinečné hodnoty. To se provádí pomocí MATCH a COUNTIF a hlavní trik je zde:
= INDEX (list,row)
Tady, C.OUNTIF počítá, kolikrát se položky, které se již nacházejí v unikátním seznamu, objeví v hlavním seznamu pomocí rozšiřující odkaz pro rozsah,$ D $ 4: D4.
Rozšiřující reference je absolute na jedné straně, relativní na straně druhé. V takovém případě se při kopírování vzorce odkaz rozbalí a zahrne do jedinečného seznamu více řádků.
Všimněte siodkazzačíná v D4,jedna řada výše první jedinečný záznam v jedinečném seznamu. Toto 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é reference. Začneme tedy na řádku výše.
Důležité: ujistěte se, že nadpis pro jedinečný seznam se v hlavním seznamu neobjeví.
Pro kritéria v COUNTIF používáme samotný hlavní seznam. Pokud je zadáno více kritérií, COUNTIF vrátí více výsledků ve formátu pole . V každém novém řádku máme jiné pole, jako je toto:
COUNTIF ($D:D4,list)
Poznámka: COUNTIF zpracovává více kritérií se vztahem „NEBO“ (tj. COUNTIF (rozsah, {'červená', 'modrá', 'zelená'}) počítá červenou, modrou nebo zelenou.
jaké jsou relativní odkazy v aplikaci Excel
Nyní máme pole, která potřebujeme k nalezení pozic (čísla řádků). K nalezení nulových hodnot používáme MATCH, nastavený pro přesnou shodu. Pokud vložíme pole vytvořená pomocí COUNTIF výše do MATCH, získáme následující:
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8
MATCH vyhledává položky hledáním počtu nula (tj. Hledáním položek, které se ještě nenacházejí v unikátním seznamu). To funguje, protože MATCH vždy vrátí první shodu, pokud existují duplikáty.
Nakonec jsou pozice vloženy do INDEX jako čísla řádků a INDEX vrátí jméno na této pozici.
Verze bez pole s LOOKUP
Můžete vytvořit vzorec bez pole k extrahování jedinečných položek pomocí flexibilní funkce LOOKUP:
MATCH (0,{0000000},0) // 1 (Joe) MATCH (0,{1000100},0) // 2 (Bob) MATCH (0,{1100101},0) // 3 (Sue) MATCH (0,{1111101},0) // 6 (Aya)
Konstrukce vzorce je podobná výše uvedenému vzorci INDEX MATCH, ale LOOKUP může operaci pole zvládnout nativně.
- COUNTIF vrací počty každé hodnoty ze „seznamu“ v rozšiřování dosahu $ D $ 4: D4
- Porovnáním s nulou se vytvoří pole SKUTEČNÝCH a NEPRAVDIVÝCH hodnot
- Číslo 1 je děleno polem, což vytváří pole 1s a #DIV/0 chyb
- Toto pole se stane lookup_vector uvnitř LOOKUP
- Vyhledávací hodnota 2 je větší než jakékoli hodnoty v lookup_vector
- LOOKUP bude odpovídat poslední hodnotě bez chyby ve vyhledávacím poli
- LOOKUP vrátí odpovídající hodnotu v result_vector, pojmenovaný rozsah 'list'
Extrahujte položky, které se zobrazí pouze jednou
Výše uvedený vzorec LOOKUP lze snadno rozšířit pomocí booleovská logika . Chcete -li extrahovat seznam jedinečných položek, které se ve zdrojových datech zobrazují pouze jednou, můžete použít vzorec, jako je tento:
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)
Jediným přírůstkem je druhý výraz COUNTIF:
jak používat funkci irr v aplikaci Excel
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)
Zde COUNTIF vrací řadu počtů položek takto:
COUNTIF (list,list)=1
které jsou porovnány s 1, což má za následek řadu hodnot PRAVDA/NEPRAVDA:
{2222212}
které fungují jako „filtr“ k omezení výstupu na položky, které se ve zdrojových datech vyskytují pouze jednou.
v Excel 365 , JEDINEČNÁ funkce je nejlepší způsob, jak extrahovat jedinečné hodnoty.
Autor Dave Bruns