Описание занятия
Видеоверсия
Текстовая версия
Во-первых, учтем тот факт, что большинство вопросов по прошлому материалу касались сравнения двух прейскурантов (или прайс-листов), в качестве примера данного материала сравним именно прайсы, причем осознанно выберем неправильно созданный прайс с точки зрения представления однотипных данных. Неправильно подготовленный прайс специально не выбирался, а был первым «попавшимся» при поиске.
Во-вторых, в качестве инструментария, ограничимся только Excel, ввиду высокой распространенности данного универсального продукта. Однако, следует упомянуть, что для обработки больших объемов данных, когда нужно сравнить данные файла, выбрать уникальные записи, склеить несколько файлов по определенному ключу и т. д., это не всегда лучший выбор.
И, в-третьих, поскольку результат сравнения может использоваться для разных целей: просто пометка в колонке об изменении данных в соответствующей строке или вывод новых/ устаревших данных отдельным списком, постараемся учесть все варианты и выведем как пометку, так и новые, или старые записи, отдельными списками.
Графически изобразить желаемый результат сравнения двух файлов можно следующим образом:
При дальнейшем обновлении файлов, проделывать операцию сравнения заново необходимости нет, достаточно изменить файлы (старый и новый) в указанных позициях. Естественно, вышесказанное справедливо при сохранении структуры файлов, т.е. взять прейскурант от другого магазина с другими столбцами и автоматически провести сравнение просто заменив файлы, не получится.
Сразу под данным текстом находится полная видеоверсия сравнения двух прейскурантов в Excel, а ниже детально документированное поэтапное сравнение, т.е. тоже самое, только с текстовым пояснением и разбивкой на логические части.
Полное видео
Решение:
- Подготовка данных
Видеоверсия
Текстовая версия
Перед непосредственными операциями сравнения, для исходных файлов необходимо создать подключения. Это делается с помощью команды: Получить данные/ Из файла/ Из книги. Далее выбирается расположение файла, нажимается кнопка «Открыть», потом выбирается лист с данными (в нашем случае это единственный «Лист1») и выбирается команда «Загрузить в…», а, в следующем диалоговом окне выбирается опция «Создать только подключение».
Немного пояснений по проделанным операциям:
- с точки зрения решения задачи очередность загруженных файлов (какой старый, а какой новый) не важна, однако, она важна с точки зрения составления запросов. Например, в нашем случае исходный, т.е. старый прайс, загружен первым, а обновленный – вторым. При поиске устаревших позиций, мы ориентируемся на эту очередность, если порядок добавления файлов изменить, то мы найдем не устаревшие, а новые позиции. В любом случае, переименование запросов в: «Устаревший» и «Новый», полностью решает проблему запоминания, какой файл был добавлен первым.
- В последнем диалоговом окне нужно выбирать опцию «Создать только подключение», т.к. в противном случае созданные подключения сразу будут выгружены на лист рабочей книги, другими словами, скопируются. С запросами можно будет работать, но это просто ненужное нагромождение данных.
После создания подключений сделаем несколько подготовительных операций, а именно удалим первую строку с общим названием всех товаров, она не несет никакой смысловой нагрузки, а второй операцией сделаем вторую строку (которая, после удаления первой, стала первой) заголовками таблицы. Операция удаления выполняется командой: «Сократить строки/ Удалить строки/ Удаление верхних строк» из вкладки «Главная». Для того, чтобы использовать первую строку в качестве заголовков, следует воспользоваться одноименной командой из вкладки «Преобразования» редактора запросов Power Query.
- Нахождение новых и устаревших позиций товаров
Видеоверсия
Текстовая версия
Переходим на вкладку «Главная» и выбираем команду «Комбинировать/ Объединить запросы/ Объединить запросы в новый».
В диалоговом окне слияния первым файлом выбираем «Устаревший» а вторым «Новый», далее кликаем по столбцу «Модель №» – это поле будет использоваться в качестве ключевого, т.е. по нему будет идти сравнение.
Важный пункт здесь – это тип соединения, выбирая «Анти-соединение слева» мы находим устаревшие товары, а «Анти-соединение справа» – новые товары. Естественно, если изменить порядок входящих файлов (сначала новый прайс, а потом устаревший), то «Анти-соединение слева» будет находить новые позиции, а «Анти-соединение справа», соответственно, – старые товары.
При дальнейшем рассмотрении материала будем отталкиваться от порядка входящих файлов, когда устаревший прайс идет первым, а новый – вторым.
В результате проделанных операций и в первом и во втором случае мы получим таблицу со столбцами из обоих прайсов, только для устаревших позиций столбцы нового прайса будут пустыми, а для новых – старого. Пустые столбцы нужно удалить.
Все и новые и старые позиции товаров найдены и запросы готовы к выгрузке на лист книги Excel.
- Построение сводной таблицы со всеми товарами
Видеоверсия
Текстовая версия
Перейдем, непосредственно, к созданию запроса для всех товаров. Как и в первых двух случаях, единственное, что сейчас выбираем «Полное внешнее» соединение, чтобы сводная таблица содержала все записи из обоих файлов.
Сейчас в сводном прайсе находятся как позиции из старого листа, так и из нового, однако, получившееся «полотно» имеет в два раза больше столбцов (столбцы старого файла + столбцы нового), нежели изначальные файлы, позиции товаров, которые находятся в обоих листах продублированы, устаревшие позиции имеют пустые записи в столбцах нового прайса, а новые товары, соответственно, пропуски в столбцах, которые «пришли» из нового файла.
Чтобы сделать выходной файл с таким же количеством столбцов, как в исходных, или меньшим (удалив лишние) необходимо сделать новые, так называемые «сводные», столбцы для модели товара, описания и цены. Эти столбцы будут содержать записи либо из старого прайса, либо из нового (если их нет в старом). Таким образом мы лишимся как пропусков в сводном файле, так и дублирования записей.
Добавление новых столбцов делается с помощью вкладки «Добавить столбец» команда «Условный столбец». Здесь получается условие, что, по умолчанию данные берутся из столбца, который «пришел» из старого файла, если записи нет, тогда запись берется из столбца, который «пришел» из нового файла, в противном случае запишем «error», таких записей не должно быть, поэтому, если они появятся, нужно исследовать случай и посмотреть, что было сделано не так.
Первым создадим сводный столбец с моделью товара. После того, как мы создали сводный столбец именно с моделью товара мы можем построить столбец с примечаниями в котором будет записываться различная информация по поводу товаров, например, это новый товар, устаревший, либо на него изменилась цена.
Сразу же и опробуем данную возможность и сделаем отметку для новых товаров, однако, чтобы иметь такую отметку, нам необходимо знать какой товар новый, для этого в существующую сводную таблицу добавим созданный ранее запрос с нахождением новых товаров: «Комбинировать/ Объединить запросы», а не «Комбинировать/ Объединить запросы в новый» поскольку нам не нужна дополнительная таблица, а будем модифицировать уже существующую.
В качестве второй присоединенной таблицы с начала выбираем таблицу «Новые позиции», так мы сможем пометить новые товары, которые уже присутствуют во сводной. Тип соединения – «Внешнее соединение слева». При создании данного запроса есть один очень важный нюанс в сводной таблице в качестве ключевого столбца необходимо выбирать наш созданный условный столбец «Сводная модель».
Теперь у нас слева опять появился столбец «Table», но мы не будем разворачивать все его столбцы, а отобразим только столбец с моделью (на самом деле здесь не важно, что отобразить здесь важен сам факт не пустой записи, другими словами, если что-то есть это модель, или цена, значит в примечание можно записать, что товар новый).
Туже самую операцию следует проделать и для пометки устаревших товаров. Т.е опять выбираем «Комбинировать/ Объединить запросы», но в качестве второй таблицы выбираем наш ранее созданный запрос с устаревшими товарами. Остальное тоже самое.
Теперь все готово для создания столбца с примечаниями, где будет пометка нового или устаревшего товара напротив соответствующих позиций. На самом деле пометка уже есть, просто она отображена моделью товара в двух последних добавленных столбцах. Т.е. если модель записана в столбце «Новые позиции.Модель», значит товар новый, если «Устаревшие позиции.Модель», значит товар устаревший. Созданием столбца с примечаниями мы, во-первых, просто все разместим в одном столбце, во-вторых, напишем понятные «Новый товар/ Устаревший товар», а не просто модель.
Итак, переходим на вкладку «Добавить столбец» и выбираем команду «Условный столбец». Название для этого столбца будет «Примечание».
В качестве условия для нового столбца запишем, что если в столбце «Новые позиции.Модель» значение не равно «null» (т.е. там что-то есть, не важно что), значит в наш новый столбец записываем «Новый товар», второе условие, если «Устаревшие позиции.Модель» значение не равно «null», значит в наш новый столбец записываем «Устаревший товар».
Кроме того, в этот же столбец добавим еще правила для сравнения старой и новой цены. В имени столбца выбираем «Цена в рублях» (напомню это столбец из старого прайса), больше чем «Новый. Цена в рублях», значит в столбец с примечанием следует записать «Цена уменьшилась». И еще точно такое же правило, только уже, если старая цена меньше новой.
В полученном столбце, появятся еще записи «error», они на вычисления не влияют, а являются результатом неправильной организации данных в наших прайсах, о чем было сказано ранее.
Фактически сводная таблица создана остается только привести ее в порядок (убрать ненужные столбцы, сделать новый сводный столбец с названием т.к. есть новые и устаревшие товары и сделать сводный столбец для цены).
Важно. При создании сводных столбцов мы брали за основу старый прайс, на самом деле, лучше брать за основу новый, например, если были уточнения по описанию, но это не критично, для столбцов «Модель» и «Название», а вот для цены критично. Т.е. при создании сводного столбца с ценой изначально цена берется с нового прайса, т.е. со столбца «Новый.Цена», а для устаревших позиций (их просто нет в новом прайсе) со старого, т.е. со столбца «Цена».
Кстати, на видео (16:31), как раз и была допущена эта ошибка, позже (19:30), на этапе проверки (когда цены в прайсах специально изменялись) столбца примечания эта ошибка будет найдена, но, к сожалению, исправлена неправильно.
Новый столбец для модели мы уже создали ранее, сейчас добавим столбец для описания и цены.
Если с самим описанием не так критично за основу брать старое описание или новое, то для цены — это серьезная ошибка, т.к. в случае ее изменения в итоговой таблице все-равно будет отображена старая.
Теперь нужно привести в порядок нашу общую таблицу, а по факту, просто удалить все лишние столбцы, оставив только столбцы с приставкой «Сводная» и «Примечание».
После приведения в порядок сводной таблицы остается выгрузить запрос на страницу Excel командой «Закрыть и загрузить» из вкладки «Главная» редактора Power Query.
Данные выгружаются на лист в режиме так называемой «умной таблицы» и, хотя работа уже сделана, можно несколько визуализировать полученный результат.
- Использование условного форматирования для подсветки изменений
Видеоверсия
Текстовая версия
Подробное описание того, как пользоваться условным форматированием в Excel, рассказано в 3м вопросе 5-го занятия курса Excel от новичка до профессионала.
Здесь мы раскрасим полностью строку таблицы в случае, если произошло изменение цены для товара и просто отдельную ячейку, если товар новый или устаревший.
Условное форматирование вызывается из вкладки «Главная» и правило для выделения позиций у которых увеличилась цена выглядит следующим образом, главное, перед вызовом диалогового окна настройки правила на забыть выделить весь диапазон с таблицей.
Правило выделения строк таблицы, при условии уменьшении цены точно такое же, за исключением того, что мы ищем фразу «Цена уменьшилась», что логично ну и другие настройки цветовой раскраски.
Как уже было сказано выше, чтобы выделялась полностью строка в таблице необходимо выделять полностью таблицу перед созданием правила, что мы и делали при создании двух первых правил, а вот для выделения конкретной ячейки, нужно выделять уже не таблицу, а конкретный ее столбец перед созданием правила для условного форматирования.
На первый взгляд правила абсолютно такие, как и для цены, на самом деле так и есть, отличие заключается лишь в том какой диапазон был выделен перед созданием правила.
Кстати, в excel есть один хитрый трюк связанный с форматированием и, если вы ошиблись с определением первоначального диапазона для правила условного форматирования не стоит спешить с переделкой правила с нуля. Итак, большинство знакомо с таким инструментов в MS Office, как формат по образцу, этот инструмент есть и в Excel, и в PowerPoint и в Word и позволяет копировать форматирование одного участка документа на другой. Однако, в Excel он отлично работает и для условного форматирования, ведь это тоже форматирование, таким образом, если вы создали правило, которое выделяет только ячейки одного столбца, а потом ходите скопировать это форматирование на остальную часть таблицы, достаточно просто скопировать форматирование.
Кстати, это работает и в обратную сторону, если нужно убрать условное форматирование с определенных ячеек/ диапазонов столбцов, достаточно воспользоваться инструментом «Очистить/ Очистить форматы» из вкладки «Главная», группа «Редактирование».
- Визуализация отчета с помощью Power BI
Видеоверсия
Текстовая версия
На вкладке «Home» выбираем команду «Get Data» и выбираем источник данных, в нашем случае, книга Excel.
На следующем этапе выбираем лист/ листы, которые будут выступать источником для визуализации, в нашем случае это лист «Сводная сравнение» и нажимаем кнопку «Load».
Кстати, если изначально планируется использовать визуализации Power Bi, то нет необходимости выгружать данные на лист Excel, можно подключаться непосредственно к запросу Power Query. запросы находятся вверху списка.
После подключения к источнику данных выбираем тип визуализации – таблица и отмечаем поля для вывода, поскольку подготовку данным мы сделали на этапе работы с Power Query, то просто отмечаем все доступные столбцы.
Из мелочей, которые просто реализовать, можно использовать фильтр. Для добавления фильтра убираем фокус из таблицы, кликая в пустое пространство страницы Power BI, а затем выбираем фильтр из панели визуализаций. В качестве поля по которому будут фильтроваться значения, выбираем поле «Примечание».
На этом, пока все.
Файл с примером
Поскольку в редакторе Power Query прописываются абсолютные пути к файлам, то, чтобы сравнение работало корректно на вашей системе, необходимо изменить пути к старому и новому файлу. Это делается в самом редакторе запросов.
Уважаемые пользователи, архив с примером вы можете также скачать в ветке нашего форума, где задать вопрос, или просто обсудить материал.
Ссылки по теме
Как сравнить два файла MS Excel (с использованием функции ВПР и СУБД Access)