Як порівняти два аркуші Excel (для відмінностей)

Подивіться відео - Як порівняти два аркуші Excel для відмінностей

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

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

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

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

Давайте розпочнемо!

Порівняйте два аркуші Excel в окремих файлах Excel (паралельно)

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

Це Перегляд Поряд варіант.

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

Давайте подивимося, як це використовувати, коли вам доведеться порівнювати два окремі файли або два аркуші в одному файлі.

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

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

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

Нижче наведено кроки для вирівнювання двох файлів поруч та їх порівняння:

1. Відкрийте файли, які потрібно порівняти.
2. У кожному файлі виберіть аркуш, який потрібно порівняти.
3. Перейдіть на вкладку Вигляд
4. У групі Windows натисніть опцію «Переглянути поруч». Це стає доступним лише тоді, коли у вас відкрито два або більше файлів Excel.

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

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

Відкриється діалогове вікно «Впорядкувати Windows», де можна вибрати «Вертикально».

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

Але для цього потрібно включити Синхронна прокрутка.

Щоб увімкнути синхронну прокрутку, клацніть на вкладці Перегляд (у будь -якій із книг), а потім натисніть на опцію Синхронна прокрутка. Це кнопка перемикання (тому, якщо ви хочете її вимкнути, просто натисніть ще раз на неї).

Порівняння кількох аркушів у окремих файлах Excel (паралельно)

За допомогою опції "Перегляд поруч" можна порівняти лише два файли Excel за один раз.

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

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

У діалоговому вікні Впорядкувати Windows виберіть Вертикальна/Горизонтальна, а потім натисніть OK.

Це дозволить розташувати всі відкриті файли Excel у вибраному порядку (вертикальному чи горизонтальному).

Порівняйте два аркуші (поруч) у тій же книзі Excel

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

Але ви все одно можете зробити те саме паралельне порівняння.

Це стало можливим завдяки "Нова функція Windows в Excel, що дозволяє відкривати два екземпляри в одній книзі. Після того, як у вас відкрито два екземпляри, ви можете упорядкувати їх поруч, а потім порівняти.

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

Нижче наведено кроки для порівняння двох аркушів у Excel:

1. Відкрийте книгу з аркушами, які потрібно порівняти.
2. Перейдіть на вкладку Вигляд
3. У групі «Вікно» натисніть на опцію «Нове вікно». Відкриється другий екземпляр тієї самої книги.
4. На вкладці «Перегляд» натисніть «Впорядкувати все». Відкриється діалогове вікно Впорядкувати Windows
5. Виберіть "По вертикалі", щоб порівняти дані у стовпцях (або по горизонталі, якщо потрібно порівняти дані у рядках).
6. Натисніть OK.

Наведені вище кроки упорядковують обидва екземпляри книги по вертикалі.

На даний момент в обох книгах буде вибрано однаковий аркуш. В одній із книг виберіть інший аркуш, який потрібно порівняти з активним аркушем.

Як це працює?

Коли ви натискаєте Нове вікно, воно знову відкриває ту саму книгу з дещо іншою назвою. Наприклад, якщо ім’я вашої робочої книги - «Тест», і ви натискаєте «Нове вікно», вона буде називати вже відкриту книгу «Тест - 1», а другий екземпляр - «Тест - 2».

Зауважте, що це все та сама книга. Якщо ви внесете зміни до будь -якої з цих книг, це відобразиться в обох.

І коли ви закриєте будь -який екземпляр відкритого файлу, ім’я повернеться до початкового.

Ви також можете включити синхронну прокрутку, якщо хочете (натиснувши на опцію «Синхронна прокрутка» на вкладці «Перегляд»)

Порівняйте два аркуші та виділіть відмінності (використання умовного форматування)

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

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

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

Цей метод дійсно корисний, якщо у вас є дві версії на двох різних аркушах, і ви хочете швидко перевірити, що змінилося.

Зауважте, що ви НЕ МОЖЕ порівняйте два аркуші в різних робочих зошитах.

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

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

Нижче наведено кроки для цього.

1. Виберіть дані на аркуші, де потрібно виділити зміни. Оскільки я хочу перевірити, як змінилися ціни з січня по лютий, я вибрав дані на аркуші за лютий.
2. Натисніть вкладку Домашня сторінка
3. У групі Стилі натисніть «Умовне форматування»
4. У параметрах, що з’являються, натисніть «Нове правило»
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати"
6. У поле формули введіть таку формулу: = В2Ян! В2
7. Натисніть кнопку Формат
8. У діалоговому вікні «Форматування клітинок» натисніть вкладку «Заповнити» та виберіть колір, яким потрібно виділити невідповідні дані.
9. Натисніть OK
10. Натисніть OK

Вищезазначені кроки миттєво виділять будь -які зміни в наборі даних на обох аркушах.

Як це працює?

Умовне форматування виділяє клітинку, коли дана формула для цієї комірки повертає TRUE. У цьому прикладі ми порівнюємо кожну клітинку на одному аркуші з відповідною коміркою на іншому аркуші (це робиться за допомогою оператора не дорівнює у формулі).

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

Зауважте, що в цьому прикладі я використовував відносне посилання (A1, а не $ A $ 1 або $ A1 або A $ 1).

Використовуючи цей метод для порівняння двох аркушів у Excel, пам’ятайте наступне;

• Цей метод хороший для швидкого виявлення відмінностей, але ви не можете використовувати його постійно. Наприклад, якщо я вводжу новий рядок у будь -який з наборів даних (або видаляю рядок), це дасть мені неправильні результати. Як тільки я вставляю/видаляю рядок, усі наступні рядки вважаються різними і відповідно виділяються.
• Ви можете порівняти лише два аркуші в одному файлі Excel
• Ви можете порівняти лише значення (а не різницю у формулі чи форматуванні).

Порівняйте два файли/аркуші Excel і отримайте відмінності за допомогою формули

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

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

Цей метод підійде, якщо потрібно порівняти дві окремі книги Excel або робочі аркуші в одній книзі.

Дозвольте мені показати вам приклад, коли я порівнюю два набори даних на двох аркушах (в одній книзі).

Припустимо, у вас є набір даних, як показано нижче на аркуші під назвою Jan (і подібні дані на аркуші під назвою Feb), і ви хочете знати, які значення відрізняються.

Щоб порівняти два аркуші, спочатку вставте новий аркуш (назвемо цей аркуш «Різниця»).

У клітинці А1 введіть таку формулу:

= IF (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Value Value:" & Feb! A1, "")

Скопіюйте та вставте цю формулу для діапазону так, щоб вона охоплювала весь набір даних на обох аркушах. Оскільки у мене є невеликий набір даних, я скопіюю та вставлю цю формулу лише в діапазон A1: B10.

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

Хорошим моментом цього методу є те, що він лише дає вам відмінності та точно показує, у чому різниця. У цьому прикладі я легко бачу, що ціна в клітинках В4 і В8 відрізняється (як і точні значення в цих комірках).

Порівняйте два файли/аркуші Excel та отримайте відмінності за допомогою VBA

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

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

Припустимо, у вас є два аркуші січень і лютий, і ви хочете порівняти та виділити відмінності на аркуші січня, ви можете скористатися наведеним нижче кодом VBA:

Sub CompareSheets () Dim rngCell As Range for each rngCell in Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Cells (rngCell.Row, rngCell.Column) Тоді rngCell.Interior.Color = vbYellow End Якщо далі rngCell End Sub

Наведений вище код використовує цикл For Next для проходження кожної клітинки на аркуші Jan (увесь використаний діапазон) і порівнює її з відповідною коміркою на аркуші Feb. Якщо він виявляє різницю (це перевіряється за допомогою оператора If-Then), він виділяє ці клітинки жовтим кольором.

Ви можете використовувати цей код у звичайному модулі у редакторі VB.

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

Ось кроки, щоб отримати особисту книгу макросів у Excel (вона недоступна за умовчанням, тому її потрібно ввімкнути).

Ось кроки для збереження цього коду в книзі особистих макросів.

І тут ви знайдете кроки, щоб додати цей макрокод до QAT.

Використання стороннього інструменту - компаратор XL

Ще один швидкий спосіб порівняти два файли Excel і перевірити їх збіги та відмінності-це використання безкоштовного стороннього інструменту, такого як XL Comparator.

Це веб-інструмент, де ви можете завантажити два файли Excel, і він створить файл порівняння, який буде містити загальні дані (або різні дані залежно від того, який варіант ви обрали.

Припустимо, у вас є два файли, які мають набори даних клієнтів (наприклад, ім’я та електронну адресу), і ви хочете швидко перевірити, які клієнти є у файлі 1, а не у файлі 2.

Нижче описано, як ви порівнюєте два файли Excel та створюєте звіт про порівняння:

1. Відкрийте https://www.xlcomparator.net/
2. Використовуйте опцію Вибрати файл, щоб завантажити два файли (максимальний розмір кожного файлу може становити 5 МБ)
3. Натисніть кнопку Далі.
4. Виберіть спільний стовпець в обох цих файлах. Інструмент буде використовувати цей загальний стовпець для пошуку збігів та відмінностей
5. Виберіть один із чотирьох варіантів, незалежно від того, чи хочете ви отримати відповідні дані чи різні дані (на основі файлу 1 або файлу 2)
6. Натисніть Далі
7. Завантажте файл порівняння, у якому будуть дані (на основі того, який варіант ви вибрали на кроці 5)

Нижче наведено відео, яке показує, як працює інструмент XL Comparator.

Одне занепокоєння, яке може виникнути у вас при використанні стороннього інструменту для порівняння файлів Excel, стосується конфіденційності. Якщо у вас є конфіденційні дані і конфіденційність дійсно важлива для них, краще скористатися іншими методами, показаними вище. Зауважте, що на веб -сайті XL Comparator згадується, що вони видаляють усі файли через 1 годину порівняння.

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