photosdematures.com

La fórmula Vlookup només funciona quan la matriu de taula de la fórmula no canvia, però si hi ha una nova columna inserida a la taula o s’elimina una columna, la fórmula dóna un resultat incorrecte o reflecteix un error, perquè la fórmula estigui lliure d’errors a aquestes situacions dinàmiques utilitzem la funció de coincidència per coincidir realment amb l’índex de les dades i retornar el resultat real.

Combineu VLOOKUP amb Match

La fórmula vlookup és la funció més utilitzada que s’utilitza per cercar i retornar el mateix valor a l’índex de columna especificat o el valor d’un índex de columna diferent amb referència al valor coincident de la primera columna. El principal desafiament a l’hora d’utilitzar vlookup és que l’índex de columna que cal especificar és estàtic i no té una funcionalitat dinàmica. Especialment quan esteu treballant en diversos criteris que requereixen canviar manualment l’índex de columna de referència. Per tant, aquesta necessitat es compleix mitjançant l'ús de la fórmula "MATCH" per tenir un millor control o control de l'índex de columna que canvia freqüentment a la fórmula VLOOKUP.

Fórmula de cerca i coincidència

# 1 - Fórmula VLOOKUP

La fórmula de la funció VLOOKUP a Excel

Aquí tots els arguments que cal introduir són obligatoris.

• Valor_cerca– Aquí s’hauria d’introduir una cel·la de referència o text amb cometes dobles per identificar-les a l’interval de columnes.
• Matriu de taules– Aquest argument requereix que s'introdueixi l'interval de la taula on s'ha de cercar el valor de cerca i les dades que es recuperaran es troben a l'interval de columnes concret.
• Col_index_num– En aquest argument, s’ha d’introduir el número d’índex de columna o el recompte de la columna de la primera columna de referència a partir de la qual s’ha d’extreure el valor corresponent de la mateixa posició que el valor cercat a la primera columna.
• [Range_lookup] - Aquest argument donarà dues opcions.
• CERT - Coincidència aproximada: - L'argument es pot introduir com a TRUE o numèric "1", que retorna la coincidència aproximada corresponent a la columna de referència o a la primera columna. A més, els valors de la primera columna de la matriu de taules s’han d’ordenar en ordre ascendent.
• FALS - Coincidència exacta: - Aquí l'argument que cal introduir pot ser FALS o numèric "0". Aquesta opció només retornarà la coincidència exacta del valor corresponent a identificar des de la posició del primer rang de columnes. Si no es busca el valor de la primera columna, es mostrarà un missatge d'error "# N / A".

# 2 - Fórmula del partit

La funció de coincidència retorna la posició de cel·la del valor introduït per a la matriu de taula donada.

Tots els arguments de la sintaxi són obligatoris.

• Valor_cerca - Aquí l'argument introduït pot ser la referència de cel·la del valor o una cadena de text amb cometes dobles la posició de la qual de la cel·la s'ha de treure.
• Look__array - Cal introduir l'interval de matriu de la taula el valor o contingut de la cel·la que es vulgui identificar.
• [tipus de partit] - Aquest argument proporciona tres opcions tal com s'explica a continuació.
• "1-Menys de"– Aquí l'argument que cal introduir és el número 1 que retornarà el valor inferior o igual al valor de cerca. A més, la matriu de cerca s'ha d'ordenar en ordre ascendent.
• "0-Coincidència exacta" - Aquí l'argument que cal introduir ha de ser numèric "0". Aquesta opció retornarà la posició exacta del valor de cerca coincident. Tanmateix, la matriu de cerca pot estar en qualsevol ordre.
• "-1-superior a" -L'argument que cal introduir ha de ser "-1" numèric. La tercera opció troba el valor més petit que és superior o igual al valor de cerca. Aquí l'ordre de la matriu de cerca s'ha de col·locar en ordre descendent.

# 3 - VLOOKUP amb Fórmula MATCH

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Com s'utilitza VLOOKUP amb fórmula de coincidència a Excel?

L'exemple següent us ajudarà a entendre el funcionament de la fórmula vlookup i coincidència a l'hora d'ajuntar.

Podeu descarregar aquest VLookup amb la plantilla Match Excel aquí - VLookup amb la plantilla Match Excel

Tingueu en compte la següent taula de dades que descriu les especificacions del vehicle donat a comprar.

Per obtenir la claredat de la funció combinada de vlookup i funció de coincidència, entenem com funciona la fórmula individual i, a continuació, arribem als resultats de coincidència de vlookup quan es combinen.

Pas 1 - Apliquem la fórmula vlookup a nivell individual per arribar al resultat.

La sortida es mostra a continuació:

Aquí el valor de cerca es refereix a $ B9 que és el model "E" i la matriu de cerca es dóna com a rang de la taula de dades amb el valor absolut "$"; l'índex de columna es refereix a la columna "4", que és el recompte de a la columna "Tipus" i la cerca d'interval es concreta exactament.

Per tant, s'aplica la següent fórmula per retornar el valor de la columna "Combustible".

La sortida es mostra a continuació:

Aquí el valor de cerca amb la cadena absoluta "$" aplicada per al valor de cerca i lookup_array ajuda a corregir la cel·la de referència fins i tot si la fórmula s'està copiant a una cel·la diferent. A la columna "Combustible", hem de canviar l'índex de columna a "5" a mesura que canvia el valor a partir del qual cal obtenir les dades.

Pas 2:Ara apliquem la fórmula Match per recuperar la posició del valor de cerca donat.

La sortida es mostra a continuació:

Com es pot veure a la captura de pantalla anterior, aquí estem intentant recuperar la posició de la columna de la matriu de taula. En aquest cas, el número de columna que s’ha d’extreure es coneix com a cel·la C8 que és la columna “Tipus” i l’interval de cerca que s’ha de cercar es dóna com a rang de capçaleres de columna i el tipus de concordança es concreta exactament per ser “ 0 ”.

Així, la taula següent donarà el resultat desitjat per a les posicions de la columna "Combustible".

Ara, aquí la columna que s'ha cercat es dóna a la cel·la D8 i l'índex de columna desitjat es torna a "5".

Pas 3 - Ara la fórmula Coincidència s'utilitzarà dins de la funció vlookup per obtenir el valor de la posició de columna identificada.

La sortida es mostra a continuació:

A la fórmula anterior, la funció de coincidència es substitueix al paràmetre d'índex de columna de la funció vlookup. Aquí la funció de concordança identificarà la cel·la de referència del valor de cerca "C8" i torneu el número de columna a través de la matriu de taula donada. Aquesta posició de columna servirà com a entrada a l'argument d'índex de columna a la funció vlookup. Què al seu torn ajudarà vlookup a identificar el valor que es retornarà del número d’índex de columna resultant?

De la mateixa manera, també hem aplicat vlookup amb fórmula de concordança per a la columna "Combustible".

La sortida es mostra a continuació:

Per tant, podem aplicar aquesta funció de combinació a altres columnes "Tipus" i "Combustible".

Coses que cal recordar

• VLOOKUP només es pot aplicar als valors de cerca a la part esquerra més important. Qualsevol valor present que es cerqui a la part dreta de la taula de dades retornarà el valor d'error "# N / A".
• L'interval de table_array introduït al segon argument hauria de ser la referència absoluta de cel·la "$", això mantindrà l'interval de matriu de taula fixa en aplicar la fórmula de cerca a altres cel·les, o bé les cel·les de referència per a l'interval de matriu de taula es canviaran a la cel·la següent referència.
• El valor introduït al valor de cerca no ha de ser inferior al valor més petit de la primera columna de la matriu de la taula, en cas contrari la funció retornarà el valor d'error "# N / A".
• Abans d'aplicar una coincidència aproximada "TRUE" o "1" a l'últim argument, recordeu sempre d'ordenar la matriu de la taula en ordre ascendent.
• La funció de concordança només retorna la posició del valor a la matriu de taula vlookup i no torna el valor.
• En cas que la funció de coincidència no pugui identificar la posició del valor de cerca a la matriu de la taula, la fórmula retorna "# N / A" al valor d'error.
• Les funcions Vlookup i Match no distingeixen entre majúscules i minúscules quan es fa coincidir el valor de cerca amb el valor de text que coincideix a la matriu de la taula.