Опыт разработки макросов и надстроек для Мойофис показал, что можно автоматизировать практически любой процесс, который раньше выполнялся с использованием 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) оба предыдущих варианта вместе. Как показывает опыт, последний вариант хоть и затратнее, но при нём оперативнее решаются вопросы в случае каких-то сбоев или апгрейда макросов.
