Hur VLOOKUP från ett annat ark i Google Sheets
du kan låsa upp den fulla potentialen i Vlookup, (vertikal sökning), i Google Sheets genom att använda den för att ringa data över olika kalkylblad och arbetsböcker.
Vlookup-funktionen är en otroligt kraftfull funktion som kan göra arbetstimmar till bara några sekunder. Medan funktionen är användbar för dataanalys på ett enstaka ark kan du ytterligare förbättra Vlookup användbarhet genom att ringa data över olika kalkylblad.
med den här funktionen kan du slå upp och hämta specifika värden från stora mängder data snabbt och effektivt.
att kunna använda den över flera kalkylblad gör det lättare att hålla dina data rena och uppdateras automatiskt när det finns ändringar mellan ark.
denna artikel omfattar:
VLOOKUP-formeln
VLOOKUP-syntaxen är ganska enkel men kan användas i mycket komplex grad.
som ”vertikal” i namnet antyder söker Vlookup den vänstra kolumnen i det etablerade intervallet efter en radmatchning och returnerar värdet i cellen som identifieras av indexet.
den söker i en kolumn och returnerar ett värde från den matchande raden, vanligtvis från en annan kolumn.
en av de största svagheterna i Vlookup är att det inte kan söka efter en matchning i en kolumn till höger och ta tillbaka ett resultat från en kolumn till vänster. Med andra ord, Vlookup kan bara titta eller sväng höger.
så här ser formeln ut när den delas upp efter Del:
=Vlookup(search_key,range,index,is_sorted)
Låt oss titta på varje komponent individuellt:
- =Vlookup (): detta är själva funktionen utan några parametrar. Det är det som säger att Google Sheets ska agera.
- search_key: denna parameter definierar vilken information vi vill matcha. Det kan vara ett namn, ett tal, ett booleskt värde eller något annat. Det kan vara ett statiskt värde som vi har definierat eller det kan vara ett relativt värde som lagras i en cell. Om vi ställer in detta som ” A2 ”söker det efter värdet i cellen”A2”. Om vi ställer in det för ”true” kommer det att matcha textsträngen ”true.”
- intervall: Detta berättar Vlookup-funktionen var man ska leta efter en matchning och det intervall där värdet det ska returnera finns. Om vi söker efter en värdematchning i kolumn B och vill returnera ett värde i kolumnerna C eller D, ställer vi in intervallet som B:D.
- index: den här parametern berättar för Vlookup vilket kolumnvärde som ska returneras. Indexet är relativt intervallet, inte arket. Därför är det ett tal istället för en bokstav som kolumner brukar definieras. Om vår sökning matchar i kolumn B och returnerar ett värde från kolumn C är indexvärdet 2. Om vi returnerar kolumn D från en kolumn B-matchning är indexvärdet 3.
- is_sorted: den här parameterns namn är inte lika tydligt som det returnerar exakta matchningar när den är inställd på ”false” och närmaste match när den är inställd på ”true.”True är inställt som standard, men false rekommenderas i de flesta användningar.
låt oss nu titta på allt tillsammans:
=Vlookup(A2,A2:B5,2,false)
använda VLOOKUP i samma arbetsbok och i en annan arbetsbok
medan vi för det mesta använder VLOOKUP-formeln i Google Sheets på samma ark, många gånger kan du behöva använda den för att VLOOKUP mellan två ark i samma arbetsbok eller till och med över den olika arbetsboken.
du kanske till exempel vill hämta data från specifika objekt i ett kalkylblad, medan uppslagsdata finns i ett annat ark eller en annan arbetsbok.
hur du använder Vlookup-funktionen för att ta in data från ett annat ark är något annorlunda när du arbetar i samma arbetsbok eller en annan arbetsbok.
formeln för samma arbetsbok Vlookup ser ut som:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
Lägg märke till att det finns en”!”mellan arknamnet och cellområdet. Också, det finns inga citat runt intervallet på samma-arbetsbok Vlookup.
i vårt test vill vi ställa in intervallet till A3:B6 på vårt ark med namnet ”Called.”Vi söker efter en matchning i kolumn A och returnerar värdet i kolumn B.
nedan är formeln som gör detta:
=Vlookup(A2,Called!A2:B5,2,false)
ovanstående formel hämtar värdet från den andra kolumnen från arket med namnet ”Called” i det aktuella arket.
Låt oss nu titta på ett exempel där vi måste hämta värdet från en annan Google Sheets-arbetsbok
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
den här versionen använder ett nytt kommando som heter ”importrange()”.
kommandots syntax ber dig att definiera webbadressen till arbetsboken du importerar data från, definiera det specifika arket och ange intervallet.
formeln bryts ner så här:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}: i citat lägger du till webbadressen till den Google Sheets-fil du vill komma åt. Exempel: ”https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
- {ark namn}!{cel range}: Ställ in detta på samma sätt som du konfigurerade det i samma arbetsboksmetod. Observera dock att den här gången finns citat runt arknamnet och cellområdet.
titta Nu på det med ett mervärde och alla värden läggs till :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
för att upprepa finns det tre viktiga värden som du måste ställa in i en VLOOKUP-referens i flera ark:
- arbetsbokens URL {sheetsURL}
- Bladsida {arknamn}
- cellområde {cellområde}:
kör genom Vlookup-processen
Låt oss förstå processen för hur VLOOKUP fungerar när vi refererar till andra ark eller arbetsböcker.
VLOOKUP från ett annat kalkylblad i samma arbetsbok
Låt oss använda ett enkelt fall av produktinventarier för att visa hur Vlookup fungerar.
vi har en serie med fyra produkter i det här exemplet: Gadgets, Gizmos, Thingamabobs och Widgets. För närvarande har vi inga Gizmos i lager. Vårt första ark, ”Active” ger oss en lista över alla våra produkter och berättar om en produkt finns i lager:
vi har också ett andra ark som vi har namngett” Called ” som visar hur många av varje produkt vi har i lager.
låt oss nu säga att vi vill ha fliken ”aktiv” visa hur många produkter som finns i lager, men vi vill bara oroa oss för att uppdatera en kalkylblad.
- ange vår Vlookup-formel i den översta cellen-i vårt fall använder vi
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
- dra ned den nedre högra indikatorn för att tillämpa den för varje produkt (blå pil).
nu drar vår ”aktiva” sida in värdena från den” kallade ” sidan. En av fördelarna med att använda Vlookup är att produkterna fortfarande matchar om båda kalkylarken listar dem i en annan ordning.
det kommer inte heller att kastas om en produkt saknas i det refererade kalkylbladet.
VLOOKUP från ett annat kalkylblad i en annan arbetsbok
processen att referera till data från en annan arbetsbok är lite mer komplicerad.
vi använder samma produktutbud av Prylar, Gizmos, Thingamabobs och Widgets från föregående exempel.
den här gången vill vi ta in samma information i ett ark som heter ”utanför” i en annan arbetsbok.
vi vill ta in informationen från” Called ” – arket i den andra arbetsboken:
låt oss nu säga att vi vill ha fliken ”utanför” visa hur många produkter som finns i lager, men vi vill bara oroa oss för att uppdatera den ”kallade” kalkylbladet i den ursprungliga arbetsboken.
ange vår Vlookup-formel i den översta cellen-i vårt fall använder vi
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
nu hänvisar vår andra arbetsbok till den första arbetsbokens inventeringsantal:
detta kan vara mycket användbart för någon som analyserar informationen i det första kalkylbladet utan risk för fel på originaldata.
det är också mycket användbart för att få in en bråkdel av informationen i ett kalkylblad för enklare analys i en annan. Kommandot Vlookup gör det lättare att bara ta med den information du vill ha.
några Tips när du använder VLOOKUP för att referera till ett annat ark/arbetsbok
här är några tips att tänka på när du refererar till ett annat ark eller arbetsböcker i formeln:
var specifik om intervallet
Vlookup-funktionen kan vara mycket prestationshungrig och få en arbetsboks prestanda att komma till en genomsökning.
du kan undvika långsam prestanda genom att vara specifik med de intervall du refererar till.
- istället för att ringa hela kolumner som ” A:B ” referera till de specifika start-och slutcellerna som ”A1:B1000”. Detta skär ner på hur mycket arbete Google Sheets behöver göra för att få in samma mängd information.
- om du söker i kolumn A efter information och tar tillbaka resultatet i kolumn D, använd en referens som” A1:D1000 ”istället för”A1:F1000”. Det finns ingen anledning att referera kolumnerna E och F om i intervallet om de inte används.
detta är särskilt viktigt när du ringer information mellan olika arbetsböcker. När du gör en Vlookup med flera arbetsböcker kräver det internetbandbredd för att överföra data mellan de två.
använd villkorliga uttalanden för att förhindra onödiga samtal
ett annat sätt du kan förhindra avmattning med Vlookup över flera ark är att använda ett villkorat uttalande för att avgöra om Google Sheets ska köra Vlookup alls.
om det till exempel finns information på arket använder du Vlookup-samtalet som säger att du inte behöver köra det, använd det till din fördel.
i vårt produktexempel listar arket ”aktivt” om en produkt finns i lager eller inte. Eftersom vi vet att en produkt är slut i lager behöver vi inte använda Vlookup för att definiera lagerantalet.
för detta använder vi funktionen ”=if ()”. Den här funktionen frågar om ett givet villkor är sant eller falskt och gör sedan något annat för varje fall. Syntaxen ser ut så här:
=if(logical_expression, value_if_true, value_if_false)
i ett enkelt användningsfall kan vi använda det för att avgöra om värdet i cell A1 är större än värdet i cell B1. Så uttrycket A1> B1 skulle se ut så här:
=if(A1>B1, "A1 is greater", "B1 is greater")
formeln returnerar texten ”A1 är större” om A1 är det större antalet och ”B1 är större” om B1 är det större antalet.
när det gäller vårt produktblad, om värdet ”i lager” är ”nej” vill vi inte köra Vlookup. Så vi ställer in vårt if-uttalande så här:
- logical_expression: B2= ” Ja ”- detta kommer att köra” värdet om sant ”om data i cell B2 är”ja”.
- Value_if_true: LETARAD(A2, Kallas!A2: B5, 2, false) –detta kommer att köra Vlookup om det logiska uttrycket returnerar true.
- Value_if_false: ”slut i lager” – detta returnerar texten” slut i lager ”om värdet i cell B2 är något annat än” ja.”
om vi sätter ihop allt kommer det ut så här:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
Lägg märke till hur cell C3 nu säger ”slut i lager” istället för att returnera värdet ”0”. I det här fallet undvek vi att köra en sökning eftersom vi inte behövde ta tillbaka data.
medan du matar in mer information i Google Sheets för att beräkna resultat skapar det mindre arbete för programmet.
det är mycket mindre arbete att köra många ”if-kontroller” än en enda sökning. Att använda den här tekniken hjälper dig att påskynda Google Sheets och förbättra prestanda.
se till att du har behörighet
av uppenbara säkerhetsskäl låter Google Sheets dig inte hämta data från en annan arbetsbok om du inte har behörighet att göra det.
för att referera till en arbetsbok från en annan med Vlookup måste du antingen vara skaparen av båda eller ha behörighet att använda båda. Du kan läggas till som auktoriserad användare antingen via konto eller via en delningsadress.
att använda Vlookup för att referera till information över olika ark och arbetsböcker är ett otroligt kraftfullt verktyg att ha till ditt förfogande.
kommandot är särskilt användbart vid användning av flera ark eftersom det kommer att återspegla eventuella ändringar som gjorts i originalarket över alla refererade ark.