Excel VLOOKUP je zmogljiv, vendar v posebnih situacijah ne bo deloval. Danes si oglejte, kako odpraviti težave z VLOOKUP.
VLOOKUP je moja najljubša funkcija v Excelu. Če lahko izvedete VLOOKUP, lahko v Excelu rešite številne težave. Vendar obstajajo stvari, ki lahko sprožijo VLOOKUP. Ta tema govori o nekaj izmed njih.
Najprej pa osnove VLOOKUP-a v preprosti angleščini.
Podatki v A: C so prišli iz IT oddelka. Prosili ste za prodajo po artiklu in datumu. Dali so vam številko izdelka. Potrebujete opis izdelka. Namesto da čakate, da oddelek za IT ponovno zažene podatke, najdete tabelo, prikazano v stolpcu F: G.
Želite, da VLOOKUP najde element v A2, medtem ko išče po prvem stolpcu tabele v $ F $ 3: $ G $ 30. Ko VLOOKUP najde ujemanje v F7, želite, da VLOOKUP vrne opis iz drugega stolpca tabele. Vsak VLOOKUP, ki išče natančno ujemanje, se mora končati z False (ali nič, kar je enakovredno False). Spodnja formula je pravilno nastavljena.
Upoštevajte, da s F4 naslovu iskalne tabele dodate znake štirih dolarjev. Ko kopirate formulo v stolpec D, potrebujete, da naslov iskalne tabele ostane nespremenjen. Obstajata dve pogosti možnosti: Kot iskalno tabelo lahko določite celotne stolpce F: G. Lahko pa F3: G30 poimenujete z imenom, kot je ItemTable. Če uporabljate =VLOOKUP(A2,ItemTable,2,False), imenovani obseg deluje kot absolutna referenca.
Kadar koli naredite vrsto VLOOKUP-ov, morate razvrstiti stolpec VLOOKUP-ov. Razvrstite ZA in morebitne napake # N / A bodo na vrhu. V tem primeru obstaja ena. V iskalni tabeli manjka element BG33-9. Mogoče je tiskarska napaka. Mogoče gre za povsem nov izdelek. Če je nov, vstavite novo vrstico kamor koli sredi iskalne tabele in dodajte nov element.
Dokaj normalno je, da imamo nekaj # N / A napak. Toda na spodnji sliki popolnoma enaka formula ne vrne nič drugega kot # N / A. Ko se to zgodi, vidim, ali lahko rešim prvi VLOOKUP. Iščete BG33-8 iz A2. Začnite križariti po prvem stolpcu iskalne tabele. Kot lahko vidite, je ujemajoča se vrednost očitno v F10. Zakaj lahko to vidite, Excel pa ne?
Pojdite na vsako celico in pritisnite tipko F2. Tukaj je F10. Upoštevajte, da se kazalec za vstavljanje prikaže takoj za 8.
Tu je celica A2 v načinu urejanja. Kazalec za vstavljanje je nekaj presledkov oddaljen od 8. To je znak, da so bili ti podatki v določenem trenutku shranjeni v starem naboru podatkov COBOL. Nazaj v COBOL, če bi bilo polje Element definirano kot 10 znakov in ste vtipkali le 6 znakov, bi ga COBOL zapolnil s 4 dodatnimi presledki.
Rešitev? Namesto da poiščete A2, poiščite TRIM(A2).
Funkcija TRIM () odstranjuje vodilni in zadnji prostor. Če imate med besedami več presledkov, jih bo TRIM pretvoril v en presledek. Na spodnji sliki so presledki pred in za obema imenoma v A1. =TRIM(A1)odstrani vse, razen enega prostora v A3.
Mimogrede, kaj če bi težava zaostajala za presledki v stolpcu F namesto v stolpcu A? V stolpec E dodajte stolpec funkcij TRIM (), ki kaže na stolpec F. Kopirajte jih in prilepite kot vrednosti v F, da bodo poizvedbe znova začele delovati.
Tu je prikazan drugi zelo pogost razlog, da VLOOKUP ne bo deloval. Stolpec F ima realne številke. Stolpec A vsebuje besedilo, ki je videti kot številke.
Izberite ves stolpec A. Pritisnite alt = "" + D, E, F. To bo privzeto besedilo v stolpce in bo pretvorilo vsa besedilna števila v dejanske številke. Iskanje začne znova delovati.
Oglejte si video
- VLOOKUP rešuje številne težave
- Pogoste težave z VLOOKUP:
- Če VLOOKUP začne delovati, vendar # N / A postane bolj opazen: pozabite $ v iskalni tabeli
- Nekaj # N / A: v tabeli manjka elementov
- Noben od VLOOKUP ne deluje: preverite, ali obstajajo presledki
- S TRIM odstranite zadnje presledke
- Številke in številke, shranjene kot besedilo
- Izberite oba stolpca in uporabite alt = "" + DEF
- Epizoda vključuje šalo, ki se računovodjem in IT-jevcem zdi smešna, vendar iz različnih razlogov
Video zapis
Naučite se Excel iz podcasta, epizoda 2027 - Odpravljanje težav VLOOKUP!
V redu, podcasting celotno to knjigo, kliknite "i" v zgornjem desnem kotu, da pridete do seznama predvajanja!
VLOOKUP je moja najljubša funkcija v celotnem Excelu in to šalo vedno povem na enem od svojih seminarjev in nasmeji se, ali ste IT-jevec ali ne. Vedno rečem: »No, glejte, tukaj na levi imamo te podatke in jih lahko pogledam in povem, da so podatki prišli iz oddelka za informacijsko tehnologijo, ker je to točno tisto, kar sem prosil, ne pa tudi tisto, kar sem potreboval. Prosil sem za datum in količino artikla, dali so mi datum in količino številke artikla, vendar se niso potrudili, da bi mi dali opis, kajne? " In računovodje se temu vedno smejijo, saj se jim to ves čas dogaja, informatiki pa so: "No, dal sem vam, kar ste zahtevali, nisem kriv!" Torej oba menita, da je smešno iz različnih razlogov, zato, veste, in IT-jevec je zaposlen, ne more se vrniti k prepisovanju poizvedbezato moramo sami nekaj rešiti.
Tako ta majhna tabela, ki sem jo kopiral od nekje drugje v svojem računalniku, v preprostem angleškem jeziku, pravi VLOOKUP: "Hej, imamo številko izdelka W25-6." Tu imamo tabelo, želim se pomakniti skozi prvi stolpec tabele, dokler ne najdem te številke elementa, in nato vrniti nekaj iz te vrstice. V redu, v tem primeru hočem drugi stolpec iz te vrstice. In na koncu vsakega VLOOKUP-a moramo postaviti bodisi FALSE bodisi 0. Zdaj tukaj, potem ko izberem obseg, pritisnem tipko F4, nato pa želim drugi stolpec in nato FALSE za natančen tekmo. Nikoli ne izberite približnega ujemanja, nikoli, nikoli! To ni približna tekma, je zelo posebna stvar, ki ne deluje ves čas. Če želite svoje številke ponastaviti Komisiji za vrednostne papirje in borze, jih vsekakor uporabite,TRUE ali preprosto pustite, FALSE izključeno. Toda vsak VLOOKUP, ki ga kdajkoli ustvarite, se mora končati z, FALSE ali, 0, 0 je krajši od FALSE, tja bi morali vstaviti FALSE, toda 0 je enako kot FALSE.
V redu, najprej odpravljanje težav, ko naredite cel kup VLOOKUP-ov, je zelo normalno, da imate nekaj # N / As, kajne? In vedno najdem # N / A s pomočjo Data, ZA, ki pripelje # N / As na vrh, tam, BG33-9, bodisi gre za tipkarsko napako ali gre za popolnoma nov izdelek, da bi to ugotovili. Torej tukaj na desni imam nove podatke, to bom izrezal, nato pa Alt + IED, vstavite te celice na sredino, ni nujno, da so abecedne, ni treba razvrščati te tabele. Ko uporabljate različico FALSE, tabela ni - lahko jo preprosto postavite kamorkoli želite, nisem dal na koncu, ker formule ni bilo treba prepisovati, želim le, delo. V redu, torej nekaj # N / A, izjemno, skrajno običajnih, a poglejte to.
Ko začnete z odgovori, ki delujejo, nato pa se številke N / A začnejo pojavljati rahlo pogosto, nato pa se sčasoma prikažejo do konca, to je zanesljiv znak, da niste zaklenili sklica tabele. V redu, glej, tukaj se tabela premika, ko kopiram formulo navzdol, kajne in tako imam srečo, ko sem zadel nekaj, ki so bili na koncu seznama. Ampak sčasoma pridem do točke, ko gleda tu v povsem praznih celicah in seveda ni ničesar najti. Torej, to je prva stvar, dobiš par, ki deluje, nekaj # N / A, še nekaj, ki delujejo, nato pa še # N / A preostali del poti - zanesljiv znak, da nisi dal $ v.
Pojdite nazaj, F2 za način urejanja, izberite dvopičje, pritisnite F4, da vnesete vse $, dvokliknite, da ga sestrelite, in stvari začnejo znova delovati, v redu. Naslednja, popolnoma enaka formula, formula je popolna, BG33-8 glej, nič od tega ne dobimo prav. V redu, torej grem pogledat, BG33-8, hej, tam je, tam je, v rdeči barvi bi ga moral videti! Torej pridem do tega na desni strani, pritisnem F2 in opazujem utripajočo točko vstavljanja in vidim, takoj za 8 je tako, potem pa pridem sem in pritisnem F2, tam so nekaj zaostalih prostorov tam. To je zelo, zelo pogosto v času COBOL-a, rekli bi: "Veste, poglejte, dali vam bomo 10 presledkov za številko predmeta, in če ne vtipkate vseh 10 presledkov, bodo zapolnili prostore . " In to je zelo pogosto,in odlična rešitev tukaj je, da se s funkcijo TRIM znebite tistih vodilnih in zadnjih prostorov. Namesto A2 uporabite TRIM (A2), dvokliknite, da ga ustrelite, v redu, še vedno je BG33-9 spet v drugi tabeli.
V redu, samo da boste razumeli, kako deluje TRIM, sem vtipkal kup presledkov, John, kup presledkov, Durran in kup presledkov, nato pa sem združil * pred in po, tako da lahko vidite, kako izgleda . Ko prosim za TRIM (P4), se znebimo vseh vodilnih prostorov, vseh zadnjih prostorov, nato pa se več notranjih prostorov zmanjša na en prostor. V redu, tako da lahko vidite, nekako si predstavljate, da se znebijo vodilnih in zaostalih prostorov, vsi podvojeni prostori na sredini postanejo en sam prostor. Torej TRIM, odlično, izvrstno orodje v vašem arzenalu, v redu, tukaj je še eno zelo pogosto.
Če to niso zadnji presledki, potem je najpogostejša stvar, ki sem jo videl, tam, kjer imamo resnične številke in tukaj imamo številke, shranjene kot besedilo. In VLOOKUP tega ne bo videl kot ujemanje, čeprav je 4399 to številka, to je besedilo, ne deluje. Najhitreje pretvorite stolpec besedila v številke, izberite stolpec, tri črke v zaporedju, alt = "" DEF in nenadoma naši VLOOKUP-i spet začnejo delovati, odličen, odličen namig iz približno 1500 podcastov. Torej, to so najpogostejše težave z VLOOKUP-om, ali ste pozabili $, ali imate končne presledke ali pa imate številke in številke, shranjene kot besedilo. Vsi ti nasveti so v tej knjigi “MrExcel XL”, kliknite ihe “i” v zgornjem desnem kotu, knjigo lahko kupite.
V redu, hitro povzemanje: VLOOKUP rešuje številne težave, če VLOOKUP začne delovati, vendar # N / A! postane bolj viden, ste pozabili vstaviti $ v iskalno tabelo. Če je nekaj # N / A, v tabeli manjkajo le predmeti. Če noben od VLOOKUP-ov ne deluje in je besedilo, preverite, ali obstajajo presledki, lahko uporabite funkcijo TRIM. Če imate številke in številke shranjene kot besedilo, izberite kateri koli stolpec, tisti, ki vsebuje besedilo, nato pa alt = "" DEF mora vse te vrniti na številke.
V redu, hej, zahvaljujem se vam, da ste se ustavili, se vidimo naslednjič za še eno oddajo!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2027.xlsx
