Excel VLOOKUP je výkonný, ale nebude fungovať, keď máte konkrétnu situáciu. Dnes sa pozrieme na to, ako riešiť problémy s VLOOKUP.
VLOOKUP je moja obľúbená funkcia v programe Excel. Ak zvládnete funkciu VLOOKUP, ste schopní vyriešiť veľa problémov v programe Excel. Existujú však veci, ktoré môžu VLOOKUP narušiť. Táto téma hovorí o niekoľkých z nich.
Najprv však základy VLOOKUPU v jednoduchej angličtine.
Údaje v A: C pochádzajú z oddelenia IT. Požiadali ste o predaj podľa položky a dátumu. Dali vám číslo položky. Potrebujete popis položky. Namiesto čakania na opätovné spustenie údajov oddelením IT nájdete tabuľku uvedenú v stĺpci F: G.
Chcete, aby VLOOKUP našiel položku v A2 pri prehľadávaní prvého stĺpca tabuľky v $ F $ 3: $ G $ 30. Keď VLOOKUP nájde zhodu vo F7, chcete, aby VLOOKUP vrátil popis nájdený v druhom stĺpci tabuľky. Každý SVETER, ktorý hľadá presnú zhodu, musí končiť číslom False (alebo nulou, ktorá je ekvivalentom výrazu False). Vzorec uvedený nižšie je nastavený správne.
Všimnite si, že pomocou klávesu F4 pridáte na adresu vyhľadávacej tabuľky štyri znaky dolára. Pri kopírovaní vzorca dolu v stĺpci D potrebujete, aby adresa vyhľadávacej tabuľky zostala konštantná. Existujú dve bežné alternatívy: Môžete zadať celé stĺpce F: G ako vyhľadávaciu tabuľku. Alebo môžete pomenovať F3: G30 názvom, napríklad ItemTable. Ak používate =VLOOKUP(A2,ItemTable,2,False), pomenovaný rozsah funguje ako absolútna referencia.
Kedykoľvek robíte veľa VLOOKUPOV, musíte zoradiť stĺpec VLOOKUPov. Zoradiť ZA a všetky # N / A chyby sa dostanú na začiatok. V tomto prípade existuje jeden. Vo vyhľadávacej tabuľke chýba položka BG33-9. Možno je to preklep. Možno je to úplne nová položka. Ak je nový, vložte nový riadok kdekoľvek do stredu vyhľadávacej tabuľky a pridajte novú položku.
Je celkom normálne mať niekoľko chýb # N / A. Ale na nasledujúcom obrázku presne ten istý vzorec nevracia nič iné ako # N / A. Keď sa to stane, uvidím, či dokážem vyriešiť prvý VLOOKUP. Hľadáte BG33-8 nájdený v A2. Začnite križovať dole cez prvý stĺpec vyhľadávacej tabuľky. Ako vidíte, zhodná hodnota je jednoznačne vo F10. Prečo to vidíte, ale Excel to nevidí?
Prejdite do každej bunky a stlačte kláves F2. Tu je F10. Všimnite si, že kurzor sa nachádza hneď za 8.
Tu je bunka A2 v režime úprav. Vkladací kurzor je vzdialený pár medzier od značky 8. Toto je znamenie, že v určitom okamihu boli tieto údaje uložené v starej množine údajov COBOL. Ak by ste boli späť v COBOLe, keby bolo pole Položka definované ako 10 znakov a vy by ste napísali iba 6 znakov, COBOL by ho vložil so 4 medzerami navyše.
Riešenie? Namiesto toho, aby ste hľadali A2, vyhľadajte TRIM(A2).
Funkcia TRIM () odstráni úvodné a koncové medzery. Ak máte medzi slovami viac medzier, TRIM ich prevedie na jednu medzeru. Na obrázku nižšie sú medzery pred a za obidvomi menami v skupine A1. =TRIM(A1)odstráni všetky medzery okrem jedného v A3.
Mimochodom, čo ak by problémom boli koncové medzery v stĺpci F namiesto stĺpca A? Pridajte stĺpec funkcií TRIM () do E, ukážte na stĺpec F. Skopírujte ich a prilepte ako hodnoty do F, aby vyhľadávania začali znova fungovať.
Je tu uvedený ďalší veľmi častý dôvod, prečo VLOOKUP nebude fungovať. Stĺpec F má skutočné čísla. Stĺpec A má text, ktorý vyzerá ako čísla.
Vyberte všetky stĺpce A. Stlačte alt = "" + D, E, F. Týmto sa nastaví predvolený text na stĺpce a všetky textové čísla sa prevedú na skutočné čísla. Vyhľadávanie začne znova fungovať.
Pozeraj video
- VLOOKUP rieši veľa problémov
- Bežné problémy VLOOKUP:
- Ak VLOOKUP začne fungovať, ale # N / A sa stane výraznejším: zabudli ste $ vo vyhľadávacej tabuľke
- Niekoľko # N / A: položky v tabuľke chýbajú
- Žiadna z funkcií VLOOKUP nefunguje: skontrolujte koncové medzery
- Odstráňte koncové medzery pomocou TRIM
- Čísla a čísla uložené ako text
- Vyberte oba stĺpce a použite alt = "" + DEF
- Epizóda obsahuje vtip, ktorý účtovníkom aj IT ľuďom pripadá zábavný, ale z rôznych dôvodov
Prepis videa
Naučte sa Excel z podcastu, epizóda 2027 - Riešenie problémov s VLOOKUP!
Dobre, podcastujte celú túto knihu a kliknutím na „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb!
VLOOKUP je moja obľúbená funkcia v celom programe Excel a tento vtip vždy poviem na jednom zo svojich seminárov. Vysmeje sa, či už ste IT alebo nie. Vždy hovorím: „No, pozrite, máme tieto údaje tu naľavo. Môžem sa na ne pozrieť a povedať, že údaje pochádzajú z oddelenia IT, pretože to je presne to, čo som požadoval, ale nie to, čo som potreboval. Požiadal som o dátum a množstvo položky a oni mi dali číslo a dátum položky a množstvo, ale neobťažovali sa mi dať popis, však? “ A účtovníci sa na tom vždy smejú, pretože sa im to stáva neustále a IT-čkári sú ako: „No, dal som vám, o čo ste požiadali, nie je to moja chyba!“ Takže si obaja myslia, že je to zábavné z rôznych dôvodov, takže viete, že IT pracovník je zaneprázdnený a nemôže sa vrátiť k prepisovaniu dopytu,takže musíme niečo urobiť, aby sme to zachránili sami.
A tak táto malá tabuľka, ktorú som skopíroval odinakiaľ v mojom počítači, v jednoduchej angličtine, čo VLOOKUP robí, je „Hej, máme číslo položky W25-6.“ Máme tu tabuľku, chcem prejsť krížom cez prvý stĺpec tabuľky, kým nenájdem toto číslo položky, a potom niečo vrátiť z tohto riadku. Dobre, v tomto prípade to, čo chcem, je 2. stĺpec z tohto riadku. A potom na konci každého VLOOKUPU musíme dať buď FALSE, alebo 0. Teraz tu, po výbere rozsahu, stlačím klávesu F4 a potom chcem 2. stĺpec a potom FALSE na presný zápas. Nikdy si nevyberajte približnú zhodu, nikdy, nikdy! Nie je to približný zápas, je to veľmi zvláštna vec, nefunguje to stále. Ak chcete svoje čísla prepočítať na Komisiu pre cenné papiere, vo všetkých ohľadoch ich môžete pokojne použiť,PRAVDA alebo len nechajte hodnotu FALSE vypnutú. Ale každý VLOOKUP, ktorý kedy vytvoríte, by mal končiť na, FALSE alebo, 0, 0 je kratší ako FALSE, mali by ste tam vložiť FALSE, ale 0 je to isté ako FALSE.
Dobre, riešenie problémov, prvá vec, keď robíte kopu VLOOKUPOV, je veľmi normálne mať pár # N / As, však? A vždy nájdem # N / A od spoločnosti Data, ZA, ktorá prinesie # N / As na vrchol, priamo tam, BG33-9, buď je to preklep, alebo je to úplne nová položka, v poriadku, a máme prísť na to. Takže tu napravo mám nové údaje, ktoré vystrihnem a potom Alt + IED vložím tieto bunky do stredu, nemusí to byť abecedné, táto tabuľka nemusí byť zoradená. Keď používate verziu FALSE, tabuľka nie je - môžete ju jednoducho umiestniť kamkoľvek chcete. Nedával som ju na koniec, pretože som nemusel prepisovať vzorec, chcem iba vzorec práca. Dobre, takže pár # N / A, extrémne, extrémne normálne, ale skontrolujte to.
Ak začnete odpoveďami, ktoré fungujú, ale potom sa # N / A začnú objavovať mierne často a nakoniec sa objavia úplne dole, je to istý znak toho, že ste nezamkli odkaz na tabuľku. Dobre, takže, tak sa tu pohybuje tabuľka, keď kopírujem vzorec, dole, a teda mám šťastie, že narazím na niekoľko, ktoré boli na konci zoznamu. Ale nakoniec sa dostanem do bodu, keď to tu pozerá do úplne prázdnych buniek a samozrejme sa nič nenájde. Dobre, takže to je prvá vec, dostanete pár, ktorý funguje, pár # N / A, pár ďalších, ktoré fungujú, a potom všetko # N / A je zvyšok cesty - určite znamenie, že ste nedávali $ in.
Len sa vráťte späť, F2 pre režim úprav, vyberte dvojbodku, stlačte F4, čím sa vložia všetky doláre, dvojitým kliknutím to zostrelíte a veci začnú opäť fungovať, dobre. Ďalej jeden, úplne rovnaký vzorec, vzorec je dokonalý, BG33-8, nedostaneme nič z toho správneho. Dobre, tak sa idem pozrieť, BG33-8, hej, je to, je to tam, je to v červenej farbe, mal som to vidieť! Takže prídem k tomuto na pravej strane, stlačím F2 a sledujem blikajúci bod vloženia a vidím, že je to tesne po tej 8, a potom prídem sem a stlačím F2, existujú nejaké zadné medzery. Toto je veľmi, veľmi bežné v dobách COBOLU, povedali by „Viete, pozri, dáme vám 10 medzier pre číslo položky, a ak nezadáte všetkých 10 medzier, vyplnia tieto medzery . “ A tak je to veľmi bežné,a skvelým riešením je tu zbaviť sa týchto vedúcich a koncových priestorov pomocou funkcie TRIM. Namiesto A2 použite TRIM (A2), dvakrát kliknite tak, že to zostrelíte, v poriadku, BG33-9 je späť v druhej tabuľke.
Dobre, len aby ste pochopili, ako funguje TRIM, tak som napísal veľa medzier, John, veľa medzier, Durran a veľa medzier, a potom som spojil * pred a po, takže ste videli, ako to vyzerá . Keď požiadam o TRIM (P4), zbavíme sa všetkých vedúcich medzier, všetkých koncových medzier a potom sa viac vnútorných priestorov zredukuje na jeden priestor. Dobre, tak vidíte, trochu si to vizualizujte, že sa zbavujú vedúcich a koncových medzier, všetky zdvojnásobené medzery uprostred sa stávajú takýmto jedným priestorom. Takže TRIM, skvelý, vynikajúci nástroj vo vašom arzenáli, v poriadku, tu je ďalší skutočne bežný nástroj.
Ak to nie sú koncové medzery, tak najbežnejšia vec, ktorú som videl, je to, kde tu máme skutočné čísla a tu máme čísla uložené ako text. A VLOOKUP to nebude považovať za zhodu, aj keď 4399, toto je číslo, toto je text, nefunguje. Najrýchlejší spôsob prevodu stĺpca textu na čísla, výber stĺpca, 3 písmená v poradí, alt = "" DEF a zrazu naše VLOOKUPY začnú znova fungovať, vynikajúci tip od asi 1500 podcastov. Dobre, takže toto sú najčastejšie problémy VLOOKUP-u, buď ste zabudli $, alebo máte koncové medzery, alebo máte čísla a čísla uložené ako text. Všetky tieto tipy nájdete v tejto knihe „MrExcel XL“. Kliknutím na „i“ v pravom hornom rohu si môžete knihu kúpiť.
Dobre, rýchla rekapitulácia: VLOOKUP rieši veľa problémov, ak VLOOKUP začne fungovať, ale # N / A! stane sa výraznejším, zabudli ste vložiť $ do vyhľadávacej tabuľky. Ak existuje niekoľko nepoužiteľných, potom v tabuľke chýbajú iba položky. Ak žiadny z VLOOKUPOV nefunguje a jedná sa o text, skontrolujte koncové medzery, môžete použiť funkciu TRIM. Ak máte čísla a čísla uložené ako text, vyberte ľubovoľný stĺpec, ktorý obsahuje text, a potom alt = "" DEF musí všetky tieto čísla vrátiť späť na čísla.
Dobre, ahoj, chcem sa ti poďakovať, že si sa zastavil, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2027.xlsx
