зміст
У нас є дві таблиці (наприклад, стара і нова версії прайс-листа), які потрібно порівняти і швидко знайти відмінності:
Відразу видно, що в новому прайсі щось додали (фініки, часник…), щось зникло (ожина, малина…), на деякі товари змінилися ціни (інжир, дині…). Потрібно швидко знайти та відобразити всі ці зміни.
Для будь-якого завдання в Excel майже завжди існує більше ніж одне рішення (зазвичай 4-5). Для нашої проблеми можна використовувати багато різних підходів:
- функція VPR (ВПР) — шукати назви товарів з нового прайс-листа в старому і показувати стару ціну поруч з новою, а потім ловити відмінності
- об’єднати два списки в один, а потім побудувати на його основі зведену таблицю, де будуть чітко видні відмінності
- використовувати надбудову Power Query для Excel
Давайте розберемо їх усіх по порядку.
Спосіб 1. Порівняння таблиць за допомогою функції VLOOKUP
Якщо ви зовсім не знайомі з цією чудовою функцією, то спочатку подивіться сюди та прочитайте або подивіться відеоінструкцію про неї – збережіть собі пару років життя.
Як правило, ця функція використовується для перетягування даних з однієї таблиці в іншу шляхом зіставлення певного загального параметра. У цьому випадку ми використаємо його, щоб вставити старі ціни в нову:
Ті продукти, щодо яких виявилася помилка #N/A, відсутні в старому списку, тобто були додані. Також чітко видно зміни цін.
профі цей спосіб: простий і зрозумілий, як то кажуть «класика жанру». Працює в будь-якій версії Excel.
мінуси також є. Для пошуку товарів, доданих до нового прайс-листа, вам доведеться виконати ту ж саму процедуру в зворотному напрямку, тобто підтягнути нові ціни до старої ціни за допомогою VLOOKUP. Якщо завтра розміри таблиць зміняться, то формули доведеться коригувати. Ну, а на дійсно великих таблицях (> 100 тисяч рядків) все це щастя буде пристойно гальмувати.
Спосіб 2: Порівняння таблиць за допомогою зведеної таблиці
Давайте скопіюємо наші таблиці одну під іншу, додавши стовпець з назвою прайс-листа, щоб потім було зрозуміти, з якого списку який рядок:
Тепер на основі створеної таблиці ми створимо зведення через Вставка – зведена таблиця (Вставити — зведена таблиця). Кидаємо поле Product до області ліній, поля ціна до області стовпця та поля ЦENA в діапазон:
Як бачите, зведена таблиця автоматично сформує загальний список усіх товарів зі старого та нового прайс-листів (без повторів!) і відсортує товари за алфавітом. Ви можете чітко бачити додані товари (вони не мають старої ціни), вилучені товари (вони не мають нової ціни) і зміни ціни, якщо такі є.
Загальні підсумки в такій таблиці не мають сенсу, і їх можна відключити на вкладці Конструктор – Загальні підсумки – Вимкнути для рядків і стовпців (Дизайн — загальні підсумки).
Якщо змінюються ціни (але не кількість товару!), то достатньо просто оновити створену зведення, клацнувши по ньому правою кнопкою миші – оновлення.
профі: цей підхід на порядок швидший для великих таблиць, ніж VLOOKUP.
мінуси: потрібно вручну скопіювати дані один під одного і додати колонку з назвою прайс-листа. Якщо розміри столів змінюються, то все доведеться робити заново.
Спосіб 3. Порівняння таблиць за допомогою Power Query
Power Query — це безкоштовна надбудова для Microsoft Excel, яка дозволяє завантажувати дані в Excel практично з будь-якого джерела, а потім перетворювати ці дані будь-яким способом. У Excel 2016 ця надбудова вже вбудована за замовчуванням на вкладці дані (Дані), а для Excel 2010-2013 потрібно завантажити окремо з сайту Microsoft і встановити – отримати нову вкладку PowerQuery.
Перш ніж завантажувати наші прайс-листи в Power Query, їх потрібно спочатку перетворити на розумні таблиці. Для цього виберіть діапазон з даними і натисніть комбінацію на клавіатурі Ctrl+T або виберіть вкладку на стрічці Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю). Назви створених таблиць можна виправити на вкладці Конструктор (Залишу стандарт Таблиця 1 и Таблиця 2, які отримані за замовчуванням).
Завантажте стару ціну в Power Query за допомогою кнопки З таблиці/діапазону (З таблиці/діапазону) із вкладки дані (Дата) або з вкладки PowerQuery (в залежності від версії Excel). Після завантаження ми повернемося до Excel із Power Query за допомогою команди Закрийте та завантажте – Закрийте та завантажте в… (Закрити та завантажити — Закрити та завантажити до…):
… і виберіть у вікні, що з’явиться Просто створіть зв'язок (Тільки підключення).
Повторіть те саме з новим прайсом.
Тепер давайте створимо третій запит, який буде об’єднувати та порівнювати дані двох попередніх. Для цього виберіть в Excel на вкладці Дані – Отримати дані – Об’єднати запити – Об’єднати (Дані — Отримати дані — Об’єднати запити — Об’єднати) або натисніть кнопку Поєднувати (Об'єднати) таб PowerQuery.
У вікні об’єднання виберіть у випадаючих списках наші таблиці, виберіть стовпці з назвами товарів, а внизу задайте спосіб об’єднання – Повний зовнішній (Повний зовнішній):
Натиснувши на OK повинна з'явитися таблиця з трьох стовпців, де в третьому стовпці потрібно розгорнути вміст вкладених таблиць за допомогою подвійної стрілки в заголовку:
В результаті ми отримуємо об'єднання даних з обох таблиць:
Краще, звичайно, перейменувати назви стовпців в шапці подвійним клацанням по більш зрозумілим:
А тепер найцікавіше. Перейти на вкладку Додати стовпець (Додати стовпець) і натисніть кнопку Умовний стовпець (Умовний стовпець). А потім у вікні, що відкриється, введіть кілька тестових умов із відповідними вихідними значеннями:
Залишилося натиснути на OK і завантажте отриманий звіт до Excel за допомогою тієї ж кнопки закрити і завантажити (Закрити та завантажити) таб Головна (Головна):
Краса.
Крім того, якщо в майбутньому в прайсах відбудуться будь-які зміни (додадуться або видаляться рядки, зміняться ціни тощо), то достатньо буде просто оновити наші запити за допомогою комбінації клавіш Ctrl+інший+F5 або кнопкою Оновити все (Оновити все) таб дані (Дата).
профі: Мабуть, найкрасивіший і найзручніший спосіб з усіх. Розумно працює з великими столами. Не потребує редагування вручну при зміні розміру таблиць.
мінуси: потрібне встановлення надбудови Power Query (у Excel 2010-2013) або Excel 2016. Назви стовпців у вихідних даних змінювати не можна, інакше ми отримаємо помилку «Стовпець такий-то не знайдено!» при спробі оновити запит.
- Як зібрати дані з усіх файлів Excel у певній папці за допомогою Power Query
- Як знайти збіги між двома списками в Excel
- Об'єднання двох списків без дублікатів