Extraieu elements únics d'una llista

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

Per extreure només valors únics d'una llista o columna, podeu utilitzar una fórmula de matriu basada en INDEX, MATCH i COUNTIF. A l'exemple que es mostra, la fórmula de D5, copiada, és:

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

on 'llista' és el rang anomenat B5: B11.

Nota: es tracta d'un fitxer fórmula de matriu i s'ha d'introduir mitjançant control + shift + enter.

excel genera un nombre aleatori entre dos valors

El nucli d'aquesta fórmula és una cerca bàsica amb INDEX:

 
= INDEX (list,row)

En altres paraules, doneu a INDEX la llista i un número de fila, i INDEX recuperarà un valor per afegir a la llista única.

El treball dur consisteix a esbrinar el número de FILA per donar INDEX, de manera que només obtinguem valors únics. Això es fa amb MATCH i COUNTIF, i el truc principal és aquí:

 
 COUNTIF ($D:D4,list)

Aquí, COUNTIF compta el nombre de vegades que apareixen elements de la llista única a la llista principal mitjançant un fitxer ampliació de referència per a la gamma,$ D $ 4: D4.

Una referència en expansió ésbsolut per una banda, relatiu per l’altra. En aquest cas, a mesura que es copia la fórmula, la referència s'ampliarà per incloure més files a la llista única.

Tingueu en compte el fitxerreferènciacomença a D4,una fila a sobre la primera entrada única, a la llista única. Això és intencionat: volem comptar els elements * ja * a la llista única i no podem incloure la cel·la actual sense crear una referència circular. Comencem, doncs, per la fila superior.

Important: assegureu-vos que l'encapçalament de la llista única no aparegui a la llista mestra.

Per als criteris de COUNTIF, utilitzem la llista principal. Quan es donen diversos criteris, COUNTIF retornarà diversos resultats en un fitxer matriu . A cada fila nova, tenim una matriu diferent com aquesta:

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

Nota: COUNTIF gestiona diversos criteris amb una relació 'O' (és a dir, COUNTIF (rang, {'vermell', 'blau', 'verd')) compta amb vermell, blau o verd.

com anomenem referència a una cel·la d’un altre full?

Ara tenim les matrius que necessitem per trobar posicions (números de fila). Per a això, fem servir MATCH, configurat per a la concordança exacta, per trobar zero valors. Si posem les matrius creades per COUNTIF més amunt a MATCH, això és el que obtenim:

 
 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 localitza elements cercant un recompte de zero (és a dir, cercant articles que encara no apareixen a la llista única). Això funciona, perquè MATCH sempre retorna la primera coincidència quan hi ha duplicats.

Finalment, les posicions s’introdueixen a INDEX com a números de fila i INDEX retorna el nom en aquesta posició.

Versió no array amb LOOKUP

Podeu crear una fórmula que no sigui una matriu per extreure elements únics mitjançant la funció de cerca flexible:

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

La construcció de la fórmula és similar a la fórmula INDEX MATCH anterior, però LOOKUP pot gestionar l'operació de matriu de forma nativa.

• COUNTIF retorna els recomptes de cada valor de la llista a la llista ampliant el rang $ D $ 4: D4
• En comparar amb zero es crea una matriu de valors TRUE i FALSE
• El número 1 es divideix per la matriu, creant una matriu d’1s i errors # DIV / 0
• Aquesta matriu es converteix en el vector de cerca dins de LOOKUP
• El valor de cerca de 2 és més gran que qualsevol valor del lookup_vector
• LOOKUP coincidirà amb l'últim valor de no error de la matriu de cerca
• LOOKUP retorna el valor corresponent a result_vector, l'interval anomenat 'llista'

Extraieu els elements que apareixen només una vegada

La fórmula LOOKUP anterior és fàcil d’ampliar lògica booleana . Per extreure una llista d'elements únics que apareixen una sola vegada a les dades d'origen, podeu utilitzar una fórmula com aquesta:

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

L'única addició és la segona expressió COUNTIF:

com s'utilitza la fórmula de l'índex a Excel

 
 COUNTIF (list,list)=1

Aquí, COUNTIF retorna una sèrie de recomptes d'elements com aquest:

 
{2222212}

que es comparen amb 1, donant lloc a una matriu de valors TRUE / FALSE:

 
{FALSEFALSEFALSEFALSEFALSETRUEFALSE}

que actuen com a 'filtre' per restringir la sortida als elements que es produeixen només una vegada a les dades d'origen.

En Excel 365 , el Funció ÚNICA és la millor manera d’extreure valors únics.