Sådan LLOOKUP fra et andet ark i Google Sheets
du kan låse det fulde potentiale af Vlookup, (lodret opslag), i Google Sheets ved at bruge det til at kalde data på tværs af forskellige regneark og projektmapper.
VLOOKUP-funktionen er en utrolig kraftfuld funktion, der kan gøre arbejdstimer til få sekunder. Mens funktionen er nyttig til dataanalyse på et enkeltark, kan du yderligere forbedre vlookups anvendelighed ved at kalde data på tværs af forskellige regneark.
med denne funktion kan du hurtigt og effektivt søge og hente specifikke værdier fra store datamængder.
at kunne bruge det på tværs af flere regneark gør det lettere at holde dine data rene og opdateres automatisk, når der er ændringer mellem ark.
denne artikel dækker:
VLOOKUP-formlen
VLOOKUP-syntaksen er ret ligetil, men kan bruges i meget kompleks grad.
som “lodret” i sit navn antyder, søger Vlookup i kolonnen længst til venstre i det etablerede område for en rækkematch og returnerer værdien i cellen identificeret af indekset.
den søger i en kolonne og returnerer en værdi fra den matchende række, normalt fra en anden kolonne.
en af de største svagheder i Vlookup er, at den ikke kan søge efter et match i en kolonne til højre og bringe et resultat tilbage fra en kolonne til venstre. Med andre ord kan Vlookup kun se eller dreje til højre.
Sådan ser formlen ud, når den opdeles efter Del:
=Vlookup(search_key,range,index,is_sorted)
lad os se på hver komponent individuelt:
- =Vlookup (): dette er selve funktionen uden nogen parametre. Det er det, der fortæller Google Sheets at handle.
- search_key: denne parameter definerer, hvilke oplysninger vi søger at matche. Det kan være et navn, et tal, en boolsk værdi eller noget andet. Det kan være en statisk værdi, vi har defineret, eller det kan være en relativ værdi, der er gemt i en celle. Hvis vi indstiller dette som” A2″, vil det søge efter værdien i celle”A2″. Hvis vi indstiller det til” sandt”, matcher det tekststrengen ” sandt.”
- rækkevidde: Dette fortæller VLOOKUP-funktionen, hvor man skal kigge efter en kamp, og det interval, hvor den værdi, den skal returnere, er placeret. Hvis vi søger efter en værdimatch i kolonne B og vil returnere en værdi i kolonne C eller D, indstiller vi området som B:D.
- indeks: denne parameter fortæller Vlookup, hvilken kolonneværdi der skal returneres. Indekset er i forhold til området, ikke arket. Derfor er det et tal i stedet for et bogstav som kolonner er normalt defineret. Hvis vores søgning matcher i kolonne B og returnerer en værdi fra kolonne C, er indeksværdien 2. Hvis vi returnerer kolonne D fra en kolonne B-kamp, er indeksværdien 3.
- is_sorted: denne parameters navn er ikke så klart, da det returnerer nøjagtige matches, når det er indstillet til “false” og det nærmeste match, når det er indstillet til “true.”True er indstillet som standard, men false anbefales i de fleste anvendelser.
lad os nu se på det hele sammen:
=Vlookup(A2,A2:B5,2,false)
brug af VLOOKUP i den samme projektmappe og i en anden projektmappe
mens vi for det meste bruger VLOOKUP-formlen i Google Sheets på det samme ark, kan du ofte bruge den til at VLOOKUP mellem to ark i den samme projektmappe eller endda på tværs af den forskellige projektmappe.
du kan f.eks. hente dataene fra bestemte elementer i et regneark, mens opslagsdataene er i et andet ark eller en anden projektmappe.
hvordan du bruger funktionen LOPSLAG til at hente data fra et andet ark, er noget anderledes, når du arbejder i den samme projektmappe eller en anden projektmappe.
formlen for den samme projektmappe Vlookup ligner:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
Bemærk, at der er en”!”mellem arknavnet og celleområdet. Også, der er ingen citater omkring området på en samme-projektmappe Vlookup.
i vores test ønsker vi at indstille området til A3:B6 på vores ark med navnet “Called.”Vi søger efter et match I kolonne A og returnerer værdien i kolonne B.
nedenfor er formlen, der vil gøre dette:
=Vlookup(A2,Called!A2:B5,2,false)
ovenstående formel henter værdien fra den anden kolonne fra arket med navnet “kaldet” i det aktuelle ark.
lad os nu se på et eksempel, hvor vi skal hente værdien fra en anden Google Sheets-projektmappe
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
denne version bruger en ny kommando kaldet “importrange()”.
denne kommandos syntaks beder dig om at definere URL ‘ en til den projektmappe, du importerer data fra, definere det specifikke ark og indstille området.
formlen bryder sammen som denne:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}: i citater skal du tilføje URL ‘ en til den Google Sheets-fil, du vil have adgang til. Eksempel: “https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
- {ark navn}!{cel range}: Indstil dette på samme måde som du konfigurerede det i samme projektmappemetode. Bemærk dog, at denne gang er der Citater omkring arknavnet og celleområdet.
se nu på det med en merværdi og alle værdier tilføjet :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
for at gentage er der tre vigtige værdier, du skal indstille i en cross-sheet Vlookup reference:
- arbejdsbog URL {sheetsURL}
- Arkside {arknavn}
- celleområde {celleområde}:
kører gennem Vlookup-processen
lad os forstå processen med, hvordan VLOOKUP fungerer, når der henvises til andre ark eller arbejdsbøger.
VLOOKUP fra et andet regneark i samme projektmappe
lad os bruge et simpelt tilfælde af produktbeholdning til at vise, hvordan Vlookup fungerer.
vi har en fire-produkt lineup i dette eksempel: Gadgets, Dimser, Thingamabobs, og kontroller. I øjeblikket har vi ingen dimser på lager. Vores første ark,” Aktiv ” giver os en liste over alle vores produkter og fortæller os, om et produkt er på lager:
vi har også et andet ark, vi har navngivet “kaldet”, der viser, hvor mange af hvert produkt vi har på lager.
lad os nu sige, at vi vil have fanen “Aktiv”, hvor mange produkter der er på lager, men vi vil kun bekymre os om at opdatere en regnearksside.
- Indtast vores VLOOKUP-formel i den øverste celle — i vores tilfælde bruger vi
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
- træk indikatoren nederst til højre ned for at anvende den for hvert produkt (blå pil).
nu trækker vores” aktive “side værdierne fra den” kaldte ” side. En af fordelene ved at bruge Vlookup er, at produkterne stadig matcher, hvis begge regneark viser dem i en anden rækkefølge.
det vil heller ikke blive smidt ud, hvis et produkt mangler fra det refererede regneark.
VLOOKUP fra et andet regneark i en anden projektmappe
processen med at henvise til data fra en anden projektmappe er lidt mere kompliceret.
vi bruger det samme produktsortiment af Gadgets, Dimser, Thingamabobs og kontroller fra det foregående eksempel.
denne gang vil vi bringe de samme oplysninger ind i et ark kaldet “udenfor” i en anden projektmappe.
vi ønsker at bringe oplysningerne fra det” kaldte ” ark i den anden projektmappe:
lad os nu sige, at vi vil have fanen “udenfor”, hvor mange produkter der er på lager, men vi vil kun bekymre os om at opdatere siden “kaldet” regneark i den originale projektmappe.
Indtast vores VLOOKUP formel i den øverste celle-i vores tilfælde bruger vi
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
nu henviser vores anden projektmappe til den første projektmappes beholdningstælling:
dette kan være yderst nyttigt for nogen, der analyserer oplysningerne i det første regneark uden risiko for fejl i de originale data.
det er også meget nyttigt for at bringe i en brøkdel af oplysningerne i et regneark for lettere analyse i en anden. Kommandoen Vlookup gør det lettere at bringe det kun de ønskede oplysninger.
nogle tip, når du bruger VLOOKUP til at henvise til et andet ark/projektmappe
her er nogle tip, du skal huske på, når du henviser til et andet ark eller projektmapper i formlen:
vær specifik om området
Vlookup-funktionen kan være meget præstationshungrende og få en projektmappes ydeevne til at komme til en gennemgang.
du kan undgå langsom ydeevne ved at være specifik med de områder, du refererer til.
- i stedet for at kalde hele kolonner som “A:B ” henvis til de specifikke start-og slutceller som “A1:B1000”. Dette skærer ned på, hvor meget arbejde Google Sheets skal gøre for at få den samme mængde information.
- hvis du søger efter oplysninger i kolonne A og bringer resultatet tilbage i kolonne D, skal du bruge en reference som “A1:D1000” i stedet for “A1:F1000”. Der er ingen grund til at henvise til kolonner E og F, hvis de er i området, hvis de ikke bruges.
dette er især vigtigt, når du ringer til oplysninger mellem forskellige arbejdsbøger. Når du laver en cross-projektmappe Vlookup, det kræver internet båndbredde til at overføre data mellem de to.
brug betingede udsagn til at forhindre unødvendige opkald
en anden måde, du kan forhindre afmatning med Cross-sheet Vlookup, er at bruge en betinget erklæring til at afgøre, om Google Sheets overhovedet skal køre Vlookup.
hvis der for eksempel er oplysninger på det ark, du bruger VLOOKUP-opkaldet, der fortæller dig, at der ikke er behov for at køre det, skal du bruge det til din fordel.
i vores produkteksempel viser det “aktive” ark, om et produkt er på lager eller ej. Da vi ved, at et produkt ikke er på lager, behøver vi ikke bruge Vlookup til at definere lagerantalet.
til dette bruger vi funktionen “=if ()”. Denne funktion spørger, om en given betingelse er sand eller falsk, og gør derefter noget andet for hvert tilfælde. Syntaksen ser sådan ud:
=if(logical_expression, value_if_true, value_if_false)
i en simpel brugssag kan vi bruge den til at bestemme, om værdien i celle A1 er større end værdien i celle B1. Så udtrykket A1> B1 ville se sådan ud:
=if(A1>B1, "A1 is greater", "B1 is greater")
formlen returnerer teksten “A1 er større”, hvis A1 er det større antal og “B1 er større”, hvis B1 er det større antal.
i tilfælde af vores produktark, hvis værdien “på lager” er “nej”, ønsker vi ikke at køre Vlookup. Så vi oprettede vores if-erklæring som denne:
- logical_ekspression: B2=” ja “— dette vil køre” værdi hvis sand”, hvis dataene i celle B2 er”ja”.
- Value_if_true: Vlookup (A2, Kaldet!A2: B5, 2, false) –dette kører Vlookup, hvis det logiske udtryk returnerer true.
- Værdi_if_false: “udsolgt” – dette returnerer teksten” udsolgt”, hvis værdien i celle B2 er andet end ” ja.”
hvis vi sætter det hele sammen, kommer det ud til at se sådan ud:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
bemærk, hvordan celle C3 nu siger “udsolgt” i stedet for at returnere værdien “0”. I dette tilfælde undgik vi at køre en Vlookup, fordi vi ikke behøvede at bringe data tilbage.
mens du indtaster flere oplysninger i Google Sheets for at beregne resultater, skaber det mindre arbejde for programmet.
det er meget mindre arbejde at køre mange “if checks” end en enkelt Vlookup. Brug af denne teknik hjælper dig med at fremskynde Google Sheets og forbedre ydeevnen.
sørg for, at du har tilladelse
af åbenlyse sikkerhedsmæssige årsager lader Google Sheets dig ikke hente data fra en anden projektmappe, medmindre du har tilladelse til det.
for at henvise til en projektmappe fra en anden med Vlookup skal du enten være skaberen af begge eller have tilladelse til at bruge begge. Du kan tilføjes som en autoriseret bruger enten via konto eller via en delings-URL.
brug af Vlookup til reference information på tværs af forskellige ark og arbejdsbøger er et utroligt kraftfuldt værktøj at have til din rådighed.
kommandoen er særlig nyttig ved brug på tværs af ark, fordi den afspejler eventuelle ændringer, der er foretaget i det originale ark på tværs af alle refererede ark.