Innehåll
I den andra delen av vår handledning om funktionen VPR (VLOOKUP) i Excel kommer vi att analysera några exempel som hjälper dig att styra all kraft VPR att lösa de mest ambitiösa Excel-uppgifterna. Exemplen förutsätter att du redan har en grundläggande kunskap om hur denna funktion fungerar. Om inte, kanske du är intresserad av att börja med den första delen av denna handledning, som förklarar syntaxen och grundläggande användning. VPR. Nåväl, låt oss börja.
Sök i Excel med flera kriterier
Funktion VPR i Excel är ett riktigt kraftfullt verktyg för att utföra sökningar efter ett visst värde i en databas. Det finns dock en betydande begränsning - dess syntax låter dig söka efter endast ett värde. Vad händer om du vill söka efter flera villkor? Du hittar lösningen nedan.
Exempel 1: Sök efter 2 olika kriterier
Anta att vi har en lista med beställningar och vi vill hitta Mängd varor (Antal), baserat på två kriterier – Kundnamn (Kund) и Nöjdhet (Produkt). Saken kompliceras av det faktum att var och en av köparna beställde flera typer av varor, vilket framgår av tabellen nedan:
regelbunden funktion VPR kommer inte att fungera i det här scenariot eftersom det kommer att returnera det första värdet den hittar som matchar det givna uppslagsvärdet. Till exempel om du vill veta mängden av en vara Sötsaker'beställt av köparen Jeremy Hill, skriv följande formel:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– denna formel kommer att returnera resultatet 15motsvarande produkten äpplen, eftersom det är det första värdet som matchar.
Det finns en enkel lösning – skapa en extra kolumn där du kan kombinera alla önskade kriterier. I vårt exempel är dessa kolumner Kundnamn (Kund) и Nöjdhet (Produkt). Glöm inte att den sammanslagna kolumnen alltid måste vara kolumnen längst till vänster i sökområdet, eftersom det är den vänstra kolumnen som funktionen VPR tittar upp när du letar efter ett värde.
Så du lägger till en hjälpkolumn i tabellen och kopierar följande formel över alla dess celler: =B2&C2. Om du vill att strängen ska vara mer läsbar kan du separera de kombinerade värdena med ett mellanslag: =B2&» «&C2. Efter det kan du använda följande formel:
=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;ЛОЖЬ)
Var är cellen B1 innehåller det sammanlänkade värdet av argumentet letauppvärde (lookup_value) och 4 – Argument kolumnindex (kolumnnummer), dvs numret på den kolumn som innehåller data som ska hämtas.
Exempel 2: VLOOKUP av två kriterier med tabell som visas på ett annat ark
Om du behöver uppdatera huvudtabellen (Huvudtabellen) genom att lägga till data från den andra tabellen (Lookup table), som finns på ett annat ark eller i en annan Excel-arbetsbok, kan du samla det önskade värdet direkt i formeln som du infogar in i huvudtabellen.
Som i föregående exempel behöver du en extra kolumn i Lookup-tabellen med de kombinerade värdena. Denna kolumn måste vara kolumnen längst till vänster i sökintervallet.
Så formeln med VPR kan vara så här:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Här innehåller kolumn B och C kundnamn respektive produktnamn samt länken Beställningar!$A&$2:$D$2 definierar en tabell att slå upp i ett annat blad.
För att göra formeln mer läsbar kan du ge vyintervallet ett namn, och då kommer formeln att se mycket enklare ut:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
För att formeln ska fungera måste värdena i kolumnen längst till vänster i tabellen du tittar på kombineras på exakt samma sätt som i sökkriterierna. I figuren ovan kombinerade vi värdena u2bu2band satte ett mellanslag mellan dem, på samma sätt som du behöver göra i det första argumentet för funktionen (BXNUMX& "" & CXNUMX).
Kom ihåg! Funktion VPR begränsad till 255 tecken kan den inte söka efter ett värde som är mer än 255 tecken långt. Tänk på detta och se till att längden på det önskade värdet inte överstiger denna gräns.
Jag håller med om att det inte är den mest eleganta och inte alltid acceptabel lösningen att lägga till en extra kolumn. Du kan göra detsamma utan hjälpkolumnen, men det skulle kräva en mycket mer komplex formel med en kombination av funktioner INDEX (INDEX) och MATCH (MER EXPONERAD).
Vi extraherar värdena 2:a, 3:e, etc. med hjälp av VLOOKUP
Det vet du redan VPR kan endast returnera ett matchande värde, närmare bestämt det första som hittades. Men vad händer om detta värde upprepas flera gånger i den visade arrayen och du vill extrahera den andra eller tredje av dem? Tänk om alla värden? Problemet verkar komplicerat, men lösningen finns!
Anta att en kolumn i tabellen innehåller namnen på kunderna (kundnamn), och den andra kolumnen innehåller produkterna (produkt) som de köpte. Låt oss försöka hitta den 2:a, 3:e och 4:e varan som köpts av en viss kund.
Det enklaste sättet är att lägga till en extra kolumn före kolumnen Köparens namn och fyll den med kundnamn med repetitionsnumret för varje namn, till exempel, John Doe 1, John Doe 2 etc. Vi kommer att göra susen med numrering med funktionen COUNTIF (COUNTIF), givet att kundnamnen finns i kolumn B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Därefter kan du använda den normala funktionen VPRför att hitta önskad beställning. Till exempel:
- hitta 2: a vara beställd av kunden Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - hitta 3: a vara beställd av kunden Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Faktum är att du kan ange en cellreferens som uppslagsvärde istället för text, som visas i följande figur:
Om du bara letar efter 2-e upprepning kan du göra det utan hjälpkolumnen genom att skapa en mer komplex formel:
=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;ИСТИНА);"")
I denna formel:
- $ F $ 2 – en cell som innehåller köparens namn (det är oförändrat, observera – länken är absolut);
- $ B $ – kolumn Köparens namn;
- Table4 – Ditt bord (den här platsen kan också vara ett vanligt utbud);
- $ C16 – slutcellen i din tabell eller ditt område.
Denna formel hittar bara det andra matchande värdet. Om du behöver extrahera de återstående upprepningarna, använd den tidigare lösningen.
Om du behöver en lista över alla matchningar – funktionen VPR detta är inte en hjälpreda, eftersom det bara returnerar ett värde åt gången – punkt. Men Excel har en funktion INDEX (INDEX), som lätt kan klara av denna uppgift. Hur en sådan formel kommer att se ut får du lära dig i följande exempel.
Hämta alla repetitioner av önskat värde
Som nämnts ovan VPR kan inte extrahera alla dubbletter av värden från det skannade området. För att göra detta behöver du en lite mer komplex formel, uppbyggd av flera Excel-funktioner, som t.ex INDEX (INDEX), SMALL (LITEN) och RAD (LINJE)
Till exempel hittar formeln nedan alla upprepningar av värdet från cell F2 i intervallet B2:B16 och returnerar resultatet från samma rader i kolumn 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));"")}
Ange denna matrisformel i flera intilliggande celler, till exempel cellerna F4: F8som visas i figuren nedan. Antalet celler måste vara lika med eller större än det högsta möjliga antalet upprepningar av det sökta värdet. Glöm inte att klicka Ctrl + Skift + Enterför att ange matrisformeln korrekt.
Om du är intresserad av att förstå hur det fungerar, låt oss dyka in i detaljerna i formeln lite:
Del 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – jämför värdet i cell F2 med vart och ett av värdena i intervallet B2:B16. Om en matchning hittas, då uttrycket STRÄNG(C2:C16)-1 returnerar numret på motsvarande rad (värde -1 låter dig inte inkludera rubrikraden). Om det inte finns några matchningar, funktionen IF (IF) returnerar en tom sträng.
Funktionsresultat IF (IF) det kommer att finnas en sådan horisontell array: {1,"",3,"",5,"","","","","","",12,"","",""}
Del 2:
ROW()-3
СТРОКА()-3
Här är funktionen RAD (LINE) fungerar som en extra räknare. Eftersom formeln kopieras in i cellerna F4:F9 subtraherar vi talet 3 från funktionsresultat till att få värde 1 i cellen F4 (rad 4, subtrahera 3) för att få 2 i cellen F5 (rad 5, subtrahera 3) och så vidare.
Del 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funktion SMALL (LITEN) returnerar n-åh det minsta värdet i datamatrisen. I vårt fall bestäms vilken position (från den minsta) som ska returneras av funktionen RAD (LINE) (se del 2). Alltså för en cell F4 fungera SMALL({matris},1) återgår 1: a (minsta) arrayelement, dvs 1. För cell F5 återgår 2: a det minsta elementet i arrayen, det vill säga 3Etc.
Del 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))
Funktion INDEX (INDEX) returnerar helt enkelt värdet för en specifik cell i en array C2:C16. För cell F4 fungera INDEX($C$2:$C$16) kommer att återvända äpplenFör F5 fungera INDEX($C$2:$C$16) kommer att återvända Sötsaker' och så vidare.
Del 5:
IFERROR()
ЕСЛИОШИБКА()
Slutligen lägger vi formeln inuti funktionen FEL (IFERROR), eftersom du sannolikt inte kommer att vara nöjd med felmeddelandet #AT (#N/A) om antalet celler som formeln kopieras till är mindre än antalet dubbletter av värden i intervallet som visas.
XNUMXD sökning efter känd rad och kolumn
Att utföra en XNUMXD sökning i Excel innebär att söka efter ett värde efter ett känt rad- och kolumnnummer. Med andra ord extraherar du cellvärdet i skärningspunkten mellan en viss rad och kolumn.
Så låt oss vända oss till vår tabell och skriva en formel med en funktion VPR, som kommer att hitta information om kostnaden för citroner som säljs i mars.
Det finns flera sätt att utföra en XNUMXD sökning. Kolla in alternativen och välj det som passar dig bäst.
VLOOKUP och MATCH funktioner
Du kan använda en massa funktioner VPR (VLOOKUP) och MER EXPONERAD (MATCH) för att hitta värdet i skärningspunkten mellan fälten Nöjdhet (sträng) och Månad (kolumn) för arrayen i fråga:
=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);ЛОЖЬ)
Formeln ovan är en vanlig funktion VPR, som letar efter en exakt matchning av värdet "Citroner" i cellerna A2 till A9. Men eftersom du inte vet vilken kolumn marsförsäljningen ligger i, kommer du inte att kunna ställa in kolumnnumret för det tredje funktionsargumentet. VPR. Istället används funktionen MER EXPONERADför att definiera denna kolumn.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Översatt till mänskligt språk betyder denna formel:
- Vi letar efter karaktärerna "Mar" – argument letauppvärde (uppslagsvärde);
- Titta i celler från A1 till I1 – argument lookup_array (uppslagsmatris);
- Returnerar exakt matchning – argument matchningstyp (matchningstyp).
Använda 0 i det tredje argumentet säger du funktioner MER EXPONERAD leta efter det första värdet som exakt matchar värdet du letar efter. Detta motsvarar värdet FALSK (FALSKT) för det fjärde argumentet VPR.
Så här kan du skapa en tvåvägssökningsformel i Excel, även känd som tvådimensionell sökning eller dubbelriktad sökning.
SUMPRODUKT funktion
Funktion SUMPRODUCT (SUMPRODUKT) returnerar summan av produkterna för de valda arrayerna:
=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)
INDEX och MATCH funktioner
I nästa artikel kommer jag att förklara dessa funktioner i detalj, så för nu kan du bara kopiera den här formeln:
=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))
Namngivna områden och korsningsoperatören
Om du inte gillar alla dessa komplexa Excel-formler kanske du gillar det här visuella och minnesvärda sättet:
- Välj tabellen, öppna fliken Formler (Formler) och klicka Skapa från urval (Skapa från urval).
- Markera rutorna Översta raden (på raden ovan) och Vänster kolumn (i kolumnen till vänster). Microsoft Excel kommer att tilldela namn till intervallen från värdena i den översta raden och vänstra kolumnen i ditt kalkylblad. Nu kan du söka med dessa namn direkt utan att skapa formler.
- Skriv i valfri tom cell =radnamn kolumnnamn, till exempel så här:
=Citroner Mar
… eller tvärtom:
=Mar Citroner
Kom ihåg att rad- och kolumnnamnen måste separeras med ett mellanslag, vilket i det här fallet fungerar som korsningsoperatorn.
När du anger ett namn kommer Microsoft Excel att visa ett verktygstips med en lista över matchande namn, precis som när du anger en formel.
- Presse ange och kontrollera resultatet
I allmänhet, vilken av ovanstående metoder du än väljer, kommer resultatet av en tvådimensionell sökning att vara detsamma:
Använda flera VLOOKUPs i en formel
Det händer att huvudtabellen och uppslagstabellen inte har en enda kolumn gemensam, och detta hindrar dig från att använda den vanliga funktionen VPR. Det finns dock en annan tabell som inte innehåller den information vi är intresserade av utan har en gemensam kolumn med huvudtabellen och uppslagstabellen.
Låt oss ta en titt på följande exempel. Vi har en huvudtabell med en kolumn SKU (ny), där du vill lägga till en kolumn med motsvarande priser från en annan tabell. Dessutom har vi 2 uppslagstabeller. Den första (uppslagstabell 1) innehåller uppdaterade nummer SKU (ny) och produktnamn, och den andra (uppslagstabell 2) – produktnamn och gamla nummer SKU (gammal).
För att lägga till priser från den andra uppslagstabellen till huvudtabellen måste du utföra en åtgärd som kallas dubbel VPR eller kapslad VPR.
- Skriv en funktion VPR, som hittar produktnamnet i tabellen Uppslagstabell 1med hjälp av SKU, som önskat värde:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Här Ny_SKU – namngiven räckvidd $A:$B i bordet Uppslagstabell 1, 2 – detta är kolumn B, som innehåller varornas namn (se bilden ovan)
- Skriv en formel för att infoga priser från en tabell Uppslagstabell 2 baserat på välkända produktnamn. För att göra detta, klistra in formeln du skapade tidigare som uppslagsvärde för den nya funktionen VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Här Pris – namngiven räckvidd $ A: $ C i bordet Uppslagstabell 2, 3 är kolumn C som innehåller priser.
Bilden nedan visar resultatet som returneras av formeln vi skapade:
Dynamisk substitution av data från olika tabeller med VLOOKUP och INDIRECT
Låt oss först klargöra vad vi menar med uttrycket "Dynamisk ersättning av data från olika tabeller" för att se till att vi förstår varandra korrekt.
Det finns situationer när det finns flera ark med data i samma format, och det är nödvändigt att extrahera den nödvändiga informationen från ett visst ark, beroende på värdet som anges i en given cell. Jag tror att det är lättare att förklara detta med ett exempel.
Föreställ dig att du har försäljningsrapporter för flera regioner med samma produkter och i samma format. Du vill hitta försäljningssiffror för en specifik region:
Om du bara har två sådana rapporter kan du använda en skamligt enkel formel med funktioner VPR и IF (IF) för att välja önskad rapport att söka:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Var:
- $D$2 är en cell som innehåller namnet på produkten. Observera att vi använder absoluta referenser här för att undvika att ändra uppslagsvärdet när du kopierar formeln till andra celler.
- $ D3 är en cell med namnet på regionen. Vi använder en absolut kolumnreferens och en relativ radreferens eftersom vi planerar att kopiera formeln till andra celler i samma kolumn.
- FL_Sales и CA_Försäljning – namnen på tabellerna (eller namngivna intervall) som innehåller motsvarande försäljningsrapporter. Du kan naturligtvis använda de vanliga arknamnen och cellintervallsreferenser till exempel 'FL Sheet'!$A$3:$B$10, men namngivna intervall är mycket bekvämare.
Men när det finns många sådana tabeller, funktionen IF är inte den bästa lösningen. Istället kan du använda funktionen INDIREKT (INDIREKT) för att returnera önskat sökintervall.
Som du säkert vet, funktionen INDIREKT används för att returnera en länk som ges av en textsträng, vilket är precis vad vi behöver nu. Så ersätt djärvt uttrycket i formeln ovan med funktionen IF att länka till funktion INDIREKT. Här är en kombination VPR и INDIREKT fungerar bra med:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Var:
- $D$2 – detta är en cell med namnet på produkten, den är oförändrad på grund av den absoluta länken.
- $ D3 är cellen som innehåller den första delen av regionnamnet. I vårt exempel, detta FL.
- _Försäljning – den gemensamma delen av namnet för alla namngivna intervall eller tabeller. När det kombineras med värdet i cell D3, bildar det det fullständiga namnet på det obligatoriska intervallet. Nedan följer några detaljer för dig som är ny på funktionen INDIREKT.
Hur INDIRECT och VLOOKUP fungerar
Låt mig först påminna dig om syntaxen för funktionen INDIREKT (INDIREKT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Det första argumentet kan vara en cellreferens (stil A1 eller R1C1), ett intervallnamn eller en textsträng. Det andra argumentet bestämmer vilken typ av länk som finns i det första argumentet:
- A1om argumentet är SANT KOD (TRUE) eller inte specificerat;
- R1C1, Om FSOM E (FALSK).
I vårt fall har länken stilen A1, så att du kan utelämna det andra argumentet och fokusera på det första.
Så låt oss gå tillbaka till våra försäljningsrapporter. Om du kommer ihåg är varje rapport en separat tabell som finns på ett separat ark. För att formeln ska fungera korrekt måste du namnge dina tabeller (eller intervall), och alla namn måste ha en gemensam del. Till exempel så här: CA_Försäljning, FL_Försäljning, TX_Försäljning och så vidare. Som du kan se finns "_Sales" i alla namn.
Funktion INDIREKT kopplar samman värdet i kolumn D och textsträngen "_Sales", och talar därmed VPR i vilken tabell man ska söka. Om cell D3 innehåller värdet "FL", kommer formeln att söka i tabellen FL_Försäljning, om "CA" – i tabellen CA_Försäljning och så vidare.
Resultatet av funktionerna VPR и INDIREKT kommer att vara följande:
Om data finns i olika Excel-böcker måste du lägga till namnet på boken före det namngivna intervallet, till exempel:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Om funktionen INDIREKT hänvisar till en annan arbetsbok, den arbetsboken måste vara öppen. Om den stängs kommer funktionen att rapportera ett fel. #REF! (#SSYL!).