4.1. Формулы

Основные операции

В системе JetCalc формулы в подавляющем большинстве случаев состоят из следующих элементов:

  • ссылки на ячейки вида $код_строки@код_колонки.Yномер_года.Pкод_периода#код_объекта_учета[код_валюты]?

  • численные константы вида 10, 1.5, 1000 и т.п.;

  • арифметические операторы +(сложение), - (вычитание), * (умножение) и / (деление);

  • круглые скобки, позволяющие изменять порядок вычисления арифметических операторов.

В качестве примера можно привести формулу расчета прироста значения показателя отчетного периода 11 к базовому периоду 301, выраженного в процентах, для строки a110, колонки VAL и валюты RUB:

( $a110@VAL.Y2017.P11#150[RUB]? - $a110@VAL.Y2017.P301#150[RUB]? ) / $a110@VAL.Y2017.P301#150[RUB]? * 100

Ячейки

В системе JetCalc каждая ячейка отличается от другой уникальным набором атрибутов, сочетание которых образует вышеказанный уникальный код (идентификатор) ячеек, используемых расчетной системой JetCalc:

  • код строки;

  • код колонки;

  • номер года;

  • код периода;

  • код объекта учета;

  • код валюты.

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

Ячейки делятся на два вида:

  • первичные - ячейки, хранящие натуральные числовые значения в базе данных;

  • формульные - ячейки, значения которых вычисляются динамически по формуле без сохранения в базе данных.

Расчет значений формульных ячеек выполняется при наступлении следующих событий:

  • при открытии документа;

  • при изменении контекста открытого документа (объект учета, год, период, валюта);

  • при нажатии клавиши F9 (расчет по кешу формул) или Ctrl+F9 (расчет с предварительным сбросом кеша формул).

Если между указанными событиями изменились значения первичных ячеек, то последнее вычисленное значение будет отличаться от предыдущего, т.е. JetCalc является расчетной системой реального времени.

Контекст ячейки

Атрибуты ячейки делятся на две группы:

  • контекст ячейки (год, период, объект учета и валюта);

  • измерения ячейки (строка и колонка).

Первоначально контекст ячейки устанавливается на уровне документа и используется как контекст по умолчанию. Этот набор значений контекста ячеек называется контекстом документа.

Далее на уровне отдельных колонок контекст документа может быть переопределен полностью либо частично. При этом к контексту документа добавляется код колонки. В этом случае применяется понятие контекст колонки.

Чаще всего в контексте колонки переопределяются значения номера года и кода периода. Очень редко переопределяется код валюты. И только в исключительных случаях - код объекта учета.

На уровне строк контекст документа не может быть переопределен. На уровне строки применяется контекст документа с добавлением кода строки, что формирует понятие контекст строки.

Например, в контексте документа с номером года 2017, кодом периода 11, кодом объекта учета 150 и кодом валюты RUB вышеприведенный пример формулы расчета прироста значения показателя в процентах будет выглядеть следующим образом:

( $a110@VAL? - $a110@VAL.P301? ) / $a110@VAL.P301? * 100

В контексте колонки с кодом VAL формула сокращается еще сильнее:

( $a110? - $a110.P301? ) / $a110.P301? * 100

В контексте строки с кодом a110 формула будет выглядеть следующим образом:

( @VAL? - @VAL.P301? ) /@VAL.P301? * 100

Формула ячейки

Формула ячейка может содержать как ссылки на первичные ячейки, так и на другие формульные ячейки.

В отличие от электронных таблиц, формулы в JetCalc прописываются не для каждой ячейки, а формируются в два этапа:

  1. На этапе настройки документа пользователем системы заполняются формулы строк и(или) колонок.

  2. На этапе открытия документа расчетная система JetCalc производит приведение формул строк или колонок к контексту ячейки путем определения приоритета формул, добавления недостающих атрибутов для всех ссылок на другие ячейки и заключительного преобразование расчетных периодов к соответствующим первичным периодам.

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

Операции по приведению формул строк и колонок к контексту являются ресурсоемкими, поэтому их результаты сохраняются в оперативной памяти для последующего использования. Повторение операции приведения строк и колонок к контексту ячеек может быть выполнено принудительно по нажатию сочетания клавиш Ctrl+F9 либо автоматически при изменениях в настройках строк и колонок, влияющих на расчетную систему.

Определение приоритета формул

На практике очень часто встречаются ситуации, когда формулы определены как для строки, так и для колонки ячейки. В этом случае расчетная система JetCalc для формирования формул ячеек использует механизм приоритета формул, заключающийся в следующем:

  1. При формировании формулы ячейки приоритет по умолчанию имеет формула колонки.

  2. Чтобы для ячейки применить формулу строки, необходимо у колонки включить параметр Приоритет строки.

  3. Если для ячейки с колонкой, у которой установлен параметр Приоритет строки, необходимо использовать формулу колонки, то необходимо у строки включить параметр Приоритет колонки.

Продемонстрировать данный механизм можно на следующих примерах.

Пример 1. Расчет суммы (колонка SUMMA) продаж по двум видам продукции на основе показателей количества (колонка KOL) и цен (колонка CENA) продукции.

а) расчет значений с приоритетом формул по умолчанию

Коды строк и колонок

KOL

CENA

SUMMA = @KOL? * @CENA?

a110

10

20

10 * 20 = 200

a120

15

30

15 * 30 = 450

a200 = $a110? + $a120?

10 + 15 = 25

20 + 30 = 50

25 * 50 = 1250

Как видно из результатов расчета, для ячейки $a200@SUMMA? рассчитано неправильное значение 1250.

б) расчет значений с установленным для колонки SUMMA параметром Приоритет строки

Коды строк и колонок

KOL

CENA

SUMMA = @KOL? * @CENA? (Приоритет строки)

a110

10

20

10 * 20 = 200

a120

15

30

15 * 30 = 450

a200 = $a110? + $a120?

10 + 15 = 25

20 + 30 = 50

200 + 450 = 650

После изменения приоритета формирования формул для ячейки $a200@SUMMA? получено правильное значение 650.

Примечание: механизм расчета суммовых значений для удельных первичных показателей, таких как в колонке CENA, рассмотрен в главе 6.4. Агрегирование.

Пример 2. К модифицированной таблице предыдущего примера добавляется строка a125, в которой к количеству и цене добавляется по 10 условных единиц, а сумма должна быть получена путем умножения количества на цену

а) расчет значений с установленным для колонки SUMMA параметром Приоритет строки

Коды строк и колонок

KOL

CENA

SUMMA = @KOL? * @CENA? (Приоритет строки)

a120

15

30

15 * 30 = 450

a125 = @a120? + 10

15 + 10 = 25

30 + 10 = 40

450 + 10 = 460

Как видно из результатов расчетов, для ячейки $a125@SUMMA? рассчитано неправильное значение 460.

б) расчет значений с установленным для колонки SUMMA параметром Приоритет строки, который отменен на уровне строки a125 путем установления для этой строки параметра Приоритет колонки

Коды строк и колонок

KOL

CENA

SUMMA = @KOL? * @CENA? (Приоритет строки)

a120

15

30

15 * 30 = 450

a125 = @a120? + 10 (Приоритет колонки)

15 + 10 = 25

30 + 10 = 40

25 * 40 = 1000

После изменения приоритета формирования формул для ячейки $a125@SUMMA? получено правильное значение 1000.

Приведение формул к контексту ячеек

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

1. Если формула определена в строке, например с кодом a990, то изначально она будет выглядеть следующим образом:

( $a110? - $a110.P301? ) / $a110.P301? * 100

На этапе открытия документа формула строки будет приведена к контексту ячеек:

а) для колонки VAL и строки a990 будет сформирована формула ячейки $a990@VAL?

( $a110@VAL? - $a110@VAL.P301? ) / $a110@VAL.P301? * 100

б) для колонки ON и строки a990 будет сформирована формула ячейки $a990@ON?

( $a110@ON? - $a110@ON.P301? ) / $a110@ON.P301? * 100

2. Если формула определена в колонке, например с кодом PRIROST, то изначально она будет выглядеть следующим образом:

( @VAL? - @VAL.P301? ) /@VAL.P301? * 100

На этапе открытия документа формула колонки будет приведена к контексту ячеек:

а) для строки a110 и колонки PRIROST будет сформирована формула ячейки $a110@PRIROST?

( $a110@VAL? - $a110@VAL.P301? ) / $a110@VAL.P301? * 100

б) для строки b220 и колонки PRIROST будет сформирована формула ячейки $b220@PRIROST?

( $b220@VAL? - $b220@VAL.P301? ) / $b220@VAL.P301? * 100

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

Преобразование формульных периодов

После приведения формул строки и колонок к контексту ячеек с учетом приоритета формул выполняется преобразование формульных периодов до первичных (см. раздел 3.2).

Например, если у расчетного периода -211 установлено, что для отчетного периода 22 выполняется суммирование периодов 11 и 12, то формула $a120@KOL.P-211? * 10 для контекстного периода 22 будет преобразована к следующему виду:

$a120@KOL.P-211? * 10 = ( $a120@KOL.P11? + $a120@KOL.P11? ) * 10

Виртуальные колонки

В целях сокращения формул и уменьшения повторяемости кода в JetCalc реализован механизм виртуальных колонок, который заключается в создании формульной колонки с признаком Виртуальная колонка, обладающая следующими ограничениями:

  • может применяться только в формулах строк;

  • не может использоваться в колсетах в качестве формульной колонки.

Виртуальная колонка по своей сути является шаблоном, упрощающим написание длинных строковых формул, особенно в случаях необходимости написания однотипных формул, отличающихся друг от друга только кодом строк. Проще всего работу виртуальной колонки продемонстрировать на примере.

Предположим, что для нескольких строк необходимо написать следующие формулы, отличающиеся друг от друга только кодами используемых для них строк (в данном примере кодами продукции из справочника продукции):

$a100120forcol ( SLREV: $[102033]@VAL<<(B:9001)? ; SLSS : $[102033]@VAL<<(B:9002)? )

$a100130? = forcol ( SLREV: $[102034]@VAL<<(B:9001)? ; SLSS : $[102034]@VAL<<(B:9002)? )

...

Вышеприведенные формулы отличаются друг от друга только кодами продукции [102033] и [102034]. В этом случае можно вынести общую часть формул в виртуальную колонку с названием, например, V_DR:

V_DR = forcol ( SLREV: @VAL<<(B:9001)? ; SLSS : @VAL<<(B:9002)? )

Тогда первоначальные формулы могут быть сведены к следующему виду:

$[102033]@V_DR?

$[102034]@V_DR?

...

Previous4. Расчетная системаNext4.2. Функции

Last updated

Was this helpful?