Для автоматизировано обработки массива данных внутри программного продукта ТИР используются два основных механизма по работе с числовыми значениями ячеек табличного раздела документа. Первый и них - это механизм формул и скриптов, который является надстройкой над встроенным в ТИР ЯваСкрипт языком. Второй механизм - это проверка на корректность цисловых показателей табличного разела в соответствии со иерархической структурой боковика и возможностью автоматического суммирования.
Формулы, преимущественно, используются для математических операция в колонках таблицы, в виду того что жизненный цикл документа не предусматривает модификации шапки, количество колонок остается фиксированным, формулы функционируют корректно (см. Рисунок 9.1, “Формулы” ).
Механизм суммирования работает исключительно для строк. Единственная его функция - это обеспечение корректных значений в суммирующих категориях и подкатегориях записей в соответствии с иерархической структурой боковика. Таким образом порядковый номер строки в документе не имеет значения а суммирование происходит на основании заданных элементов боковика этой строки. Документ, в течение жизненного цикла подразумевает изменения количества строк, а так же допускает расширение словарей боковика, в связи с чем, механизм суммирования показывает себя гораздо более подходящим для получения сводных данных в строках категорий записей, чем использование адресных формул.(см. Рисунок 9.2, “Иерархия” ).
Далее будет рассмотрен механизм формул начиная с общих принципов и заканчивая практикой применения в реальных документах.
Первое что надо понимать, что формула - это некое выражение, которое может быть записано руками в следующие области табличного раздела: ячейка, столбец, строка (как формула в строку записывается?). Чтобы записать формулу необходимо установить курсор на элемент табличного раздела: ячейка(см. Рисунок 9.3, “Формула ячейки”), колонка шапки(см. Рисунок 9.4, “Формула шапки”), строка боковика(см. Рисунок 9.5, “Формула боковика”) и начать вводить текст формулы в специально предназначенное поле визуального интерфейса (см. Рисунок 9.6, “Строка ввода формулы"). После завершения набора формулы следует нажать клавишу Enter на клавиатуре для подтверждения записи формулы. Сразу после создания формулы произойдет её немедленное выполнение (вычисление результата), результат выполнения формулы незамедлительно отобразится в соответствующей формуле ячейке. При перемещении курсора по табличному разделу, если активный элемент содержит формулу, она будет отображена в том поле ввода формул. Для удаления формулы элемента таблицы, следует установить на него курсор, убедиться что формула отобразилась в поле ввода формул, выбрать это поле мышкой и удалить всё содержимое используя клавиатуру. После этого следует подтвердить удаление нажатием клавиши Enter на клавиатуре. Стоит помнить, что после удаления формулы, если ячейка содержала какие либо данные, являющиеся результатом формулы, то эти данные так и останутся в ячейке после удаления формулы.
Следующее что надо знать, это что ячейки, к которым применены формулы становятся закрыты для редактирования вручную и выделяются соответствующем цветом (см. Рисунок 9.7, “Закрытая ячейка”). После удаления формулы, цветное выделение снимается, ячейка вновь станет доступна к редактированию.
Область действия формулы.
Как было сказан ранее, формулы могут быть записаны как в одиночную ячейку табличного раздела, так и в шапку с боковиком.
В случае записи формулы в ячейку, результат вычисления формулы будет отображаться в одной лишь этой ячейке. Однако табличные документы ТИР могут содержать множество колонок и строк, в том числе такие, для которых требуются одинаковые калькуляции для каждой ячейки в столбце или для каждой ячейки в строке (например элементарное сложение колонок). Для того чтобы формулы работала для всей колонки, следует установить курсор в ячейке шапки, находящейся непосредственно над соответствующей колонкой. В случае если шапка имеет несколько уровней и объединенные ячейки, формулы устанавливаются только в ячейки нижнего уровня, которые не участвуют в объединении (см. Рисунок 9.8, “Шапка с формулой”). После установки такой формулы, вся колонка подсветится соответствующим цветом и для каждой из ячеек этой колонки запустится выполнение формулы и отобразится результат вычисления. Аналогично для применения формулы ко всем ячейкам строки необходимо выбрать соответствующую ячейку боковика и ввести формулу.
Приоритеты формул.
Нередко может возникнуть ситуация, при которой, пользователь установил формулу на какую либо колонку таблицы (используя для этого соответствующую ячейку шапки), а так же на строку этой же таблицы (используя ячейку боковика) (см. Рисунок 9.9, “Формула с шапке и боковике”). Таким образом, та ячейка таблицы, которая находится на пересечении колонки и строки не может отобразить одновременно результат двух разных формул. Исходя из встроенной механики, формула строки имеет приоритет над формулой колонки. Еще один случай, когда в одну из ячеек, которые уже отображают вычисляемые результат формулы колонки или формулы строки, пользователь запишет еще одну формулу (непосредственно в саму ячейку) (см. Рисунок 9.10, “Формула в ячейке”), то эта ячейка отобразит результат введенной формулы. Таким образом формул единичной ячейки имеет приоритет над формулами строки и колонки. Напомним, если пользователь установил формулу для колонки или строки, то все соответствующие ячейки должны выводить только результат выполнения этой формулы и закрыты для ручного ввода значений, тем не менее в любую из этих ячеек можно записать свою индивидуальную формулу и тогда формула колонки или формул строки для этой ячейки вычисляться не будет.
Синтаксис формул.
По сути формула является текстовым полем которое может быть пустым, может состоять из всего лишь одного числа, чисел и математических операций либо сложным выражением с функциями и ссылками на другие ячейки документа. В любом случае в ячейке соответствующей формуле будет отображаться результат её вычисления. Если в тексте формулы допущены ошибки и результат не может быть вычислен, то пользователь не сможет сохранить эту формулу.
Рассмотрим примеры составления формул. Самое простое что можно ввести в поле редактирования формулы - это обычное число, например "1", "20","92374","123.23". Следует обратить внимание, что дробные числа вводятся с разделителем "." точка и обязательно иметь и целую и дробную часть, например такие записи "234.", ".242", "423.423.25" будут считаться ошибкой. После ввода формулы, все ячейки, которые соответствуют этой формуле отобразят введенное число. Так же поддерживаются математические операции с соответствующим порядком действий:
"2+2*2" результат: "6";
"(2+2)*2" результат: "8".
Использование функций в формулах.
Функция - это фрагмент программного кода, к которому можно обратиться непосредственно из формулы.
Функция может принимать параметры и должна возвращать числовое значение.
Функция обозначается названием, состоящим из латинских букв верхнего или нижнего регистров и скобками, в которых могут передаются параметры.
Например:
модуль числа, "ABS(-5)" результат: "5";
округление, "round(1.3)" результат: "1";
минимальное из двух чисел "min(4,9)" результат: "4";
строка ячейки, если формула относится к первой строке "row()" результат: "0".
Так же с результатами формул можно производить математические операции и передавать их в другие функции:
"round(min(10,90)*100/90)" результат "11".
Не допускается использовать функции без скобок, передавать количество параметров отличное от предусмотренного, оставлять открытые скобки незакрытыми.
Следующие формулы будут ошибкой "abs 2", "min(3)", "round(4.2".
Подробнее о списке и параметрах функций описывается в конструкторе формул.
Использование данных из других ячеек.
Самым мощным функционалом механизма работы формул является возможность использовать в формулах значения других ячеек. Для получения значения ячейки спользуется функция "C(строка, колонка)". Например если в первой колонке первой строке присутствует значение "5", то формула "C(0, 0)" + 6 отобразит результат "11", где бы такая формула ни была написана.
(необходимо учитывать что при записи С(…, …) – номера колонок и строк начинаются с 0, т. е. 0, 1, 2, 3 и т.д., что не соотносится, например, с отображаемыми номерами колонок в документе).Это с синтаксисом языка ЯваСкрипт, в ктором нумерация списков начинается с "0". Поэтому функция получения значения ячейки записанная в синтаксисе ЯваСкрипт оперирует индексами ячеек начиная с нуля ("0").
Для удобства записи ссылок на ячейки в ТИР используются упрощенный вид записи ссылок на ячейки. Так функцию "C(0, 0)" можно заменить так "C1K1" (Строка 1 Колонка 1). По сути такая запись не является функцией, а только сокращенной её записью, потому скобочек в этом случае не нужно.
(необходимо учитывать что при записи С…К… – номера колонок и строк начинаются с 1, т. е. 1, 2, 3 и т.д., в отличие от записи С(…, …)).Упрощенный вид записи отиентируется на привычный пользователю синтаксис, схожий с современными офисными пакетами и индексация расчитана на соответствие визуальной нумерации таблиц.
Таким образом сложить две ячейки можно как используя функции:
"C(0, 0)+C(0, 1)";
так и с помощью сокращенной записи:
"C1K1 + C1K2" - результат будет одинаковый.
Так же если в ячейке записана формула - то эту ячейку можно использовать в формуле в другой ячейке:
формула второй ячейки "C0K0+1" результат: значение первой ячейки увеличенное на 1;
формула третьей ячейки "C0K1+2" результатом будет значение второй ячейки увеличенное на 2 или первой ячейки на 3.
Всегда следует помнить, что не допускается появление цикличных ссылок между формулами, например "C0K1+1" в ячейке первой колонки и "C0K0-1" в ячейке второй колонки.
Суммируя вышеописанные тезисы можно понять, как работают действия с колонками табличного раздела, т.е. наиболее используемые на практике формулы.
Принимая то что жизненный цикл документа подразумевает неизменяемую шапку, а значит постоянное число колонок, при этом строки документа могут как добавляться, так и удаляться, записывать формулы в каждую строку не имеет смысла. Вместо этого следует использовать формулы колонок, например у нас есть шапка, имеющая следующие поля: "показатель первого полугодия", "показатель второго полугодия", "суммирующий показатель", "годовая норма", "процент от нормы". Для заполнения всех этих показателей достаточно заполнить первую, вторую и четвёртую колонку, остальные будут посчитаны автоматически.
Чтобы посчитать сумму первых двух показателей в третьей колонки следует в ячейку шапки третьей колонки записать формулу:
"C(row(), 0)+C(row(), 1)" или же в упрощенной записи "K0+K1" (более явно озаглавить синтаксис записи К…).
Если разобрать эти формулы, то мы видим, что для каждой строки row() будет результат равной номеру строки. Для первой результат "0", для второй "1" и т.д. до последней. Таким образом сумма для первой строки будет посчитана в виде:
"C(0, 0)+C(0, 1)",
а сумма для второй строке будет:
"C(1, 0)+C(1, 1)".
Что же касается сокращенной записи, то аналогично:
"C(row(), 0)" соответствует записи "K1" т.е. первая колонка той же строки что и ячейка для которой рассчитывается формула.
Теперь считаем условно процент от нормы, округлённый до целого числа. Для этого в соответствующей ячейке шапки запишем формулу:
"round(C(row(),2)*100/C(row(),3))" или сокращенный вид "round(K2*100/K3)".
Такой подход разработан быть максимально понятным и даже чем то похожим на синтаксис популярных офисных пакетов, но в отличие от них имеется возможность задавать формулы сразу для всей колонки не обращая внимание на изменение количества и порядка строк. Естественно если пользователь добавит или удалит колонку таблицы, то все формулы, которые записаны правее этой колонки потеряют свою актуальность.
Условный оператора "if" в формулах
Иногда нам нужно выполнить различные действия в зависимости от условий. Для этого мы можем использовать оператор "if"
Инструкция if([условие]){[блок 1]}else{[блок 2]} вычисляет условие в скобках и, если результат true, то выполняет блок кода 1, если результат false, то выполняет блок кода 2 (см. Рисунок 9.11, “Условие в формуле”)
В примере выше, условие – это простая проверка на равенство "K1>10", т.е. если числовое значение в колоке 1 больше 10, то выводится строка 'достаточно' в противном случае выводится 'мало', но оно может быть и гораздо более сложным.
Блоки кода, выполняемые при вычисления соответствующего условия тоже могут содержать более сложные выражения, например те же математические формулы (см. Рисунок 9.12, “Условие с формулами”)
Следует всегда следить за тем, чтобы результат вычисления условия в скобках всегда соответствовал бинарной логической переменной с результатом 'true' либо 'false', например используя значения ячеек, числа и операторы сравнения, например:
"K1 > 10", "K2 < K3", "K1 == K2", "K1 != K4"
Блоки кода, записываемые в фигурных скобках, разделенные оператором 'else' могут содержать строковое значение у кавычках, числовое значение, числами из ячеек табличного раздела, а так же либо выражения их содержащие. Например:
if(K1+K2 == 10){'Десять'}else{round(K1 * K2 / 100)}