Mange prøver å bruke Excel som en database. Selv om det kan fungere som en database, er noen av oppgavene som vil være veldig enkle i et databaseprogram ganske komplekse i Excel. En av disse oppgavene er å matche to lister basert på et felles felt; Dette kan enkelt oppnås ved hjelp av Excel VLOOKUP. Du vil finne at funksjonen VLOOKUP er ekstremt nyttig, så sjekk ut et eksempel på når og hvordan du bruker denne funksjonen nedenfor.
Si at reisebyrået ditt sender deg en månedsrapport om alle stedene dine ansatte har reist. Rapporten bruker flyplasskoder i stedet for bynavn. Det ville være nyttig hvis du enkelt kunne legge inn det virkelige bynavnet i stedet for bare koden.
På Internett finner du og importerer en liste som viser bynavnet for hver flyplasskode.
Men hvordan får du denne informasjonen til hver post i rapporten?
- Bruk VLOOKUP-funksjonen. VLOOKUP står for “Vertical Lookup”. Det kan brukes når som helst at du har en liste over data med nøkkelfeltet i kolonnen til venstre.
- Begynn å skrive funksjonen
=VLOOKUP(,. Skriv Ctrl + A for å få hjelp med funksjonen. - VLOOKUP trenger fire parametere. Først er bykoden i den opprinnelige rapporten. I dette eksemplet vil det være celle D4
- Den neste parameteren er området med oppslagstabellen. Fremhev rekkevidden. Sørg for å bruke F4 for å gjøre rekkevidden absolutt. (En absolutt referanse har et dollartegn før både kolonnenummeret og radnummeret. Når formelen kopieres, vil referansen fortsette å peke mot I3: J351.
- Den tredje parameteren forteller Excel i hvilken kolonne bynavnet er funnet. I området I3: J351 er bynavnet i kolonne 2. Skriv inn en 2 for denne parameteren.
- Den fjerde parameteren forteller Excel om en "lukk" match er OK. I dette tilfellet er det ikke, så skriv inn False.
- Klikk OK for å fullføre formelen. Dra fyllhåndtaket for å kopiere formelen.
- Fordi du nøye har skrevet inn absolutte formler, kan du kopiere kolonne E til kolonne D for å få destinasjonsbyen. I dette tilfellet er alle avgangene fra Pearson International Airport i Toronto.
Mens dette eksemplet fungerte perfekt, når seere bruker VLOOKUP, betyr det vanligvis at de samsvarer med lister som kom fra forskjellige kilder. Når lister kommer fra forskjellige kilder, kan det alltid være subtile forskjeller som gjør listene vanskelig å matche. Her er tre eksempler på hva som kan gå galt, og hvordan du kan rette dem.
-
Den ene listen har bindestreker og den andre listen ikke. Bruk
=SUBSTITUTE()funksjonen til å fjerne bindestrekene. Første gang du prøver VLOOKUP, får du N / A-feil.For å fjerne bindestrekene med en formel, bruk SUBSTITUTE-formelen. Bruk 3 argumenter. Det første argumentet er cellen som inneholder verdien. Det neste argumentet er teksten du vil endre. Det siste argumentet er erstatningsteksten. I dette tilfellet vil du endre bindestreker til ingenting, så formelen er
=SUBSTITUTE(A4,"-","").Du kan pakke den funksjonen inn i VLOOKUP for å få beskrivelsen.
-
Denne er subtil, men veldig vanlig. Én liste har etterfølgende tomrom etter oppføringen. Bruk = TRIM () for å fjerne overflødige mellomrom. Når du først skriver inn formelen, finner du at alle svarene er N / A-feil. Du vet helt sikkert at verdiene er i listen, og at alt ser bra ut med formelen.
En standard ting å sjekke er å flytte til cellen med oppslagsverdien. Trykk på F2 for å sette cellen i redigeringsmodus. Når du er i redigeringsmodus, kan du se at markøren er plassert ett mellomrom fra den siste bokstaven. Dette indikerer at det er en etterfølgende plass i oppføringen.
For å løse problemet, bruk TRIM-funksjonen.
=TRIM(D4)vil fjerne ledende mellomrom, etterfølgende mellomrom og erstatte eventuelle interne dobbeltrom med ett mellomrom. I dette tilfellet fungerer TRIM perfekt for å fjerne bakrommet.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)er formelen. -
Jeg nevnte et bonustips i shownotatene: hvordan erstatte # N / A-resultatet for manglende verdier med et blankt. Hvis oppslagsverdien ikke er i oppslagstabellen, vil VLOOKUP returnere en N / A-feil.
Denne formelen bruker
=ISNA()funksjonen til å oppdage om resultatet av formelen er en N / A-feil. Hvis du får feilen, vil det andre argumentet i IF-funksjonen fortelle Excel å legge inn hvilken som helst tekst du ønsker.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP lar deg spare tid når du samsvarer med lister med data. Ta deg tid til å lære deg den grunnleggende bruken, og du vil kunne gjøre langt kraftigere oppgaver i Excel.
