Jak porovnat dva sloupce v Excelu (pro shody a rozdíly)
Podívejte se na video – Porovnání dvou sloupců v Excelu pro shody a rozdíly
Jedním z dotazů, které často dostávám, je – „jak porovnat dva sloupce v Excelu?“.
To lze provést mnoha různými způsoby a metoda, kterou použijete, závisí na struktuře dat a na tom, co od ní uživatel chce.
Můžete například chtít porovnat dva sloupce a najít nebo zvýraznit všechny shodné datové body (které jsou v obou sloupcích), nebo pouze rozdíly (kdy je datový bod v jednom sloupci a v druhém ne) atd.
Protože se mě na to často ptají, rozhodl jsem se napsat tento rozsáhlý návod se záměrem pokrýt většinu (ne-li všechny) možné scénáře.
Pokud se vám bude zdát užitečný, předejte ho dalším uživatelům Excelu.
Tento návod zahrnuje:
Všimněte si, že techniky porovnávání sloupců uvedené v tomto návodu nejsou jediné.
V závislosti na vaší datové sadě může být nutné metodu změnit nebo upravit. Základní principy však zůstanou stejné.
Pokud si myslíte, že by se do tohoto návodu dalo něco přidat, dejte mi vědět v komentářích
Porovnání dvou sloupců pro přesnou shodu řádků
Tento způsob porovnání je nejjednodušší. V tomto případě je třeba provést porovnání řádek po řádku a určit, které řádky mají stejná data a které ne.
Příklad: Porovnání buněk ve stejném řádku
Níže je uveden soubor dat, kde potřebuji zkontrolovat, zda jméno ve sloupci A je stejné ve sloupci B, nebo ne.
Pokud je shoda, potřebuji výsledek jako „TRUE“, a pokud není shoda, pak potřebuji výsledek jako „FALSE“.
Následující vzorec by to udělal:
=A2=B2
Příklad: Porovnání buněk ve stejném řádku (pomocí vzorce IF)
Pokud chcete získat popisnější výsledek, můžete použít jednoduchý vzorec IF, který vrátí „Shoda“, pokud jsou názvy stejné, a „Neshoda“, pokud jsou názvy různé.
=IF(A2=B2,"Match","Mismatch")
Poznámka: V případě, že chcete, aby porovnání rozlišovalo malá a velká písmena, použijte následující vzorec IF:
=IF(EXACT(A2,B2),"Match","Mismatch")
Podle výše uvedeného vzorce by se „IBM“ a „ibm“ považovaly za dva různé názvy a výše uvedený vzorec by vrátil „Neshoda“.
Příklad:
Pokud chcete zvýraznit řádky se shodnými daty (namísto získání výsledku v samostatném sloupci), můžete to provést pomocí podmíněného formátování.
Níže jsou uvedeny kroky, jak to provést:
- Vyberte celý soubor dat.
- Klikněte na kartu „Domů“.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- V rozbalovacím seznamu klikněte na „Nové pravidlo“.
- V dialogovém okně „Nové pravidlo formátování“ klikněte na „Použít vzorec pro určení, které buňky formátovat“.
- Do pole vzorce zadejte vzorec: =$A1=$B1
- Klikněte na tlačítko Formátovat a zadejte formát, který chcete použít na odpovídající buňky.
- Klikněte na tlačítko OK.
Tím se zvýrazní všechny buňky, jejichž názvy jsou v každém řádku stejné.
Porovnat dva sloupce a zvýraznit shody
Pokud chcete porovnat dva sloupce a zvýraznit shodné údaje, můžete použít funkci duplikování v podmíněném formátování.
Všimněte si, že je to jiné než to, co jsme viděli při porovnávání jednotlivých řádků. V tomto případě nebudeme provádět porovnání řádek po řádku.
Příklad:
Něco jako na obrázku níže:
Všimněte si, že seznam ve sloupci A je větší než seznam ve sloupci B.
Všimněte si, že seznam ve sloupci B je větší. Také některé názvy jsou v obou seznamech, ale ne ve stejném řádku (například IBM, Adobe, Walmart).
Pokud chcete zvýraznit všechny shodné názvy společností, můžete to udělat pomocí podmíněného formátování.
Tady je postup, jak to udělat:
- Vyberte celý soubor dat.
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- Přejděte kurzorem na možnost Zvýraznit pravidla buněk.
- Klikněte na možnost Duplikovat hodnoty.
- V dialogovém okně Duplikovat hodnoty zkontrolujte, zda je vybrána možnost „Duplikovat“.
- Zadejte formátování.
- Klepněte na tlačítko OK.
Výše uvedené kroky by vám daly výsledek, jak je uvedeno níže.
Poznámka: Pravidlo pro podmíněné formátování duplicit nerozlišuje velká a malá písmena. Takže „Apple“ a „jablko“ jsou považovány za stejné a byly by zvýrazněny jako duplicity.
Příklad:
- Vyberte celou sadu dat.
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- Přejděte kurzorem na možnost Zvýraznit pravidla buněk.
- Klikněte na možnost Duplikovat hodnoty.
- V dialogovém okně Duplikovat hodnoty zkontrolujte, zda je vybrána možnost „Jedinečné“.
- Určete formátování.
- Klepněte na tlačítko OK.
Tím získáte výsledek, který je uveden níže. Zvýrazní se všechny buňky, které mají název, který se v druhém seznamu nevyskytuje.
Porovnání dvou sloupců a nalezení chybějících datových bodů
Pokud chcete zjistit, zda se datový bod z jednoho seznamu vyskytuje v druhém seznamu, musíte použít vyhledávací vzorce.
Předpokládejme, že máte datovou sadu podle obrázku níže a chcete identifikovat společnosti, které jsou přítomny ve sloupci A, ale nejsou ve sloupci B,
K tomu mohu použít následující vzorec VLOOKUP.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Tento vzorec používá funkci VLOOKUP ke kontrole, zda je název společnosti ve sloupci A přítomen ve sloupci B, nebo ne. Pokud je přítomen, vrátí tento název ze sloupce B, jinak vrátí chybu #N/A.
Tyto názvy, které vrátí chybu #N/A, jsou ty, které ve sloupci B chybí.
FunkceISERROR by vrátila TRUE, pokud je výsledek VLOOKUP chybný, a FALSE, pokud chybný není.
Pokud chcete získat seznam všech jmen, kde není žádná shoda, můžete sloupec s výsledkem filtrovat tak, abyste získali všechny buňky s hodnotou TRUE.
K témuž účelu můžete použít také funkci MATCH;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Poznámka: Osobně dávám přednost použití funkce Match (nebo kombinaci INDEX/MATCH) před funkcí VLOOKUP. Považuji ji za flexibilnější a výkonnější. Rozdíl mezi Vlookup a Index/Match si můžete přečíst zde.
Porovnat dva sloupce a vytáhnout odpovídající data
Pokud máte dvě datové sady a chcete porovnat položky v jednom seznamu s druhým a vytáhnout odpovídající datový bod, musíte použít vyhledávací vzorce.
Příklad: Příklad: V níže uvedeném seznamu chci získat hodnotu tržního ocenění pro sloupec 2. K tomu potřebuji vyhledat tuto hodnotu ve sloupci 1 a poté načíst odpovídající hodnotu tržního ocenění.
Níže je uveden vzorec, který toto provede:
=VLOOKUP(D2,$A:$B,2,0)
nebo
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Příklad:
Tyto vyhledávací vzorce potřebují přesnou shodu, aby poskytly správný výsledek.
V případě, že získáte soubor dat, kde je drobný rozdíl v názvech dvou sloupců, nebude použití výše uvedených vyhledávacích vzorců fungovat. Ve funkci VLOOKUP nebo MATCH existuje možnost přibližné shody, ale tu zde nelze použít.
Předpokládejme, že máte soubor dat, jak je uvedeno níže. Všimněte si, že ve sloupci 2 jsou názvy, které nejsou úplné (například JPMorgan místo JPMorgan Chase a Exxon místo ExxonMobil).
V takovém případě můžete použít částečné vyhledávání pomocí zástupných znaků.
V tomto případě poskytne správný výsledek následující vzorec:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
nebo
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
V uvedeném příkladu je hvězdička (*) zástupný znak, který může představovat libovolný počet znaků. Pokud je jí vyhledávací hodnota lemována z obou stran, bude za shodu považována jakákoli hodnota ve sloupci 1, která obsahuje vyhledávací hodnotu ve sloupci 2.
Například *Exxon* by byla shoda pro ExxonMobil (protože * může představovat libovolný počet znaků).
Mohou se vám také líbit následující tipy pro Excel & Výukové programy:
- Jak porovnat dva listy Excelu (pro zjištění rozdílů)
- Jak zvýraznit prázdné buňky v Excelu.
- Zvýraznění KAŽDÉHO dalšího řádku v Excelu.
- Pokročilý filtr Excelu:
- Zvýraznění řádků na základě hodnoty buňky v aplikaci Excel.
Kompletní průvodce s příklady.