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

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

Порівняння двох списків в Excel

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

Постановка проблеми

Тому нам потрібно порівняти ці стовпці. Процедура порівняння двох документів така:

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

Усі ці три умови одночасно є умовами нашої задачі.

Рішення задачі

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

Щоб порівняти два списки, виконайте такі дії:

1. В окремому стовпці ми створюємо список унікальних значень, характерних для обох списків. Для цього використовуємо формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Сама формула повинна бути записана у вигляді формули масиву.
2. Давайте визначимо, скільки разів кожне унікальне значення зустрічається в масиві даних. Ось формули для цього: =COUNTI(список1;D5) і =COUNTI(список2;D5).
3. Якщо і кількість повторень, і кількість унікальних значень однакові у всіх списках, які входять до цих діапазонів, тоді функція повертає значення 0. Це вказує на те, що відповідність становить XNUMX%. У цьому випадку заголовки цих списків отримають зелений фон.
4. Якщо весь унікальний вміст є в обох списках, то повертається формулами =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) значення буде нульовим. Якщо E1 не містить нуля, але таке значення міститься в комірках E2 і F2, то в цьому випадку діапазони будуть визнані співпадаючими, але лише частково. У цьому випадку заголовки відповідних списків стануть помаранчевими.
5. І якщо одна з формул, описаних вище, повертає ненульове значення, списки будуть абсолютно невідповідними.

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

Приклад тестування

У нашій версії таблиці є три типи списків кожного типу, описаного вище. Має часткові та повні збіги, а також незбіги.

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

Пошук відмінностей у двох списках двома способами

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

Варіант 1. Синхронні списки

Це простий варіант. Припустимо, у нас є такі списки.

Щоб визначити, скільки разів значення не зійшлися, можна скористатися формулою: =SUMPRODUCT(—(A2:A20<>B2:B20)). Якщо в результаті ми отримали 0, це означає, що два списки однакові.

Варіант 2: перетасовані списки

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

Як порівняти 2 стовпці рядок за рядом

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

приклад. Як порівняти 2 стовпці на збіги та відмінності в одному рядку

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

Нам слід записати таку формулу, щоб зрозуміти, однакові значення в обох стовпцях чи ні: =ЯКЩО(A2=B2, “Збіг”, “”). Логіка цієї функції дуже проста: вона порівнює значення в комірках A2 і B2, і якщо вони збігаються, виводить значення «Збігається». Якщо дані відрізняються, значення не повертається. Ви також можете перевірити клітинки, щоб побачити, чи є відповідність між ними. У цьому випадку використовується така формула: =IF(A2<>B2, “Не збігається”, “”). Принцип той же, спочатку проводиться перевірка. Якщо виявиться, що комірки відповідають критерію, то виводиться значення «Не відповідає».

Також можна використовувати наступну формулу в полі формули, щоб відобразити як «Збіг», якщо значення однакові, так і «Не збігатися», якщо вони різні: =IF(A2=B2; «Збіг», «Не збіг»). Ви також можете використовувати оператор нерівності замість оператора рівності. Тільки порядок значень, які будуть відображатися в цьому випадку, буде дещо іншим: =IF(A2<>B2, “Не збігається”, “Збігається”). Після використання першого варіанту формули результат буде наступним.

Цей варіант формули не враховує регістр. Тому, якщо значення в одному стовпці відрізняються від інших тільки тим, що вони написані великими літерами, то програма цієї різниці не помітить. Щоб зробити порівняння чутливим до регістру, вам потрібно використовувати функцію в критеріях EXACT. Решту аргументів залишити без змін: =ЯКЩО(ТОЧНИЙ(A2;B2), “Збіг”, “Унікальний”).

Як порівняти кілька стовпців на збіги в одному рядку

Аналізувати значення в списках можна за цілим набором критеріїв:

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

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

приклад. Як знайти збіги в одному рядку в кількох стовпцях таблиці

Припустимо, у нас є ряд стовпців, які містять потрібну нам інформацію. Перед нами стоїть завдання визначити ті рядки, в яких значення однакові. Для цього потрібно скористатися такою формулою: =ЯКЩО(І(A2=B2;A2=C2), «збіг», ” “).

Якщо в таблиці занадто багато стовпців, то вам просто потрібно використовувати його разом з функцією IF оператор COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”збіг”;” “). Число, яке використовується в цій формулі, вказує на кількість стовпців, які потрібно перевірити. Якщо він відрізняється, то вам потрібно написати стільки, скільки відповідає вашій ситуації.

приклад. Як знайти збіги в одному рядку в будь-яких 2 стовпцях таблиці

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

Ми використовуємо цю формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Може виникнути ситуація, коли в таблиці багато стовпців. У цьому випадку формула буде величезною, і підбір всіх необхідних комбінацій може зайняти багато часу. Щоб вирішити цю проблему, потрібно скористатися функцією COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; «Унікальний рядок»; «Не унікальний рядок»)

Ми бачимо, що всього у нас дві функції COUNTIF. З першим по черзі визначаємо, скільки стовпців мають подібність до A2, а з другим перевіряємо кількість подібностей зі значенням B2. Якщо в результаті обчислення за цією формулою ми отримуємо нульове значення, це говорить про те, що всі рядки в цьому стовпці унікальні, якщо більше, то є схожість. Отже, якщо в результаті обчислення за двома формулами і складання підсумкових результатів ми отримуємо нульове значення, то повертається текстове значення «Унікальний рядок», якщо це число більше, то пишеться, що цей рядок не є унікальним.

Як порівняти 2 стовпці в Excel на збіги

Тепер візьмемо приклад. Припустимо, у нас є таблиця з двома колонками. Потрібно перевірити, чи збігаються вони. Для цього потрібно застосувати формулу, де також буде використана функція IF, і оператор COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, «Немає збігів у стовпці B», «Є збіги у стовпці B»)

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

Як порівняти 2 стовпці в Excel на збіги та виділити кольором

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

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

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

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

Знаходження та виділення відповідних рядків

Техніка перевірки відповідності рядків дещо відрізняється. По-перше, нам потрібно створити додатковий стовпець, і там ми будемо використовувати комбіновані значення за допомогою оператора &. Для цього потрібно записати формулу виду: =A2&B2&C2&D2.

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

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