Jak porovnat dvě buňky v Excelu (porovnávání řetězců)

Návod ukazuje, jak porovnávat textové řetězce v Excelu bez rozlišení velikosti písmen a přesnou shodu. Naučíte se řadu vzorců pro porovnání dvou buněk podle jejich hodnot, délky řetězce nebo počtu výskytů určitého znaku a také jak porovnat více buněk.

Při používání aplikace Excel k analýze dat je nejdůležitější přesnost. Nesprávné informace vedou k nedodržení termínů, špatnému odhadu trendů, chybným rozhodnutím a ztrátě příjmů.

Ačkoli vzorce Excelu jsou vždy dokonale pravdivé, jejich výsledky mohou být chybné, protože do systému pronikla nějaká chybná data. V takovém případě je jedinou nápravou kontrola správnosti dat. Ruční porovnání dvou buněk není žádný velký problém, ale odhalit rozdíly mezi stovkami a tisíci textových řetězců je téměř nemožné.

Tento kurz vás naučí, jak automatizovat zdlouhavý a na chyby náchylný úkol porovnávání buněk a jaké vzorce je nejlepší použít v každém konkrétním případě.

Jak porovnat 2 buňky v aplikaci Excel
Jak porovnat více buněk
Porovnání rozsahu buněk se vzorovou buňkou
Porovnání buněk podle délky řetězce
Porovnání řetězců podle výskytu určitého znaku

Jak porovnat dvě buňky v Excelu

V Excelu existují dva různé způsoby porovnávání řetězců v závislosti na tom, zda hledáte malá a velká písmena-citlivé nebo nerozlišující velká a malá písmena.

Vzorec pro porovnání dvou buněk bez ohledu na velikost písmen

Chcete-li v Excelu porovnat dvě buňky bez ohledu na velikost písmen, použijte jednoduchý vzorec, jako je tento:

=A1=B1

Kde A1 a B1 jsou buňky, které porovnáváte. Výsledkem vzorce jsou logické hodnoty TRUE a FALSE.

Pokud chcete vypisovat vlastní texty pro shody a rozdíly, vložte výše uvedený příkaz do logického testu funkce IF. Například:

=IF(A1=B1, "Equal", "Not equal")

Jak vidíte na obrázku níže, oba vzorce stejně dobře porovnávají textové řetězce, data a čísla:
Vzorce pro porovnání 2 buněk v aplikaci Excel bez ohledu na velikost písmen

Vzorce pro porovnání řetězců v aplikaci Excel s ohledem na velikost písmen

V některých situacích může být důležité nejen porovnat text ve dvou buňkách, ale také porovnat velikost znaků. Porovnání textu s ohledem na velikost písmen lze provést pomocí funkce Excel EXACT:

EXACT (text1, text2)

Kde text1 a text2 jsou dvě buňky, které porovnáváte.

Předpokládáme-li, že vaše řetězce jsou v buňkách A2 a B2, vzorec vypadá následovně:

=EXACT(A2, B2)

Výsledkem bude TRUE pro textové řetězce, které se shodují přesně včetně velikosti jednotlivých znaků, FALSE v opačném případě.

Pokud chcete, aby funkce EXACT poskytovala nějaké jiné výsledky, vložte ji do vzorce IF a zadejte vlastní text pro argumenty value_if_true a value_if_false:

=IF(EXACT(A2 ,B2), "Exactly equal", "Not equal")

Následující snímek obrazovky ukazuje výsledky porovnávání řetězců podle velikosti písmen v aplikaci Excel:
Vzorec pro porovnání řetězců v Excelu bez rozlišení velkých a malých písmen

Jak porovnat více buněk v Excelu

Chcete-li porovnat více než 2 buňky v řádku, použijte vzorce popsané ve výše uvedených příkladech v kombinaci s operátorem AND. Úplné podrobnosti jsou uvedeny níže.

Vzorec pro porovnání více než 2 buněk bez rozlišení velikosti písmen

V závislosti na tom, jak chcete zobrazit výsledky, využijte jeden z následujících vzorců:

=AND(A2=B2, A2=C2)

nebo

=IF(AND(A2=B2, A2=C2), "Equal", "Not equal")

Vzorce AND vrací hodnotu TRUE, pokud všechny buňky obsahují stejnou hodnotu, FALSE, pokud se některá hodnota liší. Vzorec IF vypíše popisky, které do něj zadáte, v tomto příkladu „Rovná se“ a „Nerovná se“.

Jak ukazuje obrázek níže, vzorec dokonale funguje s jakýmikoliv typy dat – textem, daty a číselnými hodnotami:

Porovnávání více buněk s ignorováním velikosti znaků

Vzorec pro porovnávání textu v několika buňkách s ohledem na velikost písmen

Chcete-li vzájemně porovnat více řetězců a zjistit, zda se přesně shodují, použijte následující vzorce:

=AND(EXACT(A2,B2), EXACT(A2, C2))

Nebo

=IF(AND(EXACT(A2,B2), EXACT(A2, C2)),"Exactly equal", "Not equal")

Stejně jako v předchozím příkladu první vzorec poskytuje hodnoty TRUE a FALSE, zatímco druhý vzorec zobrazuje vlastní texty pro shody a rozdíly:
Porovnání více řetězců přesně včetně velikosti znaků

Porovnání rozsahu buněk se vzorovou buňkou

Následující příklady ukazují, jak můžete ověřit, že všechny buňky v daném rozsahu obsahují stejný text jako ve vzorové buňce.

Vzorec pro porovnání buněk se vzorovým textem bez ohledu na velikost písmen

Pokud na velikosti písmen skutečně nezáleží, můžete pro porovnání buněk se vzorovým textem použít následující vzorec:

ROVNICE(rozsah)*CELKY(rozsah)=COUNTIF(rozsah, vzorová buňka)

V logickém testu funkce IF porovnáváte dvě čísla:

Celkový počet buněk v zadaném rozsahu (počet řádků vynásobený počtem sloupců) a
Počet buněk obsahujících stejnou hodnotu jako ve vzorové buňce (vrácenou funkcí COUNTIF).

Předpokládáme-li, že vzorový text je v buňce C2 a řetězce k porovnání jsou v rozsahu A2:B6, vzorec vypadá takto:

=ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2)

Aby byly výsledky uživatelsky přívětivější, tj. vypsat něco jako „Všechny odpovídají“ a „Všechny neodpovídají“ místo TRUE a FALSE, použijte funkci IF, jako jsme to udělali v předchozích příkladech:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2),"All match", "Not all match")

Vzorec pro porovnání buněk se vzorovým textem bez rozlišení velkých a malých písmen

Jak ukazuje výše uvedený snímek obrazovky, vzorec si dokonale poradí s rozsahem textových řetězců, ale lze jej použít i pro porovnání čísel a dat.

Vzorce pro porovnání řetězců s ukázkovým textem s ohledem na velikost písmen

Pokud záleží na velikosti písmen, můžete porovnat řetězce s ukázkovým textem pomocí následujících vzorců pro pole.

IF(ROWS(range)*COLUMNS(range)=SUM(–EXACT(sample_cell, range)), „text_if_match“, „text_if_not match“)

Při zdrojovém rozsahu umístěném v A2:B6 a vzorový text v C2 má vzorec následující tvar:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(--EXACT(C2, A2:B6)), "All match", "Not all match")

Na rozdíl od běžných vzorců aplikace Excel se vzorce pole dokončují stisknutím kláves Ctrl + Shift + Enter. Při správném zadání Excel uzavře vzorec pole do {východných závorek}, jak je znázorněno na snímku obrazovky:
Vzorový vzorec pro porovnání řetězců se vzorovým textem

Jak porovnat dvě buňky podle délky řetězce

Někdy můžete chtít zkontrolovat, zda textové řetězce v jednotlivých řádcích obsahují stejný počet znaků. Vzorec pro tuto úlohu je velmi jednoduchý. Nejprve zjistíte délku řetězce dvou buněk pomocí funkce LEN a poté tato čísla porovnáte.

Předpokládáme-li, že porovnávané řetězce jsou v buňkách A2 a B2, použijte jeden z následujících vzorců:

=LEN(A2)=LEN(B2)

nebo

=IF(LEN(A2)=LEN(B2), "Equal", "Not equal")

Jak již víte, první vzorec vrací logické hodnoty TRUE nebo FALSE, zatímco druhý vzorec vypisuje vlastní výsledky:

Porovnání dvou buněk podle délky řetězce

Jak ukazuje obrázek výše, vzorce fungují pro textové řetězce i čísla.

Tip. Pokud dva zdánlivě stejné řetězce vracejí různé délky, s největší pravděpodobností je problém v počátečních nebo koncových mezerách v jedné nebo obou buňkách. V takovém případě odstraňte přebytečné mezery pomocí funkce TRIM. Podrobné vysvětlení a příklady vzorců najdete zde:

Porovnání dvou buněk podle výskytu určitého znaku

Jedná se o poslední příklad v našem výukovém programu Excel Porovnání řetězců, který ukazuje řešení poměrně specifické úlohy. Předpokládejme, že máte 2 sloupce textových řetězců, které obsahují pro vás důležitý znak. Vaším cílem je zkontrolovat, zda dvě buňky v každém řádku obsahují stejný počet výskytů daného znaku.

Aby bylo vše jasnější, uvažujte následující příklad. Řekněme, že máte dva seznamy odeslaných (sloupec B) a přijatých objednávek (sloupec C). Každý řádek obsahuje objednávky pro určitou položku, jejíž jedinečný identifikátor je obsažen ve všech ID objednávek a je uveden ve stejném řádku ve sloupci A (viz obrázek níže). Chcete zajistit, aby každý řádek obsahoval stejný počet odeslaných a přijatých položek s tímto konkrétním ID.

Pro vyřešení tohoto problému napište vzorec s následující logikou:

Nejprve nahraďte jedinečný identifikátor ničím pomocí funkce SUBSTITUTE:
SUBSTITUTE(A1, character_to_count,"")
Poté vypočítejte, kolikrát se jedinečný identifikátor objeví v každé buňce. K tomu zjistěte délku řetězce bez jedinečného identifikátoru a odečtěte ji od celkové délky řetězce. Tato část se zapíše pro buňku 1 a buňku 2 jednotlivě, například:
LEN(cell 1) - LEN(SUBSTITUTE(cell 1, character_to_count, ""))
a
LEN(cell 2) - LEN(SUBSTITUTE(cell 2, character_to_count, ""))
Nakonec porovnáte tato 2 čísla tak, že mezi výše uvedené části vložíte znak rovnosti (=).

LEN(buňka 1) – LEN(SUBSTITUTE(buňka 1, znak_do_počtu, „“))=
LEN(buňka 2) – LEN(SUBSTITUTE(buňka 2, znak_do_počtu, „“))

V našem příkladu je jedinečný identifikátor v buňce A2 a řetězce k porovnání jsou v buňkách B2 a C2. Celý vzorec tedy vypadá takto:

=LEN(B2)-LEN(SUBSTITUTE(B2,$A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2,$A2,""))

Ve vzorci se vrátí TRUE, pokud buňky B2 a C2 obsahují stejný počet výskytů znaku v A2, FALSE v opačném případě. Aby byly výsledky pro uživatele smysluplnější, můžete vzorec vložit do funkce IF:

=IF(LEN(B2)-LEN(SUBSTITUTE(B2, $A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2, $A2,"")), "Equal", "Not equal")

Porovnat výskyty určitého znaku ve dvou buňkách

Jak vidíte na obrázku výše, vzorec funguje dokonale i přes několik dalších komplikací:

Počítávaný znak (jedinečný identifikátor) se může vyskytovat kdekoli v textovém řetězci.
Řetězce obsahují různý počet znaků a různé oddělovače, například středník, čárku nebo mezeru.

Takto se porovnávají řetězce v aplikaci Excel. Chcete-li se blíže seznámit se vzorci probíranými v tomto kurzu, můžete si stáhnout pracovní list Porovnání řetězců v aplikaci Excel. Děkuji vám za přečtení a doufám, že se příští týden uvidíme na našem blogu.

Mohlo by vás také zajímat

Porovnání dvou sloupců v Excelu na shody a rozdíly
Jak porovnat dva listy Excelu na rozdíly
Jak použít logický operátor k porovnání čísel
4 způsoby, jak v Excelu provést vlookup s rozlišením velkých a malých písmen