Из оператора в Data-инженеры: выверка данных через шаблоны Excel
Всем привет! Меня зовут Ксения, в 2019 году я пришла в СИГМУ оператором по оцифровке ГИС-планшетов с местоположением кабельных линий. Учитывая специфику деятельности компании, работа с большими данными — ежедневная практика. И если не владеть языками программирования (как в моем случае), на выручку может прийти Excel.
Внутри команды мы используем специальный шаблон для выверки данных распределительных электросетевых компаний. Он оказался настолько простым и удобным, что постепенно вместо ручной обработки данных я полностью перешла на работу в Excel. А через год работы неожиданно поняла, что научилась программировать. Это произошло настолько органично! Я даже не успела подумать, что вообще-то гуманитарий и у меня не получится использовать айтишные инструменты.
Сейчас я могу самостоятельно подготовить многоуровневый отчет, написать SQL-запрос с регулярными выражениями или создать запрос на новую разработку, которая будет понятна нашим программистам.
В этом материале хочу поделиться своим опытом работы в шаблоне Excel, который помог мне стать экспертом по выверке данных.
Начну с главного
Вот ссылка для свободного скачивания и использования.
P.S. При открытии может появиться сообщение о блокировке файла, пугаться не нужно. Решается все очень просто:
Откройте проводник Windowsи перейдите к папке, в которой сохранили файл.
Щелкните файл правой кнопкой мыши и выберите «Свойства» в контекстном меню.
В нижней части вкладки «Общее» установите флажок «Разблокировать» и нажмите кнопку «Ок».
Шаблон будет полезен не только специалистам с начальным уровнем подготовки, но и всем, кто хочет формализовать работу с данными и повысить свою производительность.
Какие компоненты входят в шаблон:
Автоматическое формирование заголовков для столбцов;
«Удобности» для функции ВПР;
Загрузка данных из внешних СУБД;
Пользовательские функции;
Заготовки для быстрого создания сводных таблиц.
Специфика распределительных сетей такова, что один и тот же объект в разных источниках может называться по-разному. Например, ввод 1 и «Жилой дом ул. Лесная 8». И в базе с номером ввода 1 нет ни слова про улицу Лесная. То есть связь таблиц нужно делать по косвенным признакам. Например, по значению замеров зимнего максимума и других аналогичных полей. Думаю, что и в вашей деятельности есть подобные нюансы.
Чего помогает добиться шаблон:
Быстрой связи данных из различных цифровых источников.
Условий для командной работы за счет единой формализованной среды, которую понимают все члены команды, и использования этого инструмента для постановки задач аналитикам и разработчикам.
Среды для быстрого и качественного обучения, а также последующего перехода на более высокий уровень специалистов даже с минимальной подготовкой.
И немного контекста. В своей работе я консолидирую данные по загрузке трансформаторов и низковольтных кабельных линий, это более 300 тыс. строк в отчете. А также работаю с координатами распределительных устройств по милицейскому адресу, те задаю алгоритмы для выделения адреса из 110 тыс. неформализованных наименований распределительных устройств. Все это великолепие усложняется расшифровкой сокращений в названиях улиц и удалением лишних фраз: подъезд, этаж, жилой дом и т.д.. Наконец, я создаю условия для написания адаптированной под специфику сетевой компании программы разбора данных на основе регулярных выражений. Где, например, по одной букве Ф можно догадаться, что речь идет об улице Фрунзе, так как в окрестностях трансформаторной подстанции N есть только одна улица с буквой Ф.
В материале я сначала покажу особенности шаблона, который может стать основой для создания других рабочих книг, а затем расскажу о примерах своей работы в Excel.
Особенности шаблона
Внешне шаблон выглядит следующим образом.
В зависимости от типа данных, листы книги раскрашиваются в различные цвета.
Цвет листа | Имя | Примечание |
Нет | readme | Оглавление и общая информация о назначении файла Excel |
Нет | SQL | Заготовки с наиболее популярными SQL-запросами для выгрузки данных из внешних СУБД |
Синий | 1, 3 | Основные данные загружаются на лист из СУБД с помощью SQL-запросов |
Зеленый | 2 | Наименования полей таблицы и комментарии к полям для автоматического формирования заголовков на листе 1 |
Красный | 4 | Данные добавляются на лист без загрузки из внешних СУБД (вручную) Для каждого нового справочника создается новый лист |
Оранжевый | 1+, 3+, 4+ | Заготовки сводных таблиц для «синих» и «красных» листов Лист 1+ содержит шаблон сводной таблицы для листа 1 Сводная таблица опирается на диапазон с именем «База1» |
В шаблоне зарезервированы строки с 1 по 10, и каждая имеет свое назначение.
Номера строк | Назначение |
1-3 | А3 – формула для формирования названия листа А2 – формула для расчета количества активных строк на листе Прочие ячейки используются произвольным образом |
4 | Номер столбца на листе. Используется для функции ВПР. На эти ячейки ссылаются формулы ВПР, которые находятся на других листах книги |
5 | Содержит формулу для нахождения номера столбца в функции ВПР. Пример формулы: "='4'!$H$4-'4'!$B$4+1" |
6,8 | Строки для автоматического формирования наименований столбцов официальных отчетов. Наименование поля и порядковый номер поля в отчете |
7 | Содержит детальные комментарии к содержанию столбца. Текст комментариев берется из комментариев к полям СУБД |
9 | Строка для сохранения формул в качестве эталона При обработке больших объемов данных формулы в таблице могут заменяться на их значения. Формулы, сохраненные в этой строке, позволяют вернуть формулы в диапазон с данными и выполнить необходимые расчеты |
10 | Основные заголовки столбцов По умолчанию равны названиям полей из СУБД |
Автоматизируем заголовки отчетов
Преимущество шаблона заключается в автоматическом формировании заголовков официальных отчетов. На листе 1 строки 6-8 содержат значения, автоматически подгружаемые из листа 2.
Названия полей и комментарии к полям загружаются вот таким запросом:
SELECT a.Column_Name as Поле, a.Column_ID as ID, b.COMMENTS as Коммент
FROM all_tab_columns a
left join all_col_comments b on a.owner = b.owner and a.table_name = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.table_name = 'НАЗВАНИЕ_ТАБЛИЦЫ' AND a.owner = 'НАЗВАНИЕ_СХЕМЫ'
Order by a.column_ID
Тут также есть свои особенности. Комментарии к полям делятся на 2 части:
До двух восклицательных знаков идет «официальная» часть. Это краткое название поля, которое будет выводиться в заголовке отчета. Рекомендуемая длина до 30 знаков.
После двух восклицательных знаков идет подробное описание поля, которое может быть длиной до 4000 знаков (минус длина официальной части).
Заголовки на листе 1 заполняются с помощью формулы:
=ЕСНД(ВПР(B10;Запрос2;'2'!$E$4-'2'!$B$4+1;0);"")
Лирическое отступление. Настройки панели быстрого доступа
Шаблон — отличный инструмент для ускорения и оптимизации работы в Excel с электронными таблицами. Это «адаптированное пространство» для быстрой и эффективной работы человека с начальным уровнем подготовки. Но, как и с любым другим сложным инструментом, все начинается с настроек. Чтобы возможности программы было использовать приятнее, советую настроить панель быстрого доступа – вроде, базовые инструменты, но помогают сократить цепочку действий и сэкономить минуты (которые потом выливаются в часы).
Для настройки достаточно загрузить в настройку панели быстрого доступа уже готовый файл с настройками (1).
Или настроить самостоятельно. Советую использовать следующие элементы.
Удобная методика работы с функцией ВПР
Функция вертикального просмотра (ВПР) является одной из универсальных и часто используемых при работе с данными в Excel. На просторах сети про ВПР написано немало, поэтому нет смысла останавливаться на том, что это такое и как работает. Но у нас есть свои «фишки» для этой функции.
Вот две основные:
Использование именованных диапазонов. Например, База1, если используем для сверки первый столбец именованного диапазона База1.
Формирование Индекса для столбца. Это гарантия того, что функция будет правильно работать после добавления или удаления новых столбцов на листе 1.
Обычно при использовании ВПР нужно указать диапазон ячеек для вычислений. Для того, чтобы каждый раз не вспоминать, какие же ячейки нужны, и не наматывать километры курсором по документу мы создаем «Базу», в которой закрепляем нужную нам область ячеек с данными. Обязательное условие для того, чтобы все это работало — столбец, по которому выверяем данные, должен быть первым (крайним левым) в данной базе. На одном листе может быть несколько баз, главное — не запутаться с их наименованиями.
Чтобы быстро найти или добавить новый именованный диапазон, кнопка «Диспетчер имен» должна быть вынесена на панель быстрого доступа.
Как я уже упоминала, вторая отличительная особенность нашего шаблона — использование индекса для столбца. Часто при сверке используются данные с разных листов документа, и проставленные ссылки на столбцы, при добавлении новых данных могут съехать. Использование индекса для столбца и абсолютных ссылок фиксирует «привязку» формулы к нужному столбцу и гарантирует, что формула не «поплывет» при добавлении новых данных.
Данные для функции ВПР подтягиваются с разных листов через «ключ» — поле с одним и тем же параметром для обоих листов. Ключ может быть просто табличным значением, а может составным — из значений двух или более полей. Чтобы каждый раз не указывать диапазон ячеек, можно создать базу на листе, с которого будут подтягиваться значения.
Пример основной формулы:
=ВПР([@Ключ1];База4;$D$5;0)
Формула для получения номера столбца (ячейка D5 на листе 1):
= '4'!G4-'4'!B4+1
В каждой ячейке строки 4 листа 4 стоит формула «=СТОЛБЕЦ()», которая всегда показывает актуальный номер столбца. То есть при добавлении нового столбца на лист 4 номер столбца для ВПР автоматически изменится.
В шаблоне есть заранее заданные наименования диапазонов: «База4», «Запрос2».
Загрузка данных из внешних СУБД
Работая с Excel, рано или поздно мы сталкиваемся с необходимость загрузки каких-либо данных из внешних источников. И если внешний источник — другой файл, то можно все решить стандартным копированием, либо сохранить данные, изменив формат файла.
Если же внешний источник — база данных — нужен запрос SQL. Для этого на вкладке «Данные» нужно выбрать «Свойства» (1). И в открывшемся окне «Свойства внешних подключений» открыть окно «Свойства подключения».
В строке подключения (3):
DSN=нужная база
UID=логин
PWD= пароль
DBQ=база
в окне Текст команды (4) запрос:
SELECT *
FROM SCHEME.TABLE (нужная схема.таблица)
WHERE S1_NUM='23456'
Если выборку данных из таблицы нужно ограничить — добавляем необходимые условия.
Меняя запрос, можно экспортировать в Excel любой набор данных из одной или нескольких таблиц и обрабатывать эти данные в привычном «офисном» формате.
Пользовательские функции, которые облегчают жизнь
В нашем шаблоне есть пользовательские функции, предусмотренные для упрощения обработки данных, в том числе, созданные специально для обработки данных электросетевых компаний. Остановиться хочу лишь на нескольких.
=f_SuperMid() — последовательно вырезает из текстовой строки блоки текста, находящиеся между двумя подстроками. Функция особенно выручает, когда в большом объеме данных нужно удалить один и тот же текст.
Если нужно осуществить поиск назад (справа налево) — указываем значение «TRUE», если вперед (слева направо) — «FALSE».
1 - формула, 2 - первоначальный текст, 3 - результатТаким образом, на скриншоте:
f_SuperMid(B58; "находится ;-та"; "false") — вырезает из первоначального текста часть между «находится» и « –та» и получает — «на балансе аб».
f_SuperMid(B58;"ТП;(на";"false") — вырезает из первоначального текста часть между «ТП» и « (на» и получает — номер ТП и район.
=f_NumbersOnly — удаляет из строки текст, преобразует текстовую строку в строку из чисел, разделенных пробелом (или другим разделителем). Идеально подойдет, когда нужны сухие цифры, а буквы «только мешают».
=f_TextOnly — противоположная предыдущей функции. Работает, когда нужны только текстовые значения. Удаляет из строки цифры, преобразует текстовую строку в строку из букв, разделенных пробелом.
=f_СцепитьДиапазонВСтроке — преобразует данные нескольких ячеек в единый текст одной строкой. Может собрать в строку любое количество ячеек.
Быстро, но не всегда удобно, так как функция соединяет текст без пробелов. Если знаки препинания и пробелы нужны — используйте «сцепить» с амперсандами и т.д.
=F_DeleteSubstrings — вырезает из оригинального текста заданный блок. Эта функция позволяет за одно выполнение удалить до 5 блоков «мусорного» текста. При необходимости можно прямо в коде макроса поменять 5 на 50 и автоматически собрать «длинную» формулу для удаления всего «мусорного текста».
Таким образом, на скриншоте с формулой (1) f_DeleteSubstrings(B99;$D$10) в столбцах отображается результат после удаления первого блока «подъезд строение» (2). Блок записан в строке наименований, в формуле закреплена ссылка на ячейку с текстовым блоком. Также можно получить результат после удаления второго блока «на балансе абонента» (3).
=f_FindLastNumber — находит и вырезает последний блок символов в строке после пробела. Блок может быть любым: текстовым, числовым, смешанным.
Есть еще более «узкая» функция f_FindNumbersNearKeyWords. Она используется для нахождения данных по текстовому ключу. В моем случае применяется для нахождения номера района в диапазоне от 1 до 25 (используемые нами, при желании можно изменить диапазон в скрипте функции).
Она находит и вырезает номер слева от указанного ключа «РЭР». Удобна для обработки формализованных данных, в которых используются одни и те же названия территориальных единиц. То есть везде должен быть использован либо «район», либо «РЭР». Если используются разные названия территориальных единиц, можно «докрутить» с помощью встроенных функций, например, «ЕСЛИОШИБКА»:
Конечно, многие из этих функций можно заменить формулами и получить тот же результат. Но чаще всего эти пользовательские функции — более простое и «красивое» решение, особенно если речь идет о недавних пользователях Excel, еще не освоивших весь возможный функционал.
Главное — быстро. Создание сводных таблиц
Еще один из нежно любимых инструментов Excel — cводные таблицы. Они позволяют максимально быстро преобразовать любое количество строк данных в краткий отчет. Дополнительный плюс — возможность очень оперативно изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Использование сводных таблиц даже на базовом уровне экономит огромное количество времени, при этом качество и точность анализа вырастают в разы по сравнению с попыткой «свести» данные вручную.
В шаблоне предусмотрены заготовки сводных таблиц для каждого листа с данными. Пример формирования отчета из данных электронной таблицы:
И далее отчет, сформированный из них. Не забываем, что заказчики у СИГМЫ — крупнейшие электросетевые компании, поэтому пример отражает специфику отрасли.
В отчете было сверено 3543 строки с местоположением трансформаторных подстанций. Из них однозначно верное местоположение у 881 ТП, остальные — ошибки с разными статусами: местоположение либо уже уточняется, либо неизвестно и будет выясняться посредством анализа наименования, сопоставления с названиями населенных пунктов и так далее. Так же хорошо визуализируется балансовая принадлежность — ТП на балансе и абонентские ТП.
Чем еще удобны сводные таблицы — быстрой трансформацией результата в зависимости от запроса (на основании одних и тех же данных). Например, ниже выведено процентное соотношение того или иного статуса ТП от общего количества.
При необходимости фильтры помогут визуализировать только часть данных, что помогает для составления отчета под разные задачи.
Поговорим о применении
Здесь я приведу примеры того, как шаблон решает мои рабочие задачи. Уверена, что эти примеры можно адаптировать и под вашу деятельность. Если нужно, могу поделиться этими шаблонами по запросу.
Шаблон Ведомости загрузки трансформаторов 6-20 кВ
Исторически данный отчет формировался из 60 различных источников информации и подготавливался не менее недели. С помощью базового шаблона Excel я смогла подготовить отчет и одновременно сделать формализованную постановку для наших программистов.
Сейчас отчет, который содержит около 14 млн. полей, полностью автоматизирован и создается за 1 час. Все вычисления делаются в СУБД. Данные загружаются в Excel через ODBC с помощью SQL запроса.
Шаблон для создания таблиц Oracle
При разработке отчета «Ведомость загрузки трансформаторов (ВЗТ)» я использовала шаблон для автоматического создания таблиц в СУБД.
Особенности:
Создает строки SQL-запроса, с помощью которого можно создать требуемую таблицу.
Позволяет правильно подобрать названия полей для новой таблицы, определить тип поля и быстро заполнить поле «comment». Выгружает названия поля, тип и комментарии из таблицы, набора таблиц или целой схемы.
Шаблон для Макросов, эмулирующих ручной ввод
С помощью этого шаблона я могу создать макросы, которые «нажимают» кнопки в программе вместо оператора-человека. На скриншотах ниже приведен макрос, который загружает в систему трансформаторные подстанции с определенными ID и размещает их в точки с координатами X и Y.
Вот так выглядит макрос для двух ТП:
sigma.Callback("PoiDisplayDeviceCardsMCB", "", "11642725");)
sigma.TabChange("11642725, "Container_823", "Container_896");
sigma.ValueChange("11642725", "y", "8446,9609375");
sigma.ValueChange("11642725", "x", "23436,734375");
sigma.Callback("PoiDisplayDeviceCardsMCB", "", "11642726");)
sigma.TabChange("11642726, "Container_823", "Container_896");
sigma.ValueChange("11642726", "y", "8446,9609375");
sigma.ValueChange("11642726", "x", "23436,6484375");
Итоги
Неожиданно для себя за 2 года я смогла пройти путь от «уверенного пользователя» пакета MS Office до специалиста, способного за час проанализировать 5 источников, в которых хранятся данные о топологии сети 0.4-20 кВ. Параллельно найти ошибки и противоречия, и собрать из этой информации наиболее достоверную и непротиворечивую схему нормального режима, на которой сможет работать наш модуль по автоматическому расчету технических условий на технологическое присоединение.
Несмотря на то, что сейчас я уже многие вопросы решаю в самой базе данных, мне сложно представить работу без этого шаблона. Он помогает решать массу рабочих задач и является отличным инструментом обучения методам анализа данных новых сотрудников как в СИГМЕ, так и у заказчиков.
В шаблоне используются не самые популярные возможности Excel, но пользу он принесет как специалистам с начальным уровнем подготовки, так и уверенным пользователям. Особенно он облегчит жизнь тем, кто загружает данные из внешних баз, так как шаблон позволяет обрабатывать информацию в привычном формате даже без навыков работы с программными продуктами для обработки БД.
И еще несколько плюсов из практики применения шаблона:
Обучение работе с шаблоном нового сотрудника занимает 1-2 недели. И это без предварительного опыта работы с Excel.
Через 6-12 месяцев после начала работы с шаблоном 50% сотрудников способны перейти на работу с типовыми SQL-запросами, которые они берут готовыми из Глоссария. То есть фактически они начинают выполнять работу программистов. Шаблон Excel помогает преодолеть психологический барьер и сделать шаг от «у меня не получится» до «это же так просто».
На этом сегодня — все! Буду рада, если воспользуетесь заготовками и сможете улучшить свою производительность при работе с данными. Если появились вопросы, обязательно их задавайте. Постараюсь быстро и подробно поделиться всем, чему смогла научиться. А еще буду рада, если подкинете лайфхаки, которые не были обозначены, но могут быть использованы в повседневной работе с Excel.