Порівняння даних у двох стовпцях для пошуку дублікатів у Excel

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

Спосіб 1. Використання формули робочого листа

1. Запустіть Excel.
2. Як приклад введіть у новому аркуші введіть такі дані (залишіть стовпець B порожнім):

Число, що повторюються, відображаються в стовпці B, як у наступному прикладі:

Спосіб 2. Використання макросу Visual Basic

Попередження: Корпорація Майкрософт надає приклади програмування лише для ілюстрації, без явних або певних гарантій. Це включає гарантії товарного стану або придатності для конкретної мети, але не обмежується ними. У цій статті передбачається, що ви знайомі з мовою програмування, що демонструється, та інструментами, що використовуються для створення та налагодження процедур. Фахівці служби підтримки корпорації Майкрософт можуть допомогти пояснити функціональність тієї чи іншої процедури. Однак вони не змінюватимуть ці приклади для надання додаткових функціональних можливостей або створення процедур для задоволення ваших конкретних вимог.

Щоб використати макрос Visual Basic для порівняння даних у двох стовпцях, виконайте дії, описані в наведеному нижче прикладі.

1. Запустіть Excel.
2. Натисніть ALT+F11, щоб запустити редактор Visual Basic.
3. У меню Вставка виберіть Модуль.
4. Введіть наступний код на аркуші модуля:

Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: Якщо compare range is located on another workbook ' or worksheet, use the following syntax.'Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop через будь-який порт у виборі і compare it to ' кожен комп'ютер в CompareRange. Для кожного x у списку для кожного і в CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub

Примітка: Якщо ви не бачите вкладку Розробникможливо, вам знадобиться включити її. Для цього виберіть Файл > Параметри > Налаштувати стрічку, а потім виберіть вкладку Розробник у полі налаштування праворуч.

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

Порівняння двох таблиць в Excel на збіг значень у стовпцях

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

Порівняння двох стовпців на збіги в Excel

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

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

1. Виберіть інструмент «ФОРМУЛИ»-«Визначені імена»-«Присвоїти ім'я».
2. У вікні, що з'явилося в полі «Ім'я:» введіть значення – Таблица_1.
3. Лівою клавішею миші зробіть клацання по полю введення «Діапазон:» та виділіть діапазон: A2:A15. І натисніть OK.

Для другого списку виконайте ті ж дії тільки назву присвойте – Таблица_2. А діапазон вкажіть C2: C15 відповідно.

Корисна порада! Імена діапазонів можна надавати швидше за допомогою поля імен. Воно знаходиться ліворуч від рядка формул. Просто виділяйте діапазони осередків, а в полі імен введіть відповідне ім'я для діапазону та натисніть Enter.

Тепер скористаємося умовним форматуванням, щоб порівняти два списки в Excel. Нам потрібно отримати наступний результат:

Позиції, які є в Таблиці_1, але немає в Таблиці_2, будуть відображатися зеленим кольором. У той же час, позиції, що знаходяться в Таблиці_2, але відсутні в Таблиці_1, будуть підсвічені синім кольором.

1. Виділіть діапазон першої таблиці: A2:A15 і оберіть інструмент: «ГОЛОВНА»-«Умовне форматування»-«Створити правило»-«Використовувати формулу для визначення форматованих осередків:».
2. У полі введення введіть формулу:
3. Натисніть кнопку «Формат» і на вкладці «Заливка» вкажіть зелений колір. На всіх вікнах тиснемо ОК.
4. Виділіть діапазон першого списку: C2:C15 і знову оберіть інструмент: «ГОЛОВНА»-«Умовне форматування»-«Створити правило»- «Використовувати формулу для визначення форматованих осередків:».
5. У полі введення введіть формулу:
6. Натисніть кнопку «Формат» і на вкладці «Заливка» вкажіть синій колір. На всіх вікнах тиснемо ОК.

Принцип порівняння даних двох стовпців в Excel

При визначенні умов для форматування осередків стовпців ми використовували функцію РАХУНКИ. У цьому прикладі ця функція перевіряє скільки разів зустрічається значення другого аргументу (наприклад, A2) у списку першого аргументу (наприклад, Таблица_2). Якщо кількість разів = 0 у такому разі формула повертає значення ІСТИНА. У такому випадку осередку присвоюється формат користувача, зазначений у параметрах умовного форматування.

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

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Як порівнювати дані в Excel

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

Команда контент-менеджерів wikiHow ретельно слідкує за роботою редакторів, щоб гарантувати відповідність кожної статті нашим високим стандартам якості.

Кількість переглядів цієї статті: 111 041.

У цій статті розповідається, як порівняти дані в Excel: у двох шпальтах, на двох аркушах або у двох книгах.

Порівняння двох стовпців

• Наприклад, якщо стовпці з даними починаються з осередків A2 і B2, клацніть по осередку C2.

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

Зверніть увагу на параметри Збігається та Не збігається. Вони вказують на те, збігаються або не збігаються дані у двох осередках. Даними можуть бути літери, слова, дати, числа та час. Зверніть увагу, що регістр літер не враховується (наприклад, слова «СИНІЙ» та «синій» будуть оцінені як такі, що збігаються). [1] X Джерело інформації