La funció INDEX es pot utilitzar sola, però si niu la funció MATCH dins es crea una cerca avançada.
Aquesta funció imbricada és més flexible que BUSCAR V i pot donar resultats més ràpidament.
Aquest article explica com utilitzar les funcions INDEX i COINCORDA juntes en totes les versions d'Excel, inclosos Excel 2019 i Microsoft 365.
Quines són les funcions INDEX i MATCH?
INDEX i MATCH són funcions de cerca d'Excel. Tot i que són dues funcions completament separades que es poden utilitzar soles, també es poden combinar per crear fórmules avançades.
La funció INDEX retorna un valor o la referència a un valor d'una selecció concreta. Per exemple, es pot utilitzar per trobar el valor a la segona fila d'un conjunt de dades o a la cinquena fila i la tercera columna.
Tot i que INDEX es pot utilitzar sol, nidificar MATCH a la fórmula el fa una mica més útil. La funció MATCH cerca un element especificat en un rang de cel·les i després retorna la posició relativa de l'element a l'interval. Per exemple, es podria utilitzar per determinar que un nom específic és el tercer element d'una llista de noms.
Image
INDEX i MATCH Sintaxi i arguments
Així és com s'han d'escriure ambdues funcions perquè Excel les entengui:
=INDEX (matriu, row_num, [column_num])
array és l'interval de cel·les que utilitzarà la fórmula. Pot ser una o més files i columnes, com ara A1:D5. És obligatori.
row_num és la fila de la matriu des de la qual es retorna un valor, com ara 2 o 18. És obligatori tret que hi hagi column_num.
column_num és la columna de la matriu des de la qual es retorna un valor, com ara 1 o 9. És opcional.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value és el valor que voleu fer coincidir a lookup_array. Pot ser un número, un text o un valor lògic que s'escriu manualment o es fa referència mitjançant una referència de cel·la. Això és obligatori.
lookup_array és l'interval de cel·les que cal mirar. Pot ser una sola fila o una sola columna, com ara A2:D2 o G1:G45. Això és obligatori.
match_type pot ser -1, 0 o 1. Especifica com es fa coincidir lookup_value amb els valors de lookup_array (vegeu més avall). 1 és el valor predeterminat si s'omet aquest argument.
Quin tipus de concordança utilitzar
Tipus de concordança
Què fa
Regla
Exemple
1
Troba el valor més gran que sigui inferior o igual a lookup_value.
Els valors de lookup_array s'han de col·locar en ordre ascendent (p. ex., -2, -1, 0, 1, 2; o A-Z; o FALSE, TRUE.
lookup_value és 25, però f alta a lookup_array, de manera que es retorna la posició del número més petit següent, com ara 22.
0
Troba el primer valor que és exactament igual a lookup_value.
Els valors de lookup_array poden estar en qualsevol ordre.
lookup_value és 25, de manera que retorna la posició de 25.
-1
Troba el valor més petit que és més gran o igual a lookup_value.
Els valors de lookup_array s'han de col·locar en ordre descendent (p. ex., 2, 1, 0, -1, -2).
lookup_value és 25, però f alta a lookup_array, de manera que es retorna la posició del següent nombre més gran, com ara 34.
Feu servir 1 o -1 per als moments en què necessiteu fer una cerca aproximada al llarg d'una escala, com ara quan tracteu amb números i quan les aproximacions siguin correctes. Però recordeu que si no especifiqueu match_type, 1 serà el valor predeterminat, cosa que pot alterar els resultats si realment voleu una coincidència exacta.
Exemple de fórmules INDEX i MATCH
Abans de veure com combinar INDEX i MATCH en una sola fórmula, hem d'entendre com funcionen aquestes funcions per si soles.
INDEX Exemples
=ÍNDEX(A1:B2, 2, 2)
=ÍNDEX (A1:B1, 1)
=ÍNDEX (2:2, 1)=ÍNDEX(B1:B2, 1)
Image
En aquest primer exemple, hi ha quatre fórmules INDEX que podem utilitzar per obtenir diferents valors:
=INDEX(A1:B2, 2, 2) mira A1:B2 per trobar el valor a la segona columna i a la segona fila, que és Stacy.
=INDEX(A1:B1, 1) mira A1:B1 per trobar el valor a la primera columna, que és Jon.
=INDEX(2:2, 1) mira tot el que hi ha a la segona fila per localitzar el valor de la primera columna, que és Tim.
=INDEX(B1:B2, 1) mira a través de B1:B2 per localitzar el valor a la primera fila, que és Amy.
Exemples de MATCH
=PARTIDA("Stacy", A2:D2, 0)
=PARTIDA (14, D1:D2)
=PARTIDA (14, D1:D2, -1)=PARTIDA (13, A1:D1, 0)
Image
Aquí hi ha quatre exemples senzills de la funció MATCH:
=MATCH("Stacy", A2:D2, 0) està cercant Stacy a l'interval A2:D2 i retorna 3 com a resultat.
=MATCH(14, D1:D2) està cercant 14 a l'interval D1:D2, però com que no es troba a la taula, MATCH troba el següent valor més gran això és menor o igual que 14, que en aquest cas és 13, que es troba a la posició 1 de lookup_array.
=MATCH(14, D1:D2, -1) és idèntica a la fórmula que hi ha a sobre, però com que la matriu no està en ordre descendent com ho requereix -1, tenim un error.
=MATCH(13, A1:D1, 0) cerca 13 a la primera fila del full, que retorna 4 ja que és el quart element d'aquesta matriu.
Exemples d'INDEX-MATCH
Aquí hi ha dos exemples on podem combinar INDEX i MATCH en una fórmula:
Trobar la referència de cel·les a la taula
=ÍNDEX(B2:B5, PARTIDA(F1, A2:A5))
Image
Aquest exemple està nidant la fórmula COINCIDENT dins de la fórmula INDEX. L'objectiu és identificar el color de l'element mitjançant el número d'article.
Si mireu la imatge, podeu veure a les files "Separades" com s'escriurien les fórmules soles, però com que les estem nidificant, això és el que està passant:
MATCH(F1, A2:A5) està buscant el valor F1 (8795) al conjunt de dades A2:A5. Si fem el compte enrere de la columna, podem veure que és 2, de manera que això és el que acaba de descobrir la funció COINCORD.
La matriu INDEX és B2:B5, ja que finalment busquem el valor d'aquesta columna.
La funció INDEX ara es podria reescriure així, ja que 2 és el que ha trobat MATCH: INDEX(B2:B5, 2, [column_num]).
Atès que column_num és opcional, podem eliminar-ho per deixar-ho amb això: INDEX(B2:B5, 2).
Així ara, això és com una fórmula d'INDEX normal on estem trobant el valor del segon element a B2:B5, que és vermell.
En aquest exemple de MATCH i INDEX, estem fent una cerca bidireccional. La idea és veure quants diners vam guanyar amb articles ecològics al maig. Això és molt semblant a l'exemple anterior, però hi ha una fórmula MATCH addicional imbricada a INDEX.
MATCH(G1, A2:A13, 0) és el primer ítem resolt en aquesta fórmula. Està buscant G1 (la paraula "maig") a A2:A13 per obtenir un valor particular. No el veiem aquí, però són 5.
MATCH(G2, B1:E1, 0) és la segona fórmula MATCH, i és molt semblant a la primera, però busca G2 (la paraula "verd") als encapçalaments de columnes a B1:E1. Aquest es resol en 3.
Ara podem reescriure la fórmula INDEX com aquesta per visualitzar què està passant: =INDEX(B2:E13, 5, 3). S'està buscant a tota la taula, B2:E13, la cinquena fila i la tercera columna, que retornen 180 $.
Règles de PARTIDA i INDEX
Hi ha diverses coses a tenir en compte a l'hora d'escriure fórmules amb aquestes funcions:
MATCH no distingeix entre majúscules i minúscules, de manera que les majúscules i les minúscules es tracten de la mateixa manera quan coincideixen amb valors de text.
MATCH retorna N/A per diversos motius: si match_type és 0 i lookup_value no es troba si match_type és -1 i lookup_array no està en ordre descendent, si match_type és 1 i lookup_array no és ascendent ordre, i si lookup_array no és una sola fila o columna.
Podeu utilitzar un caràcter comodí a l'argument lookup_value si coincidència és 0 i lookup_value és una cadena de text. Un signe d'interrogació coincideix amb qualsevol caràcter i un asterisc coincideix amb qualsevol seqüència de caràcters (p.p. ex., =COMPARACIÓ("Jo", 1:1, 0)). Per utilitzar MATCH per trobar un signe d'interrogació o un asterisc real, escriviu ~ primer.
INDEX retorna REF! si row_num i column_num no apunten a una cel·la dins de la matriu.
Funcions d'Excel relacionades
La funció COINCIDIR és similar a BUSCAR, però COINCIDIR retorna la posició de l'element en lloc de l'element en si.
VLOOKUP és una altra funció de cerca que podeu utilitzar a Excel, però a diferència de MATCH que requereix INDEX per a cerques avançades, les fórmules VLOOKUP només necessiten aquesta funció.
La funció ISBLANK de Microsoft Excel pot ser una gran eina per trobar forats a la vostra base de dades. A continuació s'explica com utilitzar-lo al màxim, amb format condicional i no
Microsoft Excel ofereix funcions no només per a números, sinó també per a text. La funció Excel MID us ajudarà a obtenir text del contingut de la meitat de les cel·les. A continuació s'explica com utilitzar-lo
La funció INDEX d'Excel és una fórmula que agafa un valor d'una llista o taula en funció de les dades d'ubicació que introduïu a la fórmula. A continuació s'explica com utilitzar la fórmula INDEX, juntament amb exemples
Utilitzeu la funció MATCH d'Excel per trobar la primera posició relativa de valors específics en una llista o taula de dades. Actualitzat per incloure Excel 2019
