Заняття 2 з 3
Як порівняти два файли Excel V2
Оновлене покрокове керівництво, як у Excel за допомогою Power Query порівняти два файли однакової структури та виділити нові й застарілі записи.
Опис заняття
Відеоверсія
Текстова версія
З моменту публікації запису про порівняння двох файлів Excel, точніше записів у файлах Excel, пройшло достатньо часу, отримано певний «фідбек» як позитивного, так і негативного характеру, і саме час оновити запис. Тим більше, що зараз можна простіше й наочніше провести порівняння файлів однакової структури, але з різними записами.
По-перше, врахуємо той факт, що більшість запитань щодо попереднього матеріалу стосувалися порівняння двох прайс-листів, тож у якості прикладу цього матеріалу порівняємо саме прайси, причому свідомо виберемо неправильно створений прайс з точки зору подання однотипних даних. Неправильно підготовлений прайс спеціально не обирався, він був першим, що потрапився при пошуку.
По-друге, як інструментарій обмежимося лише Excel, через високу поширеність цього універсального продукту. Однак слід згадати, що для обробки великих обсягів даних, коли потрібно порівняти дані файлу, вибрати унікальні записи, зшити кілька файлів за певним ключем тощо, це не завжди найкращий вибір.
І, по-третє, оскільки результат порівняння може використовуватися для різних цілей: просто позначка в колонці про зміну даних у відповідному рядку або вивід нових/застарілих даних окремим списком, постараємося врахувати всі варіанти і виведемо як позначку, так і нові або старі записи окремими списками.
Графічно зобразити бажаний результат порівняння двох файлів можна таким чином:

При подальшому оновленні файлів виконувати операцію порівняння заново не потрібно — достатньо змінити файли (старий і новий) у вказаних позиціях. Звісно, вищесказане справедливе за збереження структури файлів, тобто взяти прайс іншого магазину з іншими стовпцями й автоматично провести порівняння просто замінивши файли не вдасться.
Повне відео
Розв'язок:
1. Підготовка даних
Відеоверсія
Текстова версія
Порівняння в Excel буде виконуватися за допомогою інструмента Power Query. Цей інструмент призначений для обробки даних, у тому числі й умовно великих обсягів (понад 1 млн записів), коли стандартні обмеження аркуша Excel не дозволяють працювати.
Перед безпосередніми операціями порівняння для початкових файлів необхідно створити підключення. Це робиться за допомогою команди: Отримати дані/ З файлу/ З книги. Далі вибирається розташування файлу, натискається кнопка "Відкрити", потім вибирається аркуш з даними (у нашому випадку це єдиний "Лист1") і вибирається команда "Завантажити в…", а в наступному діалоговому вікні обирається опція "Створити лише підключення".

Трохи пояснень щодо виконаних операцій:
- з точки зору рішення завдання порядок завантажених файлів (який старий, а який новий) не важливий, проте він важливий з точки зору складання запитів. Наприклад, у нашому випадку початковий, тобто старий прайс, завантажено першим, а оновлений — другим. Під час пошуку застарілих позицій ми орієнтуємося на цей порядок; якщо змінити порядок додавання файлів, то ми знайдемо не застарілі, а нові позиції. У будь-якому разі перейменування запитів у: "Устарілий" і "Новий" повністю вирішує проблему запам'ятовування, який файл було додано першим.
- У останньому діалоговому вікні потрібно вибирати опцію "Створити лише підключення", бо в іншому випадку створені підключення відразу будуть вивантажені на аркуш робочої книги, іншими словами, скопійовані. З запитами працювати можна буде, але це просто зайве нагромадження даних.
Після створення підключень виконаємо кілька підготовчих операцій, а саме видалимо перший рядок з загальною назвою всіх товарів — він не несе смислового навантаження, а другою операцією зробимо другий рядок (який після видалення першого став першим) заголовками таблиці. Операція видалення виконується командою: "Скоротити рядки/ Видалити рядки/ Видалення верхніх рядків" на вкладці "Основне". Для того, щоб використати перший рядок як заголовки, слід скористатися однойменною командою на вкладці "Перетворення" редактора запитів Power Query.

2. Знаходження нових і застарілих позицій товарів
Відеоверсія
Текстова версія
Почнемо з простих операцій для знаходження лише нових (товарів, яких немає в старому прайсі) або застарілих позицій (товарів, яких немає в новому прайсі). Переходимо на вкладку "Основне" і вибираємо команду "Комбінувати/ Об'єднати запити/ Об'єднати запити в новий".
У діалоговому вікні злиття першим файлом вибираємо "Устарілий", а другим — "Новий", далі клікаємо по стовпцю "Модель №" — це поле буде використовуватися як ключ, тобто по ньому відбуватиметься порівняння.
Важливий пункт тут — це тип з'єднання: вибираючи "Анти-з'єднання зліва" ми знаходимо застарілі товари, а "Анти-з'єднання справа" — нові товари. Звісно, якщо змінити порядок вхідних файлів (спочатку новий прайс, а потім застарілий), то "Анти-з'єднання зліва" буде знаходити нові позиції, а "Анти-з'єднання справа" — відповідно, старі товари.

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

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

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

Щоб зробити вихідний файл із такою ж кількістю стовпців, як у початкових, або меншим (видаливши зайве), необхідно створити нові, так звані "зведені" стовпці для моделі товару, опису й ціни. Ці стовпці міститимуть записи або зі старого прайсу, або з нового (якщо їх немає в старому). Таким чином ми позбудемося як пропусків у зведеному файлі, так і дублювання записів.
Додавання нових стовпців робиться за допомогою вкладки "Додати стовпець" командою "Умовний стовпець". Тут формується умова, що за замовчуванням дані беруться зі стовпця, який надійшов із старого файлу; якщо запису немає, тоді запис береться зі стовпця, який надійшов із нового файлу, у протилежному випадку записуємо "error" — таких записів не повинно бути, тому якщо вони з'являться, слід дослідити випадок і подивитися, що зроблено не так.
Спочатку створимо зведений стовпець з моделлю товару. Після того, як ми створили зведений стовпець саме з моделі товару, можемо побудувати стовпець з примітками, у якому буде записуватися різна інформація щодо товарів, наприклад: це новий товар, застарілий або на нього змінилася ціна.

Одразу ж опробуємо цю можливість і зробимо позначку для нових товарів, проте, щоб мати таку позначку, нам потрібно знати, який товар новий. Для цього в існуючу зведену таблицю додамо створений раніше запит з пошуком нових товарів: "Комбінувати/ Об’єднати запити", а не "Комбінувати/ Об’єднати запити в новий", оскільки нам не потрібна додаткова таблиця — будемо модифікувати вже наявну.
В якості другої приєднаної таблиці спочатку вибираємо таблицю "Новые позиции", так ми зможемо позначити нові товари, які вже присутні у зведеній. Тип з’єднання – "Зовнішнє з’єднання зліва". При створенні цього запиту є один дуже важливий нюанс: у зведеній таблиці в якості ключового стовпця необхідно вибирати наш створений умовний стовпець "Сводная модель".

Тепер у нас зліва знову з’явився стовпець "Table", але ми не будемо розгортати всі його стовпці, а відобразимо лише стовпець з моделлю (насправді тут не важливо, що відобразити — важливий сам факт непустого запису; іншими словами, якщо щось є — це модель або ціна, отже в примітку можна записати, що товар новий).

Ту саму операцію слід виконати й для позначки застарілих товарів. Тобто знову вибираємо "Комбинировать/ Об’єднати запити", але в якості другої таблиці вибираємо наш раніше створений запит із застарілими товарами. Решта — так само.

Тепер усе готово для створення стовпця з примітками, де буде позначка "новий" або "застарілий" товар поруч із відповідними позиціями. Насправді позначка вже є — просто вона відображена моделлю товару в двох останніх доданих стовпцях. Тобто якщо модель записана в стовпці "Новые позиции.Модель", значить товар новий; якщо в "Устаревшие позиции.Модель" — товар застарілий. Створюючи стовпець з примітками, ми по-перше зберемо все в одному стовпці, по-друге запишемо зрозумілі "Новий товар/ Устарілий товар", а не просто модель.
Отже, переходимо на вкладку "Додати стовпець" і вибираємо команду "Умовний стовпець". Назва для цього стовпця буде "Примітка".
В якості умови для нового стовпця запишемо, що якщо в стовпці "Новые позиции.Модель" значення не дорівнює "null" (тобто там щось є, не важливо що), то в наш новий стовпець записуємо "Новий товар"; друге умова: якщо "Устаревшие позиции.Модель" значення не дорівнює "null", то в наш новий стовпець записуємо "Устарілий товар".
Крім того, в цей же стовпець додамо ще правила для порівняння старої і нової ціни. В імені стовпця вибираємо "Цена в рублях" (нагадую, це стовпець зі старого прайсу): якщо більше, ніж "Новый. Цена в рублях", значить у стовпець з приміткою слід записати "Цена уменьшилась". І ще точно таке ж правило, тільки вже якщо стара ціна менша за нову.

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

Фактично зведена таблиця створена — залишається лише привести її до ладу (прибрати зайві стовпці, зробити новий зведений стовпець із назвою, оскільки є нові та застарілі товари, і зробити зведений стовпець для ціни).
Важливо. При створенні зведених стовпців ми брали за основу старий прайс; насправді краще брати за основу новий, наприклад, якщо були уточнення щодо опису, але це не критично для стовпців "Модель" і "Название". А от для ціни це критично. Тобто при створенні зведеного стовпця з ціною спочатку ціна береться з нового прайсу, тобто зі стовпця "Новый.Цена", а для застарілих позицій (їх просто немає в новому прайсі) — зі старого, тобто зі стовпця "Цена".
До речі, на відео (16:31) саме була допущена ця помилка; пізніше (19:30), на етапі перевірки (коли ціни в прайсах спеціально змінювалися), стовпця примітки ця помилка буде знайдена, але, на жаль, виправлена неправильно.
Новий стовпець для моделі ми вже створили раніше, зараз додамо стовпець для опису і ціни.

Якщо з самим описом не так критично брати за основу старе або нове описання, то для ціни — це серйозна помилка, бо в разі її зміни в підсумковій таблиці все одно відобразиться стара.

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

Дані вивантажуються на аркуш у режимі так званої "розумної таблиці" і, хоча робота вже виконана, можна трохи візуалізувати отриманий результат.
4. Використання умовного форматування для підсвічування змін
Відеоверсія
Текстова версія
При великій кількості записів банальної позначки в колонці про зміну ціни може бути недостатньо. В Excel є чудовий інструмент "Умовне форматування", який дозволяє підсвічувати відповідні поля таблиці залежно від значень певних клітинок (це можуть бути ті клітинки, які форматуються, або зовсім інші).
Докладний опис того, як користуватися умовним форматуванням в Excel, розказано в 3-му питанні 5-го заняття курсу Excel від новачка до професіонала.
Тут ми підфарбуємо повністю рядок таблиці у випадку, якщо відбулося змінення ціни для товару, і просто окрему клітинку, якщо товар новий або застарілий.
Умовне форматування викликається з вкладки "Основне", і правило для виділення позицій, у яких збільшилася ціна, виглядає таким чином. Головне — перед викликом діалогового вікна налаштування правила не забути виділити весь діапазон з таблицею.

Правило виділення рядків таблиці за умови зменшення ціни точно таке саме, за винятком того, що ми шукаємо фразу "Цена уменьшилась", що логічно, ну і інших налаштувань кольорового оформлення.

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

На перший погляд правила абсолютно такі ж, як і для ціни — насправді так і є; відмінність лише в тому, який діапазон був виділений перед створенням правила.
До речі, в Excel є один хитрий прийом, пов’язаний із форматуванням: якщо ви помилилися з визначенням початкового діапазону для правила умовного форматування, не поспішайте переробляти правило з нуля. Більшість знайома з таким інструментом в MS Office, як "Формат за зразком" — цей інструмент є і в Excel, і в PowerPoint, і в Word і дозволяє копіювати форматування з одного фрагмента документа на інший. У Excel він чудово працює й для умовного форматування, оскільки це теж форматування; таким чином, якщо ви створили правило, яке виділяє лише клітинки одного стовпця, а потім хочете скопіювати це форматування на решту таблиці, достатньо просто скопіювати форматування.

До речі, це працює й в зворотному напрямку: якщо потрібно прибрати умовне форматування з певних клітинок/діапазонів стовпців, достатньо скористатися інструментом "Очистити/ Очистити формати" з вкладки "Основне", група "Редагування".
5. Візуалізація звіту за допомогою Power BI
Відеоверсія
Текстова версія
Ця частина суто опціональна — вся робота вже виконана. Просто, якщо результати порівняння потрібно переглядати в браузері, то Power BI чудово підходить для цих цілей. До речі, Microsoft пропонує спробувати можливості бізнес-аналітики Power BI безкоштовно протягом року — можна не лише опробувати нові можливості, але й добре навчитися працювати.
На вкладці "Основне" вибираємо команду "Отримати дані" і обираємо джерело даних — у нашому випадку книга Excel.

На наступному етапі вибираємо аркуш/аркуші, які будуть джерелом для візуалізації; у нашому випадку це аркуш "Сводная сравнение" і натискаємо кнопку "Завантажити".
До речі, якщо спочатку планується використовувати візуалізації Power BI, то немає потреби вивантажувати дані на аркуш Excel — можна підключатися безпосередньо до запиту Power Query. Запити знаходяться у верхній частині списку.
Після підключення до джерела даних обираємо тип візуалізації — таблиця й позначаємо поля для виведення, оскільки підготовку даних ми зробили на етапі роботи з Power Query, то просто позначаємо всі доступні стовпці.

З дрібниць, які легко реалізувати, можна використати фільтр. Щоб додати фільтр, прибираємо фокус із таблиці, клацнувши в пустий простір сторінки Power BI, а потім обираємо фільтр із панелі візуалізацій. Як поле, за яким буде фільтруватися значення, обираємо поле "Примітка".
На цьому поки все.
Файл із прикладом
Оскільки в редакторі Power Query прописуються абсолютні шляхи до файлів, то, щоб порівняння працювало коректно на вашій системі, необхідно змінити шляхи до старого й нового файлу. Це робиться в самому редакторі запитів.

Шановні користувачі, архів із прикладом ви також можете завантажити в гілці нашого форуму, де поставити питання або просто обговорити матеріал.
Завантажити архів
Посилання за темою
Як порівняти два файли MS Excel (з використанням функції ВПР (VLOOKUP) та СУБД Access)