Porovnání 2 souborů v Excelu pro rozdíly

Každý uživatel se může dostat do situace, kdy potřebuje porovnat dvě tabulky. No a v krajním případě musí každý porovnat dva sloupce. Ano, samozřejmě, práce se soubory Excelu je velmi pohodlná a pohodlná. Pardon, tohle není srovnání. Vizuální třídění malé tabulky je samozřejmě možné, ale když počet buněk jde do tisíců, musíte použít další analytické nástroje.

Bohužel ještě nebyla otevřena kouzelná hůlka, která umožňuje automaticky porovnávat všechny informace mezi sebou jedním kliknutím. Proto musíte pracovat, konkrétně sbírat data, specifikovat potřebné vzorce a provádět další akce, které vám umožní alespoň trochu automatizovat porovnávání.

Takových akcí je mnoho. Podívejme se na některé z nich.

Jaký je účel porovnávání souborů Excel?

Důvodů, proč se porovnává několik souborů aplikace Excel, může být celá řada. Dříve nebo později se s takovou potřebou setká každý uživatel a on takové otázky nemá. Můžete například chtít porovnat data ze dvou přehledů za různá čtvrtletí, abyste zjistili, zda finanční výsledky vzrostly nebo klesly.

Nebo, alternativně, učitel potřebuje vidět, kteří studenti byli vyhozeni z univerzity, porovnáním složení studentské skupiny v loňském a letošním roce.

Takových situací může být obrovské množství. Přejděme ale k praxi, protože téma je poměrně složité.

Všechny způsoby, jak porovnat 2 tabulky v Excelu

Přestože je téma složité, je snadné. Ano, nedivte se. Je složitý, protože se skládá z mnoha částí. Ale tyto části samy o sobě jsou snadno pochopitelné a proveditelné. Podívejme se, jak můžete porovnat dvě excelové tabulky, přímo v praxi.

Vzorec rovnosti a test nepravda-pravda

Začněme samozřejmě tou nejjednodušší metodou. Tento způsob porovnávání dokumentů je možný a v poměrně širokém rozsahu. Porovnávat můžete nejen textové hodnoty, ale i číselné. A vezměme si malý příklad. Řekněme, že máme dva rozsahy s buňkami ve formátu čísel. Chcete-li to provést, jednoduše napište vzorec rovnosti =C2=E2. Pokud se ukáže, že jsou stejné, do buňky se zapíše „PRAVDA“. Pokud se liší, pak FALSE. Poté musíte tento vzorec přenést do celého rozsahu pomocí značky automatického doplňování.

Nyní je rozdíl viditelný pouhým okem.

Zvýraznění odlišných hodnot

Hodnoty, které se od sebe liší, můžete také zvýraznit speciální barvou. To je také docela jednoduchý úkol. Pokud vám stačí najít rozdíly mezi dvěma rozsahy hodnot nebo celými tabulkami, musíte přejít na kartu „Domů“ a vybrat tam položku „Najít a zvýraznit“. Než na něj kliknete, nezapomeňte zvýraznit sadu buněk, ve kterých jsou uloženy informace pro porovnání. 

V zobrazené nabídce klikněte na nabídku „Vyberte skupinu buněk…“. Dále se otevře okno, ve kterém musíme jako kritérium vybrat rozdíly podle řádků.

Porovnání 2 tabulek pomocí podmíněného formátování

Podmíněné formátování je velmi pohodlná a hlavně funkční metoda, která umožňuje zvolit barvu, která zvýrazní jinou nebo stejnou hodnotu. Tuto možnost najdete na kartě Domů. Zde najdete tlačítko s příslušným názvem a v zobrazeném seznamu vyberte „Spravovat pravidla“. Objeví se správce pravidel, ve kterém musíme vybrat nabídku „Vytvořit pravidlo“.

Dále ze seznamu kritérií musíme vybrat to, kde je uvedeno, že musíme použít vzorec k určení buněk, které budou naformátovány zvláštním způsobem. V popisu pravidla musíte zadat vzorec. V našem případě je to =$C2<>$E2, poté potvrdíme naše akce stisknutím tlačítka „Formátovat“. Poté nastavíme vzhled buňky a přes speciální miniokénko s ukázkou uvidíme, zda se nám líbí. 

Pokud vše vyhovuje, klikněte na tlačítko „OK“ a potvrďte akce.

Ve Správci pravidel podmíněného formátování může uživatel najít všechna pravidla formátování, která jsou platná v tomto dokumentu. 

Funkce COUNTIF + pravidla porovnávání tabulek

Všechny metody, které jsme popsali dříve, jsou vhodné pro ty formáty, jejichž formát je stejný. Pokud nebyly tabulky dříve objednány, pak je nejlepší metodou porovnat dvě tabulky pomocí funkce COUNTIF a pravidla. 

Představme si, že máme dva rozsahy s mírně odlišnými informacemi. Stojíme před úkolem je porovnat a pochopit, která hodnota je jiná. Nejprve jej musíte vybrat v prvním rozsahu a přejít na kartu „Domů“. Zde najdeme dříve známou položku „Podmíněné formátování“. Vytvoříme pravidlo a nastavíme pravidlo na použití vzorce. 

V tomto příkladu je vzorec takový, jaký je zobrazen na tomto snímku obrazovky.

Poté nastavíme formát, jak je popsáno výše. Tato funkce analyzuje hodnotu obsaženou v buňce C1 a podívá se na rozsah určený ve vzorci. Odpovídá druhému sloupci. Musíme vzít toto pravidlo a zkopírovat ho v celém rozsahu. Hurá, všechny buňky s neopakujícími se hodnotami jsou zvýrazněny.

Funkce VLOOKUP pro porovnání 2 tabulek

V této metodě budeme uvažovat funkci VPR, který kontroluje, zda existují nějaké shody ve dvou tabulkách. Chcete-li to provést, musíte zadat vzorec zobrazený na obrázku níže a přenést jej na celý rozsah, který se používá pro srovnání.

Tato funkce iteruje každou hodnotu a zjišťuje, zda existují nějaké duplikáty z prvního sloupce do druhého. Po provedení všech operací se tato hodnota zapíše do buňky. Pokud tam není, dostaneme chybu #N/A, což je dostačující k tomu, abychom automaticky pochopili, která hodnota se nebude shodovat.

Funkce IF

Logická funkce POKUD – toto je další dobrý způsob, jak porovnat dva rozsahy. Hlavním rysem této metody je, že můžete použít pouze část pole, která se porovnává, a ne celou tabulku. To šetří prostředky pro počítač i uživatele.

Vezměme si malý příklad. Máme dva sloupce – A a B. Některé informace v nich musíme vzájemně porovnat. K tomu musíme připravit další sloupec služby C, ve kterém je zapsán následující vzorec.

Použití vzorce, který používá funkce IF, IFERROR и VÍCE EXPOZOVANĚ můžete iterovat přes všechny požadované prvky ve sloupci A a poté ve sloupci B. Pokud byl nalezen ve sloupcích B a A, vrátí se do odpovídající buňky.

Makro VBA

Makro je nejsložitější, ale také nejpokročilejší metoda porovnávání dvou tabulek. některé možnosti porovnání obecně nejsou možné bez skriptů VBA. Umožňují vám automatizovat proces a šetřit čas. Všechny nezbytné operace pro přípravu dat, pokud jsou jednou naprogramovány, budou nadále prováděny.

Na základě řešeného problému můžete napsat jakýkoli program, který porovnává data bez jakéhokoli zásahu uživatele.

Jak porovnávat soubory v Excelu

Pokud si uživatel dal za úkol (no, nebo dostal jeden) porovnat dva soubory, lze to provést dvěma způsoby najednou. První z nich využívá specializovanou funkci. Chcete-li implementovat tuto metodu, postupujte podle pokynů:

1. Otevřete soubory, které chcete porovnat.
2. Otevřete záložku „Zobrazit“ – „Okno“ – „Zobrazit vedle sebe“.

Poté se v jednom excelovém dokumentu otevřou dva soubory.

Totéž lze provést s běžnými nástroji Windows. Nejprve musíte otevřít dva soubory v různých oknech. Poté vezměte jedno okno a přetáhněte jej na zcela levou stranu obrazovky. Poté otevřete druhé okno a přetáhněte jej na pravou stranu. Poté budou dvě okna vedle sebe. 

Podmíněné formátování pro porovnání 2 souborů Excel

Porovnávat dokumenty velmi často znamená zobrazovat je vedle sebe. V některých případech je však možné tento proces automatizovat pomocí podmíněného formátování. S ním můžete zkontrolovat, zda jsou mezi listy rozdíly. To vám umožní ušetřit čas, který lze využít pro jiné účely.

Nejprve musíme převést porovnávané listy do jednoho dokumentu. 

Chcete-li to provést, musíte kliknout pravým tlačítkem myši na příslušný list a poté v rozbalovací nabídce kliknout na tlačítko „Přesunout nebo zkopírovat“. Dále se objeví dialogové okno, ve kterém si uživatel může vybrat dokument, do kterého má být tento list vložen.

Dále je třeba vybrat všechny požadované buňky, aby se zobrazily všechny rozdíly. Nejjednodušší způsob, jak to udělat, je kliknout na levou horní buňku a poté stisknout kombinaci kláves Ctrl + Shift + End.

Poté přejděte do okna podmíněného formátování a vytvořte nové pravidlo. Jako kritérium použijeme vzorec vhodný v konkrétním případě, poté nastavíme formát.

Pozor: adresy buněk musí být uvedeny ty, které jsou na jiném listu. To lze provést prostřednictvím nabídky zadání vzorce.

Porovnání dat v Excelu na různých listech

Předpokládejme, že máme seznam zaměstnanců, který také uvádí jejich platy. Tento seznam je aktualizován každý měsíc. Tento seznam se zkopíruje na nový list.

Předpokládejme, že potřebujeme porovnat platy. V tomto případě můžete jako data použít tabulky z různých listů. Pro zvýraznění rozdílů použijeme podmíněné formátování. Všechno je jednoduché.

S podmíněným formátováním můžete provádět efektivní srovnání, i když jsou jména zaměstnanců v jiném pořadí.

Jak porovnat 2 listy v excelové tabulce

Porovnání informací umístěných na dvou listech se provádí pomocí funkce VÍCE EXPOZOVANĚ. Jeho prvním parametrem je dvojice hodnot, které musíte hledat na listu, který je zodpovědný za další měsíc. Jednoduše řečeno březen. Prohlížený rozsah můžeme označit jako kolekci buněk, které jsou součástí pojmenovaných rozsahů, kombinované ve dvojicích.

Můžete tedy porovnávat řetězce podle dvou kritérií – příjmení a platu. No, nebo jakýkoli jiný, definovaný uživatelem. U všech nalezených shod se do buňky, do které se zadává vzorec, zapíše číslo. Pro Excel bude tato hodnota vždy pravdivá. Proto, aby bylo formátování použito na ty buňky, které byly odlišné, musíte tuto hodnotu nahradit LEŽÍCÍ, pomocí funkce =NOT().

Nástroj pro porovnávání tabulek

Excel má speciální nástroj, který umožňuje porovnávat tabulky a automaticky zvýrazňovat změny. 

Je důležité si uvědomit, že tento nástroj je dostupný pouze uživatelům, kteří si zakoupili kancelářské balíky Professional Plus.

Můžete jej otevřít přímo z karty „Domů“ výběrem položky „Porovnat soubory“.

Poté se objeví dialogové okno, ve kterém je třeba vybrat druhou verzi knihy. Můžete také zadat internetovou adresu, kde se tato kniha nachází.

Poté, co vybereme dvě verze dokumentu, musíme naše akce potvrdit klávesou OK.

V některých případech může dojít k chybě. Pokud se objeví, může to znamenat, že soubor je chráněn heslem. Po kliknutí na OK budete vyzváni k jeho zadání. 

Porovnávací nástroj vypadá jako dvě excelové tabulky vedle sebe ve stejném okně. V závislosti na tom, zda byly informace přidány, odebrány nebo došlo ke změně ve vzorci (a také na jiných typech akcí), jsou změny zvýrazněny různými barvami. 

Jak interpretovat výsledky srovnání

Je to velmi jednoduché: různé typy rozdílů jsou označeny různými barvami. Formátování se může rozšířit jak na výplň buňky, tak na samotný text. Pokud tedy byla do buňky zadána data, je výplň zelená. Pokud je něco nejasné, samotná služba má symboly, které ukazují, jaký typ změny je zvýrazněn jakou barvou.