Excel VLOOKUP je výkonný, ale nebude fungovat, když máte konkrétní situaci. Dnes se podíváme na to, jak řešit problémy s VLOOKUP.
VLOOKUP je moje oblíbená funkce v aplikaci Excel. Pokud můžete udělat SVYHLEDAT, jste schopni vyřešit mnoho problémů v aplikaci Excel. Existují však věci, které mohou VLOOKUP vypnout. Toto téma hovoří o několika z nich.
Ale nejdříve, základy VLOOKUP v jednoduché angličtině.
Data v A: C pocházela z IT oddělení. Požádali jste o prodej podle položky a data. Dali vám číslo položky. Potřebujete popis položky. Spíše než čekat, až IT oddělení znovu spustí data, najdete tabulku zobrazenou ve sloupci F: G.
Chcete, aby VLOOKUP našel položku v A2, zatímco prohledává první sloupec tabulky v $ F $ 3: $ G $ 30. Když VLOOKUP najde shodu ve F7, chcete, aby VLOOKUP vrátil popis nalezený ve druhém sloupci tabulky. Každý VLOOKUP, který hledá přesnou shodu, musí končit False (nebo nula, což odpovídá False). Vzorec níže je nastaven správně.
Všimněte si, že pomocí klávesy F4 přidáte na adresu vyhledávací tabulky čtyři znaky dolaru. Při kopírování vzorce dolů ve sloupci D potřebujete, aby adresa vyhledávací tabulky zůstala konstantní. Existují dvě běžné alternativy: Jako vyhledávací tabulku můžete určit celé sloupce F: G. Nebo můžete název F3: G30 pojmenovat například ItemTable. Pokud používáte =VLOOKUP(A2,ItemTable,2,False), pojmenovaný rozsah funguje jako absolutní reference.
Kdykoli uděláte spoustu VLOOKUPŮ, musíte seřadit sloupec VLOOKUPů. Třídit ZA a jakékoli # N / A chyby se dostanou nahoru. V tomto případě existuje jedna. Ve vyhledávací tabulce chybí položka BG33-9. Možná je to překlep. Možná je to zbrusu nová položka. Pokud je nový, vložte nový řádek kdekoli uprostřed vyhledávací tabulky a přidejte novou položku.
Je celkem normální mít několik chyb # N / A. Ale na následujícím obrázku přesně stejný vzorec nevrací nic jiného než # N / A. Když k tomu dojde, uvidím, jestli mohu vyřešit první VLOOKUP. Hledáte BG33-8 nalezený v A2. Začněte projíždět prvním sloupcem vyhledávací tabulky. Jak vidíte, shodná hodnota je jasně ve F10. Proč to vidíte, ale Excel to nevidí?
Přejděte do každé buňky a stiskněte klávesu F2. Tady je F10. Všimněte si, že kurzor se zobrazí hned za 8.
Tady je buňka A2 v režimu úprav. Kurzor pro vložení je pár mezer od osy 8. To je známka toho, že v určitém okamžiku byla tato data uložena ve staré datové sadě COBOL. Zpět v COBOLu, pokud bylo pole Položka definováno jako 10 znaků a zadali jste pouze 6 znaků, COBOL by jej vložil se 4 mezerami navíc.
Řešení? Místo vyhledávání A2 vyhledejte TRIM(A2).
Funkce TRIM () odstraní úvodní a koncové mezery. Pokud máte mezi slovy více mezer, převede je TRIM na jednu mezeru. Na obrázku níže jsou mezery před a za oběma jmény v A1. =TRIM(A1)odstraní v A3 všechny mezery kromě jednoho.
Mimochodem, co když by problémem byly koncové mezery ve sloupci F místo ve sloupci A? Přidejte sloupec funkcí TRIM () do E, přejděte na sloupec F. Zkopírujte je a vložte jako hodnoty do F, aby vyhledávání začala znovu fungovat.
Zde je uveden další velmi častý důvod, proč VLOOKUP nebude fungovat. Sloupec F má reálná čísla. Sloupec A má text, který vypadá jako čísla.
Vyberte celý sloupec A. Stiskněte alt = "" + D, E, F. Tím se nastaví výchozí text na sloupce a všechna textová čísla se převedou na skutečná čísla. Vyhledávání začne znovu fungovat.
Sledovat video
- VLOOKUP řeší mnoho problémů
- Běžné problémy VLOOKUP:
- Pokud VLOOKUP začne fungovat, ale # N / A se stane výraznějším: zapomněl $ ve vyhledávací tabulce
- Několik # N / A: položky chybí v tabulce
- Žádná z funkcí VLOOKUP nefunguje: zkontrolujte koncové mezery
- Odstraňte koncové mezery pomocí TRIM
- Čísla a čísla uložená jako text
- Vyberte oba sloupce a použijte alt = "" + DEF
- Epizoda obsahuje vtip, který účetní i IT považují za vtipný, ale z různých důvodů
Přepis videa
Naučte se Excel z podcastu, epizoda 2027 - Odstraňování problémů VLOOKUP!
Dobře, podcasting celé této knihy, kliknutím na „i“ v pravém horním rohu se dostanete do seznamu skladeb!
VLOOKUP je moje oblíbená funkce v celém Excelu a tento vtip vždycky prozradím na jednom ze svých seminářů a rozesmál se, ať už jste IT nebo ne IT. Vždycky říkám: „Podívej, máme tu datovou sadu nalevo a můžu se na ta data podívat a říct, že ta data pocházejí z IT oddělení, protože to je přesně to, o co jsem žádal, ale ne to, co jsem potřeboval. Požádal jsem o datum a množství položky a oni mi dali číslo a datum položky a množství, ale neobtěžovali se mi poskytnout popis, že? “ A účetní se tomu vždy smějí, protože se jim to děje pořád a IT kluci jsou jako „No, dal jsem ti, o co jsi žádal, není to moje chyba!“ Takže si oba myslí, že je to legrační z různých důvodů, takže víte, a IT člověk je zaneprázdněn, nemůže se vrátit k přepsání dotazu,takže musíme něco udělat, abychom to sami zachránili.
A tak tento malý stůl, který jsem zkopíroval odjinud do svého počítače, v prosté angličtině, to, co VLOOKUP dělá, říká: „Hele, we have an item number W25-6.“ Máme tady tabulku, chci projíždět prvním sloupcem tabulky, dokud nenajdu toto číslo položky, a pak z toho řádku něco vrátit. Dobře, v tomto případě to, co chci, je 2. sloupec z tohoto řádku. A pak na konci každého SVYHLEDÁNÍ musíme dát buď FALSE, nebo 0. Teď tady, poté, co vyberu rozsah, stisknu klávesu F4 a potom chci 2. sloupec a poté FALSE zápas. Nikdy si nevybírejte přibližnou shodu, nikdy, nikdy! Není to přibližná shoda, je to velmi zvláštní věc, nefunguje to pořád. Pokud chcete přepočítat svá čísla Komisi pro cenné papíry, v každém případě neváhejte použít,PRAVDA nebo prostě nechte FALSE vypnuto. Ale každý VLOOKUP, který kdy vytvoříte, by měl skončit FALSE nebo 0, 0 je kratší než FALSE, měli byste tam dát FALSE, ale 0 je totéž jako FALSE.
Dobře, řešení problémů, první věc, když uděláte spoustu VLOOKUPů, je velmi normální mít pár # N / As, že? A vždy najdu # N / A tím, že půjdu Data, ZA, což přinese # N / As na vrchol, právě tam, BG33-9, ať už je to překlep, nebo je to zcela nová položka, dobře, a máme přijít na to. Tady napravo mám nová data, oříznu je a potom Alt + IED vložíte tyto buňky do středu, nemusí to být abecední, tato tabulka nemusí být tříděna. Když používáte verzi FALSE, tabulka není - můžete ji jednoduše umístit kamkoli chcete, nedal jsem na konec, protože jsem nemusel přepsat vzorec, chci jen vzorec práce. Dobře, takže pár # N / A, extrémně, extrémně normální, ale podívejte se na to.
Když začnete s odpověďmi, které fungují, ale poté se # N / A začnou objevovat mírně často a nakonec se objeví úplně dolů, je to jisté znamení, že jste nezamkli odkaz na tabulku. Dobře, takže tady se stůl pohybuje, jak kopíruji vzorec dolů, správně, a tak mám štěstí, když narazím na několik, které byly na konci seznamu. Ale nakonec se dostanu do bodu, kdy se to dívá sem do úplně prázdných buněk, a samozřejmě se nic nenachází. Dobře, takže to je první věc, dostanete pár, který funguje, pár # N / A, pár dalších, které fungují, a pak vše # N / A je zbytek cesty - určitě znaménko, že jste nedali $ v.
Stačí se vrátit zpět, F2 pro režim úprav, vybrat dvojtečku, stisknout F4, která vloží všechny $, dvojitým kliknutím to sestřelit a věci začnou znovu fungovat, dobře. Další, přesně stejný vzorec, vzorec je dokonalý, viz BG33-8, nic z toho nedostáváme. Dobře, tak se jdu podívat, BG33-8, hej, tady to je, je to tam přímo, je to v červené barvě, měl jsem to vidět! Přišel jsem tedy k tomuto na pravé straně, zmáčknu F2 a sleduji blikající bod vložení a vidím, že je to hned po 8, a tak, pak přijdu sem a zmáčknu F2, tam jsou nějaké koncové mezery. Toto je velmi, velmi běžné v dobách COBOLU, říkali: „Víte, podívejte se, dáme vám 10 mezer pro číslo položky, a pokud nezadáte všech 10 mezer, vyplní mezery . “ A tak je to velmi běžné,a skvělým řešením je zbavit se těchto předních a koncových mezer pomocí funkce TRIM. Místo A2 použijte TRIM (A2), dvojitým kliknutím to sestřelte, dobře, stále je BG33-9 zpět v druhé tabulce.
Dobře, jen abyste pochopili, jak funguje TRIM, tak jsem napsal spoustu mezer, Johna, spoustu mezer, Durran a spoustu mezer, a pak jsem spojil * před a po, abyste viděli, jak to vypadá . Když se zeptám na TRIM (P4), zbavíme se všech předních prostorů, všech koncových prostorů a pak se více vnitřních prostorů zredukuje na jeden prostor. Dobře, takže můžete vidět, trochu si to vizualizovat, že se zbavují předních a koncových mezer, jakékoli zdvojnásobené mezery uprostřed se stanou takovým jediným prostorem. Takže TRIM, skvělý, skvělý nástroj ve vašem arzenálu, dobře, tady je další opravdu běžný nástroj.
Pokud to nejsou koncové mezery, pak nejběžnější věcí, kterou jsem viděl, je místo, kde tady máme skutečná čísla a tady máme čísla uložená jako text. A VLOOKUP to nebude vidět jako shodu, i když 4399, toto je číslo, toto je text, nefunguje. Nejrychlejší způsob, jak převést sloupec textu na čísla, vybrat sloupec, 3 písmena v pořadí, alt = "" DEF a najednou začnou naše VLOOKUPy znovu fungovat, skvělý, skvělý tip asi před 1500 podcasty. Dobře, takže to jsou nejčastější problémy VLOOKUP, buď jste zapomněli $, nebo máte koncové mezery, nebo máte čísla a čísla uložená jako text. Všechny tyto tipy jsou v této knize „MrExcel XL“, klikněte na „i“ v pravém horním rohu, knihu si můžete koupit.
Dobře, rychlá rekapitulace: VLOOKUP řeší mnoho problémů, pokud VLOOKUP začne fungovat, ale # N / A! stane se výraznějším, zapomněli jste $ do vyhledávací tabulky. Pokud existuje několik # N / A, jsou to jen položky, které v tabulce chybí. Pokud žádný z VLOOKUPů nefunguje a je to text, zkontrolujte koncové mezery, můžete použít funkci TRIM. Pokud máte čísla a čísla uložená jako text, vyberte libovolný sloupec, ten, který má text, a proveďte alt = "" DEF pro všechny ty zpět na čísla.
Dobře, ahoj, chci vám poděkovat, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2027.xlsx
