Contidos
Previamente explicamos aos principiantes como usar as funcións básicas de VLOOKUP (en inglés VLOOKUP, a abreviatura significa "función de busca vertical"). E aos usuarios experimentados mostráronselles varias fórmulas máis complicadas.
E neste artigo trataremos de dar información sobre outro método de traballo coa busca vertical.
Podes estar a preguntar: "Por que é necesario isto?". E isto é necesario para mostrar todos os métodos de busca posibles. Ademais, numerosas restricións VLOOKUP adoitan impedir obter o resultado desexado. Neste sentido, INDEX( ) MATCH( ) é moito máis funcional e diverso, e tamén teñen menos restricións.
Conceptos básicos INDEX MATCH
Dado que o propósito desta guía é mostrar o bo que é esta función, nós Vexamos a información básica sobre os principios do seu funcionamento. E mostraremos exemplos, e tamén consideraremos por que é mellor que BUSCAR V ().
Sintaxe e uso da función INDEX
Esta función axuda a atopar o valor desexado entre as áreas de busca especificadas en función da columna ou do número de liña. Sintaxe:
=INDEX(matriz, número de fila, número de columna):
- matriz: a área na que terá lugar a busca;
- número de liña: o número da liña que se vai buscar na matriz especificada. Se o número de fila é descoñecido, debe especificarse o número de columna;
- número de columna: o número da columna que se atopa na matriz especificada. Se o valor é descoñecido, é necesario un número de liña.
Un exemplo dunha fórmula sinxela:
=ÍNDICE(A1:S10,2,3;XNUMX;XNUMX)
A función buscará no rango de A1 a C10. Os números mostran a fila (2) e a columna (3) desde a que se mostra o valor desexado. O resultado será a cela C2.
Moi sinxelo, non? Pero cando traballas con documentos reais, é improbable que teñas información sobre números de columnas ou celas. Para iso serve a función MATCH().
Sintaxe e uso da función MATCH
A función MATCH() busca o valor desexado e mostra o seu número aproximado na área de busca especificada.
A sintaxe searchpos() ten o seguinte aspecto:
= COINCIDIR (valor para buscar, matriz para buscar, tipo de coincidencia)
- valor de busca: o número ou texto que se vai atopar;
- matriz buscada: a área onde terá lugar a busca;
- tipo de coincidencia: especifica se buscar o valor exacto ou os valores máis próximos a el:
- 1 (ou ningún valor especificado): devolve o valor máis grande que é igual ou menor que o valor especificado;
- 0: mostra unha coincidencia exacta co valor buscado. Na combinación INDEX() MATCH() case sempre necesitará unha coincidencia exacta, polo que escribimos 0;
- -1: mostra o valor máis pequeno que é maior ou igual ao valor especificado na fórmula. A clasificación realízase en orde descendente.
Por exemplo, no rango B1:B3 están rexistrados Nova York, París e Londres. A seguinte fórmula mostrará o número 3 porque Londres é o terceiro na lista:
=EXPOSICIÓN(Londres;B1:B3,0;XNUMX)
Como traballar coa función INDEX MATCH
Probablemente xa comezaches a comprender o principio polo que se constrúe o traballo conxunto destas funcións. En resumo, pois INDEX() busca o valor desexado entre as filas e columnas especificadas. E MATCH() mostra os números destes valores:
=INDEX(columna desde a que se devolve o valor, COINCIDIR(valor para buscar, columna para buscar, 0))
Aínda tes dificultades para entender como funciona? Quizais un exemplo explique mellor. Supoña que tes unha lista de capitais mundiais e a súa poboación:
Para coñecer o tamaño da poboación dunha determinada capital, por exemplo, a capital de Xapón, utilizamos a seguinte fórmula:
=ÍNDICE(C2:C10; PARTICIPACIÓN(Xapón; A2:A10,0;XNUMX))
Explicación:
- A función MATCH() busca o valor - "Xapón" na matriz A2:A10 e devolve o número 3, porque Xapón é o terceiro valor da lista.
- Esta cifra vai paranúmero de liña” na fórmula INDEX() e dille á función que imprima un valor desta fila.
Entón, a fórmula anterior convértese na fórmula estándar ÍNDICE(C2:C10,3). A fórmula busca desde as celas C2 ata C10 e devolve os datos da terceira cela deste intervalo, é dicir, C4, porque a conta atrás comeza a partir da segunda fila.
Non queres prescribir o nome da cidade na fórmula? Despois escríbeo en calquera cela, digamos F1, e utilízao como referencia na fórmula MATCH(). E acabas cunha fórmula de busca dinámica:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Importante! Número de liñas en orde INDEX() debe ser o mesmo que o número de filas matriz considerada en MATCH(), se non, obterás un resultado incorrecto.
Agarda un minuto, por que non usas a fórmula VLOOKUP()?
=BUSCARV(F1; A2:C10; 3; Falso)
Para que serve perder o tempo intentando descubrir todas estas complexidades de INDEX MATCH?
Neste caso, non importa a función que se utilice. Este é só un exemplo para comprender como funcionan xuntas as funcións INDEX() e MATCH(). Outros exemplos mostrarán de que son capaces estas funcións en situacións nas que a BUSCAR V é impotente.
COINCIDENCIA DE ÍNDICE ou BUSCAR V
Ao decidir que fórmula de busca usar, moitos coinciden en que INDEX() e MATCH() son moi superiores a VLOOKUP. Non obstante, moitas persoas aínda usan VLOOKUP(). En primeiro lugar, VLOOKUP() é máis sinxelo e, en segundo lugar, os usuarios non comprenden completamente todas as vantaxes de traballar con INDEX() e MATCH(). Sen este coñecemento, ninguén aceptará dedicar o seu tempo a estudar un sistema complexo.
Aquí están as principais vantaxes de INDEX() e MATCH() sobre VLOOKUP():
- Busca de dereita a esquerda. VLOOKUP() non pode buscar de dereita a esquerda, polo que os valores que busca sempre deben estar nas columnas máis á esquerda da táboa. Pero INDEX() e MATCH() poden xestionar isto sen problema. Este artigo dirá como se ve na práctica: como atopar o valor desexado no lado esquerdo.
- Adición ou eliminación segura de columnas. A fórmula BUSCARV() mostra resultados incorrectos ao eliminar ou engadir columnas porque BUSCARV() necesita o número de columna exacto para ter éxito. Por suposto, cando se engaden ou eliminan columnas, os seus números tamén cambian.
E nas fórmulas INDEX() e MATCH() especifícase un rango de columnas, non columnas individuais. Como resultado, pode engadir e eliminar columnas con seguridade sen ter que actualizar a fórmula cada vez.
- Sen límites nos volumes de busca. Ao usar BUSCARV(), o número total de criterios de busca non debe superar os 255 caracteres ou obterá un #VALOR! Polo tanto, se os teus datos conteñen un gran número de caracteres, INDEX() e MATCH() son a mellor opción.
- Alta velocidade de procesamento. Se as túas táboas son relativamente pequenas, é improbable que notedes ningunha diferenza. Pero, se a táboa contén centos ou miles de filas e, en consecuencia, hai centos e miles de fórmulas, INDEX () e MATCH () funcionarán moito máis rápido que VLOOKUP (). O feito é que Excel procesará só as columnas especificadas na fórmula, en lugar de procesar toda a táboa.
O impacto no rendemento de BUSCARV() será especialmente notable se a súa folla de traballo contén un gran número de fórmulas como BUSCARV() e SUMA(). Requírense verificacións separadas das funcións VLOOKUP() para analizar cada valor nunha matriz. Entón, Excel ten que procesar unha gran cantidade de información, e isto ralentiza significativamente o traballo.
Exemplos de fórmulas
Xa descubrimos a utilidade destas funcións, polo que podemos pasar á parte máis interesante: a aplicación dos coñecementos na práctica.
Fórmula para buscar de dereita a esquerda
Como xa se mencionou, VLOOKUP non pode realizar esta forma de busca. Polo tanto, se os valores desexados non están na columna da esquerda, VLOOKUP() non producirá un resultado. As funcións INDEX() e MATCH() son máis versátiles e a localización dos valores non xoga un papel importante para que funcionen.
Por exemplo, engadiremos unha columna de rango ao lado esquerdo da nosa táboa e tentaremos descubrir que rango en termos de poboación ocupa a capital do Noso País.
Na cela G1, escribimos o valor a atopar e, a continuación, usamos a seguinte fórmula para buscar no intervalo C1:C10 e devolvemos o valor correspondente de A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Aviso. Se pensas usar esta fórmula para varias celas, asegúrate de corrixir os intervalos mediante o enderezo absoluto (por exemplo, $A$2: $A$10 e $C$2: 4C$10).
ÍNDICE MÁIS EXPOSITO MÁIS EXPOSTO para buscar en columnas e filas
Nos exemplos anteriores, usamos estas funcións como substitución de BUSCARV() para devolver valores dun rango predefinido de filas. Pero e se precisa facer unha busca matricial ou a dúas caras?
Parece complicado, pero a fórmula para tales cálculos é semellante á fórmula estándar INDEX() MATCH(), con só unha diferenza: a fórmula MATCH() debe usarse dúas veces. A primeira vez para obter o número de fila e a segunda vez para obter o número de columna:
=INDEX(matriz, COINCIDIR (valor de busca vertical, columna de busca, 0), COINCIDIR (valor de busca horizontal, fila de busca, 0))
Vexamos a seguinte táboa e intentemos facer unha fórmula INDEX() EXPRESS() EXPRESS() para mostrar datos demográficos dun país específico para un ano seleccionado.
O país de destino está na cela G1 (busca vertical) e o ano de destino está na cela G2 (busca horizontal). A fórmula será así:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Como funciona esta fórmula
Como con calquera outra fórmula complexa, son máis fáciles de entender descompoñendoas en ecuacións individuais. E entón podes entender o que fai cada función individual:
- PARTICIPAR(G1;A2:A11,0;XNUMX) – busca un valor (G1) no rango A2:A11 e mostra o número deste valor, no noso caso é 2;
- BUSCAR(G2;B1:D1,0;XNUMX) – busca un valor (G2) no rango B1:D1. Neste caso, o resultado foi 3.
Os números de fila e columna atopados envíanse ao valor correspondente na fórmula INDEX():
=ÍNDICE(B2:D11,2,3;XNUMX;XNUMX)
Como resultado, temos un valor que está nunha cela na intersección de 2 filas e 3 columnas no rango B2:D11. E a fórmula mostra o valor desexado, que está na cela D3.
Busca por varias condicións con INDEX e MATCH
Se leu a nosa guía para BUSCARV(), probablemente probaches varias fórmulas de busca. Pero este método de busca ten unha limitación importante: a necesidade de engadir unha columna auxiliar.
Pero a boa noticia é que Con INDEX() e MATCH() pode buscar varias condicións sen ter que editar ou cambiar a súa folla de traballo.
Aquí está a fórmula xeral de busca de varias condicións para INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
A nota: esta fórmula debe usarse xunto co atallo de teclado CTRL+MAIÚS+INTRO.
Supoña que necesitas atopar o valor que buscas en función de dúas condicións: comprador и Produto
Isto require a seguinte fórmula:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
Nesta fórmula, C2:C10 é o intervalo no que terá lugar a busca, F1 - esta condición, A2:A10 — é o intervalo para comparar a condición, F2 - condición 2, V2:V10 - Intervalo para a comparación da condición 2.
Non esquezas presionar a combinación ao final do traballo coa fórmula CTRL + MAIÚS + ENTRAR - Excel pechará automaticamente a fórmula con chaves, como se mostra no exemplo:
Se non queres usar unha fórmula matricial para o teu traballo, engade outro INDEX() á fórmula e preme ENTER, parecerase no exemplo:
Como funcionan estas fórmulas
Esta fórmula funciona do mesmo xeito que a fórmula estándar INDEX() MATCH(). Para buscar varias condicións, simplemente crea varias condicións falsas e verdadeiras que representan as condicións individuais correctas e incorrectas. E entón estas condicións aplícanse a todos os elementos correspondentes da matriz. A fórmula converte os argumentos Falso e Verdadeiro en 0 e 1, respectivamente, e dá como resultado unha matriz onde 1 son os valores coincidentes que se atoparon na cadea. MATCH() atopará o primeiro valor que coincida con 1 e pasalo á fórmula INDEX(). E, á súa vez, devolverá o valor xa desexado na liña especificada da columna desexada.
Unha fórmula sen matriz depende da capacidade de INDEX() para manexalas por si só. O segundo INDEX() da fórmula coincide con falsy (0), polo que pasa a matriz completa con eses valores á fórmula MATCH().
Esta é unha explicación bastante longa da lóxica detrás desta fórmula. Para máis información lea o artigo "COINCIDENCIA DE ÍNDICE con varias condicións».
MEDIA, MAX e MIN en INDEX e MATCH
Excel ten as súas propias funcións especiais para atopar medias, máximos e mínimos. Pero e se queres obter datos da cela asociada a eses valores? Neste caso AVERAGE, MAX e MIN deben usarse xunto con INDEX e MATCH.
INDEX MATCH e MAX
Para atopar o valor máis grande na columna D e mostralo na columna C, use a fórmula:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
COINCIDENCIA ÍNDICE e MIN
Para atopar o valor máis pequeno na columna D e mostralo na columna C, use a seguinte fórmula:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
ÍNDICE DE BUSCA e SERPE
Para atopar o valor medio na columna D e mostrar este valor en C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Dependendo de como se escriban os teus datos, o terceiro argumento de MATCH() é 1, 0 ou -1:
- se as columnas están ordenadas en orde ascendente, establece 1 (a continuación, a fórmula calculará o valor máximo, que é menor ou igual ao valor medio);
- se a ordenación é descendente, entón -1 (a fórmula mostrará o valor mínimo que é maior ou igual á media);
- se a matriz de busca contén un valor que é exactamente igual á media, entón establéceo en 0.
No noso exemplo, a poboación está ordenada en orde descendente, polo que poñemos -1. E o resultado é Tokio, xa que o valor da poboación (13,189) é o que máis se achega ao valor medio (000).
VLOOKUP() tamén pode realizar tales cálculos, pero só como fórmula matricial: VLOOKUP con AVERAGE, MIN e MAX.
INDEX MATCH e ESND/IFERROR
Probablemente xa notaches que se a fórmula non pode atopar o valor desexado, xera un erro #N / A. Pode substituír a mensaxe de erro estándar por algo máis informativo. Por exemplo, establece o argumento na fórmula No XNUMX:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Con esta fórmula, se introduces datos que non están na táboa, o formulario darache a mensaxe especificada.
Se queres detectar todos os erros, excepto No XNUMX se pode usar SE ERRO:
=SI ERRO(ÍNDICE(C2:C10;COINCIDENCIA(F1;A2:A10,0)), "Algo saíu mal!")
Pero lembre que enmascarar os erros deste xeito non é unha boa idea, porque os erros estándar informan de infraccións na fórmula.
Esperamos que che resulte útil a nosa guía para usar a función INDEX MATCH().