INDEX-MATCH s'ha convertit en una eina més popular per a Excel, ja que resol la limitació de la funció BUSCAR V, i és més fàcil d'utilitzar. La funció INDEX-MATCH a Excel té una sèrie d'avantatges respecte a la funció BUSCAR V:

1. INDEX i MATCH són més flexibles i ràpids que Vlookup
2. És possible executar la cerca horitzontal, la cerca vertical, la cerca bidireccional, la cerca a l'esquerra, la cerca sensible a majúscules i minúscules i fins i tot cerques basades en diversos criteris.
3. A les dades ordenades, INDEX-MATCH és un 30% més ràpid que VLOOKUP. Això vol dir que en un conjunt de dades més gran un 30% més ràpid té més sentit.

Comencem amb els conceptes detallats de cada INDEX i PARTIDA.

Funció INDEX

La funció INDEX d'Excel és molt potent alhora que una eina flexible que recupera el valor en una ubicació determinada d'un interval. En una altra paraula, retorna el contingut d'una cel·la, especificat per desplaçament de fila i columna.

Sintaxi:

=INDEX(reference, [row], [column])>

Paràmetres:

referència: la matriu de cel·les a desplaçar-se. Pot ser un únic rang o un conjunt de dades sencer en una taula de dades. fila [opcional]: el nombre de files desplaçades. Vol dir que si triem un rang de referència de taula com A1:A5, la cel·la/contingut que volem extreure es troba a quina distància vertical. Aquí, per a la fila A1 serà 1, per a la fila A2 = 2, i així successivament. Si donem fila = 4 aleshores extreu A4. Com que la fila és opcional, si no especifiquem cap número de fila, extreu files senceres a l'interval de referència. Això és A1 a A5 en aquest cas. column [opcional]: el nombre de columnes desplaçades. Vol dir que si triem un rang de referència de taula com A1:B5, la cel·la/contingut que volem extreure es troba a quina distància horitzontal. Aquí, per a la fila A1 serà 1 i la columna serà 1, per a la fila B1 serà 1, però la columna serà 2 de la mateixa manera per a la fila A2 = 2 columna = 1, per a la fila B2 = 2 columna = 2 i així successivament. Si donem fila = 5 i columna 2, s'extreurà B5. Com que la columna és opcional, si no especifiquem cap fila no. aleshores extreu tota la columna de l'interval de referència. Per exemple, si donem fila = 2 i columna com a buides, s'extreurà (A2:B2). Si no especifiquem Fila i columna, llavors extreu tota la taula de referència que és (A1:B5).

Taula de referència: La taula següent s'utilitzarà com a taula de referència per a tots els exemples de la funció INDEX. La primera cel·la es troba a B3 (ALIMENTACIÓ) i la darrera cel·la diagonal és a F10 (180).

Exemples: A continuació es mostren alguns exemples de funcions d'índex.

Cas 1: No s'esmenten files ni columnes.

Comandament d'entrada: =ÍNDEX(B3:C10)

Cas 2: Només s'esmenten les files.

Comandament d'entrada: =ÍNDEX(B3:C10;2)

Cas 3: S'esmenten tant les files com les columnes.

Comandament d'entrada: =ÍNDEX(B3:D10;4;2)

Cas 4: Només s'esmenten les columnes.

Ordre d'entrada: =ÍNDEX(B3 : D10 , , 2)

Problema amb la funció INDEX: El problema amb la funció INDEX és que cal especificar files i columnes per a les dades que busquem. Suposem que estem tractant amb un conjunt de dades d'aprenentatge automàtic de 10.000 files i columnes, llavors serà molt difícil cercar i extreure les dades que estem buscant. Aquí ve el concepte de funció de concordança, que identificarà files i columnes en funció d'alguna condició.

Funció MATCH

Recupera la posició d'un element/valor en un rang. És una versió menys refinada d'un VLOOKUP o HLOOKUP que només retorna la informació de la ubicació i no les dades reals. MATCH no distingeix entre majúscules i minúscules i no li importa si l'interval és horitzontal o vertical.

Sintaxi:

=MATCH(search_key, range, [search_type])>

Paràmetres:

search_key: el valor que cal cercar. Per exemple, 42, Gats o I24. rang: la matriu unidimensional que s'ha de cercar. Pot ser una sola fila o una sola columna. Per exemple->A1:A10, A2:D2, etc. search_type [opcional]: el mètode de cerca. = 1 (per defecte) troba el valor més gran inferior o igual a search_key quan l'interval s'ordena en ordre ascendent.
• = 0 troba el valor exacte quan l'interval no està ordenat.
• = -1 troba el valor més petit més gran o igual que cerca_clau quan l'interval s'ordena en ordre descendent.

El número de fila o el número de columna es pot trobar mitjançant la funció de concordança i es pot utilitzar dins de la funció d'índex, de manera que si hi ha algun detall sobre un element, es pot extreure tota la informació sobre l'element trobant la fila/columna de l'element mitjançant la coincidència. després niu-la a la funció d'índex.

Taula de referència: La taula següent s'utilitzarà com a taula de referència per a tots els exemples de la funció MATCH. La primera cel·la és a B3 (ALIMENTACIÓ) i l'última cel·la diagonal és a F10 (180)

Exemples: A continuació es mostren alguns exemples de la funció MATCH:

Cas 1: Tipus de cerca 0, vol dir concordança exacta.

Ordre d'entrada: =COINCIDENT(South Indian,C3:C10,0)

Cas 2: Tipus de cerca 1 (per defecte).

Comandament d'entrada: =COINCIDENT(South Indian,C3:C10)

multithreading en java

Cas 3: Tipus de cerca -1.

Comandament d'entrada: =COINCIDENT(South Indian,C3:C10,-1)

INDEX-MATCH Junts

En els exemples anteriors, els valors estàtics de files i columnes es van proporcionar a la funció INDEX Suposem que no hi ha coneixements previs sobre les files i la posició de les columnes, llavors les files i columnes es poden proporcionar mitjançant la funció MATCH. Aquesta és una manera dinàmica de cercar i extreure valor.

Sintaxi:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Taula de referència: S'utilitzarà la següent taula de referència. La primera cel·la és a B3 (ALIMENTACIÓ) i l'última cel·la diagonal és a F10 (180)

Exemple: Diguem que la tasca és trobar el cost de Masala Dosa. Se sap que la columna 3 representa el cost dels articles, però no es coneix la posició de la fila de Masala Dosa. El problema es pot dividir en dos passos:

Pas 1: Trobeu la posició de Masala Dosa utilitzant la fórmula:

 =MATCH('Masala Dosa',B3:B10,0)>

Aquí B3:B10 representa Column Food i 0 significa Coincidència exacta. Tornarà el número de fila de Masala Dosa.

Pas 2: Troba el cost de Masala Dosa. Utilitzeu la funció INDEX per trobar el cost de Masala Dosa. Substituint la consulta de funció MATCH anterior dins de la funció INDEX al lloc on es requereix la posició exacta de Masala Dosa i el número de columna de cost és 3 que ja es coneix.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Cerca de dues maneres amb INDEX-MATCH junts

A l'exemple anterior, la posició de la columna de l'atribut Cost es va codificar en dur. Per tant, no era totalment dinàmic.

Cas 1: Suposem que tampoc no es coneix el nombre de columna del cost, llavors es pot obtenir mitjançant la fórmula:

 =MATCH('Cost',B3:F3,0)>

Aquí B3:F3 representa la columna de capçalera.

Cas 2: Quan la fila, així com el valor de la columna, es proporcionen mitjançant la funció MATCH (sense donar valor estàtic), s'anomena cerca bidireccional. Es pot aconseguir mitjançant la fórmula:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Cerca a l'esquerra

Un dels avantatges clau d'INDEX i MATCH respecte a la funció VLOOKUP és la capacitat de realitzar una cerca a l'esquerra. Significa que és possible extreure la posició de la fila d'un element utilitzant qualsevol atribut a la dreta i es pot extreure el valor d'un altre atribut a l'esquerra.

Per exemple, suposem que compreu aliments el cost dels quals hauria de ser de 140 Rs. Indirectament estem dient que compreu Biryani. En aquest exemple, es coneix el cost de Rs 140/-, hi ha una necessitat d'extreure el menjar. Com que la columna Cost es troba a la dreta de la columna Aliments. Si s'aplica BUSCARV, no es podrà cercar al costat esquerre de la columna Cost. És per això que amb VLOOKUP no és possible obtenir el nom de l'aliment.

Per superar aquest desavantatge, la funció INDEX-MATCH es pot utilitzar la cerca a l'esquerra.
Pas 1: Primera posició de fila d'extracció de Cost 140 Rs mitjançant la fórmula:

 =MATCH(140, D3:D10,0)>

Aquí D3: D10 representa la columna Cost on s'està fent la cerca del número de fila Cost 140 Rs.

Pas 2: Després d'obtenir el número de fila, el següent pas és utilitzar la funció INDEX per extreure el nom de l'aliment mitjançant la fórmula:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Aquí B3:B10 representa la columna d'aliments i 140 és el cost de l'aliment.

Cerca sensible a majúscules i minúscules

Per si mateixa, la funció MATCH no distingeix entre majúscules i minúscules. Això vol dir que si hi ha un nom d'aliment DHOKLA i la funció MATCH s'utilitza amb la següent paraula de cerca:

1. Dhokla
2. dhokla
3. DhOkLA

Tot retornarà la posició de fila de DHOKLA. Tanmateix, la funció EXACTE es pot utilitzar amb INDEX i MATCH per fer una cerca que respecti les majúscules i les minúscules.

Funció exacta: La funció EXACTE d'Excel compara dues cadenes de text, tenint en compte els caràcters majúscules i minúscules, i retorna TRUE si són iguals i FALSE si no. EXACT distingeix entre majúscules i minúscules.

Exemples:

EXACT (DHOKLA,DHOKLA): Això tornarà True. EXACT(DHOKLA,Dhokla): Això tornarà False. EXACT(DHOKLA,dhokla): Això tornarà False. EXACT(DHOKLA,DhOkLA): Això tornarà False.

Exemple: Diguem que la tasca és cercar el tipus d'aliment Dhokla però de manera sensible a majúscules i minúscules. Això es pot fer mitjançant la fórmula-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Aquí la funció EXACTA retornarà True si el valor de la Columna B3:B10 coincideix amb Dhokla amb el mateix cas, en cas contrari tornarà False. Ara la funció MATCH s'aplicarà a la columna B3:B10 i cercarà una fila amb el valor exacte TRUE. Després d'això, la funció INDEX recuperarà el valor de la columna C3:C10 (columna del tipus d'aliment) a la fila que retorna la funció MATCH.

Cerca de criteris múltiples

Un dels problemes més complicats d'Excel és una cerca basada en diversos criteris. En altres paraules, una cerca que coincideixi amb més d'una columna alhora. A l'exemple següent, la funció INDEX i MATCH i la lògica booleana s'utilitzen per coincidir en 3 columnes:

1. Menjar.
2. Cost.
3. Quantitat.

Per extreure el cost total.

Exemple: Suposem que la tasca és calcular el cost total de Pasta on

Menjar: pasta. Cost: 60. Quantitat: 1.

Per tant, en aquest exemple, hi ha tres criteris per realitzar un Match. A continuació es mostren els passos per a la cerca basada en diversos criteris:

Pas 1: Primer coincideix la columna d'aliments (B3:B10) amb la pasta utilitzant la fórmula:

 'PASTA' = B3:B10>

Això convertirà els valors B3:B10 (columna d'aliments) com a booleans. Això és cert quan el menjar és pasta, sinó fals.

Pas 2: Després d'això, coincideixi amb els criteris de cost de la manera següent:

 60 = D3:D10>

Això substituirà els valors D3:D10 (Columna de cost) com a booleans. Això és vertader on Cost=60 sinó fals.

Pas 3: El següent pas és fer coincidir el tercer criteri que són Quantitat = 1 de la manera següent:

 1 = E3:E10>

Això substituirà la columna E3:E10 (columna de quantitat) com a Vertader, on Quantitat = 1, sinó serà Fals.

Pas 4: Multiplica el resultat del primer, segon i tercer criteri. Aquesta serà la intersecció de totes les condicions i convertirà Boolean True / False com a 1/0.

Pas 5: Ara el resultat serà una columna amb 0 i 1. Aquí utilitzeu la funció MATCH per trobar el nombre de fila de columnes que contenen 1. Perquè si una columna té el valor 1, vol dir que compleix els tres criteris.

Pas 6: Després d'obtenir el número de fila, utilitzeu la funció INDEX per obtenir el cost total d'aquesta fila.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Aquí F3:F10 representa la columna del cost total.