Excel

Izvlecite edinstvene predmete s seznama

Extract Unique Items From List

Formula Excel: izvlecite edinstvene elemente s seznamaSplošna formula | _+_ | Povzetek

Če želite iz seznama ali stolpca izvleči samo edinstvene vrednosti, lahko uporabite formulo matrike, ki temelji na INDEX, MATCH in COUNTIF. V prikazanem primeru je kopirana formula v D5:



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

kjer je 'seznam' imenovani obseg B5: B11.

Opomba: to je formula matrike in ga je treba vnesti s tipkama control + shift + enter.





excel preštejte, kolikokrat se vrednost prikaže v stolpcu
Pojasnilo

Jedro te formule je osnovno iskanje z INDEX:

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

Z drugimi besedami, dajte INDEX -u seznam in številko vrstice, INDEX pa bo pridobil vrednost, ki jo bo dodal edinstvenemu seznamu.



Trdo delo je ugotoviti številko ROW za INDEX, tako da dobimo samo edinstvene vrednosti. To se naredi z MATCH in COUNTIF, glavni trik pa je tukaj:

 
= INDEX (list,row)

Tukaj, C.OUNTIF šteje, kolikokrat se elementi, ki so že na edinstvenem seznamu, pojavijo na glavnem seznamu z uporabo razširitev referenc za domet,$ D $ 4: D4.

Razširljiva referenca je ab absolutno na eni strani, relativno na drugi. V tem primeru, ko se formula kopira, se bo sklic razširil in na edinstven seznam vključil več vrstic.

Upoštevajtesklicse začne v D4,eno vrstico zgoraj prvi edinstven vnos na edinstvenem seznamu. To je namerno - želimo šteti elemente *, ki so že * na edinstvenem seznamu, in trenutne celice ne moremo vključiti, ne da bi ustvarili krožno referenco. Torej, začnemo pri zgornji vrstici.

Pomembno: Prepričajte se, da naslov edinstvenega seznama ni na glavnem seznamu.

Za merila v COUNTIF uporabljamo sam glavni seznam. Če je podanih več meril, bo COUNTIF vrnil več rezultatov v matriko . V vsaki novi vrstici imamo drugačno matriko, kot je ta:

 
 COUNTIF ($D:D4,list)

Opomba: COUNTIF obravnava več meril z razmerjem 'OR' (tj. COUNTIF (obseg, {'rdeča', 'modra', 'zelena'}) šteje rdečo, modro ali zeleno.

uporabljati enotno obliko podčrtanega računovodstva

Zdaj imamo matrike, ki jih potrebujemo za iskanje položajev (številk vrstic). Za to uporabimo MATCH, nastavljeno za natančno ujemanje, za iskanje ničelnih vrednosti. Če matrike, ki jih je ustvaril COUNTIF zgoraj, postavimo v MATCH, dobimo naslednje:

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

MATCH najde elemente tako, da išče število nič (tj. Išče elemente, ki se še ne pojavijo na edinstvenem seznamu). To deluje, ker MATCH vedno vrne prvo ujemanje, ko so podvojeni.

Nazadnje se pozicije vnesejo v INDEX kot številke vrstic in INDEX vrne ime na tem mestu.

Različica brez matrike z LOOKUP

S prilagodljivo funkcijo LOOKUP lahko zgradite formulo brez matrike za ekstrahiranje edinstvenih elementov:

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

Konstrukcija formule je podobna formuli INDEX MATCH zgoraj, vendar LOOKUP lahko ročno upravlja z operacijo niza.

  • COUNTIF vrne štetje vsake vrednosti s seznama v širi obseg $ D $ 4: D4
  • Primerjava z nič ustvari niz vrednosti TRUE in FALSE
  • Številka 1 je razdeljena z matriko, pri čemer nastane niz napak 1s in #DIV/0
  • Ta matrika postane lookup_vector znotraj LOOKUP
  • Iskalna vrednost 2 je večja od vseh vrednosti v lookup_vector
  • LOOKUP se bo ujemal z zadnjo vrednostjo brez napak v iskalnem nizu
  • LOOKUP vrne ustrezno vrednost v result_vector, imenovani obseg 'list'

Izvlecite elemente, ki se pojavijo samo enkrat

Zgornjo formulo LOOKUP je enostavno razširiti logična logika . Če želite izvleči seznam edinstvenih elementov, ki se enkrat pojavijo v izvornih podatkih, lahko uporabite formulo, kot je ta:

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

Edini dodatek je drugi izraz COUNTIF:

kako izračunati irr na
 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Tu COUNTIF vrne niz števila elementov, kot je ta:

 
 COUNTIF (list,list)=1

ki so v primerjavi z 1, kar ima za posledico niz vrednosti TRUE/FALSE:

 
{2222212}

ki delujejo kot „filter“ za omejevanje izpisa na postavke, ki se v izvornih podatkih pojavijo samo enkrat.

V Excel 365 , UNIQUE funkcija je najboljši način za pridobivanje edinstvenih vrednot.

Avtor Dave Bruns


^