Jak porovnat dva seznamy v Excelu

Excel je efektivní program pro zpracování dat. A jednou z metod analýzy informací je porovnání dvou seznamů. Pokud správně porovnáte dva seznamy v Excelu, bude organizace tohoto procesu velmi snadná. Stačí se jen řídit některými body, o kterých bude dnes řeč. Praktická implementace této metody zcela závisí na potřebách osoby nebo organizace v konkrétním okamžiku. Proto je třeba zvážit několik možných případů.

Porovnání dvou seznamů v Excelu

Samozřejmě můžete porovnat dva seznamy ručně. Ale bude to trvat dlouho. Excel má vlastní inteligentní sadu nástrojů, která vám umožní nejen rychle porovnávat data, ale také získat informace, které není tak snadné získat očima. Předpokládejme, že máme dva sloupce se souřadnicemi A a B. Některé hodnoty se v nich opakují.

Formulace problému

Musíme tedy tyto sloupce porovnat. Postup pro porovnání dvou dokumentů je následující:

1. Pokud jsou jedinečné buňky každého z těchto seznamů stejné a celkový počet jedinečných buněk je stejný a buňky jsou stejné, lze tyto seznamy považovat za stejné. Na pořadí, ve kterém jsou hodnoty v tomto seznamu naskládány, tolik nezáleží.
2. Můžeme mluvit o částečné shodě seznamů, pokud jsou jedinečné hodnoty samy o sobě stejné, ale počet opakování je jiný. Proto mohou takové seznamy obsahovat různý počet prvků.
3. Skutečnost, že se tyto dva seznamy neshodují, je indikována odlišnou sadou jedinečných hodnot.

Všechny tyto tři podmínky jsou současně podmínkami našeho problému.

Řešení problému

Pojďme vygenerovat dva dynamické rozsahy, abychom si usnadnili porovnávání seznamů. Každý z nich bude odpovídat každému ze seznamů.

Chcete-li porovnat dva seznamy, postupujte takto:

1. V samostatném sloupci vytvoříme seznam jedinečných hodnot, které jsou specifické pro oba seznamy. K tomu použijeme vzorec: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Samotný vzorec musí být zapsán jako maticový vzorec.
2. Pojďme určit, kolikrát se každá jedinečná hodnota vyskytuje v datovém poli. Zde jsou vzorce, jak to udělat: =COUNTIF(Seznam1,D5) a =COUNTI(Seznam2,D5).
3. Pokud je počet opakování i počet jedinečných hodnot stejný ve všech seznamech, které jsou zahrnuty v těchto rozsazích, pak funkce vrátí hodnotu 0. To znamená, že shoda je XNUMX %. V tomto případě získají nadpisy těchto seznamů zelené pozadí.
4. Pokud je veškerý jedinečný obsah v obou seznamech, vrátí se pomocí vzorců =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) hodnota bude nula. Pokud E1 neobsahuje nulu, ale taková hodnota je obsažena v buňkách E2 a F2, pak v tomto případě budou rozsahy rozpoznány jako odpovídající, ale pouze částečně. V tomto případě se nadpisy příslušných seznamů zbarví oranžově.
5. A pokud některý z výše popsaných vzorců vrátí nenulovou hodnotu, budou seznamy zcela neodpovídající.

Toto je odpověď na otázku, jak analyzovat sloupce na shodu pomocí vzorců. Jak je vidět, s využitím funkcí lze realizovat téměř jakoukoli úlohu, která na první pohled s matematikou nesouvisí.

Příklad testování

V naší verzi tabulky existují tři typy seznamů každého výše popsaného typu. Má částečnou a úplnou shodu, stejně jako neshodu.

Pro porovnání dat používáme rozsah A5:B19, do kterého střídavě vkládáme tyto dvojice seznamů. Co bude výsledkem srovnání, pochopíme podle barvy původních seznamů. Pokud jsou zcela odlišné, pak to bude červené pozadí. Pokud je část dat stejná, pak žlutá. V případě úplné identity budou odpovídající nadpisy zelené. Jak udělat barvu podle toho, jaký je výsledek? To vyžaduje podmíněné formátování.

Nalezení rozdílů ve dvou seznamech dvěma způsoby

Popišme si další dvě metody hledání rozdílů v závislosti na tom, zda jsou seznamy synchronní či nikoli.

Možnost 1. Synchronní seznamy

Toto je snadná možnost. Předpokládejme, že takové seznamy máme.

Chcete-li zjistit, kolikrát se hodnoty nesblížily, můžete použít vzorec: =SOUČET (-(A2:A20<>B2:B20)). Pokud ve výsledku dostaneme 0, znamená to, že oba seznamy jsou stejné.

Možnost 2: Zamíchané seznamy

Pokud seznamy nejsou identické v pořadí objektů, které obsahují, musíte použít funkci, jako je podmíněné formátování a obarvit duplicitní hodnoty. Nebo použijte funkci COUNTIF, pomocí kterého určíme, kolikrát se prvek z jednoho seznamu vyskytuje ve druhém.

Jak porovnat 2 sloupce řádek po řádku

Když porovnáváme dva sloupce, často potřebujeme porovnat informace, které jsou v různých řádcích. K tomu nám pomůže operátor LI. Pojďme se podívat, jak to funguje v praxi. K tomu uvádíme několik názorných situací.

Příklad. Jak porovnat 2 sloupce pro shody a rozdíly v jednom řádku

Abychom analyzovali, zda hodnoty, které jsou ve stejném řádku, ale různé sloupce jsou stejné, zapíšeme funkci IF. Vzorec se vkládá do každého řádku umístěného v pomocném sloupci, kde se zobrazí výsledky zpracování dat. Není ale vůbec nutné jej předepisovat do každého řádku, stačí jej zkopírovat do zbývajících buněk tohoto sloupce nebo použít značku automatického doplňování.

Měli bychom si zapsat takový vzorec, abychom pochopili, zda jsou hodnoty v obou sloupcích stejné nebo ne: =IF(A2=B2, “Shoda”, “”). Logika této funkce je velmi jednoduchá: porovnává hodnoty v buňkách A2 a B2, a pokud jsou stejné, zobrazí hodnotu „Coincid“. Pokud se data liší, nevrací žádnou hodnotu. Můžete také zkontrolovat buňky a zjistit, zda mezi nimi existuje shoda. V tomto případě je použit vzorec: =IF(A2<>B2, “Neodpovídají”, “”). Princip je stejný, nejprve se provede kontrola. Pokud se ukáže, že buňky splňují kritérium, zobrazí se hodnota „Neodpovídá“.

Je také možné použít následující vzorec v poli vzorce k zobrazení „Shoda“, pokud jsou hodnoty stejné, a „Neodpovídají“, pokud se liší: =IF(A2=B2; „Shoda“, „Neshoda“). Místo operátoru rovnosti můžete také použít operátor nerovnosti. Pouze pořadí hodnot, které se v tomto případě zobrazí, se bude mírně lišit: =IF(A2<>B2; „Nesouhlasit“, „Shodovat se“). Po použití první verze vzorce bude výsledek následující.

Tato obměna vzorce nerozlišuje malá a velká písmena. Pokud se tedy hodnoty v jednom sloupci liší od ostatních pouze tím, že jsou napsány velkými písmeny, program tento rozdíl nezaznamená. Aby se při porovnání rozlišovala velká a malá písmena, musíte použít funkci v kritériích PŘESNÝ. Zbytek argumentů zůstává nezměněn: =KDYŽ(PŘESNĚ(A2,B2), “Shoda”, “Unikátní”).

Jak porovnat více sloupců pro shody v jednom řádku

Hodnoty v seznamech je možné analyzovat podle celé sady kritérií:

1. Najděte ty řádky, které mají všude stejné hodnoty.
2. Najděte ty řádky, kde jsou shody pouze ve dvou seznamech.

Podívejme se na pár příkladů, jak v každém z těchto případů postupovat.

Příklad. Jak najít shodu v jednom řádku ve více sloupcích tabulky

Předpokládejme, že máme řadu sloupců, které obsahují informace, které potřebujeme. Stojíme před úkolem určit ty řádky, ve kterých jsou hodnoty stejné. Chcete-li to provést, musíte použít následující vzorec: =KDYŽ(A(A2=B2,A2=C2), “shoda”, “ “).

Pokud je v tabulce příliš mnoho sloupců, stačí ji použít společně s funkcí IF provozovatel COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”shoda”;” “). Číslo použité v tomto vzorci udává počet sloupců ke kontrole. Pokud se liší, musíte napsat tolik, kolik platí pro vaši situaci.

Příklad. Jak najít shodu v jednom řádku v libovolných 2 sloupcích tabulky

Řekněme, že potřebujeme zkontrolovat, zda se hodnoty v jednom řádku shodují ve dvou sloupcích s hodnotami v tabulce. Chcete-li to provést, musíte použít funkci jako podmínku OR, kde střídavě zapisujte rovnost každého ze sloupců k druhému. Zde je příklad.

Použijeme tento vzorec: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Může nastat situace, kdy je v tabulce hodně sloupců. V tomto případě bude vzorec obrovský a výběr všech potřebných kombinací může trvat hodně času. Chcete-li tento problém vyřešit, musíte použít funkci COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinečný řetězec”; “Nejedinečný řetězec”)

Vidíme, že celkem máme dvě funkce COUNTIF. U prvního střídavě zjišťujeme, kolik sloupců má podobnost s A2, a u druhého kontrolujeme počet podobností s hodnotou B2. Pokud v důsledku výpočtu podle tohoto vzorce dostaneme nulovou hodnotu, znamená to, že všechny řádky v tomto sloupci jsou jedinečné, pokud více, existují podobnosti. Pokud tedy v důsledku výpočtu podle dvou vzorců a sečtení konečných výsledků dostaneme nulovou hodnotu, pak je vrácena textová hodnota „Unikátní řetězec“, pokud je toto číslo větší, je napsáno, že tento řetězec není jedinečný.

Jak porovnat 2 sloupce v Excelu pro shody

Nyní si vezměme příklad. Řekněme, že máme tabulku se dvěma sloupci. Musíte zkontrolovat, zda se shodují. K tomu je potřeba aplikovat vzorec, kde se funkce také použije IF, a provozovatel COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Žádné shody ve sloupci B”, “Ve sloupci B jsou shody”)

Není vyžadována žádná další akce. Po výpočtu výsledku tímto vzorcem dostaneme if hodnotu třetího argumentu funkce IF zápasy. Pokud žádné nejsou, pak obsah druhého argumentu.

Jak porovnat 2 sloupce v Excelu pro shody a zvýraznit je barvou

Chcete-li usnadnit vizuální identifikaci odpovídajících sloupců, můžete je zvýraznit barvou. Chcete-li to provést, musíte použít funkci „Podmíněné formátování“. Podívejme se v praxi.

Hledání a zvýraznění shod podle barvy ve více sloupcích

Chcete-li určit shody a zvýraznit je, musíte nejprve vybrat datový rozsah, ve kterém bude kontrola provedena, a poté otevřít položku „Podmíněné formátování“ na kartě „Domů“. Zde vyberte jako pravidlo pro výběr buňky „Duplicitní hodnoty“.

Poté se objeví nové dialogové okno, ve kterém v levém vyskakovacím seznamu najdeme možnost „Opakování“ a v pravém seznamu vybereme barvu, která bude použita pro výběr. Po kliknutí na tlačítko „OK“ se vybere pozadí všech buněk s podobností. Pak už jen porovnejte sloupce podle oka.

Hledání a zvýraznění odpovídajících čar

Technika kontroly, zda se řetězce shodují, se mírně liší. Nejprve musíme vytvořit další sloupec a tam použijeme kombinované hodnoty pomocí operátoru &. Chcete-li to provést, musíte napsat vzorec formuláře: =A2&B2&C2&D2.

Vybereme sloupec, který byl vytvořen a obsahuje kombinované hodnoty. Dále provedeme stejnou sekvenci akcí, která je popsána výše pro sloupce. Duplicitní řádky budou zvýrazněny barvou, kterou určíte.

Vidíme, že na hledání opakování není nic těžkého. Excel k tomu obsahuje všechny potřebné nástroje. Před uvedením všech těchto znalostí do praxe je důležité jen cvičit.