Excel VLOOKUP je moćan, ali neće raditi u određenim situacijama. Danas pogledajte kako riješiti problem s VLOOKUP-om.
VLOOKUP je moja omiljena funkcija u programu Excel. Ako možete raditi VLOOKUP, u Excelu ste u stanju riješiti mnoge probleme. Ali postoje stvari koje mogu spotaknuti VLOOKUP. Ova tema govori o nekoliko njih.
Ali prvo, osnove VLOOKUP-a na jednostavnom engleskom jeziku.
Podaci u A: C dolazili su iz IT odjela. Tražili ste prodaju po stavci i datumu. Dali su vam broj predmeta. Trebate opis predmeta. Umjesto da čekate da IT odjel ponovno pokrene podatke, naći ćete tablicu prikazanu u stupcu F: G.
Želite da VLOOKUP pronađe stavku u A2 dok pretražuje prvi stupac tablice u $ F $ 3: $ G $ 30. Kada VLOOKUP pronađe podudaranje u F7, želite da VLOOKUP vrati opis pronađen u drugom stupcu tablice. Svaki VLOOKUP koji traži točno podudaranje mora završiti s False (ili nulom, što je ekvivalentno False). Formula u nastavku pravilno je postavljena.
Primijetite da koristite F4 za dodavanje znakova od četiri dolara na adresu tablice pretraživanja. Dok kopirate formulu dolje u stupcu D, trebate da adresa za tablicu pretraživanja ostane konstantna. Dvije su uobičajene alternative: Možete odrediti cijele stupce F: G kao tablicu pretraživanja. Ili, F3: G30 možete nazvati imenom kao što je ItemTable. Ako koristite =VLOOKUP(A2,ItemTable,2,False), imenovani raspon djeluje poput apsolutne reference.
Kad god napravite hrpu VLOOKUP-ova, trebate sortirati stupac VLOOKUP-ova. Poredaj ZA, a sve pogreške # N / A doći će na vrh. U ovom slučaju postoji jedan. Stavka BG33-9 nedostaje u tablici pretraživanja. Možda je to pogreška u kucanju. Možda je to potpuno novi predmet. Ako je nov, umetnite novi redak bilo gdje u sredini tablice pretraživanja i dodajte novu stavku.
Prilično je normalno imati nekoliko pogrešaka # N / A. Ali na donjoj slici, potpuno ista formula ne vraća ništa osim # N / A. Kad se to dogodi, vidim mogu li riješiti prvi VLOOKUP. Tražite BG33-8 koji se nalazi u A2. Započnite krstarenje dolje kroz prvi stupac tablice pretraživanja. Kao što vidite, podudarna vrijednost očito je u F10. Zašto to možete vidjeti, a Excel ne?
Idite na svaku ćeliju i pritisnite tipku F2. Ovdje je F10. Imajte na umu da se pokazivač za umetanje pojavljuje odmah nakon 8.
Ovdje se nalazi ćelija A2 u načinu uređivanja. Kursor za umetanje udaljen je nekoliko razmaka od 8. To je znak da su u nekom trenutku ti podaci bili pohranjeni u starom skupu podataka COBOL. Natrag u COBOL, ako je polje Stavka definirano kao 10 znakova i ako ste upisali samo 6 znakova, COBOL bi ga dodao s 4 dodatna razmaka.
Rješenje? Umjesto da tražite A2, potražite TRIM(A2).
Funkcija TRIM () uklanja početni i prateći razmak. Ako između riječi imate više razmaka, TRIM će ih pretvoriti u jedan razmak. Na donjoj slici postoje razmaci prije i iza oba imena u A1. =TRIM(A1)uklanja sve osim jednog prostora u A3.
Usput, što da je problem praćenje razmaka u stupcu F umjesto stupca A? Dodajte stupac funkcija TRIM () u E, pokazujući na stupac F. Kopirajte ih i zalijepite kao vrijednosti u F kako bi pretraživanja ponovno počela raditi.
Ovdje je prikazan drugi vrlo čest razlog zašto VLOOKUP neće raditi. Stupac F ima stvarne brojeve. Stupac A sadrži tekst koji izgleda poput brojeva.
Odaberite cijeli stupac A. Pritisnite alt = "" + D, E, F. To čini zadani tekst u stupce i pretvorit će sve brojeve teksta u stvarne brojeve. Pregled ponovno počinje raditi.
Gledaj video
- VLOOKUP rješava mnoge probleme
- Uobičajeni problemi s VLOOKUP-om:
- Ako VLOOKUP počne raditi, ali # N / A postaje sve istaknutiji: zaboravio je $ u tablici pretraživanja
- Nekoliko # N / A: stavki nedostaje iz tablice
- Niti jedan od VLOOKUP-a ne radi: provjerite postoje li razmaci
- Uklonite prateće razmake TRIM-om
- Brojevi i brojevi pohranjeni u obliku teksta
- Odaberite oba stupca i upotrijebite alt = "" + DEF
- Epizoda uključuje šalu koja je i računovođama i IT-ovcima smiješna, ali iz različitih razloga
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2027 - Rješavanje problema VLOOKUP!
U redu, podcasting cijelu ovu knjigu, kliknite "i" u gornjem desnom kutu da biste došli do popisa za reprodukciju!
VLOOKUP je moja omiljena funkcija u cijelom Excelu, a ovu šalu uvijek ispričam na jednom od svojih seminara i nasmije se bez obzira jeste li IT ili ne. Uvijek kažem „Pa, pogledajte, imamo ovdje postavljene podatke s lijeve strane i mogu ih pogledati i reći da su podaci došli iz IT odjela jer je to točno ono što sam tražio, ali zapravo nije ono što sam trebao. Tražio sam datum i količinu predmeta, dali su mi datum i količinu broja predmeta, ali nisu se potrudili da mi daju opis, zar ne? " A računovođe se uvijek tome smiju, jer im se to stalno događa, a IT-ovci su poput "Pa, dao sam vam ono što ste tražili, nisam ja kriv!" Pa oboje misle da je to smiješno iz različitih razloga, pa, znate, a IT-jevac je zauzet, ne može se vratiti prekrajanju upita,pa moramo sami nešto poduzeti da to spasimo.
I tako ova mala tablica koju sam kopirao s nekog drugog mjesta na računalu, na jednostavnom engleskom, ono što VLOOKUP radi je: "Hej, imamo broj predmeta W25-6." Ovdje imamo tablicu, želim proći kroz prvi stupac tablice dok ne pronađem taj broj stavke, a zatim vratiti nešto iz tog retka. U redu, u ovom slučaju, ono što želim je drugi stupac iz tog retka. I onda na kraj svakog VLOOKUP-a moramo staviti ili FALSE ili 0. Sada upravo ovdje, nakon što odaberem raspon, pritisnut ću tipku F4, a zatim želim drugi stupac, a zatim FALSE za točno podudaranje. Nikad ne biraj približno podudaranje, nikad, nikad! To nije približno podudaranje, to je vrlo posebna stvar, ne radi cijelo vrijeme. Ako svoje brojeve želite ponovno prijaviti Povjerenstvu za vrijednosne papire, svakako ga upotrijebite,TOČNO ili jednostavno isključite, FALSE. Ali svaki VLOOKUP koji ikada napravite trebao bi završiti na, FALSE ili, 0, 0 je kraći od FALSE, tamo biste trebali staviti FALSE, ali 0 je isto što i FALSE.
U redu, rješavanje problema, prvo, kad napravite gomilu VLOOKUP-ova, sasvim je normalno imati nekoliko # N / As, zar ne? I uvijek pronađem # N / A odlazeći na Data, ZA, koji dovodi # N / As na vrh, upravo tamo, BG33-9, bilo da se radi o tipkarskoj grešci ili je to potpuno nova stavka, u redu, i dobili smo da shvatim. Dakle, ovdje desno imam nove podatke, izrezati ću ih, a zatim Alt + IED, ubaciti te ćelije u sredinu, ne mora biti abecedno, ova tablica ne mora biti sortirana. Kad koristite verziju FALSE, tablica nije - možete je jednostavno staviti gdje god želite, nisam stavio na kraju, jer nisam morao prepisivati formulu, samo želim da formula raditi. U redu, dakle, par N / A, izuzetno, krajnje normalnih, ali pogledajte ovo.
Kada započnete s odgovorima koji funkcioniraju, ali onda se # N / A počinju pojavljivati malo često, a zatim se na kraju pojave skroz dolje, to je siguran znak da niste zaključali referencu tablice. U redu, vidite, ovdje se tablica pomiče dok kopiram formulu prema dolje, zar ne, i tako mi se posrećilo pogoditi nekoliko koji su bili na kraju popisa. Ali na kraju se spustim do točke kad gleda ovdje u potpuno praznim stanicama, i naravno da se ništa ne može pronaći. U redu, dakle, to je prva stvar, dobijete par koji rade, nekoliko # N / A, još par koji rade, a zatim svi # N / A ostatak puta - siguran znak da niste stavili $ u.
Samo se vratite natrag, F2 za način uređivanja, odaberite dvotačku, pritisnite F4, koji unosi sve $, dvaput kliknite da biste to oborili i stvari će opet početi raditi, u redu. Sljedeća, potpuno ista formula, formula je savršena, BG33-8 vidite, ništa od toga ne shvaćamo ispravno. Dobro, idem pogledati, BG33-8, hej, evo ga, tu je, u crvenoj je boji, trebao sam ga vidjeti! Dakle, dolazim do ovog s desne strane, pritisnem F2 i gledam trepćuću točku umetanja i vidim, to je odmah nakon 8, onako, a onda dođem ovdje i pritisnem F2, postoje tamo neki prateći prostori. To je vrlo, vrlo često u doba COBOL-a, rekli bi „Znate, pogledajte, dat ćemo vam 10 mjesta za broj stavke, a ako ne upišete svih 10 mjesta, popunit će ih. . " I tako je ovo vrlo često,a izvrsno rješenje ovdje je riješiti se vodećih i pratećih prostora pomoću funkcije TRIM. Umjesto A2 upotrijebite TRIM (A2), dvaput kliknite da biste ga oborili, u redu, i dalje se BG33-9 vratio u drugu tablicu.
Dobro, samo da biste razumjeli kako TRIM radi, pa sam upisao hrpu razmaka, John, gomilu prostora, Durran i gomilu prostora, a zatim sam spojio * prije i poslije kako biste mogli vidjeti kako to izgleda . Kada tražim TRIM (P4), rješavamo se svih vodećih prostora, svih pratećih prostora, a zatim se više unutarnjih prostora svodi na jedan prostor. U redu, kako biste tamo mogli vidjeti, nekako vizualizirajte da se rješavaju vodećih i pratećih prostora, svi udvostručeni prostori u sredini postaju jedinstveni takav prostor. Dakle, TRIM, sjajan, sjajan alat u vašem arsenalu, u redu, evo još jednog stvarno uobičajenog.
Ako to nisu prateći razmaci, tada je najčešća stvar koju sam vidio, gdje ovdje imamo prave brojeve, a ovdje brojeve pohranjujemo kao tekst. A VLOOKUP to neće shvatiti kao podudaranje, iako je ovo 4399, ovo je tekst, ne radi. Najbrži način pretvorbe stupca teksta u brojeve, odabir stupca, 3 slova u nizu, alt = "" DEF, i odjednom, naši VLOOKUP-ovi ponovno počinju raditi, sjajan, sjajan savjet od prije oko 1500 podcastova. Dobro, to su najčešći problemi s VLOOKUP-om, ili ste zaboravili $, ili imate prazne razmake, ili imate brojeve i brojeve pohranjene kao tekst. Svi ovi savjeti nalaze se u ovoj knjizi "MrExcel XL", kliknite "i" u gornjem desnom kutu, knjigu možete kupiti.
U redu, brzo sažimanje: VLOOKUP rješava mnoge probleme ako VLOOKUP počne raditi, ali # N / A! postane istaknutiji, zaboravili ste staviti $ u tablicu pretraživanja. Ako postoji nekoliko # N / A, u tablici nedostaju samo predmeti. Ako niti jedan od VLOOKUP-ova ne radi, a riječ je o tekstu, provjerite postoje li razmaci, možete koristiti funkciju TRIM. Ako imate brojeve i brojeve koji su pohranjeni kao tekst, odaberite bilo koji stupac, onaj koji ima tekst, a zatim učinite alt = "" DEF za sve one natrag u brojeve.
Dobro, hej, želim vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedan prijenos od!
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2027.xlsx
