Anvendelse og definition af denne Excel -funktion
VLOOKUP er en Excel -funktion, som brugeren kan søge efter og evaluere tabelindhold med. Denne funktion er tilgængelig i versioner fra Excel 2007 til Windows og Mac.
Hvad er VLOOKUP?
De mulige anvendelser af VLOOKUP skal forklares her ved hjælp af et eksempel: I denne er du en stor fan af litteratur og har derfor oprettet dit eget Excel -regneark, hvor du omhyggeligt kan sortere de bøger, du har samlet. Hvert værk indtastes med oplysninger om følgende kategorier:
-
forfatter
-
titel
-
Sidenummer
-
Udgivelsesår
Nu vil du gerne give en ven et bogtip til at tage med på dit næste møde. Desværre kan du kun tænke på forfatteren, ikke titlen på bogen. Det er her, VLOOKUP spiller ind, da den kan bruge denne inputværdi til at smide den information, du leder efter, i et hug.
Hvordan bruges VLOOKUP?
Inden man overhovedet tænker på at formulere formler, bør det bestemmes, hvor inputfeltet og de forskellige outputfelter vil blive placeret senere. For at gøre dette er det fornuftigt at oprette en separat tabel, der i første omgang er tom og dermed giver plads til de nævnte oplysninger. Hvis du designer denne nye tabel baseret på eksemplet på den eksisterende tabel, har du en tidsbesparende fordel senere.
På dette grundlag kan VLOOKUP -formlen enten oprettes manuelt eller automatisk genereres af Excel. For begyndere er det værd at bruge sidstnævnte fremgangsmåde for gradvist at kende strukturen og effekten af formlen. For at gøre dette vælges knappen til "Indsæt funktion" på fanen "Formler". VLOOKUP er skjult i det vindue, der åbnes. Efter bekræftelse åbnes der igen et vindue, hvor de fire parametre i formlen kan udfyldes. Disse er:
-
Søgekriterium
-
matrix
-
Kolonneindeks
-
Område_reference
Det rå udkast til formlen ser derfor sådan ud:
= VLOOKUP (søgekriterium, matrix, kolonneindeks, område_link)
og i en mulig applikation som denne:
= OPLYSNING (H3; A3: E40; 5)
Søgekriterium
For at funktionen ved, hvilken værdi der skal bruges som udgangspunkt, noteres den linje, der blev valgt som inputfelt to trin tidligere i feltet "Søgekriterium". I vores eksempel er navnet på bogforfatteren "Phillip Pulmann" angivet der. Dette gør formlen fleksibel og skal ikke justeres igen, så snart den indtastede værdi ændres.
matrix
Inputfeltet "Matrix" beskriver tabellen, hvor de oplysninger, der skal sendes, kan findes. Denne særlige matrix indeholder således også kolonnerne for bogtitlen, sidetallet og udgivelsesåret.
Matrixen vælges fuldstændigt en gang uden overskrifterne fra øverste venstre til nederste højre margen. På denne måde ved Excel, hvilket indhold der skal tages i betragtning ved evaluering.
Kolonneindeks
Inputfeltet for "kolonneindeks" beder brugeren om at definere kolonnen i matrixen, hvor kun den ønskede værdi er angivet. Tildelingen af kolonnerne er nummereret kronologisk. Det betyder, at den første kolonne i tabellen modtager værdien 1, den anden værdien 2 osv. I vores eksempel svarer dette til kolonneindeks 1 for forfatteren, kolonneindeks 2 for titlen, kolonneindeks 3 for sidetallet og kolonneindeks 4 for udgivelsesåret.
For at holde tabellen så fleksibel som muligt, kan kolonneoverskriften forbindes i stedet for nummeret. Dette har den fordel, at formlen også uden problemer kan overføres til andre rækker, da kolonneoverskriften kan tilpasses fleksibelt hver gang.
Opmærksomhed: VLOOKUP læser matrixen fra venstre mod højre, hvorfor kolonneindekset skal placeres til højre for kolonnen for søgekriteriet for at blive taget i betragtning af funktionen!
Område_reference
Parameteren "Range_Lookup" fuldender VLOOKUP -formlen ved at angive den nøjagtighed, hvormed tabellen evalueres. Den adskiller sig imidlertid fra de tidligere nævnte komponenter i formlen, fordi den er valgfri. Hvis værdien 0 er angivet for "forkert", søger Excel kun efter den værdi, der blev angivet som søgekriteriet. Med værdien 1 for "sand" fortsætter søgningen imidlertid efter indlysende værdier, hvis den nøjagtige værdi ikke kunne findes.
Det er valgfrit at angive denne parameter, fordi værdien 1 er angivet som standard. Denne indstilling vil være nyttig senere i den avancerede VLOOKUP med flere søgekriterier.
Sammenlægningen
Så snart alle de nødvendige parametre er konfigureret, kan VLOOKUP bruges. Efter at have indtastet søgekriteriet og bekræftet funktionen, vises den værdi, du leder efter, på den linje, der er defineret som et outputfelt.
I vores eksempel vises bogtitlen "Det gyldne kompas" nu, hvilket svarer til den angivne forfatter. For hurtigt at finde ud af sidetal og udgivelsesår, behøver der ikke gøres mere end at trække den eksisterende VLOOKUP -formel ind i de efterfølgende celler. Dette er så let, fordi kolonneindekset i VLOOKUP er blevet linket til kolonneoverskriften i den første tabel, og den anden tabel er også struktureret i samme rækkefølge.
I tilfælde af at tabellerne skulle afvige fra hinanden, eller at der opstår en fejl trods alt, kan VLOOKUP -formlen også ændres manuelt. For at gøre dette skal det næstsidste ciffer for kolonneindekset matches med kolonnen med den nye værdi, der skal udlæses.
VLOOKUP med flere søgekriterier
Ofte sker det, at et enkelt søgekriterium ikke er nok til nøjagtigt at evaluere en stor Excel -tabel. Så giver det mening at køre VLOOKUP med flere søgekriterier. For at gøre dette skal den eksisterende formel suppleres med en ekstra IF -funktion. På denne måde kan der tages højde for op til otte forskellige søgekriterier under ansøgningen.
VLOOKUP i flere Excel -regneark
Hvis søgekriteriet ikke kun kan findes i en tabel, men muligvis også i en anden, kan VLOOKUP -formlen justeres i overensstemmelse hermed. Til dette skal både en if -funktion og en FEJL -funktion placeres foran den eksisterende formel. Fem parametre er nødvendige for dette:
-
Søgekriterium
-
Matrix1 og Matrix 2
-
Kolonneindeks1 og kolonneindeks2
Resultatet ser sådan ud:
= HVIS (FEJL (VLOOKUP (søgekriterium, matrix1, kolonne-indeks1, 0));
VLOOKUP (søgekriterium; matrix2; kolonneindeks2,0); VLOOKUP (søgekriterium; matrix1; kolonneindeks1;))
og i en mulig applikation som denne:
= HVIS (FEJL (VISNING (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Søgekriteriet bruges til at indsætte den værdi, der skal søges efter i de to tabeller. Matrix1 og Matrix2 definerer de respektive celleområder i de to tabeller. Kolonneindeks1 og kolonneindeks2 bruges til at definere mere detaljeret, hvilke kolonner i de respektive tabeller der skal søges i.
Hvis den værdi, du leder efter, forekommer i begge tabeller, sender Excel resultatet fra den første tabel. Men hvis værdien ikke findes i nogen af de to tabeller, vises der en fejlmeddelelse. Fordelen ved formlen er, at de to lister ikke behøver at have samme struktur eller være af samme størrelse.
Tildel værdier til kategorier ved hjælp af VLOOKUP
En ekstra funktion af VLOOKUP gør det muligt at opdele de anførte værdier automatisk i bogstaver og prædikater efter eget valg. I vores tidligere eksempel skulle der indsættes en ekstra tabellkolonne for bogtypen. Bøgerne med en længde på op til 50 sider bør falde ind i novellens genre, mens bøger fra 51 til 150 sider er tildelt novellen og fra 151 sider til romanen. For at gøre dette muligt kræves der ingen yderligere formel i VLOOKUP, kun brugen af krøllede parenteser “{}”. Den færdige formel ser sådan ud:
= OPLYSNING (B1; {1. "Novelle"; 51. "Novella"; 151. "Roman"}; 2)
Indholdet af de krøllede parenteser angiver en matrix, der definerer området for en respektive bogtype. Tildelingen af sidelængden til den relevante slægt er derfor placeret inden for de krøllede parenteser. Formlen bruger par af værdier, hver adskilt af et punkt. Matrixen {1. "Novelle"; 51. "Novella"; 151. "Roman"} læses som følger:
"Fra 1 viser en novelle, fra 51 viser en novelle, fra 151 viser en roman."
Denne matrix kan let tilpasses forskellige opgaver. Dette vedrører på den ene side størrelsen og antallet af matricer samt deres betegnelse. Så det er muligt at udsende strenge eller tal som et resultat i stedet for individuelle bogstaver. Alt du skal gøre er at justere bogstaverne i formlen.
VLOOKUP på tværs af flere regneark
En anden funktion i VLOOKUP giver brugerne mulighed for at linke indhold, der er placeret på forskellige regneark. I vores eksempel kan denne mulighed være nyttig, når oplysninger først sorteres i forskellige regneark og derefter opdateres i en oversigtstabel.
Forestil dig, at du udover dine bøger også viser dine indsamlede film i et Excel -regneark. Du kombinerer derefter begge samlinger i et stort bord.
Fordelen ved denne procedure ligger ikke kun i den øgede rækkefølge, men også i at undgå potentielle fejl. Hvis du vil oprette en ny post eller opdatere en eksisterende, behøver du ikke søge i den store tabel, men kan i stedet få adgang til de mindre. Værdierne overføres derefter automatisk til den opsummerende Excel -tabel. Dette gør omskrivning i det store bord overflødigt, hvilket i bedste fald undgår et uheldigt træk og en efterfølgende kæde af fejlmeddelelser.
Hvordan ser formlen ud?
Denne funktion gøres mulig igen ved at indsætte en anden formel. Mens søgning med flere kriterier krævede en ekstra IF -formel, kræver arbejde med flere regneark en INDIREKT formel. Dette gør det muligt at specificere et område fra et andet regneark til VLOOKUP -matrixen.
= VLOOKUP (søgekriterium; INDIRECT (matrix); kolonneindeks; område_link)
Opmærksomhed: Denne formel fungerer kun, hvis de enkelte tabeller i de forskellige ark har de samme navne som kolonneoverskrifterne i den generelle tabel. Hele tabeller kan navngives i "Navnefelt" øverst til venstre over cellegitteret. Tabeller, der allerede er navngivet, kan ses med tastekombinationen Ctrl + F3.
Håndtering af nye fejlmeddelelser
Arbejde med sammenkædede Excel -tabeller kan føre til uønskede problemer. Dette omfatter især output af forkerte værdier. I tilfælde af at den forkerte værdi 0 udsendes, er der et lille problem i Excel -indstillingerne, som hurtigt kan rettes.
Den almindelige fejlmeddelelse #NV er derimod en bevidst funktion af VLOOKUP, som angiver for brugeren, at den krævede værdi ikke er tilgængelig. Denne note kan designes anderledes ved hjælp af en formel.
VLOOKUP - en oversigt
VLOOKUP er en nyttig Excel -funktion, der kan bruges til at søge og evaluere tabeller. Dens fordele er tydelige i den brugervenlige og fleksible applikation. På denne måde kan alle, der regelmæssigt arbejder med Excel -tabeller, drage fordel af funktionen. Det være sig den private indsamler, der skaber sine egne små borde, eller den store virksomhed, der behandler betydeligt mere omfattende datasæt.
Hvis du på den anden side stadig har ubesvarede anmodninger, som VLOOKUP ikke kunne imødekomme, kan du se frem til en ekstra Excel-mulighed: Microsoft har siden begyndelsen af 2022-2023 tilbudt Excel 365-brugere den nye XLOOKUP. Dette bygger på VLOOKUP's kompetencer og supplerer dem med yderligere, nogle gange endda enklere funktioner. Derfor åbner en ny rutine inden for datavurdering også på dette tidspunkt.
