Podívejte se na video - Porovnejte dva sloupce v aplikaci Excel pro shody a rozdíly
Jeden dotaz, který dostávám hodně, je - „jak porovnat dva sloupce v aplikaci Excel?“.
To lze provést mnoha různými způsoby a použitá metoda bude záviset na datové struktuře 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 odpovídající datové body (které jsou v obou sloupcích), nebo pouze rozdíly (kde je datový bod v jednom sloupci a ne ve druhém) atd.
Protože se mě na to tolik ptají, rozhodl jsem se napsat tento masivní tutoriál se záměrem pokrýt většinu (ne -li všechny) možné scénáře.
Pokud to považujete za užitečné, předejte to dalším uživatelům aplikace Excel.
Všimněte si, že techniky porovnávání sloupců zobrazené v tomto kurzu nejsou jediné.
Na základě vaší datové sady možná budete muset metodu změnit nebo upravit. Základní principy by však zůstaly stejné.
Pokud si myslíte, že do tohoto tutoriálu lze něco přidat, dejte mi vědět v sekci komentáře
Porovnejte dva sloupce pro přesnou shodu řádků
Toto je nejjednodušší forma srovnání. V tomto případě musíte provést porovnání po řádcích a určit, které řádky mají stejná data a které ne.
Příklad: Porovnejte buňky ve stejném řádku
Níže je soubor dat, kde musím zkontrolovat, zda je název ve sloupci A stejný ve sloupci B nebo ne.
Pokud existuje shoda, potřebuji výsledek jako „PRAVDIVÝ“, a pokud se neshoduje, pak potřebuji výsledek jako „NEPRAVDIVÝ“.
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 více popisný výsledek, můžete použít jednoduchý vzorec IF pro návrat „Match“, pokud jsou jména stejná, a „Mismatch“, pokud jsou názvy odlišná.
= IF (A2 = B2, "Shoda", "Neshoda")
Poznámka: V případě, že chcete porovnat velká a malá písmena, použijte následující vzorec IF:
= IF (PŘESNÉ (A2, B2), "Shoda", "Neshoda")
S výše uvedeným vzorcem by „IBM“ a „ibm“ byly považovány za dvě různá jména a výše uvedený vzorec by vrátil „Neshoda“.
Příklad: Zvýrazněte řádky pomocí odpovídajících dat
Pokud chcete zvýraznit řádky, které mají shodná data (namísto získání výsledku v samostatném sloupci), můžete to provést pomocí podmíněného formátování.
Zde jsou kroky, jak toho dosáhnout:
- Vyberte celou datovou sadu.
- Klikněte na kartu „Domů“.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- V rozevíracím seznamu klikněte na „Nové pravidlo“.
- V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
- Do pole vzorce zadejte vzorec: = $ A1 = $ B1
- Klikněte na tlačítko Formát a zadejte formát, který chcete použít pro odpovídající buňky.
- Klikněte na OK.
Tím se zvýrazní všechny buňky, kde jsou názvy v každém řádku stejné.
Porovnejte dva sloupce a zvýrazněte shody
Pokud chcete porovnat dva sloupce a zvýraznit odpovídající data, můžete použít funkce duplikátu v podmíněném formátování.
Všimněte si, že se to liší od toho, co jsme viděli při porovnávání každého řádku. V tomto případě nebudeme provádět porovnání řádek po řádku.
Příklad: Porovnejte dva sloupce a zvýrazněte odpovídající data
Datové sady často získáte tam, kde jsou shody, ale nemusí být ve stejném řádku.
Něco, jak je uvedeno níže:
Seznam ve sloupci A je větší než seznam v B. Také některá jména jsou v obou seznamech, ale ne ve stejném řádku (například IBM, Adobe, Walmart).
Pokud chcete zvýraznit všechny odpovídající názvy společností, můžete to provést pomocí podmíněného formátování.
Zde jsou kroky, jak toho dosáhnout:
- Vyberte celou sadu dat.
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- Najeďte kurzorem na možnost Zvýraznit pravidla buňky.
- Klikněte na Duplicitní hodnoty.
- V dialogovém okně Duplikovat hodnoty zkontrolujte, zda je vybrána možnost „Duplikovat“.
- Zadejte formátování.
- Klikněte na OK.
Výše uvedené kroky vám poskytnou výsledek, jak je uvedeno níže.
Poznámka: Pravidlo duplikátu podmíněného formátování nerozlišuje velká a malá písmena. „Apple“ a „jablko“ jsou tedy považovány za stejné a budou zvýrazněny jako duplikáty.
Příklad: Porovnejte dva sloupce a zvýrazněte nesoulad dat
V případě, že chcete zvýraznit jména, která jsou přítomna v jednom seznamu, a nikoli v druhém, můžete k tomu použít také podmíněné formátování.
- Vyberte celou sadu dat.
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na možnost „Podmíněné formátování“.
- Najeďte kurzorem na možnost Zvýraznit pravidla buňky.
- Klikněte na Duplicitní hodnoty.
- V dialogovém okně Duplikovat hodnoty zkontrolujte, zda je vybrána možnost „Unikátní“.
- Zadejte formátování.
- Klikněte na OK.
Tím získáte výsledek, jak je uvedeno níže. Zvýrazní všechny buňky, které mají název, který není uveden v jiném seznamu.
Porovnejte dva sloupce a najděte chybějící datové body
Pokud chcete zjistit, zda je datový bod z jednoho seznamu přítomen ve druhém seznamu, musíte použít vyhledávací vzorce.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete identifikovat společnosti, které jsou přítomny ve sloupci A, ale nikoli ve sloupci B,
K tomu mohu použít následující vzorec VLOOKUP.
= ISERROR (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Tento vzorec používá funkci VLOOKUP ke kontrole, zda je název společnosti v A uveden ve sloupci B nebo ne. Pokud je přítomen, vrátí toto jméno ze sloupce B, jinak vrátí chybu #N/A.
Tato jména, která vrací chybu #N/A, jsou ta, která ve sloupci B chybí.
Funkce ISERROR by vrátila hodnotu TRUE, pokud existuje výsledek VLOOKUP je chyba a FALSE, pokud se nejedná o chybu.
Pokud chcete získat seznam všech jmen, kde neexistuje shoda, můžete filtrovat sloupec výsledků, abyste získali všechny buňky pomocí PRAVDA.
K tomu můžete použít také funkci MATCH;
= NE (ČÍSLO (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Poznámka: Osobně dávám přednost použití funkce Match (nebo kombinace INDEX/MATCH) místo VLOOKUP. Považuji to za flexibilnější a výkonnější. Rozdíl mezi Vlookup a Index/Match si můžete přečíst zde.
Porovnejte dva sloupce a vytáhněte odpovídající data
Pokud máte dvě datové sady a chcete porovnat položky v jednom seznamu s druhým a načíst odpovídající datový bod, musíte použít vyhledávací vzorce.
Příklad: Vytáhněte odpovídající data (Přesné)
Například v níže uvedeném seznamu chci načíst 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ý to provede:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
nebo
= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Příklad: Vytáhněte odpovídající data (částečná)
V případě, že získáte datovou sadu, kde je menší rozdíl v názvech ve dvou sloupcích, výše uvedené vyhledávací vzorce nebudou fungovat.
Tyto vzorce vyhledávání vyžadují přesnou shodu, aby poskytly správný výsledek. Ve funkci VLOOKUP nebo MATCH existuje možnost přibližné shody, ale tu nelze použít.
Předpokládejme, že máte sadu dat, jak je uvedeno níže. Všimněte si toho, že ve sloupci 2 jsou jména, 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ů.
Následující vzorec dá v tomto případě správný výsledek:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
nebo
= INDEX ($ A $ 2: $ B $ 14, MATCH („*“ & D2 & „*“, $ A $ 2: $ A $ 14,0), 2)
Ve výše uvedeném příkladu je hvězdička (*) zástupný znak, který může představovat libovolný počet znaků. Když je vyhledávací hodnota na obou stranách lemována, bude jakákoli hodnota ve sloupci 1, která obsahuje vyhledávací hodnotu ve sloupci 2, považována za shodu.
Například * Exxon * by odpovídalo ExxonMobil (protože * může představovat libovolný počet znaků).
Také by se vám mohly líbit následující Excel tipy a návody:
- Jak porovnat dva listy aplikace Excel (pro rozdíly)
- Jak zvýraznit prázdné buňky v Excelu.
- Zvýrazněte KAŽDOU další ŘADU v aplikaci Excel.
- Pokročilý filtr aplikace Excel: Kompletní průvodce s příklady.
- Zvýrazněte řádky na základě hodnoty buňky v aplikaci Excel.
