Jak porovnat dva listy aplikace Excel (pro rozdíly)

Podívejte se na video - Jak porovnat rozdíly dvou listů Excelu

Porovnání dvou souborů aplikace Excel (nebo porovnání dvou listů ve stejném souboru) může být obtížné, protože sešit aplikace Excel zobrazuje pouze jeden list najednou.

To se stává obtížnějším a náchylnějším k chybám, když máte spoustu dat, která je třeba porovnat.

Naštěstí v aplikaci Excel existuje několik skvělých funkcí, které vám umožňují otevřít a snadno porovnat dva soubory aplikace Excel.

V tomto tutoriálu k Excelu vám ukážu několik způsobů, jak porovnat dva různé soubory aplikace Excel (nebo listy) a zkontrolujte rozdíly. Metoda, kterou zvolíte, bude záviset na tom, jak jsou vaše data strukturována a jaký druh srovnání hledáte.

Začněme!

Porovnání dvou listů aplikace Excel v samostatných souborech aplikace Excel (vedle sebe)

Pokud chcete porovnat dva samostatné soubory aplikace Excel vedle sebe (nebo dva listy ve stejném sešitu), existuje v aplikaci Excel integrovaná funkce, jak to provést.

To je Pohled vedle sebe volba.

To se doporučuje pouze v případě, že máte malou datovou sadu a ruční porovnávání těchto souborů bude pravděpodobně méně časově náročné a náchylné k chybám. Pokud máte velkou datovou sadu, doporučuji použít podmíněnou metodu nebo metodu vzorce popsanou dále v tomto kurzu.

Podívejme se, jak to použít, když musíte porovnat dva samostatné soubory nebo dva listy ve stejném souboru.

Předpokládejme, že máte dva soubory na dva různé měsíce a chcete zkontrolovat, jaké hodnoty se v těchto dvou souborech liší.

Ve výchozím nastavení pravděpodobně při otevření souboru zabere celou obrazovku. I když zmenšíte velikost, vždy uvidíte jeden soubor aplikace Excel nahoře.

Pomocí možnosti zobrazení vedle sebe můžete otevřít dva soubory a poté je uspořádat vodorovně nebo svisle. To vám umožní snadno porovnávat hodnoty bez přepínání tam a zpět.

Níže jsou uvedeny kroky, jak zarovnat dva soubory vedle sebe a porovnat je:

1. Otevřete soubory, které chcete porovnat.
2. V každém souboru vyberte list, který chcete porovnat.
3. Klikněte na kartu Zobrazit
4. Ve skupině Windows klikněte na možnost „Zobrazit vedle sebe“. Tato možnost bude k dispozici, pouze pokud máte otevřeny dva nebo více souborů aplikace Excel.

Jakmile kliknete na možnost Zobrazit vedle sebe, Excel uspořádá sešit vodorovně. Oba soubory budou viditelné a můžete je upravovat/porovnávat, když jsou uspořádány vedle sebe.

V případě, že chcete soubory uspořádat svisle, klikněte na možnost Uspořádat vše (na kartě Zobrazení).

Otevře se dialogové okno „Uspořádat Windows“, kde můžete vybrat „Svisle“.

V tomto okamžiku, pokud se posunete dolů v jednom z listů, druhý by zůstal tak, jak je. Můžete to změnit tak, že když se posouváte v jednom listu, posouvá se současně i druhý. To usnadňuje porovnávání po řádcích a zjišťování případných rozdílů.

K tomu ale musíte povolit Synchronní posouvání.

Chcete -li povolit synchronní posouvání, klikněte na kartu Zobrazit (v kterémkoli ze sešitů) a poté klikněte na možnost Synchronní posouvání. Toto je přepínací tlačítko (pokud ho tedy chcete vypnout, jednoduše na něj znovu klikněte).

Porovnání více listů v samostatných souborech aplikace Excel (vedle sebe)

Pomocí možnosti „Zobrazit vedle sebe“ můžete porovnávat pouze dva soubory aplikace Excel najednou.

Pokud máte otevřeno více souborů aplikace Excel, po kliknutí na možnost Zobrazit vedle sebe se vám zobrazí dialogové okno „Porovnat vedle sebe“, kde si můžete vybrat, který soubor chcete porovnat s aktivním sešitem.

Pokud chcete porovnat více než dva soubory najednou, otevřete všechny tyto soubory a poté klikněte na možnost Uspořádat vše (je to na kartě Zobrazení).

V dialogovém okně Uspořádat Windows vyberte Svisle/Vodorovně a poté klikněte na OK.

Tím se uspořádají všechny otevřené soubory aplikace Excel ve vybraném pořadí (svisle nebo vodorovně).

Porovnejte dva listy (vedle sebe) ve stejném sešitu aplikace Excel

V případě, že chcete porovnat dva samostatné listy ve stejném sešitu, nemůžete použít funkci Zobrazit vedle sebe (protože funguje pouze pro samostatné soubory aplikace Excel).

Ale stále můžete dělat stejné srovnání vedle sebe.

To je možné díky „Nová funkce Windows v Excelu vám umožňuje otevřít dvě instance na stejném sešitu. Jakmile otevřete dvě instance, můžete je uspořádat vedle sebe a poté je porovnat.

Předpokládejme, že máte sešit aplikace Excel, který má dva listy pro dva různé měsíce (leden a únor) a chcete je porovnat vedle sebe, abyste zjistili, jak se změnily tržby na obchod:

Níže jsou uvedeny kroky ke srovnání dvou listů v aplikaci Excel:

1. Otevřete sešit obsahující listy, které chcete porovnat.
2. Klikněte na kartu Zobrazit
3. Ve skupině Okno klikněte na možnost „Nové okno“. Tím se otevře druhá instance stejného sešitu.
4. Na kartě „Zobrazit“ klikněte na „Uspořádat vše“. Tím se otevře dialogové okno Uspořádat Windows
5. Chcete -li porovnávat data ve sloupcích, vyberte možnost „Svisle“ (pokud chcete porovnávat data v řádcích, vyberte možnost Horizontální).
6. Klikněte na OK.

Výše uvedené kroky by uspořádaly obě instance sešitu svisle.

V tomto okamžiku by v obou sešitech byl vybrán stejný pracovní list. V jednom ze sešitů vyberte druhý list, který chcete porovnat s aktivním listem.

Jak to funguje?

Když kliknete na Nové okno, znovu otevře stejný sešit s trochu jiným názvem. Pokud je například název vašeho sešitu „Test“ a kliknete na Nové okno, pojmenuje již otevřený sešit „Test - 1“ a druhá instance jako „Test - 2“.

Všimněte si, že se stále jedná o stejný sešit. Pokud provedete jakékoli změny v kterémkoli z těchto sešitů, projeví se to v obou.

A když zavřete jakoukoli instanci otevřeného souboru, název se vrátí zpět na původní.

Pokud chcete, můžete také povolit synchronní posouvání (kliknutím na možnost „Synchronní posouvání“ na kartě „Zobrazit“)

Porovnejte dva listy a zvýrazněte rozdíly (pomocí podmíněného formátování)

Výše uvedenou metodu můžete použít k zarovnání sešitů a ručnímu procházení dat po řádcích, ale v případě, že máte hodně dat, to není dobrý způsob.

Také ruční provádění této úrovně porovnání může vést k mnoha chybám.

Takže místo toho, abyste to dělali ručně, můžete využít sílu podmíněného formátování k rychlému zvýraznění jakýchkoli rozdílů ve dvou listech Excelu.

Tato metoda je velmi užitečná, pokud máte dvě verze na dvou různých listech a chcete rychle zkontrolovat, co se změnilo.

Všimněte si, že vy NEMŮŽE porovnat dva listy v různých sešitech.

Protože podmíněné formátování nemůže odkazovat na externí soubor aplikace Excel, listy, které potřebujete porovnat, musí být ve stejném sešitu aplikace Excel. Pokud tomu tak není, můžete zkopírovat list z jiného souboru do aktivního sešitu a provést toto srovnání.

V tomto případě předpokládejme, že máte sadu dat, jak je uvedeno níže, na dva měsíce (leden a únor) ve dvou různých listech a chcete rychle porovnat data v těchto dvou listech a zkontrolovat, zda se ceny těchto položek změnily nebo ne.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte data v listu, kde chcete změny zvýraznit. Protože chci zkontrolovat, jak se ceny změnily od ledna do února, vybral jsem data v listu února.
2. Klikněte na kartu Domů
3. Ve skupině Styly klikněte na „Podmíněné formátování“
4. V zobrazených možnostech klikněte na „Nové pravidlo“
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“
6. Do pole vzorce zadejte následující vzorec: = B2Jan! B2
7. Klikněte na tlačítko Formát
8. V zobrazeném dialogovém okně Formát buněk klikněte na kartu „Vyplnit“ a vyberte barvu, ve které chcete zvýraznit neshodující se data.
9. Klikněte na OK
10. Klikněte na OK

Výše uvedené kroky by okamžitě zvýraznily všechny změny v datové sadě v obou listech.

Jak to funguje?

Podmíněné formátování zvýrazní buňku, když daný vzorec pro tuto buňku vrátí hodnotu PRAVDA. V tomto příkladu porovnáváme každou buňku na jednom listu s odpovídající buňkou na druhém listu (provedeno pomocí operátoru nerovná se ve vzorci).

Když podmíněné formátování zjistí jakýkoli rozdíl v datech, zvýrazní to v listu Jan (ten, ve kterém jsme použili podmíněné formátování.

Všimněte si, že jsem v tomto příkladu použil relativní odkaz (A1 a ne $ A $ 1 nebo $ A1 nebo A $ 1).

Při použití této metody k porovnání dvou listů v aplikaci Excel nezapomeňte na následující;

• Tato metoda je vhodná k rychlé identifikaci rozdílů, ale nemůžete ji používat průběžně. Pokud například zadám nový řádek do kterékoli z datových sad (nebo odstraním řádek), dalo by mi to nesprávné výsledky. Jakmile vložím/odstraním řádek, všechny následující řádky budou považovány za odlišné a příslušně zvýrazněny.
• Můžete porovnat pouze dva listy ve stejném souboru aplikace Excel
• Můžete porovnat pouze hodnotu (nikoli rozdíl ve vzorci nebo formátování).

Porovnejte dva soubory/listy aplikace Excel a získejte rozdíly pomocí vzorce

Pokud vás zajímá pouze rychlé srovnání a identifikace rozdílů mezi dvěma listy, můžete pomocí vzorce načíst pouze ty hodnoty, které jsou odlišné.

Pro tuto metodu budete potřebovat samostatný list, kde můžete načíst rozdíly.

Tato metoda by fungovala, pokud chcete porovnat dva samostatné sešity nebo pracovní listy aplikace Excel ve stejném sešitu.

Ukážu vám příklad, kde porovnávám dva soubory dat na dvou listech (ve stejném sešitu).

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, v listu s názvem Jan (a podobná data v listu s názvem Feb) a chcete vědět, jaké hodnoty se liší.

Chcete -li tyto dva listy porovnat, nejprve vložte nový list (nazvěme tento list „Rozdíl“).

Do buňky A1 zadejte následující vzorec:

= IF (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Zkopírujte a vložte tento vzorec pro rozsah tak, aby pokrýval celou datovou sadu v obou listech. Protože mám malou datovou sadu, zkopíruji a vložím pouze tento vzorec v rozsahu A1: B10.

Výše uvedený vzorec používá ke kontrole rozdílů podmínku IF. V případě, že v hodnotách není žádný rozdíl, vrátí se prázdné a v případě rozdílu vrátí hodnoty z obou listů do samostatných řádků ve stejné buňce.

Dobré na této metodě je, že vám poskytne pouze rozdíly a ukáže vám přesně, jaký je rozdíl. V tomto příkladu snadno vidím, že cena v buňce B4 a B8 je odlišná (stejně jako přesné hodnoty v těchto buňkách).

Porovnejte dva soubory/listy aplikace Excel a získejte rozdíly pomocí jazyka VBA

Pokud potřebujete poměrně často porovnávat soubory nebo listy Excelu, je dobré mít připravený kód VBA pro makro aplikace Excel a použít jej, kdykoli potřebujete provést srovnání.

Makro můžete také přidat na panel nástrojů Rychlý přístup, abyste k němu měli přístup jediným tlačítkem a okamžitě věděli, jaké buňky se v různých souborech/listech liší.

Předpokládejme, že máte dva listy v lednu a v únoru a chcete porovnat a zvýraznit rozdíly v listu Jan, můžete použít níže uvedený kód VBA:

Sub CompareSheets () Dim rngCell as Range for each rngCell In Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Cells (rngCell.Row, rngCell.Column) Then rngCell.Interior.Color = vbYellow End Pokud Další rngCell End Sub

Výše uvedený kód používá smyčku For Next k procházení každou buňkou v listu Jan (celý použitý rozsah) a porovnává ji s odpovídající buňkou v listu února. V případě, že najde rozdíl (který je zkontrolován pomocí příkazu If-Then), zvýrazní tyto buňky žlutě.

Tento kód můžete použít v běžném modulu v editoru VB.

A pokud to potřebujete dělat často, je lepší tento kód uložit do sešitu Osobní makro a poté jej přidat na panel nástrojů Rychlý přístup. Tímto způsobem budete moci provést toto srovnání kliknutím na tlačítko.

Zde jsou kroky k získání osobního sešitu maker v aplikaci Excel (ve výchozím nastavení není k dispozici, takže jej musíte povolit).

Zde jsou kroky k uložení tohoto kódu do osobního sešitu maker.

A zde najdete kroky k přidání tohoto kódu makra do QAT.

Použití nástroje třetí strany - XL Comparator

Dalším rychlým způsobem, jak porovnat dva soubory aplikace Excel a zkontrolovat shody a rozdíly, je použití bezplatného nástroje třetí strany, jako je XL Comparator.

Jedná se o webový nástroj, do kterého můžete nahrát dva soubory aplikace Excel, a vytvoří srovnávací soubor, který bude obsahovat data, která jsou společná (nebo různá data podle toho, jakou možnost jste vybrali.

Předpokládejme, že máte dva soubory, které mají datové sady zákazníků (například jméno a e -mailovou adresu), a chcete rychle zkontrolovat, kde jsou zákazníci, soubor 1 a ne soubor 2.

Níže je uvedeno, jak porovnáváte dva soubory aplikace Excel a vytváříte srovnávací zprávu:

1. Otevřete https://www.xlcomparator.net/
2. Pomocí možnosti Vybrat soubor nahrajte dva soubory (maximální velikost každého souboru může být 5 MB)
3. Klikněte na tlačítko Další.
4. V obou těchto souborech vyberte společný sloupec. Nástroj použije tento společný sloupec k vyhledání shod a rozdílů
5. Vyberte jednu ze čtyř možností, ať už chcete získat shodná data nebo jiná data (na základě souboru 1 nebo souboru 2)
6. Klikněte na Další
7. Stáhněte si srovnávací soubor, který bude obsahovat data (podle toho, jakou možnost jste vybrali v kroku 5)

Níže je video, které ukazuje, jak nástroj XL Comparator funguje.

Při používání nástroje jiného výrobce k porovnávání souborů aplikace Excel se můžete obávat o soukromí. Pokud máte důvěrná data a soukromí je pro ně opravdu důležité, je lepší použít jiné metody uvedené výše. Web XL Comparator uvádí, že po 1 hodině porovnávání odstraní všechny soubory.

Toto jsou některé z metod, které můžete použít ke srovnání dvou různých souborů aplikace Excel (nebo listů ve stejném souboru aplikace Excel). Doufám, že jste tento návod k Excelu považovali za užitečný.