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

Щоб порівняти дані у двох стовпцях аркуша Microsoft Excel і знайти повторювані записи, скористайтеся наведеними нижче способами.

Метод 1. Використання формули аркуша

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

Повторювані числа відображаються в стовпці B, як у прикладі нижче.

Метод 2. Використання макросу Visual Basic

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

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

1. Запустіть програму Excel.
2. Натисніть клавіші Alt+F11, щоб запустити редактор Visual Basic.
3. У меню Insert (Вставлення) виберіть Module (Модуль).
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: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub

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

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

Як порівняти два стовпці в Excel за допомогою VLOOKUP

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

Як порівняти два стовпці в Excel за допомогою VLOOKUP

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

Щоб побудувати формулу VLOOKUP в її базовому вигляді, потрібно зробити ось що:

• За lookup_value (1-й аргумент), використовуйте саму верхню комірку зі списку 1.
• За table_array (2-й аргумент), надати весь Перелік 2.
• За col_index_num (3-й аргумент), використовуйте 1, оскільки в масиві лише один стовпець.
• За range_lookup (4-й аргумент), встановити FALSE – точний збіг.

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

Формула потрапляє в комірку E2, а потім ви перетягуєте її вниз через стільки комірок, скільки елементів у списку 1.

Звертаємо Вашу увагу на те, що table_array блокується абсолютними посиланнями ($C$2:$C$9), щоб вона залишалася незмінною при копіюванні формули в наступні комірки.

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

Маскування помилок #N/A

Розглянута вище формула VLOOKUP чудово виконує свою основну задачу – повертає загальні значення та визначає відсутні точки даних. Однак вона видає купу помилок #N/A, які можуть заплутати недосвідченого користувача, змусивши його подумати, що у формулі щось не так.

Замінити помилки на порожні клітинки використовуйте VLOOKUP у поєднанні з функцією IFNA або IFERROR таким чином:

=IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), “”)

Наша покращена формула повертає порожній рядок (“”) замість #N/A. Ви також можете повернути свій власний текст Наприклад, “Немає в переліку 2”, “Відсутній” або “Немає в наявності”:

=IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), “Не в списку 2”)

Це базова формула СРЗНАЧ для порівняння двох стовпців в Excel. Залежно від конкретного завдання, вона може бути модифікована, як показано в наступних прикладах.

Порівняння двох стовпців на різних аркушах Excel за допомогою VLOOKUP

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

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

Припускаючи, що Список 1 знаходиться в колонці А на Аркуш1 а список 2 знаходиться в колонці А на Лист2 можна порівняти два стовпчики і знайти збіги, використовуючи цю формулу:

=IFNA(VLOOKUP(A2, Sheet2!$A$2:$A$9, 1, FALSE), “”)

Більш детальну інформацію можна знайти за посиланням:

Порівняти два стовпці та повернути спільні значення (співпадіння)

У попередніх прикладах ми розглянули формулу VLOOKUP у її найпростішому вигляді:

=IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), “”)

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

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

В Excel для Microsoft 365 та Excel 2021, які підтримують динамічні масиви, можна скористатися функцією ФІЛЬТР для динамічного відсіювання пропусків. Для цього в якості критерію ФІЛЬТР використовуйте формулу IFNA VLOOKUP:

=FILTER(A2:A14, IFNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE), “”)”””)

Звертаємо Вашу увагу, що в даному випадку ми постачаємо весь Список 1 (А2:А14) до lookup_value Функція порівнює кожне з шуканих значень зі Списком 2 (C2:C9) і повертає масив збігів та помилок #N/A, що означають пропущені значення. Функція IFNA замінює помилки на порожні рядки і передає результати функції FILTER, яка відфільтровує пропуски (“”) і виводить масив збігів як кінцевий результат.

Крім того, ви можете використовувати функцію ISNA для перевірки результату VLOOKUP і відфільтрувати елементи, що оцінюються як FALSE, тобто значення, відмінні від помилок #N/A:

=FILTER(A2:A14, ISNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE))=FALSE)

Такого ж результату можна досягти за допомогою функції XLOOKUP, яка робить формулу ще простішою. Завдяки здатності XLOOKUP обробляти помилки #N/A внутрішньо (опція if_not_found аргумент), можна обійтися без обгортки IFNA чи ISNA:

=FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,””)””)

Порівняйте два стовпчики та знайдіть пропущені значення (відмінності)

Щоб порівняти 2 стовпці в Excel і знайти відмінності, можна діяти таким чином:

1. Запишіть основну формулу для пошуку першого значення зі списку 1 (A2) у списку 2 ($C$2:$C$9): VLOOKUP(A2, $C$2:$C$9, 1, FALSE)
2. Вкладіть наведену вище формулу у функцію ISNA, щоб перевірити вихід VLOOKUP на наявність помилок #N/A. У разі помилки ISNA видає значення TRUE, в іншому випадку – FALSE: ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE))
3. Використовуйте формулу ISNA VLOOKUP для логічного тесту функції IF. Якщо тест оцінюється як TRUE (помилка #N/A), поверніть значення зі Списку 1 в тому ж рядку. Якщо тест оцінюється як FALSE (знайдено збіг у Списку 2), поверніть порожній рядок.

Повна формула має такий вигляд:

=IF(ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE)), A2, “”)

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

В Excel 365 і Excel 2021 ви можете відфільтрувати список результатів динамічно. Для цього просто помістіть формулу ISNA VLOOKUP в поле включають аргумент функції FILTER:

=FILTER(A2:A14, ISNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE)))

Інший спосіб полягає у використанні XLOOKUP для критеріїв – функція повертає порожні рядки (“”) для відсутніх точок даних, і ви фільтруєте значення в Списку 1, для яких XLOOKUP повернула порожні рядки (=””):

=FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,””)=””)

Формула VLOOKUP для визначення збігів і відмінностей між двома стовпцями

Якщо ви хочете додати до першого списку текстові підписи, що вказують, які значення доступні в другому списку, а які ні, використовуйте формулу СРЗНАЧ разом з функціями ЕСЛИ і ИСТИНА/ОШИБКА.

Наприклад, для визначення назв, які є в обох колонках А і D, і тих, які є тільки в колонці А, використовується формула:

=IF(ISNA(VLOOKUP(A2, $D$2:$D$9, 1, FALSE)), “Не має права”, “Має право”)

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

У цьому прикладі ми використовували мітки “Не кваліфіковано”/”Кваліфіковано”, які підходять для нашого прикладу набору даних. Ви можете замінити їх на “Не в списку 2″/”У списку 2”, “Не доступно”/”Доступно” або будь-які інші мітки, які ви вважаєте за потрібне.

Цю формулу найкраще вставити в стовпчик, що прилягає до Списку 1, і скопіювати через стільки клітинок, скільки елементів у вашому списку.

Ще один спосіб виявити збіги та розбіжності у 2 колонках – це використання функції СХОЖІСТЬ:

=IF(ISNA(MATCH(A2, $D$2:$D$9, 0)), “Не в списку 2”, “В списку 2”)

Порівняти 2 стовпці і повернути значення з третього

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

Наприклад, щоб порівняти імена в колонках A і D у двох таблицях нижче і повернути час з колонки E, формула має вигляд:

Щоб приховати помилки #N/A, скористайтеся перевіреним рішенням – функцією IFNA:

=IFNA(VLOOKUP(A3, $D$3:$E$10, 2, FALSE), “”)

Замість пропусків ви можете повернути будь-який текст для відсутніх точок даних – просто введіть його в останньому аргументі. Наприклад:

=IFNA(VLOOKUP(A3, $D$3:$E$10, 2, FALSE), “Не доступно”)

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

Особисто я б покладався на більш гнучку формулу INDEX MATCH:

=IFNA(INDEX($E$3:$E$10, MATCH(A3, $D$3:$D$10, 0)), “”)

Або скористайтеся сучасним наступником VLOOKUP – функцією XLOOKUP, доступною в Excel 365 та Excel 2021:

Щоб отримати імена кваліфікованих учасників з групи А та їх результати, просто відфільтруйте порожні клітинки в колонці Б:

Інструменти порівняння

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

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

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

Порівняти кілька аркушів – знайти і виділити відмінності відразу в декількох аркушах.

Практичний посібник для завантаження

VLOOKUP в Excel для порівняння стовпців – приклади (файл .xlsx)

Michael Brown

Майкл Браун — відданий ентузіаст технологій із пристрастю до спрощення складних процесів за допомогою програмних засобів. Маючи понад десятирічний досвід роботи в технологічній індустрії, він відточив свої навички роботи з Microsoft Excel і Outlook, а також із Google Таблицями та Документами. Блог Майкла присвячений тому, щоб поділитися своїми знаннями та досвідом з іншими, надаючи прості поради та навчальні посібники для підвищення продуктивності та ефективності. Незалежно від того, чи є ви досвідченим професіоналом чи початківцем, блог Майкла пропонує цінну інформацію та практичні поради щодо отримання максимальної користі від цих основних програмних інструментів.

Як порівняти два стовпці за допомогою функції VLOOKUP в Excel

Існує багато способів порівняти два списку У цій статті я збираюся порівняти / знайти збіги в двох стовпцях в Excel, використовуючи ПЕРЕГЛЯД В одній зі своїх попередніх статей я обговорював Як порівняти два стовпці або списки в Excel де я розповідав про інші методи порівняння.

Перш ніж почати порівняння, я розповім про синтаксис, аргументи та інші необхідні речі про ПЕРЕГЛЯД функцію.

Завантажити Практичний посібник

Огляд функції ВСТАВКА в Excel

V в ПЕРЕГЛЯД означає “вертикальний”. ПЕРЕГЛЯД “У нас тут є ПЕРЕГЛЯД вбудована функція Excel, яка виконує вертикальний пошук шляхом пошуку певного значення стовпця в іншому стовпці.

VLOOKUP( значення_пошуку, масив_таблиці, номер_індексу_колонки, [діапазон_пошуку] ). Аргументи, які вказуються в цьому синтаксисі, мають певне значення.

Про це свідчать результати дослідження, проведеного в рамках ПЕРЕГЛЯД можуть бути текстові рядки або числові дані, залежно від того, які дані ви використовуєте. Якщо НЕПРАВДА використовується як [range_lookup]. то буде знайдено точний збіг. Якщо точного збігу не знайдено, то буде повернуто значення #N/A Якщо ПРАВДА використовується як [range_lookup]. Якщо не знайдено жодного наближеного збігу, то повертається наступне менше значення.

Читати далі: Excel LOOKUP vs VLOOKUP: на 3 прикладах

2 способи порівняння двох стовпців за допомогою СЦЕПИ в Excel

1. використання тільки функції VLOOKUP для порівняння двох стовпців

Скажімо, у вас є два стовпці, в яких перераховані деякі кольори. Я збираюся порівняти ці два стовпці для точного збігу. Наприклад, якщо я виберу синій колір з 1-го стовпця і шукатиму цей колір у 3-му стовпці, він почне шукати синій колір, і якщо цього кольору не існує, то він поверне значення #N/A Отже, почнемо порівняння. Нижче наведені списки кольорів, а в колонці “Існування” буде показано порівняння.

• Ви побачите, що в цій комірці міститься значення червоного кольору. Тепер перетягніть вниз Ручка наповнення скопіювати формулу для решти комірок стовпця. Ви побачите результат, який шукали.

На сьогоднішній день, на жаль, це не так. #N/A результати отримані, оскільки синій та білий кольори відсутні у Переліку кольорів-2.

Примітка: Ми вказуємо діапазон як $D$5:$D$10. Символ “$” використовується для того, щоб зробити клітинки абсолютними і постійними у формулі. Таким чином, коли ви копіюєте формулу для інших клітинок, вона буде використовувати той самий діапазон.

Читати далі: Чому VLOOKUP повертає #N/A при наявності збігу (5 причин та шляхи вирішення)

2. використання функцій IF, ISNA та VLOOKUP для порівняння двох стовпчиків

Тут я буду використовувати той же приклад, але вставлю дві нові функції з ПЕРЕГЛЯД Якщо немає точного збігу між стовпчиками, формула поверне NO. Якщо збіги існують, формула поверне YES щодо першого стовпчика. Списки кольорів наведені нижче, а в полі Колір Існує у колонці буде показано порівняння.

Розбивка формули:

На сьогоднішній день, на жаль, це не так. ПЕРЕГЛЯД поверне вивід для шуканого значення.

Далі в рамках заходу відбулася презентація ISNA Функція поверне TRUE, якщо отримає #N/A інакше повернеться помилка НЕПРАВДА .

Насамкінець, в рамках проекту ЯКЩО функція поверне НІ для ПРАВДА і ТАК для НЕПРАВДА .

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

Через кілька хвилин ви отримаєте всі результати, як на зображенні нижче.

Читати далі: Як використовувати функцію ЕСЛИ ИСНА з функцією VLOOKUP в Excel (3 приклади)

Схожі читання

• VLOOKUP не працює (8 причин та шляхи вирішення)
• Що таке табличний масив у VLOOKUP (пояснення на прикладах)
• Функція INDEX MATCH vs VLOOKUP (9 прикладів)
• Використання СЦЕПИ з декількома критеріями в Excel (6 способів + альтернативи)
• ВЛОКЗАП Excel для повернення декількох значень по вертикалі

Як порівняти два аркуші Excel за допомогою функції VLOOKUP

У цьому прикладі ми будемо порівняти два стовпці двох різних аркушів Excel за допомогою VLOOKUP Формула буде аналогічною, за винятком Table_array Припустимо, що у нас є два списки кольорів на двох різних аркушах. Ім’я аркушів визначається як CL-1 і CL-2. Ми порівняємо Список кольорів-1 з CL-1 робочий аркуш з Список кольорів 2 про CL-2 Нижче наведено два списки з двох робочих аркушів – це Список-1.

• Ви побачите, що з’явилося значення Червоний. Це тому, що червоний колір є загальним кольором в обох стовпчиках CL-1 і Робочі аркуші CL-2. Тепер скопіюйте цю формулу з Комірка С6 до C11 за допомогою програми Ручка наповнення для знаходження результату для всіх двох стовпчиків.

Через деякий час з’явиться весь вихідний матеріал.

Тут жовтий, помаранчевий і чорний кольори не відображаються в CL-2 Ось чому ми отримуємо решту #N/A в CL-1 аркуш.

Читати далі: Приклад переходу між двома аркушами в Excel

Як порівняти два стовпці за допомогою функції VLOOKUP і повернути третє значення

Нам часто доводиться використовувати ПЕРЕГЛЯД для повернення третього значення шляхом порівняння двох стовпців. У цьому розділі ми навчимося робити це простим способом. Для цього ми модифікували набір даних, зв’язали деякі розміри з кольорами і додали ще один стовпець, в якому розмістили три кольори. Тепер ми будемо порівнювати стовпці з кольорами і повертати розмір у вихідному стовпці.

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

Ось всі треті значення після порівняння.

Увага!

Важливо знати, що ПЕРЕГЛЯД функціонувати належним чином, оскільки будь-яка невелика помилка в аргументах не дасть вам бажаного результату. Ви можете скористатися командою МАТЧ і ІНДЕКС функціонує як альтернатива ПЕРЕГЛЯД функції.

Висновок

Сподіваюся, описані вище процедури будуть достатніми для того, щоб порівняти/знайти збіги в двох колонках в Excel, використовуючи функцію ПЕРЕГЛЯД Не соромтеся задавати будь-які питання в розділі коментарів та залишати свої відгуки. Відвідайте сайт, щоб дізнатися більше.

Hugh West

Г’ю Вест — досвідчений тренер і аналітик Excel із понад 10-річним досвідом роботи в галузі. Він має ступінь бакалавра з бухгалтерського обліку та фінансів і ступінь магістра з ділового адміністрування. Г’ю має пристрасть до викладання та розробив унікальний підхід до викладання, який легко зрозуміти та дотримуватися. Його експертне знання Excel допомогло тисячам студентів і професіоналів у всьому світі вдосконалити свої навички та досягти успіху в кар’єрі. У своєму блозі Г’ю ділиться своїми знаннями зі світом, пропонуючи безкоштовні навчальні посібники з Excel та онлайн-навчання, щоб допомогти окремим особам і компаніям повністю розкрити свій потенціал.