Шаблон Excel

 Пред.  Начало  След.

Правила и особенности создания шаблона отчета.

 

1. Форматирование значений в ячейках

 

1.1. Автоподбор высоты.

  Нельзя включать свойство ячейки "автоподбор высоты строк" для объединений по высоте (объединены две или более строк). Если ячейки объединены в одной строке (несколько колонок), то автоподбор высоты разрешен.

 

1.2. Разный формат символов в текстовых ячейках.

  Используются теги:

<b>..</b> - жирный

<i>..</i> - наклонный
<ins>..</ins> - подчеркнутый

 

<sup>..</sup> - надстрочный

<sub>..</sub> - подстрочный

 

Пример разного форматирования в одной строке, вывод корреспондента с ИНН (выводимые значения выделены синим цветом для наглядности).

В шаблоне формула: >>"<b>ИНН</b> " & "<i>"& {ccorr1} & "</i>" & " " & "<ins>" & СЖПРОБЕЛЫ({corr_n1}) & "</ins>"

Результат в отчете: ИНН 3000000000 Управление государственной службы
 

Пример с двойным форматированием (жирный с наклоном, надстрочный с подчеркиванием), вывод кода БК.

В шаблоне формула: >>"<b>"&"<i>"&{cexp}&"</i>"&"</b>"& " -- " & "<ins>"&"<sub>"&{exp_n}& "</sub>& "</ins>"

Результат в отчете: 410 -- Бюджетные инвестиции

 

 

2. Имя поля в запросе.

  Длина имени поля (в запросах) не более 10 символов.

 

3. Итоговый показатель

3.1. Итоговые суммы.

Формула MS Excel для вычисления итогов по группе/отчету, если нужно обработать несколько параметров :

>>СУММ(<s_kass>) - СУММ(<s_retukass>)

 

Неправильно: >>СУММ(<s_kass> - <s_retukass>)

 

3.2 Итоговые суммы в пределах заданной группы.

Функция СУММГРУПП() считает сумму абсолютных значений суммы, сгруппированной по какому-либо полю, в пределах той группировки отчета где она применена. Первый параметр - поле, по которому идет группировка, второй параметр - сумма, которую необходимо считать. Пример вызова:

>>СУММГРУПП(<cexp>;<sy0>)

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

Например, данные на входе: 121 =100руб, 129=200 руб, 121=-300 руб. Тогда СУММГРУПП(<cexp>;<sy0>) в целом по отчету = |(100-300)|+|200| = 200+200 = 400 руб.

 

3.3 Итоговые строковые значения.

Объединение строковых значений

>>СУММСТР(<имя_поля>;". ") - где второй параметр является разделителем сложенных значений. Пример СУММСТР(<note1>;"# ") - сложение значений примечаний с разделителем # .

 

 

4. Значения зарезервированных ячеек шаблона.

 

Первая строка содержит общую информацию по отчету:

 

Столбец первой строки

Назначение

B

 Число групп в отчете

C

 Дополнительные поля, в том числе и вычисляемые. Примеры:

•добавление укрупненного кода вида расхода хх0 (cexp2) и полной классификации расходов 20 знаков (cbkfull ):
left(nvl(cexp," "),2)+"0" as cexp2, nvl(cdep," ")+nvl(cdiv," ")+nvl(ctgt," ")+nvl(cexp," ") as cbkfull
•добавление поля-признака принимающего значения в зависимости от кода вида расхода:
iif(left(nvl(cexp,""),1)="1","выплаты персоналу",iif(left(nvl(cexp,""),1)="2","закупки товаров","прочее")) as cexp_s

D

 Дополнительные таблицы (расширение «.DBF» обязательно)

E

 Дополнительные условия на таблицы. Примеры:

1) nvl(sy0,0)<>0 and nvl(sy1,0)<>0 or gr<>5

2) cadd_bk = "80185"

где nvl - функция замены пустого значения.

F

 Принудительная сортировка (перечисление полей через запятую «,»). Пример:  numdok,link,caccount,citem   .

G

 Количество строк на листе. Если значение > 100 - то это максимальное число строк на листе, после которых создается новый лист Excel (не страница); если <=100 - то это число висячих строк на последнем листе, создается новый лист Excel (не страница).

H

 Не применяется (не учитывается) с версии 21.1.

I

 Не применяется (не учитывается) с версии 21.1.

K

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

L

  Формула для указания имени окна (вкладки) с открывшимся отчетом:

<Caption>...</Caption> для внутреннего редактора;

<FileName>...</FileName>  для внешнего редактора.

  В качестве значения может указываться зарезервированное слово shablon - имя шаблона по которому форимруется отчет (см меню "Менеджер шаллонов").

  Пример: <Caption>"Реестр финансирования за период "&dDate1&"-"&dDate2</Caption><FileName>shablon</FileName> , dDate1 и dDate2 - переменные отчета, опеределяющие его период, Shablon - перменная, содержит имя шаблона. В результате во внутреннем редакторе отчет откроется в окне с заголовком "Реестр финансирования за период 01.05.2020-31.05.2020", а в MS Excel окно будет называться "rep_reest_fin_korr".

N

  ИСТИНА - дубли итоговых строк будут удаляться. По умолчанию ЛОЖЬ.

Пример: в отчете группировка по виду расхода Х00 и ХХ0. Для документа с видом расхода 100, при значении ЛОЖЬ в отчете будет две итоговые строки с кодом 100: Х00 (в отчете выглядит как 100) и ХХ0 (в отчете выглядит как 100). При значении ИСТИНА второй итог (по ХХ0) будет удален.

 

 

В формулах граф С и E применяются функции в нотации Visual FoxPro:

•nvl(x,z) - преобразование пустых значений: если значение переменной Х не задано, то присвоить значение Z. Применяется для предотвращения вывода в отчете значений NULL.
•iif(x,z1,z2) - присвоение значений по условию: если условие X истино, то выводится значение Z1, иначе Z2.

 

Разделительная линия шапки группы содержит свойства данной группы:

 

Столбец шапки группы

Назначение

B

поле группы, либо «Отчет», если это титульная группа по всему отчету

C

«ИСТИНА», если каждый уровень группировки необходимо начинать на новом листе EXCEL

D

«ИСТИНА», если каждый уровень группировки необходимо начинать с новой страницы

E

«ИСТИНА», если по каждому уровню группировки необходимо показывать символы группировки

F

«ИСТИНА», если шапку группы необходимо печатать на каждом листе (сквозные строки)

G

«ИСТИНА», если шапку группы необходимо зафиксировать на листе (закрепление областей)

H

Условие печати соответствующей полосы.

Например:

>>СУММ(<summa>)<>0 and gr>2  - при ненулевой сумме во второй группе данных.

>>NVL({cadd_bk};"")<>"" - при непустом коде дополнительной классификации (т.е. при наличии дополнительной классификации).

Применимо и для концовки группы.

 

5. Пользовательские шаблоны на основе системных.

При копировании системного шаблона для создания пользовательского, в копии нужно очистить ячейки H1-M1 (если они не пустые).

 

6. Функции, доступные для применения в пользовательских шаблонах

 

Номер по порядку.

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

>>НОМЕРПП2([парам](1);признак) - вычисление номера по порядку по уникальному значению заданного параметра, в данном примере Link (документ). Подходит для отчетов, в которых по порядку нумеруется не каждая строка, а раздел отчета (например каждый документ). Значение первого параметра функции (в примере Link) индивидуален для каждого отчета.

•парам - поле выборки, по которому надо вычислять порядковый номер. (1) - первое значение является начальным.
•признак - игнорировать (ИСТИНА) или нет (ЛОЖЬ) пустые значения парам. При значении ИСТИНА подсчет гарантированно начнется с 1.

Пример: >>НОМЕРПП2([Link](1);ИСТИНА)

 

>>НОМЕРПП([парам](2);конец;признак) - вычисление номера по порядку по уникальному значению заданного параметра, в данном примере Link (документ). Подходит для отчетов, в которых по порядку нумеруется не каждая строка, а раздел отчета (например каждый документ). Значение первого параметра функции (в примере Link) индивидуален для каждого отчета.

•парам - поле выборки, по которому надо вычислять порядковый номер. (1) - первое значение является начальным.
•конец - значение парам, после которого прекратить подсчет порядкового номера. Для подсчета всех значений следует задать пустую строку "" либо несуществующее значение.
•признак - игнорировать (ИСТИНА) или нет (ЛОЖЬ) пустые значения парам. При значении ИСТИНА подсчет гарантированно начнется с 1.

Пример: >>НОМЕРПП([Link](1))

 

=СТРОКА()-<начальный_номер>  - каждая строка отчета нумеруется порядковым номером. Вместо <начальный номер> следует вписать конкретное число - номер строки, после которой начинается вывод строк табличной части отчета.

 

Дата в виде «31» марта 2017г.

Пример Excel формулы для вывода текущей календарной даты:

  >>СИМВОЛ(171) & ЛЕВСИМВ(ДАТАПРОП(СЕГОДНЯ();4);2) & СИМВОЛ(187) & ПСТР(ДАТАПРОП(СЕГОДНЯ();0);3;100) & "г."

где СИМВОЛ(171) - символ открывающей кавычки « , а СИМВОЛ(187) - закрывающей » .

 

 

Дата в виде 31 марта 2017г.

Пример выражения для вывода даты проводки в различных вариантах:

•в виде "31 марта 2017"   >>ДАТАПРОП({date_exec};0)  ,
•в виде "31.03.2017" >>ДАТАПРОП({date_exec};4) ,
•в виде "31.03.17" >>ДАТАПРОП({date_exec};2) .

 

Пример для даты окончания периода отчета:

•в виде "31 марта 2017"   >>ДАТАПРОП(dDate2;0)  

 

Дата: месяц прописью март

Пример для даты окончания периода отчета:

>>МЕСЯЦПРОП(dDate2; ЛОЖЬ)

 

Дата: изменение даты на заданное число дней

Пример для даты окончания периода отчета, увеличение даты на 1 день:

>>"на " & ДАТАПРОП(ДОБАВИТЬДНИ(dDate2;1);0) 

 

 

Число (сумма) прописью

Общий вид функции ЧИСЛОПРОП(Число/переменная; признак "Выводить прописью"; признак "Учитывать падеж"; признак "Выводить 0 копеек") .

Пример применения в шапке или концовке группы:

>>>ЧИСЛОПРОП(СУММ(<summa>); ИСТИНА; ЛОЖЬ; ЛОЖЬ)

в детализации

>>>ЧИСЛОПРОП({summa}; ИСТИНА; ЛОЖЬ; ЛОЖЬ)

 

Место для вставки штампа ЭП

#stamp_paste:П;З

где П (параметр) - номер реквизита ЭЦП, число от 0 до 8; З (значение) - искомое значение заданного параметра. См описание по выводу штампа ЭП в отчетах.

Например #stamp_paste:5;1 - вывести в ячейке ЭП 1 уровня (5 - "Уровень ЭП", 1 - искомое значение 5-го параметра).

 

Структурированный текстовый набор данных с разделителями

Структура данных: набор1#набор2#....   , где набор имеет структуру поле1|поле2|...

Пример: Иванов|бухгалтер|1999#Петров|водитель|1998

 

Функции: ПАРАМЕТР(find_ecp(<структурированный текст>;<номер поля>; <значение>); <номер поля>; "|")   , где find_ecp возвращает искомый набор , содержащий заданное значение заданного поля, а ПАРАМЕТР возвращает значение заданного поля из требуемого набора.

Пример 1: >>ПАРАМЕТР(find_ecp({otv_lic};2;"водитель"); 1; "|")     -->   При значении переменной otv_lic=[Иванов|бухгалтер|1999#Петров|водитель|1998] получим в отчете  значение "Петров".

Пример 2: >>ПАРАМЕТР(find_ecp({dopuk};1;"4.5"); 2; "|")   -->  При значении переменной dopuk=[2.5|Белый белый снег.#4.5|И на марсе будут цвести] получим в отчете  значение "И на марсе будут цвести" .

 

7. Формулы Excel в шаблоне

 

  В шаблоне можно использовать формулы Excel с префиксом ">>=". При этом для обращения к в другим ячейкам шаблона необходимо пользоваться функцией ДВССЫЛ с RC типом адресации.

  Примеры:

Сумма левых двух ячеек в той же строке:

>>=ДВССЫЛ("R[0]C[-1]"; ЛОЖЬ)+ДВССЫЛ("R[0]C[-2]"; ЛОЖЬ)

 

Результат деления значения ячейки в 5 строке того же столбца на 1000:

>>=ДВССЫЛ("R5C[0]"; ЛОЖЬ)/1000

 

Сумма ячеек того же столбца от 5 строки до предыдущей строки

>>=СУММ(ДВССЫЛ("R5C[0]:R[-1]C[0]";ЛОЖЬ))