Ми маємо дві таблиці замовлень, скопійованих в один робочий лист. Необхідно виконати порівняння даних двох таблиць в Excel і перевірити, які позиції є першою таблицею, але немає в другій. Немає сенсу вручну порівнювати значення кожного осередку.
Порівняння двох стовпців на збіги в Excel
Як зробити порівняння значень у Excel двох стовпців? Для вирішення цього завдання рекомендуємо використовувати умовне форматування, яке швидко виділити кольором позиції, що знаходяться лише в одному стовпчику. Робочий лист із таблицями:
Насамперед необхідно присвоїти імена обом таблицям. Завдяки цьому легше зрозуміти, які порівнюються діапазони осередків:
- Виберіть інструмент «ФОРМУЛИ»-«Визначені імена»-«Присвоїти ім'я».
- У вікні, що з'явилося в полі «Ім'я:» введіть значення – Таблиця_1.
- Лівою клавішею миші зробіть клацання по полю введення «Діапазон:» та виділіть діапазон: A2:A15. І натисніть OK.
Для другого списку виконайте ті ж дії тільки назву присвойте – Таблица_2. А діапазон вкажіть C2: C15 відповідно.
Корисна порада! Імена діапазонів можна надавати швидше за допомогою поля імен. Воно знаходиться ліворуч від рядка формул. Просто виділяйте діапазони осередків, а в полі імен введіть відповідне ім'я для діапазону та натисніть Enter.
Тепер скористаємося умовним форматуванням, щоб порівняти два списки в Excel. Нам потрібно отримати наступний результат:
Позиції, які є в Таблиці_1, але немає в Таблиці_2, будуть відображатися зеленим кольором. У той же час, позиції, що знаходяться в Таблиці_2, але відсутні в Таблиці_1, будуть підсвічені синім кольором.
Принцип порівняння даних двох стовпців в Excel
При визначенні умов для форматування осередків стовпців ми використовували функцію РАХУНКИ. У цьому прикладі ця функція перевіряє скільки разів зустрічається значення другого аргументу (наприклад, A2) у списку першого аргументу (наприклад, Таблица_2). Якщо кількість разів = 0 у такому разі формула повертає значення ІСТИНА. У такому випадку осередку присвоюється формат користувача, зазначений у параметрах умовного форматування.
Посилання у другому аргументі відносне, отже по черзі будуть перевірені всі осередки виділеного діапазону (наприклад, A2:A15). Наприклад, для порівняння двох прайсів в 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.
Іноді виникає потреба порівняти два файли 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. Ваші пропозиції чекаємо у коментарях.
Порівняємо дві таблиці, які мають практично однакову структуру. Таблиці різняться значеннями в окремих рядках, деякі найменування рядків зустрічаються в одній таблиці, але в іншій можуть бути відсутні.
Нехай на аркушах Січеньі Лютийє дві таблиці з оборотами у період за відповідними рахунками.
Як видно з малюнків, таблиці різняться:
- Наявністю (відсутністю) рядків (найменувань рахунків). Наприклад, у таблиці на аркуші Січеньвідсутній рахунок 26 (див. файл прикладу ), а таблиці на аркуші Лютийвідсутні рахунок 10 та його субрахунки.
- Різними значеннями у рядках. Наприклад, за рахунком 57 обертів за січень та лютий не збігаються.
Якщо структури таблиць приблизно однакові (більшість найменувань рахунків (рядків) збігаються, кількість і найменування стовпців збігаються), можна порівняти дві таблиці. Проведемо порівняння двома способами: один простіше у реалізації, інший наочніше.
Простий варіант порівняння 2-х таблиць
Спочатку визначимо, які рядки (найменування рахунків) присутні в одній таблиці, але відсутні в іншій. Потім, у таблиці, в якій менше рядківвідсутня (в найбільш повній таблиці), виведемо звіт про порівняння, що є різницею по стовпцях (різниця оборотів за січень і лютий).
Основним недоліком цього підходу є те, що звіт про порівняння таблиць не включає рядки відсутні в найбільш повній таблиці. Наприклад, у цьому випадку, найбільш повною таблицею є таблиця на аркуші Січень,в якій відсутній рахунок 26 із лютневої таблиці.
Щоб визначити, яка з двох таблиць є найбільш повною, потрібно відповісти на 2 питання: Які рахунки в лютневій таблиці відсутні в січневій? та Які рахунки в січневій таблиці відсутні у січневій?
Це можна зробити за допомогою формул (див. стовпець Е): = ЯКЩО(ЕНД(ВПР(A7;Січень!$A$7:$A$81;1;0));"Ні";"Є"і = ЯКЩО(ЕНД(ВПР(A7;Лютий!$A$7:$A$77;1;0));"Ні";"Є"
Порівняння оборотів за рахунками зробимо за допомогою формул: = ЯКЩО(ЕНД(ВПР($A7;Лютий!$A$7:$C77;2;0));0;ВПР($A7;Лютий!$A$7:$C77;2;0))-B7і = ЯКЩО(ЕНД(ВПР($A7;Лютий!$A$7:$C77;3;0));0;ВПР($A7;Лютий!$A$7:$C77;3;0))-C7
У разі відсутності відповідного рядка функція ВПР() повертає помилку #Н/Д, яка обробляється зв'язкою функцій ЕНД() і ЕСЛИ() , замінюючи помилку на 0 (у разі відсутності рядка) або значення з відповідного стовпця.
За допомогою можна виділити розбіжності (наприклад, червоним).
Найбільш наочний варіант порівняння 2-х таблиць (але складніший)
За аналогією із завданням вирішеним у статті можна сформувати список найменувань рахунків, що включає ВСІ найменування рахунків з обох таблиць (без повторів). Потім вивести різницю по шпальтах.
Для цього необхідно:
- За допомогою = ЕСЛИПОМИЛКА(ЕСЛИПОМИЛКА(ІНДЕКС(Січень;ПОШУКПОЗ(0;РАХУНЧИКИ(A$4:$A4;Січень);0))); ;"")сформувати у стовпці А перелік рахунків з обох таблиць (без повторів);
- За допомогою = ЯКЩОПОМИЛКА(ІНДЕКС(Список; ПОШУКПОЗ(Найменший(ЛІЧИСЛИ(Список; "Список;"))<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , де Список -
Стаття дає відповіді такі питання:
- Як порівняти дві таблиці в 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".
