Як порівняти два стовпці на різних аркушах. Порівняння даних в Excel на різних аркушах

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

Домислюючи умови завдання найпоширенішими обставинами, додатково встановимо, що:

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

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

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

Наприклад, при порівнянні таблиць значень, отриманих за оборотно-сальдовою відомістю рахунку обліку сировини та матеріалів, вимірами будуть колонки, що містять номенклатуру та склад, а ресурсами - залишки та обороти рахунку. А при порівнянні табличних частин «Товари» вимірами будуть номенклатура, характеристика та серія, а ресурсами – всі інші реквізити цієї табличної частини. І тоді шляхом порівняння версій табличних частин можна буде сказати, що така номенклатура була видалена або додана, а така - змінена.

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

Після довгих коливань було прийнято таке рішення: результатом порівняння двох таблиць Таблиця0 і Таблиця1 має бути таблиця «Різниця» тієї ж структури, що і таблиці, що порівнюються. «Різниця» повинна містити рядки двох таблиць, що відрізняються (віддалені, додані, змінені). При цьому в додатковому стовпці «Знак» має стояти позначка: 0 - якщо рядок є в Таблиці0 і 1 - якщо рядок є в Таблиці1. Це можна інтерпретувати як 0 - рядок видалений, 1 - доданий, або 0 - рядок до зміни, 1 - після. Крім того (увага!), рядки з однаковими значеннями вимірювань повинні бути розташовані один під одним, що реалізує зручний для візуального контролю спосіб зв'язування рядків до і після зміни.

Наприклад, якщо порівняти запропонованим способом таблицю "7 клас" з таблицею "8 клас", то має вийти таблиця "Різниця".

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

2. Критерії оцінки та методика випробувань

Головним критерієм оцінки, природно, вибрати час виконання порівняння. Додатковим критерієм може бути простота функції порівняння. Час виконання порівняння можна виміряти спеціально створеною при цьому обробкою. Простоту функцій пропонується оцінювати суб'єктивно.

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

3.Короткий опис порівнюваних методів

Усього для детального тестування було відібрано сім різних методів:

3.1. Згортка та сортування

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

Функція РізницяТаблицьЗначень(Таблиця0, Таблиця1, Вимірювання) Експорт УсіКолонки = "";

Для Кожного Колонка З Таблиця0.

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

Функція РізницяТаблицьЗначень(Таблиця0, Таблиця1, Вимірювання) Експорт УсіКолонки = "";

Для Кожного Колонка З Таблиця0.

ВсіКолонки = Середовище(ВсіКолонки, 2);

Таблиця = Таблиця1.Скопіювати();<>Рядок1[Ресурс] Тоді ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Рядок0);

ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Рядок1);

Перервати КінецьЯкщо КінецьЦикл;

Рядок1.Знак = 0 КінецьЯкщо КінецьЦикл;<>Для кожного рядка1 З таблиці1.ЗнайтиРядки(Новий Структура("Знак", 1)) Цикл ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Рядок1);<>Гілка[Ресурс] Тоді ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Рядок0);

ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Гілка);

Перервати КінецьЯкщо КінецьЦикл;

Гілка.Знак = 0 КінецьЯкщо КінецьЦикл;<>Для кожного рядка1 З таблиці1.ЗнайтиРядки(Новий Структура("Знак", 1)) Цикл ЗаповнитиЗначенняВластивостей(Різниця.Додати(), Рядок1);<>КінецьЦикл;< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1]; Строка2 = Таблица1[Индекс2]; ИначеЕсли РезультатСравнения >Таблиця1.Колонки.Видалити("Знак");< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1] Иначе ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Индекс2 = Индекс2 - 1; Если Индекс2 < 0 Тогда Прервать КонецЕсли; Строка2 = Таблица1[Индекс2] КонецЕсли КонецЦикла; Пока Индекс1 >Повернення Різниця КінецьФункції

3.5. Злиття

Функція заснована на передачі у запит двох таблиць, де вони з'єднуються за рівністю значень у вимірах. Невелике ускладнення пов'язане з наступною "розгорткою" у два рядки рядків,відрізняються за ресурсами.

Функція Рядки(Рядок, Розділювач) Експорт ПозиціяРозділювача = Знайти(Рядок, Розділювач);<>Т1.Поле()) |

|Упорядкувати за | ()Поле"); СтруктураВимірювань = Новий Структура(Вимірювання); Секції = СтрЧасті(Запрос.Текст, "()"); Запит.Текст = Секції; СтрЗамінити(Секції, "Поле", Колонка.Ім'я) КонецЦикла; Запрос.Текст = Запрос.Текст + Секції; Ім'я) КонецЦикла;Запрос.Текст = Запрос.Текст + Зак. Текст + Секції; Для Кожного Елемент З СтруктураВимірів. СтрЗамінити(Секції, "Поле", Таблиця1.Колонки.Ім'я); Для Кожного Колонка З Таблиця1.Колонки Цикл Якщо НЕ СтруктураВимірів. Колонка.Ім'я) КінецьЯкщо КінецьЦикл;

Запит.Текст = Запит.Текст + Секції;

Функція РізницяТаблицьЗначень(Таблиця0, Таблиця1, Вимірювання) Експорт Запит = Новий Запит("ВИБРАТИ | 0 ЯК Знак, | Т.Поле |ПОМІСТИТИ Т0 |З | &Таблиця0 ЯК Т |; | |//////////// //////////////////////////////////////////////////// ////////////////Вибрати | 1 ЯК Знак, | //////////////////////////////////////////////////// //////////////////// | ВИБРАТИ | |Т.Знак, |Т |Поле | //////////////////////////////////////////////////// | ВИБРАТИ |СУММА(Т.ЗНАК) |З |ПО |

ВсіКолонки = "";

Для кожного Колонка З Таблиця1.Колонки Цикл ВсеКолонки = ВсеКолонки + ", Т." + Колонка.Ім'я КінецьЦикл;

Запит.Текст = СтрЗамінити(Запрос.Текст, "Т.Поле", Середовище(ВсіКолонки, 2));

Запит.Текст = СтрЗаменить(Запрос.Текст, "Поле//", Вимірювання);

Запит.ВстановитиПараметр("Таблиця0", Таблиця0);

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

4.2 Вплив довжини значень

Тепер досліджуємо залежність часу від довжини значень типу рядок. Число рядків покладемо рівним 50000. Інші параметри такі ж, як у 4.1. Результат представимо у вигляді стовпчикової діаграми. Вона краще показує співвідношення часу роботи різних методів і дозволяє виділити лідера, яким у більшості випадків є метод згортки.

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

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

4.3 Вплив типів даних

Наступне цікаве питання – ставлення методів до типів даних. Його вказує наступна діаграма. Тут також число рядків 50000, довжина рядкового та числового значення - 10. Решта як у 4.1.

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

4.3 Вплив числа колонок

Ще одна залежність – це залежність часу порівняння від числа колонок. Її вказує наступна діаграма. Число рядків тут 50000, тип даних - рядок довжини 10, відсоток додавань, спотворень та видалень по 5. Одна ключова колонка.

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

4.4 Вплив кількості вимірів

Цікавіша залежність від числа ключових колонок, наведена нижче. Число рядків тут 50000, тип даних - рядок довжини 10, відсоток додавань, спотворень та видалень по 5. Усього колонок 10.

Видно, що метод на основі відповідності, який раніше показував непогані результати, тепер опиняється в аутсайдерах. Також погіршується злиття. А ось пошук за індексом покращується – за рахунок того, що порівнювати залишається менша кількість колонок.

4.5 Вплив різниці розмірів таблиць

Тепер звернемо увагу на несиметричність методів 1 - 4 (згортки та з'єднання) щодо розмірів порівнюваних таблиць. Всім цим методам вигідніше, щоб перша таблиця була меншою! Це підтверджує наступна таблиця, яка показує час порівняння двох таблиць 50000 та 40000 рядків у різному порядку.

На наведеній діаграмі помітний цікавий артефакт. При даній кількості рядків та стовпців виявляється вигідніше додавати у циклі 50 тисяч рядків до таблиці із 40 тисяч рядків, ніж навпаки. Можливо, це пов'язано з особливостями виділення пам'яті для таблиці значень.

4.6 Вплив кількості відмінностей

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

4.7 Вплив обладнання та програмного оточення

Тести виконувалися на платформі 8.3.5.1248 на ноутбуці VGN-Z51MRG. Отримані залежності загалом підтверджуються на іншому обладнанні, але є й деякі особливості, узагальнити які поки що не вдалося.

5. Висновки

5.1. Найпростіший метод згортки виявляється в більшості випадків і найпродуктивнішим. Його і потрібно використовувати як універсальний метод, але не у спеціальних випадках.

5.2 При малому розмірі (до 50 000 рядків) можна отримати додаткове прискорення згортки, застосувавши копіювання стовпців при об'єднанні таблиць (метод 3.2).

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

5.4 У спеціальному випадку кількох ключових колонок при значній кількості відмінностей та не відсортованості порівнюваних таблиць потрібно використовувати метод з'єднання за індексом.

5.5 Для максимальної ефективності способів 1-4 необхідно вибирати правильний порядок вказівки таблиць при порівнянні.

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

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

5.8 Якщо в таблицях переважають числові дані, дати, середні та довгі рядки, то у запитах порівняння таблиць слід використовувати угруповання, і тільки для дуже коротких рядків – повне з'єднання.

6. Загальні висновки

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

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

6.3 Введення таблиць значень запити може займати значний час, що у більшості випадків зводить нанівець ефективність їх застосування у завданнях, коли дані беруться з пам'яті, а чи не з бази. Бездумне використання запитів у цій задачі – шкідлива помилка.

6.4 Час роботи методу Знайти Рядки за наявності індексу по колонках, які входять у відбір, залежить від розміру таблиці значень. Таким чином, правильною оцінкою швидкодії методу порівняння таблиць з використанням з'єднання за індексом є O(N).

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

Порівняння двох листів у Excel

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

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

Щоб знайти зміни на зарплатних листах:

Після введення всіх умов форматування Excel автоматично виділив кольором тих співробітників зарплати яких змінилися порівняно з попереднім місяцем.

Принцип порівняння двох діапазонів даних в Excel на різних аркушах:

У певній умові важливе значення має функція ПОШУКПОЗ. У її першому аргументі міститься пара значень, яка має бути знайдена на вихідному аркуші наступного місяця, тобто «Березень». Перегляд діапазон визначається як з'єднання значень діапазонів, визначених іменами, в пари. Таким чином виконується порівняння рядків за двома ознаками – прізвище та зарплата. Для знайдених збігів повертається число, що насправді для Excel є істиною. Тому слід використовувати функцію =НЕ(), яка дозволяє замінити значення ІСТИНА на брехню. Інакше буде застосовано форматування для осередків, значення яких збіглися. Для кожної не знайденої пари значень (тобто – невідповідність) &B2&$C2 у діапазоні Прізвище&Зарплата, функція ПОШУКПОЗ повертає помилку. Помилкове значення перестав бути логічним значенням. Тому виповзаємо функцію ЕСЛИПОМИЛКА, яка надасть логічне значення для кожної помилки - ІСТИНА. Це сприяє присвоєнню нового формату тільки для осередків без збігів значень із зарплати щодо наступного місяця – березня.

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

Використання умовного оператора ЯКЩО

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

Розмістіть обидва стовпці для порівняння в колонках A та B робочого листа.

В комірці С2 введіть наступну формулу =ЯКЩО(ПОМИЛКА(ПОШУКПОЗ(A2;$B$2:$B$11;0));"";A2) і протягніть її до комірки С11. Дана формула послідовно переглядає наявність кожного елемента зі стовпця A в стовпці B і повертає значення елемента, якщо його було знайдено в стовпці B.

Використання формули підстановки ВВР

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

Щоб порівняти два стовпці з даними, що знаходяться в стовпцях A і B(аналогічно попередньому способу), введіть наступну формулу =ВПР(A2;$B$2:$B$11;1;0) в комірку С2 і протягніть її до комірки С11.

Дана формула переглядає кожен елемент з основного масиву в порівнюваному масиві і повертає його значення, якщо воно було знайдено в стовпці B. В іншому випадку Excel поверне помилку #Н/Д.

Використання макросу VBA

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

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

Щоб використовувати макрос, поверніться на робочий лист, виділіть основний діапазон (у нашому випадку, це комірки A1:A11), натисніть клавіші Alt+F8. У діалоговому вікні виберіть макрос Find_Matchesі натисніть кнопку виконати.

Після виконання макросу, результат має бути наступним:

Використання надбудови Inquire

Підсумок

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

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

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

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

Порівняємо два стовпці цифрових значень, у яких відмінність є лише кількох осередках. Записавши просту формулу в сусідньому стовпці, умова рівності двох осередків "=B3=C3", ми отримаємо результат «ІСТИНА», якщо вміст осередків однаковий, і «БРЕХНЯ», якщо вміст комірок відрізняється. Розтягнувши формулу по всій висоті стовпця порівнюваних значень дуже легко буде знайти осередок, що відрізняється.

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

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

У диспетчері правил вибираємо пункт "Створити правило", а у створенні правил вибираємо . Тепер ми можемо задати формулу «=$B3<>$ C3»для визначення комірки, що форматується, і задати для неї формат, натиснувши на кнопку «Формат».

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

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

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

Порядок дій наступний, виділяємо перший набір даних, який ми називаємо «Стовпець 1», і в меню "Умовне форматування"вибираємо пункт "Створити правило ...". У віконці, що з'явилося, вибираємо , вписуємо необхідну формулу «=РАХУНОКИ($C$3:$D$12;A3)=0»та вибираємо спосіб форматування.

У нашій формулі використовується функція «РАХУНОК», яка підраховує кількість повторень значення з певного осередку "A3"у заданому діапазоні "$C$3:$D$12", Яким виступає наш другий стовпець. Як порівнювана комірка необхідно вказувати першу комірку з діапазону значень, до яких буде застосовуватися форматування.

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

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

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

Необхідна нам формула виглядатиме так: «=ВПР(B18;$B$3:$C$10;2;БРЕХНЯ)». Перше значення «B18»відповідає першому осередку шуканого найменування товару. Друге значення "$B$3:$C$10"означає постійну адресу діапазону таблиці старого прайсу, значення якої нам знадобляться. Третє значення «2»означає номер стовпця з виділеного діапазону, в комірці якого ми і братимемо стару ціну товару. І останнє значення «БРЕХНЯ»задає пошук лише з точному збігу значень. Після протягування формули по всьому стовпцю нової таблиці ми отримаємо в цьому стовпці старі значення цін за кожною позицією, що є в новій таблиці. Навпаки назви останнього товару формула виводить повідомлення помилки «#Н/Д», що свідчить про відсутність цього найменування у старому прайсі.

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