Alle hemmelighederne ved Excel VLOOKUP-funktionen til at finde data i en tabel og udtrække dem i en anden

2024 Forfatter: Malcolm Clapton | [email protected]. Sidst ændret: 2023-12-17 03:51

Efter at have læst artiklen lærer du ikke kun, hvordan du finder data i et Excel-regneark og udtrækker det til et andet, men også teknikker, der kan bruges i forbindelse med VLOOKUP-funktionen.

Når du arbejder i Excel, er der meget ofte behov for at finde data i en tabel og udtrække dem i en anden. Hvis du stadig ikke ved, hvordan du gør dette, vil du efter at have læst artiklen ikke kun lære, hvordan du gør dette, men også finde ud af, under hvilke forhold du kan presse den maksimale ydeevne ud af systemet. De fleste af de meget effektive teknikker, der bør bruges i forbindelse med VLOOKUP-funktionen, tages i betragtning.

Selvom du har brugt VLOOKUP-funktionen i årevis, vil denne artikel med stor sandsynlighed være nyttig for dig og vil ikke efterlade dig ligeglad. Som IT-specialist og derefter IT-chef har jeg for eksempel brugt VLOOKUP i 15 år, men jeg nåede først at håndtere alle nuancerne nu, hvor jeg begyndte at undervise folk i Excel på et professionelt grundlag.

VOPSLAG er en forkortelse for vlodret NSinspektion. Ligeledes VLOOKUP - Lodret LOOKUP. Selve navnet på funktionen antyder os, at den søger i tabellens rækker (lodret - itererer over rækkerne og fikser kolonnen), og ikke i kolonnerne (vandret - itererer over kolonnerne og fikser rækken). Det skal bemærkes, at VLOOKUP har en søster - en grim ælling, som aldrig bliver en svane - dette er HLOOKUP-funktionen. HLOOKUP, i modsætning til VLOOKUP, udfører horisontale søgninger, men begrebet Excel (og faktisk begrebet dataorganisation) indebærer, at dine tabeller har færre kolonner og mange flere rækker. Derfor skal vi søge på rækker mange gange oftere end på kolonner. Hvis du bruger HLO-funktionen for ofte i Excel, så er det sandsynligt, at du ikke har forstået noget her i livet.

Syntaks

VLOOKUP-funktionen har fire parametre:

= OPSLAG (;; [;]), her:

- den ønskede værdi (sjældent) eller en reference til en celle, der indeholder den ønskede værdi (langt de fleste tilfælde);

- reference til et område af celler (to-dimensionelt array), i den FØRSTE (!) kolonne, hvoraf parameterværdien vil blive søgt;

- kolonnenummer i det område, hvorfra værdien vil blive returneret;

- dette er en meget vigtig parameter, der besvarer spørgsmålet om, hvorvidt den første kolonne i området er sorteret i stigende rækkefølge. Hvis arrayet er sorteret, angiver vi værdien TRUE eller 1, ellers - FALSE eller 0. Hvis denne parameter udelades, er den som standard 1.

Jeg vil vædde på, at mange af dem, der kender VLOOKUP, fungerer som ujævne, efter at have læst beskrivelsen af den fjerde parameter, kan de føle sig utilpas, da de er vant til at se det i en lidt anden form: normalt taler de om et nøjagtigt match, når søgning (FALSK eller 0) eller en rækkeviddescanning (SAND eller 1).

Nu skal du anstrenge dig og læse det næste afsnit flere gange, indtil du mærker meningen med det, der blev sagt til slutningen. Hvert ord er vigtigt der. Eksempler vil hjælpe dig med at finde ud af det.

Hvordan fungerer VLOOKUP-formlen præcist?

• Formel type I. Hvis den sidste parameter er udeladt eller angivet lig med 1, så antager VOPSLAG, at den første kolonne er sorteret i stigende rækkefølge, så søgningen stopper ved den række, der umiddelbart før linjen, der indeholder værdien større end den ønskede … Hvis der ikke findes en sådan streng, returneres den sidste række i området.

  

• Formel II. Hvis den sidste parameter er angivet som 0, så scanner VLOOKUP den første kolonne i arrayet sekventielt og stopper straks søgningen, når det første nøjagtige match med parameteren er fundet, ellers fejlkoden # N/A (# N/A) er returneret.

  

Formler arbejdsgange

VPR type I

VPR type II

Følger for formler af form I

1. Formler kan bruges til at fordele værdier på tværs af intervaller.
2. Hvis den første kolonne indeholder duplikerede værdier og er sorteret korrekt, vil den sidste af rækkerne med duplikerede værdier blive returneret.
3. Hvis du søger efter en værdi, der åbenlyst er større end den første kolonne kan indeholde, så kan du nemt finde den sidste række i tabellen, hvilket kan være ret værdifuldt.
4. Denne visning returnerer kun # N/A-fejlen, hvis den ikke finder en værdi, der er mindre end eller lig med den ønskede.
5. Det er ret svært at forstå, at formlen returnerer de forkerte værdier, hvis dit array ikke er sorteret.

Følger for formler af type II

Hvis den ønskede værdi forekommer flere gange i den første kolonne i arrayet, vil formlen vælge den første række til efterfølgende datahentning.

VLOOKUP ydeevne

Du har nået artiklens højdepunkt. Det ser ud til, ja, hvad er forskellen, hvis jeg angiver nul eller én som den sidste parameter? Grundlæggende angiver alle selvfølgelig nul, da dette er ret praktisk: du behøver ikke bekymre dig om at sortere den første kolonne i arrayet, du kan straks se, om værdien blev fundet eller ej. Men hvis du har flere tusinde VLOOKUP-formler på dit ark, vil du bemærke, at VLOOKUP II er langsom. Samtidig begynder alle normalt at tænke:

• Jeg har brug for en mere kraftfuld computer;
• Jeg har brug for en hurtigere formel, for eksempel kender mange til INDEX + MATCH, som angiveligt er sølle 5-10 % hurtigere.

Og få mennesker tror, at så snart du begynder at bruge VLOOKUP af type I og sikrer, at den første kolonne er sorteret på nogen måde, vil hastigheden af VLOOKUP stige 57 gange. Jeg skriver med ord - 57 GANGE! Ikke 57 %, men 5.700 %. Jeg kontrollerede dette faktum ganske pålideligt.

Hemmeligheden bag så hurtigt arbejde ligger i, at en ekstremt effektiv søgealgoritme kan anvendes på et sorteret array, som kaldes binær søgning (halveringsmetode, dikotomimetode). Så VLOOKUP af type I anvender det, og VLOOKUP af type II søger uden nogen optimering overhovedet. Det samme gælder for MATCH-funktionen, som indeholder en lignende parameter, og LOOKUP-funktionen, som kun virker på sorterede arrays og er inkluderet i Excel for kompatibilitet med Lotus 1-2-3.

Ulemper ved formlen

Ulemperne ved VLOOKUP er indlysende: For det første søger den kun i den første kolonne i det angivne array, og for det andet kun til højre for denne kolonne. Og som du forstår, kan det godt ske, at kolonnen, der indeholder de nødvendige oplysninger, vil være til venstre for den kolonne, vi skal kigge i. Den allerede nævnte kombination af formler INDEX + MATCH er blottet for denne ulempe, hvilket gør den til den mest fleksible løsning til at udtrække data fra tabeller i sammenligning med VLOOKUP (VLOOKUP).

Nogle aspekter af at anvende formlen i det virkelige liv

Rækkeviddesøgning

En klassisk illustration af en rækkeviddesøgning er opgaven med at bestemme en rabat efter ordrestørrelse.

Søg efter tekststrenge

VLOOKUP ser naturligvis ikke kun efter tal, men også efter tekst. Man skal huske på, at formlen ikke skelner mellem tilfælde af tegn. Hvis du bruger jokertegn, kan du organisere en uklar søgning. Der er to jokertegn: "?" - erstatter et hvilket som helst tegn i en tekststreng, "*" - erstatter et vilkårligt antal tegn.

Kamprum

Spørgsmålet bliver ofte rejst, hvordan man løser problemet med ekstra pladser ved søgning. Hvis opslagstabellen stadig kan ryddes for dem, så er den første parameter i VLOOKUP-formlen ikke altid op til dig. Derfor, hvis der er risiko for at tilstoppe cellerne med ekstra mellemrum, så kan du bruge TRIM-funktionen til at rydde den.

Forskelligt dataformat

Hvis den første parameter i VLOOKUP-funktionen refererer til en celle, der indeholder et tal, men som er gemt i cellen som tekst, og den første kolonne i matrixen indeholder tal i det korrekte format, så mislykkes søgningen. Den modsatte situation er også mulig. Problemet kan let løses ved at oversætte parameter 1 til det nødvendige format:

= VLOOKUP (−− D7; Produkter! $ A $ 2: $ C $ 5; 3; 0) - hvis D7 indeholder tekst, og tabellen indeholder tal;

= VLOOKUP (D7 & ""); Produkter $ A $ 2: $ C $ 5; 3; 0) - og omvendt.

I øvrigt kan du oversætte tekst til et tal på flere måder på én gang, vælg:

• Dobbelt negation -D7.
• Multiplikation med én D7 * 1.
• Nul addition D7 + 0.
• Hæve til første potens D7 ^ 1.

Konvertering af et tal til tekst sker gennem sammenkædning med en tom streng, som tvinger Excel til at konvertere datatypen.

Sådan undertrykkes # N/A

Dette er meget praktisk at gøre med IFERROR-funktionen.

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

Hvis VLOOKUP returnerer fejlkoden # N/A, opsnapper IFERROR den og erstatter parameter 2 (i dette tilfælde en tom streng), og hvis der ikke opstår en fejl, vil denne funktion lade som om, at den slet ikke eksisterer, men der er kun VLOOKUP, der returnerede normalt resultat.

Array

Ofte glemmer de at gøre referencen til arrayet absolut, og når de strækker arrayet "flyder". Husk at bruge $ A $ 2: $ C $ 5 i stedet for A2: C5.

Det er en god idé at placere referencearrayet på et separat ark i projektmappen. Det kommer ikke under fødderne, og det vil være mere sikkert.

En endnu bedre idé ville være at erklære dette array som et navngivet område.

Mange brugere, når de angiver et array, bruger en konstruktion som A:C, der angiver hele kolonner. Denne tilgang har ret til at eksistere, da du er reddet fra at skulle holde styr på, at dit array indeholder alle de nødvendige strenge. Hvis du tilføjer rækker til arket med det originale array, skal området angivet som A: C ikke justeres. Selvfølgelig tvinger denne syntaktiske konstruktion Excel til at udføre lidt mere arbejde end at specificere området nøjagtigt, men denne overhead kan negligeres. Vi taler om hundrededele af et sekund.

Nå, på grænsen til geni - at arrangere arrayet i form.

Brug af funktionen COLUMN til at angive den kolonne, der skal udtrækkes

Hvis tabellen, som du henter data ind i ved hjælp af VOPSLAG, har samme struktur som opslagstabellen, men blot indeholder færre rækker, så kan du bruge funktionen KOLONNE () i VOPSLAG til automatisk at beregne antallet af de kolonner, der skal hentes. I dette tilfælde vil alle VLOOKUP formler være de samme (justeret for den første parameter, som ændres automatisk)! Bemærk, at den første parameter har en absolut kolonnekoordinat.

Oprettelse af en sammensat nøgle med & "|" &

Hvis det bliver nødvendigt at søge efter flere kolonner på samme tid, så er det nødvendigt at lave en sammensat nøgle til søgningen. Hvis returværdien ikke var tekstlig (som det er tilfældet med "Code"-feltet), men numerisk, ville den mere bekvemme SUMIFS-formel være egnet til dette, og den sammensatte kolonnenøgle ville slet ikke være påkrævet.

Dette er min første artikel til en Lifehacker. Hvis du kunne lide det, inviterer jeg dig til at besøge og også gerne læse i kommentarerne om dine hemmeligheder ved at bruge VLOOKUP-funktionen og lignende. Tak.:)