Søknad og definisjon av denne Excel -funksjonen
VLOOKUP er en Excel -funksjon som brukeren kan søke etter og evaluere tabellinnhold med. Denne funksjonen er tilgjengelig i versjoner fra Excel 2007 for Windows og Mac.
Hva er VLOOKUP?
Den mulige bruken av VLOOKUP skal forklares her ved hjelp av et eksempel: I denne er du en stor fan av litteratur og har derfor laget ditt eget Excel -regneark der du nøye kan sortere bøkene du har samlet. Hvert verk er lagt inn med informasjon om følgende kategorier:
-
forfatter
-
tittel
-
Side nummer
-
Utgivelsesår
Nå vil du gi en venn et boktips du kan ta med deg på ditt neste møte. Dessverre kan du bare tenke på forfatteren, ikke tittelen på boken. Det er her VLOOKUP spiller inn, ettersom den kan bruke denne inngangsverdien til å kaste ut informasjonen du leter etter i ett slag.
Hvordan brukes VLOOKUP?
Før du tenker på å formulere formler, bør det bestemmes hvor inndatafeltet og de forskjellige utdatafeltene vil bli plassert senere. For å gjøre dette er det fornuftig å lage et eget bord som i utgangspunktet er tomt og dermed gir plass til informasjonen som er nevnt. Hvis du designer denne nye tabellen basert på eksemplet på den eksisterende tabellen, vil du ha en tidsbesparende fordel senere.
På dette grunnlaget kan VLOOKUP -formelen enten opprettes manuelt eller automatisk genereres av Excel. For nybegynnere er det verdt å bruke sistnevnte tilnærming for gradvis å bli kjent med strukturen og effekten av formelen. For å gjøre dette, er knappen for "Sett inn funksjon" valgt på "Formler" -fanen. VLOOKUP er skjult i vinduet som åpnes. Etter bekreftelse åpnes et vindu igjen der de fire parameterne i formelen kan fylles ut. Disse er:
-
Søkekriterium
-
matrise
-
Kolonneindeks
-
Area_reference
Det rå utkastet til formelen ser derfor slik ut:
= VLOOKUP (søkekriterium, matrise, kolonneindeks, områdekobling)
og i en mulig applikasjon som denne:
= VISNING (H3; A3: E40; 5)
Søkekriterium
Slik at funksjonen vet hvilken verdi som skal brukes som utgangspunkt, er linjen som ble valgt som inndatafelt to trinn tidligere notert i "Søkekriterium" -feltet. I vårt eksempel er navnet på bokforfatteren "Phillip Pulmann" angitt der. Dette gjør formelen fleksibel og trenger ikke å justeres igjen så snart den angitte verdien endres.
matrise
Inndatafeltet "Matrise" beskriver tabellen der informasjonen som skal sendes, kan bli funnet. Denne spesielle matrisen inneholder dermed også kolonnene for boktittelen, sidetallet og utgivelsesåret.
Matrisen er fullstendig valgt en gang uten overskriftene øverst til venstre til nederst til høyre. På denne måten vet Excel hvilket innhold som må tas med i vurderingen.
Kolonneindeks
Inndatafeltet for "kolonneindeksen" ber brukeren definere kolonnen i matrisen der bare den søkte verdien er oppført. Tildelingen av kolonnene er nummerert kronologisk. Dette betyr at den første kolonnen i tabellen mottar verdien 1, den andre verdien 2, etc. I vårt eksempel tilsvarer dette kolonneindeks 1 for forfatteren, kolonneindeks 2 for tittelen, kolonneindeks 3 for sidetallet og kolonneindeks 4 for utgivelsesåret.
For å holde tabellen så fleksibel som mulig, kan kolonneoverskriften kobles i stedet for tallet. Dette har fordelen av at formelen også kan overføres til andre rader uten problemer, ettersom kolonneoverskriften kan tilpasses fleksibelt hver gang.
Merk følgende: VLOOKUP leser matrisen fra venstre til høyre, og derfor må kolonneindeksen plasseres til høyre for kolonnen for søkekriteriet for å bli tatt hensyn til av funksjonen!
Area_reference
Parameteren "Range_Lookup" fullfører VLOOKUP -formelen ved å spesifisere nøyaktigheten som tabellen evalueres med. Den skiller seg imidlertid fra de tidligere nevnte komponentene i formelen fordi den er valgfri. Hvis verdien 0 er angitt for "feil", søker Excel bare etter verdien som ble angitt som søkekriteriet. Med verdien 1 for "true" fortsetter imidlertid søket etter åpenbare verdier hvis den eksakte verdien ikke ble funnet.
Det er valgfritt å angi denne parameteren fordi verdien 1 er angitt som standard. Denne innstillingen vil være nyttig senere i den avanserte VLOOKUP med flere søkekriterier.
Sammenslåingen
Så snart alle nødvendige parametere er konfigurert, kan VLOOKUP brukes. Etter at du har angitt søkekriteriet og bekreftet funksjonen, vises verdien du leter etter på linjen som er definert som et utdatafelt.
I vårt eksempel vises nå boktittelen “The Golden Compass”, som tilsvarer forfatteren som er angitt. For å raskt finne ut sidetallet og publiseringsåret, trenger du ikke gjøre noe mer enn å dra den eksisterende VLOOKUP -formelen til de påfølgende cellene. Dette er så enkelt fordi kolonneindeksen til VLOOKUP har blitt koblet til kolonneoverskriften i den første tabellen og den andre tabellen også er strukturert i samme rekkefølge.
I tilfelle tabellene skulle skille seg fra hverandre eller at det oppstår en feil til tross for alt, kan VLOOKUP -formelen også endres manuelt. For å gjøre dette må det nest siste sifferet for kolonneindeksen matches med kolonnen med den nye verdien som skal sendes ut.
VLOOKUP med flere søkekriterier
Ofte skjer det at et enkelt søkekriterium ikke er nok til å evaluere en stor Excel -tabell nøyaktig. Da er det fornuftig å kjøre VLOOKUP med flere søkekriterier. For å gjøre dette må den eksisterende formelen suppleres med en ekstra IF -funksjon. På denne måten kan det tas hensyn til opptil åtte forskjellige søkekriterier under søknaden.
VLOOKUP i flere Excel -regneark
Hvis søkekriteriet ikke bare finnes i en tabell, men muligens også i en annen, kan VLOOKUP -formelen justeres deretter. For dette må både en if -funksjon og en FEIL -funksjon plasseres foran den eksisterende formelen. Fem parametere kreves for dette:
-
Søkekriterium
-
Matrise1 og Matrix 2
-
Kolonneindeks1 og kolonneindeks2
Resultatet ser slik ut:
= HVIS (FEIL (VISNING (søkekriterium, matrise1, kolonne-indeks1, 0));
VLOOKUP (søkekriterium; matrise2; kolonneindeks2,0); VLOOKUP (søkekriterium; matrise1; kolonneindeks1;))
og i en mulig applikasjon som denne:
= HVIS (FEIL (VISNING (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Søkekriteriet brukes til å sette inn verdien som skal søkes etter i de to tabellene. Matrise1 og Matrix2 definerer de respektive celleområdene i de to tabellene. Kolonneindeks1 og kolonneindeks2 brukes til å definere mer detaljert hvilke kolonner i de respektive tabellene som skal søkes etter.
Hvis verdien du leter etter forekommer i begge tabellene, vil Excel sende resultatet fra den første tabellen. Men hvis verdien ikke finnes i noen av de to tabellene, vises en feilmelding. Fordelen med formelen er at de to listene ikke trenger å ha samme struktur eller ha samme størrelse.
Tilordne verdier til kategorier ved å bruke VLOOKUP
En tilleggsfunksjon av VLOOKUP gjør det mulig å dele oppførte verdier automatisk i bokstaver og predikater etter eget valg. I vårt forrige eksempel bør en ekstra tabellkolonne settes inn for boktypen. Bøkene med en lengde på opptil 50 sider bør falle inn i sjangeren til novellen, mens bøker fra 51 til 150 sider er tildelt novellen og fra 151 sider til romanen. For å gjøre dette mulig, kreves ingen tilleggsformel i VLOOKUP, bare bruk av krøllete parenteser «{}». Den ferdige formelen ser slik ut:
= OPPLYSNING (B1; {1. "Novell"; 51. "Novella"; 151. "Roman"}; 2)
Innholdet i de krøllete parentesene indikerer en matrise som definerer området til en respektive boktype. Tildelingen av sidelengden til den riktige slekten er derfor plassert innenfor de krøllete parentesene. Formelen bruker par verdier, hver atskilt med et punkt. Matrisen {1. "Novell"; 51. "Novella"; 151. "Roman"} leses som følger:
"Fra 1 viser en novelle, fra 51 viser en novelle, fra 151 viser en roman."
Denne matrisen kan enkelt tilpasses forskjellige oppgaver. Dette gjelder på den ene siden størrelsen og antallet på matrisene, så vel som deres betegnelse. Så det er mulig å skrive ut strenger eller tall som et resultat i stedet for individuelle bokstaver. Alt du trenger å gjøre er å justere bokstavene i formelen.
VLOOKUP på tvers av flere regneark
En annen funksjon av VLOOKUP lar brukerne koble til innhold som ligger på forskjellige regneark. I vårt eksempel kan dette alternativet være nyttig når informasjon først sorteres i forskjellige regneark og deretter oppdateres i en oppsummeringstabell.
Tenk deg at du i tillegg til bøkene dine også viser de innsamlede filmene i et Excel -regneark. Deretter kombinerer du begge samlingene i ett stort bord.
Fordelen med denne prosedyren ligger ikke bare i den økte rekkefølgen, men også i å unngå potensielle feil. Hvis du vil opprette en ny oppføring eller oppdatere en eksisterende, trenger du ikke søke i den store tabellen, men kan i stedet få tilgang til de mindre. Verdiene blir deretter automatisk overført til den oppsummerende Excel -tabellen. Dette gjør omskrivning i det store bordet overflødig, noe som i beste fall unngår et uheldig trekk og en påfølgende kjetting av feilmeldinger.
Hvordan ser formelen ut?
Denne funksjonen gjøres mulig igjen ved å sette inn en annen formel. Mens søk med flere kriterier krevde en ekstra IF -formel, krever arbeid med flere regneark en INDIREKT formel. Dette gjør at et område fra et annet regneark kan spesifiseres for VLOOKUP -matrisen.
= VLOOKUP (søkekriterium; INDIRECT (matrise); kolonneindeks; range_link)
Merk følgende: Denne formelen fungerer bare hvis de enkelte tabellene i de forskjellige bladene har samme navn som kolonneoverskriftene i den generelle tabellen. Hele tabeller kan navngis i "Navn -feltet" øverst til venstre over celle -rutenettet. Tabeller som allerede er navngitt, kan vises med tastekombinasjonen Ctrl + F3.
Å håndtere nye feilmeldinger
Arbeid med sammenkoblede Excel -tabeller kan føre til uønskede problemer. Dette inkluderer spesielt utmatingen av feil verdier. I tilfelle feil verdi 0 sendes ut, er det et lite problem i Excel -innstillingene, som kan løses raskt.
Den vanlige feilmeldingen #NV, derimot, er en bevisst funksjon av VLOOKUP, som indikerer for brukeren at den nødvendige verdien ikke er tilgjengelig. Denne notatet kan utformes annerledes ved hjelp av en formel.
VLOOKUP - en oversikt
VLOOKUP er en nyttig Excel -funksjon som kan brukes til å søke og evaluere tabeller. Fordelene er tydelige i den brukervennlige og fleksible applikasjonen. På denne måten kan alle som regelmessig jobber med Excel -tabeller dra nytte av funksjonen. Det være seg den private samleren som lager sine egne små bord, eller det store selskapet som behandler betydelig mer omfattende datasett.
Hvis du derimot fortsatt har ubesvarte forespørsler som VLOOKUP ikke kunne imøtekomme, kan du se frem til et ekstra Excel-alternativ: Microsoft har tilbudt Excel 365-brukere den nye XLOOKUP siden begynnelsen av 2022-2023. Dette bygger på kompetansen til VLOOKUP og supplerer dem med flere, noen ganger enda enklere, funksjoner. Derfor åpner også en ny rutine i dataevaluering på dette tidspunktet.
