Exceli VLOOKUP on võimas, kuid see ei toimi, kui teil on konkreetseid olukordi. Täna heitke pilk VLOOKUPi tõrkeotsingule.
VLOOKUP on minu Exceli lemmikfunktsioon. Kui saate teha VLOOKUPi, saate Excelis lahendada palju probleeme. Kuid on asju, mis võivad VLOOKUPi üles tõsta. See teema räägib mõnest neist.
Aga kõigepealt VLOOKUPi põhitõed lihtsas inglise keeles.
Andmed jaotises A: C tulid IT-osakonnast. Palusid müüki kauba ja kuupäeva järgi. Nad andsid teile kauba numbri. Teil on vaja üksuse kirjeldust. Selle asemel, et oodata, kuni IT-osakond andmete uuesti käivitab, leiate veerus F: G näidatud tabeli.
Kui soovite, et VLOOKUP leiaks üksuse A2 üksusest, kui see otsib tabeli esimeses veerus jaotises $ F $ 3: $ G $ 30. Kui VLOOKUP leiab vaste F7-st, soovite, et VLOOKUP tagastaks tabeli teises veerus leitud kirjelduse. Iga VLOOKUP, mis otsib täpset vastet, peab lõppema valega (või nulliga, mis on samaväärne valega). Alltoodud valem on õigesti seadistatud.
Pange tähele, et kasutate otsingu tabeli aadressile nelja dollarimärgi lisamiseks klahvi F4. Valemi veergu D alla kopeerimisel vajate aadressi, et otsingutabel püsiks püsiv. On kaks levinumat alternatiivi: otsingutabelina võite määrata kogu veeru F: G. Või võite nimetada F3: G30 sellise nimega nagu ItemTable. Kui kasutate =VLOOKUP(A2,ItemTable,2,False), toimib nimetatud vahemik absoluutse võrdlusena.
Iga kord, kui teete hulga VLOOKUPe, peate VLOOKUPide veeru sortima. Sorteeri ZA ja kõik # N / A vead tõusevad üles. Sellisel juhul on üks. Üksus BG33-9 puudub otsingu tabelist. Võib-olla on see kirjaviga. Võib-olla on see täiesti uus toode. Kui see on uus, sisestage uus rida suvalisse otsingutabeli keskele ja lisage uus üksus.
On üsna normaalne, et teil on vähe # puuduva viga. Kuid alloleval joonisel ei anna täpselt sama valem muud kui # N / A. Kui see juhtub, näen, kas suudan esimese VLOOKUPi lahendada. Otsite üles A2-st leitud BG33-8. Alustage otsingutabeli esimese veeru kaudu liikumist. Nagu näete, on vastav väärtus selgelt F10-s. Miks te seda näete, kuid Excel seda ei näe?
Minge igasse lahtrisse ja vajutage klahvi F2. Siin on F10. Pange tähele, et sisestuskursor kuvatakse kohe pärast klahvi 8.
Siin on redigeerimisrežiimis lahter A2. Sisestuskursor on paar tühikut punktist 8. See on märk, et mingil hetkel salvestati need andmed vanasse COBOL-i andmekogumisse. Tagasi COBOL-is, kui väli Element oli määratletud 10 tähemärgina ja sisestasite ainult 6 tähemärki, lisaks COBOL sellele 4 täiendavat tühikut.
Lahendus? Selle asemel, et otsida üles A2, otsige üles TRIM(A2).
Funktsioon TRIM () eemaldab ees- ja tagaosad. Kui teil on sõnade vahel mitu tühikut, teisendab TRIM need üheks tühikuks. Alloleval joonisel on A1-s mõlema nime ees ja järel tühikud. =TRIM(A1)eemaldab A3-s kõik tühikud peale ühe.
Muide, mis oleks, kui probleem oleks olnud veeru F asemel tühikud F veerus? Lisage funktsiooni TRIM () veerg E-le, osutades veergule F. Kopeerige need ja kleepige väärtustena F-i, et otsingud uuesti tööle hakkaksid.
Teine väga levinud põhjus, miks VLOOKUP ei tööta, on kuvatud siin. Veerus F on reaalarvud. A-veerus on tekst, mis näeb välja nagu arv.
Valige kogu veerg A. Vajutage alt = "" + D, E, F. See muudab vaiketeksti veergudeks ja teisendab kõik tekstinumbrid reaalarvudeks. Otsing hakkab uuesti tööle.
Vaata videot
- VLOOKUP lahendab palju probleeme
- Levinud VLOOKUP-probleemid:
- Kui VLOOKUP hakkab töötama, kuid # N / A muutub silmatorkavamaks: unustasite $ otsingutulemusse
- Mõned # N / A: tabelist puuduvad üksused
- Ükski VLOOKUP-ist ei tööta: kontrollige tühikute lõppu
- Eemaldage TRIM-i abil tühikud
- Numbrid ja tekstina salvestatud numbrid
- Valige mõlemad veerud ja kasutage klahvi alt = "" + DEF
- Episood sisaldab nalja, mida nii raamatupidajad kui ka IT-inimesed peavad naljakaks, kuid erinevatel põhjustel
Video ärakiri
Õppige Exceli podcastist, osa 2027 - VLOOKUPi tõrkeotsing!
Hästi, kogu selle raamatu taskuhäälingu edastamiseks klõpsake esitusloendisse pääsemiseks paremas ülanurgas oleval nupul „i”!
VLOOKUP on minu lemmikfunktsioon kogu Excelis ja ma räägin selle nalja alati ühel oma seminaril ja see naerab, kas olete IT-inimene või mitte IT-inimene. Ma ütlen alati: "Noh, vaadake, meil on see andmekogum siin vasakul ja ma võin neid andmeid vaadata ja öelda, et andmed tulid IT-osakonnast, sest see oli täpselt see, mida ma palusin, kuid mitte tegelikult see, mida ma vajasin. Ma küsisin toote kuupäeva ja kogust ning nad andsid mulle toote numbri kuupäeva ja koguse, kuid nad ei viitsinud mulle kirjeldust anda, eks? " Ja raamatupidajad naeravad selle üle alati, sest seda juhtub neil kogu aeg ja IT-poisid on sellised: "Noh, ma andsin teile, mida te palusite, see pole minu süü!" Nii et nad mõlemad arvavad, et see on erinevatel põhjustel naljakas, nii et teate, ja IT-mees on hõivatud, ta ei saa enam päringut uuesti kirjutada,nii et peame selle päästmiseks ise midagi tegema.
Ja nii see väike tabel, mille ma oma arvutist kuskilt mujalt kopeerisin, on inglise keeles, mida VLOOKUP teeb: "Hei, meil on üksuse number W25-6." Meil on siin tabel, ma tahan mööda tabeli esimest veergu alla liikuda, kuni leian selle üksuse numbri, ja siis tagastan sellest reast midagi. Olgu, antud juhul tahan selle rea 2. veergu. Ja siis peame iga VLOOKUPi lõppu panema kas FALSE või 0. Nüüd siin, pärast vahemiku valimist, vajutan klahvi F4 ja siis tahan täpsustamiseks teist veergu ja seejärel FALSE matš. Ärge kunagi valige ligikaudset mängu, mitte kunagi, mitte kunagi! See pole ligikaudne matš, see on väga eriline asi, see ei tööta kogu aeg. Kui soovite oma numbreid väärtpaberite ja börsikomisjonile korrata, võite igal juhul julgelt kasutada,TRUE või jätke lihtsalt FALSE välja. Kuid iga teie loodud VLOOKUP peaks lõppema, FALSE või, 0, 0 on lühem kui FALSE, peaksite sinna panema FALSE, kuid 0 on sama asi, mis FALSE.
Olgu, veaotsing, esimene asi, kui teete terve hulga VLOOKUP-e, on väga normaalne, kui teil on paar # N / As, eks? Ja ma leian alati # N / A-d, minnes Data, ZA-sse, mis viib # N / As-i ülespoole, sealsamas, BG33-9, kas see on kirjaviga või see on täiesti uus toode, olgu, ja saime et sellest aru saada. Nii et siin, paremal, on mul uued andmed, ma lõikan need ja siis Alt + IED, sisestage need lahtrid keskele, see ei pea olema tähestikuline, seda tabelit ei pea sortima. Kui kasutate versiooni FALSE, pole tabel seda - võite selle lihtsalt panna kuhu iganes soovite, ma ei pannud lõppu, sest ma ei pidanud valemit ümber kirjutama, vaid tahan, et töö. Hästi, nii et paar # N / A-d on äärmiselt, äärmiselt normaalne, kuid kontrollige seda.
Kui alustate vastustega, mis töötavad, kuid siis hakkavad # N / A-d veidi sageli ilmuma ja lõpuks ilmuvad need lõpuni alla, on see kindel märk sellest, et te ei lukustanud tabeliviidet. Hea küll, nii et siin on tabel liikumas, kui kopeerin valemit alla, eks, ja nii on mul õnne tabada mõnda loendi lõpus olevat. Kuid lõpuks jõuan sinnamaani, et see vaatab siia täiesti tühjadesse lahtritesse ja loomulikult ei leita midagi. Hästi, nii et see on esimene asi, saate paar, mis töötab, mõned # N / A, veel paar, mis töötab, ja siis kõik # N / A on ülejäänud tee - kindel märk sellest, et te ei pannud $ sisse.
Lihtsalt minge tagasi, redigeerimisrežiimi jaoks klahvi F2, valige koolon, vajutage klahvi F4, mis paneb kogu dollari sisse, topeltklõpsake selle tulistamiseks ja asjad hakkavad jälle toimima. Järgmine, täpselt sama valem, valem on täiuslik, BG33-8 vaata, me ei saa sellest õigust. Hästi, nii et ma lähen vaatan, BG33-8, hei, seal see on, see on seal, see on punasega, ma oleksin pidanud seda nägema! Nii et tulen selle paremal küljel, vajutan klahvi F2 ja vaatan vilkuvat sisestuspunkti ja näen, see on kohe pärast sellist kaheksat, ja siis tulen siia ja vajutan klahvi F2, seal on seal mõned tühikud. See on COBOLi päevil väga-väga levinud, nad ütleksid: "Tead, vaata, me anname teile 10 numbrit üksuse numbri jaoks ja kui te ei kirjuta kõiki 10 tühikut, täidavad need tühikud . ” Ja nii on see väga levinud,ja suurepärane lahendus on siin TRIM-funktsiooniga vabaneda juhtivatest ja tagumistest ruumidest. A2 asemel kasutage TRIM-i (A2), topeltklõpsake, et see maha lasta, olgu, ikka on BG33-9 teises tabelis tagasi.
Hästi, lihtsalt selleks, et saaksite aru, kuidas TRIM töötab, tippisin hulga tühikuid, Johni, hulga tühikuid, Durrani ja hulga tühikuid ning liitsin siis enne ja pärast tähe *, et saaksite näha, kuidas see välja näeb . Kui ma küsin TRIM-i (P4), vabaneme kõigist eesmistest ruumidest, kõikidest tagumistest ruumidest ja seejärel vähendatakse mitu siseruumi ühte ruumi. Hästi, nii et näete, mingil moel visualiseerige seal, et nad vabanevad eesmistest ja tagumistest tühikutest, kõik keskosa kahekordistunud ruumid saavad ühtseks selliseks ruumiks. Nii et TRIM, suurepärane, suurepärane tööriist teie arsenalis, olgu, siin on veel üks tõeliselt tavaline.
Kui see pole lõppu jääv tühik, siis on kõige tavalisem asi, mida siin näeme, kus meil on tõelised arvud ja siin on meil tekstina salvestatud numbrid. Ja VLOOKUP ei näe seda vastena, kuigi 4399, see on number, see on tekst, ei tööta. Kiireim viis tekstiveeru teisendamiseks numbriteks, valige veerg, 3 järjestikku tähte, alt = "" DEF ja äkki hakkavad meie VLOOKUP-id uuesti tööle, suurepärane, suurepärane näpunäide umbes 1500 taskuhäälingusaagist. Olgu, nii et need on kõige tavalisemad VLOOKUP-probleemid, kas unustasite $, olete tühikute vahele jätnud või olete numbreid ja numbreid tekstina salvestanud. Kõik need näpunäited on selles raamatus “MrExcel XL”, klõpsake paremas ülanurgas nupul “i”, saate raamatu osta.
Hästi, kiire kokkuvõte: VLOOKUP lahendab paljud probleemid, kui VLOOKUP hakkab tööle, kuid # N / A! muutub silmatorkavamaks, unustasite sisestada $ otsingu tabelisse. Kui on vähe # N / A-d, on tabelist puudu vaid üksused. Kui ükski VLOOKUPidest ei tööta ja see on tekst, kontrollige tühikute lõppu, võite kasutada funktsiooni TRIM. Kui teil on numbreid ja numbreid tekstina salvestatud, valige üks veerg, see, millel on tekst, seejärel tehke kõigi nende juurde tagasi numbrite juurde alt = "" DEF.
Hästi, hei, ma tahan tänada teid, et peatusite, näeme järgmine kord järgmise netisaate jaoks!
Laadige fail alla
Laadige näidisfail alla siit: Podcast2027.xlsx
