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ě?
- 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.
- Začněte psát funkce
=VLOOKUP(
. Zadejte Ctrl + A, abyste získali nápovědu k této funkci. - 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
- 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.
- 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.
- Čtvrtý parametr říká aplikaci Excel, zda je shoda typu „close“ v pořádku. V takovém případě tomu tak není, zadejte tedy False.
- Vzorec dokončete kliknutím na OK. Přetažením úchytu zkopírujte vzorec dolů.
- 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.
-
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.
-
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. -
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.