Poslední komentáře

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í dat v Excelu

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í dat v Excelu funkcí STEJNÉ

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í.

Porovnání dat v Excelu za pomoci podmíněného formátování

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

Filtrování výsledků porovnání dat

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

Porovnání dat v Excelu

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.

Porovnání dat v Excelu

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).
Porovnání dat v Excelu

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.

Porovnání dat v Excelu pomocí Rozšířeného filtru

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.

Porovnání dat v Excelu za pomoci Rozšířeného filtru
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í dat v Excelu za pomocí Rozšířeného filtru

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ů.
Micorosoft Excel - porovnání souborů

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í.

Inquire - Microsoft Excel

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í sešitů v Excelu

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

Porovnání Excelových souborů

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.

Porovnání souborů MS Excel - INQUIRE

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.


Komentáře   

 
+9 # Josef Kroupa 2015-10-19 23:14
Edumatiku,
díky za skvělý seznam možností porovnávání dat v Excelu. Bez něj bych na svém tisíci položkovém seznamu dělal minimálně další rok :D.
Odpovědět | Odpovědět citací | Citovat
 
 
0 # Jan 2016-05-31 10:44
Dobrý den, můžete mi prosím poradit? Mám 2 tabulky, potřebuji porovnat jeden sloupec ve dvou tabulkách (tabulka "1" a tabulka "2", a potřebuji aby se mi označily pouze hodnoty, které jsou pouze v tabulce "1"
Odpovědět | Odpovědět citací | Citovat
 
 
0 # Daniel Mikláš 2016-06-08 10:13
Dobrý den, šel bych na to přes funkci SVYHLEDAT. Touto funkcí bych se "zeptal", zda se údaje z první tabulky nachází v druhé a případně naopak. Pokud údaje z jedná tabulky budou v druhé, funkce SVYHLEDAT vrátí název existujícího údaje, jestliže tam údaj nebude, funkce vrátí chybu #NENÍ_K_DISPOZI CI. Celé to lze učesat přes pomocné funkce IFERROR nebo KDYŽ.
Odpovědět | Odpovědět citací | Citovat
 
 
0 # Martin 2016-11-08 19:00
Dobrý den, nedaří se mi nikde najít postup na porovnání dat pouze v jednom sloupci? Abych zjistil, zda-li jsem omylem nezadal nějaké jméno či jiný údaj do sloupce dvakrát. Poradil byste mi jak na to prosím?
Odpovědět | Odpovědět citací | Citovat
 

Přidat komentář


Bezpečnostní kód
Obnovit