Alla hemligheter med Excel VLOOKUP-funktionen för att hitta data i en tabell och extrahera den till en annan

2024 Författare: Malcolm Clapton | [email protected]. Senast ändrad: 2023-12-17 04:11

Efter att ha läst artikeln kommer du inte bara att lära dig hur du hittar data i ett Excel-kalkylblad och extraherar det till ett annat, utan också tekniker som kan användas tillsammans med funktionen VLOOKUP.

När du arbetar i Excel finns det ofta ett behov av att hitta data i en tabell och extrahera den till en annan. Om du fortfarande inte vet hur du gör detta, kommer du efter att ha läst artikeln inte bara lära dig hur du gör detta, utan också ta reda på under vilka förhållanden du kan pressa ut den maximala prestandan ur systemet. De flesta av de mycket effektiva teknikerna som bör användas tillsammans med UPPSÖKNINGSfunktionen beaktas.

Även om du har använt VLOOKUP-funktionen i flera år, kommer den här artikeln med stor sannolikhet att vara användbar för dig och kommer inte att lämna dig oberörd. Som IT-specialist, och sedan chef inom IT, har jag till exempel använt VLOOKUP i 15 år, men jag lyckades ta itu med alla nyanser först nu, när jag började lära människor Excel på professionell basis.

VLOOKUP är en förkortning för vvertikal NSinspektion. Likaså VLOOKUP - Vertical LOOKUP. Själva namnet på funktionen antyder för oss att den söker i tabellens rader (vertikalt - itererar över raderna och fixar kolumnen), och inte i kolumnerna (horisontellt - itererar över kolumnerna och fixar raden). Det bör noteras att VLOOKUP har en syster - en ful ankunge som aldrig kommer att bli en svan - detta är HLOOKUP-funktionen. HLOOKUP, i motsats till VLOOKUP, utför horisontella sökningar, men konceptet med Excel (och faktiskt konceptet med dataorganisation) innebär att dina tabeller har färre kolumner och många fler rader. Det är därför vi behöver söka efter rader många gånger oftare än efter kolumner. Om du använder HLO-funktionen för ofta i Excel, är det troligt att du inte förstod något här i livet.

Syntax

VLOOKUP-funktionen har fyra parametrar:

= VLOOKUP (;; [;]), här:

- det önskade värdet (sällan) eller en referens till en cell som innehåller det önskade värdet (de allra flesta fall);

- referens till ett cellområde (tvådimensionell array), i den FÖRSTA (!) kolumnen vars parametervärde kommer att genomsökas;

- kolumnnummer i intervallet från vilket värdet kommer att returneras;

- detta är en mycket viktig parameter som svarar på frågan om den första kolumnen i intervallet är sorterad i stigande ordning. Om matrisen är sorterad anger vi värdet TRUE eller 1, annars - FALSE eller 0. Om den här parametern utelämnas är den som standard 1.

Jag slår vad om att många av de som känner till VLOOKUP fungerar som fläckiga, efter att ha läst beskrivningen av den fjärde parametern kan de känna sig obekväma, eftersom de är vana vid att se den i en lite annorlunda form: vanligtvis talar de om en exakt matchning när sökning (FALSE eller 0) eller en räckviddsskanning (TRUE eller 1).

Nu måste du anstränga dig och läsa nästa stycke flera gånger tills du känner innebörden av det som sades till slutet. Varje ord är viktigt där. Exempel hjälper dig att ta reda på det.

Hur exakt fungerar VLOOKUP-formeln?

• Formel typ I. Om den sista parametern utelämnas eller anges lika med 1, antar VLOOKUP att den första kolumnen är sorterad i stigande ordning, så sökningen stannar vid raden som omedelbart före raden som innehåller värdet som är större än det önskade … Om ingen sådan sträng hittas returneras den sista raden i intervallet.

  

• Formel II. Om den sista parametern är specificerad som 0, så skannar VLOOKUP den första kolumnen i arrayen sekventiellt och stoppar omedelbart sökningen när den första exakta matchningen med parametern hittas, annars felkoden # N/A (# N/A) returneras.

  

Formler arbetsflöden

VPR typ I

VPR typ II

Följder för formler av formen I

1. Formler kan användas för att fördela värden över intervall.
2. Om den första kolumnen innehåller dubblettvärden och är korrekt sorterad, kommer den sista av raderna med dubblettvärden att returneras.
3. Om du söker efter ett värde som uppenbarligen är större än den första kolumnen kan innehålla, så kan du enkelt hitta den sista raden i tabellen, vilket kan vara ganska värdefullt.
4. Denna vy returnerar # N/A-felet endast om den inte hittar ett värde som är mindre än eller lika med det önskade.
5. Det är ganska svårt att förstå att formeln returnerar fel värden om din array inte är sorterad.

Följder för formler av typ II

Om det önskade värdet förekommer flera gånger i den första kolumnen i arrayen, kommer formeln att välja den första raden för efterföljande datahämtning.

VLOOKUP prestanda

Du har nått höjdpunkten i artikeln. Det verkar, ja, vad är skillnaden om jag anger noll eller en som sista parameter? I grund och botten anger alla, naturligtvis, noll, eftersom detta är ganska praktiskt: du behöver inte oroa dig för att sortera den första kolumnen i arrayen, du kan omedelbart se om värdet hittades eller inte. Men om du har flera tusen VLOOKUP-formler på ditt ark, kommer du att märka att VLOOKUP II är långsam. Samtidigt börjar vanligtvis alla tänka:

• Jag behöver en kraftfullare dator;
• Jag behöver en snabbare formel, till exempel är det många som känner till INDEX + MATCH, som lär vara ynka 5-10 % snabbare.

Och få människor tror att så snart du börjar använda VLOOKUP av typ I och ser till att den första kolumnen sorteras på något sätt, kommer hastigheten på VLOOKUP att öka 57 gånger. Jag skriver i ord - FEMTIOSJU GÅNGER! Inte 57 %, utan 5 700 %. Jag kontrollerade detta faktum ganska tillförlitligt.

Hemligheten med ett så snabbt arbete ligger i det faktum att en extremt effektiv sökalgoritm kan appliceras på en sorterad array, som kallas binär sökning (halveringsmetod, dikotomimetod). Så VLOOKUP av typ I tillämpar det, och VLOOKUP av typ II söker utan någon optimering alls. Detsamma gäller för MATCH-funktionen, som inkluderar en liknande parameter, och LOOKUP-funktionen, som bara fungerar på sorterade arrayer och ingår i Excel för kompatibilitet med Lotus 1-2-3.

Nackdelar med formeln

Nackdelarna med VLOOKUP är uppenbara: för det första söker den bara i den första kolumnen i den angivna arrayen, och för det andra bara till höger om denna kolumn. Och som du förstår kan det mycket väl hända att kolumnen som innehåller den nödvändiga informationen kommer att vara till vänster om kolumnen där vi ska titta. Den redan nämnda kombinationen av formler INDEX + MATCH saknar denna nackdel, vilket gör den till den mest flexibla lösningen för att extrahera data från tabeller i jämförelse med VLOOKUP (VLOOKUP).

Några aspekter av att tillämpa formeln i verkliga livet

Avståndssökning

En klassisk illustration av en intervallsökning är uppgiften att bestämma en rabatt efter orderstorlek.

Sök efter textsträngar

Naturligtvis letar VLOOKUP inte bara efter siffror utan också efter text. Man bör komma ihåg att formeln inte skiljer mellan fallet med tecken. Om du använder jokertecken kan du organisera en suddig sökning. Det finns två jokertecken: "?" - ersätter valfritt tecken i en textsträng, "*" - ersätter valfritt antal tecken.

Kamputrymmen

Frågan ställs ofta hur man löser problemet med extra utrymmen vid sökning. Om uppslagstabellen fortfarande kan rensas från dem, är den första parametern i VLOOKUP-formeln inte alltid upp till dig. Därför, om risken för att cellerna täpps till med extra utrymmen finns, kan du använda TRIM-funktionen för att rensa den.

Olika dataformat

Om den första parametern i VLOOKUP-funktionen hänvisar till en cell som innehåller ett nummer, men som lagras i cellen som text, och den första kolumnen i arrayen innehåller siffror i rätt format, misslyckas sökningen. Den motsatta situationen är också möjlig. Problemet kan enkelt lösas genom att översätta parameter 1 till önskat format:

= VLOOKUP (−− D7; Produkter! $ A $ 2: $ C $ 5; 3; 0) - om D7 innehåller text och tabellen innehåller siffror;

= VLOOKUP (D7 & ""); Produkter! $ A $ 2: $ C $ 5; 3; 0) - och vice versa.

Förresten, du kan översätta text till ett nummer på flera sätt samtidigt, välj:

• Dubbel negation -D7.
• Multiplikation med ett D7 * 1.
• Nolltillägg D7 + 0.
• Höjning till första potens D7 ^ 1.

Konvertering av ett tal till text görs genom sammanlänkning med en tom sträng, vilket tvingar Excel att konvertera datatypen.

Hur man undertrycker # N/A

Detta är mycket bekvämt att göra med IFERROR-funktionen.

Till exempel: = IFERROR (VLOOKUP (D7; Produkter! $ A $ 2: $ C $ 5; 3; 0); "").

Om VLOOKUP returnerar felkoden # N / A, kommer IFERROR att fånga upp den och ersätta parameter 2 (i det här fallet en tom sträng), och om inget fel inträffar kommer den här funktionen att låtsas att den inte existerar alls, men det är bara VLOOKUP som gav normalt resultat.

Array

Ofta glömmer de att göra referensen för arrayen absolut, och när de sträcker arrayen "flyter". Kom ihåg att använda $ A $ 2: $ C $ 5 istället för A2: C5.

Det är en bra idé att placera referensarrayen på ett separat ark i arbetsboken. Det går inte under fötterna, och det blir säkrare.

En ännu bättre idé skulle vara att deklarera denna array som ett namngivet intervall.

Många användare, när de anger en array, använder en konstruktion som A:C, som anger hela kolumner. Detta tillvägagångssätt har rätt att existera, eftersom du är räddad från att behöva hålla reda på det faktum att din array innehåller alla nödvändiga strängar. Om du lägger till rader på arket med den ursprungliga matrisen behöver inte intervallet som anges som A: C justeras. Naturligtvis tvingar denna syntaktiska konstruktion Excel att göra lite mer arbete än att specificera intervallet exakt, men denna overhead kan försummas. Vi pratar om hundradelar av en sekund.

Tja, på gränsen till geni - att ordna arrayen i formen.

Använd funktionen COLUMN för att ange kolumnen som ska extraheras

Om tabellen som du hämtar data till med hjälp av VLOOKUP har samma struktur som uppslagstabellen, men helt enkelt innehåller färre rader, kan du använda funktionen COLUMN () i VLOOKUP för att automatiskt beräkna antalet kolumner som ska hämtas. I detta fall kommer alla VLOOKUP-formler att vara desamma (justeras för den första parametern, som ändras automatiskt)! Observera att den första parametern har en absolut kolumnkoordinat.

Skapa en sammansatt nyckel med & "|" &

Om det blir nödvändigt att söka efter flera kolumner samtidigt, är det nödvändigt att göra en sammansatt nyckel för sökningen. Om returvärdet inte var textmässigt (som är fallet med "Code"-fältet), utan numeriskt, skulle den mer praktiska SUMIFS-formeln vara lämplig för detta och den sammansatta kolumnnyckeln skulle inte behövas alls.

Detta är min första artikel för en Lifehacker. Om du gillade det, inbjuder jag dig att besöka och också gärna läsa i kommentarerna om dina hemligheter med att använda VLOOKUP-funktionen och liknande. Tack.:)