Huvud Programvara Hur man skapar en Excel-vänstersökningsformel med VLOOKUP
Programvara

Hur man skapar en Excel-vänstersökningsformel med VLOOKUP

Hur man skapar en Excel-vänstersökningsformel med VLOOKUP
Anonim

Kombinera Excel's VLOOKUP och VÄLJ för att skapa en vänstersökningsformel

  • Dokument
  • presentationer
  • Desktop Publishing
  • Grafisk design
  • databaser
  • Animering och video
  • byTed French

    En kalkylarksexpert som är befälhavare på Excel och Google Sheets.

    Excel's VLOOKUP-funktion används för att hitta och returnera information från en datatabell baserat på ett uppslagningsvärde som du väljer.

    Vanligtvis kräver VLOOKUP att uppslagningsvärdet finns i kolumnen längst till vänster i datatabellen, och funktionen returnerar ett annat fält med data som finns i samma rad till höger om detta värde.

    Men genom att kombinera VLOOKUP med CHOOSE-funktionen kan en formel för vänsteruppslag skapas som:

    • Tillåter att sökvärdet kommer från vilken kolumn som helst i datatabellen
    • Returnerar information som finns i valfri kolumn till vänster om sökvärdet

    Dessa anvisningar gäller Excel-versionerna 2019, 2016, 2013, 2010 och Excel för Office 365.

    01

    av 05

    Handledning: Använda VLOOKUP- och VÄLJ-funktionerna i en vänstersökningsformel

    För att skapa den vänstra uppslagningsformeln som visas i exempelbilden använder du formeln:

    = LETARAD ($ D $ 2, VÄLJ ({1, 2}, $ F: $ F, $ D: $ D), 2, FALSE)

    I detta exempel gör formeln det möjligt att hitta den del som tillhandahålls av de olika företagen som anges i kolumn 3 i datatabellen.

    Jobbet med CHOOSE-funktionen i formeln är att lura VLOOKUP att tro att kolumn 3 är kolumn 1. Som ett resultat kan företagets namn användas som uppslagningsvärde för att hitta namnet på den del som levereras av varje företag.

    Ange handledningsdata

    1. Ange rubriken Leverantör i cell D1 .
    2. Ange rubriken Del i cell E1 .
    3. Ange datatabellen i bilden ovan i cellerna D4 till F9
    4. Raderna 2 och 3 lämnas tomma för att tillgodose sökkriterierna och den vänstra sökformeln som skapades under denna tutorial

    Öppna dialogrutan VLOOKUP

    Även om det är möjligt att skriva formeln direkt i cell F1 i kalkylbladet har många människor svårt med formelens syntax.

    I det här fallet är det säkrare att använda dialogrutan VLOOKUP. Nästan alla Excel-funktioner har en dialogruta som låter dig ange var och en av funktionens argument på en separat rad.

    1. Klicka på cell E2 i kalkylbladet. E2 är den plats där resultaten av den vänstra uppslagningsformeln visas.
    2. Klicka på fliken Formler i bandet.
    3. Klicka på alternativet Sök & referens i bandet för att öppna rullgardinsmenyn.
    4. Klicka på VLOOKUP i listan för att visa funktionens dialogruta.

    02

    av 05

    Ange argument i dialogrutan VLOOKUP

    En funktions argument är värdena som används av funktionen för att beräkna ett resultat.

    I en funktions dialogruta finns namnet på varje argument på en separat rad följt av ett fält för att ange ett värde.

    Ange följande värden för vart och ett av VLOOKUPs argument på rätt rad i dialogrutan som visas i den bifogade bilden.

    Sökvärde

    Sökvärdet är informationsfältet som används för att söka i tabelluppsättningen. VLOOKUP returnerar ett annat datafält från samma rad som sökvärdet.

    I det här exemplet används en cellreferens till platsen där företagsnamnet kommer att matas in i kalkylbladet. Fördelen med detta är att det gör det enkelt att ändra företagets namn utan att redigera formeln.

    1. Klicka på raden lookup_value i dialogrutan.
    2. Klicka på cell D2 för att lägga till denna cellreferens till raden lookup_value .
    3. Tryck på F4- tangenten på tangentbordet för att göra cellreferensen absolut - $ D $ 2.

    Absoluta cellreferenser används för uppslagningsvärdet och tabellarrayargument för att förhindra fel om uppslagningsformeln kopieras till andra celler i kalkylbladet.

    Gå in i CHOOSE-funktionen

    Argumentet för tabelluppsättningen är blocket av sammanhängande data från vilka specifik information hämtas.

    Vanligtvis ser VLOOKUP bara till höger om uppslagningsvärdet för att hitta data i tabelluppsättningen. För att det ska se till vänster måste VLOOKUP luras genom att ordna om kolumnerna i tabellgruppen med hjälp av VÄLJ-funktionen.

    I denna formel utför CHOOSE-funktionen två uppgifter:

    1. Det skapar en tabellgrupp som bara är två kolumner bred (kolumner D och F).
    2. Det ändrar ordningen från höger till vänster för kolumnerna i tabelluppsättningen så att kolumn F kommer först och kolumn D är andra.

    Ange funktioner

    När man matar in funktioner manuellt måste var och en av funktionens argument separeras med ett komma.

    1. I dialogrutan VLOOKUP-funktionen klickar du på raden Table_array .
    2. Ange följande VÄLJ- funktion: VÄLJ ({1, 2}, $ F: $ F, $ D: $ D)

    Kolumnindexnummer

    Normalt indikerar kolumnindexnumret vilken kolumn i tabellgruppen som innehåller de data du letar efter. I denna formel hänvisar det dock till ordningen på kolumner som ställs in med CHOOSE-funktionen.

    Funktionen VÄLJ skapar en tabellgrupp som är två kolumner bred med kolumn F först följt av kolumn D. Eftersom den sökta informationen - delnamnet - finns i kolumn D måste värdet på kolumnindexargumentet ställas in på 2.

    1. Klicka på Col_index_num- raden i dialogrutan.
    2. Skriv en 2 i den här raden.

    Range Lookup

    VLOOKUP: s Range_lookup-argument är ett logiskt värde (endast SANT eller FALSE) som indikerar om du vill att VLOOKUP ska hitta en exakt eller ungefärlig matchning till sökvärdet.

    • Om SANT eller om detta argument utelämnas, returnerar VLOOKUP antingen en exakt matchning till Lookup_value eller, om en exakt matchning inte hittas, returnerar VLOOKUP nästa största värde. För att formeln ska kunna göra detta måste data i den första kolumnen i Table_array sorteras i stigande ordning.
    • Om FALSE använder VLOOKUP bara en exakt matchning till Lookup_value. Om det finns två eller flera värden i den första kolumnen i Table_array som matchar uppslagningsvärdet används det första värdet som hittades. Om en exakt matchning inte hittas returneras ett # N / A-fel.

    Eftersom vi letar efter ett visst delnamn är Range_lookup inställd på False, så att bara exakta matchningar returneras av formeln.

    1. Klicka på Range_lookup- raden i dialogrutan.
    2. Skriv ordet False i den här raden för att indikera att vi vill att VLOOKUP ska returnera en exakt matchning för de data vi söker.
    3. Klicka på OK för att slutföra den vänstra sökformeln och stänga dialogrutan.
    4. Eftersom vi ännu inte har angett företagsnamnet i cell D2, visas ett # N / A-fel i cell E2.

    03

    av 05

    Återvända data med vänster sökformel

    För att hitta vilka företag som levererar vilka delar skriver du ett företags namn i cell D2 och trycker på ENTER- tangenten på tangentbordet.

    Delnamnet visas i cell E2.

    1. Klicka på cell D2 i arbetsbladet.
    2. Skriv Gadgets Plus i cell D2 och tryck på ENTER- tangenten på tangentbordet.
    3. Texten "Gadgets" - den del som tillhandahålls av företaget Gadgets Plus - ska visas i cell E2.

    Testa uppslagningsformeln vidare genom att skriva andra företagsnamn i cell D2, och motsvarande delnamn ska visas i cell E2.

    Om ett felmeddelande som # N / A visas i cell E2, kontrollera om det finns stavfel i cell D2.

    04

    av 05

    Skapa en tabellgrupp med två kolumner

    Syntaxen för CHOOSE-funktionen är:

    = VÄLJ (indexnummer, värde1, värde2, … värde254)

    CHOOSE-funktionen returnerar vanligtvis ett värde från listan med värden (Value1 till Value254) baserat på det angivna indexnumret.

    Om indexnumret är 1, returnerar funktionen Value1 från listan; om indexnumret är 2, returnerar funktionen Value2 från listan och så vidare.

    När flera indexnummer anges returnerar funktionen flera värden i önskad ordning. Att få VÄLJ för att returnera flera värden görs genom att skapa en matris.

    Om du vill ange en matris omger du numren som matats in med lockiga hängslen eller parenteser. Två siffror anges för indexnumret: {1, 2} .

    Det bör noteras att VÄLJ inte begränsas till att skapa en tabell med två kolumner. Genom att inkludera ett ytterligare nummer i matrisen - som {1, 2, 3} - och ett ytterligare intervall i värderargumentet skapar CHOOSE en tre-kolumnstabell.

    Ytterligare kolumner gör att du kan returnera annan information med vänster sökformel genom att ändra VLOOKUPs kolumnindexnummernummer till kolumnnumret med önskad information.

    05

    av 05

    Ändra ordning på kolumner med CHOOSE-funktionen

    I CHOOSE-funktionen som används i denna formel:

    VÄLJ ({1, 2}, $ F: $ F, $ D: $ D)

    intervallet för kolumn F listas före kolumn D.

    Eftersom CHOOSE-funktionen ställer in VLOOKUPs tabelluppsättning (datakällan för den funktionen) överförs ordningen på kolumnerna i CHOOSE-funktionen till VLOOKUP.

    När det gäller VLOOKUP är tabelluppsättningen bara två kolumner bred med kolumn F till vänster och kolumn D till höger. Eftersom kolumn F innehåller namnet på företaget vi vill söka efter, och eftersom kolumn D innehåller delnamn kan VLOOKUP utföra sina vanliga uppslagningsuppgifter för att hitta data som finns till vänster om sökvärdet.

    Som ett resultat kan VLOOKUP använda företagsnamnet för att hitta den del de levererar.