Jak porovnat dva sloupce v aplikaci Excel pomocí funkce VLOOKUP

Při práci s velkými datovými sadami v Excelu možná budete muset porovnat dva sloupce, abyste našli podobnosti nebo rozdíly.

VLOOKUP je výkonná funkce, která vám umožňuje vyhledávat shodná data mezi dvěma sloupci. Funkce umožňuje vyhledávat hodnoty v jednom sloupci, které se objevují v jiném.

Tento článek ukazuje, jak porovnat dva sloupce pomocí funkce VLOOKUP, abyste mohli efektivně analyzovat svá data.

Obsah

• Základy funkce Vlookup
  • Funkce Vlookup
• Příprava vašich dat pro VLOOKUP
  • Formátování dat
• Jak používat SVYHLEDAT k porovnání dvou sloupců
  • Řešení dalších chyb
• Alternativy k VLOOKUP pro porovnávání sloupců
  • 1. Použití funkcí indexu a shody
  • 2. Použití XLOOKUP v Excelu
• Pět tipů, jak zlepšit výkon SVYHLEDAT
  • 1. Omezte rozsah vyhledávání
  • 2. Používejte absolutní odkazy
  • 3. Seřaďte svá data
  • 4. Použijte dvojité SVYHLEDAT
  • 5. Použijte podmíněné formátování
• Pokročilá použití pro VLOOKUP
• Naše závěrečné slovo

Základy funkce Vlookup

Předpokládejme, že máte tabulku se dvěma seznamy položek ve sloupci A a B. Chcete najít položky v seznamu 1, které se také zobrazují v seznamu 2.

Dokážete si představit, že ruční práce přes seznamy tisíců položek by byla časově velmi náročná. Naštěstí přináší VLOOKUP na záchranu!

Termín VLOOKUP znamená vertikální vyhledávání. Funkce porovná dva sloupce, najde mezi nimi shody a vrátí související hodnoty.

Funkce Vlookup

Zde je základní syntaxe funkce VLOOKUP:

=VLOOKUP(vyhledávací_hodnota, pole_tabulky, číslo indexu_sloupce, [vyhledání_rozsahu])

Toto je rozdělení prvků:

• lookup_value : hodnota, kterou chcete vyhledat v prvním sloupci pole table_array.

• table_array : rozsah buněk obsahujících data, ve kterých chcete vyhledávat.

• col_index_num : číslo sloupce v table_array, ze kterého chcete vrátit hodnotu.

• range_lookup : Volitelné. Je to buď PRAVDA (přibližná shoda) nebo NEPRAVDA (přesná shoda). Výchozí hodnota je TRUE.

Příprava vašich dat pro VLOOKUP

Než použijete funkci VLOOKUP k porovnání dvou , musíte si připravit data.

Vytvořte dva samostatné sloupce v listu, kde chcete porovnat hodnoty. Tento článek používá pro naše příklady sloupce A a B.

Zde jsou ukázková data použitá v tomto článku:

Toto je vzorová tabulka se záhlavím, která má být přidána do sekce s názvem „Příprava dat pro SVYHLEDAT“:

Seznam 1 Seznam 2
Položka 1 Položka
3 Položka 2 Položka 4
Položka 3 Položka 1
Položka 4 Položka 5

Formátování dat

Je důležité zajistit, aby data v obou sloupcích byla formátována podobně. SVYHLEDAT rozlišuje velká a malá písmena, což znamená, že na velkých a malých písmenech záleží.

Pokud je formátování nekonzistentní, mohou se v konečném výsledku objevit chyby shody.

Je také dobré odstranit duplicitní hodnoty nebo prázdné buňky, abyste minimalizovali riziko chyb.

Postup odstranění duplicitních hodnot:

1. Vyberte sloupec.

2. Přejděte na kartu Data v horním pásu karet.

3. Klikněte na Odstranit duplikáty v části Datové nástroje.

Tlačítko může být trochu špatně vidět. Pomůže vám tento obrázek:

Postup odstranění prázdných buněk:

1. Vyberte sloupec.

2. Přejděte na kartu Domů v horním pásu karet.

3. Rozbalte nabídku „Najít a vybrat“.

4. Vyberte „Přejít na speciální“.

5. Z možností vyberte „Blanks“.

6. Klikněte na „Odstranit“ v části Buňky.

Jak používat SVYHLEDAT k porovnání dvou sloupců

Jakmile jsou data připravena, můžete napsat vzorec SVYHLEDAT pro porovnání dvou sloupců v Excelu a identifikaci shod. Následuj tyto kroky:

1. Vyberte buňku v novém sloupci, kde chcete zobrazit výsledky porovnání (např. buňku C2).

2. Zadejte následující vzorec: =VLOOKUP(A2, B:B, 1, FALSE))

3. Stisknutím klávesy Enter použijete vzorec.

4. V případě shodné hodnoty se hodnota zobrazí ve stejném řádku sloupce s výsledkem (např. sloupec C).

5. Přetáhněte vzorec dolů z C2 a zkopírujte jej přes tolik buněk, kolik potřebujete.

Pokud používáte naše vzorová data, umístěte výsledky srovnání do třetího sloupce. Vzorec najde tři shody ze čtyř položek v seznamu 1.

Všimněte si, že položka 2 se zobrazuje jako #N/A. Excel nám říká, že shoda není použitelná, tj. nebyla nalezena.

To je správné a je to užitečná informace o chybějících hodnotách. Někteří uživatelé Excelu si však mohou myslet, že došlo k problému s daty nebo chybě funkce.

Je dobrým zvykem ukázat jinou indikaci, že nebyla nalezena žádná shoda. To může být prostě prázdnota.

Chcete-li to provést, zkombinujte funkci VLOOKUP s funkcí IFNA takto:

=IFNA(SVYHLEDAT(A1; B:B; 1; NEPRAVDA); “”)

Funkce IFNA detekuje chybu #N/A a nahradí výstup prázdným místem (“”). Můžete také použít funkci ISNA nebo mít další logiku s funkcí IF.

Řešení dalších chyb

Funkce SVYHLEDAT může někdy generovat další chyby ve výsledcích porovnání. #REF ! Chyba v Excelu je skutečný problém s vašimi daty.

Obvykle k tomu dochází, když je váš zadaný rozsah nesprávný. Náš příklad odkazoval na celý sloupec, ale můžete použít i vertikální rozsahy buněk.

Ujistěte se, že rozsah vyhledávání, na který odkazujete, pokrývá všechny hodnoty, které chcete porovnat.

Alternativy k VLOOKUP pro porovnávání sloupců

Při porovnávání dvou sloupců v Excelu za účelem nalezení shod existují dvě hlavní alternativní vyhledávací funkce.

1. Použití funkcí indexu a shody

Místo použití funkce SVYHLEDAT můžete porovnat dva sloupce v Excelu kombinací funkcí INDEX a MATCH.

Tato metoda poskytuje flexibilnější způsob vyhledávání dat a je zvláště užitečná při práci s nesousedícími sloupci nebo když se index sloupce může změnit.

Toto je syntaxe, která se má vložit do sloupce výsledků:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : rozsah buněk, které obsahují data, která chcete vrátit.

• Lookup_value : hodnota, kterou chcete vyhledat v rozsahu vyhledávání.

• Lookup_range : rozsah buněk, ve kterých chcete najít vyhledávací hodnotu.

Pomocí stejných dat v předchozích příkladech nahradíme vzorec SVYHLEDAT následovně:

=(INDEX(B:B, SHODA(A2; B:B; 0)))

Funkce MATCH vrátí relativní polohu hledané hodnoty v rámci hledané oblasti a funkce INDEX vrátí odpovídající hodnotu z vráceného rozsahu.

Výsledky na stejných datech budou stejné jako u VLOOKUP, které jsme použili dříve. Tento obrázek ukazuje používané funkce:

Chybu #N/A můžete také nahradit vlastní zprávou. Zde je příklad použití funkce IFERROR.

=IFERROR(INDEX(B:B, MATCH(A2; B:B; 0)), “Nenalezeno”)

2. Použití XLOOKUP v Excelu

Pro uživatele s Excel 365 nebo Excel 2019 je XLOOKUP alternativní logický test k VLOOKUP pro nalezení společných hodnot ve dvou sloupcích.

XLOOKUP poskytuje několik výhod. Můžete jej použít k prohledávání dat horizontálně i vertikálně, práci s nesousedícími sloupci a zadávání vlastních hodnot pro chyby.

Syntaxe XLOOKUP je:

=XLOOKUP(hodnota_hledání, rozsah_hledání, rozsah_vrácení, [pokud_nenalezeno], [režim_shody], [režim_hledání])

• lookup_value : hodnota, kterou chcete hledat v rozsahu vyhledávání.

• rozsah_hledání : rozsah buněk, ve kterém chcete najít vyhledávací hodnotu.

• return_range : rozsah buněk obsahujících data, která chcete vrátit.

• Podle potřeby přidejte vlastní chybové hodnoty, režim shody a parametry režimu vyhledávání.

XLOOKUP najde hledanou hodnotu v rozsahu vyhledávání a vrátí odpovídající hodnotu z vráceného rozsahu.

Vzorec je zvláště užitečný, když máte dva seznamy vložené do datových sad s více sloupci. V našich předchozích příkladech byly seznamy v prvním a druhém sloupci, ale váš list může obsahovat více dat.

Zde je příklad se dvěma seznamy v prvním a třetím sloupci tabulky:

Vzorec XLOOKUP vypadá takto:

=XLOOKUP(A2;C:C,C:C,””)

Tento obrázek ukazuje výsledek s porovnáním ve sloupci D. První hodnota je přítomna, ale druhá chybí.

Všimněte si, že nemám žádné další chybové vzorce, ale chybějící hodnota je zobrazena jako prázdná. Je to proto, že používám čtvrtý parametr, což je vlastní chybová hodnota. V tomto případě je to prázdný řetězec.

Jako bonus vám ukážu, jak používat XLOOKUP k porovnání napříč řádky. Pokud máte dva seznamy, které jsou na řádku 1 a řádku 2, nejjednodušší použití vzorce vypadá takto:

=XLOOKUP(B1; 2:2; 2:2; “”)

Tento obrázek ukazuje výsledky na dvou řádcích.

Díky vylepšené funkčnosti a flexibilitě je XLOOKUP vynikající alternativou k SVYHLEDAT pro porovnávání seznamů v Excelu za účelem zjištění, zda existují shody.

Pět tipů, jak zlepšit výkon SVYHLEDAT

Optimalizace funkce VLOOKUP vám může pomoci vyhnout se dlouhým čekacím dobám a zlepšit celkovou odezvu vašeho listu Excel.

Zde je šest tipů a triků, které můžete použít ke zlepšení výkonu VLOOKUP v Excelu.

1. Omezte rozsah vyhledávání

V příkladech jsem použil celé sloupce, aby byly jednoduché. Pokud pracujete s velkým množstvím dat, měli byste se tomu vyhnout. Použití plných sloupců může zpomalit proces výpočtu Excelu.

Místo toho zkuste použít přesný rozsah požadovaný pro vaše data (např. A1:A100). Tím se sníží počet buněk, které funkce SVYHLEDAT potřebuje vyhodnotit.

2. Používejte absolutní odkazy

Při zadávání rozsahu dat (např. buňky od B2 do B5) použijte absolutní odkazy. Tím zajistíte, že vzorec bude konzistentní a data budou existovat, když jej zkopírujete přes více buněk.

Zde je příklad:

=VLOOKUP(A2; $B$2:$B$5; 1; NEPRAVDA)

3. Seřaďte svá data

Pokud víte, že data ve vyhledávacím sloupci jsou řazena vzestupně, můžete použít SVYHLEDAT s hodnotou TRUE nebo 1 pro argument 'vyhledání_rozsahu'.

To způsobí, že Excel provede přibližnou shodu, která je rychlejší než přesná shoda napříč všemi buňkami. Při používání této možnosti však buďte opatrní, protože nesprávné řazení může vést k nesprávným výsledkům.

4. Použijte dvojité SVYHLEDAT

Pro urychlení procesu vyhledávání v Excelu můžete použít dvě funkce VLOOKUP.

První VLOOKUP určí, zda vyhledávací hodnota existuje, nastavením 'col_index_num' na 1 a 'range_lookup' na TRUE.

Pokud vrátí hodnotu TRUE, druhé SVYHLEDAT načte požadovanou hodnotu s nastavením 'rozsah_vyhledání' na hodnotu TRUE.

5. Použijte podmíněné formátování

Podmíněné formátování můžete v listu aplikace Excel použít ke zvýraznění odpovídajících nebo chybějících hodnot v určeném sloupci. Barvy můžete také použít na jedinečné hodnoty. Díky tomu jsou vaše data snáze čitelná.

Nabídku podmíněného formátování najdete ve skupině stylů na kartě Domů.

Pokročilá použití pro VLOOKUP

Párové vzorky T-testy se používají k porovnání průměrů dvou příbuzných vzorků. Toto video ukazuje jejich použití v Excelu.

Máte-li další data nebo proměnné, na které potřebujete během analýzy odkazovat, můžete použít SVYHLEDAT k načtení potřebných hodnot z jiné tabulky nebo listu.

VLOOKUP můžete například použít k načtení demografických informací nebo podmínek léčby pro každé spárované pozorování ve vaší datové sadě.

Naše závěrečné slovo

Nyní již dobře rozumíte používání funkce VLOOKUP. Tato výkonná funkce umožňuje rychle identifikovat rozdíly a shodné hodnoty mezi dvěma seznamy, díky čemuž je analýza dat efektivnější a přesnější.

Také jste viděli příklady dalších vyhledávacích a referenčních funkcí, jako je XLOOKUP, INDEX a MATCH. Přidání těchto prvků do vašich dovedností dále posílí vaše schopnosti analýzy dat.

Pokračujte v procvičování a zdokonalování svých dovedností SVYHLEDAT a brzy se stanete odborníkem na porovnávání sloupců v Excelu, čímž ušetříte čas a zvýšíte svou produktivitu.