Методи порівняння таблиць в Microsoft Excel

Досить часто перед користувачами Excel стоїть завдання порівняння двох таблиць або списків для виявлення в них відмінностей або відсутніх елементів. Кожен користувач справляється з цим завданням за своїм, але частіше за все на вирішення зазначеного питання витрачається досить багато часу, так як далеко не всі підходи до даної проблеми є раціональними. У той же час, існує кілька перевірених алгоритмів дій, які дозволять порівняти списки або табличні масиви в досить стислі терміни з мінімальною затратою зусиль. Давайте детально розглянемо ці види.

Читайте також: Порівняння двох документів в MS Word

способи порівняння

Існує досить багато способів порівняння табличних областей в Excel, але всі їх можна розділити на три великі групи:

Саме виходячи з цієї класифікації, перш за все, підбираються методи порівняння, а також визначаються конкретні дії і алгоритми для виконання завдання. Наприклад, при проведенні порівняння в різних книгах потрібно одночасно відкрити два файли Excel.

Крім того, слід сказати, що порівнювати табличні області має сенс тільки тоді, коли вони мають схожу структуру.

Спосіб 1: проста формула

Найпростіший спосіб порівняння даних у двох таблицях - це використання простої формули рівності. Якщо дані збігаються, то вона видає показник ІСТИНА, а якщо немає, то - БРЕХНЯ. Порівнювати можна, як числові дані, так і текстові. Недолік даного методу полягає в тому, що ним можна користуватися тільки в тому випадку, якщо дані в таблиці впорядковані або відсортовані однаково, синхронізовані і мають рівну кількість рядків. Давайте подивимося, як використовувати даний спосіб на практиці на прикладі двох таблиць, розміщених на одному аркуші.

Отже, маємо дві прості таблиці зі списками працівників підприємства і їх окладами. Потрібно порівняти списки співробітників і виявити невідповідності між стовпцями, в яких розміщені прізвища.

1. Для цього нам знадобиться додатковий стовпець на аркуші. Вписуємо туди знак «=». Потім натискаємо на першу найменуванню, яке потрібно порівняти в першому списку. Знову ставимо символ «=» з клавіатури. Далі натискаємо по першій клітинці колонки, яку ми порівнюємо, у другій таблиці. Вийшло вираз наступного типу:

   =A2=D2

   Хоча, звичайно, в кожному конкретному випадку координати будуть відрізнятися, але суть залишиться однаковою.

2. Клацаємо по клавіші Enter, щоб отримати результати порівняння. Як бачимо, при порівнянні перших осередків обох списків програма вказала показник «ІСТИНА», що означає збіг даних.
3. Тепер нам потрібно провести аналогічну операцію і з іншими осередками обох таблиць в тих колонках, які ми порівнюємо. Але можна просто провести копіювання формули, що дозволить істотно заощадити час. Особливо даний фактор важливий при порівнянні списків з великою кількістю рядків.

   Процедуру копіювання найлегше виконати за допомогою маркера заповнення. Наводимо курсор на правий нижній кут комірки, де ми отримали показник «ІСТИНА». При цьому він повинен перетворитися в чорний хрестик. Це і є маркер заповнення. Тиснемо ліву кнопку миші і тягнемо курсор вниз на кількість рядків у порівнюваних табличних масивах.

4. Як бачимо, тепер в додатковому стовпці відобразилися всі результати порівняння даних у двох колонках табличних масивів. У нашому випадку не співпали дані тільки в одному рядку. При їх порівнянні формула видала результат «БРЕХНЯ». За всіма іншими рядками, як бачимо, формула порівняння видала показник «ІСТИНА».
5. Крім того, існує можливість за допомогою спеціальної формули підрахувати кількість розбіжностей. Для цього виділяємо той елемент листа, куди воно буде виводитися. Потім клацаємо по значку «Вставити функцію».
6. У вікні Майстра функцій в групі операторів «Математичні» виділяємо найменування СУММПРОИЗВ. Клацаємо по кнопці «OK».
7. Активується вікно аргументів функції СУММПРОИЗВ, головним завданням якої є обчислення суми творів виділеного діапазону. Але цю функцію можна використовувати і для наших цілей. Синтаксис у неї досить простий:

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

   Всього в якості аргументів можна використовувати адреси до 255 масивів. Але в нашому випадку ми будемо використовувати всього два масиви, до того ж, як один аргумент.

   Ставимо курсор в поле «массів1» і виділяємо на аркуші порівнюваний діапазон даних в першій області. Після цього в поле ставимо знак «не дорівнює» (<>) і виділяємо порівнюваний діапазон другий області. Далі обворачіваем отриманий вираз дужками, перед якими ставимо два знака «-». У нашому випадку вийшло такий вислів:

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

   Клацаємо по кнопці «OK».

8. Оператор проводить розрахунок і виводить результат. Як бачимо, в нашому випадку результат дорівнює числу «1», тобто, це означає, що в порівнюваних списках було знайдено одне розбіжність. Якби списки були повністю ідентичними, то результат був би дорівнює числу «0».

Таким же чином можна проводити порівняння даних в таблицях, які розташовані на різних аркушах. Але в цьому випадку бажано, щоб рядки в них були пронумеровані. В іншому процедура порівняння практично точно така, як була описана вище, крім того факту, що при внесенні формули доведеться перемикатися між листами. У нашому випадку вираз матиме такий вигляд:

=B2=Лист2!B2

Тобто, як бачимо, перед координатами даних, які розташовані на інших аркушах, відмінних від того, де виводиться результат порівняння, вказується номер листа і знак оклику.

Спосіб 2: виділення груп осередків

Порівняння можна зробити за допомогою інструменту виділення груп осередків. З його допомогою також можна порівнювати тільки синхронізовані і впорядковані списки. Крім того, в цьому випадку списки повинні розташовуватися поруч один з одним на одному аркуші.

1. Виділяємо порівнювані масиви. Переходимо у вкладку «Головна». Далі клацаємо по значку «Знайти і виділити», який розташовується на стрічці в блоці інструментів «Редагування». Відкривається список, в якому слід вибрати позицію «Виділення групи осередків ...».

   Крім того, в потрібне нам вікно виділення групи осередків можна потрапити і в інший спосіб. Даний варіант особливо буде корисний тим користувачам, у яких встановлена версія програми раніше Excel 2007, так як метод через кнопку «Знайти і виділити» ці програми не підтримують. Виділяємо масиви, які бажаємо порівняти, і тиснемо на клавішу F5.

2. Активується невелике віконце переходу. Клацаємо по кнопці «Виділити ...» в його нижньому лівому кутку.
3. Після цього, який би з двох перерахованих вище варіантів ви не обрали, запускається вікно виділення груп осередків. Встановлюємо перемикач в позицію «Виділити по рядках». Тиснемо на кнопку «OK».
4. Як бачимо, після цього неспівпадаючі значення рядків будуть підсвічені відрізняється відтінком. Крім того, як можна судити з вмісту рядка формул, програма зробить активної одну з комірок, що знаходиться в зазначених не співпало рядках.

Спосіб 3: умовне форматування

Провести порівняння можна, застосувавши метод умовного форматування. Як і в попередньому способі, порівнювані області повинні знаходитися на одному робочому аркуші Excel і бути синхронізованими між собою.

1. Перш за все, вибираємо, яку табличну область будемо вважати основною, а в який шукати відмінності. Останнє давайте будемо робити у другій таблиці. Тому виділяємо список працівників, що знаходиться в ній. Перемістившись на вкладку «Головна», клацаємо по кнопці «Умовне форматування», яка має місце розташування на стрічці в блоці «Стилі». У списку переходимо по пункту «Управління правилами».
2. Активується віконце диспетчера правил. Тиснемо в ньому на кнопку «Створити правило».
3. У запустити вікні виробляємо вибір позиції «Використовувати формулу». В поле «Форматувати осередки» записуємо формулу, яка містить адреси перших осередків діапазонів порівнюваних стовпців, розділені знаком «не дорівнює» (<>). Тільки перед цим виразом на цей раз буде стояти знак «=». Крім того, до всіх до координат стовпців в цій формулі потрібно застосувати абсолютну адресацію. Для цього виділяємо формулу курсором і тричі тиснемо на клавішу F4. Як бачимо, за будь-яких адрес стовпців з'явився знак долара, що і означає перетворення посилань в абсолютні. Для нашого конкретного випадку формула прийме наступний вигляд:

   =$A2<>$D2

   Цей вираз ми і записуємо в вищевказане поле. Після цього клацаємо по кнопці «Формат ...».

4. Активується вікно «Формат ячеек». Йдемо у вкладку «Заливка». Тут в переліку квітів зупиняємо вибір на кольорі, яким хочемо фарбувати ті елементи, де дані не будуть збігатися. Тиснемо на кнопку «OK».
5. Повернувшись у вікно створення правила форматування, тиснемо на кнопку «OK».
6. Після автоматичного переміщення в вікно «Диспетчера правил» клацаємо по кнопці «OK» і в ньому.
7. Тепер у другій таблиці елементи, які мають дані, неспівпадаючі з відповідними значеннями першої табличній області, будуть виділені вибраним кольором.

Існує ще один спосіб застосування умовного форматування для виконання поставленого завдання. Як і попередні варіанти, він вимагає розташування обох порівнюваних областей на одному аркуші, але на відміну від раніше описаних способів, умова синхронізації або сортування даних не буде обов'язковим, що вигідно відрізняє даний варіант від раніше описаних.

1. Виробляємо виділення областей, які потрібно порівняти.
2. Виконуємо перехід у вкладку під назвою «Головна». Робимо клацання по кнопці «Умовне форматування». В активувати списку вибираємо позицію «Правила виділення осередків». У наступному меню робимо вибір позиції «повторюються значення».
3. Запускається вікно настройки виділення повторюваних значень. Якщо ви все зробили правильно, то в даному вікні залишається тільки натиснути на кнопку «OK». Хоча при бажанні в відповідному полі даного віконця можна вибрати інший колір виділення.
4. Після того, як ми проведемо вказане дію, все повторювані елементи будуть виділені вибраним кольором. Ті елементи, які не збігаються, залишаться пофарбованими в свій початковий колір (за замовчуванням білий). Таким чином, можна відразу візуально побачити, в чому відмінність між масивами.

При бажанні можна, навпаки, пофарбувати неспівпадаючі елементи, а ті показники, які збігаються, залишити з заливкою колишнім кольором. При цьому алгоритм дій практично той же, але в вікні налаштування виділення повторюваних значень в першому полі замість параметра «повторюються» слід вибрати параметр «Унікальні». Після цього натиснути на кнопку «OK».

Таким чином, будуть виділені саме ті показники, які не збігаються.

урок: Умовне форматування в Ексель

Спосіб 4: комплексна формула

Також порівняти дані можна за допомогою складної формули, основою якої є функція СЧЁТЕСЛІ. За допомогою даного інструменту можна зробити підрахунок того, скільки кожен елемент з вибраного стовпця другий таблиці повторюється в першій.

Оператор СЧЁТЕСЛІ відноситься до статистичної групі функцій. Його завданням є підрахунок кількості осередків, значення в яких задовольняють заданій умові. Синтаксис цього оператора має такий вигляд:

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

Аргумент «Діапазон» є адреса масиву, в якому проводиться підрахунок співпадаючих значень.

Аргумент «Критерій» задає умову збігу. У нашому випадку він буде являти собою координати конкретних осередків першої табличній області.

1. Виділяємо перший елемент додаткового стовпця, в якому буде проводитися підрахунок кількості збігів. Далі клацаємо по піктограмі «Вставити функцію».
2. Відбувається запуск Майстра функцій. Переходимо в категорію «Статистичні». Знаходимо в переліку найменування «СЧЁТЕСЛІ». Після його виділення клацаємо по кнопці «OK».
3. Відбувається запуск вікна аргументів оператора СЧЁТЕСЛІ. Як бачимо, найменування полів в цьому вікні співпадають з назвами аргументів.

   Встановлюємо курсор в поле «Діапазон». Після цього, затиснувши ліву кнопку миші, виділяємо все значення стовпця з прізвищами другий таблиці. Як бачимо, координати тут же потрапляють в вказане поле. Але для наших цілей слід зробити цю адресу абсолютним. Для цього виділяємо дані координати в поле і тиснемо на клавішу F4.

   Як бачимо, посилання прийняла абсолютну форму, що характеризується наявністю знаків долара.

   Потім переходимо до поля «Критерій», встановивши туди курсор. Клацаємо по першому елементу з прізвищами в першому табличному діапазоні. В даному випадку залишаємо посилання відносної. Після того, як вона була відображена в поле, можна клацати по кнопці «OK».

4. В елемент листа виводиться результат. Він дорівнює числу «1». Це означає, що в переліку імен другий таблиці прізвище «Гриньов В. П.», яка є першою в списку першого табличного масиву, зустрічається один раз.
5. Тепер нам потрібно створити подібний вираз і для всіх інших елементів першої таблиці. Для цього виконаємо копіювання, скориставшись маркером заповнення, як це ми вже робили раніше. Ставимо курсор в нижню праву частину елемента листа, який містить функцію СЧЁТЕСЛІ, і після перетворення його в маркер заповнення затискаємо ліву кнопку миші і тягнемо курсор вниз.
6. Як бачимо, програма справила обчислення збігів, порівнявши кожну клітинку першої таблиці з даними, які розташовані в другому табличному діапазоні. У чотирьох випадках результат вийшов «1», а в двох випадках - «0». Тобто, програма не змогла відшукати в другій таблиці два значення, які є в першому табличному масиві.

Звичайно, цей вислів для того, щоб порівняти табличні показники, можна застосовувати і в існуючому вигляді, але є можливість його удосконалити.

Зробимо так, щоб ті значення, які є в другій таблиці, але відсутні в першій, виводилися окремим списком.

1. Перш за все, трохи переробимо нашу формулу СЧЁТЕСЛІ, а саме зробимо її одним з аргументів оператора ЯКЩО. Для цього виділяємо перший осередок, в якій розташований оператор СЧЁТЕСЛІ. У рядку формул перед нею дописуємо вираз «ЯКЩО» без лапок і відкриваємо дужку. Далі, щоб нам легше було працювати, виділяємо в рядку формул значення «ЯКЩО» і тиснемо на іконку «Вставити функцію».
2. Відкривається вікно аргументів функції ЯКЩО. Як бачимо, перше поле вікна вже заповнене значенням оператора СЧЁТЕСЛІ. Але нам потрібно дописати дещо ще в це поле. Встановлюємо туди курсор і до вже існуючого висловом дописуємо «= 0» без лапок.

   Після цього переходимо до поля «Значення якщо істина». Тут ми скористаємося ще однією вкладеної функцією - СТРОКА. Вписуємо слово «СТРОКА» без лапок, далі відкриваємо дужки і вказуємо координати першого осередку з прізвищем в другій таблиці, після чого закриваємо дужки. Саме в нашому випадку в поле «Значення якщо істина» вийшло такий вираз:

   СТРОКА(D2)

   Тепер оператор СТРОКА буде повідомляти функції ЯКЩО номер рядка, в якій розташована конкретне прізвище, і в разі, коли умова, заданий в першому полі, буде виконуватися, функція ЯКЩО буде виводити цей номер в клітинку. Тиснемо на кнопку «OK».

3. Як бачимо, перший результат відображається, як «БРЕХНЯ». Це означає, що значення не задовольняє умовам оператора ЯКЩО. Тобто, перша прізвище присутній в обох списках.
4. За допомогою маркера заповнення, вже звичним способом копіюємо вираз оператора ЯКЩО на весь стовпець. Як бачимо, по двох позиціях, які присутні в другій таблиці, але відсутні в першій, формула видає номера рядків.
5. Відступаємо від табличній області вправо і заповнюємо колонку номерами по порядку, починаючи від 1. Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Відкривається Майстер функцій. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Клацаємо по кнопці «OK».
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Клацаємо по кнопці «OK».

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

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

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

    Тиснемо на кнопку «OK».

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

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

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

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

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

• ← SQL запити в Microsoft Excel
• Робота зі зв'язаними таблицями в Microsoft Excel →