Типове завдання, яке періодично постає перед кожним користувачем Excel - порівняти два діапазони з даними і знайти відмінності між ними. Спосіб розв'язання, в даному випадку, визначається типом вихідних даних.
Варіант 1. Синхронні списки
Якщо списки синхронізовані (відсортовані), то все робиться дуже просто, тому що необхідно, власне, порівнювати значення в сусідніх осередках кожного рядка. Як найпростіший варіант використовуємо формулу порівняння значень, яка видає на виході логічні значення ІСТИНА (ПРАВДА) or БРЕХНЯ (ПОМИЛКОВИЙ):
Кількість невідповідностей можна розрахувати за формулою:
=SUMPRODUCT(—(A2:A20<>B2:B20))
або англійською =SUMPRODUCT(—(A2:A20<>B2:B20))
Якщо результат дорівнює нулю, списки ідентичні. В іншому вони мають відмінності. Формула повинна бути введена як формула масиву, тобто після введення формули в клітинку не натискайте Що натомість? Створіть віртуальну версію себе у , І Ctrl + Shift + Enter.
Якщо вам потрібно щось зробити з різними осередками, то підійде інший швидкий спосіб: виділіть обидва стовпці та натисніть клавішу F5, потім у вікні, що відкрилося кнопка Виділіть (Спеціальний) - Лінійні відмінності (Відмінності рядків). В останніх версіях Excel 2007/2010 також можна використовувати кнопку Знайдіть і виберіть (Знайти та вибрати) – Виділення групи клітинок (Перейти до спеціального) таб Головна (Головна)
Excel виділить комірки, що відрізняються за вмістом (по рядку). Потім їх можна обробити, наприклад:
- заповнити кольором або якось візуально відформатувати
- очистити ключем видаляти
- заповніть все одночасно тим самим значенням, ввівши його та натиснувши Ctrl + Enter
- видалити всі рядки з виділеними клітинками за допомогою команди Головна — Видалити — Видалити рядки з аркуша (Домашня сторінка — Видалити — Видалити рядки)
- і т.п.
Варіант 2: перетасовані списки
Якщо списки різного розміру і не відсортовані (елементи розташовані в іншому порядку), то вам потрібно піти іншим шляхом.
Найпростішим і найшвидшим рішенням є підсвічування різниць кольором за допомогою умовного форматування. Виберіть обидва діапазони з даними та виберіть на вкладці головна сторінка – Умовне форматування – Правила виділення комірок – Повторювані значення:
Якщо вибрати опцію Поточний, тоді Excel виділить збіги в наших списках, якщо така опція унікальні – відмінності.
Кольорове виділення, однак, не завжди зручно, особливо для великих столів. Крім того, якщо елементи можуть повторюватися всередині самих списків, цей метод не працюватиме.
Крім того, ви можете скористатися функцією COUNTIF (COUNTIF) з категорії Статистичний, який підраховує, скільки разів кожен елемент із другого списку зустрічається в першому:
Отриманий нуль вказує на відмінності.
І, нарешті, «вищий пілотаж» – відмінності можна вивести окремим списком. Для цього вам доведеться використовувати формулу масиву:
Виглядає страшно, але виконує роботу ідеально 😉
- Виділіть дублікати у списку кольором
- Порівняння двох діапазонів із надбудовою PLEX
- Заборона на введення повторюваних значень