Vigtigste Software Sådan opretter du en Excel-formel til venstre-opslag ved hjælp af VLOOKUP
Software

Sådan opretter du en Excel-formel til venstre-opslag ved hjælp af VLOOKUP

Sådan opretter du en Excel-formel til venstre-opslag ved hjælp af VLOOKUP
Anonim

Kombiner Excel's VLOOKUP og VÆLG for at oprette en venstre opslag formel

  • Dokumenter
  • Præsentationer
  • Desktop Publishing
  • Grafisk design
  • Databaser
  • Animation og video
  • byTed fransk

    En regnearksekspert, der er master i Excel og Google Sheets.

    Excel's VLOOKUP-funktion bruges til at finde og returnere information fra en datatabel baseret på en søgningsværdi, du vælger.

    VLOOKUP kræver typisk, at opslagets værdi er i kolonnen til venstre i datatabellen, og funktionen returnerer et andet datafelt, der er placeret i den samme række til højre for denne værdi.

    Ved at kombinere VLOOKUP med VÆLG-funktionen kan der imidlertid oprettes en venstre opslagformel, der:

    • Tillader, at opslagets værdi er fra en hvilken som helst kolonne i datatabellen
    • Returnerer oplysninger, der findes i en hvilken som helst kolonne til venstre for opslagværdien

    Disse instruktioner gælder for Excel-versioner 2019, 2016, 2013, 2010 og Excel til Office 365.

    01

    af 05

    Tutorial: Brug af VLOOKUP- og VÆLG-funktionerne i en venstre opslagformel

    Hvis du vil oprette den venstre opslagformel, der ses i eksemplet, skal du bruge formlen:

    = LOPSLAG ($ D $ 2, VÆLG ({1, 2}, $ F: $ F, $ D: $ D), 2, FALSK)

    I dette eksempel gør formlen det muligt at finde den del, der leveres af de forskellige virksomheder, der er anført i kolonne 3 i datatabellen.

    Jobbet med VÆLG-funktionen i formlen er at narre VLOOKUP til at tro, at kolonne 3 er kolonne 1. Som et resultat kan virksomhedens navn bruges som opslagværdi til at finde navnet på den del, der leveres af hver virksomhed.

    Indtast vejledningsdata

    1. Indtast overskriften leverandør i celle D1 .
    2. Indtast overskriften Del i celle E1 .
    3. Indtast datatabellen set på billedet ovenfor i celler D4 til F9
    4. Rækker 2 og 3 efterlades tomme for at imødekomme søgekriterierne og den venstre opslagformel, der er oprettet under denne tutorial

    Åbn VLOOKUP-dialogboksen

    Selvom det er muligt at indtaste formlen direkte i celle F1 i regnearket, har mange mennesker vanskeligheder med formelens syntaks.

    I dette tilfælde er det sikrere at bruge dialogboksen VLOOKUP. Næsten alle Excel's funktioner har en dialogboks, der giver dig mulighed for at indtaste hver af funktionens argumenter på en separat linje.

    1. Klik på celle E2 i regnearket. E2 er det sted, hvor resultaterne af den venstre opslagformel vises.
    2. Klik på fanen Formler i båndet.
    3. Klik på indstillingen Opslag og reference i båndet for at åbne rullelisten med funktion.
    4. Klik på VLOOKUP på listen for at få vist funktionens dialogboks.

    02

    af 05

    Indtastning af argumenter i VLOOKUP-dialogboksen

    En funktions argumenter er de værdier, der bruges af funktionen til at beregne et resultat.

    I en funktions dialogboks er navnet på hvert argument på en separat linje efterfulgt af et felt, hvor en værdi skal indtastes.

    Indtast følgende værdier for hver af VLOOKUPs argumenter på den rigtige linje i dialogboksen som vist på det medfølgende billede.

    Opslagets værdi

    Opslagets værdi er det informationsfelt, der bruges til at søge i tabellen array. VLOOKUP returnerer et andet datafelt fra den samme række som opslagværdien.

    Dette eksempel bruger en cellehenvisning til det sted, hvor virksomhedsnavnet indtastes i regnearket. Fordelen med dette er, at det gør det nemt at ændre virksomhedsnavnet uden at redigere formlen.

    1. Klik på lookup_value- linjen i dialogboksen.
    2. Klik på celle D2 for at tilføje denne cellehenvisning til linjen lookup_value .
    3. Tryk på F4- tasten på tastaturet for at gøre cellehenvisningen absolut - $ D $ 2.

    Absolutte cellehenvisninger bruges til opslagværdi og tabelarray-argumenter for at forhindre fejl, hvis opslagsformlen kopieres til andre celler i regnearket.

    Gå ind i VÆLG-funktionen

    Tabelarray-argumentet er en blok af sammenhængende data, hvorfra specifik information hentes.

    Normalt ser VLOOKUP kun til højre for argumentet om opslagværdi for at finde data i tabellen array. For at få det til at se til venstre, skal VLOOKUP blive narret ved at omarrangere kolonnerne i tabellarrayet ved hjælp af funktionen VÆLG.

    I denne formel udfører CHOOSE-funktionen to opgaver:

    1. Det opretter en tabellarray, der kun er to kolonner bred (kolonner D og F).
    2. Det ændrer rækkefølgen fra højre til venstre for kolonnerne i tabellarrayet, så kolonne F kommer først og kolonne D er nummer to.

    Indtastning af funktioner

    Når man indtaster funktioner manuelt, skal hver af funktionens argumenter adskilles med et komma.

    1. I dialogboksen VLOOKUP- funktion skal du klikke på linjen Table_array .
    2. Gå ind i følgende VÆLG- funktion: VÆLG ({1, 2}, $ F: $ F, $ D: $ D)

    Kolonneindeksnummer

    Normalt angiver kolonneindeksnummeret, hvilken kolonne i tabellarrayet indeholder de data, du er ude efter. I denne formel henviser det imidlertid til rækkefølgen af ​​kolonner, der er indstillet med funktionen VÆLG.

    CHOOSE-funktionen opretter en tabellarray, der er to kolonner bred med kolonne F først efterfulgt af kolonne D. Da den søgte information - delnavnet - er i kolonne D, skal værdien af ​​kolonneindeksargumentet indstilles til 2.

    1. Klik på Col_index_num linjen i dialogboksen.
    2. Skriv en 2 i denne linje.

    Områdeopslag

    VLOOKUPs Range_lookup-argument er en logisk værdi (kun SAND eller FALSE), der angiver, om du vil have VLOOKUP til at finde et nøjagtigt eller et omtrentlig match til opslagværdien.

    • Hvis SAND, eller hvis dette argument er udeladt, vender VLOOKUP enten et nøjagtigt match til Lookup_value, eller hvis et nøjagtigt match ikke findes, returnerer VLOOKUP den næste største værdi. For at formlen kan gøre dette, skal dataene i den første kolonne i Table_array sorteres i stigende rækkefølge.
    • Hvis FALSE, bruger VLOOKUP kun et nøjagtigt match til Lookup_value. Hvis der er to eller flere værdier i den første kolonne i Table_array, der matcher opslagets værdi, bruges den første fundne værdi. Hvis der ikke findes et nøjagtigt match, returneres en # N / A-fejl.

    I denne tutorial, da vi leder efter et bestemt delnavn, er Range_lookup indstillet til False, så kun nøjagtige matchninger returneres af formlen.

    1. Klik på Range_lookup- linjen i dialogboksen.
    2. Skriv ordet Falsk i denne linje for at indikere, at vi ønsker, at VLOOKUP skal returnere et nøjagtigt match for de data, vi søger.
    3. Klik på OK for at udføre den venstre opslagformel og lukke dialogboksen.
    4. Da vi endnu ikke har indtastet firmanavnet i celle D2, vises der en # N / A-fejl i celle E2.

    03

    af 05

    Returnering af data med den venstre opslagsformel

    For at finde ud af, hvilke firmaer der leverer hvilke dele, skal du indtaste et firmanavn i celle D2 og trykke på ENTER- tasten på tastaturet.

    Delnavnet vises i celle E2.

    1. Klik på celle D2 i dit regneark.
    2. Skriv Gadgets Plus i celle D2, og tryk på ENTER- tasten på tastaturet.
    3. Teksten "Gadgets" - den del, der leveres af firmaet Gadgets Plus - skal vises i celle E2.

    Test opslagsformlen yderligere ved at indtaste andre firmanavne i celle D2, og det tilsvarende delnavn skal vises i celle E2.

    Hvis der vises en fejlmeddelelse som # N / A i celle E2, skal du kontrollere for stavefejl i celle D2.

    04

    af 05

    Oprettelse af en tabel med to kolonner

    Syntaks for CHOOSE-funktionen er:

    = VÆLG (Indeksnummer, værdi1, værdi2, … værdi254)

    Funktionen VÆLG returnerer typisk en værdi fra listen over værdier (Værdi1 til Værdi254) baseret på det indtastede indeksnummer.

    Hvis indeksnummeret er 1, returnerer funktionen Value1 fra listen; hvis indeksnummeret er 2, returnerer funktionen Value2 fra listen og så videre.

    Når der indtastes flere indeksnumre, returnerer funktionen flere værdier i en hvilken som helst ønsket rækkefølge. At få VÆLG for at returnere flere værdier gøres ved at oprette en matrix.

    Hvis du vil indtaste en matrix, skal du omringe de indtastede numre med krøllede seler eller parenteser. To numre indtastes for indeksnummeret: {1, 2} .

    Det skal bemærkes, at VÆLG ikke er begrænset til at oprette en to-kolonnetabel. Ved at inkludere et yderligere tal i arrayet - såsom {1, 2, 3} - og et ekstra interval i værdiargumentet opretter CHOOSE en tre-kolonnetabel.

    Yderligere kolonner giver dig mulighed for at returnere forskellige oplysninger med den venstre opslagformel ved at ændre VLOOKUPs kolonneindeksnummerargument til nummeret på kolonnen, der indeholder den ønskede information.

    05

    af 05

    Ændring af rækkefølgen af ​​kolonner med VÆLG-funktionen

    I den VÆLG-funktion, der bruges i denne formel:

    VÆLG ({1, 2}, $ F: $ F, $ D: $ D)

    området for kolonne F er anført før kolonne D.

    Da CHOOSE-funktionen indstiller VLOOKUPs tabellarray (kilden til data for den funktion) skiftes rækkefølgen af ​​kolonnerne i CHOOSE-funktionen videre til VLOOKUP.

    Hvad angår VLOOKUP, er tabellen array kun to kolonner brede med kolonne F til venstre og kolonne D til højre. Da kolonne F indeholder navnet på det firma, vi vil søge efter, og da kolonne D indeholder delnavne, kan VLOOKUP udføre sine regelmæssige opslagsopgaver ved at finde data, der er placeret til venstre for opslagværdien.

    Som et resultat kan VLOOKUP bruge firmanavnet til at finde den del, de leverer.