Poměrně často uživatelé aplikace Excel mají za úkol porovnat dvě tabulky nebo seznamy, aby identifikovali rozdíly nebo chybějící prvky v nich. Každý uživatel se s tímto úkolem vyrovnává svým vlastním způsobem, ale častěji se na řešení tohoto problému věnuje značné množství času, protože ne všechny přístupy k tomuto problému jsou racionální. Současně existuje několik validovaných algoritmů akce, které vám umožní porovnat seznamy nebo tabulkové sestavy v poměrně krátké době s minimálním úsilím. Podívejme se blíže na tyto možnosti.

Čtěte také: Srovnání dvou dokumentů v MS Word

Metody srovnání

Existuje poměrně málo způsobů, jak porovnat tabulkové oblasti v aplikaci Excel, ale lze je rozdělit do tří velkých skupin:

  • srovnávací seznamy na jednom listu;
  • Srovnání tabulek umístěných na různých listech;
  • Srovnání tabulkových rozsahů v různých souborech.
  • Na základě této klasifikace je na prvním místě vybrána metoda porovnání a určena je i specifická akce a algoritmy pro provedení úkolu. Například při porovnávání v různých knihách musíte současně otevřít dva soubory aplikace Excel.

    Dále je třeba říci, že porovnávání tabulek má smysl pouze tehdy, když mají podobnou strukturu.

    Metoda 1: Jednoduchý vzorec

    Nejjednodušší způsob, jak porovnávat data ve dvou tabulkách, je použít jednoduchý vzorec rovnosti. Pokud se data shodují, udává TRUE indikátor, a pokud ne, pak - FALSE. Můžete porovnat jak číselná data, tak text. Nevýhodou této metody je, že může být použita pouze tehdy, jsou-li údaje v tabulce seřazeny nebo seřazeny shodně, synchronizovány a mají stejný počet řádků. Podívejme se, jak tuto metodu použít v praxi pomocí příkladu dvou tabulek umístěných na jednom listu.

    Takže máme dvě jednoduché tabulky se seznamy zaměstnanců podniku a jejich platy. Je třeba porovnat seznamy zaměstnanců a identifikovat nesrovnalosti mezi sloupci, ve kterých jsou umístěna jména.

    Porovnatelné tabulky v aplikaci Microsoft Excel

    1. K tomu potřebujeme další sloupec na listu. Zadejte znamení "=" tam. Poté klikněte na první jméno, které chcete porovnat v prvním seznamu. Opět vložte symbol "=" z klávesnice. Dále klikněte na první buňku sloupce, který porovnáváme ve druhé tabulce. Byl získán výraz následujícího typu:

      =A2=D2

      Vzorec pro porovnávání buněk v aplikaci Microsoft Excel

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

    2. Klepnutím na tlačítko Enter získáte výsledky porovnání. Jak je vidět, při porovnávání prvních buněk obou seznamů indikoval program indikátor "TRUE" , což znamená, že se data shodují.
    3. Výsledek uložení prvního řádku dvou tabulek v aplikaci Microsoft Excel

    4. Teď musíme provést podobnou operaci se zbývajícími buňkami obou tabulek ve sloupcích, které srovnáváme. Ale můžete pouze zkopírovat vzorec, který ušetří spoustu času. Zvláště tento faktor je důležitý při porovnávání seznamů s velkým počtem řádků.

      Nejjednodušší způsob, jak provést kopírování, je použití rukojeti výplně. Přesuňme kurzor do pravého dolního rohu buňky, kde máme ukazatel TRUE . V tomto případě je třeba jej převést na černý kříž. Toto je značka naplnění. Klepněte na levé tlačítko myši a přetáhněte kurzor směrem dolů počet řádků v porovnávaných tabulkových polích.

    5. Filler v aplikaci Microsoft Excel

    6. Jak vidíte, nyní v dalším sloupci byly zobrazeny všechny výsledky porovnání dat ve dvou sloupcích tabulek. V našem případě data neodpovídala pouze jednomu řádku. Při jejich porovnávání vzorec získal výsledek "LIE" . Pro všechny ostatní řádky, jak vidíme, porovnávací vzorec byl vydán indikátorem "TRUE" .
    7. Výpočet výsledku pro celý sloupec v aplikaci Microsoft Excel

    8. Navíc je možné vypočítat počet nesouladů pomocí speciálního vzorce. Chcete-li to provést, vyberte prvek listu, na kterém bude výstup. Potom klikněte na ikonu "Vložit funkci" .
    9. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

    10. V okně Průvodce funkcí , v okně "Matematická" skupina operátorů, vyberte název SUMPRODUCT . Klepněte na tlačítko "OK" .
    11. Přepnutí do okna argumentů funkce SUMPRODUCT v aplikaci Microsoft Excel

    12. Je aktivováno okno argumentu funkce SUMPRODUCT , jehož hlavním úkolem je vypočítat součet produktů vybraného rozsahu. Ale tato funkce může být použita pro naše účely. Syntaxe je poměrně jednoduchá:

      =СУММПРОИЗВ(массив1;массив2;…)

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

      Kurzor jsme umístili do pole "Array1" a v první oblasti vybrali srovnávaný rozsah dat. Poté vložte do pole značku "není rovno" ( <> ) a vyberte srovnávaný rozsah druhé oblasti. Pak obalte výsledný výraz s závorkami, před kterými položíme dva znaky "-" . V našem případě byl výraz:

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

      Klepněte na tlačítko "OK" .

    13. Okno argumentů funkce SUMPRODUCT v aplikaci Microsoft Excel

    14. Operátor vypočítá a zobrazí výsledek. Jak vidíte, v našem případě se výsledek rovná číslu "1" , to znamená, že v porovnávacích seznamech bylo nalezeno jedno nesoulad. Pokud by seznamy byly zcela totožné, výsledek by se rovnal počtu "0" .

    Výsledek výpočtu funkce SUMPRODUCT v aplikaci Microsoft Excel

    Stejným způsobem můžete porovnávat data v tabulkách, které jsou na různých listech. V tomto případě je však žádoucí, aby čárky v nich byly očíslovány. V opačném případě je porovnávací postup téměř přesně stejný, jak je popsáno výše, s výjimkou skutečnosti, že při zadávání vzorce musíte přepínat mezi listy. V našem případě bude vypadat takto:

    =B2=Лист2!B2

    Srovnání tabulek na různých listech v aplikaci Microsoft Excel

    To znamená, jak vidíte, před datovými souřadnicemi, které se nacházejí na jiných listech, odlišné od místa, kde je zobrazen výsledek porovnání, jsou označeny číslo listu a vykřičník.

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

    Pomocí nástroje pro výběr skupin buněk můžete provést srovnání. S ním můžete také porovnávat pouze synchronizované a objednané seznamy. Kromě toho by v tomto případě měly být seznamy umístěny vedle sebe na jednom listu.

    1. Vybíráme porovnávané pole. Přejděte na kartu "Domov" . Potom klikněte na ikonu "Najít a vybrat " , která je umístěna na pásu karet v poli "Upravit" . Otevře se seznam, ve kterém vyberete položku "Výběr skupiny buněk ..." .

      Přejděte do okna výběru skupiny buněk v aplikaci Microsoft Excel

      Kromě toho můžete také přistupovat k výběrovému okně pro skupinu buněk jiným způsobem. Tato volba bude užitečná zejména pro ty uživatele, kteří mají verzi programu dříve než aplikace Excel 2007, protože tyto metody nepodporují použití tlačítka "Najít a zvýraznit" . Vyberte pole, která chceme porovnat, a stiskněte klávesu F5 .

    2. Je aktivováno malé přechodové okno. Klepněte na tlačítko "Vybrat ..." v levém dolním rohu.
    3. Přechodové okno v aplikaci Microsoft Excel

    4. Poté, podle libovolné z výše uvedených možností, spustí se okno pro výběr skupin buněk. Nastavte přepínač na pozici "Vybrat podle řádků" . Klikněte na tlačítko "OK" .
    5. Okno výběru skupin buněk v aplikaci Microsoft Excel

    6. Jak můžete vidět, pak budou nesouladné hodnoty řádků zvýrazněny jiným odstínem. Kromě toho, jak lze posoudit z obsahu řádku vzoru, program provede jednu z buněk aktivních v označených nesourodých řádcích aktivní.

    Nevyhovující údaje v aplikaci Microsoft Excel

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

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

    1. Nejprve si vybereme, která oblast tabulky budeme považovat za hlavní a jak hledat rozdíly. Udělme to druhé v druhé tabulce. Proto vybereme seznam zaměstnanců, kteří jsou v něm. Po přesunu na kartu "Domov" klikneme na tlačítko "Podmíněné formátování" , které je umístěno na pásu v bloku "Styly" . V rozevíracím seznamu přejděte na položku Spravovat pravidla .
    2. Přejděte do okna podmíněných formátovacích pravidel v aplikaci Microsoft Excel

    3. Okno Správce pravidel je aktivováno. Klikněte na tlačítko Vytvořit pravidlo .
    4. Podmíněný správce formátovacích pravidel v aplikaci Microsoft Excel

    5. V okně, které se otevře, vybereme položku "Použít vzorec" . V poli "Formát buňky" zapíšeme vzorec obsahující adresy prvních buněk v rozmezích porovnávaných sloupců, oddělených znakem "není rovno" ( <> ). Teprve před tímto výrazem bude znamení "=" . Navíc ke všem souřadnicím sloupců v tomto vzorci musíte použít absolutní adresování. Chcete-li to provést, vyberte vzorec pomocí kurzoru a třikrát stiskněte klávesu F4 . Jak můžete vidět, kolem všech sloupových adres bylo dolarové znamení, což znamená, že tyto vazby jsou absolutní. V našem konkrétním případě má vzorec následující formu:

      =$A2<>$D2

      Tento výraz zapíšeme do výše uvedeného pole. Poté klikněte na tlačítko "Formát ..." .

    6. Přejděte do okna výběru formátu v aplikaci Microsoft Excel

    7. Okno "Formát buňky" je aktivováno. Přejděte na záložku "Vyplnit" . Zde v seznamu barev zastavíme výběr na barvě, kterou chceme namalovat ty prvky, kde se data nebudou shodovat. Klikněte na tlačítko "OK" .
    8. Vyberte barvu výplně v okně Formát buňky v aplikaci Microsoft Excel

    9. Vraťte se do okna pro vytvoření pravidla pro formátování, klikněte na tlačítko "OK" .
    10. Okno pro vytvoření pravidla formátování v aplikaci Microsoft Excel

    11. Po automatickém přesunu do okna "Správce pravidel" klikněte na tlačítko "OK" .
    12. Použití pravidla v Správci pravidel v aplikaci Microsoft Excel

    13. Nyní ve druhé tabulce budou prvky, které mají data, která neodpovídají odpovídajícím hodnotám první plochy tabulky, zvýrazněna zvolenou barvou.

    Nevyhovující data jsou označena podmíněným formátováním v aplikaci Microsoft Excel

    Existuje i jiný způsob, jak lze na úlohu použít podmíněné formátování. Stejně jako u předchozích verzí vyžaduje umístění obou srovnávaných oblastí na jednom listu, ale na rozdíl od dříve popsaných metod nebude podmínka synchronizace nebo třídění dat povinná, což rozlišuje tuto volbu od dříve popsaných.

    1. Vybíráme výběr oblastí, které je třeba porovnávat.
    2. Výběr porovnávaných tabulek v aplikaci Microsoft Excel

    3. Přejdeme na kartu s názvem "Domov" . Klepněte na tlačítko "Podmíněné formátování" . V aktivovaném seznamu vyberte položku "Pravidla výběru buňky" . V další nabídce si vyberete pozici "Duplicitní hodnoty" .
    4. Přechod na podmíněné formátování v aplikaci Microsoft Excel

    5. Otevře se okno pro nastavení výběru duplicitních hodnot. Pokud jste vše udělali správně, pak v tomto okně zůstane pouze klepnutím na tlačítko "OK" . I když je to požadováno, můžete v odpovídajícím poli tohoto okna zvolit jinou barvu zvýraznění.
    6. Okno pro nastavení výběru duplicitních hodnot v aplikaci Microsoft Excel

    7. Po provedení určité akce se všechny opakující se prvky zvýrazní vybranou barvou. Ty prvky, které neodpovídají, zůstanou zbarveny v původní barvě (výchozí je bílá). Takže můžete vizuálně vidět, jaký je rozdíl mezi poli.

    Duplicitní hodnoty jsou zvýrazněny v aplikaci Microsoft Excel

    Pokud je to požadováno, naopak můžete namalovat nesouladné prvky a ty indikátory, které se shodují, opouštějí stejnou barvu. V tomto případě je algoritmus akcí téměř stejný, ale v okně nastavení pro výběr duplicitních hodnot v prvním poli namísto parametru "Duplikát" vyberte možnost "Jedinečný" . Poté klikněte na tlačítko "OK" .

    Nastavení výběru jedinečných hodnot v aplikaci Microsoft Excel

    Takto se zvýrazní ukazatele, které se neshodují.

    Jedinečné hodnoty jsou zvýrazněny v aplikaci Microsoft Excel

    Lekce: Podmíněné formátování v aplikaci Excel

    Metoda 4: Složitý vzorec

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

    Operátor ZEMĚ odkazuje na statistickou skupinu funkcí. Jeho úkolem je počítat počet buněk, jejichž hodnoty splňují daný stav. Syntaxe tohoto operátora je:

    =СЧЁТЕСЛИ(диапазон;критерий)

    Argument "Rozsah" je adresa pole, ve kterém jsou vypočítané odpovídající hodnoty.

    Argument kritéria nastavuje podmínku zápasu. V našem případě představují souřadnice specifických buněk oblasti první tabulky.

    1. Vyberte první prvek dalšího sloupce, ve kterém bude počítán počet shody. Potom klikněte na ikonu "Vložit funkci" .
    2. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

    3. Spustí se Průvodce funkcí . Pojďme do kategorie "Statistické" . V seznamu se nachází název "RADA" . Po jeho výběru klikněte na tlačítko "OK" .
    4. Přejděte do okna argumentů funkce COUNTIF v aplikaci Microsoft Excel

    5. Zobrazí se okno argumentů operátora Rady. Jak vidíte, názvy polí v tomto okně odpovídají názvům argumentů.

      Nastavte kurzor do pole "Rozsah" . Poté přidržením levého tlačítka myši vyberte všechny hodnoty sloupce s názvy druhé tabulky. Jak vidíte, souřadnice se okamžitě dostanou do zadaného pole. Pro naše účely je však tato adresa absolutní. Chcete-li to provést, vyberte souřadnice v poli a stiskněte klávesu F4 .

      Jak vidíte, spojka měla absolutní podobu, která je charakterizována přítomností znaků dolaru.

      Poté přejděte na pole "Kritérium" a nastavte kurzor tam. Klepněte na první prvek s názvy v prvním rozsahu tabulky. V tomto případě ponecháme referenční relativní. Po zobrazení v poli můžete kliknout na tlačítko "OK" .

    6. Okno argumentu funkce COUNTIF v aplikaci Microsoft Excel

    7. Výsledek se zobrazí v elementu listu. To se rovná číslu "1" . To znamená, že v seznamu názvů druhé tabulky se vyskytuje příjmení "Grinev VP" , které je první v seznamu prvního tabulkového pole.
    8. Výsledek výpočtu funkce COUNTIF v aplikaci Microsoft Excel

    9. Nyní musíme vytvořit podobný výraz pro všechny ostatní prvky první tabulky. Chcete-li to provést, proveďte kopírování pomocí značky pro vyplnění, jak jsme učinili dříve. Umístěte kurzor do pravé dolní části listového prvku, který obsahuje funkci COUNTIF a po jeho převedení na značku naplnění zatlačíme levým tlačítkem myši a přetáhněte kurzor dolů.
    10. Filler v aplikaci Microsoft Excel

    11. Jak je vidět, program provedl výpočet koincidencí porovnáním každé buňky první tabulky s daty, která jsou umístěna v druhém rozsahu tabulky. Ve čtyřech případech byl výsledek "1" a ve dvou případech "0" . To znamená, že program nemohl najít ve druhé tabulce dvě hodnoty, které jsou k dispozici v prvním tabulkovém poli.

    Výsledek výpočtu sloupce funkcí COUNTIF v aplikaci Microsoft Excel

    Samozřejmě, že tento výraz pro porovnání tabulkových ukazatelů, můžete použít ve stávající podobě, ale existuje možnost jej zlepšit.

    Hodnoty, které jsou k dispozici v druhé tabulce, ale nikoliv v první tabulce, se zobrazí v samostatném seznamu.

    1. Především budeme přepracovat náš vzorec pro COUNTRY , trochu to učiníme jedním z argumentů operátora IF . Chcete-li to provést, vyberte první buňku, ve které je umístěn operátor ZEMĚ . V řádku vzorců před tím přidáme výraz "IF" bez uvozovek a otevřete konzolu. Dále, abychom usnadnili práci, zvolíme v řádku vzorce hodnotu "IF" a klikneme na ikonu "Vložit funkci" .
    2. Přejděte do okna argumentů funkce IF v aplikaci Microsoft Excel

    3. Otevře se okno argumentu funkce IF . Jak vidíte, první pole okna je již obsazeno hodnotou operátora Rady. Ale musíme dokončit něco jiného v této oblasti. Kurzor tam nastavíme a do již existujícího výrazu přidáme "= 0" bez uvozovek.

      Poté přejděte na pole Hodnota, pokud je pravda . Zde budeme používat jednu další vnořenou funkci - STRING . Zadáváme slovo "LINE" bez uvozovek, pak otevřete závorky a ve druhé tabulce určíte souřadnice první buňky s posledním jménem a pak zavřete závorky. Konkrétně v našem případě v poli "Hodnota, pokud je pravda" byl získán následující výraz:

      СТРОКА(D2)

      Nyní příkaz LINE nahlásí funkci, pokud je číslo řádku, ve kterém je konkrétní příjmení umístěno, a pokud je splněna podmínka uvedená v prvním poli, funkce IF vytiskne toto číslo do buňky. Klikněte na tlačítko "OK" .

    4. Okno funkcí argumenty IF v aplikaci Microsoft Excel

    5. Jak vidíte, první výsledek se zobrazí jako "FALSE" . To znamená, že hodnota nesplňuje podmínky provozovatele IF . To znamená, že první příjmení je v obou seznamech.
    6. Hodnota vzorce FALSE IF v aplikaci Microsoft Excel

    7. Pomocí značky naplnění zkopírujeme výraz operátora IF na celý sloupec obvyklým způsobem. Jak vidíte, u dvou položek, které se nacházejí ve druhé tabulce, ale nikoli v první tabulce, vzorec udává čísla řádků.
    8. Čísla řádků v aplikaci Microsoft Excel

    9. Odstoupíme z tabulky směrem doprava a vyplníme sloupec čísly v pořadí od 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
    10. Нумерация строк в Microsoft Excel

    11. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
    12. Vložit funkci v aplikaci Microsoft Excel

    13. Otevře se Průvodce funkcí . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Klepněte na tlačítko "OK" .
    14. Переход в окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

    15. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

      В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

      В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Klepněte na tlačítko "OK" .

    16. Окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

    17. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
    18. Результат расчета функции НАИМЕНЬШИЙ в Microsoft Excel

    19. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой ( ; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
    20. Переход в окно аргументов функции ИНДЕКС в Microsoft Excel

    21. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
    22. Окошко выбора вида функции ИНДЕКС в Microsoft Excel

    23. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

      Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

      В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

      Klikněte na tlačítko "OK" .

    24. Окно аргументов функции ИНДЕКС в Microsoft Excel

    25. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.

    Фамилии выведены с помощью функции ИНДЕКС в Microsoft Excel

    Способ 5: сравнение массивов в разных книгах

    При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

    Сравнение таблиц в двух книгах в Microsoft Excel

    Lekce: Как открыть Эксель в разных окнах

    Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.