Іноді виникає потреба порівняти два файли 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
У фірмі може бути понад сто співробітників, серед яких одні звільняються, інші працевлаштовуються, треті йдуть у відпустку або на лікарняну тощо. Внаслідок чого можуть виникнути труднощі з порівнянням даних із зарплати. Наприклад, прізвища співробітників будуть постійно різною послідовністю. Як зробити порівняння двох таблиць Excelна різних аркушах?
Вирішити це непросте завдання нам допоможе умовне форматування. Наприклад, візьмемо дані за лютий і березень, як показано на малюнку:
Щоб знайти зміни на зарплатних листах:
Після введення всіх умов для форматування Excelавтоматично виділив кольором тих співробітників, зарплати яких змінилися в порівнянні з попереднім місяцем.
Принцип порівняння двох діапазонів даних в Excel на різних аркушах:
У певній умові важливе значення має функція ПОШУКПОЗ. У її першому аргументі міститься пара значень, яка має бути знайдена на вихідному аркуші наступного місяця, тобто «Березень». Перегляд діапазон визначається як з'єднання значень діапазонів, визначених іменами, в пари. Таким чином виконується порівняння рядків за двома ознаками – прізвище та зарплата. Для знайдених збігів повертається число, що насправді для Excel є істиною. Тому слід використовувати функцію =НЕ(), яка дозволяє замінити значення ІСТИНА на брехню. Інакше буде застосовано форматування для осередків, значення яких збіглися. Для кожної не знайденої пари значень (тобто – невідповідність) &B2&$C2 у діапазоні Прізвище&Зарплата, функція ПОШУКПОЗ повертає помилку. Помилкове значення не є логічним значенням. Тому виповзаємо функцію ЕСЛИПОМИЛКА, яка надасть логічне значення для кожної помилки - ІСТИНА. Це сприяє присвоєнню нового формату тільки для осередків без збігів значень із зарплати щодо наступного місяця – березня.
Часто постає завдання порівняти два списки елементів. Робити це вручну занадто втомливо, до того ж не можна виключати ймовірність помилок. Excel полегшує цю операцію. У цій раді описується метод із застосуванням умовного форматування.
На рис. 164.1 наведено приклад двох багатостовпних списків імен. Застосування умовного форматування може зробити відмінності у списках очевидними. Ці приклади списків містять текст, але метод, що розглядається, працює і з числовими даними.
Перший список - А2:В31, цей діапазон називається OldList. Другий список - D2:E31, діапазон називається NewList. Діапазони були названі за допомогою команди Формули Певні імена Присвоїти ім'я. Давати назви діапазонам необов'язково, але це полегшує роботу з ними.
Почнемо з додавання умовного форматування до старого списку.
- Виділіть комірки діапазону OldList.
- Виберіть .
- У вікні Створення правила форматуваннявиберіть пункт під назвою Використати формулу
- Введіть цю формулу в поле вікна (рис. 164.2): = РАХУНКИ (NewList; A2) = 0 .
- Натисніть кнопку Форматі задайте форматування, яке буде застосовуватися, коли умова є істинною. Найкраще вибрати різні кольори заливки.
- Натисніть кнопку ОК.
Осередки в діапазоні NewListвикористовують подібну формулу умовного форматування.
- Виділіть комірки діапазону NewList.
- Виберіть Головна Умовне форматування Створити правилодля відкриття діалогового вікна Створення правила форматування.
- У вікні Створення правилаформатування виберіть пункт Використати формулувизначення форматованих осередків.
- Введіть цю формулу у вікні: =РАХУНКИ(OldList;D2)=0 .
- Натисніть кнопку Форматі задайте форматування, яке буде застосовуватися, коли умова є істинною (інший колір заливки).
- Натисніть кнопку ОК.
В результаті імена, які знаходяться в старому списку, але яких немає в новому, будуть виділені (рис. 164.3). Крім того, імена у новому списку, яких немає в старому, також виділені, але іншим кольором. Імена, які з'являються в обох списках, не виділено.
Обидві формули умовного форматування використовують функцію ЗЛІЧИЛИ. Вона розраховує, скільки разів певне значення з'являється в діапазоні. Якщо формула повертає 0, це означає, що елемент не входить до діапазону. Таким чином, умовне форматування береться до справи, і колір фону комірки змінюється.
Читання цієї статті займе близько 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 за допомогою формул – це не так вже й складно.
Стаття дає відповіді такі питання:
- Як порівняти дві таблиці в Excel?
- Як порівнювати складні таблиці в Excel?
- Як порівняти таблиці в Excel з використанням функції ВПР()?
- Як формувати унікальні ідентифікатори рядків, якщо їхня унікальність спочатку визначається набором значень у кількох стовпцях?
- Як фіксувати значення осередків у формулах при копіюванні формул?
При роботі з великими обсягами інформації користувач може мати справу з таким завданням, як порівняння двох табличних джерел даних. При зберіганні даних у єдиній системі обліку (наприклад, системи на базі 1С Підприємство, системи, що використовують SQL базиданих) для порівняння даних можуть використовуватися вбудовані в систему або СУБД можливості. Як правило, для цього достатньо залучити програміста, який напише запит до бази даних або програмний механізм звіту. З запитом може впоратися і досвідчений користувач, який володіє навичкою написання запитів 1C або SQL.
Проблеми починаються, коли потрібно виконати завдання порівняння даних терміново, а залучення програміста і написання ним запиту чи програмного звіту у часі може перевищити встановлені вирішення завдання терміни. Ще однією не менш поширеною проблемою є потреба порівняння інформації із різних джерел. У такому разі постановка задачі для програміста звучатиме як інтеграція двох систем. Вирішення такої задачі вимагатиме вищої кваліфікації програміста і займе більше часу, ніж розробка в єдиній системі.
Для вирішення зазначених проблем ідеальним прийомом є використання для порівняння даних табличного редактора Microsoft Excel. Більшість поширених систем управлінського та регламентованого обліку підтримують вивантаження у формат Excel. Це завдання вимагатиме лише певної кваліфікації користувача по роботі з цим офісним пакетомі не вимагатиме навичок програмування.
Розглянемо розв'язання задачі порівняння таблиць Excel на прикладі. Ми маємо дві таблиці, що містять переліки квартир. Джерела вивантаження - 1С Підприємство (облік будівництва) та таблиця в Excel (облік продажів). Таблиці розміщені в робочій книзі Excel на першому та другому аркушах відповідно.
Перед нами постає завдання порівняти ці списки за адресою. У першій таблиці – усі квартири будинку. У другій таблиці - лише продані квартири та ім'я покупця. Кінцева мета - відобразити в першій таблиці по кожній квартирі ім'я покупця (для квартир, які були продані). Завдання ускладнюється тим, що адреса квартири в кожній таблиці є будівельною і складається з кількох полів: 1) адреса корпусу (будинку), 2) секція (під'їзд), 3) поверх, 4) номер на поверсі (наприклад, від 1 до 4) .
Для порівняння двох таблиць Excel нам потрібно добитися того, щоб у обох таблицях кожен рядок ідентифікувався одним полем, а не чотирма. Отримати таке поле можна, об'єднавши значення чотирьох полів адреси функцією Зчепити(). Призначення функції Зчепити() - об'єднання кількох текстових значень в один рядок. Значення функції перераховуються через символ ";". Як значення можуть виступати як адреси осередків, і довільний текст, заданий у лапках.
Крок 1.Вставимо на початку першої таблиці порожню колонку "A" і пропишемо в комірці цієї колонки навпроти першого рядка з даними формулу:
=ЗЧЕПИТИ(B3;"-";C3;"-";D3;"-";E3)
Для зручності візуального сприйняття між значеннями комірок, що об'єднуються, ми встановили символи "-".
Крок 2Скопіюємо формулу в нижченаведені комірки колонки А.
Крок 4.Для порівняння таблиць Excel за значеннями слід користуватися функцією ВПР(). Призначення функції ВПР() - пошук значення в крайньому лівому стовпці таблиці та повернення значення комірки, що знаходиться у вказаному стовпці того ж рядка. Перший параметр - потрібне значення. Другий параметр - таблиця, у якій здійснюватиметься пошук значення. Третій параметр - номер стовпця, з комірки якого у знайденому рядку буде повернуто значення. Четвертий параметр – тип пошуку: брехня – точний збіг, істина – наближений збіг.
Оскільки вихідна інформація має бути розміщена в першій таблиці (саме до неї потрібно додати імена покупців), то формулу будемо прописувати в ній. Сформуємо у вільній колонці праворуч від таблиці навпроти першого рядка даних формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;БРЕХНЯ)
При копіюванні формул "розумний" Excel автоматично змінює адресацію осередків. У нашому випадку потрібне значення для кожного рядка буде змінюватися: A3, A4 і т.д., а адреса таблиці, в якій ведеться пошук, має залишатися незмінною. Для цього зафіксуємо комірки у параметрі адреси таблиці символами "$". Замість "Лист2!A3:F10" робимо "Лист2!$A$3:$F$10".
