методи порівняння таблиць у Microsoft Excel

Зміст

• Способи порівняння
  • Спосіб 1: проста формула
  • Спосіб 2: виділення груп осередків
  • Спосіб 3: умовне форматування
  • Спосіб 4: комплексна формула
  • Спосіб 5: порівняння масивів у різних книгах
• Питання та відповіді

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

Читайте також: порівняння двох документів у MS Word

Способи порівняння

Існує досить багато способів порівняння табличних областей в Excel, але всі їх можна розділити на три великі групи:

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

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

Спосіб 1: проста формула

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

Отже, маємо дві прості таблиці зі списками працівників підприємства та їх окладами. Потрібно порівняти списки співробітників і виявити невідповідності між стовпцями, в яких розміщені прізвища.

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

   =A2=D2

   

   Хоча, звичайно, в кожному конкретному випадку координати будуть відрізнятися, але суть залишиться однаковою.

2. Клацаємо по клавіші Enter , щоб отримати результати порівняння. Як бачимо, при порівнянні перших осередків обох списків програма вказала показник «правда» , що означає збіг даних.

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

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


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

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

6. У вікні майстри функцій у групі операторів »Математичні" виділяємо найменування СУММВИРОБ . Клацаємо по кнопці «OK» .

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

   =СУМПРОІЗВ(масив1; масив2;...)

   Всього в якості аргументів можна використовувати адреси до 255 масивів. Але в нашому випадку ми будемо використовувати всього два масиви, до того ж, як один аргумент.

   Ставимо курсор в поле " Масив1» і виділяємо на аркуші порівнюваний діапазон даних в першій області. Після цього в поле ставимо знак " Не дорівнює» ( <> ) і виділяємо порівнюваний діапазон другої області. Далі обворачіваем отримане вираз дужками, перед якими ставимо два знака «-» . У нашому випадку вийшло такий вираз:

   --(A2:A7<>D2:D7)

   Клацаємо по кнопці «OK» .


8. Оператор робить розрахунок і виводить результат. Як бачимо, в нашому випадку результат дорівнює числу «1» , тобто, це означає, що в порівнюваних списках було знайдено одне розбіжність. Якби списки були повністю ідентичними, то результат би дорівнював числу «0» .

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

=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: комплексна формула

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

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

=COUNTIF (діапазон; критерій)

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

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

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

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

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

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

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

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


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

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

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

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

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

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

2. Відкривається вікно аргументів функції якщо . Як бачимо, перше поле вікна вже заповнене значенням оператора COUNTIF . Але нам потрібно дописати дещо ще в це поле. Встановлюємо туди курсор і до вже існуючого виразу дописуємо «=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 для того, щоб відкрити обидва вікна одночасно, потрібно провести додаткові маніпуляції. Як це зробити розповідається в окремому уроці.

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

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