Настройка формул в отчетности
Общие требования и принципы работы формул
-
По умолчанию в формулах используется русский язык.
При написании формулы допускается написание английских названий функций Excel.
- Разделитель дробной части - «,» (запятая). Использование «.» (точки) в качестве разделителя дробной части не допускается, включая и при использовании английских названий функций Excel.
- Редактор формул позволяет вводить текстовые и числовые константы вместо формулы. Текстовая константа должна быть заключена в «"» (кавычки).
- Начальный знак «=» (равно) в редакторе выражений необязателен. При создании формулы он автоматически добавляется, а при вводе текстовой или числовой константы - не добавляется.
Вставка формул на получение значений ячеек форм отчетности
- Click или Enter с другой формы, независимо от листа, вставляет формулу ФОРМАЗНАЧ.
- Click или Enter с исходной формы с того же листа вставляет ссылку на диапазон вида RxCx, а с другого листа - вида ЛИСТ!RxCx.
Дополнительные горячие клавиши для вставки формул
| Shift+Click или Shift+Enter | Вставка формулы ФОРМАЗНАЧПРЕД независимо от формы и листа |
| Ctrl+Click или Ctrl+Enter | Вставка формулы ФОРМАЗНАЧПРОШ независимо от формы и листа |
| Alt+Click или Alt+Enter | Вставка формулы ФОРМАЗНАЧ независимо от формы и листа |
Создание формулы
- Откройте шаблон формы отчетности, в которой необходимо создать формулу.
-
Установите курсор мыши в ячейку формы отчетности, в которую необходимо ввести формулу, и на клавиатуре нажмите на клавишу = (равно). Откроется форма Редактор формул. В качестве заголовка формы приводится:
- Наименование формы
- Наименование вкладки (таблицы)
- Адрес настраиваемой ячейки
×Данная форма также может быть открыта при нажатии в строке состояния формулы на пиктограмму Fх.
× -
В поле для ввода формул открывшейся формы установите курсор мыши. Затем установите курсор мыши в ячейку формы отчетности, значение которой используется в формуле. Двойным нажатием левой кнопки мыши нажмите по данной ячейке. Ссылка на соответствующую ячейку формы отчетности появится в форме Редактор формул.
При помощи клавиш на клавиатуре введите соответствующее арифметическое действие (например, "+", "-", "*", "/", "()").
Для использования функции Microsoft Excel нажмите на кнопку Функция и выберите необходимую функцию.
По завершении редактирования формулы нажмите на кнопку ОК. Форма Редактор формул будет закрыта.
×
Синтаксис формул на получение значений ячеек форм отчетности
Все формулы возвращают значения в виде одномерного массива данных. Таким образом, формулы могут использоваться в других формулах, принимающих в качестве аргументов массивы данных, например «СУММ».
В формулах могут использоваться следующие параметры:
| Тип данных | Комментарий |
| Форма |
Строка, заключенная в кавычки, наименование формы отчетности, из которой получается значение. Обязательный параметр. Параметр может быть пустым. В этом случае кавычки не указываются, а разделитель «;» сохраняется. Если параметр пустой, то он принимает значение наименования исходной формы, формулы которой рассчитываются. При использовании функции в качестве объекта шаблона документа параметр обязателен. |
| Год |
Число, финансовый год формы отчетности, из которой получается значение. Обязательный параметр. Параметр может быть пустым, в этом случае разделитель «;» сохраняется. Если параметр пустой, то он принимает значение финансового года исходной формы, формулы которой рассчитываются. При использовании функции в качестве объекта шаблона документа параметр принимает значение проверяемого года проекта. |
| Период |
Число (3, 6, 9, 12 или год в формате ГГГГ), период (количество месяцев или год) формы отчетности, из которой получается значение. Обязательный параметр. Параметр может быть пустым, в этом случае разделитель «;» сохраняется. Если параметр пустой, то он принимает значение периода отчетности исходной формы, формулы которой рассчитываются. При использовании функции в качестве объекта шаблона документа параметр принимает значение квартала (умноженного на 3) проверяемого года проекта. Проверяемый период по проекту задается на вкладке Планирование и ключевые даты карточки проекта по аудиту. Период / Дата определяется по значению поля Проверяемый период / Окончание. 
×
|
| Лист |
Строка, заключенная в кавычки, наименование листа (таблицы) формы отчетности, из которой получается значение. Обязательный параметр. Параметр может быть пустым, в этом случае кавычки не указываются, а разделитель «;» сохраняется. Если параметр пустой, то он принимает значение наименования листа отчетности исходной формы, формулы которой рассчитываются. При использовании функции в качестве объекта шаблона документа параметр принимает значение наименования листа шаблона документа. |
| Диапазон |
Адрес ячейки формы отчетности, из которой получается значение, допускается указание до 30 одиночных адресов ячеек или диапазонов адресов ячеек. Необязательный параметр. Если ни один адрес или диапазон не указан, то параметр принимает значение адреса ячейки отчетности исходной формы, формула которой рассчитывается. При использовании функции в качестве объекта шаблона документа параметр принимает значение адреса ячейки шаблона документа, формула которой рассчитывается. Обратите внимание
Формат адреса ячейки R1C1 / A1 должен соответствовать формату ссылок в книге. В формах отчетности используется исключительно формат R1C1. Рекомендуем настраивать формулы в формате R1C1. Формат ссылок формул в книге изменяется путем нажатия на кнопку R1C1 / A1 в панели табличного файла(группа полей Файл). Адрес ячейки прописывается в формуле |
Функции для получения значений ячеек форм отчетности
| Функция | Комментарий |
| ФОРМАЗНАЧ |
Получает значения из указанных ячеек указанного листа формы отчетности с указанным именем за указанный отчетный год и период. Применение: ФОРМАЗНАЧ(форма; год; период; лист; диапазон) Расчет: По полученным наименованию формы, году и периоду в базе данных ищется соответствующая форма отчетности, если найдено более одной формы, то берется случайная форма. В найденной форме ищется лист с полученным именем, из которого получаются значения по указанным диапазонам адресов. |
| ФОРМАЗНАЧПРЕД |
Получает значения из указанных ячеек указанного листа формы отчетности с указанным именем за предыдущий отчетный год и период. Применение: ФОРМАЗНАЧПРЕД (форма; лист; диапазон) Расчет: Для расчета дополнительно запрашиваются значения года и периода – при использовании формулы в шаблоне документа используются проверяемый год и квартал из проекта, при использовании в форме отчетности используются год и период исходной формы отчетности. Если у формы отчетности в качестве периода указан квартал, то полученные год и период вместе уменьшаются на 3 месяца (один квартал), иначе на 1 год каждый. По полученным наименованию формы, году и периоду в базе данных ищется соответствующая форма отчетности, если найдено более одной формы, то берется случайная форма. В найденной форме ищется лист с полученным именем, из которого получаются значения по указанным диапазонам адресов. Например, если по отчетности установлен 2018 год, то по формуле ФОРМАЗНАЧПРЕД будут получены данные за 2017 г. Если по отчетности будет установлен 4 кв. 2018 года, то по формуле ФОРМАЗНАЧПРЕД будут получены данные за 3 кв. 2018 г. |
| ФОРМАЗНАЧПРОШ |
Получает значения из указанных ячеек указанного листа формы отчетности с указанным именем за прошлый отчетный год и период. Применение: ФОРМАЗНАЧПРОШ (форма; лист; диапазон) Расчет: Для расчета дополнительно запрашиваются значения года и периода – при использовании формулы в шаблоне документа используются проверяемый год и квартал из проекта, при использовании в форме отчетности используются год и период исходной формы отчетности. Если у формы отчетности в качестве периода указан квартал, то полученные год и период вместе уменьшаются на 6 месяцев (два квартала), иначе на 2 года каждый. По полученным наименованию формы, году и периоду в базе данных ищется соответствующая форма отчетности, если найдено более одной формы, то берется случайная форма. В найденной форме ищется лист с полученным именем, из которого получаются значения по указанным диапазонам адресов. Например, если по отчетности установлен 2018 год, то по формуле ФОРМАЗНАЧПРЕД будут получены данные за 2016 г. Если по отчетности будет установлен 4 кв. 2018 года, то по формуле ФОРМАЗНАЧПРОШ будут получены данные за 1 полугодие 2018 г. |
Изменение формулы
Установите курсор мыши в ячейку формы отчетности, которую необходимо изменить, и на клавиатуре нажмите на клавишу = (равно). Откроется форма Редактор формул. Данная форма также может быть открыта при нажатии в строке состояния формулы на кнопку Fх.
Клавишами Delete или Backspace удалите необходимое значение, действие или ссылку на ячейку. Ввод нового значения, действия или ссылки на ячейку производится в общем порядке, используемым при создании формулы.
Поиск и замена работает по формулам. Поэтому могут возникнуть циклические ссылки, когда формула в ячейке использует диапазон, в который она сама же и входит. Циклические ссылки по правилам Excel не допускаются.
Редактор формул работает по принципу текстового редактора – выделенный текст заменяется при вставке формул или диапазонов.
Копирование формулы
Установите курсор мыши в ячейку формы отчетности, которую необходимо скопировать и на клавиатуре нажмите сочетание клавиш Alt+стрелка вниз (вверх, вправо, влево). В зависимости от выбранной кнопки стрелки формула копируется соответственно вниз, вверх, вправо или влево.
При копировании формулы используются следующие типы ссылок: относительная, абсолютная, смешанная.
| Тип ссылки | Пример |
| АБСОЛЮТНАЯ строка и АБСОЛЮТНЫЙ столбец | R2C3 |
| ОТНОСИТЕЛЬНАЯ строка и АБСОЛЮТНЫЙ столбец | R[0]C3 |
| АБСОЛЮТНАЯ строка и ОТНОСИТЕЛЬНЫЙ столбец | R2C[-1] |
| ОТНОСИТЕЛЬНАЯ строка и ОТНОСИТЕЛЬНЫЙ столбец | R[0]C[-1] |
Установление типа ссылки производится с использованием клавиши F4 в форме Редактор формул по аналогии с программой Microsoft Excel.
Замена типа ссылки (абсолютная – относительная) осуществляется для каждой ссылки в отдельности. Изменение типа нескольких выделенных ссылок (как в Excel) не работает.
Удаление формулы
Установите курсор мыши в ячейку формы отчетности, которую необходимо очистить. На клавиатуре нажмите на кнопку Delete. Ячейка будет очищена.
Вставка текстового значения в ячейку
Установите курсор мыши в ячейку формы отчетности, в которую необходимо ввести текстовое значение, и на клавиатуре нажмите на клавишу = (равно). Откроется форма Редактор формул.
Данная форма также может быть открыта при нажатии в строке состояния формулы на пиктограмму Fх.
Введите необходимый текст. Текстовая константа должна быть заключена в «"» (кавычки).
По завершении редактирования ячейки нажмите на кнопку ОК. Форма будет закрыта. Ячейка изменит цвет на зеленый.
Замена данных в тексте и формуле
В форме отчетности нажмите Ctrl+H. В открывшейся форме Заменить введите искомое значение и заменяемое значение. Нажмите на кнопку ОК.