Excel VLOOKUP er stærk, men det fungerer ikke, når du har specifikke situationer. I dag skal du se, hvordan du foretager fejlfinding af VLOOKUP.
VLOOKUP er min yndlingsfunktion i Excel. Hvis du kan gøre VLOOKUP, er du i stand til at løse mange problemer i Excel. Men der er ting, der kan udløse en VLOOKUP. Dette emne taler om nogle få af dem.
Men først, det grundlæggende i VLOOKUP på almindelig engelsk.
Dataene i A: C kom fra it-afdelingen. Du bad om salg efter vare og dato. De gav dig varenummer. Du har brug for varebeskrivelse. I stedet for at vente på, at it-afdelingen kører dataene igen, finder du tabellen vist i kolonne F: G.
Du vil have VLOOKUP til at finde elementet i A2, mens det søger gennem den første kolonne i tabellen i $ F $ 3: $ G $ 30. Når VLOOKUP finder matchet i F7, vil du have VLOOKUP til at returnere beskrivelsen i den anden kolonne i tabellen. Hver VLOOKUP, der er på udkig efter et nøjagtigt match, skal slutte med Falsk (eller nul, hvilket svarer til Falsk). Formlen nedenfor er konfigureret korrekt.
Bemærk, at du bruger F4 til at tilføje fire dollartegn til adressen til opslagstabellen. Når du kopierer formlen ned i kolonne D, skal du bruge adressen til, at opslagstabellen forbliver konstant. Der er to almindelige alternativer: Du kan angive hele kolonnerne F: G som opslagstabel. Eller du kan navngive F3: G30 med et navn som ItemTable. Hvis du bruger =VLOOKUP(A2,ItemTable,2,False), fungerer det navngivne interval som en absolut reference.
Hver gang du laver en masse VLOOKUP'er, skal du sortere kolonnen med VLOOKUPs. Sorter ZA, og eventuelle # N / A-fejl kommer til toppen. I dette tilfælde er der en. Vare BG33-9 mangler i opslagstabellen. Måske er det en skrivefejl. Måske er det en helt ny genstand. Hvis det er nyt, skal du indsætte en ny række hvor som helst i midten af din opslagstabel og tilføje det nye element.
Det er ret normalt at have et par # N / A-fejl. Men i nedenstående figur returnerer nøjagtigt den samme formel intet andet end # N / A. Når dette sker, ser jeg, om jeg kan løse den første VLOOKUP. Du ser på BG33-8, der findes i A2. Start med at køre ned gennem den første kolonne i opslagstabellen. Som du kan se, er den matchende værdi klart i F10. Hvorfor kan du se dette, men Excel kan ikke se det?
Gå til hver celle og tryk på F2-tasten. Her er F10. Bemærk, at indsættelsesmarkøren vises lige efter 8.
Her er celle A2 i redigeringstilstand. Indsættelsesmarkøren er et par mellemrum væk fra 8. Dette er et tegn på, at disse data på et eller andet tidspunkt blev gemt i et gammelt COBOL-datasæt. Tilbage i COBOL, hvis elementfeltet var defineret som 10 tegn, og du kun skrev 6 tegn, ville COBOL pude det med 4 ekstra mellemrum.
Løsningen? I stedet for at kigge op på A2 skal du kigge op på TRIM(A2).
TRIM () -funktionen fjerner ledende og bageste mellemrum. Hvis du har flere mellemrum mellem ord, konverterer TRIM dem til et enkelt mellemrum. I figuren nedenfor er der mellemrum før og efter begge navne i A1. =TRIM(A1)fjerner alt undtagen et mellemrum i A3.
Hvad forresten, hvis problemet havde været bageste mellemrum i kolonne F i stedet for kolonne A? Føj en kolonne med TRIM () -funktioner til E, pegende på kolonne F. Kopier dem og indsæt som værdier i F for at få opslagene til at arbejde igen.
Den anden meget almindelige årsag til, at VLOOKUP ikke fungerer, vises her. Kolonne F har reelle tal. Kolonne A har tekst, der ligner tal.
Vælg hele kolonne A. Tryk på alt = "" + D, E, F. Dette gør en standardtekst til kolonner og konverterer alle tekstnumre til reelle tal. Opslaget begynder at arbejde igen.
Se video
- VLOOKUP løser mange problemer
- Almindelige VLOOKUP problemer:
- Hvis VLOOKUP begynder at arbejde, men # N / A bliver mere fremtrædende: glemte $ i opslagstabellen
- Et par # N / A: elementer mangler i tabellen
- Ingen af VLOOKUP-funktionerne: Kontroller for bageste mellemrum
- Fjern bageste mellemrum med TRIM
- Tal og tal gemt som tekst
- Vælg begge kolonner, og brug alt = "" + DEF
- Episode inkluderer en vittighed, som både revisorer og IT-folk finder sjove, men af forskellige grunde
Videoudskrift
Lær Excel fra podcast, afsnit 2027 - Fejlfinding VLOOKUP!
Okay, podcasting af hele denne bog, klik på "i" i øverste højre hjørne for at komme til afspilningslisten!
VLOOKUP er min yndlingsfunktion i hele Excel, og jeg fortæller altid denne vittighed på et af mine seminarer, og det griner, uanset om du er en it-person eller ikke en it-person. Jeg siger altid ”Nå se, vi har dette datasæt her til venstre, og jeg kan se på disse data og fortælle, at data kom fra IT-afdelingen, fordi det er præcis, hvad jeg bad om, men ikke rigtig, hvad jeg havde brug for. Jeg bad om varedato og antal, og de gav mig dato og antal for varenummer, men de gik ikke med at give mig en beskrivelse, ikke? " Og revisorerne griner altid af dette, for det sker dem hele tiden, og it-fyrene er som "Nå, jeg gav dig, hvad du bad om, det er ikke min skyld!" Så de synes begge, det er sjovt af forskellige grunde, så ved du, og it-fyren har travlt, han kan ikke komme tilbage til at omskrive forespørgslen,så vi er nødt til at gøre noget for at redde dette selv.
Og så denne lille tabel, jeg kopierede fra et andet sted på min computer, på almindelig engelsk, hvad VLOOKUP gør er, siger "Hej, vi har et varenummer W25-6." Vi har en tabel her, jeg vil krydse ned gennem den første kolonne i tabellen, indtil jeg finder det varenummer og derefter returnere noget fra den række. Okay, i dette tilfælde, hvad jeg vil have, er den anden kolonne fra den række. Og så i slutningen af hver VLOOKUP skal vi sætte enten FALSE eller 0. Nu lige her, efter at jeg har valgt området, skal jeg trykke på F4-tasten, og så vil jeg have 2. kolonne og derefter FALSE for en nøjagtig match. Vælg aldrig nogensinde match, aldrig, aldrig! Det er ikke et omtrentligt match, det er en meget speciel ting, det fungerer ikke hele tiden. Hvis du gerne vil omstille dine numre til Securities and Exchange Commission, er du velkommen til at bruge,SAND, eller lad bare FALSE være slået fra. Men hver VLOOKUP, du nogensinde opretter, skal ende med, FALSE eller, 0, 0 er kortere end FALSE, du skal placere en FALSE der, men en 0 er den samme som FALSE.
Okay nu, fejlfinding, første ting, når du laver en hel masse VLOOKUP'er, er det meget normalt at have et par # N / As, ikke? Og jeg finder altid # N / A'er ved at gå til Data, ZA, der bringer # N / As til toppen, lige der, BG33-9, enten det er en tastefejl eller det er en helt ny vare, okay, og vi fik for at finde ud af det. Så her til højre har jeg de nye data, jeg vil klippe det, og derefter Alt + IED, indsæt disse celler i midten, det behøver ikke at være alfabetisk, denne tabel behøver ikke at blive sorteret. Når du bruger FALSE-versionen, er tabellen ikke - du kan bare placere den hvor som helst, du vil, jeg lagde ikke i slutningen, fordi jeg ikke behøvede at omskrive formlen, jeg vil bare have formlen til arbejde. Okay, så et par # N / A'er, ekstremt, ekstremt normalt, men tjek dette ud.
Når du starter med svar, der fungerer, men derefter # N / A begynder at vises lidt hyppigt, og så til sidst vises de hele vejen ned, det er et sikkert tegn på, at du ikke låste tabelhenvisningen ned. Okay, så her bevæger tabellen sig, når jeg kopierer formlen ned, højre, og så bliver jeg heldig ved at ramme et par, der var i slutningen af listen. Men til sidst kommer jeg ned til det punkt, hvor det ser over her i helt tomme celler, og selvfølgelig findes der intet. Okay, så det er den første ting, du får et par, der fungerer, et par # N / A'er, et par mere, der fungerer, og så er alle # N / A resten af vejen - sikkert tegn på, at du ikke lagde $ ind.
Bare gå tilbage, F2 til redigeringstilstand, vælg kolon, tryk på F4, der sætter alle $ i, dobbeltklik for at skyde det ned, og ting begynder at fungere igen, okay. Den næste, nøjagtig samme formel, formlen er perfekt, BG33-8 se, vi får intet af det rigtige. Okay, så jeg kigger, BG33-8, hej, der er det, det er lige der, det er i rødt, jeg skulle have set det! Så jeg kommer til denne på højre side, jeg trykker på F2, og jeg ser det blinkende indsættelsespunkt og ser, det er lige efter 8 sådan, og så kommer jeg herover og jeg trykker på F2, der er nogle bageste rum der. Dette er meget, meget almindeligt tilbage i COBOLs dage, de vil sige "Du ved, se, vi giver dig 10 mellemrum til varenummer, og hvis du ikke skriver alle 10 mellemrum, udfylder de mellemrumene . ” Og så er dette meget almindeligt,og den gode løsning her er at slippe af med de førende og bageste rum med TRIM-funktionen. I stedet for A2 skal du bruge TRIM (A2), dobbeltklik for at skyde det ned, okay, stadig er BG33-9 tilbage i den anden tabel.
Okay, bare så du forstår, hvordan TRIM fungerer, så jeg skrev en masse mellemrum, John, en masse mellemrum, Durran og en masse mellemrum, og så sammenkædede jeg en * før og efter, så du kan se, hvordan det ser ud . Når jeg beder om TRIM (P4), slipper vi for alle de førende rum, alle de bageste rum, og derefter reduceres de flere indvendige rum ned til et mellemrum. Okay, så du kan se, slags visualisere der, at de er ved at slippe af med ledende og bageste rum, ethvert fordoblet rum i midten bliver et sådant rum. Så TRIM, fantastisk, fantastisk værktøj i dit arsenal, okay, her er en anden rigtig almindelig.
Hvis det ikke er de bageste mellemrum, er den mest almindelige ting, jeg har set, hvor vi her har sande tal, og her har vi numre gemt som tekst. Og VLOOKUP vil ikke se det som et match, selvom 4399, dette er et tal, dette er tekst, fungerer ikke. Den hurtigste måde at konvertere en tekstkolonne til tal, vælg kolonnen, 3 bogstaver i rækkefølge, alt = "" DEF, og pludselig begynder vores VLOOKUP'er at arbejde igen, fantastisk, godt tip fra omkring 1500 podcasts siden. Okay, så det er de mest almindelige VLOOKUP-problemer, enten har du glemt $, eller du har efterfølgende mellemrum, eller du har numre og tal gemt som tekst. Alle disse tip findes i denne bog "MrExcel XL", klik på "i" øverst til højre, du kan købe bogen.
Okay, hurtig resumé: VLOOKUP løser mange problemer, hvis en VLOOKUP begynder at arbejde, men # N / A! bliver mere fremtrædende, har du glemt at sætte $ i opslagstabellen. Hvis der er et par # N / A'er, mangler det bare ting i tabellen. Hvis ingen af VLOOKUP'erne fungerer, og det er tekst, skal du kontrollere om der er mellemrum, du kan bruge TRIM-funktionen. Hvis du har tal og tal gemt som tekst, skal du vælge en af kolonnerne, den der har teksten, og derefter gøre alt = "" DEF til alle dem tilbage til numrene.
Okay hej, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2027.xlsx
