Порівняння таблиць значень 1с 8. Як порівняти два стовпці в Excel на збіги

Читання цієї статті займе близько 10 хвилин. У наступні 5 хвилин Ви зможете легко порівняти два стовпці в Excel і дізнатися про наявність дублікатів, видалити їх або виділити кольором. Отже, час настав!

Excel – це дуже потужний і справді крутий додаток для створення та обробки великих масивів даних. Якщо у Вас є кілька робочих книг з даними (або тільки одна величезна таблиця), то, ймовірно, Ви захочете порівняти 2 стовпці, знайти значення, що повторюються, а потім зробити з ними які-небудь дії, наприклад, видалити, виділити кольором або очистити вміст . Стовпці можуть бути в одній таблиці, бути суміжними або не суміжними, можуть бути розташовані на 2-х різних аркушах або навіть у різних книгах.

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

Варіант А:обидва стовпці знаходяться на одному аркуші. Наприклад, стовпець Aі стовпець B.

Варіант В:Стовпці розташовані на різних аркушах. Наприклад, стовпець Aна аркуші Sheet2і стовпець Aна аркуші Sheet3.

В Excel 2013, 2010 та 2007 є вбудований інструмент Remove Duplicate(Видалити дублікати), але він безсилий у такій ситуації, оскільки не може порівнювати дані у 2 стовпцях. Більше того, він може лише видалити дублікати. Інших варіантів, таких як виділення чи зміна кольору, не передбачено. І крапка!

Порівнюємо 2 стовпці в Excel і знаходимо записи, що повторюються, за допомогою формул

Варіант А: обидва стовпці знаходяться на одному аркуші


Підказка:У великих таблицях скопіювати формулу вийде швидше, якщо використовувати комбінації клавіш. Виділіть комірку C1та натисніть Ctrl+C(щоб скопіювати формулу в буфер обміну), потім натисніть Ctrl+Shift+End(щоб виділити всі не порожні осередки в стовпі С) і, нарешті, натисніть Ctrl+V(щоб вставити формулу у всі виділені комірки).


Варіант В: два стовпці знаходяться на різних аркушах (у різних книгах)

Обробка знайдених дублікатів

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

Показати тільки рядки, що повторюються, в стовпці А

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

Клацніть правою кнопкою миші та в контекстному меню виберіть Insert(Вставити):

Дайте назви стовпцям, наприклад, “ Name” та “ Duplicate?” Потім відкрийте вкладку Data(Дані) та натисніть Filter(Фільтр):

Після цього натисніть дрібну сіру стрілку поруч із “ Duplicate?“, щоб відкрити меню фільтра; зніміть галочки з усіх елементів цього списку, крім Duplicate, та натисніть ОК.

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

Щоб знову відобразити всі рядки стовпця А, натисніть символ фільтра в стовпці У, який тепер виглядає як вирва з маленькою стрілочкою і виберіть Select all(Виділити все). Або Ви можете зробити те саме через Стрічку, натиснувши Data(Дані) > Select & Filter(Сортування та фільтр) > Clear(Очистити), як показано на знімку екрана:

Зміна кольору або виділення знайдених дублікатів

Якщо позначки “ Duplicate” не достатньо для Ваших цілей, і Ви хочете відзначити комірки, що повторюються, іншим кольором шрифту, заливки або будь-яким іншим способом…

У цьому випадку відфільтруйте дублікати, як показано вище, виділіть усі відфільтровані осередки та натисніть Ctrl+1, щоб відкрити діалогове вікно Format Cells(Формат осередків). Як приклад, давайте змінимо колір заливки осередків у рядках із дублікатами на яскраво-жовтий. Звичайно, Ви можете змінити колір заливки за допомогою інструмента Fill(Колір заливки) на вкладці Home(Головна), але перевага діалогового вікна Format Cells(Формат комірок) у тому, що можна налаштувати одночасно всі параметри форматування.

Тепер Ви точно не пропустите жодного осередку з дублікатами:

Видалення значень, що повторюються, з першого стовпця

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

Якщо 2 стовпці, які Ви порівнюєте, знаходяться на різних аркушах, тобто в різних таблицях, клацніть правою кнопкою миші виділений діапазон і в контекстному меню виберіть Delete Row(Видалити рядок):

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

Якщо 2 стовпці розташовані на одному аркуші, впритул один одному (суміжні) або не впритул один до одного (не суміжні), то процес видалення дублікатів буде трохи складнішим. Ми не можемо видалити весь рядок з значеннями, що повторюються, оскільки так ми видалимо осередки і з другого стовпця теж. Отже, щоб залишити лише унікальні записи у стовпці А, зробіть таке:

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

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

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

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

Як робочий приклад візьмемо файл із показниками вигаданих учасників: біг на 100 метрів, біг на 3000 метрів та підтягування. Перший файл – це замір на початку сезону, а другий – кінець сезону.

Перший спосіб вирішення поставленого завдання. Рішення лише силами формул MS Excel.

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

Для порівняння показників бігу на 100 метрів формула виглядає так:
=ЯКЩО(ВПР($B2;Sheet2!$B$2:$F$13;3;ІСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ІСТИНА);"Різниці немає")
Якщо різниці немає, виводиться повідомлення, що різниці немає, якщо вона присутня, тоді від значення в кінці сезону віднімається показник початку сезону.

Формула для бігу на 3000 метрів виглядає так:
=ЯКЩО(ВПР($B2;Sheet2!$B$2:$F$13;4;ІСТИНА)<>E2; "Різниця є"; "Різниці немає")
Якщо кінцеве та початкове значення не дорівнюють виводиться відповідне повідомлення. Формула для підтягувань може бути аналогічна будь-якій із попередніх, додатково наводити її сенсу немає. Кінцевий файл зі знайденими розбіжностями наведено нижче.

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

Відео порівняння двох файлів MS Excel за допомогою функцій та .

Другий спосіб розв'язання задачі. Рішення з допомогою MS Access.

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

Останній являє собою зв'язок файлів Excel і Access, тому при зміні даних у файлах Excel розбіжності будуть знаходитися автоматично при виконанні запиту в MS Access.

Наступним кроком після здійснення імпорту буде створення зв'язків між таблицями. Як сполучне поле вибираємо унікальне поле «№ п/п».
Третім кроком буде створення простого запиту вибірку за допомогою конструктора запитів.

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

Відео порівняння файлів MS в Excel за допомогою MS Access.

В результаті виконаних маніпуляцій виведено всі записи, з різними даними в полі: «Біг на 100 метрів». Файл MS Access представлений нижче (на жаль, впровадити як файл Excel, SkyDrive не дозволяє)

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

Для того щоб перевірити, чи містять два стовпці одного рядка, однакові дані нам знадобиться формула:

=ЯКЩО(A2=B2; “Збігаються”; “”)

Формула, що визначає відмінності між даними двох стовпців в одному рядку виглядатиме так:

=ЯКЩО(A2<>B2; "Не збігаються"; "")

Ми можемо вмістити перевірку на збіги та різницю між двома стовпцями в одному рядку в одній формулі:

=ЯКЩО(A2=B2; “Збігаються”; “Не збігаються”)

=ЯКЩО(A2<>B2; "Не збігаються"; "Збігаються")

Приклад результату обчислень може мати такий вигляд:

Для порівняння даних у двох стовпцях одного рядка з урахуванням регістру слід використовувати формулу:

=ЯКЩО(СПІД(A2,B2); “Збігається”; “Унікальне”)

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

В Excel є можливість порівняти дані в кількох стовпцях одного рядка за такими критеріями:

  • Знайти рядки з однаковими значеннями у всіх стовпцях таблиці;
  • Знайти рядки з однаковими значеннями у будь-яких двох стовпцях таблиці;

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

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

=ЯКЩО(І(A2=B2;A2=C2); “Збігаються”; ” “)

Якщо в нашій таблиці дуже багато стовпців, то більш просто використовувати функцію в поєднанні з :

=ЯКЩО(ПОЛІЧИЛИ($A2:$C2;$A2)=3;”Збігаються”;” “)

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

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

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

=ЯКЩО(АБО(A2=B2;B2=C2;A2=C2);”Збігаються”;” “)

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

=ЯКІ(ЛІЧИЛИ(B2:D2;A2)+ЛІЧІСЛИ(C2:D2;B2)+(C2=D2)=0; “Унікальний рядок”; “Не унікальний рядок”)

=ЯКЩО(ПОЛІЧИЛИ($B:$B;$A5)=0; “Немає збігів у стовпці B”; “Є збіги в стовпці В”)

Ця формула перевіряє значення у стовпці B на збіг з даними осередків у стовпці А.

Якщо таблиця складається з фіксованого числа рядків, ви можете вказати у формулі чіткий діапазон (наприклад, $B2:$B10). Це дозволить прискорити роботу формули.

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

Коли ми шукаємо збіги між двома стовпцями в Excel, нам може знадобитися візуалізувати знайдені збіги або відмінності даних, наприклад, за допомогою виділення кольором. Найпростіший спосіб виділення кольором збігів і відмінностей – використовувати “Умовне форматування” в Excel. Розглянемо як це зробити на прикладах нижче.

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

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

  • Виділити стовпці з даними, у яких слід обчислити збіги;
  • На вкладці "Головна" на Панелі інструментів натискаємо на пункт меню "Умовне форматування" -> "Правила виділення осередків" -> "Повторювані значення";
  • У спливаючому діалоговому вікні виберіть у лівому випадаючому списку пункт “Повторювані”, у правому списку виберіть яким кольором будуть виділені повторювані значення. Натисніть кнопку “ОК”:
  • Після цього у виділеній колонці будуть підсвічені кольором збігу:

Пошук і виділення кольором рядків, що збігаються в Excel

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

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

Розглянемо як знайти рядки, що збігаються в таблиці:

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

=A2&B2&C2&D2

У допоміжному стовпчику ви побачите об'єднані дані таблиці:

Тепер, для визначення рядків, що збігаються в таблиці, зробіть наступні кроки:

  • Виділіть область з даними у допоміжній колонці (у нашому прикладі це діапазон осередків E2:E15 );
  • На вкладці "Головна" на Панелі інструментів натискаємо на пункт меню "Умовне форматування" -> "Правила виділення осередків" -> "Повторювані значення";
  • У спливаючому діалоговому вікні виберіть у лівому випадаючому списку “Повторювані”, у правому списку виберіть яким кольором будуть виділені повторювані значення. Натисніть кнопку “ОК”:
  • Після цього у виділеній колонці будуть підсвічені рядки, що дублюються:


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

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

Перший список - А2:В31, цей діапазон називається OldList. Другий список - D2:E31, діапазон називається NewList. Діапазони були названі за допомогою команди Формули Певні імена Присвоїти ім'я. Давати назви діапазонам необов'язково, але це полегшує роботу з ними.

Почнемо з додавання умовного форматування до старого списку.

  1. Виділіть комірки діапазону OldList.
  2. Виберіть .
  3. У вікні Створення правила форматуваннявиберіть пункт під назвою Використати формулу
  4. Введіть цю формулу в поле вікна (рис. 164.2): = РАХУНКИ (NewList; A2) = 0 .
  5. Натисніть кнопку Форматі задайте форматування, яке буде застосовуватися, коли умова є істинною. Найкраще вибрати різні кольори заливки.
  6. Натисніть кнопку ОК.

Осередки в діапазоні NewListвикористовують подібну формулу умовного форматування.

  1. Виділіть комірки діапазону NewList.
  2. Виберіть Головна Умовне форматування Створити правилодля відкриття діалогового вікна Створення правила форматування.
  3. У вікні Створення правилаформатування виберіть пункт Використати формулувизначення форматованих осередків.
  4. Введіть цю формулу у вікні: =РАХУНКИ(OldList;D2)=0 .
  5. Натисніть кнопку Форматі задайте форматування, яке буде застосовуватися, коли умова є істинною (інший колір заливки).
  6. Натисніть кнопку ОК.

В результаті імена, які знаходяться в старому списку, але яких немає в новому, будуть виділені (рис. 164.3). Крім того, імена у новому списку, яких немає в старому, також виділені, але іншим кольором. Імена, які з'являються в обох списках, не виділено.

Обидві формули умовного форматування використовують функцію ЗЛІЧИЛИ. Вона розраховує, скільки разів певне значення з'являється в діапазоні. Якщо формула повертає 0, це означає, що елемент не входить до діапазону. Таким чином, умовне форматування береться до справи, і колір фону комірки змінюється.



Поділитися з друзями:
Подібні публікації