když pracujete s daty v aplikaci Excel, dříve nebo později budete muset data porovnat. Mohlo by to být porovnání dvou sloupců nebo dokonce dat v různých listech / sešitech.
v tomto tutoriálu aplikace Excel vám ukážu různé metody, jak porovnat dva sloupce v aplikaci Excel a hledat shody nebo rozdíly.
Existuje několik způsobů, jak to udělat v Excelu a v tomto tutoriálu vám ukážu některé z nich (jako je například porovnávání pomocí VLOOKUP vzorce, nebo POKUD vzorec nebo Podmíněné formátování).
tak pojďme začít!
Obsah
- Porovnat Dva Sloupce (Side by Side)
- Porovnat bok po Boku Pomocí Rovno Podepsat Operátor
- Porovnejte vedle sebe pomocí funkce IF
- Označte Řádky s Odpovídající Údaje (nebo Jiná Data)
- Porovnat Dva Sloupce Pomocí funkce SVYHLEDAT (Najít Odpovídající/Jiné Údaje)
- Porovnejte dva sloupce pomocí VLOOKUP a najděte shody
- Porovnání Dvou Sloupců Pomocí funkce SVYHLEDAT a Najít Rozdíly (Chybějící Datové Body)
- Časté Dotazy při Porovnávání Dvou Sloupců
Porovnat Dva Sloupce (Side by Side)
To je nejvíce základní typ srovnání, kde musíte porovnat buňky v jednom sloupci s buňky ve stejném řádku v jiném sloupci.
Předpokládejme, že máte soubor údajů, jak je uvedeno níže, a vy prostě chcete zkontrolovat, zda je hodnota ve sloupci A v určité buňce je stejný (nebo jiný), když ve srovnání s hodnotou v sousední buňce.
samozřejmě, můžete to udělat, když máte malý datový soubor, když máte velký jeden, můžete použít jednoduché srovnání vzorec, aby si to udělat. A pamatujte, že vždy existuje šance na lidskou chybu, když to uděláte ručně.
dovolte mi, abych vám ukázal několik jednoduchých způsobů, jak to udělat.
Porovnat bok po Boku Pomocí Rovno Podepsat Operátor
Předpokládejme, že máte pod dataset a chcete vědět, co se řádky mají odpovídající údaje a to, co řádky mají různé údaje.
Níže je jednoduchý návod, jak porovnat dva sloupce (side by side):
=A2=B2
výše uvedený vzorec bude vám TRUE, pokud obě hodnoty jsou stejné a FALSE v případě, že nejsou.
Nyní, pokud potřebujete znát všechny hodnoty, které odpovídají jednoduše použít filtr a zobrazit pouze všechny SKUTEČNÉ hodnoty. A pokud chcete znát všechny hodnoty, které se liší, filtrujte všechny hodnoty, které jsou nepravdivé (jak je uvedeno níže):
Při použití této metody udělat sloupec srovnání v aplikaci Excel, je to vždy lepší zkontrolovat, že vaše data nemá žádné počáteční či koncové mezery. Pokud jsou přítomny, i když mají stejnou hodnotu, Excel je zobrazí jako odlišné. Zde je skvělý průvodce, Jak odstranit přední a koncové mezery v aplikaci Excel.
Porovnejte vedle sebe pomocí funkce IF
další metodou, kterou můžete použít k porovnání dvou sloupců, může být pomocí funkce IF.
to je podobné metodě výše, kde jsme použili operátor equal to ( = ), s jednou přidanou výhodou. Při použití funkce IF můžete zvolit hodnotu, kterou chcete získat, pokud existují shody nebo rozdíly.
například pokud existuje shoda, můžete získat text „shoda“ nebo získat hodnotu jako 1. Podobně, pokud existuje nesoulad, můžete naprogramovat vzorec tak, aby vám dal text „nesoulad“ nebo vám dal 0 nebo prázdnou buňku.
níže je uveden vzorec IF, který vrací „shodu“ , když mají obě buňky hodnotu buňky, a „neshodu“, když je hodnota odlišná.
=IF(A2=B2,"Match","Not a Match")
výše uvedený vzorec používá stejný stav pro kontrolu, zda dva buněk (ve stejné řadě) mít odpovídající údaje, nebo ne (A2=B2). Ale protože používáme funkci IF, můžeme ji požádat o vrácení konkrétního textu v případě, že je podmínka pravdivá nebo nepravdivá.
Jakmile máte vzorec výsledky v samostatném sloupci, můžete rychle filtrovat údaje a získat řádky, které mají odpovídající údaje nebo řádky neodpovídající dat.
Označte Řádky s Odpovídající Údaje (nebo Jiná Data)
Další skvělý způsob, jak rychle zkontrolovat řádky, které mají odpovídající údaje (nebo jiné údaje), je upozornit na tyto řádky pomocí podmíněného formátování.
můžete udělat obojí-zvýrazněte řádky, které mají stejnou hodnotu v řádku, stejně jako případ, kdy je hodnota odlišná.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete zvýraznit všechny řádky, kde je název stejný.
Níže jsou kroky k použití podmíněného formátování pro zvýraznění řádků s odpovídající údaje:
- Vyberte celý dataset (kromě záhlaví)
- Klikněte na kartu Domů
- Ve skupině Styly klikněte na Podmíněné Formátování
- V možnostech, které se objeví, klikněte na tlačítko Nové Pravidlo‘
- V Nové Pravidlo Formátování v dialogovém okně, klikněte na možnosti -„Použít vzorec k určení, které buňky do formátu‘
- V Podobě hodnot, kde tento vzorec je pravda, pole, zadejte vzorec: =$A2=$B2
- Klikněte na tlačítko Formát
- Klikněte na „Fill“ kartu a vyberte barvu, ve které chcete zvýraznit řádky se stejnou hodnotou v obou sloupcích
- Klepněte na tlačítko OK
výše uvedené kroky, by okamžitě zvýraznit řádky, kde název je stejný v obou sloupcích a a B (ve stejném řádku). A v případě, že je název jiný, nebudou tyto řádky zvýrazněny.
V případě, že chcete porovnat dva sloupce a zvýraznit řádky, kde názvy jsou různé, použít níže uvedené vzorce v podmíněné formátování dialogové okno (v kroku 6).
=$A2<>$B2
jak to funguje?
když použijeme podmíněné formátování se vzorcem, zvýrazní pouze ty buňky, kde je vzorec pravdivý.
Když používáme $A2=$B2, bude kontrolovat každé buňce (v obou sloupcích) a zjistit, zda hodnota v řádku ve sloupci A je rovno jedné ve sloupci B, nebo ne.
v případě, že se jedná o přesnou shodu, zvýrazní ji v zadané barvě a v případě, že se neshoduje, nebude.
nejlepší část o podmíněném formátování je, že nevyžaduje použití vzorce v samostatném sloupci. Také, když použijete pravidlo na datovou sadu, zůstává dynamický. To znamená, že pokud změníte libovolný název v datovém souboru, podmíněné formátování se odpovídajícím způsobem upraví.
Porovnat Dva Sloupce Pomocí funkce SVYHLEDAT (Najít Odpovídající/Jiné Údaje)
Ve výše uvedených příkladech jsem vám ukázal, jak porovnat dva sloupce (nebo seznamy), když jsme jen srovnání bok po boku buněk.
ve skutečnosti tomu tak bude jen zřídka.
Ve většině případů, budete mít dva sloupce s daty, a budete muset zjistit, zda datový bod v jednom sloupci existuje v jiné sloupec nebo ne.
v takových případech nemůžete použít jednoduché znaménko rovno nebo dokonce funkci IF.
potřebujete něco silnějšího …
… něco, co je přímo v uličce VLOOKUP!
ukážu vám dva příklady, kde porovnáme dva sloupce v Excelu pomocí funkce VLOOKUP k nalezení shody a rozdílů.
Porovnejte dva sloupce pomocí VLOOKUP a najděte shody
Předpokládejme, že máme datovou sadu, jak je uvedeno níže, kde máme některá jména ve sloupcích A A B.
Pokud budete muset zjistit, jaké jsou názvy, které jsou ve sloupci B, které jsou také ve sloupci, můžete použít níže uvedené vzorce SVYHLEDAT:
=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")
výše uvedený vzorec porovná dva sloupce (a, B) a dává vám jméno v případě, že název je ve sloupci B, jako dobře, a to se vrací „Žádný Zápas“ v případě, že název je ve Sloupci B, a ne ve Sloupci A.
ve výchozím nastavení, funkce SVYHLEDAT vrátí #N/A chyba v případě, že se nenajde přesnou shodu. Tak, aby se předešlo chybě, jsem omotal funkce SVYHLEDAT v IFERROR funkce, tak, že to dává „Žádný Zápas“, když název není k dispozici v sloupci a.
můžete to také udělat opačně srovnání – chcete-li zkontrolovat, zda název je ve Sloupci A, stejně jako Sloupec B. níže uvedený vzorec by udělat, že:
=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")
Porovnání Dvou Sloupců Pomocí funkce SVYHLEDAT a Najít Rozdíly (Chybějící Datové Body)
Zatímco ve výše uvedeném příkladu, zkontrolovali jsme, zda data v jednom sloupci byl tam v jiném sloupci, nebo ne.
stejný koncept můžete také použít k porovnání dvou sloupců pomocí funkce VLOOKUP a nalezení chybějících dat.
Předpokládejme, že máme datový soubor, jak je znázorněno níže, kde máme nějaká jména ve sloupcích a a B.
Pokud budete muset zjistit, jaké jsou názvy, které jsou ve sloupci B, které není ve sloupci, můžete použít níže uvedené vzorce SVYHLEDAT:
=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")
výše uvedený vzorec zkontroluje, zda je název ve sloupci B proti všechny názvy ve Sloupci a. V případě, že najde přesnou shodu, vrátí toto jméno a v případě, že nenajde přesnou shodu, vrátí chybu #N/A.
Protože jsem zájem najít chybějící jména, která jsou tam je sloupec B a ne ve sloupci A, potřebuji znát jména, která vrátí #N/A chybu.
to je důvod, proč jsem zabalil funkci VLOOKUP do funkcí IF a ISERROR. Celý tento vzorec dává hodnotu – „není k dispozici“, když název chybí ve sloupci A, a „k dispozici“, když je přítomen.
Chcete-li znát všechna jména, která chybí, můžete filtrovat sloupec výsledek na základě hodnoty „není k dispozici“.
můžete také použít níže ZÁPAS funkce, aby dostat stejný výsledek:
=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")
Níže jsou uvedeny některé běžné dotazy, které jsem obvykle, když lidé se snaží porovnat data ve dvou sloupcích v aplikaci Excel.
Q1. Jak porovnat více sloupců v aplikaci Excel ve stejném řádku pro zápasy? Počítat celkové duplikáty také.
Ans. Dali jsme postup pro porovnání dvou sloupců v aplikaci excel pro stejný řádek výše. Ale pokud chcete porovnat více sloupců v aplikaci excel na stejném řádku pak vidíte příklad
=IF(AND(A2=B2, A2=C2),"Full Match", "")
Tady máme ve srovnání údajů sloupec A, sloupec B sloupec C. Po tomto, použil jsem výše uvedený vzorec ve sloupci D a získat výsledek.
nyní pro počítání duplikátů musíte použít funkci Countif.
=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")
Q2. Který operátor používáte pro zápasy a rozdíly?
Ans. Níže jsou uvedeny operátoři k použití:
- najít zápasy, použijte rovná znaménko (=)
- najít rozdíly (nesoulad), použijte ne-rovná-podepsat (<>)
Q3. Jak porovnat dvě různé tabulky a vytáhnout odpovídající data?
Ans. K tomu můžete použít funkci VLOOKUP nebo INDEX & MATCH. Abychom lépe porozuměli této věci, vezmeme si příklad.
zde budeme mít dvě tabulky a nyní chceme udělat data shody. V první tabulce máte datovou sadu a ve druhé tabulce vezměte seznam ovoce a poté použijte data odpovídající tahu v jiném sloupci. Pro pull odpovídající, použijte vzorec
=INDEX($B:$B,MATCH($D2,$A:$A,0))
Q4. Jak odstranit duplikáty v aplikaci Excel?
Ans. Chcete-li odstranit duplicitní data, musíte nejprve najít duplicitní hodnoty.
Chcete-li najít duplikát, můžete použít různé metody, jako je podmíněné formátování, Vlookup, if příkaz a mnoho dalších. Excel má také in-vestavěný nástroj, kde si můžete jen vybrat data, a odstranit duplikáty ze sloupce nebo dokonce více sloupců
Q5. Vidím, že v obou sloupcích je odpovídající hodnota. Vzorce, které jste sdíleli výše, je však nepovažují za přesné shody. Proč?
Ans: Excel považuje něco za přesnou shodu, když se každý znak jedné buňky rovná druhému. Existuje vysoká šance, že ve vašem datovém souboru jsou přední nebo koncové mezery.
ačkoli tyto mezery mohou stále způsobit, že se hodnoty zdají být rovné pouhým okem, pro Excel se tyto liší. Pokud máte takovou datovou sadu, je nejlepší se těchto prostorů zbavit(pro tento účel můžete použít funkce Excel, jako je TRIM).
Q7. Jak porovnat dva sloupce, které dávají výsledek jako pravdivý, když celočíselné hodnoty všech prvních sloupců nejsou menší než celočíselné hodnoty druhého sloupce. K vyřešení tohoto problému nevyžaduji podmíněné formátování, funkci Vlookup, příkaz If a další vzorce. Potřebuji vzorec k vyřešení tohoto problému.
Ans. Pro řešení tohoto problému můžete použít vzorec pole.
syntaxe je {=a (H6:H12>I6:I12)}. Výsledkem bude „True“, kdykoli je hodnota sloupce H větší než hodnota ve sloupci i, jinak bude výsledkem „False“.
mohlo by se vám také líbit následující výukové programy aplikace Excel:
- Porovnat Dva Sloupce v aplikaci Excel (shody a rozdíly)
- Jak Skrýt Sloupce Na Základě Hodnoty Buňky v aplikaci Excel
- Jak Rozdělit Jeden Sloupec na Více Sloupců v aplikaci Excel
- Jak Vybrat Alternativní Sloupce v aplikaci Excel (nebo každý N-tý Sloupec)
- Jak Vložit na Filtrovaný Sloupec Přeskočit Skryté Buňky,
- Nejlepší aplikace Excel Knihy (které vám bude aplikace Excel Pro v 2020)
zpráva této ad 