Per què INDEX i MATCH són millors que VLOOKUP a Excel

Anteriorment hem explicat als principiants com utilitzar les funcions bàsiques de VLOOKUP (en anglès VLOOKUP, l'abreviatura significa "funció de cerca vertical"). I als usuaris experimentats se'ls van mostrar diverses fórmules més complicades.

I en aquest article intentarem donar informació sobre un altre mètode de treball amb la cerca vertical.

Potser us preguntareu: "Per què és necessari?". I això és necessari per mostrar tots els mètodes de cerca possibles. A més, moltes restriccions VLOOKUP sovint impedeixen obtenir el resultat desitjat. En aquest sentit, INDEX( ) MATCH( ) és molt més funcional i divers, i també tenen menys restriccions.

Conceptes bàsics INDEX MATCH

Com que l'objectiu d'aquesta guia és mostrar com de bona és aquesta funció, nosaltres Vegem la informació bàsica sobre els principis del seu funcionament. I mostrarem exemples, i també considerar per què, és millor que BUSCARV ().

ÍNDEX Funció Sintaxi i ús

Aquesta funció ajuda a trobar el valor desitjat entre les àrees de cerca especificades en funció del número de columna o línia. Sintaxi:

=INDEX(matriu, número de fila, número de columna):

• matriu: l'àrea on es durà a terme la cerca;
• número de línia: el número de la línia que s'ha de cercar a la matriu especificada. Si el número de fila és desconegut, s'ha d'especificar el número de columna;
• número de columna: el número de la columna que es troba a la matriu especificada. Si es desconeix el valor, cal un número de línia.

Un exemple de fórmula senzilla:

=ÍNDEX(A1:S10,2,3;XNUMX;XNUMX)

La funció cercarà en el rang d'A1 a C10. Els números mostren de quina fila (2) i columna (3) es mostra el valor desitjat. El resultat serà la cel·la C2.

Bastant senzill, oi? Però quan treballeu amb documents reals, és poc probable que tingueu informació sobre números de columnes o cel·les. Per això serveix la funció MATCH().

Sintaxi i ús de la funció MATCH

La funció MATCH() cerca el valor desitjat i mostra el seu número aproximat a l'àrea de cerca especificada.

La sintaxi searchpos() té aquest aspecte:

=COINCIDENT(valor a cercar, matriu a cercar, tipus de concordança)

• valor de cerca: el número o el text que cal trobar;
• matriu cercada: l'àrea on es durà a terme la cerca;
• tipus de concordança: especifica si cal cercar el valor exacte o els valors més propers a ell:
  • 1 (o cap valor especificat): retorna el valor més gran que és igual o inferior al valor especificat;
  • 0: mostra una coincidència exacta amb el valor cercat. A la combinació INDEX() MATCH() gairebé sempre necessitareu una coincidència exacta, així que escrivim 0;
  • -1: mostra el valor més petit que és més gran o igual que el valor especificat a la fórmula. La classificació es realitza en ordre descendent.

Per exemple, al rang B1:B3 es registren Nova York, París i Londres. La fórmula següent mostrarà el número 3 perquè Londres és el tercer a la llista:

=EXPOSIÓ(Londres,B1:B3,0)

Com treballar amb la funció INDEX MATCH 

Probablement ja heu començat a entendre el principi pel qual es construeix el treball conjunt d'aquestes funcions. En resum, doncs INDEX() cerca el valor desitjat entre les files i columnes especificades. I MATCH() mostra els números d'aquests valors:

=INDEX(columna des de la qual es retorna el valor, COINCORD (valor a cercar, columna a cercar, 0))

Encara us costa entendre com funciona? Potser un exemple ho explica millor. Suposem que teniu una llista de capitals mundials i la seva població:

Per esbrinar la mida de la població d'una determinada capital, per exemple, la capital del Japó, utilitzem la fórmula següent:

=ÍNDEX(C2:C10; COINCIDENT (Japó; A2:A10,0;XNUMX))

Explicació:

• La funció MATCH() cerca el valor - "Japó" a la matriu A2:A10 i retorna el número 3, perquè Japó és el tercer valor de la llista. 
• Aquesta xifra va anúmero de línia” a la fórmula INDEX() i diu a la funció que imprimeixi un valor d'aquesta fila.

Així, la fórmula anterior es converteix en la fórmula estàndard ÍNDEX(C2:C10,3). La fórmula cerca de les cel·les C2 a C10 i retorna dades de la tercera cel·la d'aquest interval, és a dir, C4, perquè el compte enrere comença des de la segona fila.

No voleu prescriure el nom de la ciutat a la fórmula? A continuació, escriviu-lo a qualsevol cel·la, per exemple F1, i utilitzeu-lo com a referència a la fórmula MATCH(). I acabeu amb una fórmula de cerca dinàmica:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Important Nombre de línies en formació INDEX() ha de ser el mateix que el nombre de files matriu considerat a MATCH(), en cas contrari obtindreu el resultat equivocat.

Espereu un minut, per què no utilitzeu la fórmula BUSCARV()?

=CERCAV(F1; A2:C10; 3; Fals)

 Quin sentit té perdre el temps intentant esbrinar totes aquestes complexitats d'INDEX MATCH?

En aquest cas, no importa quina funció utilitzar. Aquest és només un exemple per entendre com funcionen juntes les funcions INDEX() i MATCH(). Altres exemples mostraran de què són capaços aquestes funcions en situacions en què la VLOOKUP és impotent. 

Coincidència d'índex o CERCA V

A l'hora de decidir quina fórmula de cerca utilitzar, molts estan d'acord que INDEX() i MATCH() són molt superiors a VLOOKUP. Tanmateix, molta gent encara utilitza VLOOKUP(). En primer lloc, VLOOKUP() és més senzill i, en segon lloc, els usuaris no entenen tots els avantatges de treballar amb INDEX() i MATCH(). Sense aquest coneixement, ningú acceptarà dedicar el seu temps a estudiar un sistema complex.

Aquests són els avantatges clau d'INDEX() i MATCH() sobre VLOOKUP():

• Cerca de dreta a esquerra. VLOOKUP() no pot cercar de dreta a esquerra, de manera que els valors que cerqueu sempre han d'estar a les columnes més a l'esquerra de la taula. Però INDEX() i MATCH() poden gestionar-ho sense cap problema. Aquest article us dirà com es veu a la pràctica: com trobar el valor desitjat al costat esquerre.

1. Addició o eliminació segura de columnes. La fórmula BUSCARV() mostra resultats incorrectes en eliminar o afegir columnes perquè VLOOKUP() necessita el número de columna exacte per tenir èxit. Naturalment, quan s'afegeixen o s'eliminen columnes, els seus números també canvien. 

I a les fórmules INDEX() i MATCH() s'especifica un rang de columnes, no columnes individuals. Com a resultat, podeu afegir i eliminar columnes de manera segura sense haver d'actualitzar la fórmula cada vegada.

1. Sense límits en els volums de cerca. Quan utilitzeu BUSCARV(), el nombre total de criteris de cerca no ha de superar els 255 caràcters o obtindreu un #VALOR! Per tant, si les vostres dades contenen un gran nombre de caràcters, INDEX() i MATCH() són la millor opció.
2. Alta velocitat de processament. Si les vostres taules són relativament petites, és poc probable que no noteu cap diferència. Però, si la taula conté centenars o milers de files i, en conseqüència, hi ha centenars i milers de fórmules, INDEX () i MATCH () ho faran molt més ràpid que VLOOKUP (). El fet és que Excel processarà només les columnes especificades a la fórmula, en lloc de processar tota la taula. 

L'impacte en el rendiment de VLOOKUP() serà especialment notable si el vostre full de treball conté un gran nombre de fórmules com VLOOKUP() i SUM(). Es requereixen comprovacions separades de les funcions VLOOKUP() per analitzar cada valor d'una matriu. Per tant, Excel ha de processar una gran quantitat d'informació, i això alenteix significativament el treball.

Exemples de fórmules 

Ja hem descobert la utilitat d'aquestes funcions, així que podem passar a la part més interessant: l'aplicació del coneixement a la pràctica.

Fórmula per cercar de dreta a esquerra

Com ja s'ha esmentat, VLOOKUP no pot realitzar aquesta forma de cerca. Per tant, si els valors desitjats no es troben a la columna més esquerra, VLOOKUP() no produirà cap resultat. Les funcions INDEX() i MATCH() són més versàtils i la ubicació dels valors no juga un paper important perquè funcionin.

Per exemple, afegirem una columna de rang al costat esquerre de la nostra taula i intentarem esbrinar quin rang ocupa la capital del nostre país en termes de població.

A la cel·la G1, escrivim el valor que cal trobar i, a continuació, utilitzem la fórmula següent per cercar a l'interval C1:C10 i retornar el valor corresponent de A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Avís. Si teniu previst utilitzar aquesta fórmula per a diverses cel·les, assegureu-vos de corregir els intervals mitjançant l'adreçament absolut (per exemple, $A$2: $A$10 i $C$2: 4C$10).

ÍNDEX MÉS EXPOSATS MÉS EXPOSATS  per cercar en columnes i files

En els exemples anteriors, hem utilitzat aquestes funcions com a reemplaçament de VLOOKUP() per retornar valors d'un rang predefinit de files. Però, què passa si necessiteu fer una cerca matricial o a dues cares?

Sembla complicat, però la fórmula per a aquests càlculs és similar a la fórmula estàndard INDEX() MATCH(), amb només una diferència: la fórmula MATCH() s'ha d'utilitzar dues vegades. La primera vegada per obtenir el número de fila i la segona vegada per obtenir el número de columna:

=INDEX(matriu, COINCORDA (valor de cerca vertical, columna de cerca, 0), COINCIDENCIA (valor de cerca horitzontal, fila de cerca, 0))

Mirem la taula següent i intentem fer una fórmula INDEX() EXPRESS() EXPRESS() per mostrar dades demogràfiques d'un país específic per a un any seleccionat.

El país objectiu es troba a la cel·la G1 (cerca vertical) i l'any objectiu és a la cel·la G2 (cerca horitzontal). La fórmula serà així:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Com funciona aquesta fórmula

Com amb qualsevol altra fórmula complexa, són més fàcils d'entendre desglossant-les en equacions individuals. I llavors podeu entendre què fa cada funció individual:

• COINCIDENT(G1;A2:A11,0;XNUMX) – cerca un valor (G1) en el rang A2:A11 i mostra el número d'aquest valor, en el nostre cas és 2;
• CERCA (G2;B1:D1,0;XNUMX) – cerca un valor (G2) en el rang B1:D1. En aquest cas, el resultat va ser 3.

Els números de fila i columna trobats s'envien al valor corresponent a la fórmula INDEX():

=ÍNDEX(B2:D11,2,3;XNUMX;XNUMX)

Com a resultat, tenim un valor que es troba en una cel·la a la intersecció de 2 files i 3 columnes en el rang B2:D11. I la fórmula mostra el valor desitjat, que es troba a la cel·la D3.

Cerca per múltiples condicions amb INDEX i MATCH

Si heu llegit la nostra guia de VLOOKUP(), probablement heu provat diverses fórmules de cerca. Però aquest mètode de cerca té una limitació important: la necessitat d'afegir una columna auxiliar.

Però la bona notícia és que Amb INDEX() i MATCH() podeu cercar diverses condicions sense haver d'editar o canviar el vostre full de treball.

Aquí teniu la fórmula general de cerca de diverses condicions per a INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

La nota: aquesta fórmula s'ha d'utilitzar juntament amb la drecera del teclat CTRL+MAJ+INTER.

Suposem que necessiteu trobar el valor que busqueu en funció de 2 condicions: Comprador и Producte

Això requereix la fórmula següent:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

En aquesta fórmula, C2:C10 és l'interval en què es durà a terme la cerca, F1 - aquesta condició, A2:A10 — és l'interval per comparar la condició, F2 - condició 2, V2:V10 - Interval per a la comparació de la condició 2.

No oblideu prémer la combinació al final del treball amb la fórmula CTRL + MAJÚS + ENTRADA – Excel tancarà automàticament la fórmula amb claus, tal com es mostra a l'exemple:

Si no voleu utilitzar una fórmula de matriu per al vostre treball, afegiu un altre INDEX() a la fórmula i premeu ENTER, es veurà com a l'exemple:

Com funcionen aquestes fórmules

Aquesta fórmula funciona de la mateixa manera que la fórmula estàndard INDEX() MATCH(). Per cercar diverses condicions, només cal que creeu diverses condicions Falses i Vertaderes que representin les condicions individuals correctes i incorrectes. I llavors aquestes condicions s'apliquen a tots els elements corresponents de la matriu. La fórmula converteix els arguments Fals i True a 0 i 1, respectivament, i genera una matriu on 1 són els valors coincidents que es van trobar a la cadena. MATCH() trobarà el primer valor que coincideixi amb 1 i el passarà a la fórmula INDEX(). I, al seu torn, retornarà el valor ja desitjat a la línia especificada de la columna desitjada.

Una fórmula sense matriu depèn de la capacitat de INDEX() per gestionar-les per si mateixa. El segon INDEX() de la fórmula coincideix amb fals (0), de manera que passa tota la matriu amb aquests valors a la fórmula MATCH(). 

Aquesta és una explicació força llarga de la lògica darrere d'aquesta fórmula. Per a més informació llegiu l'article "INDEX MATCH amb múltiples condicions».

MITJANA, MAX i MIN a INDEX i MATCH

Excel té les seves pròpies funcions especials per trobar mitjanes, màxims i mínims. Però, què passa si voleu obtenir dades de la cel·la associada amb aquests valors? En aquest cas AVERAGE, MAX i MIN s'han d'utilitzar juntament amb INDEX i MATCH.

INDEX MATCH i MAX

Per trobar el valor més gran a la columna D i mostrar-lo a la columna C, utilitzeu la fórmula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH i MIN

Per trobar el valor més petit a la columna D i mostrar-lo a la columna C, utilitzeu la fórmula següent:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

ÍNDEX DE RECERCA i SERRP

Per trobar el valor mitjà a la columna D i mostrar aquest valor a C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Depenent de com s'escriuen les vostres dades, el tercer argument de MATCH() és 1, 0 o -1:

• si les columnes s'ordenen en ordre ascendent, establiu 1 (aleshores la fórmula calcularà el valor màxim, que és inferior o igual al valor mitjà);
• si l'ordenació és descendent, aleshores -1 (la fórmula mostrarà el valor mínim que sigui superior o igual a la mitjana);
• si la matriu de cerca conté un valor que és exactament igual a la mitjana, establiu-lo a 0. 

 En el nostre exemple, la població s'ordena en ordre descendent, de manera que posem -1. I el resultat és Tòquio, ja que el valor de població (13,189) és el més proper al valor mitjà (000).

VLOOKUP() també pot realitzar aquests càlculs, però només com a fórmula de matriu: VLOOKUP amb AVERAGE, MIN i MAX.

INDEX MATCH i ESND/IFERROR

Probablement ja us heu adonat que si la fórmula no pot trobar el valor desitjat, genera un error # N / A. Podeu substituir el missatge d'error estàndard per quelcom més informatiu. Per exemple, establiu l'argument a la fórmula Al XNUMXè:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Amb aquesta fórmula, si introduïu dades que no estan a la taula, el formulari us donarà el missatge especificat.

Si voleu detectar tots els errors, excepte Al XNUMXè es pot utilitzar SI ERROR:

=SIERROR(ÍNDEX(C2:C10,COINCIDENT(F1,A2:A10,0)), "Alguna cosa ha anat malament!")

Però recordeu que emmascarar els errors d'aquesta manera no és una bona idea, perquè els errors estàndard informen d'infraccions a la fórmula.

Esperem que us sigui útil la nostra guia per utilitzar la funció INDEX MATCH().