Excel

Extrahujte jedinečné položky zo zoznamu

Extract Unique Items From List

Vzorec programu Excel: Extrahujte jedinečné položky zo zoznamuGenerický vzorec | _+_ | Zhrnutie

Ak chcete zo zoznamu alebo stĺpca extrahovať iba jedinečné hodnoty, môžete použiť vzorec poľa na základe INDEX, MATCH a COUNTIF. V uvedenom príklade vzorec v D5, skopírovaný nadol, je:





{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}

kde „zoznam“ je pomenovaný rozsah B5: B11.

Poznámka: toto je an maticový vzorec a je potrebné ho zadať pomocou klávesovej skratky + shift + enter.





počet Excel, koľkokrát sa hodnota objaví v stĺpci
Vysvetlenie

Jadrom tohto vzorca je základné vyhľadávanie pomocou INDEX:

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

Inými slovami, zadajte INDEX zoznam a číslo riadka a INDEX získa hodnotu, ktorú chcete pridať do jedinečného zoznamu.



Tvrdá práca je zistiť číslo riadku, aby sa dal INDEX, aby sme získali iba jedinečné hodnoty. To sa deje pomocou MATCH a COUNTIF a hlavný trik je tu:

 
= INDEX (list,row)

Tu, C.OUNTIF spočíta, koľkokrát sa položky, ktoré sa už nachádzajú v jedinečnom zozname, zobrazia v hlavnom zozname pomocou rozširujúca referencia pre rozsah,$ D $ 4: D4.

Rozširujúcou sa referenciou je absolute na jednej strane, relatívne na druhej strane. V tomto prípade sa pri kopírovaní vzorca nadol referencia rozšíri a zahrnie do jedinečného zoznamu viac riadkov.

Všimnite siodkazzačína na D4,jeden riadok vyššie prvý jedinečný záznam v jedinečnom zozname. Je to zámerné - chceme počítať položky * už * do jedinečného zoznamu a nemôžeme zahrnúť aktuálnu bunku bez vytvorenia kruhovej referencie. Začneme teda v riadku vyššie.

Dôležité: uistite sa, že nadpis pre jedinečný zoznam sa nenachádza v hlavnom zozname.

Pre kritériá v COUNTIF používame samotný hlavný zoznam. Keď sú zadané viaceré kritériá, COUNTIF vráti viacero výsledkov vo formáte pole . V každom novom riadku máme iné pole, ako je toto:

 
 COUNTIF ($D:D4,list)

Poznámka: COUNTIF spracováva viacero kritérií so vzťahom „ALEBO“ (t.j. COUNTIF (rozsah, {'červená', 'modrá', 'zelená'}) počíta červenú, modrú alebo zelenú.

ako zaokrúhliš na najbližšie celé číslo

Teraz máme polia, ktoré potrebujeme na nájdenie pozícií (čísla riadkov). Na to používame MATCH, nastavený na presnú zhodu, na nájdenie nulových hodnôt. Ak vložíme polia vytvorené programom COUNTIF vyššie do MATCH, dostaneme toto:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

MATCH vyhľadáva položky tak, že hľadá počet nula (t.j. hľadá položky, ktoré sa ešte nenachádzajú v jedinečnom zozname). Funguje to, pretože MATCH vždy vráti prvý zápas, ak existujú duplikáty.

Nakoniec sa pozície vložia do INDEXu ako čísla riadkov a INDEX vráti meno na tejto pozícii.

Verzia bez poľa s LOOKUP

Môžete vytvoriť vzorec bez poľa na extrahovanie jedinečných položiek pomocou flexibilnej funkcie 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)

Konštrukcia vzorca je podobná vzorcu INDEX MATCH vyššie, ale LOOKUP dokáže operáciu poľa zvládnuť natívne.

  • COUNTIF vráti počty každej hodnoty zo „zoznamu“ v súbore rozšírenie sortimentu $ D $ 4: D4
  • Porovnaním s nulou sa vytvorí pole TRUE a FALSE hodnôt
  • Číslo 1 je vydelené poľom, čím vzniká pole 1 s a #chýb DIV/0
  • Toto pole sa stane lookup_vector vo vnútri LOOKUP
  • Hodnota vyhľadávania 2 je väčšia ako všetky hodnoty v poli lookup_vector
  • LOOKUP bude zodpovedať poslednej chybovej hodnote vo vyhľadávacom poli
  • LOOKUP vráti zodpovedajúcu hodnotu v result_vector, pomenovaný rozsah „zoznam“

Extrahujte položky, ktoré sa zobrazia iba raz

Vyššie uvedený vzorec LOOKUP je možné ľahko rozšíriť booleovská logika . Ak chcete extrahovať zoznam jedinečných položiek, ktoré sa v zdrojových dátach zobrazujú iba raz, môžete použiť nasledujúci vzorec:

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

Jediným doplnkom je druhý výraz COUNTIF:

počet pracovných dní v roku
 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Tu COUNTIF vracia pole počtu položiek takto:

 
 COUNTIF (list,list)=1

ktoré sú porovnané s 1, čo má za následok pole hodnôt TRUE/FALSE:

 
{2222212}

ktoré slúžia ako „filter“ na obmedzenie výstupu na položky, ktoré sa v zdrojových dátach vyskytujú iba raz.

V Excel 365 , JEDINEČNÁ funkcia je najlepší spôsob, ako extrahovať jedinečné hodnoty.

Autor Dave Bruns


^