• Jak vidíš rozdíl mezi sešity Excelu v datech, v Knize 1 je datum "06.01.2013" a v Knize 2 je datum "07.01.2013".
• Další rozdíl jedná se o pole „zisk produktu“, jehož hodnota je 32325 pro levý list a 23325 pro pravý list.

Pomocí funkce Porovnat Suite Light

Pomocí Compare Suite Light můžete snadno porovnejte dvě stránky z excelu. Zde jsou kroky, které musíte dodržet:

1.Stažení a nainstalujte Compare Suite Light - to je volný, uvolnit program

2.Spusťte ji a z nabídky Soubor vyberte „Porovnat nový soubor“:

3. Nyní otevřeno Excel soubor do MS Excel

4. Klikněte Control+A pro výběr celého textu stránky

5. Klikněte Control+C pro zkopírování vybraného textu do schránky

6. Přejděte na Compare Suite Light, klikněte na prázdné místo na levém panelu a klikněte Control + V pro vložení textu

7.Opakovat kroky 4-6 s jiným excelovým listem musíte porovnat, ale vložit data pravý panel v porovnání Light Suite:

8. Stiskněte tlačítko " Porovnejte": Program zahájí porovnávání a po několika sekundách zvýrazní nalezenérozdíly.

Dva porovnával excelový list vypadat jako:

Omezení metody

Tato jednoduchá metoda funguje, když potřebujete pouze porovnávat několik excelových souborů když budete muset porovnat více souborů, zjistíte, že operace kopírování a vkládání trvá dlouho.

• Možné řešení: použijte PRO verzi Compare Suite.

Výhody verze PRO

• Není třeba kopírovat a vkládat text. Pomůže vám Porovnat Suite PRO otevřít soubory pro porovnání.
• S verzí PRO si můžete vybrat které stránky porovnávat a které sloupce zahrnout do srovnání:

• Výsledky srovnání se zobrazí takto:

• Zobrazí se verze PRO podrobnosti o nalezených rozdílech, Jako procento Změny pro číselné hodnoty a rozdíl ve dnech pro data.

Někdy je nutné porovnat dva soubory MS Excel. Může to být zjištění cenových nesrovnalostí pro určité pozice nebo změna jakýchkoli hodnot, na tom nezáleží, hlavní věcí je najít určité nesrovnalosti.

Nebylo by zbytečné zmínit, že pokud je v souboru MS Excel několik záznamů, nemá smysl uchýlit se k automatizaci. Pokud má soubor několik stovek nebo dokonce tisíce záznamů, pak se bez pomoci výpočetního výkonu počítače neobejdete.

Simulujme situaci, kdy dva soubory mají stejný počet řádků a nesoulad je třeba hledat v určitém sloupci nebo ve více sloupcích. Taková situace je možná, pokud je třeba porovnávat cenu zboží podle dvou ceníků, nebo porovnávat míry sportovců před a po tréninkové sezóně, ačkoli pro takovou automatizaci by jich mělo být hodně .

Jako pracovní příklad si vezměme soubor s výkonem fiktivních účastníků: běh na 100 metrů, běh na 3000 metrů a shyby. První soubor je měření na začátku sezóny a druhý je konec sezóny.

První způsob, jak problém vyřešit. Řešením je pouze použití vzorců MS Excel.

Protože jsou záznamy uspořádány vertikálně (nejlogičtější konstrukce), je nutné použít . V případě použití horizontálního umístění záznamů budete muset použít funkci.

Pro srovnání běhu na 100 m je vzorec následující:
=IF(SVYHLEDAT($B2;List2!$B$2:$F$13;3;PRAVDA)<>D2;D2-VLOOKUP($B2;List2!$B$2:$F$13;3;TRUE);"Žádný rozdíl")
Pokud není žádný rozdíl, zobrazí se zpráva, že není žádný rozdíl, pokud je přítomen, pak se od hodnoty na konci sezóny odečte ukazatel začátku sezóny.

Vzorec pro běh na 3000 metrů je následující:
=IF(VLOOKUP($B2;List2!$B$2:$F$13;4;PRAVDA)<>E2;"Existuje rozdíl";"Není žádný rozdíl")
Pokud se koncové a počáteční hodnoty neshodují, zobrazí se odpovídající zpráva. Vzorec pro přítahy může být podobný jakémukoli z předchozích, nemá smysl ho dávat dodatečně. Konečný soubor s nalezenými nesrovnalostmi je uveden níže.

Malé vysvětlení. Pro usnadnění čitelnosti vzorců byla data ze dvou souborů přesunuta do jednoho (na různé listy), ale to nebylo možné.

Video porovnávající dva soubory MS Excel pomocí funkcí a.

Druhý způsob řešení problému. Řešení pomocí MS Access.

Úlohu lze vyřešit nejprve importem souborů MS Excel do Accessu. Pokud jde o samotný způsob importu externích dat, není zde žádný rozdíl pro hledání různých polí (kterákoli z nabízených možností bude fungovat).

Poslední jmenovaný je propojením souborů Excelu a Accessu, takže při změně dat v souborech Excel budou nesrovnalosti nalezeny automaticky při spuštění dotazu v MS Access.

Dalším krokem po importu je vytvoření vztahů mezi tabulkami. Jako závazné pole vyberte jedinečné pole "Č. p / p".
Třetím krokem je vytvoření jednoduchého výběrového dotazu pomocí nástroje pro tvorbu dotazů.

V prvním sloupci uvádíme, které záznamy je třeba zobrazit, a ve druhém - za jakých podmínek se záznamy zobrazí. U druhého a třetího pole budou akce přirozeně podobné.

Video porovnání souborů MS s Excelem pomocí MS Access.

V důsledku provedených manipulací se zobrazí všechny záznamy s různými údaji v poli: "Běh na 100 metrů." Soubor MS Access je uveden níže (bohužel vám SkyDrive neumožňuje vložení jako soubor aplikace Excel)

Zde jsou dva způsoby, jak najít nesrovnalosti v tabulkách MS Excel. Každá má výhody i nevýhody. Je jasné, že toto není vyčerpávající seznam srovnání dvou souborů Excelu. Čekáme na vaše návrhy v komentářích.

Řekněme, že chcete porovnat verze sešitu, analyzovat sešit kvůli problémům nebo nesrovnalostem nebo zobrazit odkazy mezi sešity nebo listy. Pokud je v počítači nainstalován Microsoft Office 365 nebo Office Professional Plus 2013, je v Excelu k dispozici doplněk Spreadsheet Inquire.

K provedení všech těchto a dalších úkolů můžete použít příkazy na kartě Dotaz. Karta Dotaz na pásu karet Excelu obsahuje tlačítka pro příkazy popsané níže.

Pokud nevidíte Zeptejte se na pásu karet Excel, viz Zapnutí doplňku Spreadsheet Inquire .

Porovnejte dva sešity

The Porovnat soubory umožňuje zobrazit rozdíly, buňku po buňce, mezi dvěma sešity. Chcete-li spustit tento příkaz, musíte mít v aplikaci Excel otevřené dva sešity.

Výsledky jsou barevně odlišeny podle druhu obsahu, jako jsou zadané hodnoty, vzorce, pojmenované rozsahy a formáty. Existuje dokonce okno, které může zobrazovat změny kódu VBA řádek po řádku. Rozdíly mezi buňkami jsou zobrazeny ve snadno čitelném rozložení mřížky, jako je toto:

The Porovnat soubory příkaz používá Microsoft Spreadsheet Compare k porovnání těchto dvou souborů. Ve Windows 8 můžete spustit Spreadsheet Compare mimo Excel kliknutím Porovnání tabulky na Aplikace obrazovka. Ve Windows 7 klikněte na Windows Start a poté > Všechny programy > Microsoft Office 2013 > Nástroje Office 2013 > Srovnání tabulkových procesorů 2013.

Chcete-li se dozvědět více o porovnávání a porovnávání souborů, přečtěte si článek Porovnání dvou verzí sešitu .

Analyzujte sešit

The Analýza sešitu vytvoří interaktivní sestavu zobrazující podrobné informace o sešitu a jeho struktuře, vzorcích, buňkách, rozsahech a varováních. Na obrázku je velmi jednoduchý sešit obsahující dva vzorce a datová propojení s databází Accessu a textovým souborem.

Zobrazit odkazy na sešit

Sešity propojené s jinými sešity prostřednictvím odkazů na buňky mohou být matoucí. Použijte k vytvoření interaktivní, grafické mapy závislostí sešitu vytvořené spojeními (vazbami) mezi soubory. Typy odkazů v diagramu mohou zahrnovat další sešity, databáze Accessu, textové soubory, stránky HTML, databáze SQL Server a další zdroje dat. V diagramu vztahů můžete vybrat prvky a najít o nich další informace a přetažením spojovacích čar změnit tvar diagramu.

Tento diagram ukazuje aktuální sešit vlevo a propojení mezi ním a jinými sešity a zdroji dat. Zobrazuje také další úrovně připojení sešitu, což vám dává obrázek o původu dat pro sešit.

Zobrazit odkazy na pracovní listy

Máte spoustu pracovních listů, které na sobě závisí? Použijte k vytvoření interaktivní, grafické mapy spojení (odkazů) mezi listy jak ve stejném sešitu, tak v jiných sešitech. To vám pomůže získat jasnější obrázek o tom, jak mohou vaše data záviset na buňkách na jiných místech.

Tento diagram ukazuje vztahy mezi listy ve čtyřech různých sešitech se závislostmi mezi listy ve stejném sešitu a také odkazy mezi listy v různých sešitech. Když umístíte ukazatel na uzel v diagramu, jako je list s názvem „Západ“ v diagramu, zobrazí se bublina obsahující informace.

Zobrazit vztahy mezi buňkami

Chcete-li získat podrobný interaktivní diagram všech odkazů z vybrané buňky na buňky v jiných listech nebo dokonce jiných sešitech, použijte Vztah buněk nástroj. Tyto vztahy s jinými buňkami mohou existovat ve vzorcích nebo odkazech na pojmenované oblasti. Diagram může křížit pracovní listy a sešity.

Tento diagram ukazuje dvě úrovně vztahů buněk pro buňku A10 na List5 v Book1.xlsx. Tato buňka je závislá na buňce C6 na listu 1 v jiném sešitu Book2.xlsx. Tato buňka je precedentem pro několik buněk na jiných listech ve stejném souboru.

Chcete-li se dozvědět více o zobrazení vztahů mezi buňkami, přečtěte si článek Viz odkazy mezi buňkami .

Vyčistěte přebytečné formátování buněk

Už jste někdy otevřeli sešit a zjistili, že se načítá pomalu, nebo je obrovský? Může mít formátování aplikované na řádky nebo sloupce, o kterých nevíte. Použijte příkaz Vyčistěte nadměrné formátování buněk příkaz k odstranění nadměrného formátování a výraznému zmenšení velikosti souboru. To vám pomůže vyhnout se „nafouknutí tabulky“, což zvyšuje rychlost aplikace Excel.

Spravujte hesla

Pokud používáte funkce Inquire k analýze nebo porovnání sešitů, které jsou chráněny heslem, budete muset přidat heslo sešitu do svého seznamu hesel, aby Inquire mohl otevřít uloženou kopii vašeho sešitu. Použijte Hesla sešitů příkaz na Zeptejte se přidejte hesla, která se uloží do vašeho počítače. Tato hesla jsou šifrovaná a přístupná pouze vám.

Uživatelé Excelu jsou poměrně často postaveni před úkol porovnat dvě tabulky nebo seznamy, aby v nich identifikovali rozdíly nebo chybějící prvky. Každý uživatel se s tímto úkolem vyrovnává svým vlastním způsobem, ale řešením tohoto problému se nejčastěji stráví poměrně velké množství času, protože ne všechny přístupy k tomuto problému jsou racionální. Zároveň existuje několik osvědčených akčních algoritmů, které vám umožní porovnávat seznamy nebo pole tabulek v poměrně krátkém čase s minimálním úsilím. Podívejme se na tyto možnosti podrobně.

Existuje několik způsobů, jak porovnávat tabulkové prostory v Excelu, ale všechny lze rozdělit do tří velkých skupin:

porovnání seznamů umístěných na stejném listu;

porovnání tabulek umístěných na různých listech;

porovnání rozsahů tabulek v různých souborech.

Na základě této klasifikace jsou v první řadě vybírány srovnávací metody a také určeny konkrétní akce a algoritmy pro provádění úlohy. Například při porovnávání v různých sešitech musíte otevřít dva soubory aplikace Excel současně.

Navíc je třeba říci, že srovnávat tabulkové prostory má smysl, až když mají podobnou strukturu.

Metoda 1: jednoduchý vzorec

Nejjednodušší způsob, jak porovnat data ve dvou tabulkách, je použít jednoduchý vzorec rovnosti. Pokud se data shodují, vrátí hodnotu TRUE, a pokud ne, pak FALSE. Porovnávat můžete jak číselná data, tak i textová. Nevýhodou této metody je, že ji lze použít pouze v případě, že jsou data v tabulce seřazena nebo seřazena stejným způsobem, synchronizována a má stejný počet řádků. Podívejme se, jak tuto metodu použít v praxi na příkladu dvou tabulek umístěných na stejném listu.

Máme tedy dvě jednoduché tabulky se seznamy zaměstnanců podniku a jejich platů. Musíte porovnat seznamy zaměstnanců a identifikovat nesrovnalosti mezi sloupci, ve kterých jsou umístěna příjmení.

1. K tomu potřebujeme další sloupec na listu. Dej tam cedulku «=» . Poté klikneme na první porovnávanou položku v prvním seznamu. Znovu vložíme symbol «=» z klávesnice. Dále klikněte na první buňku sloupce, který porovnáváme ve druhé tabulce. Výsledkem je výraz následujícího typu:

   

   I když se samozřejmě v každém konkrétním případě budou souřadnice lišit, ale podstata zůstane stejná.

2. Klikneme na klíč Vstupte získat výsledky srovnání. Jak vidíte, při porovnávání prvních buněk obou seznamů program ukázal indikátor "SKUTEČNÝ", což znamená, že data se shodují.



3. Nyní musíme provést podobnou operaci se zbytkem buněk obou tabulek ve sloupcích, které porovnáváme. Vzorec ale můžete jednoduše zkopírovat, což ušetří spoustu času. Tento faktor je zvláště důležitý při porovnávání seznamů s velkým počtem řetězců.

   Postup kopírování lze nejsnáze provést pomocí rukojeti plnění. Najeďte myší na pravý dolní roh buňky, kde jsme získali indikátor "SKUTEČNÝ". Zároveň by se měl proměnit v černý kříž. Toto je značka výplně. Stiskněte levé tlačítko myši a přetáhněte kurzor dolů o počet řádků v porovnávaných polích tabulky.



4. Jak vidíte, další sloupec nyní zobrazuje všechny výsledky porovnávání dat ve dvou sloupcích polí tabulky. V našem případě se neshodovaly údaje pouze na jednom řádku. Při jejich porovnání vzorec poskytl výsledek "LEŽÍCÍ". Pro všechny ostatní řádky, jak vidíme, srovnávací vzorec poskytl ukazatel "SKUTEČNÝ".



5. Kromě toho je možné vypočítat počet neshod pomocí speciálního vzorce. Chcete-li to provést, vyberte prvek listu, kde se zobrazí. Poté klikněte na ikonu "Vložit funkci".



6. V okně Funkční průvodci ve skupině operátorů "Matematický" zvýrazněte jméno SUMPRODUCT. Kliknutím na tlačítko OK.



7. Aktivuje se okno argumentů funkce SUMPRODUCT, jehož hlavním úkolem je vypočítat součet součinů zvoleného sortimentu. Ale tato funkce může být použita pro naše účely. Jeho syntaxe je poměrně jednoduchá:

   SUMPRODUCT(pole1, pole2,…)

   Celkem lze jako argumenty použít adresy až 255 polí. Ale v našem případě použijeme pouze dvě pole, navíc jako jeden argument.

   Umístěte kurzor do pole "Pole1" a vyberte na listu porovnávaný rozsah dat v první oblasti. Poté dáme na pole ceduli "není stejný" (<> ) a vyberte porovnávaný rozsah druhého regionu. Následně výsledný výraz zabalíme do závorek, před které dáme dvě znaménka «-» . V našem případě jsme dostali následující výraz:

   ---(A2:A7<>D2:D7)

   Kliknutím na tlačítko OK.



8. Operátor provede výpočet a zobrazí výsledek. Jak vidíte, v našem případě je výsledek roven číslu "jeden", to znamená, že v porovnávaných seznamech byla nalezena jedna neshoda. Pokud by byly seznamy zcela totožné, pak by se výsledek rovnal číslu «0» .

Stejným způsobem můžete porovnávat data v tabulkách, které jsou umístěny na různých listech. Ale v tomto případě je žádoucí, aby řádky v nich byly očíslovány. Jinak je postup porovnávání téměř stejný jako výše, až na to, že při zadávání vzorce budete muset přepínat mezi listy. V našem případě bude výraz vypadat takto:

B2=List2!B2

To znamená, jak vidíme, před souřadnicemi dat, které jsou umístěny na jiných listech, odlišných od toho, kde je zobrazen výsledek porovnání, je uvedeno číslo listu a vykřičník.

Metoda 2: Výběr skupin buněk

Porovnání lze provést pomocí nástroje pro výběr skupin buněk. Může také porovnávat pouze synchronizované a uspořádané seznamy. Kromě toho by v tomto případě měly být seznamy umístěny vedle sebe na stejném listu.

Metoda 3: Podmíněné formátování

Můžete porovnávat pomocí podmíněného formátování. Stejně jako v předchozí metodě musí být porovnávané oblasti na stejném listu aplikace Excel a musí být vzájemně synchronizovány.

Existuje další způsob, jak použít podmíněné formátování ke splnění daného úkolu. Stejně jako předchozí možnosti vyžaduje umístění obou porovnávaných oblastí na stejném listu, ale na rozdíl od dříve popsaných metod nebude podmínka synchronizace dat nebo třídění povinná, což tuto možnost odlišuje od dříve popsaných.

Pokud chcete, můžete naopak obarvit neodpovídající prvky a ponechat ty indikátory, které se shodují, se stejnou barevnou výplní. Algoritmus akcí je zároveň téměř stejný, ale v okně nastavení pro zvýraznění duplicitních hodnot v prvním poli namísto parametru "Opakující se" musíte vybrat možnost "Unikátní". Poté klikněte na tlačítko OK.

Ty indikátory, které se neshodují, budou tedy zvýrazněny.

Metoda 4: komplexní vzorec

Data můžete také porovnávat pomocí složitého vzorce založeného na funkci COUNTIF. Pomocí tohoto nástroje můžete vypočítat, kolik se každý prvek z vybraného sloupce druhé tabulky opakuje v prvním.

Operátor COUNTIF odkazuje na statistickou skupinu funkcí. Jeho úkolem je spočítat počet buněk, jejichž hodnoty splňují danou podmínku. Syntaxe tohoto operátoru je následující:

COUNTIF(rozsah; kritéria)

Argument "Rozsah" je adresa pole, ve kterém se počítají odpovídající hodnoty.

Argument "Kritérium" určuje podmínku shody. V našem případě bude představovat souřadnice konkrétních buněk v první oblasti tabulky.

Tento výraz pro srovnání tabulkových ukazatelů lze samozřejmě použít v aktuální podobě, ale existuje možnost jej vylepšit.

Udělejme to tak, že hodnoty, které jsou ve druhé tabulce, ale ne v první, se zobrazí v samostatném seznamu.

1. Nejprve si trochu přepracujme náš vzorec. COUNTIF, totiž udělejme z toho jeden z argumentů operátora LI. Chcete-li to provést, vyberte první buňku, ve které se operátor nachází COUNTIF. Do řádku vzorců před něj přidejte výraz "LI" bez uvozovek a otevřete závorku. Dále, abychom si usnadnili práci, vybereme hodnotu v řádku vzorců "LI" a klikněte na ikonu "Vložit funkci".



2. Otevře se okno Argumenty funkce LI. Jak vidíte, první pole okna je již vyplněno hodnotou operátoru COUNTIF. K tomuto poli ale musíme přidat ještě něco. Nastavíme tam kurzor a přidáme k již existujícímu výrazu «=0» bez uvozovek.

   Poté jdeme na hřiště "hodnota, pokud je to pravda". Zde použijeme další vnořenou funkci - ČÁRA. Zadáváme slovo "ČÁRA" bez uvozovek, poté otevřete závorky a uveďte souřadnice první buňky s příjmením ve druhé tabulce, poté závorky uzavřeme. Konkrétně v našem případě v terénu "hodnota, pokud je to pravda" dostal následující výraz:

   STRING(D2)

   Nyní operátor ČÁRA bude hlásit funkce LIčíslo řádku, ve kterém se konkrétní příjmení nachází, a v případě, že je splněna podmínka uvedená v prvním poli, funkce LI zobrazí toto číslo v buňce. Klikněte na tlačítko OK.



3. Jak vidíte, první výsledek se zobrazí jako "LEŽÍCÍ". To znamená, že hodnota nesplňuje podmínky provozovatele LI. To znamená, že první příjmení je přítomno v obou seznamech.



4. Pomocí značky výplně zkopírujeme operátorový výraz obvyklým způsobem LI pro celý sloupec. Jak vidíte, pro dvě pozice, které jsou přítomny ve druhé tabulce, ale ne v první, vzorec udává čísla řádků.



5. Ustoupíme z oblasti stolu doprava a vyplníme sloupec čísly v pořadí, počínaje od 1 . Počet čísel se musí shodovat s počtem řádků v druhé porovnávané tabulce. Pro urychlení procesu číslování můžete také použít úchyt výplně.



6. Poté vyberte první buňku napravo od sloupce s čísly a klikněte na ikonu "Vložit funkci".



7. otevře Průvodce funkcí. Přejít do kategorie "Statistický" a vyberte jméno "NEJMÉNĚ". Kliknutím na tlačítko OK.



8. Funkce NEJMÉNĚ, jehož okno argumentů bylo rozšířeno, je navrženo tak, aby zobrazovalo nejmenší hodnotu určenou skóre.

   V terénu "Pole" musíte zadat souřadnice rozsahu dalšího sloupce "Počet náhod", který jsme dříve převedli pomocí funkce LI. Udělejte ze všech odkazů absolutní.

   V terénu "K" označuje, která je nejmenší zobrazená hodnota. Zde uvádíme souřadnice první buňky číslovaného sloupce, který jsme nedávno přidali. Nechte adresu relativní. Kliknutím na tlačítko OK.



9. Operátor zobrazí výsledek - číslo 3 . Je to nejmenší z číslování neodpovídajících řádků tabulkových polí. Pomocí úchytu výplně zkopírujte vzorec úplně dolů.



10. Nyní, když známe čísla řádků neodpovídajících prvků, můžeme vložit do buňky a jejich hodnoty pomocí funkce INDEX. Vyberte první prvek listu obsahující vzorec NEJMÉNĚ. Poté přejděte na řádek vzorců a před název "NEJMÉNĚ" přidat jméno "INDEX" bez uvozovek, okamžitě otevřete závorku a vložte středník ( ; ). Poté vyberte název v řádku vzorců "INDEX" a klikněte na ikonu "Vložit funkci".



11. Poté se otevře malé okno, ve kterém musíte určit, zda má mít funkce referenční typ INDEX nebo navržený pro práci s poli. Potřebujeme druhou možnost. Ve výchozím nastavení je nainstalován, takže v tomto okně stačí kliknout na tlačítko OK.



12. Otevře se okno argumentů funkce INDEX. Tento operátor je navržen pro výstup hodnoty, která se nachází v určitém poli na určeném řádku.

    Jak vidíte, pole "číslo řádku" již naplněna funkčními hodnotami NEJMÉNĚ. Od hodnoty, která tam již existuje, by se měl odečíst rozdíl mezi číslováním listu Excel a vnitřním číslováním oblasti tabulky. Jak vidíte, nad tabulkovými hodnotami máme pouze záhlaví. To znamená, že rozdíl je jeden řádek. Proto přidáváme v poli "číslo řádku" význam "-jeden" bez uvozovek.

    V terénu "Pole" zadejte adresu rozsahu hodnot druhé tabulky. Zároveň všechny souřadnice učiníme absolutními, to znamená, že před ně postavíme znak dolaru způsobem, který jsme dříve popsali.

    Klikněte na tlačítko OK.



13. Po zobrazení výsledku na obrazovce roztáhneme funkci pomocí úchytu výplně na konec sloupce dolů. Jak vidíte, obě příjmení, která jsou přítomna ve druhé tabulce, ale ne v první, jsou zobrazena v samostatném rozsahu.

Metoda 5: Porovnání polí v různých sešitech

Při porovnávání rozsahů v sešitech můžete použít výše uvedené metody, s výjimkou těch, které vyžadují, aby oba tabulkové prostory byly na stejném listu. Hlavní podmínkou pro postup porovnání je v tomto případě otevření oken obou souborů současně. U verzí Excelu 2013 a novějších a verzí před Excelem 2007 není s touto podmínkou žádný problém. V Excelu 2007 a Excelu 2010 však otevírání obou oken současně vyžaduje další manipulace. Jak to udělat, je popsáno v samostatné lekci.

Jak vidíte, existuje řada možností, jak tabulky mezi sebou porovnávat. Kterou možnost použít, závisí na tom, kde přesně jsou tabulková data relativně vůči sobě umístěna (na stejném listu, v různých knihách, na různých listech), a také na tom, jak přesně si uživatel přeje, aby se toto srovnání zobrazovalo.

Dobrý den!

Tento článek je věnován řešení takové otázky, jak porovnat dvě tabulky v Excelu nebo alespoň dva sloupce. Ano, pracovat s tabulkami je pohodlné a dobré, ale když je potřebujete porovnat, je to vizuálně docela obtížné. Možná tabulku až tuctu nebo dvou můžete vizuálně seřadit, ale když překročí tisíce, budete potřebovat další analytické nástroje.

Bohužel neexistuje kouzelná hůlka, se kterou se vše udělá na jeden klik a informace se ověří, je potřeba připravit data, napsat vzorce a další postupy, které vám umožní porovnávat tabulky.

Zvažte několik možností a možností pro porovnávání tabulek v Excelu:

Snadný způsob

Toto je nejjednodušší a nejzákladnější způsob porovnání dvou tabulek. Tímto způsobem je možné porovnávat jak číselné, tak textové hodnoty. Porovnejme například dva rozsahy číselných hodnot, stačí do další buňky napsat vzorec pro jejich rovnost =C2=E2 v důsledku toho, pokud jsou buňky stejné, dostaneme odpověď "SKUTEČNÝ“, a pokud nebudou žádné shody, budou "LEŽÍCÍ". Nyní jej pomocí jednoduchého automatického kopírování zkopírujeme do našeho vzorce, který nám umožňuje porovnat dva sloupce v Excelu a vidět rozdíl.

Rychle zvýrazněte hodnoty, které se liší

Není to také příliš zatěžující způsob. Pokud potřebujete pouze najít a ujistit se, že mezi tabulkami existují, dobře nebo žádné rozdíly, musíte na kartě „Domů“ vybrat tlačítko nabídky „Najít a vybrat“ po výběru rozsahu, ve kterém chcete porovnat dvě tabulky. tabulky v Excelu. V nabídce, která se otevře, vyberte položku "Vyberte skupinu buněk..." a v zobrazeném dialogu vyberte "rozdíly v řádcích".

Porovnejte dvě tabulky v Excelu pomocí podmíněného formátování

Velmi dobrý způsob, na kterém můžete zvýrazněnou barvou vidět hodnotu, která se liší při porovnání dvou tabulek. Přihlásit se můžete na záložce "Domov" stisknutím tlačítka "Podmíněné formátování" a vyberte z poskytnutého seznamu "Správa pravidel".
V dialogovém okně "Správce pravidel podmíněného formátování", zmáčknout tlačítko "Vytvořit pravidlo" a v novém dialogovém okně "Vytvoření pravidla formátování", zvolte pravidlo . V terénu "Upravit popis pravidla" zadejte vzorec = $ C2<>$E2 definujte buňku, která se má formátovat, a stiskněte tlačítko "Formát".
Definujeme styl, jak bude naše hodnota, která odpovídá kritériu, formátována.
Nyní se naše nově vytvořené pravidlo objevilo v seznamu pravidel, vyberete jej, kliknete "OK".

A celé pravidlo bylo aplikováno na náš sortiment, kde se snažíme ověřit podobnost mezi dvěma tabulkami a rozdíly se ukázaly, na které bylo aplikováno podmíněné formátování.

Jak porovnat dvě tabulky v Excelu pomocí funkce a pravidel COUNTIF

Všechny výše uvedené metody jsou dobré pro uspořádané tabulky, ale když data nejsou uspořádaná, jsou zapotřebí jiné metody, z nichž jednu nyní zvážíme. Představte si například, že máme 2 tabulky, jejichž hodnoty se mírně liší, a potřebujeme tyto tabulky porovnat, abychom určili hodnotu, která se liší. Vyberte hodnotu v rozsahu první tabulky a na kartě "Domov", položka menu "Podmíněné formátování" a klikněte na položku v seznamu "Vytvořit pravidlo...", vyberte pravidlo "Použijte vzorec k určení, které buňky se mají formátovat", zadejte vzorec = ($C$1:$C$7;C1)=0 a vyberte formát podmíněného formátování.

Vzorec zkontroluje hodnotu z konkrétní buňky C1 a porovná ji se zadaným rozsahem $C$1:$C$7 z druhého sloupce. Pravidlo zkopírujeme do celého rozsahu, ve kterém porovnáváme tabulky a získáme hodnoty zvýrazněné v buňkách, které se neopakují.

Jak porovnat dvě tabulky v Excelu pomocí funkce VLOOKUP

V této variantě použijeme , což nám umožní porovnejte dvě tabulky na zápasy. Chcete-li porovnat dva sloupce, zadejte vzorec =VLOOKUP(C2;$D$2:$D$7;1;0) a zkopírujte jej přes celý porovnávaný rozsah. Tento vzorec postupně začne kontrolovat, zda se ve sloupci B opakuje hodnota ze sloupce A, a podle toho vrátí hodnotu prvku, pokud tam byl nalezen, pokud hodnota nalezena nebyla, dostaneme .

Jak porovnat dvě tabulky v Excelu IF funkce

Tato možnost zahrnuje použití logické jedničky a rozdíl této metody je v tom, že k porovnání dvou sloupců nebude použit celý sloupec, ale pouze ta jeho část, která je pro porovnání potřebná.

Porovnejme například dva sloupce A a B na listu, do sousedního sloupce C zadáme vzorec: =IF( (SHODA(C2,$E$2:$E$7,0)),"";C2) a zkopírujte jej do celku. Tento vzorec vám umožňuje postupně zjistit, zda jsou určité prvky ze zadaného sloupce A ve sloupci B, a vrátit hodnotu, pokud byla nalezena ve sloupci B.

Porovnejte dvě tabulky pomocí makra VBA

Existuje mnoho způsobů, jak zkontrolovat podobnost dvou tabulek. , ale některé možnosti jsou možné pouze s makry VBA. Makra pro porovnání dvou tabulek , sjednocuje tento proces a výrazně snižuje čas strávený přípravou dat. Na základě vámi řešeného úkolu a znalosti maker VBA můžete vytvářet libovolné varianty maker. Níže jsem uvedl techniku ​​uvedenou na oficiální stránce společnosti Microsoft. Musíte vytvořit modul pro kód VBA a zadat kód:

Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant " Nastavit proměnnou CompareRange na rozsah, který se má porovnat Set CompareRange = Range("B1:B11") "Pokud je rozsah, který se má porovnat, na jiném listu nebo sešit, "použijte následující syntaxi" Set CompareRange = Workbooks("Book2"). _ " Worksheets("Sheet2").Range("B1:B11") " " Porovnejte každou položku ve vybraném rozsahu s každou položkou v CompareRange For Each x In Selection For Each y In CompareRange If x = y Then x.Offset ( 0, 2) = x Další y Další x Konec Sub

Sub Find_Matches() Dim CompareRange As Variant , x As Variant , y As Variant " Nastavení proměnné CompareRange na porovnávaný rozsah Nastavit CompareRange = Range("B1:B11") " Pokud je porovnávaný rozsah na jiném listu nebo sešitu, " použijte následující syntaxi " Set CompareRange = Workbooks("Book2") ._ Kombinace horkých kláves Alt+F8. V novém dialogovém okně vyberte své makro Najít_podobné a provést to. Srovnánípomocí doplňku Inquire Tato možnost srovnání byla k dispozici s vydáním verze Excelu 2013, dodal editor doplněk Zeptejte se, který vám umožní analyzovat a porovnat dva soubory Excel. Tato metoda je dobrá, když potřebujete porovnat dva soubory, v případě, že váš kolega pracoval na knize a provedl nějaké změny. Zde je nástroj, který potřebujete k určení těchto změn Sešit Porovnej v doplňku Inquire. Zde jsme se podívali na 8 způsobů, jak porovnat dvě tabulky v Excelu, tyto možnosti vám pomohou vyřešit vaše analytické problémy a zjednoduší vaši práci. Rád jsem vám pomohl! Zisk je poplatek, který dostanete za to, že můžete využít změny.