Мойофис: автоматизация отчётности в банке
Опыт разработки макросов и надстроек для Мойофис показал, что можно автоматизировать практически любой процесс, который раньше выполнялся с использованием Excel и макросов на VBA. А надстройки (о них не сегодня) вообще позволяют создавать корпоративные инструменты в связках «Python-Мойофис», «Nanocad-Мойофис» и т. п. Вот пример одной учебной задачи по автоматизации отчетности в Таблице Мойофис.
Задача
В работе банка много несложной, но объёмной рутины, которая генерирует свои датасеты. В нашем примере это отчет о работе обменного пункта. Данные по итогу каждого дня формируют электронную таблицу с определённой структурой. Таблицы складываются в листы xlsx-файла. Первые два листа — «Report-1» и «Report-2» — итоговый отчёт за 6 предыдущих полных месяцев. Здесь файл без макроса, а здесь то, что должно получиться. Кроме «репортов» в файле 139 листов — ежедневных отчётов. Пункт работает только с двумя валютами — USD и EUR. В итоговом отчёте мы хотим видеть на первом листе — количество купленных евро и долларов по каждому месяцу, количество проданных евро и долларов по каждому месяцу, на втором листе — общую прибыль от операций с евро и долларами по каждому месяцу.
Решение
Начало
Если представить наш файл как классический DWH, то можно сказать, что у нас есть tmp-слой — 139 таблиц ежедневных отчетов, cdm-слой — первые два листа. Не хватает raw-слоя. Им и займёмся в первую очередь. Для этого создадим два массива или две таблицы — в терминах Lua: months и datatable. В months пропишем месяцы. А datatable — и есть наш raw-слой, т. е. таблица с данными о количестве купленной, проданной валюты и прибыль по месяцам. Каждая строка этой таблицы начинается с месяца. Далее идут соответствующие поля. Поскольку все строки имеют одинаковые поля, наполним таблицу с помощью цикла. В Lua для перебора элементов таблицы используется цикл for совместно с функцией pairs (), позволяющей на каждой итерации получать ключ и значение очередного элемента. В нашем случае i — это ключ с числовым значением от1 до 12, а val — значение, т. е. название месяца. Остальные поля имеют числовые значения, равные нулю (строки 7…10).
В Таблице Мойофис десятичные дроби в качестве разделителя имеют запятую. В Lua такой набор символов определяется как «string». Для проведения расчётов необходимо эту строку преобразовать в «number». Среди строковых функций Lua есть gsub (), которая возвращает копию исходной строки, в которой все вхождения шаблона заменены на строку, заданную третьим аргументом. В нашем случае исходная строка (s) это десятичная дробь с запятой-разделителем. Как шаблон укажем выражение »%D » — любой знак кроме цифры. Наконец строка, которой заменяется соответствующая шаблону подстрока, это ».». Теперь, полученную строку, состоящую из цифр и точки преобразуем в «number» функцией tonumber () из базовой библиотеки Lua. Чтобы каждый раз не прописывать эту процедуру, обернём её в функцию и назовём StrToNumb () (строки 12…16).
Извлечение данных
Чтобы получить полную картину, нам понадобится просмотреть каждый лист документа и получить из него нужные нам данные. Метод getBlocks (): enumerate () возвращает коллекцию листов документа. Переберём всю коллекцию циклом for (строка 20). На каждой итерации сначала получим название таблицы (строка 21). Конструкцией If-Else исключим из обработки листы Report-1 и Report-2 (строка 22). Для всех остальных листов вначале определим текущий месяц. Название каждого листа — это строка из 8 символов. Четвёртый и пятый символы — подстрока номера месяца. Преобразовав эту подстроку в число, получим ключ элемента массива months — текущий месяц (строки 23, 24).
Так как количество строк на каждом листе разное, а интересующие нас данные находятся на последних двух заполненных строках, требуется найти номер строки перед ними. Для этого опять используем цикл for с инструкцией break (строки 26…36) проходя им колонку «Время». Последняя заполненная строка в этой колонке — точка отсчёта для итоговых строк.
Теперь, укладываем содержимое каждой интересующей нас ячейки в соответствующую переменную (строки 38…43)., а затем элементы таблицы datatable увеличиваем на величину полученных данных (строки 45…55). И закрываем оператор If и цикл for (строки 56, 57).
Загрузка данных в отчёт
В отчёт должны войти данные за последние 6 полных месяцев. Номера этих месяцев собраны в массив ReportMonthNdx (строка 64). В строках с 65 по 80 — механизм определения этих месяцев.
Теперь определим адреса ячеек на отчётных листах, куда нужно выгрузить данные из raw-слоя, т. е. из таблицы datatable (строки 82…87).
Наконец, выгрузка данных в отчёт. Название месяцев загрузим методом setFormattedValue (), который автоматически определяет формат ячейки листа как «Общий» (строки 95, 96). А вот для числовых данных с разделителем «точка» этот метод не подходит — он определит формат ячейки как «Общий» и диаграммы не построятся. Поэтому, используем «трансформатор» setNumber (), который форматирует ячейку в «Число»(строки 98, 100, 102, 104, 106, 108).
Итог
Запуск макроса через несколько секунд даст следующий результат:
Поставленная задача решена, и при желании отчёт можно обогатить другими данными, в том числе агрегированными. Но есть и недостатки:
Нет ограничения на количество дней в месяце. Если продублировать какой-нибудь августовский день раз двадцать, то в отчете обнаружится «удивительная аномалия» в августе.
По той же причине в отчёт нельзя вставлять данные более чем за 12 месяцев. В противном случае данные за два одинаковых по названию месяца суммируются в одну строку таблицы datatable.
И конечно, цветовая палитра графиков — «вырви глаз»! Хочется надеяться, что разработчики Мойофиса, если не поменяют палитру, то хотя бы очередность цветов изменят. А лучше — предоставят возможность настройки цвета.
В целом, этот пример призван показать, что для анализа оперативных данных, можно не прибегать к серьёзным инструментам типа Data Lens, Tableau, PowerBI и т. п. Достаточно правильно описать задачу и разработать соответствующий макрос для «Мойофис Таблица». Конечно, для разработки таких макросов требуются определённые навыки, которыми вряд ли обладает среднестатистический работник банка. Но есть три рабочих схемы — 1) обучить кого-то из сотрудников (только не IT-шников) писать макросы на Lua, 2) пригласить стороннего разработчика (консультанта) и, наконец, 3) оба предыдущих варианта вместе. Как показывает опыт, последний вариант хоть и затратнее, но при нём оперативнее решаются вопросы в случае каких-то сбоев или апгрейда макросов.
