Mange mennesker prøver at bruge Excel som en database. Mens det kan fungere som en database, er nogle af de opgaver, der ville være meget lette i et databaseprogram, ret komplekse i Excel. En af disse opgaver er at matche to lister baseret på et fælles felt; dette kan let udføres ved hjælp af Excel VLOOKUP. Du finder funktionen VLOOKUP som yderst nyttig, så tjek et eksempel på hvornår og hvordan du bruger denne funktion nedenfor.
Sig, at dit rejsebureau sender dig en månedsrapport om alle de steder, som dine medarbejdere har rejst. Rapporten bruger lufthavnskoder i stedet for bynavne. Det ville være nyttigt, hvis du let kunne indsætte det rigtige bynavn i stedet for bare koden.
På Internettet finder du og importerer en liste, der viser bynavnet for hver lufthavnskode.
Men hvordan får du disse oplysninger til hver post i rapporten?
- Brug VLOOKUP-funktionen. VLOOKUP står for "Vertical Lookup". Det kan bruges når som helst, at du har en liste over data med nøglefeltet i kolonnen længst til venstre.
- Start med at skrive funktionen
=VLOOKUP(. Skriv Ctrl + A for at få hjælp til funktionen. - VLOOKUP har brug for fire parametre. Først er bykoden i den oprindelige rapport. I dette eksempel ville det være celle D4
- Den næste parameter er området med din opslagstabel. Fremhæv området. Sørg for at bruge F4 for at gøre rækkevidden absolut. (En absolut reference har et dollartegn før både kolonnetal og række nummer. Når formlen kopieres, vil referencen fortsætte med at pege mod I3: J351.
- Den 3. parameter fortæller Excel i hvilken kolonne bynavnet findes. I området I3: J351 er bynavnet i kolonne 2. Indtast en 2 for denne parameter.
- Den fjerde parameter fortæller Excel, hvis et ”tæt” match er OK. I dette tilfælde er det ikke, så skriv Falsk.
- Klik på OK for at udfylde formlen. Træk påfyldningshåndtaget for at kopiere formlen ned.
- Fordi du omhyggeligt har indtastet absolutte formler, kan du kopiere kolonne E til kolonne D for at få destinationsbyen. I dette tilfælde er alle afgange fra Pearson International Airport i Toronto.
Mens dette eksempel fungerede perfekt, når seere bruger VLOOKUP, betyder det normalt, at de matcher lister, der kom fra forskellige kilder. Når lister kommer fra forskellige kilder, kan der altid være subtile forskelle, der gør listerne svære at matche. Her er tre eksempler på, hvad der kan gå galt, og hvordan man retter dem.
-
Den ene liste har bindestreger, og den anden liste ikke. Brug
=SUBSTITUTE()funktionen til at fjerne bindestregerne. Første gang du prøver VLOOKUP, får du N / A-fejl.For at fjerne bindestregerne med en formel skal du bruge SUBSTITUTE-formlen. Brug 3 argumenter. Det første argument er cellen, der indeholder værdien. Det næste argument er den tekst, du vil ændre. Det sidste argument er erstatningsteksten. I dette tilfælde vil du ændre bindestreger til intet, så formlen er
=SUBSTITUTE(A4,"-","").Du kan indpakke den funktion i VLOOKUP for at få beskrivelsen.
-
Denne er subtil, men meget almindelig. En liste har et efterfølgende tomrum efter posten. Brug = TRIM () til at fjerne overskydende mellemrum. Når du oprindeligt indtaster formlen, finder du ud af, at alle svarene er N / A-fejl. Du ved med sikkerhed, at værdierne er på listen, og at alt ser OK ud med formlen.
En standard ting at kontrollere er at flytte til cellen med opslagsværdien. Tryk på F2 for at sætte cellen i redigeringstilstand. Når du er i redigeringstilstand, kan du se, at markøren er placeret et mellemrum væk fra det sidste bogstav. Dette indikerer, at der er en efterfølgende plads i posten.
Brug TRIM-funktionen til at løse problemet.
=TRIM(D4)fjerner førende mellemrum, bageste mellemrum og erstatter alle interne dobbeltrum med et enkelt mellemrum. I dette tilfælde fungerer TRIM perfekt til at fjerne det bageste rum.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)er formlen. -
Jeg nævnte et bonustip i shownoterne: hvordan man erstatter # N / A-resultatet for manglende værdier med et tomt. Hvis din opslagsværdi ikke er i opslagstabellen, returnerer VLOOKUP en N / A-fejl.
Denne formel bruger
=ISNA()funktionen til at registrere, om resultatet af formlen er en N / A-fejl. Hvis du får fejlen, vil det 2. argument i IF-funktionen fortælle Excel at indsætte den tekst, du ønsker.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP giver dig mulighed for at spare tid, når du matcher lister med data. Tag dig tid til at lære den grundlæggende brug, og du vil være i stand til at udføre langt mere kraftfulde opgaver i Excel.
