contingut
A la segona part del nostre tutorial sobre la funció VPR (VLOOKUP) a Excel, analitzarem alguns exemples que us ajudaran a dirigir tot el poder VPR per resoldre les tasques d'Excel més ambicioses. Els exemples suposen que ja teniu un coneixement bàsic de com funciona aquesta funció. Si no, potser us interessa començar amb la primera part d'aquest tutorial, que explica la sintaxi i l'ús bàsic. VPR. Bé, comencem.
Cerca a Excel per múltiples criteris
function VPR a Excel és una eina molt potent per fer cerques d'un valor determinat en una base de dades. Tanmateix, hi ha una limitació important: la seva sintaxi us permet cercar només un valor. Què passa si voleu cercar per diverses condicions? Trobareu la solució a continuació.
Exemple 1: cerca per 2 criteris diferents
Suposem que tenim una llista de comandes i volem trobar-la Quantitat de mercaderies (Quantitat), basat en dos criteris: Nom del client (Client) i продукта Название (Producte). La qüestió es complica pel fet que cadascun dels compradors va demanar diversos tipus de mercaderies, com es pot veure a la taula següent:
funció regular VPR no funcionarà en aquest escenari perquè retornarà el primer valor que trobi que coincideixi amb el valor de cerca donat. Per exemple, si voleu saber la quantitat d'un article Dolços'ordenat pel comprador Jeremy Hill, escriu la fórmula següent:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– aquesta fórmula retornarà el resultat 15corresponent al producte pomes, perquè és el primer valor que coincideix.
Hi ha una solució senzilla: creeu una columna addicional per combinar tots els criteris desitjats. En el nostre exemple, aquestes són les columnes Nom del client (Client) i продукта Название (Producte). No oblideu que la columna combinada sempre ha de ser la columna més a l'esquerra del rang de cerca, ja que és la columna de l'esquerra on la funció VPR mira cap amunt quan busca un valor.
Per tant, afegiu una columna auxiliar a la taula i copieu la fórmula següent a totes les seves cel·les: =B2&C2. Si voleu que la cadena sigui més llegible, podeu separar els valors combinats amb un espai: =B2&» «&C2. Després d'això, podeu utilitzar la fórmula següent:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
On és la cèl·lula B1 conté el valor concatenat de l'argument valor_cerca (valor_de_cerca) i 4 – Argument col_index_num (número_columna), és a dir, el número de la columna que conté les dades a recuperar.
Exemple 2: CERCA V per dos criteris amb la taula que es visualitza en un altre full
Si necessiteu actualitzar la taula principal (taula principal) afegint dades de la segona taula (taula de cerca), que es troba en un altre full o en un altre llibre de treball d'Excel, podeu recollir el valor desitjat directament a la fórmula que inseriu. a la taula principal.
Com a l'exemple anterior, necessitareu una columna auxiliar a la taula de cerca amb els valors combinats. Aquesta columna ha de ser la columna més a l'esquerra de l'interval de cerca.
Així que la fórmula amb VPR podria ser així:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Aquí, les columnes B i C contenen noms de clients i noms de productes, respectivament, i l'enllaç Comandes!$A&$2:$D$2 defineix una taula per buscar en un altre full.
Per fer que la fórmula sigui més llegible, podeu donar un nom a l'interval de visualització i, aleshores, la fórmula semblarà molt més senzilla:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Perquè la fórmula funcioni, els valors de la columna més esquerra de la taula que esteu mirant s'han de combinar exactament de la mateixa manera que en els criteris de cerca. A la figura anterior, hem combinat els valors u2bu2band posant un espai entre ells, de la mateixa manera que cal fer en el primer argument de la funció (BXNUMX& “” & CXNUMX).
Recordeu! function VPR limitat a 255 caràcters, no pot cercar un valor que tingui més de 255 caràcters. Tingueu-ho en compte i assegureu-vos que la longitud del valor desitjat no superi aquest límit.
Estic d'acord que afegir una columna auxiliar no és la solució més elegant i no sempre acceptable. Podeu fer el mateix sense la columna d'ajuda, però això requeriria una fórmula molt més complexa amb una combinació de funcions ÍNDEX (ÍNDEX) i PARTIT (MÉS EXPOSAT).
Extraurem els valors 2n, 3r, etc. mitjançant VLOOKUP
Ja ho sabeu VPR només pot retornar un valor coincident, més precisament, el primer trobat. Però, què passa si aquest valor es repeteix diverses vegades a la matriu visualitzada i voleu extreure'n el segon o el tercer? I si tots els valors? El problema sembla complicat, però la solució existeix!
Suposem que una columna de la taula conté els noms dels clients (Nom del client) i l'altra columna conté els productes (Producte) que han comprat. Intentem trobar els articles 2n, 3r i 4t comprats per un client determinat.
La manera més senzilla és afegir una columna auxiliar abans de la columna Nom del client i ompliu-lo amb noms de clients amb el número de repetició de cada nom, per exemple, John Doe 1, John Doe 2 etc. Farem el truc amb la numeració mitjançant la funció COUNTIF (COUNTIF), donat que els noms dels clients es troben a la columna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Després d'això, podeu utilitzar la funció normal VPRper trobar l'ordre requerit. Per exemple:
- Cercar 2n article encarregat pel client Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Cercar 3n article encarregat pel client Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
De fet, podeu introduir una referència de cel·la com a valor de cerca en lloc de text, tal com es mostra a la figura següent:
Si només estàs buscant 2-e repetició, podeu fer-ho sense la columna auxiliar creant una fórmula més complexa:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
En aquesta fórmula:
- $F$2 – una cel·la que conté el nom del comprador (no canvia, tingueu en compte que l'enllaç és absolut);
- $ B $ – columna Nom del client;
- Taula4 – La teva taula (aquest lloc també pot ser un rang normal);
- 16 $ – la cel·la final de la vostra taula o rang.
Aquesta fórmula només troba el segon valor coincident. Si necessiteu extreure les repeticions restants, utilitzeu la solució anterior.
Si necessiteu una llista de totes les coincidències, la funció VPR això no és un ajudant, ja que només retorna un valor a la vegada - període. Però Excel té una funció ÍNDEX (ÍNDEX), que pot fer front fàcilment a aquesta tasca. Com serà aquesta fórmula, aprendràs a l'exemple següent.
Recuperar totes les repeticions del valor desitjat
Com s'ha esmentat anteriorment VPR no pot extreure tots els valors duplicats de l'interval escanejat. Per fer-ho, necessiteu una fórmula una mica més complexa, formada per diverses funcions d'Excel, com ara ÍNDEX (ÍNDEX), PETIT (PETIT) i FILA (LÍNIA)
Per exemple, la fórmula següent troba totes les repeticions del valor de la cel·la F2 a l'interval B2:B16 i retorna el resultat de les mateixes files de la columna C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Introduïu aquesta fórmula de matriu a diverses cel·les adjacents, com ara les cel·les F4: F8tal com es mostra a la figura següent. El nombre de cel·les ha de ser igual o superior al nombre màxim possible de repeticions del valor cercat. No us oblideu de fer clic Ctrl + Maj + Retornper introduir correctament la fórmula matricial.
Si us interessa entendre com funciona, aprofundim una mica en els detalls de la fórmula:
Part 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – compareu el valor de la cel·la F2 amb cadascun dels valors del rang B2:B16. Si es troba una coincidència, l'expressió CADENA(C2:C16)-1 retorna el número de la línia corresponent (valor -1 permet no incloure la línia de capçalera). Si no hi ha coincidències, la funció IF (SI) retorna una cadena buida.
Resultat de la funció IF (SI) hi haurà una matriu horitzontal: {1,"",3,"",5,"","","","","","",12,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Aquí la funció FILA (LINE) actua com a comptador addicional. Com que la fórmula es copia a les cel·les F4:F9, restem el nombre 3 del resultat de la funció per obtenir el valor 1 a la cel·la F4 (línia 4, resta 3) per obtenir 2 a la cel·la F5 (línia 5, resta 3) i així successivament.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
function PETIT (PETIT) torna n-oh el valor més petit de la matriu de dades. En el nostre cas, quina posició (del més petit) a tornar ve determinada per la funció FILA (LÍNIA) (vegeu la part 2). Per tant, per a una cèl·lula F4 function PETIT({matriu},1) rendiments 1n element de matriu (més petit), és a dir 1. Per cel·la F5 rendiments 2n l'element més petit de la matriu, és a dir 3, Etc
Part 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
function ÍNDEX (INDEX) simplement retorna el valor d'una cel·la específica d'una matriu C2:C16. Per cel·la F4 function ÍNDEX ($C$2:$C$16) tornarà pomesper F5 function ÍNDEX ($C$2:$C$16) tornarà Dolços' etcètera.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
Finalment, posem la fórmula dins de la funció SI ERROR (IFERROR), perquè és poc probable que us agradi el missatge d'error #AT (#N/A) si el nombre de cel·les en què es copia la fórmula és inferior al nombre de valors duplicats en l'interval que es visualitza.
XNUMXD cerca per fila i columna coneguda
Realitzar una cerca XNUMXD a Excel implica cercar un valor per un nombre de fila i columna coneguda. En altres paraules, esteu extreint el valor de la cel·la a la intersecció d'una fila i una columna concretes.
Per tant, anem a la nostra taula i escrivim una fórmula amb una funció VPR, que trobarà informació sobre el cost de les llimones venudes al març.
Hi ha diverses maneres de realitzar una cerca XNUMXD. Consulta les opcions i tria la que més et convingui.
Funcions VLOOKUP i MATCH
Podeu utilitzar un munt de funcions VPR (CERCA V) i MÉS EXPOSADA (COMPARACIÓ) per trobar el valor a la intersecció dels camps продукта Название (cadena) i mes (columna) de la matriu en qüestió:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
La fórmula anterior és una funció regular VPR, que cerca una coincidència exacta del valor "Llimones" a les cel·les A2 a A9. Però com que no sabeu a quina columna es troben les vendes de març, no podreu establir el número de columna per al tercer argument de la funció. VPR. En canvi, s'utilitza la funció MÉS EXPOSADAper definir aquesta columna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Traduïda al llenguatge humà, aquesta fórmula significa:
- Busquem els personatges “Mar” – argument valor_cerca (valor_cerca);
- Mirant a les cel·les de A1 a I1 - argument matriu_cerques (matriu_cerques);
- Devolució de coincidència exacta - argument tipus_concordança (tipus_coincidència).
Ús 0 en el tercer argument, dius funcions MÉS EXPOSADA cerqueu el primer valor que coincideixi exactament amb el que esteu buscant. Això és equivalent al valor FALS (FALS) per al quart argument VPR.
Així és com podeu crear una fórmula de cerca bidireccional a Excel, també coneguda com a cerca bidimensional o cerca bidireccional.
Funció SUMPRODUCT
function SUMPRODUCT (SUMPRODUCT) retorna la suma dels productes de les matrius seleccionades:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funcions INDEX i MATCH
En el següent article explicaré aquestes funcions amb detall, així que de moment només podeu copiar aquesta fórmula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Intervals anomenats i l'operador d'intersecció
Si no us agraden totes aquestes fórmules complexes d'Excel, us pot agradar aquesta manera visual i memorable:
- Seleccioneu la taula, obriu la pestanya Fórmules (Fórmules) i feu clic Crea a partir de la selecció (Crear a partir de la selecció).
- Marqueu les caselles Fila superior (a la línia de dalt) i Columna esquerra (a la columna de l'esquerra). Microsoft Excel assignarà noms als intervals dels valors de la fila superior i la columna esquerra del full de càlcul. Ara podeu cercar amb aquests noms directament sense crear fórmules.
- En qualsevol cel·la buida, escriu =nom_fila nom_columna, per exemple així:
= Llimones Mar
... o viceversa:
=Mar Lemons
Recordeu que els noms de fila i columna han d'estar separats per un espai, que en aquest cas funciona com l'operador d'intersecció.
Quan introduïu un nom, Microsoft Excel mostrarà una informació sobre eines amb una llista de noms coincidents, igual que quan introduïu una fórmula.
- Premsa Enter i comproveu el resultat
En general, qualsevol dels mètodes anteriors que trieu, el resultat d'una cerca bidimensional serà el mateix:
Utilitzant diverses VLOOKUP en una fórmula
Succeeix que la taula principal i la taula de cerca no tenen una sola columna en comú, i això impedeix utilitzar la funció habitual VPR. Tanmateix, hi ha una altra taula que no conté la informació que ens interessa, sinó que té una columna comuna amb la taula principal i la taula de cerca.
Fem una ullada a l'exemple següent. Tenim una taula principal amb una columna SKU (nou), on voleu afegir una columna amb els preus corresponents d'una altra taula. A més, tenim 2 taules de cerca. El primer (taula de cerca 1) conté números actualitzats SKU (nou) i noms de productes, i el segon (taula de cerca 2): noms de productes i números antics SKU (antiga).
Per afegir preus de la segona taula de cerca a la taula principal, heu de realitzar una acció coneguda com a doble VPR o niuada VPR.
- Escriu una funció VPR, que troba el nom del producte a la taula Taula de consulta 1ús Ref., com el valor desitjat:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Aquí SKU_nou - Interval anomenada $A:$B a la taula Taula de consulta 1, 2 - aquesta és la columna B, que conté els noms de les mercaderies (vegeu la imatge de dalt)
- Escriu una fórmula per inserir preus d'una taula Taula de consulta 2 basat en noms de productes coneguts. Per fer-ho, enganxeu la fórmula que heu creat anteriorment com a valor de cerca per a la nova funció VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Aquí preu - Interval anomenada $A:$C a la taula Taula de consulta 2, 3 és la columna C que conté els preus.
La figura següent mostra el resultat retornat per la fórmula que hem creat:
Substitució dinàmica de dades de diferents taules mitjançant VLOOKUP i INDIRECT
En primer lloc, aclarim què entenem per l'expressió “Substitució dinàmica de dades de diferents taules” per assegurar-nos que ens entenem correctament.
Hi ha situacions en què hi ha diversos fulls amb dades del mateix format, i cal extreure la informació necessària d'un determinat full, en funció del valor que s'introdueixi en una cel·la determinada. Crec que és més fàcil explicar-ho amb un exemple.
Imagineu que teniu informes de vendes per a diverses regions amb els mateixos productes i en el mateix format. Voleu trobar xifres de vendes per a una regió específica:
Si només teniu dos informes d'aquest tipus, podeu utilitzar una fórmula vergonyosamente senzilla amb funcions VPR и IF (SI) per seleccionar l'informe desitjat per cercar:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
on:
- $ D $ 2 és una cel·la que conté el nom del producte. Tingueu en compte que aquí fem servir referències absolutes per evitar canviar el valor de cerca quan copieu la fórmula a altres cel·les.
- $D3 és una cel·la amb el nom de la regió. Estem utilitzant una referència de columna absoluta i una referència de fila relativa perquè tenim previst copiar la fórmula a altres cel·les de la mateixa columna.
- FL_Sales и CA_Vendes – els noms de les taules (o rangs denominats) que contenen els informes de vendes corresponents. Per descomptat, podeu utilitzar els noms de fulls habituals i les referències d'interval de cel·les, per exemple "Full FL"!$A$3:$B$10, però els intervals amb nom són molt més convenients.
Tanmateix, quan hi ha moltes taules d'aquest tipus, la funció IF no és la millor solució. En canvi, podeu utilitzar la funció INDIRECTE (INDIRECT) per tornar l'interval de cerca desitjat.
Com probablement sabreu, la funció INDIRECTE s'utilitza per retornar un enllaç donat per una cadena de text, que és exactament el que necessitem ara. Per tant, substituïu amb valentia a la fórmula anterior l'expressió per la funció IF per enllaçar amb la funció INDIRECTE. Aquí hi ha una combinació VPR и INDIRECTE funciona molt bé amb:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
on:
- $ D $ 2 – aquesta és una cel·la amb el nom del producte, no es modifica a causa de l'enllaç absolut.
- $D3 és la cel·la que conté la primera part del nom de la regió. En el nostre exemple, això FL.
- _Ventes – la part comuna del nom de tots els rangs o taules amb nom. Quan es combina amb el valor de la cel·la D3, forma el nom complet de l'interval requerit. A continuació es mostren alguns detalls per a aquells que són nous a la funció INDIRECTE.
Com funcionen INDIRECTE i VLOOKUP
Primer, deixeu-me que us recordi la sintaxi de la funció INDIRECTE (INDIRECTE):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
El primer argument pot ser una referència de cel·la (estil A1 o R1C1), un nom d'interval o una cadena de text. El segon argument determina quin estil d'enllaç inclou el primer argument:
- A1si l'argument és CODI VERITAT (VERTADER) o no especificat;
- R1C1, Si FAS E (FALS).
En el nostre cas, l'enllaç té l'estil A1, de manera que podeu deixar de banda el segon argument i centrar-vos en el primer.
Així que tornem als nostres informes de vendes. Si ho recordeu, cada informe és una taula separada situada en un full separat. Perquè la fórmula funcioni correctament, heu de posar nom a les vostres taules (o rangs) i tots els noms han de tenir una part comuna. Per exemple, així: CA_Vendes, FL_Vendes, TX_Ventes etcètera. Com podeu veure, "_Sales" està present en tots els noms.
function INDIRECTE connecta el valor de la columna D i la cadena de text "_Sales", per dir-ho així VPR en quina taula buscar. Si la cel·la D3 conté el valor "FL", la fórmula buscarà a la taula FL_Vendes, si "CA" - a la taula CA_Vendes etcètera.
El resultat de les funcions VPR и INDIRECTE serà el següent:
Si les dades es troben en diferents llibres d'Excel, haureu d'afegir el nom del llibre abans de l'interval anomenat, per exemple:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Si la funció INDIRECTE fa referència a un altre llibre de treball, aquest llibre ha d'estar obert. Si està tancat, la funció informarà d'un error. REF! (#SSIL!).