Порівняння 2 файлів у Excel на відмінності

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

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

Таких дій багато. Давайте розглянемо деякі з них.

Яка мета порівняння файлів Excel?

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

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

Таких ситуацій може бути величезна кількість. Але переходимо до практики, адже тема досить складна.

Усі способи порівняння 2 таблиць у Excel

Хоча тема складна, але легка. Так, не дивуйтеся. Він складний, оскільки складається з багатьох частин. Але самі ці частини легко зрозуміти і виконати. Давайте розглянемо, як можна порівняти дві електронні таблиці Excel безпосередньо на практиці.

Формула рівності та перевірка хибно-істинного

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

Тепер різницю видно неозброєним оком.

Виділення відмінних значень

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

У меню, що з’явилося, клацніть по меню «Виділити групу комірок…». Далі відкриється вікно, в якому нам потрібно вибрати критерієм відмінності по лініях.

Порівняння 2 таблиць за допомогою умовного форматування

Умовне форматування - це дуже зручний і, що важливо, функціональний спосіб, який дозволяє підібрати колір, який буде виділяти різне або однакове значення. Цю опцію можна знайти на вкладці «Домашня сторінка». Там можна знайти кнопку з відповідною назвою і в списку, що з’явився, вибрати «Керувати правилами». З'явиться диспетчер правил, в якому нам потрібно вибрати меню «Створити правило».

Далі зі списку критеріїв нам потрібно вибрати той, де написано, що потрібно за допомогою формули визначити комірки, які будуть відформатовані особливим чином. В описі правила потрібно вказати формулу. У нашому випадку це =$C2<>$E2, після чого підтверджуємо свої дії натисканням кнопки «Форматувати». Після цього встановлюємо зовнішній вигляд комірки і через спеціальне міні-віконце із зразком дивимося, чи подобається вона нам. 

Якщо все влаштовує, натисніть кнопку «ОК» і підтвердіть дії.

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

Функція COUNTIF + правила порівняння таблиць

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

Уявімо, що у нас є два діапазони з дещо різною інформацією. Перед нами стоїть завдання порівняти їх і зрозуміти, яке значення відрізняється. Спочатку потрібно вибрати його в першому діапазоні і перейти на вкладку «Головна». Там знаходимо знайомий раніше пункт «Умовне форматування». Ми створюємо правило та налаштовуємо правило на використання формули. 

У цьому прикладі формула виглядає так, як показано на знімку екрана.

Після цього встановлюємо формат, як описано вище. Ця функція аналізує значення, що міститься в клітинці C1, і переглядає діапазон, указаний у формулі. Він відповідає другій колонці. Нам потрібно взяти це правило і скопіювати його на весь діапазон. Ура, всі комірки з неповторюваними значеннями виділені.

Функція VLOOKUP для порівняння 2 таблиць

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

Ця функція повторює кожне значення та перевіряє, чи є дублікати від першого стовпця до другого. Ну а після виконання всіх операцій це значення записується в клітинку. Якщо його немає, то отримуємо помилку #N/A, якої цілком достатньо, щоб автоматично зрозуміти, яке значення не збігається.

Функція IF

Логічна функція ЯКЩО – це ще один хороший спосіб порівняти два діапазони. Головна особливість цього методу полягає в тому, що можна використовувати лише ту частину масиву, яка порівнюється, а не всю таблицю. Це економить ресурси як комп’ютера, так і користувача.

Візьмемо маленький приклад. У нас є дві колонки – A і B. Нам потрібно порівняти частину інформації в них між собою. Для цього нам потрібно підготувати ще один службовий стовпець С, в якому записана наступна формула.

Використання формули, що використовує функції IF, ПОМИЛКА и БІЛЬШ ЕКСПОЗИЦІЙНИЙ ви можете перебирати всі потрібні елементи стовпця A, а потім у стовпці B. Якщо він був знайдений у стовпцях B і A, то він повертається до відповідної комірки.

Макрос VBA

Макрос — це найскладніший, але й найдосконаліший метод порівняння двох таблиць. деякі параметри порівняння зазвичай неможливі без сценаріїв VBA. Вони дозволяють автоматизувати процес і заощадити час. Усі необхідні операції для підготовки даних, якщо їх було запрограмовано один раз, виконуватимуться й надалі.

Виходячи з розв’язуваної задачі, можна написати будь-яку програму, яка порівнює дані без втручання користувача.

Як порівняти файли в Excel

Якщо користувач поставив перед собою завдання (ну або його поставили) порівняти два файли, то це можна зробити відразу двома методами. Перший — це використання спеціальної функції. Щоб реалізувати цей спосіб, дотримуйтесь інструкції:

1. Відкрийте файли, які потрібно порівняти.
2. Відкрийте вкладку «Вид» – «Вікно» – «Перегляд поруч».

Після цього два файли будуть відкриті в одному документі Excel.

Те саме можна зробити за допомогою звичайних засобів Windows. Спочатку потрібно відкрити два файли в різних вікнах. Після цього візьміть одне вікно та перетягніть його в саму ліву частину екрана. Після цього відкрийте друге вікно та перетягніть його в праву сторону. Після цього два вікна будуть поруч. 

Умовне форматування для порівняння 2 файлів Excel

Дуже часто порівняння документів означає відображення їх один біля одного. Але в деяких випадках можна автоматизувати цей процес за допомогою умовного форматування. З його допомогою можна перевірити, чи немає розбіжностей між листами. Це дозволяє заощадити час, який можна використати на інші цілі.

Спочатку нам потрібно перенести порівняні аркуші в один документ. 

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

Далі потрібно виділити всі потрібні комірки, щоб відобразити всі відмінності. Найпростіший спосіб зробити це, клацнувши верхню ліву клітинку, а потім натиснувши комбінацію клавіш Ctrl + Shift + End.

Після цього перейдіть у вікно умовного форматування та створіть нове правило. В якості критерію беремо формулу, придатну в конкретному випадку, потім задаємо формат.

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

Порівняння даних в Excel на різних аркушах

Припустімо, що у нас є список працівників, у якому також вказана їх зарплата. Цей список оновлюється щомісяця. Цей список буде скопійовано на новий аркуш.

Припустимо, нам потрібно порівняти зарплати. У цьому випадку в якості даних можна використовувати таблиці з різних аркушів. Ми будемо використовувати умовне форматування, щоб підкреслити відмінності. Все просто.

За допомогою умовного форматування можна проводити ефективні порівняння, навіть якщо імена співробітників розташовані в іншому порядку.

Як порівняти 2 аркуші в електронній таблиці Excel

Порівняння інформації, розташованої на двох аркушах, здійснюється за допомогою функції БІЛЬШ ЕКСПОЗИЦІЙНИЙ. В якості першого його параметра стоїть пара значень, які потрібно шукати на аркуші, що відповідає за наступний місяць. Простіше кажучи, березень. Ми можемо позначити діапазон, що переглядається, як набір клітинок, які є частиною іменованих діапазонів, об’єднаних попарно.

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

Інструмент порівняння електронних таблиць

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

Важливо відзначити, що цей інструмент доступний лише тим користувачам, які придбали офісні пакети Professional Plus.

Ви можете відкрити його безпосередньо з вкладки «Головна», вибравши пункт «Порівняти файли».

Після цього з'явиться діалогове вікно, в якому потрібно вибрати другу версію книги. Ви також можете ввести адресу в Інтернеті, де знаходиться ця книга.

Після того, як ми вибрали дві версії документа, нам потрібно підтвердити свої дії клавішею ОК.

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

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

Як інтерпретувати результати порівняння

Все дуже просто: різні типи відмінностей позначаються різними кольорами. Форматування може поширюватися як на заповнення комірки, так і на сам текст. Отже, якщо в клітинку були введені дані, то заливка зелена. Якщо щось стає незрозумілим, на самому сервісі є символи, які показують, який тип зміни яким кольором виділено.