Podeu utilitzar la funció COINCIDENT d'Excel per trobar la posició relativa d'un valor específic en un rang de cel·les o una matriu.
La funció MATCH és similar a la funció BUSCAR V, ja que totes dues es classifiquen a Funcions de cerca/referència d'Excel. BUSCARV cerca un valor específic en una columna i retorna un valor a la mateixa fila mentre que la funció COINCORD cerca un valor determinat en un interval i retorna la posició d'aquest valor.
La funció COINCIDENT d'Excel cerca un valor especificat en un rang de cel·les o una matriu i retorna la posició relativa de la primera aparició d'aquest valor a l'interval. La funció MATCH també es pot utilitzar per buscar un valor determinat i retornar el seu valor corresponent amb l'ajuda de la funció INDEX (igual que Vlookup). Vegem com utilitzar la funció MATCH d'Excel per trobar la posició d'un valor de cerca en un rang de cel·les.
Funció Excel MATCH
La funció MATCH és una funció integrada a Excel i s'utilitza principalment per localitzar la posició relativa d'un valor de cerca en una columna o una fila.
Sintaxi de la funció MATCH:
=COINCIDENT(valor_de_cerca,matriu_de_cerca,[tipus_de_coincidència})On:
valor_de_cerca - El valor que voleu cercar en un interval especificat de cel·les o en una matriu. Pot ser un valor numèric, un valor de text, un valor lògic o una referència de cel·la que tingui un valor.
matriu_cerques – Les matrius de cel·les en què esteu cercant un valor. Ha de ser una sola columna o una sola fila.
tipus_concordança – És un paràmetre opcional que es pot establir en 0,1 o -1 i el valor predeterminat és 1.
- 0 cerca una coincidència exacta, quan no es troba, retorna un error.
- -1 cerca el valor més petit que és més gran o igual que lookup_value quan la matriu de cerca en ordre ascendent.
- 1 cerca el valor més gran que és menor o igual que el valor look_up quan la matriu de cerca en ordre descendent.
Trobar la posició d'una coincidència exacta
Suposem que tenim el següent conjunt de dades on volem trobar la posició d'un determinat valor.
En aquesta taula, volem trobar la posició d'un nom de ciutat (Memphis) a la columna (A2:A23), de manera que fem servir aquesta fórmula:
=COINCIDENT("memphis",A2:A23,0)El tercer argument s'estableix a '0' perquè volem trobar una coincidència exacta del nom de la ciutat. Com podeu veure que el nom de la ciutat "memphis" a la fórmula està en minúscula mentre que a la taula la primera lletra del nom de la ciutat està en majúscula (Memphis). Tot i així, la fórmula és capaç de trobar la posició del valor especificat en l'interval donat. És perquè la funció MATCH no distingeix entre majúscules i minúscules.
Nota: Si el valor_de_cerca no es troba a l'interval de cerca o si especifiqueu l'interval de cerca incorrecte, la funció retornarà l'error #N/A.
Podeu utilitzar una referència de cel·la al primer argument de la funció en lloc d'un valor directe. La fórmula següent troba la posició del valor a la cel·la F2 i retorna el resultat a la cel·la F3.
Trobeu la posició d'una coincidència aproximada
Hi ha dues maneres de buscar una coincidència aproximada o exacta del valor de cerca i retornar la seva posició.
- Una manera és trobar el valor més petit que sigui més gran o igual (la següent coincidència més gran) al valor especificat. Es pot aconseguir establint l'últim argument (match_type) de la funció com a "-1"
- Una altra manera és el valor més gran que sigui menor o igual (la següent coincidència més petita) al valor donat. Es pot aconseguir establint el match_type de la funció com a "1"
Següent partit més petit
Si la funció no pot trobar una coincidència exacta amb el valor especificat quan el tipus de concordança s'estableix a '1', localitza el valor més gran que és lleugerament inferior al valor especificat (el que significa el següent valor més petit) i retorna la seva posició. . Perquè això funcioni, cal ordenar la matriu en ordre ascendent, si no, es produirà un error.
A l'exemple, utilitzem la fórmula següent per trobar la següent coincidència més petita:
=COINCIDENT(F2;D2:D23;1)Quan aquesta fórmula no ha pogut trobar la coincidència exacta per al valor de la cel·la F2, apunta a la posició (16) del valor més petit següent, és a dir, 98.
Següent partit més gran
Quan el tipus de concordança s'estableix en "-1" i la funció MATCH no pot trobar una coincidència exacta, troba el valor més petit que és més gran que el valor especificat (és a dir, el següent valor més gran) i retorna la seva posició. La matriu de cerca s'ha d'ordenar en ordre descendent per a aquest mètode, en cas contrari, retornarà un error.
Per exemple, introduïu la fórmula següent per trobar la següent coincidència més gran amb el valor de cerca:
=COINCIDENT(F2;D2:D23;-1)Aquesta funció MATCH cerca el valor de F2 (55) a l'interval de cerca D2:D23, i quan no pot trobar la coincidència exacta, retorna la posició (16) del següent valor més gran, és a dir, 58.
Coincidència de comodins
Els comodins només es poden utilitzar a la funció MATCH quan match_type s'estableix a "0" i el valor de cerca és una cadena de text. Hi ha comodins que podeu utilitzar a la funció MATCH: un asterisc (*) i un signe d'interrogació (?).
- Signe d'interrogació (?) s'utilitza per fer coincidir qualsevol caràcter o lletra amb la cadena de text.
- Asterisc (*) s'utilitza per fer coincidir qualsevol nombre de caràcters amb la cadena.
Per exemple, hem utilitzat dos comodins '?' al valor_de_cerca (Lo??n) de la funció MATCH per trobar un valor que coincideixi amb la cadena de text amb dos caràcters qualsevol (en els llocs dels comodins). I la funció retorna la posició relativa del valor coincident a la cel·la E5.
=COINCIDENT("Lo??n",A2:A22,0)
Podeu utilitzar (*) el comodí de la mateixa manera que (?), però s'utilitza un asterisc per fer coincidir qualsevol nombre de caràcters mentre que un signe d'interrogació s'utilitza per fer coincidir qualsevol caràcter.
Per exemple, si utilitzeu "sp*", la funció podria coincidir amb l'altaveu, la velocitat o el spielberg, etc. Però si la funció troba diversos valors/duplicats que coincideixen amb el valor de cerca, només retornarà la posició del primer valor.
A l'exemple, hem introduït "Kil*o" a l'argument lookup_value. Així, la funció MATCH() cerca un text que contingui "Kil" al principi, "o" al final i qualsevol nombre de caràcters entremig. "Kil*o" coincideix amb Kilimanjaro a la matriu i, per tant, la funció retorna la posició relativa de Kilimanjaro, que és 16.
ÍNDEX i PARTIDA
Les funcions MATCH rarament s'utilitzen soles. Sovint es van combinar amb altres funcions per crear fórmules potents. Quan la funció MATCH es combina amb la funció INDEX, pot realitzar cerques avançades. Molta gent encara prefereix fer servir BUSCAR V per cercar un valor, perquè és més senzill, però CONcordança amb l'índex és més flexible i més ràpid que BUSCAR V.
VLOOKUP només pot cercar un valor verticalment, és a dir, columnes, mentre que la combinació INDEX MATCH pot fer cerques tant verticals com horitzontals.
Funció INDEX utilitzada per recuperar un valor en una ubicació específica d'una taula o un interval. La funció MATCH retorna la posició relativa d'un valor en una columna o fila. Quan es combina, el MATCH troba el número de fila o columna (ubicació) d'un valor específic, i la funció INDEX recupera un valor basat en aquest número de fila i columna.
Sintaxi de la funció INDEX:
=INDEX(matriu,núm_fila,[núm_col],)De totes maneres, vegem com funciona INDEX MATCH amb un exemple.
A l'exemple següent, volem recuperar la puntuació "Quiz2" de l'estudiant "Anne". Per fer-ho utilitzarem la fórmula següent:
=ÍNDEX(B2:F20;COINCIDENT(H2;A2:A20;0);3)INDEX necessita un número de fila i columna per recuperar un valor. A la fórmula anterior, la funció MATCH imbricada troba el número de fila (posició) del valor "Anne" (H2). A continuació, proporcionem aquest número de fila a la funció INDEX amb un rang B2:F20 i un número de columna (3), que especifiquem. I la funció INDEX retorna la puntuació '91'.
Cerca bidireccional amb INDEX i MATCH
També podeu utilitzar les funcions INDEX i MATCH per cercar un valor en un rang bidimensional (cerca bidireccional). A l'exemple anterior, hem utilitzat la funció MATCH per localitzar el número de fila d'un valor, però hem introduït el número de columna manualment. Però podem trobar tant fila com columna anidant dues funcions MATCH, una a l'argument row_num i una altra a l'argument column_num de la funció INDEX.
Utilitzeu aquesta fórmula per a una cerca bidireccional amb INDEX i MATCH:
=ÍNDEX(A1:F20;COINCIDENT(H2;A2:A20;0); COINCORDACIÓ(H3;A1:F1;0))Com sabem, la funció MATCH pot buscar un valor tant horitzontalment com verticalment. En aquesta fórmula, la segona funció MATCH de l'argument num_columna troba la posició de Quiz2 (4) i la proporciona a la funció INDEX. I l'INDEX recupera la puntuació.
Ara, ja sabeu com utilitzar la funció Match a Excel.