v8: Excel-подобные вычисления для финансовых отчетов
Ключевые слова: финансы,отчеты,excel,вычисления
Начальные данные
Любой отчет состоит из строк и колонок.
Опишем все строки и колонки таблицы в виде структуры:
* Идентификатор - идентификатор строки/колонки (неуникальный);
* Категории - категории строки/колонки через точку с запятой;
* Родитель - идентификатор родителя.
* Формула - формула для вычисления значения строки/колонки.
* Итератор - функция для получения списка строк/колонок.
* Формат - формат вывода строки/колонки (необязательное)
* Название - название статьи для отчета (необязательное)
Структура может содержать и дополнительные поля. Например в поле формат можно указать идентификатор формата, которым выводится строка.
Проще всего описание строк и колонок хранить в таблице значений, которую загружать из макета MXL или XML.
В макете особенно удобно хранить формулы.
Алгоритм вывода отчета
Вывод начинается с первой указанной в описании строки и колонки.
При этом данные для отчета должны быть уже готовы и размещены в одной или нескольких таблицах значений.
После вывода очередной строки/колонки ищутся строки/колонки, у которых поле родитель ссылается на текущую строку и выводятся эти строки.
Если у строки задан итератор, то выводится не одна строка, а получаются все значения итератора и строка выводится для каждого значения итератора.
После получения всех строк и колонок отчета для каждой пары строка - колонка вычисляется значение ячейки.
При вычислении значения в ячейки используется поле формула описания строки/колонки.
Если при вычислении значения формула пытается обратиться к ячейке, которая еще не вычислена, или встречается синтаксическая ошибка в формуле, значение ячейки помечается, как еще не вычисленное.
Последовательно выполняются прогоны для вычисления невычисленных ячеек, пока их не останется, или их количество не зафиксируется (для избежания зацикливания).
После этого отчет выводится - есть набор строк и колонок. Каждая строка и колонка ссылается на строку описания, где можно определить заголовки и формат выводимых строк/колонок/ячеек.
Формулы
Формула представляет собой некоторый код на языке 1С, результат должен быть помещен в переменную Р. Можно использовать любые локальные переменные.
В поле формула можно использовать например такие функции:
фоСумма(Адрес) - суммирует все значения по указанному адресу
фоПроцент(Адрес1, Адрес2) - вычисляет процент значения в Адрес1 от Адрес2.
фоУмножить(Адрес1, Адрес2) - умножает каждый элемент массива, полученного по Адрес1 на каждый элемент массива по адресу Адрес2.
фоЗнач(Адрес) - значение по указанному адресу.
фоСлуч() - случайное значение для отладки.
фоДанные() - данные из базы данных для текущей колонки и строки.
Также можно использовать для числовых значений обычные операции +,-,*,/ и другие функции 1С.
Формула в строке ищет значения в строках текущей колонки, адресуемых адресом.
Формула в колонке ищет значения в строках текущей колонки, адресуемых адресом.
Адресация
Адрес представляет собой строку, в которой перечислены идентификаторы и категории.
Так как адрес для строк ищет строки в текущей колонке, чтобы выбрать другую колонку(и), нужно перед адресом колонки поставить знак "#"
Знак "!" используется для указания идентификатора текущей строки/колонки, соответвенно "#!" для указания текущей колонки/строки.
Естественно, адрес может ссылаться на одну ячейку, но и на множество ячеек.
Примеры:
* "Доходы." обозначает все ячейки текущей колонки, непосредственно подчиненные строке с идентификатором Доходы.
* "Доходы:" обозначает все ячейки текущей колонки, подчиненные (на любом уровне) строке с идентификатором Доходы.
* "Доходы;Выч" обозначает все ячейки текущей колонки с идентификатором или категорией "Доходы" или "Выч".
* "Доходы.;Главн." обозначает все ячейки текущей колонки, непосредственно подчиненные строке с идентификатором Доходы и Главн
* "Доходы.;#Всего" обозначает все ячейки текущей колонки, непосредственно подчиненные строке с идентификатором Доходы и находящиеся в колонках с идентификатором Всего.
Пример
Например, нам нужно написать отчет:
Доходы
Доходы от торговли
статья доходов по торговле
Доходы от услуг
статья доходов по услугам
Расходы
Регулярные расходы:
статья расходов
Разовые расходы:
статья расходов
Прибыль = Доходы - Расходы
Процент расходов от прибыли = Расход % Прибыль
Причем все параметры нужно выводить за некоторый период с разбивкой по подпериодам (например помесячно).
Описание колонок
Колонка:
* Идентификатор:Корень
* Категории:
* Родитель:
* Формула:
* Итератор:
* Видимость:ложь
Колонка:
* Идентификатор:ПодПериод
* Категории:
* Родитель:Корень
* Формула:
* Итератор: функция, которая перебирает все подпериоды(месяцы) периода.
Колонка:
* Идентификатор:Всего
* Категории:
* Родитель: Корень
* Формула: Р=фоСумма("ПодПериод")
* Итератор:
Описание строк
Строка:
* Идентификатор:Корень
* Категории:
* Родитель:
* Формула:
* Итератор:
* Видимость:ложь
Строка:
* Идентификатор:Доходы
* Категории:
* Родитель: Корень
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:ДоходыОтТорговли
* Категории:
* Родитель: Доходы
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:СтатьяДоходовПоТорговле
* Категории:
* Родитель: ДоходыОтТорговли
* Формула: Р=фоДанные()
* Итератор: перебор доходов по торговле
Строка:
* Идентификатор:ДоходыОтУслуг
* Категории:
* Родитель: Доходы
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:СтатьяДоходовОтУслуг
* Категории:
* Родитель:
* Формула: Р=фоДанные()
* Итератор: перебор доходов по услугам
Строка:
* Идентификатор:Расходы
* Категории:
* Родитель: Корень
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:РегулярныеРасходы
* Категории:
* Родитель: Расходы
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:СтатьяРегулярныхРасходов
* Категории:
* Родитель:Р=фоДанные()
* Формула:
* Итератор: перебор всех статей затрат по регулярным расходам
Строка:
* Идентификатор:РазовыеРасходы
* Категории:
* Родитель: Расходы
* Формула: Р=фоСумма("!.")
* Итератор:
Строка:
* Идентификатор:СтатьяРазовыхРасходов
* Категории:
* Родитель: РазовыеРасходы
* Формула: Р=фоДанные()
* Итератор: перебор всех статей затрат по разовым расходам
Строка:
* Идентификатор: Прибыль
* Категории:
* Родитель: Корень
* Формула: Р=фоЗнач("Доходы")-фоЗнач("Расходы")
* Итератор:
Строка: (процент расходов от прибыли)
* Идентификатор: можно не указывать, ссылок на это поле нет
* Категории:
* Родитель: Корень
* Формула: Р=фоПроцент("Расход", "Прибыль")
* Итератор:
Резюме
Вот таким изящным способом можно реализовать отчеты, от которых раньше можно было поседеть.
Особенно красиво смотрится схема отчета в табличном виде (MXL). Гораздо компактнее, чем я привел здесь. К сожалению, таблицы не очень удобно отображать в КЗ.
Реализация
Реализовываю сейчас это у себя на работе, как сделаю полный движок, выложу здесь. Сроки мне поставили сжатые, ждите в ближайшую неделю.
Последние новости
* Код написан на 80%
* Используется не несколько прогонов, а рекурсивное вычисление функций, т.е. скорость увеличена.
* Для поиска узлов по адресу используется метод: v8: Быстрый поиск - оптимальная структура индекса?
* К идентификаторам строк/колонок, полученных итератором дописывается через "___" номер строки в итераторе, начиная с 1.
* В адрес добавлен разделитель "@", т.е. то, что слева от этого знака адресует строки, то что справа - колонки. Нужно, если сумма относится не к текущей строке/колонке.
* Самый некрасивый момент - нужно как-то определить, что для некоторых строк и колонок используется не основная формула ячейки, а другая. Пока не решено изящно.
* Во все функции добавлен параметр П (структура, описывающая контекст применения функции), например фоСумма(П, "!.") - в 1С только таким образом (без глобальных переменных) можно передать параметры в функцию. А глобальные переменые нельзя использовать в общих модулях, поэтому параметры передаются в функцию.
* Используется кэширование диапазонов по их адресам для ускорения вычисления. |