Formula generică
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
rezumat
Pentru a extrage numai valori unice dintr-o listă sau coloană, puteți utiliza o formulă matrice bazată pe INDEX, MATCH și COUNTIF. În exemplul prezentat, formula din D5, copiată, este:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
unde „listă” este intervalul numit B5: B11.
Notă: aceasta este o formulă matrice și trebuie introdusă folosind control + shift + enter.
Explicaţie
Nucleul acestei formule este o căutare de bază cu INDEX:
=INDEX(list,row)
Cu alte cuvinte, dați INDEX lista și un număr de rând, iar INDEX va prelua o valoare pe care să o adăugați la lista unică.
Munca grea constă în a afla numărul ROW pentru a da INDEX, astfel încât să obținem numai valori unice. Acest lucru se face cu MATCH și COUNTIF, iar principalul truc este aici:
COUNTIF($D$4:D4,list)
Aici, COUNTIF calculează de câte ori apar elemente deja în lista unică în lista master, utilizând o referință extinsă pentru interval, $ D $ 4: D4.
O referință în expansiune este absolută pe de o parte, relativă pe de altă parte. În acest caz, pe măsură ce formula este copiată, referința se va extinde pentru a include mai multe rânduri în lista unică.
Rețineți că referința începe în D4, un rând deasupra primei intrări unice, în lista unică. Acest lucru este intenționat - vrem să numărăm elementele * deja * în lista unică și nu putem include celula curentă fără a crea o referință circulară. Deci, începem pe rândul de mai sus.
Important: asigurați-vă că titlul pentru lista unică nu apare în lista master.
Pentru criteriile din COUNTIF, utilizăm lista master în sine. Atunci când sunt date mai multe criterii, COUNTIF va returna mai multe rezultate într-o matrice. La fiecare rând nou, avem o matrice diferită ca aceasta:
(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
Notă: COUNTIF gestionează mai multe criterii cu o relație „SAU” (adică COUNTIF (interval, („roșu”, „albastru”, „verde”)) contează roșu, albastru sau verde.
Acum avem matricele de care avem nevoie pentru a găsi poziții (numere de rând). Pentru aceasta, folosim MATCH, configurat pentru potrivirea exactă, pentru a găsi valori zero. Dacă punem matricile create de COUNTIF mai sus în MATCH, iată ce obținem:
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 localizează articole căutând un număr de zero (adică căutând articole care nu apar încă în lista unică). Acest lucru funcționează, deoarece MATCH returnează întotdeauna prima potrivire atunci când există duplicate.
În cele din urmă, pozițiile sunt introduse în INDEX ca numere de rând, iar INDEX returnează numele la acea poziție.
Versiune non-array cu LOOKUP
Puteți construi o formulă non-matrice pentru a extrage elemente unice utilizând funcția de căutare flexibilă:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Construcția formulei este similară cu formula INDEX MATCH de mai sus, dar LOOKUP poate gestiona operațiunea matrice în mod nativ.
- COUNTIF returnează numărul fiecărei valori din „listă” în intervalul extins $ D $ 4: D4
- Compararea cu zero creează o serie de valori ADEVĂRATE și FALSE
- Numărul 1 este împărțit la matrice, creând o matrice de 1 și erori # DIV / 0
- Această matrice devine vectorul de căutare din CAUTARE
- Valoarea căutării 2 este mai mare decât orice valori din căutare_vector
- LOOKUP se va potrivi cu ultima valoare fără eroare din matricea de căutare
- LOOKUP returnează valoarea corespunzătoare în result_vector, intervalul numit „listă”
Extrageți elemente care apar doar o singură dată
Formula LOOKUP de mai sus este ușor de extins cu logică booleană. Pentru a extrage o listă de elemente unice care apar o singură dată în datele sursă, puteți utiliza o formulă de genul acesta:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Singura adăugare este a doua expresie COUNTIF:
COUNTIF(list,list)=1
Aici, COUNTIF returnează o serie de numărări de articole astfel:
(2;2;2;2;2;1;2)
care sunt comparate cu 1, rezultând o serie de valori ADEVĂRATE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
care acționează ca un "filtru" pentru a restricționa ieșirea la elementele care apar doar o dată în datele sursă.
