Metódy na porovnávanie tabuliek v programe Microsoft Excel

Pomerne často používatelia programu Excel majú úlohu porovnávať dve tabuľky alebo zoznamy na identifikáciu rozdielov alebo chýbajúcich prvkov v nich. Každý používateľ sa touto úlohou vyrovná svojou vlastnou cestou, ale častejšie sa na riešenie tohto problému venuje značné množstvo času, pretože nie všetky prístupy k tomuto problému sú racionálne. Súčasne existuje niekoľko validovaných akčných algoritmov, ktoré vám umožnia porovnať zoznamy alebo tabuľkové polia v relatívne krátkom čase s minimálnym úsilím. Poďme sa bližšie pozrieť na tieto možnosti.

Prečítajte si tiež: Porovnanie dvoch dokumentov v programe MS Word

Metódy porovnania

Existuje pomerne málo spôsobov, ako porovnať tabuľkové oblasti v programe Excel, ale môžu byť rozdelené do troch veľkých skupín:

Na základe tejto klasifikácie sú najprv vybrané metódy porovnania a určujú sa špecifické akcie a algoritmy na vykonanie úlohy. Napríklad pri porovnávaní v rôznych knihách musíte súčasne otvoriť dva súbory programu Excel.

Navyše je potrebné povedať, že porovnávanie tabuľkových polí má zmysel iba vtedy, ak majú podobnú štruktúru.

Metóda 1: Jednoduchý vzorec

Najjednoduchší spôsob porovnávania údajov v dvoch tabuľkách je použiť jednoduchý vzorec rovnosti. Ak sa dáta zhodujú, uvedie sa to TRUE indikátor, a ak nie, potom - FALSE. Môžete porovnať číselné údaje aj text. Nevýhodou tejto metódy je, že sa dá použiť iba vtedy, ak sú údaje v tabuľke usporiadané alebo triedené identicky, synchronizované a majú rovnaký počet riadkov. Pozrime sa, ako túto metódu použiť v praxi príkladom dvoch tabuliek umiestnených na jednom hárku.

Takže máme dve jednoduché tabuľky so zoznamom zamestnancov podniku a ich platy. Je potrebné porovnať zoznamy zamestnancov a identifikovať nezrovnalosti medzi stĺpcami, v ktorých sú mená umiestnené.

1. Na to potrebujeme ďalší stĺpec. Zadáme znak "=" tam. Potom kliknite na prvé meno, ktoré chcete porovnať v prvom zozname. Opäť vložte z klávesnice symbol "=" . Potom kliknite na prvú bunku stĺpca, ktorú porovnávame v druhej tabuľke. Bola získaná expresia nasledujúceho typu:

   =A2=D2

   Hoci samozrejme v každom konkrétnom prípade súradnice budú odlišné, ale podstata zostane rovnaká.

2. Kliknutím na tlačidlo Enter získate výsledky porovnania. Ako vidíte, pri porovnaní prvých buniek oboch zoznamov program indikoval indikátor "TRUE" , čo znamená, že údaje sa zhodujú.
3. Teraz musíme vykonať podobnú operáciu so zostávajúcimi bunkami oboch tabuliek v stĺpcoch, ktoré porovnávame. Ale môžete jednoducho skopírovať vzorec, čo ušetrí veľa času. Zvlášť tento faktor je dôležitý pri porovnávaní zoznamov s veľkým počtom riadkov.

   Najjednoduchší spôsob kopírovania je pomocou rukoväte na výplň. Presuneme kurzor do pravého dolného rohu bunky, kde sa zobrazí indikátor "TRUE" . V tomto prípade musí byť premenený na čierny kríž. Toto je značka plnenia. Kliknite na ľavé tlačidlo myši a potiahnite kurzor nadol počet riadkov v porovnávaných tabuľkových poliach.

4. Ako vidíte, teraz v dodatočnom stĺpci boli zobrazené všetky výsledky porovnávania údajov v dvoch stĺpcoch tabuľkových polí. V našom prípade sa údaje nezhodovali len s jedným riadkom. Pri ich porovnávaní vzorec priniesol výsledok "LIE" . Pre všetky ostatné linky, ako vidíme, porovnávací vzorec bol vydaný indikátorom "TRUE" .
5. Navyše je možné vypočítať počet nesúladov pomocou špeciálneho vzorca. Ak to chcete urobiť, vyberte prvok hárka, na ktorom bude výstup. Potom kliknite na ikonu "Vložiť funkciu" .
6. V okne Sprievodca funkcií v okne "Matematická" skupina operátorov vyberte názov SUMPRODUCT . Klikneme na tlačidlo "OK" .
7. Zobrazí sa okno s argumentom funkcie SUMPRODUCT , ktorej hlavnou úlohou je vypočítať súčet produktov vybraného rozsahu. Táto funkcia však môže byť použitá pre naše účely. Syntax je celkom jednoduchý:

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

   Celkovo možno použiť ako argumenty až 255 polí. Ale v našom prípade budeme navyše používať iba dve polia ako jeden argument.

   Kurzor sme umiestnili do poľa "Array1" a na prvej strane vybrali porovnaný rozsah údajov. Potom vložte do poľa znak "nie je rovnaký" ( <> ) a zvoľte porovnaný rozsah druhej oblasti. Potom výsledný výraz zabalte do zátvoriek, pred ktorými položíme dva znaky "-" . V našom prípade bol výraz:

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

   Klikneme na tlačidlo "OK" .

8. Operátor vypočíta a zobrazí výsledok. Ako vidíte, v našom prípade sa výsledok rovná číslu "1" , to znamená, že v porovnávaných zoznamoch sa našiel jeden nesúlad. Ak by zoznamy boli úplne identické, výsledok by sa rovnal počtu "0" .

Rovnakým spôsobom môžete porovnať údaje v tabuľkách, ktoré sú na rôznych hárkoch. Ale v tomto prípade je žiaduce, aby čiarky v nich boli očíslované. V opačnom prípade je porovnávací postup takmer úplne rovnaký, ako je opísaný vyššie, s výnimkou skutočnosti, že pri zadávaní vzorca musíte prepínať medzi listami. V našom prípade výraz bude vyzerať takto:

=B2=Лист2!B2

To znamená, ako vidíte, pred súradnicami údajov, ktoré sa nachádzajú na iných listoch, odlišné od toho, kde je zobrazený výsledok porovnania, sú uvedené číslo listu a vyklenutý bod.

Metóda 2: Výber skupín buniek

Porovnanie môžete vykonať pomocou nástroja na výber skupiny buniek. S ním môžete porovnávať aj len synchronizované a usporiadané zoznamy. Okrem toho by v tomto prípade mali byť listy umiestnené vedľa seba na jednom hárku.

1. Vyberieme porovnávané polia. Prejdite na kartu Domov . Potom kliknite na ikonu "Nájsť a vybrať " , ktorá je umiestnená na páse krabice v poli "Upraviť" . Otvorí sa zoznam, v ktorom vyberiete položku "Výber skupiny buniek ..." .

   Okrem toho môžete tiež otvoriť okno výberu pre skupinu buniek iným spôsobom. Táto možnosť bude zvlášť užitočná pre používateľov, ktorí majú verziu programu skôr ako Excel 2007, pretože tieto metódy nepodporujú tlačidlom "Nájsť a zvýrazniť" . Vyberte pole, ktoré chceme porovnať a stlačte kláves F5 .

2. Je aktivované malé okno prechodu. Klikneme na tlačidlo "Vybrať ..." v ľavom dolnom rohu.
3. Potom vyberiete možnosť výberu skupín buniek. Nastavte prepínač na pozíciu "Vybrať podľa riadkov" . Kliknite na tlačidlo "OK" .
4. Ako vidíte, potom sa nezhoda hodnôt riadkov zvýrazní iným odtieňom. Okrem toho, ako sa dá posúdiť z obsahu riadkovej rovnice, program urobí aktívnu jednu z buniek aktívnych v uvedených nespárovaných riadkoch.

Metóda 3: podmienené formátovanie

Porovnanie môžete vykonať použitím metódy podmieneného formátovania. Rovnako ako v predchádzajúcej metóde, porovnávané oblasti musia byť na rovnakom pracovnom hárku programu Excel a musia byť synchronizované navzájom.

1. Najprv sme si vybrali, ktorú oblasť tabuľky budeme považovať za hlavnú a ako hľadať rozdiely. Urobme to v druhej tabuľke. Preto si vyberieme zoznam zamestnancov, ktorí sú v ňom. Po prechode na kartu "Domov" klikneme na tlačidlo "Podmienené formátovanie" , ktoré má umiestnenie na páse v bloku "Štýly" . V rozbaľovacom zozname prejdite na položku Spravovať pravidlá .
2. Okno správcov pravidiel je aktivované. Kliknite na tlačidlo "Vytvoriť pravidlo" v ňom.
3. V okne, ktoré sa otvorí, si vyberieme položku "Použiť vzorec" . V poli "Formát buniek" zapíšeme vzorec obsahujúci adresy prvých buniek rozsahov porovnávaných stĺpcov, oddelených znakom "nie je rovné" ( <> ). Len pred týmto výrazom bude znamenie "=" . Okrem toho všetky súradnice stĺpcov v tomto vzorci musíte použiť absolútne adresovanie. Ak to chcete urobiť, vyberte vzorec kurzorom a trikrát stlačte kláves F4 . Ako môžete vidieť, okolo všetkých stĺpových adries bol znak dolára, čo znamená prepnutie liniek na absolútne. V našom konkrétnom prípade má vzorec nasledujúcu formu:

   =$A2<>$D2

   Napíšeme tento výraz do vyššie uvedeného poľa. Potom kliknite na tlačidlo "Formát ..." .

4. Okno "Formát buniek" je aktivované. Prejdite na kartu "Vyplniť" . Tu v zozname farieb zastavíme výber farby, ktorú chceme namaľovať tie prvky, kde sa dáta nezhodujú. Kliknite na tlačidlo "OK" .
5. Ak sa vrátite do okna pre vytvorenie pravidla formátovania, kliknite na tlačidlo "OK" .
6. Po automatickom prechode do okna "Správca pravidiel" kliknite na tlačidlo "OK" av jeho okne.
7. Teraz v druhej tabuľke sa prvky, ktoré majú údaje, ktoré nezodpovedajú zodpovedajúcim hodnotám prvej tabuľky, zvýraznia zvolenou farbou.

Existuje iný spôsob, ako môžete na úlohu aplikovať podmienené formátovanie. Rovnako ako predchádzajúce verzie vyžaduje umiestnenie obidvoch porovnaných oblastí na jednom hárku, ale na rozdiel od skôr opísaných metód nebude podmienka synchronizácie alebo triedenia údajov povinná, čo rozlišuje túto voľbu od tých, ktoré boli predtým opísané.

1. Robíme výber oblastí, ktoré je potrebné porovnať.
2. Prejdeme na kartu s názvom Domov . Klikneme na tlačidlo "Podmienené formátovanie" . V aktivovanom zozname vyberte položku "Pravidlá výberu buniek" . V ďalšej ponuke si vyberieme pozíciu "Duplicitné hodnoty" .
3. Otvorí sa okno pre nastavenie výberu duplicitných hodnôt. Ak ste urobili všetko správne, potom v tomto okne ostáva len kliknúť na tlačidlo "OK" . Aj keď je to žiaduce, môžete vybrať inú farbu zvýraznenia v príslušnom poli tohto okna.
4. Po vykonaní určenej akcie sa všetky opakujúce sa prvky zvýraznia zvolenou farbou. Tie prvky, ktoré sa nezhodujú, zostanú sfarbené v pôvodnej farbe (predvolené je biela). Môžete tak okamžite vidieť, aký je rozdiel medzi poliami.

Ak je to žiaduce, môžete naopak maľovať nezodpovedané prvky a tie indikátory, ktoré sa zhodujú, nechajte s rovnakým odtieňom farieb. V tomto prípade je algoritmus akcií takmer rovnaký, ale v okne nastavenia pre výber duplicitných hodnôt v prvom poli namiesto parametra "Duplikát" vyberte možnosť "Jedinečné" . Potom kliknite na tlačidlo "OK" .

Teda tie indikátory, ktoré sa nezhodujú, budú zvýraznené.

Lekcia: Podmienené formátovanie v programe Excel

Metóda 4: Zložitý vzorec

Tiež môžete porovnávať údaje pomocou zložitého vzorca založeného na funkcii COUNTIF . Pomocou tohto nástroja môžete počítať, koľko sa každý prvok z vybraného stĺpca druhej tabuľky zopakuje v prvom.

Operátor COUNTRY odkazuje na štatistickú skupinu funkcií. Jeho úlohou je počítať počet buniek, ktorých hodnoty spĺňajú dané podmienky. Syntax tohto operátora je:

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

Argument "Rozsah" je adresa poľa, v ktorom sú vypočítané zodpovedajúce hodnoty.

Argument kritéria nastavuje podmienku zápasu. V našom prípade predstavujú súradnice špecifických buniek prvej plochy tabuľky.

1. Vyberte prvý prvok ďalšieho stĺpca, v ktorom sa bude počítať počet zhody. Potom kliknite na ikonu "Vložiť funkciu" .
2. Spustí sa Sprievodca funkciami. Pojdme sa do kategórie "Štatistické údaje" . V zozname nájdeme názov "RADA" . Po jeho výbere kliknite na tlačidlo "OK" .
3. Objaví sa argumentácia operátora Rady . Ako vidíte, názvy polí v tomto okne zodpovedajú názvom argumentov.

   Nastavte kurzor do poľa "Rozsah" . Potom držte ľavé tlačidlo myši a vyberte všetky hodnoty stĺpca s názvami druhej tabuľky. Ako vidíte, súradnice okamžite spadajú do zadaného poľa. Ale pre naše účely je nevyhnutné urobiť túto adresu absolútnou. Ak to chcete urobiť, vyberte súradnice v poli a stlačte F4 .

   Ako vidíte, odkaz nadobudol absolútnu podobu, ktorá je charakterizovaná prítomnosťou znakov dolára.

   Potom prejdite na pole "Kritérium" a nastavte kurzor tam. Klikneme na prvý prvok s názvami v prvom rozsahu tabuľky. V tomto prípade ponecháme referenčnú relatívu. Po zobrazení v poli môžete kliknúť na tlačidlo "OK" .

4. Výsledok sa zobrazí v prvku listu. To sa rovná číslu "1" . Znamená to, že v zozname mien druhej tabuľky sa raz vyskytuje priezvisko "Grinev VP" , ktoré je prvé v zozname prvého tabuľkového poľa.
5. Teraz musíme vytvoriť podobný výraz pre všetky ostatné prvky prvej tabuľky. Ak to chcete urobiť, vykonajte kopírovanie pomocou značky naplnenia, ako sme urobili predtým. Vložte kurzor do spodnej pravej časti listového prvku, ktorý obsahuje funkciu COUNTIF a po jeho premene na značku naplnenia stlačíme ľavé tlačidlo myši a presuňte kurzor nadol.
6. Ako je vidieť, program vykonal výpočet koincidencií porovnaním každej bunky prvej tabuľky s údajmi, ktoré sú umiestnené v druhom rozsahu tabuľky. Vo štyroch prípadoch bol výsledok "1" av dvoch prípadoch "0" . To znamená, že program nemohol nájsť v druhej tabuľke dve hodnoty, ktoré sú k dispozícii v prvom poli tabuľky.

Samozrejme, že tento výraz na porovnanie tabuľkových ukazovateľov, môžete použiť v existujúcej forme, ale existuje možnosť zlepšiť.

Hodnoty, ktoré sú k dispozícii v druhej tabuľke, ale nie v prvej tabuľke, sa zobrazia v samostatnom zozname.

1. Najskôr prepracujeme náš vzorec pre COUNTRY , trochu to urobíme ako jeden z argumentov prevádzkovateľa IF . Ak to chcete urobiť, vyberte prvú bunku, v ktorej sa nachádza operátor COUNTRY . V riadku vzorcov pred ním pridávame výraz "IF" bez úvodzoviek a otvoríme držiak. Ďalej, aby sme nám uľahčili prácu, v riadku vzorca sme zvolili hodnotu "IF" a klikneme na ikonu "Vložiť funkciu" .
2. Objaví sa okno argumentu funkcie IF . Ako vidíte, prvé pole okna je už obsadené hodnotou operátora Rady . Ale musíme dokončiť niečo iné v tejto oblasti. Nastavíme tam kurzor a pridáme "= 0" bez úvodzoviek k už existujúcemu výrazu.

   Potom prejdite na pole "Hodnota, ak je pravda . " Tu budeme používať ešte jednu vnorenú funkciu - STRING . Zadáme slovo "LINE" bez úvodzoviek, potom otvoríme zátvorky a zadáme súradnice prvej bunky s posledným menom v druhej tabuľke a zatvoríme zátvorky. Konkrétne v našom prípade v poli "Hodnota, ak je pravda" bol získaný nasledujúci výraz:

   СТРОКА(D2)

   Teraz príkaz LINE oznámi funkciu, ak je číslo riadku, v ktorom sa nachádza konkrétne priezvisko, a ak je splnená podmienka uvedená v prvom poli, funkcia IF vykáže toto číslo do bunky. Kliknite na tlačidlo "OK" .

3. Ako vidíte, prvý výsledok sa zobrazí ako "FALSE" . To znamená, že hodnota nespĺňa podmienky prevádzkovateľa IF . To znamená, že v oboch zoznamoch je prvé priezvisko.
4. Použitím značky naplnenia kopírujeme výraz operátora IF na celý stĺpec obvyklým spôsobom. Ako vidíte, pre dve položky, ktoré sú prítomné v druhej tabuľke, ale nie v prvej, vzorec udáva čísla riadkov.
5. Odstúpime z tabuľky vpravo a vyplníme stĺpec s číslami v poradí od 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Otvorí sa Sprievodca funkciami. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Klikneme na tlačidlo "OK" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

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

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

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

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

    Kliknite na tlačidlo "OK" .

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

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

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

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

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

• ← SQL dotazov v programe Microsoft Excel
• Práca s prepojenými tabuľkami v programe Microsoft Excel →