Vynikat

Extrahujte jedinečné položky ze seznamu

Extract Unique Items From List

Vzorec aplikace Excel: Extrahujte jedinečné položky ze seznamuObecný vzorec | _+_ | souhrn

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 Excel
Vysvě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


^