Excela

Extrageți elemente unice dintr-o listă

Extract Unique Items From List

Formula Excel: extrageți elemente unice dintr-o listă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:D4,list),0))}

unde „listă” este gama denumită B5: B11.

Notă: acesta este un formula matrice și trebuie introdus folosind control + shift + enter.





cum se calculează procentul de marjă în Excel
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 trucul principal este aici:

 
 COUNTIF ($D:D4,list)

Aici, COUNTIF contează de câte ori apar elemente deja în lista unică în lista master, folosind un extinderea referinței pentru gama,$ D $ 4: D4.

O referință în expansiune este obsolut pe o parte, relativ pe cealaltă. Î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țireferinţăîncepe în D4,un rând de mai sus prima intrare unică, din 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 principală.

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-un matrice . La fiecare rând nou, avem o matrice diferită ca aceasta:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // 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.

dacă data este între două date excel

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,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},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 în acea poziție.

Versiune non-array cu LOOKUP

Puteți crea o formulă non-matrice pentru a extrage elemente unice utilizând funcția de căutare flexibilă:

 
= LOOKUP (2,1/( COUNTIF ($D: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ă” din gama 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:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Singura adăugare este a doua expresie COUNTIF:

formula adevărată sau falsă în excel
 
 COUNTIF (list,list)=1

Aici, COUNTIF returnează o serie de numărări de articole astfel:

 
{2222212}

care sunt comparate cu 1, rezultând o serie de valori ADEVĂRATE / FALSE:

 
{FALSEFALSEFALSEFALSEFALSETRUEFALSE}

care acționează ca un „filtru” pentru a restricționa ieșirea la elementele care apar doar o dată în datele sursă.

În Excel 365 , Funcția UNICĂ este cel mai bun mod de a extrage valori unice.

Autor Dave Bruns


^