Excel VLOOKUP er kraftig, men det fungerer ikke når du har spesifikke situasjoner. I dag, se på hvordan du feilsøker VLOOKUP.
VLOOKUP er min favorittfunksjon i Excel. Hvis du kan gjøre VLOOKUP, kan du løse mange problemer i Excel. Men det er ting som kan trekke opp en VLOOKUP. Dette emnet snakker om noen få av dem.
Men først, det grunnleggende om VLOOKUP på vanlig engelsk.
Dataene i A: C kom fra IT-avdelingen. Du ba om salg etter vare og dato. De ga deg varenummer. Du trenger varebeskrivelse. I stedet for å vente på at IT-avdelingen kjører dataene på nytt, finner du tabellen vist i kolonne F: G.
Du vil at VLOOKUP skal finne varen i A2 mens den søker gjennom den første kolonnen i tabellen i $ F $ 3: $ G $ 30. Når VLOOKUP finner samsvaret i F7, vil du at VLOOKUP skal returnere beskrivelsen i den andre kolonnen i tabellen. Hver VLOOKUP som leter etter en nøyaktig samsvar, må slutte med False (eller null, som tilsvarer False). Formelen nedenfor er satt opp riktig.
Legg merke til at du bruker F4 for å legge til fire dollartegn til adressen for oppslagstabellen. Når du kopierer formelen ned kolonne D, trenger du adressen for oppslagstabellen for å forbli konstant. Det er to vanlige alternativer: Du kan spesifisere hele kolonnene F: G som oppslagstabellen. Eller du kan gi navnet F3: G30 med et navn som ItemTable. Hvis du bruker =VLOOKUP(A2,ItemTable,2,False), fungerer det navngitte området som en absolutt referanse.
Hver gang du gjør en rekke VLOOKUP-er, må du sortere kolonnen med VLOOKUP-er. Sorter ZA, og eventuelle # N / A-feil kommer til toppen. I dette tilfellet er det en. Vare BG33-9 mangler i oppslagstabellen. Kanskje det er en skrivefeil. Kanskje det er en helt ny vare. Hvis den er ny, setter du inn en ny rad hvor som helst i midten av oppslagstabellen og legger til det nye elementet.
Det er ganske normalt å ha noen # N / A-feil. Men i figuren nedenfor gir nøyaktig samme formel ingenting annet enn # N / A. Når dette skjer, ser jeg om jeg kan løse den første VLOOKUP. Du leter etter BG33-8 funnet i A2. Begynn å cruise ned gjennom den første kolonnen i oppslagstabellen. Som du kan se, er den samsvarende verdien tydelig i F10. Hvorfor kan du se dette, men Excel kan ikke se det?
Gå til hver celle og trykk på F2-tasten. Her er F10. Merk at innsettingsmarkøren vises rett etter 8.
Her er celle A2 i redigeringsmodus. Innsettingsmarkøren er et par mellomrom unna 8. Dette er et tegn på at disse dataene på et tidspunkt ble lagret i et gammelt COBOL-datasett. Tilbake i COBOL, hvis Element-feltet ble definert som 10 tegn og du bare skrev 6 tegn, ville COBOL legge det til 4 ekstra mellomrom.
Løsningen? I stedet for å slå opp A2, slå opp TRIM(A2).
TRIM () -funksjonen fjerner ledende og etterfølgende mellomrom. Hvis du har flere mellomrom mellom ord, vil TRIM konvertere dem til ett mellomrom. I figuren nedenfor er det mellomrom før og etter begge navnene i A1. =TRIM(A1)fjerner alt bortsett fra ett mellomrom i A3.
Hva forresten, hvis problemet hadde vært å følge mellomrom i kolonne F i stedet for kolonne A? Legg til en kolonne med TRIM () -funksjoner til E, pek på kolonne F. Kopier dem og lim inn som verdier i F for å få oppslagene til å begynne å fungere igjen.
Den andre veldig vanlige årsaken til at VLOOKUP ikke fungerer, vises her. Kolonne F har reelle tall. Kolonne A har tekst som ser ut som tall.
Velg hele kolonne A. Trykk alt = "" + D, E, F. Dette gjør en standard tekst til kolonner og konverterer alle tekstnumre til reelle tall. Oppslaget begynner å fungere igjen.
Se på video
- VLOOKUP løser mange problemer
- Vanlige VLOOKUP problemer:
- Hvis VLOOKUP begynner å fungere, men # N / A blir mer fremtredende: glemte $ i oppslagstabellen
- Noen få # N / A: elementer som mangler fra tabellen
- Ingen av VLOOKUP fungerer: se etter bakrom
- Fjern etterfølgende mellomrom med TRIM
- Tall og tall lagret som tekst
- Velg begge kolonnene og bruk alt = "" + DEF
- Episode inkluderer en vits som både regnskapsførere og IT-folk synes er morsomme, men av forskjellige grunner
Videoutskrift
Lær Excel fra podcast, episode 2027 - Feilsøking VLOOKUP!
OK, podcasting av hele denne boken, klikk på “i” øverst til høyre for å komme til spillelisten!
VLOOKUP er min favorittfunksjon i hele Excel, og jeg forteller alltid denne vitsen i seminarene mine, og det blir latterlig om du er IT-person eller ikke IT-person. Jeg sier alltid ”Vel, vi har dette datasettet her til venstre, og jeg kan se på dataene og fortelle at dataene kom fra IT-avdelingen fordi det er akkurat det jeg ba om, men egentlig ikke det jeg trengte. Jeg ba om varedato og antall, og de ga meg varenummer dato og antall, men de gadd ikke gi meg beskrivelse, ikke sant? " Og regnskapsførerne ler alltid av dette, for dette skjer med dem hele tiden, og IT-gutta er som "Vel, jeg ga deg det du ba om, det er ikke min feil!" Så begge synes det er morsomt av forskjellige grunner, så vet du, og IT-fyren er opptatt, han kan ikke komme tilbake til å skrive om spørringen,så vi må gjøre noe for å redde dette selv.
Og så dette lille bordet jeg kopierte fra et annet sted på datamaskinen min, på vanlig engelsk, det VLOOKUP gjør, sier: "Hei, vi har varenummer W25-6." Vi har et bord her, jeg vil cruise ned gjennom den første kolonnen i tabellen til jeg finner det varenummeret, og deretter returnere noe fra den raden. OK, i dette tilfellet er det jeg vil ha den andre kolonnen fra den raden. Og så på slutten av hver VLOOKUP må vi sette enten FALSE eller 0. Nå akkurat her, etter at jeg har valgt området, skal jeg trykke på F4-tasten, og så vil jeg ha 2. kolonne og deretter FALSE for en nøyaktig kamp. Velg aldri omtrentlig kamp, aldri, aldri! Det er ikke en omtrentlig kamp, det er en veldig spesiell ting, det fungerer ikke hele tiden. Hvis du liker å omstille tallene dine til Securities and Exchange Commission, er du velkommen til å bruke,SANT eller bare la, FALSE være av. Men hver VLOOKUP som du noen gang oppretter, bør ende med, FALSE eller, 0, 0 er kortere enn FALSE, du skal sette en FALSE der, men en 0 er den samme som FALSE.
Greit nå, feilsøking, første ting, når du gjør en hel haug med VLOOKUP, er det veldig normalt å ha et par # N / As, ikke sant? Og jeg finner alltid # N / A ved å gå Data, ZA, som bringer # N / As til toppen, akkurat der, BG33-9, enten det er en skrivefeil eller det er et helt nytt element, ok, og vi har å finne det ut. Så her til høyre har jeg de nye dataene, jeg vil kutte det, og deretter Alt + IED, sett inn cellene i midten, det trenger ikke å være alfabetisk, denne tabellen trenger ikke å bli sortert. Når du bruker, FALSE-versjonen, er ikke tabellen - du kan bare legge den hvor som helst du vil, jeg la ikke på slutten fordi jeg ikke trengte å skrive om formelen, jeg vil bare at formelen skal arbeid. OK, så et par # N / A-er, ekstremt, ekstremt normalt, men sjekk dette ut.
Når du begynner med svar som fungerer, men da # N / A begynner å vises litt ofte, og til slutt vises de helt ned, det er et sikkert tegn på at du ikke låste ned tabellreferansen. Greit, så her er tabellen i bevegelse mens jeg kopierer formelen ned, til høyre, og så blir jeg heldig som treffer noen få som var på slutten av listen. Men til slutt kommer jeg ned til det punktet hvor det ser over her i helt tomme celler, og selvfølgelig blir ingenting funnet. OK, så det er det første du får et par som fungerer, noen # N / A-er, et par til som fungerer, og så er alle # N / A resten av veien - sikkert tegn på at du ikke la $ inn.
Bare gå tilbake, F2 for redigeringsmodus, velg kolon, trykk F4, som setter alle $ i, dobbeltklikk for å skyte det ned, og ting begynner å fungere igjen, ok. Neste, nøyaktig samme formel, formelen er perfekt, BG33-8 ser, vi får ikke noe av det riktig. Greit, så jeg går og ser, BG33-8, hei, det er det, det er der, det er i rødt, jeg burde sett det! Så jeg kommer til denne på høyre side, jeg trykker på F2, og jeg ser på det blinkende innsettingspunktet, og ser, det er rett etter 8, slik, og så kommer jeg hit og jeg trykker F2, det er noen bakrom der. Dette er veldig, veldig vanlig i COBOLs dager, de vil si "Du vet, se, vi skal gi deg 10 mellomrom for varenummer, og hvis du ikke skriver inn alle de 10 mellomromene, vil de fylle mellomrommene . ” Og så er dette veldig vanlig,og den gode løsningen her er å kvitte seg med de ledende og etterfølgende plassene med TRIM-funksjonen. I stedet for A2 bruk TRIM (A2), dobbeltklikk for å skyte det ned, ok, fremdeles er BG33-9 tilbake i den andre tabellen.
Greit, bare slik at du forstår hvordan TRIM fungerer, så jeg skrev en haug med mellomrom, John, en haug med mellomrom, Durran og en haug med mellomrom, og så sammenkoblet jeg en * før og etter slik at du kan se hvordan det ser ut . Når jeg ber om TRIM (P4), blir vi kvitt alle de ledende rommene, alle de etterfølgende rommene, og deretter reduseres de flere indre rommene ned til ett mellomrom. Greit, slik at du kan se, slags visualisere der, at de blir kvitt ledende og etterfølgende mellomrom. Eventuelle doblede mellomrom blir et enkelt rom som det. Så TRIM, flott, flott verktøy i arsenalet ditt, ok, her er en annen veldig vanlig.
Hvis det ikke er etterliggende mellomrom, er det vanligste jeg har sett hvor vi har sanne tall her, og her har vi lagret tall som tekst. Og VLOOKUP vil ikke se det som en kamp, selv om 4399, dette er et tall, dette er tekst, fungerer ikke. Raskeste måten å konvertere en tekstkolonne til tall, velg kolonnen, tre bokstaver i rekkefølge, alt = "" DEF, og plutselig begynner VLOOKUP-ene våre å fungere igjen, flott, flott tips fra ca 1500 podcaster siden. Greit, så det er de vanligste VLOOKUP-problemene, enten du har glemt $, eller du har etterfølgende mellomrom, eller så har du tall og tall lagret som tekst. Alle disse tipsene er i denne boken "MrExcel XL", klikk på "i" øverst til høyre, du kan kjøpe boken.
OK, rask oppsummering: VLOOKUP løser mange problemer hvis en VLOOKUP begynner å fungere, men # N / A! blir mer fremtredende, glemte du å sette $ i oppslagstabellen. Hvis det er noen # N / A-er, er det bare ting som mangler fra bordet. Hvis ingen av VLOOKUP-ene fungerer og det er tekst, kan du se etter mellomrom. Du kan bruke TRIM-funksjonen. Hvis du har tall og tall lagret som tekst, velg en av kolonnene, den som har teksten, og gjør alt = "" DEF til alle som er tilbake til tall.
Greit vel hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2027.xlsx
