Как Excel & Power Query помогают бороться с компьютерной безграмотностью местных налоговиков

Государственная Фискальная Служба Украины не привыкла баловать граждан данными в удобном формате. Нет-нет, пресс-релизы здесь уже научились худо-бедно писать и выкладывать на сайт. Потому что без них никак нельзя, иначе общественность забудет.

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

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

К счастью, ситуация далеко не безвыходная, во всяком случае, для пользователей последних версий Excel, работающих под Windows. С помощью надстройки Power Query и текстовых формул подобные задачи решаются минут за 20-30 (в зависимости от параметров компьютера), а то и быстрее.

***

Но сначала небольшое лирическое отступление. В январе текущего года автор и ведущий подкаста MyExcelOnline собрал 17 MVP Excel & лучших экспертов в мире по Excel и попросил их поделиться одним самым важным советом по продукту, который каждый из них лично испробовал в 2018 году. Четверо из 17 участников подкаста, не сговариваясь, ответили: «Осваивайте работу с Power Query» (бесплатной надстройкой в составе Excel, позволяющей писать запросы любой сложности и обрабатывать огромные массивы данных без потери продуктивности).

Что ж, подумала я тогда. Эти люди знают, о чем говорят, иначе бы не получили статус MVP. Надо будет и мне поглубже копнуть все возможности Power Query и поискать сходства и отличия от базовой версии Excel.

***

Самый простой сценарий — взять столбец с записями разной длины и сложности и сравнить, как реализовано его дальнейшее деление на столбцы в базовой версии Excel и в Power Query.

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

В Power Query же разделение по столбцам реализовано намного лучше плюс отсутствует необходимость загружать все данные одновременно. Кроме того здесь реализованы более удобные в сравнении с базовой версией Excel опции импорта данных: из текстовых файлов, csv-файлов, других Excel файлов, всевозможных баз данных, веб-сайтов и даже соцсетей (Facebook и пр.). Конечно, часть этих опций импорта доступна и без Power Query. Однако разве приятно каждый раз запускать запрос «с нуля», если речь идет о заборе данных в режиме реального времени?

Power Query лишен этих несуразностей. Все шаги запроса здесь автоматически фиксируются на языке M, поэтому однажды созданный запрос можно запускать бесконечное количество итераций. Лишь бы была связь с источником данных (что не всегда легко, ибо владельцы сайтов часто стараются выкладывать данные, которые тяжело читать компьютеру).

***

Покажу разницу на примере с сайтом Государственной Фискальной Службы Украины. Наша задача — трансформировать вот этот пресс-релиз с рэнкингом Топ-100 крупнейших компаний-плательщиков налогов по итогами 2018 года в удобную таблицу из трех столбцов.

Запускаем Power Query из вкладки Data и создаем новый запрос.

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

Предпримем еще одну попытку. Впрочем, опция Get external data нас тоже не особо выручит.

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

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

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

Нам надо превратить ее в таблицу из трех однородных столбцов: «Компания — Сфера деятельности — Сумма налогов».

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

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

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

Выбираем в окне Power Query вкладку Home, находим иконку Split, выбираем соответствующий разделитель.

Вуаля, теперь у нас две колонки — одна с названиями компаний, вторая — с суммами налогов в млн. грн.

Нарочно не пишу “с цифрами”, поскольку программа все данные во второй колонке цифрами не считает. Попытка поменять формат колонки с текстового на цифровой возвращает ошибку чуть ли не в половине записей. Проблема коренится в одном-единственном неделимом пробеле (в кодировке ASCI он соответствует 160) между первым и вторым знаком четырехзначных чисел.

Придется вновь выгружать данные в базовую версию Excel и призвать на помощь текстовые функции VALUE, SUBSTITUTE и CHAR.

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

Остается скопировать результаты запроса в основной рабочий лист «Топ-100», для чего смело жмем сочетания клавиш CTRL+ALT+V и выбираем Paste Values only.

Все, теперь на основе этого набора данных можно:

а) создавать всевозможные сводные таблицы и диаграммы,

б) объединять его с данными по собственникам или остальным финансовым показателям упомянутых компаний,

а также

в) сравнивать его с аналогичными рэнкингами за предыдущий и/или нынешний год и выявлять самых сознательных плательщиков налогов.

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