Alle hemmelighetene til Excel VLOOKUP-funksjonen for å finne data i en tabell og trekke den ut i en annen

2024 Forfatter: Malcolm Clapton | [email protected]. Sist endret: 2023-12-17 04:07

Etter å ha lest artikkelen vil du ikke bare lære hvordan du finner data i et Excel-regneark og trekker det ut i et annet, men også teknikker som kan brukes i forbindelse med VLOOKUP-funksjonen.

Når du arbeider i Excel, er det veldig ofte behov for å finne data i en tabell og trekke dem ut i en annen. Hvis du fortsatt ikke vet hvordan du gjør dette, vil du etter å ha lest artikkelen ikke bare lære hvordan du gjør dette, men også finne ut under hvilke forhold du kan presse maksimal ytelse ut av systemet. De fleste av de svært effektive teknikkene som bør brukes sammen med VLOOKUP-funksjonen vurderes.

Selv om du har brukt VLOOKUP-funksjonen i årevis, vil denne artikkelen med stor sannsynlighet være nyttig for deg og vil ikke forlate deg likegyldig. For eksempel som IT-spesialist, og deretter IT-sjef, har jeg brukt VLOOKUP i 15 år, men jeg klarte først å forholde meg til alle nyansene nå, da jeg begynte å lære folk Excel på en profesjonell basis.

VLOOKUP er en forkortelse for vvertikal NSundersøkelse. På samme måte VLOOKUP - Vertical LOOKUP. Selve navnet på funksjonen antyder for oss at den søker i radene i tabellen (vertikalt - itererer over radene og fikser kolonnen), og ikke i kolonnene (horisontalt - itererer over kolonnene og fikser raden). Det skal bemerkes at VLOOKUP har en søster - en stygg andunge som aldri vil bli en svane - dette er HLOOKUP-funksjonen. HLOOKUP, i motsetning til VLOOKUP, utfører horisontale søk, men konseptet med Excel (og faktisk konseptet med dataorganisering) innebærer at tabellene dine har færre kolonner og mange flere rader. Derfor må vi søke etter rader mange ganger oftere enn etter kolonner. Hvis du bruker HLO-funksjonen for ofte i Excel, så er det sannsynlig at du ikke forsto noe her i livet.

Syntaks

VLOOKUP-funksjonen har fire parametere:

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

- ønsket verdi (sjelden) eller en referanse til en celle som inneholder ønsket verdi (de aller fleste tilfeller);

- referanse til et celleområde (to-dimensjonal matrise), i den FØRSTE (!) kolonnen som parameterverdien skal søkes i;

- kolonnenummer i området som verdien vil bli returnert fra;

- dette er en veldig viktig parameter som svarer på spørsmålet om den første kolonnen i området er sortert i stigende rekkefølge. Hvis matrisen er sortert, spesifiserer vi verdien TRUE eller 1, ellers - FALSE eller 0. Hvis denne parameteren utelates, er den som standard 1.

Jeg vedder på at mange av de som kjenner VLOOKUP fungerer som flakete, etter å ha lest beskrivelsen av den fjerde parameteren, kan de føle seg ukomfortable, siden de er vant til å se den i en litt annen form: vanligvis snakker de om en eksakt match når søking (USANN eller 0) eller en rekkeviddeskanning (SANN eller 1).

Nå må du anstrenge deg og lese neste avsnitt flere ganger til du kjenner meningen med det som ble sagt til slutten. Hvert ord er viktig der. Eksempler vil hjelpe deg å finne ut av det.

Hvordan fungerer VLOOKUP-formelen?

• Formel type I. Hvis den siste parameteren er utelatt eller spesifisert lik 1, antar VLOOKUP at den første kolonnen er sortert i stigende rekkefølge, slik at søket stopper ved raden som umiddelbart foran linjen som inneholder verdien større enn den ønskede … Hvis ingen slik streng blir funnet, returneres den siste raden i området.

  

• Formel II. Hvis den siste parameteren er spesifisert som 0, skanner VLOOKUP den første kolonnen i matrisen sekvensielt og stopper søket umiddelbart når den første eksakte samsvar med parameteren er funnet, ellers # N / A (# N / A) feilkoden er returnert.

  

Formler arbeidsflyter

VPR type I

VPR type II

Følger for formler på formen I

1. Formler kan brukes til å fordele verdier på tvers av områder.
2. Hvis den første kolonnen inneholder dupliserte verdier og er sortert riktig, vil den siste av radene med dupliserte verdier bli returnert.
3. Hvis du søker etter en verdi som åpenbart er større enn den første kolonnen kan inneholde, kan du enkelt finne den siste raden i tabellen, som kan være ganske verdifull.
4. Denne visningen vil returnere # N / A-feilen bare hvis den ikke finner en verdi mindre enn eller lik den ønskede.
5. Det er ganske vanskelig å forstå at formelen returnerer feil verdier hvis matrisen din ikke er sortert.

Følger for formler av type II

Hvis den ønskede verdien forekommer flere ganger i den første kolonnen i matrisen, vil formelen velge den første raden for påfølgende datainnhenting.

VLOOKUP ytelse

Du har nådd toppen av artikkelen. Det ser ut til, vel, hva er forskjellen hvis jeg angir null eller én som siste parameter? I utgangspunktet indikerer alle, selvfølgelig, null, siden dette er ganske praktisk: du trenger ikke å bekymre deg for å sortere den første kolonnen i matrisen, du kan umiddelbart se om verdien ble funnet eller ikke. Men hvis du har flere tusen VLOOKUP-formler på arket ditt, vil du legge merke til at VLOOKUP II er treg. Samtidig begynner vanligvis alle å tenke:

• Jeg trenger en kraftigere datamaskin;
• Jeg trenger en raskere formel, for eksempel vet mange om INDEX + MATCH, som visstnok er 5-10 % raskere.

Og få mennesker tror at så snart du begynner å bruke VLOOKUP av type I og sørger for at den første kolonnen er sortert på noen måte, vil hastigheten til VLOOKUP øke 57 ganger. Jeg skriver med ord - FEMTISYV GANGER! Ikke 57 %, men 5 700 %. Jeg sjekket dette faktum ganske pålitelig.

Hemmeligheten bak et så raskt arbeid ligger i det faktum at en ekstremt effektiv søkealgoritme kan brukes på en sortert array, som kalles binært søk (halveringsmetode, dikotomimetode). Så VLOOKUP av type I bruker det, og VLOOKUP av type II søker uten optimalisering i det hele tatt. Det samme gjelder MATCH-funksjonen, som inkluderer en lignende parameter, og LOOKUP-funksjonen, som kun fungerer på sorterte arrays og er inkludert i Excel for kompatibilitet med Lotus 1-2-3.

Ulemper med formelen

Ulempene med VLOOKUP er åpenbare: for det første søker den bare i den første kolonnen i den angitte matrisen, og for det andre bare til høyre for denne kolonnen. Og som du forstår, kan det godt hende at kolonnen som inneholder nødvendig informasjon vil være til venstre for kolonnen vi skal se på. Den allerede nevnte kombinasjonen av formler INDEX + MATCH er blottet for denne ulempen, noe som gjør den til den mest fleksible løsningen for å trekke ut data fra tabeller sammenlignet med VLOOKUP (VLOOKUP).

Noen aspekter ved å bruke formelen i det virkelige liv

Områdesøk

En klassisk illustrasjon av et områdesøk er oppgaven med å bestemme en rabatt etter ordrestørrelse.

Søk etter tekststrenger

Selvfølgelig ser VLOOKUP ikke bare etter tall, men også etter tekst. Det bør huskes at formelen ikke skiller mellom tilfellet med tegn. Hvis du bruker jokertegn, kan du organisere et uklart søk. Det er to jokertegn: "?" - erstatter et hvilket som helst tegn i en tekststreng, "*" - erstatter et hvilket som helst antall tegn.

Kamprom

Spørsmålet reises ofte hvordan man kan løse problemet med ekstra mellomrom ved søk. Hvis oppslagstabellen fortsatt kan tømmes for dem, er den første parameteren i VLOOKUP-formelen ikke alltid opp til deg. Derfor, hvis risikoen for å tette cellene med ekstra mellomrom er tilstede, kan du bruke TRIM-funksjonen for å fjerne den.

Ulikt dataformat

Hvis den første parameteren til funksjonen VLOOKUP refererer til en celle som inneholder et tall, men som er lagret i cellen som tekst, og den første kolonnen i matrisen inneholder tall i riktig format, vil søket mislykkes. Den motsatte situasjonen er også mulig. Problemet kan enkelt løses ved å oversette parameter 1 til ønsket format:

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

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

Forresten, du kan oversette tekst til et tall på flere måter samtidig, velg:

• Dobbel negasjon -D7.
• Multiplikasjon med én D7 * 1.
• Nulltillegg D7 + 0.
• Heving til første potens D7 ^ 1.

Konvertering av et tall til tekst gjøres gjennom sammenkobling med en tom streng, som tvinger Excel til å konvertere datatypen.

Hvordan undertrykke # N/A

Dette er veldig praktisk å gjøre med IFERROR-funksjonen.

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

Hvis VLOOKUP returnerer feilkoden # N / A, vil IFERROR avskjære den og erstatte parameter 2 (i dette tilfellet en tom streng), og hvis ingen feil oppstår, vil denne funksjonen late som om den ikke eksisterer i det hele tatt, men det er bare VLOOKUP som returnerte normalt resultat.

Array

Ofte glemmer de å gjøre referansen til matrisen absolutt, og når de strekker seg "flyter matrisen". Husk å bruke $ A $ 2: $ C $ 5 i stedet for A2: C5.

Det er en god idé å plassere referansematrisen på et eget ark i arbeidsboken. Den kommer ikke under føttene, og den blir tryggere.

En enda bedre idé ville være å erklære denne matrisen som et navngitt område.

Mange brukere, når de spesifiserer en matrise, bruker en konstruksjon som A:C, som spesifiserer hele kolonner. Denne tilnærmingen har rett til å eksistere, siden du er reddet fra å måtte holde styr på det faktum at matrisen din inkluderer alle de nødvendige strengene. Hvis du legger til rader på arket med den originale matrisen, trenger ikke området spesifisert som A: C å justeres. Selvfølgelig tvinger denne syntaktiske konstruksjonen Excel til å gjøre litt mer arbeid enn å spesifisere området nøyaktig, men denne overheaden kan neglisjeres. Vi snakker om hundredeler av et sekund.

Vel, på grensen til geni - å ordne matrisen i form.

Bruke COLUMN-funksjonen til å spesifisere kolonnen som skal trekkes ut

Hvis tabellen du henter data inn i ved hjelp av VLOOKUP har samme struktur som oppslagstabellen, men bare inneholder færre rader, så kan du bruke COLUMN () funksjonen i VLOOKUP for automatisk å beregne tallene på kolonnene som skal hentes. I dette tilfellet vil alle VLOOKUP-formler være de samme (justert for den første parameteren, som endres automatisk)! Merk at den første parameteren har en absolutt kolonnekoordinat.

Opprette en sammensatt nøkkel med & "|" &

Hvis det blir nødvendig å søke etter flere kolonner samtidig, er det nødvendig å lage en sammensatt nøkkel for søket. Hvis returverdien ikke var tekstlig (som tilfellet er med "Code"-feltet), men numerisk, ville den mer praktiske SUMIFS-formelen være egnet for dette, og den sammensatte kolonnenøkkelen ville ikke være nødvendig i det hele tatt.

Dette er min første artikkel for en Lifehacker. Hvis du likte det, inviterer jeg deg til å besøke, og også gjerne lese i kommentarene om hemmelighetene dine med å bruke VLOOKUP-funksjonen og lignende. Takk.:)