Excel VLOOKUP - Články TechTV

Obsah

Mnoho lidí se pokouší použít Excel jako databázi. I když to může fungovat jako databáze, některé úkoly, které by v databázovém programu byly velmi snadné, jsou v Excelu poměrně složité. Jedním z těchto úkolů je párování dvou seznamů na základě společného pole; to lze snadno provést pomocí aplikace Excel VLOOKUP. Funkce VLOOKUP bude pro vás mimořádně užitečná, proto se podívejte níže na příklad, kdy a jak tuto funkci používat.

Řekněme, že vám vaše cestovní kancelář zašle na konci měsíce zprávu o všech místech, která vaši zaměstnanci navštívili. Zpráva používá namísto názvů měst kódy letišť. Bylo by užitečné, kdybyste místo kódu mohli snadno zadat název skutečného města.

Na internetu najdete a importujete seznam zobrazující název města pro každý kód letiště.

Jak ale získáte tyto informace o každém záznamu v sestavě?

  1. Použijte funkci VLOOKUP. VLOOKUP znamená „Vertikální vyhledávání“. Lze jej použít kdykoli, když máte seznam dat s polem klíče ve sloupci nejvíce vlevo.
  2. Začněte psát funkce =VLOOKUP(. Zadejte Ctrl + A, abyste získali nápovědu k této funkci.
  3. VLOOKUP potřebuje čtyři parametry. První je kód města v původní zprávě. V tomto příkladu by to byla buňka D4
  4. Dalším parametrem je rozsah s vaší vyhledávací tabulkou. Zvýrazněte rozsah. Ujistěte se, že používáte F4, aby byl rozsah absolutní. (Absolutní reference má znak dolaru před číslem sloupce i číslem řádku. Při kopírování vzorce bude odkaz i nadále ukazovat na I3: J351.
  5. Třetí parametr informuje Excel, ve kterém sloupci se nachází název města. V rozsahu I3: J351 je název města ve sloupci 2. Zadejte 2 pro tento parametr.
  6. Čtvrtý parametr říká aplikaci Excel, zda je shoda typu „close“ v pořádku. V takovém případě tomu tak není, zadejte tedy False.
  7. Vzorec dokončete kliknutím na OK. Přetažením úchytu zkopírujte vzorec dolů.
  8. Protože jste pečlivě zadali absolutní vzorce, můžete zkopírovat sloupec E do sloupce D a získat cílové město. V tomto případě jsou všechny odlety z mezinárodního letiště Pearson v Torontu.

Zatímco tento příklad fungoval perfektně, když diváci používají VLOOKUP, obvykle to znamená, že porovnávají seznamy, které pocházejí z různých zdrojů. Pokud seznamy pocházejí z různých zdrojů, mohou vždy existovat jemné rozdíly, díky nimž se seznamy obtížně shodují. Zde jsou tři příklady toho, co se může pokazit a jak je napravit.

  1. Jeden seznam má pomlčky a druhý nikoli. Pomocí této =SUBSTITUTE()funkce odeberte pomlčky. Při prvním pokusu o VLOOKUP se zobrazí chyby N / A.

    Chcete-li odstranit pomlčky pomocí vzorce, použijte vzorec NÁHRADA. Použijte 3 argumenty. Prvním argumentem je buňka obsahující hodnotu. Dalším argumentem je text, který chcete změnit. Posledním argumentem je náhradní text. V tomto případě chcete změnit pomlčky na nic, takže vzorec je =SUBSTITUTE(A4,"-","").

    Tuto funkci můžete zabalit do SVYHLEDÁNÍ a získat popis.

  2. Tenhle je jemný, ale velmi běžný. Jeden seznam má za položkou koncové prázdné místo. Použijte = TRIM () k odstranění přebytečných mezer. Když zpočátku zadáte vzorec, zjistíte, že všechny odpovědi jsou chyby N / A. Určitě víte, že hodnoty jsou v seznamu a se vzorcem je vše v pořádku.

    Jednou standardní věcí, kterou je třeba zkontrolovat, je přesunout se do buňky s vyhledávací hodnotou. Stisknutím klávesy F2 přepněte buňku do režimu úprav. Jakmile jste v režimu úprav, můžete vidět, že kurzor je umístěn o jednu mezeru od posledního písmene. To znamená, že v záznamu je koncový prostor.

    K vyřešení problému použijte funkci TRIM. =TRIM(D4)odstraní přední mezery, zadní mezery a nahradí všechny vnitřní dvojité mezery jednou mezerou. V tomto případě TRIM funguje perfektně, aby odstranil zadní prostor. =VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)je vzorec.

  3. V poznámkách k výstavě jsem zmínil bonusový tip: jak nahradit výsledek # N / A za chybějící hodnoty mezerou. Pokud vaše vyhledávací hodnota není ve vyhledávací tabulce, vrátí funkce VLOOKUP chybu N / A.

    Tento vzorec pomocí této =ISNA()funkce zjišťuje, zda je výsledkem vzorce N / A chyba. Pokud se zobrazí chyba, druhý argument ve funkci IF řekne aplikaci Excel, aby vložila libovolný text, který si přejete.

    =IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))

VLOOKUP vám umožňuje ušetřit čas při porovnávání seznamů dat. Udělejte si čas a osvojte si základní použití a budete moci dělat mnohem výkonnější úkoly v aplikaci Excel.

Zajímavé články...