Excel VLOOKUP este puternic, dar nu va funcționa atunci când aveți situații specifice. Astăzi, o privire la modul de depanare a VLOOKUP.
VLOOKUP este funcția mea preferată în Excel. Dacă puteți face VLOOKUP, puteți rezolva multe probleme în Excel. Dar există lucruri care pot împiedica o VLOOKUP. Acest subiect vorbește despre câteva dintre ele.
Dar mai întâi, elementele de bază ale VLOOKUP în engleză simplă.
Datele din A: C au venit de la departamentul IT. Ați solicitat vânzări după articol și dată. Ți-au dat numărul articolului. Aveți nevoie de Descrierea articolului. În loc să așteptați ca departamentul IT să ruleze din nou datele, găsiți tabelul prezentat în coloana F: G.
Doriți ca VLOOKUP să găsească elementul în A2 în timp ce caută prin prima coloană a tabelului în $ F $ 3: $ G $ 30. Când VLOOKUP găsește potrivirea în F7, doriți ca VLOOKUP să returneze descrierea găsită în a doua coloană a tabelului. Fiecare VLOOKUP care caută o potrivire exactă trebuie să se termine cu False (sau zero, care este echivalent cu False). Formula de mai jos este configurată corect.
Observați că utilizați F4 pentru a adăuga patru semne de dolari la adresa tabelului de căutare. Pe măsură ce copiați formula în coloana D, aveți nevoie de adresa pentru ca tabelul de căutare să rămână constant. Există două alternative comune: puteți specifica întreaga coloană F: G ca tabel de căutare. Sau, ați putea denumi F3: G30 cu un nume precum ItemTable. Dacă utilizați =VLOOKUP(A2,ItemTable,2,False), intervalul numit acționează ca o referință absolută.
De fiecare dată când faceți o grămadă de VLOOKUP-uri, trebuie să sortați coloana de VLOOKUP-uri. Sortați ZA și orice erori # N / A vor ajunge în partea de sus. În acest caz, există una. Elementul BG33-9 lipsește din tabelul de căutare. Poate că este o greșeală de tipar. Poate că este un element nou. Dacă este nou, introduceți un rând nou oriunde în mijlocul tabelului de căutare și adăugați noul element.
Este destul de normal să aveți câteva erori # N / A. Dar în figura de mai jos, exact aceeași formulă nu returnează altceva decât # N / A. Când se întâmplă acest lucru, văd dacă pot rezolva primul VLOOKUP. Căutați BG33-8 găsit în A2. Începeți să navigați prin prima coloană a tabelului de căutare. După cum puteți vedea, valoarea potrivită este clar în F10. De ce puteți vedea acest lucru, dar Excel nu îl poate vedea?
Mergeți la fiecare celulă și apăsați tasta F2. Iată F10. Rețineți că cursorul de inserare apare imediat după 8.
Iată celula A2 în modul Editare. Cursorul de inserare este la câteva spații distanță de 8. Acesta este un semn că, la un moment dat, aceste date au fost stocate într-un set de date vechi COBOL. Înapoi în COBOL, dacă câmpul Item a fost definit ca 10 caractere și ați tastat doar 6 caractere, COBOL l-ar bloca cu 4 spații suplimentare.
Soluția? În loc să căutați A2, căutați TRIM(A2).
Funcția TRIM () elimină spațiile de conducere și de spate. Dacă aveți mai multe spații între cuvinte, TRIM le va converti într-un singur spațiu. În figura de mai jos există spații înainte și după ambele nume în A1. =TRIM(A1)elimină tot spațiul cu excepția unuia din A3.
Apropo, ce se întâmplă dacă problema ar fi urmărit spații în coloana F în loc de coloana A? Adăugați o coloană de funcții TRIM () la E, indicând coloana F. Copiați-le și lipiți-le ca valori în F pentru a face căutările să înceapă să funcționeze din nou.
Celălalt motiv foarte frecvent pentru care VLOOKUP nu va funcționa este prezentat aici. Coloana F are numere reale. Coloana A are un text care arată ca cifre.
Selectați toată coloana A. Apăsați alt = "" + D, E, F. Aceasta face un text implicit în Coloane și va converti toate numerele de text în numere reale. Căutarea începe să funcționeze din nou.
Urmăriți videoclipul
- VLOOKUP rezolvă multe probleme
- Probleme comune VLOOKUP:
- Dacă VLOOKUP începe să funcționeze, dar # N / A devine mai proeminent: ați uitat $ în tabelul de căutare
- Câteva # N / A: elemente care lipsesc din tabel
- Niciunul dintre VLOOKUP nu funcționează: verificați dacă există spații
- Eliminați spațiile finale cu TRIM
- Numere și numere stocate ca text
- Selectați ambele coloane și utilizați alt = "" + DEF
- Episodul include o glumă pe care atât contabilii, cât și oamenii din domeniul IT consideră amuzantă, dar din motive diferite
Transcriere video
Aflați Excel din podcast, episodul 2027 - Depanare VLOOKUP!
Bine, podcastând această carte întreagă, faceți clic pe „i” din colțul din dreapta sus pentru a accesa lista de redare!
VLOOKUP este funcția mea preferată în toate Excel-urile și spun întotdeauna această glumă la unul din seminariile mele și râde, indiferent dacă ești IT sau nu IT. Întotdeauna spun „Ei bine, uite, avem acest set de date aici în stânga și pot să văd aceste date și să spun că datele au venit de la departamentul IT pentru că este exact ceea ce am cerut, dar nu chiar ceea ce am nevoie. Am cerut data și cantitatea articolului și mi-au dat data și cantitatea articolului, dar nu s-au obosit să-mi dea o descriere, nu? ” Și contabilii râd mereu de asta, pentru că li se întâmplă acest lucru tot timpul, iar tipii IT sunt de genul „Ei bine, ți-am dat ceea ce ai cerut, nu este vina mea!” Deci, amândoi cred că este amuzant din diferite motive, așa că, știi, iar tipul IT este ocupat, nu poate să revină la rescrierea interogării,deci trebuie să facem ceva pentru a salva acest lucru noi înșine.
Și deci acest mic tabel pe care l-am copiat de pe altă parte a computerului meu, în engleză simplă, ceea ce face VLOOKUP este că spune „Hei, avem un număr de articol W25-6”. Avem un tabel aici, vreau să trec prin prima coloană a tabelului până când găsesc numărul articolului respectiv și apoi să returnez ceva din rândul respectiv. Bine, în acest caz, ceea ce vreau este a doua coloană din rândul respectiv. Și apoi, la sfârșitul fiecărei VLOOKUP, trebuie să punem fie FALSE, fie 0. Acum, chiar aici, după ce am ales intervalul, voi apăsa tasta F4 și apoi vreau a doua coloană și apoi FALSE Meci. Nu alegeți vreodată meci aproximativ, niciodată, niciodată! Nu este un meci aproximativ, este un lucru foarte special, nu funcționează tot timpul. Dacă doriți să vă refaceți numerele către Securities and Exchange Commission, nu ezitați să le folosiți,ADEVĂRAT sau doar lăsați, FALS oprit. Dar fiecare VLOOKUP pe care l-ați creat vreodată ar trebui să se termine cu, FALSE sau, 0, 0 este mai scurt decât FALSE, ar trebui să puneți un FALSE acolo, dar un 0 este același lucru cu FALSE.
Bine acum, depanare, primul lucru, când faci o grămadă de VLOOKUP-uri, este foarte normal să ai câteva # N / As, nu? Și găsesc întotdeauna # N / A accesând Data, ZA, care aduce # N / As în partea de sus, chiar acolo, BG33-9, fie că este o greșeală de tipar, fie că este un articol nou, bine, și avem pentru a-l da seama. Deci, aici, în dreapta, am noile date, le voi tăia și apoi Alt + IED, introduceți acele celule în mijloc, nu trebuie să fie alfabetic, acest tabel nu trebuie să fie sortat. Când utilizați versiunea FALSĂ, tabelul nu este - îl puteți pune oriunde doriți, nu l-am pus la final pentru că nu a trebuit să rescriu formula, vreau doar să muncă. Bine, deci câteva # N / A, extrem, extrem de normale, dar verifică asta.
Când începeți cu răspunsuri care funcționează, dar apoi numărul # N / A începe să apară ușor frecvent și apoi în cele din urmă apar până la capăt, acesta este un semn sigur că nu ați blocat referința la tabel. În regulă, vezi, așa că tabelul se mișcă în timp ce copiez formula în jos, așa că am noroc să lovesc câteva care se aflau la sfârșitul listei. Dar în cele din urmă ajung la punctul în care se uită aici în celule complet goale și, desigur, nu se găsește nimic. Bine, deci acesta este primul lucru, ai un cuplu care funcționează, câteva # N / A, încă câteva care funcționează, și apoi toate # N / A sunt restul drumului - semn sigur că nu ai pus $ in.
Doar întoarceți-vă, F2 pentru modul Editare, selectați două puncte, apăsați F4, care pune toate $ în, faceți dublu clic pentru a trage acest lucru și lucrurile încep să funcționeze din nou, bine. Următoarea, exact aceeași formulă, formula este perfectă, BG33-8 vezi, nu obținem nimic din acest drept. Bine, așa că mă uit, BG33-8, hei, iată-l, e chiar acolo, este în roșu, ar fi trebuit să-l văd! Așa că vin la aceasta în partea dreaptă, apăs F2 și urmăresc punctul de inserare intermitent și văd, este imediat după 8, așa, și apoi vin aici și apăs F2, sunt unele spații de urmărire acolo. Acest lucru este foarte, foarte obișnuit în zilele COBOL, ei spuneau „Știi, uite, îți vom oferi 10 spații pentru numărul articolului, iar dacă nu tastați toate cele 10 spații, acestea vor umple spațiile . ” Și așa este foarte frecvent,iar soluția excelentă aici este să scăpați de acele spații de conducere și de spate cu funcția TRIM. În loc de A2, utilizați TRIM (A2), faceți dublu clic pentru a trage în jos, bine, totuși BG33-9 este din nou în celălalt tabel.
Bine, doar pentru a înțelege cum funcționează TRIM, așa că am tastat o grămadă de spații, John, o grămadă de spații, Durran și o grămadă de spații, apoi am concatenat un * înainte și după, pentru a vedea cum arată . Când cer TRIM (P4), scăpăm de toate spațiile principale, toate spațiile finale și apoi spațiile interioare multiple sunt reduse la un singur spațiu. În regulă, astfel încât să puteți vedea, un fel de vizualizare acolo, că scapă de spațiile de conducere și de spate, orice spații dublate din mijloc devin un singur spațiu de genul acesta. Deci TRIM, un instrument grozav, excelent în arsenalul tău, bine, iată un altul foarte comun.
Dacă nu sunt spațiile finale, atunci cel mai obișnuit lucru pe care l-am văzut este că aici avem numere adevărate și aici avem numere stocate ca text. Și VLOOKUP nu va vedea acest lucru ca pe un meci, chiar dacă 4399, acesta este un număr, acesta este text, nu funcționează. Cea mai rapidă modalitate de a converti o coloană de text în numere, selectați coloana, 3 litere în ordine, alt = "" DEF și dintr-o dată, VLOOKUP-urile noastre încep să funcționeze din nou, minunat, sfat extraordinar de acum aproximativ 1500 de podcast-uri. Bine, deci acestea sunt cele mai frecvente probleme VLOOKUP, fie că ați uitat $, fie că aveți spații finale, fie că aveți numere și numere stocate ca text. Toate aceste sfaturi sunt în această carte „MrExcel XL”, faceți clic pe „i” din colțul din dreapta sus, puteți cumpăra cartea.
Bine, o recapitulare rapidă: VLOOKUP rezolvă multe probleme, dacă un VLOOKUP începe să funcționeze, dar # N / A! devine mai proeminent, ați uitat să puneți $ în tabelul de căutare. Dacă există câteva # N / A, sunt doar elemente care lipsesc din tabel. Dacă niciunul dintre VLOOKUP-uri nu funcționează și este text, verificați dacă există spații finale, puteți utiliza funcția TRIM. Dacă aveți numere și numere stocate ca text, selectați oricare dintre coloane, cea care are textul, apoi alt = "" DEF trebuie să le revină la numere.
Bine, bine, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2027.xlsx
