Как сравнить два файла Excel V2

Описание занятия

Видеоверсия

Текстовая версия

С момента публикации записи о сравнении двух файлов Excel, а вернее записей в файлах Excel, прошло достаточно времени, получен какой-никакой «фидбек» как положительного опыта, так и отрицательного и самое время обновить запись. Тем более, что сейчас можно проще более наглядно провести сравнение одинаковых по структуре файлов, но с разными записями.

Во-первых, учтем тот факт, что большинство вопросов по прошлому материалу касались сравнения двух прейскурантов (или прайс-листов), в качестве примера данного материала сравним именно прайсы, причем осознанно выберем неправильно созданный прайс с точки зрения представления однотипных данных. Неправильно подготовленный прайс специально не выбирался, а был первым «попавшимся» при поиске.

Во-вторых, в качестве инструментария, ограничимся только Excel, ввиду высокой распространенности данного универсального продукта. Однако, следует упомянуть, что для обработки больших объемов данных, когда нужно сравнить данные файла, выбрать уникальные записи, склеить несколько файлов по определенному ключу и т. д., это не всегда лучший выбор.

И, в-третьих, поскольку результат сравнения может использоваться для разных целей: просто пометка в колонке об изменении данных в соответствующей строке или вывод новых/ устаревших данных отдельным списком, постараемся учесть все варианты и выведем как пометку, так и новые, или старые записи, отдельными списками.

Графически изобразить желаемый результат сравнения двух файлов можно следующим образом:

Результат сравнения двух файлов Excel
Результат сравнения двух файлов Excel

При дальнейшем обновлении файлов, проделывать операцию сравнения заново необходимости нет, достаточно изменить файлы (старый и новый) в указанных позициях. Естественно, вышесказанное справедливо при сохранении структуры файлов, т.е. взять прейскурант от другого магазина с другими столбцами и автоматически провести сравнение просто заменив файлы, не получится.

Сразу под данным текстом находится полная видеоверсия сравнения двух прейскурантов в Excel, а ниже детально документированное поэтапное сравнение, т.е. тоже самое, только с текстовым пояснением и разбивкой на логические части.

Полное видео

Решение:

  1. Подготовка данных

Видеоверсия

Текстовая версия

Сравнение в Excel будет производиться с помощью инструмента Power Query. Данный инструмент предназначен для обработки данных, в том числе и условно больших объемов (более 1 млн. записей), когда стандартные ограничения листа Excel не позволяют работать.

Перед непосредственными операциями сравнения, для исходных файлов необходимо создать подключения. Это делается с помощью команды: Получить данные/ Из файла/ Из книги. Далее выбирается расположение файла, нажимается кнопка «Открыть», потом выбирается лист с данными (в нашем случае это единственный «Лист1») и выбирается команда «Загрузить в…», а, в следующем диалоговом окне выбирается опция «Создать только подключение».

Создание подключения для файла
Создание подключения для файла

Немного пояснений по проделанным операциям:

  • с точки зрения решения задачи очередность загруженных файлов (какой старый, а какой новый) не важна, однако, она важна с точки зрения составления запросов. Например, в нашем случае исходный, т.е. старый прайс, загружен первым, а обновленный – вторым. При поиске устаревших позиций, мы ориентируемся на эту очередность, если порядок добавления файлов изменить, то мы найдем не устаревшие, а новые позиции. В любом случае, переименование запросов в: «Устаревший» и «Новый», полностью решает проблему запоминания, какой файл был добавлен первым.
  • В последнем диалоговом окне нужно выбирать опцию «Создать только подключение», т.к. в противном случае созданные подключения сразу будут выгружены на лист рабочей книги, другими словами, скопируются. С запросами можно будет работать, но это просто ненужное нагромождение данных.

После создания подключений сделаем несколько подготовительных операций, а именно удалим первую строку с общим названием всех товаров, она не несет никакой смысловой нагрузки, а второй операцией сделаем вторую строку (которая, после удаления первой, стала первой) заголовками таблицы. Операция удаления выполняется командой: «Сократить строки/ Удалить строки/ Удаление верхних строк» из вкладки «Главная». Для того, чтобы использовать первую строку в качестве заголовков, следует воспользоваться одноименной командой из вкладки «Преобразования» редактора запросов Power Query.

Базовое редактирование данных в Power Query
Базовое редактирование данных в Power Query

  1. Нахождение новых и устаревших позиций товаров

Видеоверсия

Текстовая версия

Начнем с простых операций по нахождению только новых (товаров, которых нет в старом прайсе), либо устаревших позиций (товаров, которых нет в новом прайсе).
Переходим на вкладку «Главная» и выбираем команду «Комбинировать/ Объединить запросы/ Объединить запросы в новый».

В диалоговом окне слияния первым файлом выбираем «Устаревший» а вторым «Новый», далее кликаем по столбцу «Модель №» – это поле будет использоваться в качестве ключевого, т.е. по нему будет идти сравнение.

Важный пункт здесь – это тип соединения, выбирая «Анти-соединение слева» мы находим устаревшие товары, а «Анти-соединение справа» – новые товары. Естественно, если изменить порядок входящих файлов (сначала новый прайс, а потом устаревший), то «Анти-соединение слева» будет находить новые позиции, а «Анти-соединение справа», соответственно, – старые товары.

Соединение двух таблиц в Power Query
Соединение двух таблиц в Power Query

При дальнейшем рассмотрении материала будем отталкиваться от порядка входящих файлов, когда устаревший прайс идет первым, а новый – вторым.

В результате проделанных операций и в первом и во втором случае мы получим таблицу со столбцами из обоих прайсов, только для устаревших позиций столбцы нового прайса будут пустыми, а для новых – старого. Пустые столбцы нужно удалить.

Результат соединения двух таблиц для нахождения новых и старых позиций
Результат соединения двух таблиц для нахождения новых и старых позиций

Все и новые и старые позиции товаров найдены и запросы готовы к выгрузке на лист книги Excel.

  1. Построение сводной таблицы со всеми товарами

Видеоверсия

Текстовая версия

Построение сводной таблицы с товарами, не столько сложная, сколько трудоемкая задача. По сути, все сводится к созданию такого же запроса, однако, поскольку, в результате такого объединения, мы получим все записи из обеих таблиц, то придется потратить некоторое время на приведение в порядок результата.

Перейдем, непосредственно, к созданию запроса для всех товаров. Как и в первых двух случаях, единственное, что сейчас выбираем «Полное внешнее» соединение, чтобы сводная таблица содержала все записи из обоих файлов.

Создание запроса "Полное внешнее" соединение
Создание запроса «Полное внешнее» соединение

Сейчас в сводном прайсе находятся как позиции из старого листа, так и из нового, однако, получившееся «полотно» имеет в два раза больше столбцов (столбцы старого файла + столбцы нового), нежели изначальные файлы, позиции товаров, которые находятся в обоих листах продублированы, устаревшие позиции имеют пустые записи в столбцах нового прайса, а новые товары, соответственно, пропуски в столбцах, которые «пришли» из нового файла.

Сводная таблица со всеми строками
Сводная таблица со всеми строками

Чтобы сделать выходной файл с таким же количеством столбцов, как в исходных, или меньшим (удалив лишние) необходимо сделать новые, так называемые «сводные», столбцы для модели товара, описания и цены. Эти столбцы будут содержать записи либо из старого прайса, либо из нового (если их нет в старом). Таким образом мы лишимся как пропусков в сводном файле, так и дублирования записей.
Добавление новых столбцов делается с помощью вкладки «Добавить столбец» команда «Условный столбец». Здесь получается условие, что, по умолчанию данные берутся из столбца, который «пришел» из старого файла, если записи нет, тогда запись берется из столбца, который «пришел» из нового файла, в противном случае запишем «error», таких записей не должно быть, поэтому, если они появятся, нужно исследовать случай и посмотреть, что было сделано не так.

Первым создадим сводный столбец с моделью товара. После того, как мы создали сводный столбец именно с моделью товара мы можем построить столбец с примечаниями в котором будет записываться различная информация по поводу товаров, например, это новый товар, устаревший, либо на него изменилась цена.

Создание нового условного столбца
Создание нового условного столбца

Сразу же и опробуем данную возможность и сделаем отметку для новых товаров, однако, чтобы иметь такую отметку, нам необходимо знать какой товар новый, для этого в существующую сводную таблицу добавим созданный ранее запрос с нахождением новых товаров: «Комбинировать/ Объединить запросы», а не «Комбинировать/ Объединить запросы в новый» поскольку нам не нужна дополнительная таблица, а будем модифицировать уже существующую.

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

Для слияния с новыми товарами важно правильно указать ключ
Для слияния с новыми товарами важно правильно указать ключ

Теперь у нас слева опять появился столбец «Table», но мы не будем разворачивать все его столбцы, а отобразим только столбец с моделью (на самом деле здесь не важно, что отобразить здесь важен сам факт не пустой записи, другими словами, если что-то есть это модель, или цена, значит в примечание можно записать, что товар новый).

В добавленной таблице достаточно отобразить один столбец (любой, не обязательно модель)
В добавленной таблице достаточно отобразить один столбец (любой, не обязательно модель)

Туже самую операцию следует проделать и для пометки устаревших товаров. Т.е опять выбираем «Комбинировать/ Объединить запросы», но в качестве второй таблицы выбираем наш ранее созданный запрос с устаревшими товарами. Остальное тоже самое.

Добавление таблицы с устаревшими товарами
Добавление таблицы с устаревшими товарами

Теперь все готово для создания столбца с примечаниями, где будет пометка нового или устаревшего товара напротив соответствующих позиций. На самом деле пометка уже есть, просто она отображена моделью товара в двух последних добавленных столбцах. Т.е. если модель записана в столбце «Новые позиции.Модель», значит товар новый, если «Устаревшие позиции.Модель», значит товар устаревший. Созданием столбца с примечаниями мы, во-первых, просто все разместим в одном столбце, во-вторых, напишем понятные «Новый товар/ Устаревший товар», а не просто модель.
Итак, переходим на вкладку «Добавить столбец» и выбираем команду «Условный столбец». Название для этого столбца будет «Примечание».

В качестве условия для нового столбца запишем, что если в столбце «Новые позиции.Модель» значение не равно «null» (т.е. там что-то есть, не важно что), значит в наш новый столбец записываем «Новый товар», второе условие, если «Устаревшие позиции.Модель» значение не равно «null», значит в наш новый столбец записываем «Устаревший товар».

Кроме того, в этот же столбец добавим еще правила для сравнения старой и новой цены. В имени столбца выбираем «Цена в рублях» (напомню это столбец из старого прайса), больше чем «Новый. Цена в рублях», значит в столбец с примечанием следует записать «Цена уменьшилась». И еще точно такое же правило, только уже, если старая цена меньше новой.

Настройка условий нового столбца "Примечание"
Настройка условий нового столбца «Примечание»

В полученном столбце, появятся еще записи «error», они на вычисления не влияют, а являются результатом неправильной организации данных в наших прайсах, о чем было сказано ранее.

Результат работы условий столбца "Примечание"
Результат работы условий столбца «Примечание»

Фактически сводная таблица создана остается только привести ее в порядок (убрать ненужные столбцы, сделать новый сводный столбец с названием т.к. есть новые и устаревшие товары и сделать сводный столбец для цены).

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

Кстати, на видео (16:31), как раз и была допущена эта ошибка, позже (19:30), на этапе проверки (когда цены в прайсах специально изменялись) столбца примечания эта ошибка будет найдена, но, к сожалению, исправлена неправильно.

Новый столбец для модели мы уже создали ранее, сейчас добавим столбец для описания и цены.

Здесь как раз следует поменять местами условия, т.е. по умолчанию описание брать из нового файла, а, если его нет – тогда из старого
Здесь как раз следует поменять местами условия, т.е. по умолчанию описание брать из нового файла, а, если его нет – тогда из старого

Если с самим описанием не так критично за основу брать старое описание или новое, то для цены — это серьезная ошибка, т.к. в случае ее изменения в итоговой таблице все-равно будет отображена старая.

Правильный расчет условного столбца очень важен
Правильный расчет условного столбца очень важен

Теперь нужно привести в порядок нашу общую таблицу, а по факту, просто удалить все лишние столбцы, оставив только столбцы с приставкой «Сводная» и «Примечание».
После приведения в порядок сводной таблицы остается выгрузить запрос на страницу Excel командой «Закрыть и загрузить» из вкладки «Главная» редактора Power Query.

Зеленый режим по умолчанию таблицы не очень нравиться автору, поэтому позже он будет изменен на голубой без чередования строк, который также входит в стандартный набор стилей таблиц
Зеленый режим по умолчанию таблицы не очень нравиться автору, поэтому позже он будет изменен на голубой без чередования строк, который также входит в стандартный набор стилей таблиц

Данные выгружаются на лист в режиме так называемой «умной таблицы» и, хотя работа уже сделана, можно несколько визуализировать полученный результат.

  1. Использование условного форматирования для подсветки изменений

Видеоверсия

Текстовая версия

При большом количестве записей, банальной пометки в колонке об изменении цены может быть недостаточно. В Excel есть отличный инструмент «Условное форматирование», позволяющий подсвечивать соответствующие поля таблицы в зависимости от значений определенных ячеек (это могут быть ячейки, который форматируются, а могут быть совершенно другие).

Подробное описание того, как пользоваться условным форматированием в Excel, рассказано в 3м вопросе 5-го занятия курса Excel от новичка до профессионала.

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

Условное форматирование вызывается из вкладки «Главная» и правило для выделения позиций у которых увеличилась цена выглядит следующим образом, главное, перед вызовом диалогового окна настройки правила на забыть выделить весь диапазон с таблицей.

Условное форматирование, если цена увеличилась
Условное форматирование, если цена увеличилась

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

Условное форматирование, если цена уменьшилась
Условное форматирование, если цена уменьшилась

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

Настройка правил условного форматирования для подсветки нового и устаревшего товара
Настройка правил условного форматирования для подсветки нового и устаревшего товара

На первый взгляд правила абсолютно такие, как и для цены, на самом деле так и есть, отличие заключается лишь в том какой диапазон был выделен перед созданием правила.
Кстати, в excel есть один хитрый трюк связанный с форматированием и, если вы ошиблись с определением первоначального диапазона для правила условного форматирования не стоит спешить с переделкой правила с нуля. Итак, большинство знакомо с таким инструментов в MS Office, как формат по образцу, этот инструмент есть и в Excel, и в PowerPoint и в Word и позволяет копировать форматирование одного участка документа на другой. Однако, в Excel он отлично работает и для условного форматирования, ведь это тоже форматирование, таким образом, если вы создали правило, которое выделяет только ячейки одного столбца, а потом ходите скопировать это форматирование на остальную часть таблицы, достаточно просто скопировать форматирование.

Выделение ключевых позиций происходит автоматически благодаря условному форматированию
Выделение ключевых позиций происходит автоматически благодаря условному форматированию

Кстати, это работает и в обратную сторону, если нужно убрать условное форматирование с определенных ячеек/ диапазонов столбцов, достаточно воспользоваться инструментом «Очистить/ Очистить форматы» из вкладки «Главная», группа «Редактирование».

  1. Визуализация отчета с помощью Power BI

Видеоверсия

Текстовая версия

Эта часть сугубо опциональная, вся работа уже сделана. Просто, если результаты сравнения нужно просматривать в браузере, то Power Bi отлично подойдет для этих целей. Кстати, Microsoft предлагает опробовать возможности бизнес аналитики Power BI бесплатно на протяжении года, можно не просто опробовать новые возможности, но и неплохо научиться работать.

На вкладке «Home» выбираем команду «Get Data» и выбираем источник данных, в нашем случае, книга Excel.

Выбор данных в окне Power Bi
Выбор данных в окне Power Bi

На следующем этапе выбираем лист/ листы, которые будут выступать источником для визуализации, в нашем случае это лист «Сводная сравнение» и нажимаем кнопку «Load».
Кстати, если изначально планируется использовать визуализации Power Bi, то нет необходимости выгружать данные на лист Excel, можно подключаться непосредственно к запросу Power Query. запросы находятся вверху списка.

После подключения к источнику данных выбираем тип визуализации – таблица и отмечаем поля для вывода, поскольку подготовку данным мы сделали на этапе работы с Power Query, то просто отмечаем все доступные столбцы.

Таблица в редакторе Power Bi
Таблица в редакторе Power Bi

Из мелочей, которые просто реализовать, можно использовать фильтр. Для добавления фильтра убираем фокус из таблицы, кликая в пустое пространство страницы Power BI, а затем выбираем фильтр из панели визуализаций. В качестве поля по которому будут фильтроваться значения, выбираем поле «Примечание».

На этом, пока все.

Файл с примером

Поскольку в редакторе Power Query прописываются абсолютные пути к файлам, то, чтобы сравнение работало корректно на вашей системе, необходимо изменить пути к старому и новому файлу. Это делается в самом редакторе запросов.

Изменение путей к файлам для сравнения
Изменение путей к файлам для сравнения

Уважаемые пользователи, архив с примером вы можете также скачать в ветке нашего форума, где задать вопрос, или просто обсудить материал.

Чтобы скачивать файлы, пожалуйста, войдите.

Ссылки по теме

Как сравнить два файла MS Excel (с использованием функции ВПР и СУБД Access)