Jak porovnat data v Excelu a najít rozdíly

Představujeme vám několik způsobů jak porovnat hodnoty buněk v excelových tabulkách. Rozpracované příklady naleznete ke stažení na konci článku.

Porovnání buněk za pomocí operátoru ROVNÁ SE

Základním vzorcem a operátorem rovná se (=) porovnáme obsah dvou vedlejších buněk. Pokud se buňky rovnají, vzorec vrátí stav PRAVDA, jinak NEPRAVDA.

Porovnání funkcí STEJNÉ

Alternativou je použití funkce STEJNÉ (angl. název funkce je EXACT). Funguje podobně jako základní porovnání přes operátor rovná se s podstatným rozdílem. Funkce STEJNÉ dokáže rozeznat rozdíly i v malých/velkých písmenech. Funkci lze samozřejmě použít i na porovnání číselných hodnot.

Porovnání podmíněným formátováním

Pokud bychom potřebovali stejné (nebo naopak rozdílné) údaje pouze zvýraznit, lze použít podmíněné formátování. Stačí označit oblast, kde potřebujeme vidět rozdíly a aplikovat podmíněné formátování.

Výsledné zvýraznění lze v případě potřeby filtrovat podle barvy zvýrazněné buňky.

Všechny výše uvedené postupy jsou velmi jednoduché, ale hodí se pouze na situace, kdy jsou porovnávaná data seřazena vzestupně nebo sestupně a nejsou mezi nimi přílišné rozdíly. Pokud máte nesourodá data, tyto postupy nejsou efektivní.

Jak porovnat nesourodá data?

Nachází se údaje z jednoho sloupce v druhém sloupci, resp. které údaje z jednoho sloupce v druhém chybí? Tuto otázku lze zodpovědět několika postupy.

Příklad s funkcí COUNTIF

Pokud se bude údaj v buňce nacházet v porovnávané oblasti, funkce COUNTIF vrátí hodnotu jedna nebo vyšší. Tato hodnota uvádí, kolikrát se hledaný údaj v porovnávané oblasti nachází. Na výsledný sloupec lze snadno použít filtrování.

Příklad s funkcí SVYHLEDAT

Funkce SVYHLEDAT (ang. VLOOKUP) je funkce, která v našem případě dokáže zjistit, zda se buňka z jednoho sloupce nachází v druhém. Porovnávané sloupce můžou být na jiných listech, dokonce i v jiných sešitech. Pokud se hledaný údaj z jednoho sloupce nebude nacházet v druhém, funkce SVYHLEDAT vrátí chybu #NENÍ_K_DISPOZICI (ang. #N/A). Hledaný údaj, který nebude k dispozici, ověříme funkcí JE.CHYBHODN (ang. ISERROR) a pokud opravdu nebude, necháme vypsat námi požadované hlášení funkcí KDYŽ (ang. IF), v našem případě frází ANO. Pokud bude, funkce KDYŽ nás upozorní zadanou frází. Jestli nás budou zajímat pouze chybějící údaje, necháme funkcí KDYŽ vypsat “” neboli nic.

Funkce SVYHLEDAT se v tomto případě používá k něčemu jinému, než je běžně používána. SVYHLEDAT většinou používáme pro zobrazení dalších údajů z tabulek. V našem případě ji využíváme pro dohledání chyby, tedy údaje, který není v prvním sloupci k dispozici.

Na zobrazení chyby je možné použít i jiné funkce, např. funkci POZVYHLEDAT (ang. MATCH).

Příklad s rozšířeným filtrem

Pro tuto situaci je potřeba mít originální tabulku na jednom listu a tabulku s rozdíly na druhém listu. Obě tabulky musí mít stejné názvy sloupců.

Na originální tabulce použijeme rozšířený filtr (karta DATA, skupina příkazů Seřadit a filtrovat, tlačítko Upřesnit). Oblast seznamu bude originální tabulka, oblastí kritérií bude tabulka se změnami.

Tímto se v originální tabulce vyfiltrují pouze ty údaje, které se nachází v obou tabulkách. A my potřebujeme zobrazit v originální tabulce ty záznamy, které nejsou v tabulce se změnami, tedy negaci stávajícího stavu. Toho dosáhneme tak, že v samostatném sloupci shodné údaje označíme, dáme jim určitý příznak (např. slovo ANO) a ten poté běžným filtrem odfiltrujeme.

Označení shodných dat provedeme v tomto případě výběrem buněk E2:E12, následně klávesovou zkratkou levý Alt + ; (středník) označíme pouze viditelné buňky, do kterých propíšeme slovo ANO přes zkratku Ctrl + Enter.

Následně stačí již klasickým filtrem filtrovat ve sloupci Shoda záznamy odpovídající frázi “ano”.

Porovnání celých sešitů

Ve verzi Microsoft Office Proffesional Plus 2013 (nejvyšší verze MS Office 2013) a novějších je k dispozici doplněk Spreadsheet Compare. Najdete jej mezi běžnými programy. Tento doplněk dokáže porovnat celé sešity, přičemž dokáže rozeznat rozdíly v hodnotách buněk, formátování, vzorcích a množství dalších parametrů.

Po jeho spuštění stačí přes tlačítko Compare Files vybrat excelové soubory k porovnání, určit který se bude vůči kterém porovnávat (lze změnit přes Swap) a spustit porovnání.

Samotné porovnání proběhne většinou velmi rychle a porovnává mnoho údajů ve všech listech porovnávaných sešitů.

Porovnání excelových souborů lze exportovat do samostatného souboru přes schránku nebo přes tlačítko Export results.

Pokud byste porovnávání excelových souborů prováděli pravidelně, lze tento doplněk přímo aktivovat do Excelu na kartu, resp. doplněk INQUIRE.

Aktivace doplňku INQUIRE

Inquire (česky pátrat, zkoumat, vyšetřovat) není ve výchozím stavu aktivní, tento doplněk nevidíte. Pro aktivaci je potřeba v nastavení Excelu, tedy jeho Možnostech vybrat Doplňky a přejít na správu Doplňků modelu COM (ang. COM Add-ins). Podrobný popis najdete v nápovědě Excelu.

Jak porovnat sešity přímo z Excelu?

Pokud máte doplněk aktivován, v pásu karet se nabídne nová karta INQUIRE. Nyní stačí mít otevřené dva podobné sešity a na kartě Inquire aktivovat příkaz Compare Files. Poté určíte, které se dva sešity se mají vůči sobě porovnávat a spustíte sestavu.

Zde lze vidět rozdíl mezi samostatným spuštěním doplňku Compare Files z Windows a z Excelu. Pokud jej spuštíte z Excelu, je potřeba mít soubory k porovnání otevřeny. Při porovnání z Compare Files máte navíc k dispozici možnost určit umístění neotevřeného excelového souboru.

Výše zmíněné příklady porovnávající excelové tabulky si můžete stáhnout a vyzkoušet na vašem počítači. Příklady ke stažení. Podrobně se příkladům věnujeme taktéž na našich pokročilých a manažerských kurzech Excelu.

Pokud nemáte Excel v nejvyšší edici Micorosft Office Professional Plus a máte Access, lze provést porovnání tabulek v Accessu za pomoci snadného průvodce.