photosdematures.com

Índex coincideix amb diverses columnes i files de criteris

Tots utilitzem VLOOKUP dia a dia per obtenir les dades i també som conscients del fet que VLOOKUP pot recuperar les dades d’esquerra a dreta, de manera que el valor de cerca sempre hauria d’estar al costat esquerre de les columnes de resultats. Tot i això, tenim diverses alternatives que es poden utilitzar com a alternativa a la funció VLOOKUP en excel. Amb tecnologia avançada, podem utilitzar aquesta fórmula INDEX + MATCH per fer coincidir diversos criteris per a files i columnes. Per tant, aquest article especial us explicarà detalladament aquesta tècnica.

Com s'utilitza la fórmula INDEX + MATCH per coincidir amb diversos criteris?

Aquí expliquem com utilitzar la fórmula índex + concordança per fer coincidir diversos criteris de files i columnes amb exemples.

Podeu descarregar aquesta plantilla Excel de criteris múltiples de coincidència d’índex aquí: plantilla Excel de criteris múltiples de coincidència d’índexs

Exemple # 1: fórmula INDEX + MATCH

No la majoria de les funcions de cerca d’usuaris excel més enllà de VLOOKUP, els motius poden ser tants. De totes maneres, fem una introducció senzilla a aquesta fórmula abans d’anar al nivell avançat.

Per exemple, mireu l'estructura de dades següent a Excel.

Tenim noms de representant de vendes i els seus valors de vendes respectius. D'altra banda, tenim una llista desplegable de "Representant de venda" a la cel·la D2.

Segons la selecció que fem a la llista desplegable, l'import de les vendes ha d'aparèixer a la cel·la E2.

El problema és que no podem aplicar la fórmula VLOOKUP perquè el valor de cerca "Representant de vendes" es troba a la dreta de la columna de resultats "Vendes", de manera que en aquests casos podem utilitzar la fórmula de valor de cerca combinada INDEX + MATCH.

INDEX busca el valor del número de fila esmentat a l'interval A2: A11 i, en aquest interval, hem de proporcionar de quina fila necessitem el valor de venda. Aquest valor de fila es basa en el nom "Representant de vendes" seleccionat a la llista desplegable a Excel, de manera que la funció MATCH busca el número de fila "Representant de vendes" a l'interval B2: B11 i retorna el número de fila del valor coincident .

Exemple # 2 - Criteris múltiples a la fórmula INDEX + MATCH

Ara tenim una estructura de dades com la següent.

Tenim valors mensuals de vendes de "Representant de vendes". Des d'aquesta taula, necessitem resultats dinàmics, com a la cel·la A15. He creat una llista desplegable "Representant de vendes" i a la cel·la B14 he creat una llista desplegable "Mes".

Basant-nos en la selecció feta en aquestes dues cel·les, la nostra fórmula ha d’obtenir les dades de la taula anterior.

Per exemple, si trio "Rep 8" i "Apr", ha de mostrar el valor de vendes de "Rep 8" per al mes de "Apr".

Per tant, en aquests casos, hem de fer coincidir les files i les columnes. Seguiu els passos següents per aplicar la fórmula que coincideixi amb files i columnes.

Pas 1: Obriu la funció INDEX a la cel·la B15.

Pas 2: El primer argument de la funció INDEX és "Matriu", és a dir, de quin rang de cel·les necessitem el resultat. Per tant, en aquest cas, necessitem valors de vendes, així que trieu l’interval de cel·les de B2 a G11.

Pas 3: Següent argument de la funció INDEX a partir de quina fila de l'interval seleccionat necessitem el resultat. En aquest cas, hem d’arribar al número de fila “Representant de vendes” en funció de la selecció feta a la cel·la desplegable A15. Per tant, per obtenir dinàmicament el número de fila en funció de la funció de selecció oberta MATCH.

Pas 4: EL VALOR DE BUSCA de la funció MATCH és "Representant de vendes", així que trieu la cel·la A15 com a referència.

Pas 5: La matriu de cerca serà l'interval de noms de "representant de vendes" a la taula principal. Per tant, trieu l’interval entre A2 i A11.

Pas 6: El tipus de coincidència de la funció MATCH serà exacta, de manera que introduïu zero com a valor de l’argument.

Pas 7: El següent argument de la funció INDEX és "Número de columna", és a dir, de l'interval de cel·les seleccionat de la columna en què necessitem el resultat. Això depèn del mes que seleccionem a la llista desplegable de la cel·la B14. Per obtenir el número de columna, obriu automàticament una altra funció MATCH.

Pas 8: Aquest valor de cerca de temps serà el nom del mes, així que seleccioneu la cel·la B14 com a referència.

Pas 9: La matriu de cerca serà l'interval mensual de cel·les de la taula principal, és a dir, des de B1 fins a G1.

Pas 10: L'últim argument és el tipus de concordança, trieu "Concordança exacta" com a criteri. Tanqueu dos claudàtors i premeu la tecla Retorn per obtenir el resultat.

Com podem veure més amunt, hem triat "Rep 6" i "Apr" com a mes i la nostra fórmula ha retornat el valor de vendes del mes de "Apr" per a "Rep 6".

Nota: La cel·la de color groc és la vostra referència.

Coses que cal recordar

• Una combinació d’INDEX + MATCH pot ser més potent que la fórmula VLOOKUP.
• INDEX & MATCH pot coincidir amb capçaleres de files i columnes i retornar el resultat de la taula central.
• MATCH pot retornar el número de fila i el número de columna de les capçaleres de taula de les dues files i columnes.