Порівняння таблиць в Excel

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

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

= B2 = Аркуш2! B2

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

Спосіб 2: виділення груп осередків

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

1. Виділяємо порівнювані масиви. Переходимо у вкладку «Головна». Далі клацаємо по значку «Знайти і виділити», який розташовується на стрічці в блоці інструментів «Редагування». Відкривається список, в якому слід вибрати позицію «Виділення групи осередків ...».

   

   Крім того, в потрібне нам вікно виділення групи осередків можна потрапити і в інший спосіб. Даний варіант особливо буде корисний тим користувачам, у яких встановлена ​​версія програми раніше Excel 2007, так як метод через кнопку «Знайти і виділити» ці програми не підтримують. Виділяємо масиви, які бажаємо порівняти, і тиснемо на клавішу F5.

2. Активується невелике віконце переходу. Клацаємо по кнопці «Виділити ...» в його нижньому лівому кутку.



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



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

Спосіб 3: умовне форматування

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

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



2. Активується віконце диспетчера правил. Тиснемо в ньому на кнопку «Створити правило».



3. У запустити вікні виробляємо вибір позиції «Використовувати формулу». В поле «Форматувати осередки» записуємо формулу, яка містить адреси перших осередків діапазонів порівнюваних стовпців, розділені знаком «не дорівнює» (). Тільки перед цим виразом на цей раз буде стояти знак «=». Крім того, до всіх до координат стовпців в цій формулі потрібно застосувати абсолютну адресацію. Для цього виділяємо формулу курсором і тричі тиснемо на клавішу F4. Як бачимо, за будь-яких адрес стовпців з'явився знак долара, що і означає перетворення посилань в абсолютні. Для нашого конкретного випадку формула прийме наступний вигляд:

   = $ A2 $ D2

   Цей вираз ми і записуємо в вищевказане поле. Після цього клацаємо по кнопці «Формат ...».



4. Активується вікно «Формат ячеек». Йдемо у вкладку «Заливка». Тут в переліку квітів зупиняємо вибір на кольорі, яким хочемо фарбувати ті елементи, де дані не будуть збігатися. Тиснемо на кнопку «OK».



5. Повернувшись у вікно створення правила форматування, тиснемо на кнопку «OK».



6. Після автоматичного переміщення в вікно «Диспетчера правил» клацаємо по кнопці «OK» і в ньому.



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

Існує ще один спосіб застосування умовного форматування для виконання поставленого завдання. Як і попередні варіанти, він вимагає розташування обох порівнюваних областей на одному аркуші, але на відміну від раніше описаних способів, умова синхронізації або сортування даних не буде обов'язковим, що вигідно відрізняє даний варіант від раніше описаних.

1. Виробляємо виділення областей, які потрібно порівняти.



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



3. Запускається вікно настройки виділення повторюваних значень. Якщо ви все зробили правильно, то в даному вікні залишається тільки натиснути на кнопку «OK». Хоча при бажанні в відповідному полі даного віконця можна вибрати інший колір виділення.



4. Після того, як ми проведемо вказане дію, все повторювані елементи будуть виділені вибраним кольором. Ті елементи, які не збігаються, залишаться пофарбованими в свій початковий колір (за замовчуванням білий). Таким чином, можна відразу візуально побачити, в чому відмінність між масивами.

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

Таким чином, будуть виділені саме ті показники, які не збігаються.

Урок: Умовне форматування в Ексель

Спосіб 4: комплексна формула

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

Оператор СЧЁТЕСЛІ відноситься до статистичної групі функцій. Його завданням є підрахунок кількості осередків, значення в яких задовольняють заданій умові. Синтаксис цього оператора має такий вигляд:

= СЧЁТЕСЛІ (діапазон; критерій)

Аргумент «Діапазон» є адреса масиву, в якому проводиться підрахунок співпадаючих значень.

Аргумент «Критерій» задає умову збігу. У нашому випадку він буде являти собою координати конкретних осередків першої табличній області.

1. Виділяємо перший елемент додаткового стовпця, в якому буде проводитися підрахунок кількості збігів. Далі клацаємо по піктограмі «Вставити функцію».



2. Відбувається запуск Майстра функцій. Переходимо в категорію «Статистичні». Знаходимо в переліку найменування «СЧЁТЕСЛІ». Після його виділення клацаємо по кнопці «OK».



3. Відбувається запуск вікна аргументів оператора СЧЁТЕСЛІ. Як бачимо, найменування полів в цьому вікні співпадають з назвами аргументів.

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

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

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



4. В елемент листа виводиться результат. Він дорівнює числу «1». Це означає, що в переліку імен другий таблиці прізвище «Гриньов В. П.», яка є першою в списку першого табличного масиву, зустрічається один раз.



5. Тепер нам потрібно створити подібний вираз і для всіх інших елементів першої таблиці. Для цього виконаємо копіювання, скориставшись маркером заповнення, як це ми вже робили раніше. Ставимо курсор в нижню праву частину елемента листа, який містить функцію СЧЁТЕСЛІ, і після перетворення його в маркер заповнення затискаємо ліву кнопку миші і тягнемо курсор вниз.



6. Як бачимо, програма справила обчислення збігів, порівнявши кожну клітинку першої таблиці з даними, які розташовані в другому табличному діапазоні. У чотирьох випадках результат вийшов «1», а в двох випадках - «0». Тобто, програма не змогла відшукати в другій таблиці два значення, які є в першому табличному масиві.

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

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

1. Перш за все, трохи переробимо нашу формулу СЧЁТЕСЛІ, а саме зробимо її одним з аргументів оператора ЯКЩО. Для цього виділяємо перший осередок, в якій розташований оператор СЧЁТЕСЛІ. У рядку формул перед нею дописуємо вираз «ЯКЩО» без лапок і відкриваємо дужку. Далі, щоб нам легше було працювати, виділяємо в рядку формул значення «ЯКЩО» і тиснемо на іконку «Вставити функцію».



2. Відкривається вікно аргументів функції ЯКЩО. Як бачимо, перше поле вікна вже заповнене значенням оператора СЧЁТЕСЛІ. Але нам потрібно дописати дещо ще в це поле. Встановлюємо туди курсор і до вже існуючого висловом дописуємо «= 0» без лапок.

   Після цього переходимо до поля «Значення якщо істина». Тут ми скористаємося ще однією вкладеної функцією - СТРОКА. Вписуємо слово «СТРОКА» без лапок, далі відкриваємо дужки і вказуємо координати першого осередку з прізвищем в другій таблиці, після чого закриваємо дужки. Саме в нашому випадку в поле «Значення якщо істина» вийшло такий вираз:

   СТРОКА (D2)

   Тепер оператор СТРОКА буде повідомляти функції ЯКЩО номер рядка, в якій розташована конкретне прізвище, і в разі, коли умова, заданий в першому полі, буде виконуватися, функція ЯКЩО буде виводити цей номер в клітинку. Тиснемо на кнопку «OK».



3. Як бачимо, перший результат відображається, як «БРЕХНЯ». Це означає, що значення не задовольняє умовам оператора ЯКЩО. Тобто, перша прізвище присутній в обох списках.



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



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



6. Після цього виділяємо перший осередок праворуч від колонки з номерами і клацаємо по значку «Вставити функцію».



7. Відкривається Майстер функцій. Переходимо в категорію «Статистичні» і виробляємо вибір найменування «НАЙМЕНШИЙ». Клацаємо по кнопці «OK».



8. Функція НАЙМЕНШИЙ, вікно аргументів якої було розкрито, призначена для виведення зазначеного за рахунком найменшого значення.

   В поле «Масив» слід вказати координати діапазону додаткового колонки «Кількість збігів», який ми раніше перетворили за допомогою функції ЯКЩО. Робимо все посилання абсолютними.

   В поле «K» вказується, яке за рахунком найменше значення потрібно вивести. Тут вказуємо координати першої комірки стовпчика з нумерацією, який ми недавно додали. Адреса залишаємо відносним. Клацаємо по кнопці «OK».



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



10. Тепер, знаючи номера рядків незбіжних елементів, ми можемо вставити в клітинку і їх значення за допомогою функції ІНДЕКС. Виділяємо перший елемент листа, що містить формулу НАЙМЕНШИЙ. Після цього переходимо до рядка формул і перед найменуванням «НАЙМЕНШИЙ» дописуємо назву «ІНДЕКС» без лапок, тут же відкриваємо дужку і ставимо крапку з комою (;). Потім виділяємо в рядку формул найменування «ІНДЕКС» і натискаємо на піктограму «Вставити функцію».



11. Після цього відкривається невелике віконце, в якому потрібно визначити, контрольний вид повинна мати функція ІНДЕКС або призначений для роботи з масивами. Нам потрібен другий варіант. Він встановлений за замовчуванням, так що в даному віконці просто клацаємо по кнопці «OK».



12. Запускається вікно аргументів функції ІНДЕКС. Даний оператор призначений для виведення значення, яке розташоване в певному масиві в зазначеному рядку.

    Як бачимо, поле «Номер рядка» вже заповнене значеннями функції НАЙМЕНШИЙ. Від вже існуючого там значення слід відняти різницю між нумерацією листа Excel і внутрішньої нумерацією табличній області. Як бачимо, над табличними значеннями у нас тільки шапка. Це означає, що різниця становить один рядок. Тому дописуємо в поле «Номер рядка» значення «-1» без лапок.

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

    Тиснемо на кнопку «OK».



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

Спосіб 5: порівняння масивів в різних книгах

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

Урок: Як відкрити Ексель в різних вікнах

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