В прошлых частях этого триптиха (часть 1, часть 2), созданного по просьба ребят из CATAN, я много говорю о том, что для мгновенного ответа на вопросы нужны т.н. "водопады", т.е., упорядоченные массивы данных, которые можно скармливать компьютеру. Но возникает естественный вопрос: "А где мне взять эти самые водопады?" Эта статья помогает ответить на него.
Во-первых, «водопады» — наиболее естественная форма организации данных, поскольку все базы данных основаны именно на таких таблицах. Их форма придумана 50 лет назад и нет других альтернативных форм организации хранения информации.
Но человек — причина хаоса и из-за фантазии программистов и пользователей эти данные часто бывают испорченными в разной мере.
Проблему осознали все разработчики и во многих электронных таблицах появились средства подготовки данных к анализу. В Microsoft таким средством стало дополнение Power Query, которое появилось в Excel как неотъемлемая часть с 2016 года, а в нынешнем виде этот продукт может отсчитывать свою историю с 2019 года. Обладателям более старых версий Excel можно сильно не переживать — был выпущен специальный модуль для версий, начиная с 2010 года и его можно установить на свой компьютер при условии, что он работает под управлением Windows 7 как минимум.
Я буду рассказывать о том, как выполнять некоторые действия на примере своего Excel, а вам придётся сделать необходимые коррективы, если что не найдёте — пишите в комментах, подскажу.
Когда мы познакомились с ребятами из CATAN, и мы договорились об этом цикле статей про анализ данных, я попросил их прислать мне примерный набор данных, с которыми они работают, чтобы рассказ был максимально приближен к запросам читателей. И мне прислали вот такую папочку.
И в каждом файлике лежал вот такой водопадик:
Тут уже была подсчитана кое-какая статистика по каждой позиции, но хотелось бы её знать не для одного месяца, а по всему периоду. А ещё наверняка возникнут вопросы, на которые ответа в этих табличках нет. Поэтому надо соединить все файлики воедино, также отрезать от длинных названий только правую часть, обозначающую товар, а то в отчётах они будут смотреться не очень удобно, удалить лишние столбцы и сохранить информацию о том, за какой период сделаны записи. Это информацию придётся взять из имени файла.
Запускаем Excel и в пустом файле заходим во вкладку «Данные». Там ищем кнопку «Получить данные»
Указываем папку, из которой будем импортировать данные.
Файлы, лежащие в ней, не показываются, потому что задача этого диалога — показать папку, а файлы никуда не денутся, им придётся подчиниться. Давим «Открыть».
После небольшого ожидания появляется предварительный диалог. В нём показаны все файлы, лежащие в указанной папке.
Жмём кнопку «Объединить» и выбираем команду «Объединить и преобразовать данные».
В следующем диалоге надо указать файлик из набора, который наиболее типичен и его можно использовать как образец для формирования итогового «водопада». Я оставил вариант «Первый файл», поскольку все файлики в этой папке единообразны. В этих файликах может оказать много листочков и тогда надо выбрать тот листочек, который содержит искомую информацию. В нашем примере лист всего один и выбор простой — просто тыкнуть по строчке с его именем «Аналитика категорий». Справа покажется предварительный просмотр содержимого листа, чтобы мы могли оценить правильность выбора файла и листа и можно давить OK.
Запускается окно Power Query. В ней три основные части:
Панель запросов — содержит названия всех таблиц, с которыми вы сейчас работаете. Сейчас в ней много технической информации в верхней части, на которую мы не будет отвлекаться и одна строка с названием итогового массива «Водные виды спорта» по имени папки, которая содержит исходную информацию. Кстати, соединение всех таблиц в папке уже произошло. И если бы нам этого было достаточно, то можно было бы уже выходить из Power Query (PQ) и лицезреть итоговый водопад. Но нам предстоит ещё кое-то сделать в нём.
Предварительный просмотр — показывает, что произойдёт с данными после наших преобразований. Важно понимать, что мы сейчас ничего с данными не делаем, а проектируем будущие изменения. Также важно понимать, что PQ не даёт возможности работать непосредственно с данными в ячейках и поэтому несмотря на похожесть предварительного просмотра на электронную таблицу, мы не можем зайти в клетку и исправить в ней ошибку. Мы можем думать только о массовых преобразованиях, которые затрагивают все строчки.
Список действий — это конечная цель всей нашей работы. Работая в PQ мы составляем список будущих действий с данными, создаём программу, но не как программисты, а как проектировщики, рассуждая в жизненных категориях, «что бы нам надо было сделать с этим столбцом», а PQ сам переведёт наши пожелания на свой язык и составит из них программу будущих преобразований. Это чем-то похоже на запись макроса, но в продвинутой форме.
Кстати, язык программирования, на котором происходит запись наших действий имеет имя из одной буквы «M», так что если вам потребуется программист для реализации сложных проектов в PQ, то надо писать в объявлении «Ищу программиста на языке M».
Какое-то количество действий в этом списке уже есть. Мы можем продолжать. Каждое наше действие будет записываться сюда. Классической «отмены действий» нет, но всегда можно удалить неудачную операцию, даже из середины.
Нам надо удалить из столбца «Название» лишнюю часть слева, оставив только название товарной категории. Зацепимся за то, что слева от собственно названия категории, например, слова «Рюкзак», стоит знак «>».
Тыкаем правой кнопкой мыши по заголовку столбца и выбираем «Разделить столбец» — «По разделителю...»
Возникает диалог с настройками разделения. В нём скорее всего стоит вариант пользовательского разделителя, указан сам разделитель «>» и нам остаётся только указать, что нужен только самый правый разделитель и нажать OK.
В результате мы видим, что вместо одного столбца «Название» появилось два: «Название.1» с лишним текстом и «Название.2» с именем товарной категории. А справа в списке действий мы видим новые действия, которые мы спроектировали.
Правой кнопкой мыши тыкаем по заголовку «Название.1» и выбираем команду «Удалить». Посмотрите как в списке действий после этого появится очередная запись.
Теперь надо двойным щелчком мыши по заголовку «Название.2» запустить его переименование в «Товар».
Думаю, вы справитесь со столбцами «Месяц» и «Год», которые можно получить из столбца с именем файла «Source.Name». При этом ненужные остатки от этого столбца стоит удалить.
В столбце «Год» у вас только 2 цифры года, с ними некрасиво работать, давайте их увеличим на 2000 при помощи команды: Выделить столбец «Год» — вкладка «Преобразование» — «Стандартный» — «Добавить»
В следующем диалоге пишем число, на которое хотим увеличить каждую позицию в столбце и давим OK. В итоге видим преобразованный столбец и новые действия в списке действий.
Справа в нашей табличке есть какое количество расчётных столбцов, которые вообще-то лишние, но пусть остаются, жалко ведь их. :-)
Во вкладке «Главная» жмём кнопку «Закрыть и загрузить» и в наш пустой файлик сбрасывается результат наших спроектированных преобразований.
В результате получаем водопад, соединённый из множества файликов, лежащих в одной папке и преобразованный по наших хотелкам.
Самое время открывать часть 1 и часть 2 моего триптиха. чтобы перечитать или записаться ко мне на курсы «Excel для управления и анализа» и «Power Query — будущее Excel», а также обратиться к ребятам из Catan, чтобы они вам подсказали, какие вопросы задавать к данным, полученным от маркетплейсов, чтобы заработать побольше денег и сделать свою жизнь и жизнь окружающих лучше.
Всем добра в материальном и прочих смыслах!
Возврат к списку
