Опции шаблонов Excel

Эта страница — полный справочник по опциям (ключевым словам), которые администратор базы может использовать в шаблонах первичных документов и отчётов.

Основы работы с шаблонами (регистрация, NoRange/Range-наборы, списки переменных) описаны в разделе «Шаблоны первичных документов».

Группировка со всеми параметрами, оформление строк итогов, сводные таблицы, диаграммы и прочие опции, не вошедшие в эту страницу, — на отдельной странице «Опции шаблонов Excel: группировка, итоги и остальные опции».

Опция — это ключевое слово, написанное текстом в определённой ячейке шаблона. Место ячейки определяет, на что опция действует.

Важно. Все служебные ячейки после построения отчёта очищаются автоматически — в готовом документе текста опций не остаётся.

Регистр не важен: OnlyValues, ONLYVALUES и onlyvalues — одно и то же.

Внутри одной ячейки параметры можно писать в столбик, нажимая Alt+Enter. Это удобно для длинных опций и обязательно, если в параметре есть формула Excel (в русской локали формулы содержат ;, который иначе был бы принят за разделитель опций).

Правило простое: ; разделяет опции только в конце строки. ; внутри формулы (например в ТЕКСТ(дата;«ДД.ММ.ГГГГ»)) разбор не ломает.

Это обратная сторона того же правила, и на ней легко обжечься. Перевод строки — разделитель параметров одной опции, а не разделитель опций. Чтобы с новой строки началась новая опция, в конце предыдущей строки нужна ;.

Верно — в конце первой строки стоит ;:

OnlyValues2;DeleteMacro;
MacroAfter=Module1.MacroAfter

Неверно — ; нет, и вторая строка приклеивается к DeleteMacro как её параметр:

OnlyValues2;DeleteMacro
MacroAfter=Module1.MacroAfter

Движок прочитает это как две опции: OnlyValues2 и DeleteMacro с параметром MacroAfter=Module1.MacroAfter. DeleteMacro параметров не ждёт и молча их игнорирует — то есть MacroAfter просто не сработает, и никакой ошибки при этом не будет.

Если опция «не сработала» — первым делом смотрите, стоит ли ; в конце предыдущей строки. На этом же правиле построен пример RangeBreaks в разделе 7: там ; стоит после OnlyValues и после последнего параметра RangeBreaks — то есть перед каждой строкой, с которой начинается следующая опция.

Отключает (замораживает) все формулы: в готовом отчёте вместо формул остаются только вычисленные значения.

• В A1 — по всей книге.
• В A2 — только по своему листу.

Применяется почти всегда: получатель документа не должен видеть формулы, а файл не должен «ломаться» при пересылке. Опция удаляет из файла скрытый лист с данными для формул, которые получатель не должен видеть.

OnlyValues

Выборочная заморозка. Замораживает в значения только те формулы, где есть ссылки на данные программы — то есть на поля (Title_, Table_ и т.п.) и на параметры (XLRParams_). Все пользовательские формулы Excel и итоги (=СУММ(…)) остаются живыми.

• В A1 — по всей книге.
• В A2 — только по своему листу.

Когда использовать: если в отчёте нужно оставить рабочие формулы (например, чтобы пользователь мог поменять количество и увидеть пересчёт суммы), но при этом убрать ссылки на данные, которых после выгрузки уже не существует. Опция удаляет из файла скрытый лист с данными для формул, которые получатель не должен видеть.

OnlyValues2

Внимание. OnlyValues2 не замораживает =СУММ(…). Если вам нужен полностью «замороженный» документ без формул — используйте обычный OnlyValues.

Подгоняет высоту строк под текст для ячеек, находящихся вне Range-областей (внутри областей эту работу делает RowsFit).

Работает и с обычными, и с объединёнными ячейками (обычный автоподбор высоты Excel объединённые ячейки не умеет). Высота только увеличивается: если текст помещается, строка не трогается. Скрытые столбцы не учитываются.

Ставится в A2. Через \ перечисляются ячейки и/или имена областей через запятую.

FitMergedCells\C5,D5,MyRange1,MyRange2

Типичный случай: объединённая ячейка с адресом клиента (=CLIENT_ADDRESS) или с наименованием фирмы (=FIRM_FIRM_NAME) — текст длинный, высота «съезжает».

FitMergedCells\E8,E9,E10,E11

Расстановка разрывов страниц по Range-области с повтором шапки таблицы на каждой новой странице, с необязательными промежуточными итогами по страницам и заголовком над шапкой (например «Универсальный передаточный документ №… Лист 2 из 3»).

Ставится в A2. Подробно — в разделе 7. RangeBreaks — разрывы страниц (подробно).

Задаёт имя макроса шаблона прямо в A1:

MacroAfter=Module1.MacroAfter

• MacroBefore — макрос выполняется до построения отчёта;
• MacroAfter — после построения, когда данные уже разложены.

Имя пишется как в Excel: Модуль.Процедура. Регистр не важен.

Все три означают одно и то же, выбирайте любую:

MacroAfter=Module1.MacroAfter

MacroAfter\Module1.MacroAfter

MacroAfter
Module1.MacroAfter

Третья — через Alt+Enter: перевод строки внутри ячейки движок и так считает разделителем, равным \.

Чего делать не надо. Не смешивайте разделители в одной записи: MacroAfter\=Module1.MacroAfter или MacroAfter + Alt+Enter + =Module1.MacroAfter дадут имя макроса, начинающееся со знака =, и макрос не найдётся.

Если перед опцией стоят другие, в конце предыдущей строки обязательна ; — иначе строка с MacroAfter будет прочитана как параметр предыдущей опции и макрос не сработает (см. Перевод строки НЕ заканчивает опцию):

OnlyValues2;DeleteMacro;
MacroAfter=Module1.MacroAfter

В одну строку — тоже верно:

OnlyValues2;DeleteMacro;MacroAfter=Module1.MacroAfter

До появления этой опции имя макроса задавалось двумя другими способами. Оба продолжают работать, но считаются устаревшими: опция в A1 нагляднее, видна рядом с остальными настройками шаблона и перекрывает их обоих.

Почему не рекомендуются. Именованную ячейку не видно при беглом просмотре шаблона, и про неё легко забыть при копировании листа. Решётка в имени файла вообще не говорит, какой макрос запустится: имя Module1.MacroAfter зашито в программу, и ни переименовать процедуру, ни задать MacroBefore этим способом нельзя. Вдобавок решётка работает только при выгрузке из документа, но не из конструктора отчётов.

Если заданы оба способа сразу — работает опция из A1. Это и есть штатный путь перевода старых шаблонов: добавить опцию в A1, ничего больше не трогая, и убедиться, что отчёт строится как прежде.

Удаляет из готового отчёта все макросы (VBA-модули). Ставится в A1. Используйте, если готовый файл уходит наружу и не должен содержать код.

DeleteMacro

Удаляет из готового отчёта все макросы (VBA-модули) за исключением системных модулей для работы с Excel (контроль действий при закрытии книги). Ставится в A1.

DeleteMacro2

OnlyValues;FitMergedCells\E8,E9,E10,E11

Отключает формулы в пределах этой области.

Включает автофильтр для области-списка.

Ограничение. Не работает в произвольных областях (с объединёнными ячейками или если размножаемая часть занимает больше одной строки) — приведёт к ошибке.

Автоматически подгоняет высоту строк области под значение в ячейке, если оно не помещается в высоту, заданную в шаблоне. Работает и с объединёнными ячейками. Незаменима, если в данных встречаются длинные наименования (Table_GOODS_NAME) или значения с переводом строки.

Чтобы опция работала, в свойствах ячейки должно быть установлено «Переносить по словам».

Ограничение. Если области вложенные, например Master-detail, то опцию нужно указывать у самой внешней Range области

То же самое, что RowsFit, но высота строки меняется только в сторону увеличения: если значение не влезает в заданную шаблоном высоту, строка увеличивается; если влезает — высота из шаблона сохраняется. В отличие от RowsFit, который работает в обе стороны — и увеличивает, и уменьшает.

Автоматически расширяет столбцы области по содержимому.

Отключает предварительную сортировку при использовании промежуточных итогов Group (см. раздел про итоги). Экономит время, если данные и так приходят отсортированными.

Ставится не только в области: в A1 действует на всю книгу, в A2 — на лист.

Когда это не ускорение, а необходимость. Если столбцов с Group и Sort в сумме больше трёх, предварительная сортировка не выполняется вообще. Тогда данные обязаны приходить отсортированными из запроса, а области надо ставить GroupNoSort.

Фильтр записывается в левую (служебную) ячейку строки данных области — то есть в ту же строку, где стоят поля (=Table_GOODS_NAME и т.д.), в самый левый служебный столбец.

Фильтр применяется до построения таблицы: не подошедшие записи в отчёт не попадают, итоги считаются уже по отфильтрованным строкам.

FILTER=<условие>

В условии имена полей пишутся без префикса области (просто MIX_ID, QUANT).

В номенклатуре тип позиции MIX_ID = 2 — это услуга. Чтобы в расходную накладную попали только товары:

FILTER=MIX_ID<>2

Только товары, у которых есть количество и цена выше себестоимости:

FILTER=MIX_ID<>2 AND QUANT>0 AND PRICE>SELFCOST

Позиции с суммой строки от 1000, кроме услуг:

FILTER=MIX_ID NOT IN (2) AND QUANT*PRICE>=1000

Если привычнее мыслить формулами Excel, используйте EXFILTER. В формуле поля пишутся полностью, как в ячейках области (Table_QUANT, Table_MIX_ID).

EXFILTER=И(Table_MIX_ID<>2; Table_QUANT>0)

Строка попадает в отчёт, если формула вернула ИСТИНА.

EXFILTER=ИЛИ(Table_PRICE*Table_QUANT>=1000; Table_MIX_ID=2)

Подсказка. FILTER работает быстрее и его условия короче. EXFILTER удобен, когда нужна функция Excel (ЕСЛИ, ПОИСК, ЛЕВСИМВ и т.п.). Можно указать обе опции: сначала отработает FILTER, затем EXFILTER по оставшимся строкам.

Формула в EXFILTER пишется в русской локали (И, ИЛИ, ЕСЛИ) — так же, как вы написали бы её в ячейке.

Порядок обработки области: FILTER → EXFILTER → схлопывание строк (GroupRowID) → итоги и группировка. То есть итоги всегда считаются по тому, что осталось после фильтров.

Опции пишутся в служебной строке области, каждая — под своим столбцом.

Ограничение Excel: максимум три ключа сортировки. Если столбцов с Sort / Asc / Desc и Group в сумме больше трёх, область не сортируется вообще — не «по первым трём», а совсем. Сортировка и группировка считаются вместе: три уровня группировки плюс один столбец сортировки — это уже четыре ключа. В таких случаях сортируйте данные в запросе отчёта (ORDER BY) и ставьте области GroupNoSort.

В служебной ячейке столбца указывается вид итога:

По столбцу можно получить только один итог. Если указать несколько — применится последний.

В служебной строке под столбцом «Количество» (=Table_QUANT) пишем Sum, под столбцом «Сумма» — тоже Sum. В ячейке слева от итоговых можно написать «Итого».

Опция Group ставится в тех столбцах, по которым группируем. В отчёте появятся строки промежуточных итогов (по тем столбцам, где указаны Sum, Avg и т.д.).

• Уровни группировки идут слева направо по столбцам шаблона.
• Group\Collapse — сразу свернуть дерево итогов.
• GroupNoSort — отключить предварительную сортировку. Ставится в A1, в A2 или в области.

Практических ограничений два: три ключа сортировки (см. выше — при четырёх и более область не сортируется вообще) и восемь уровней структуры Excel.

У Group есть ещё с десяток параметров: объединение подписей, позиция строки итогов, заголовок группы отдельной строкой, отключение подытогов и структуры, разрывы страниц. Все они и то, как устроена группировка внутри, — на странице «Опции шаблонов Excel: группировка, итоги и остальные опции».

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

Остаётся первая строка группы, остальные удаляются.

Столбцы области: Наименование, Артикул, Количество, Цена, Сумма.

• Под столбцом Наименование (или под =Table_GOOD_ID) — GroupRowID
• Под столбцом Количество — Sum;GroupRowSum
• Под столбцом Сумма — Sum;GroupRowSum

Наименование:  GroupRowID
Количество:    Sum;GroupRowSum
Сумма:         Sum;GroupRowSum

Если группировать нужно по нескольким признакам (например по позиции и цене), поставьте GroupRowID под обоими столбцами.

Обратите внимание: две опции в одной ячейке разделяются ; — Sum;GroupRowSum. Sum считает общий итог по столбцу, GroupRowSum — складывает значения при схлопывании.

Опция листа (ячейка A2). Нужна для многостраничных документов (УПД, счёт-фактура, ТОРГ-12), где на каждой странице должна повторяться шапка таблицы, а внизу страницы — промежуточный итог.

На каждом разрыве страницы внутри области:

1. (если задан SUBSUMCOLS) вставляет строку промежуточного итога — это копия

итоговой строки области, с суммами только по строкам этой страницы;

1. копирует шапку таблицы (HEAD) в начало новой страницы;
2. (если заданы PREHEADLEFT/PREHEADRIGHT) вставляет над шапкой строку заголовка;
3. ставит разрыв страницы.

В конце подставляет общее число страниц туда, где стоит плейсхолдер.

{PAGES} можно ставить и в тексте подвала (например «Документ составлен на {PAGES} листах») — укажите такие ячейки в PAGECOUNT, либо просто напишите плейсхолдер в ячейке: он будет заменён.

Ячейка A2 (параметры вводятся в столбик через Alt+Enter):

OnlyValues;
RangeBreaks
RANGE=TableRange
HEAD=B19:AI20
LASTROW=LastRowUPD
SUBSUMCOLS=L,P,S,T
PAGECOUNT=PageCountInfo
PREHEADLEFT="Универсальный передаточный документ №" & СЖПРОБЕЛЫ(Title_CHECK_NUMBER) & " от " & ТЕКСТ(Title_CHECK_DATE;"ДД.ММ.ГГГГ") & " г."
PREHEADRIGHT=Лист {PAGE} из {PAGES};
FitMergedCells\E8,E9

Что здесь происходит:

• RANGE=TableRange — область с позициями документа.
• HEAD=B19:AI20 — две строки шапки таблицы («Наименование / Кол-во / Цена / Сумма» и строка нумерации столбцов). Эти строки повторятся на каждой странице.
• LASTROW=LastRowUPD — имя ячейки в последней строке документа; ниже неё разрывы не ставятся (там подписи и подвал).
• SUBSUMCOLS=L,P,S,T — промежуточные итоги считаются по столбцам L, P, S, T (количество, сумма без НДС, НДС, сумма с НДС).
• PAGECOUNT=PageCountInfo — в ячейке с именем PageCountInfo (в подвале: «Документ составлен на {PAGES} листах») подставится число страниц.
• PREHEADLEFT=… — над шапкой каждой новой страницы появится «Универсальный передаточный документ №123 от 05.02.2024 г.».
• PREHEADRIGHT=Лист {PAGE} из {PAGES} — справа «Лист 2 из 3».
• Строка PREHEADRIGHT заканчивается ; — это конец опции RangeBreaks, далее идёт следующая опция.

Про столбцы для итогов. Вместо букв удобнее задать имена ячейкам итоговой строки (меню Формулы\Присвоить имя) и перечислить имена: тогда при добавлении/удалении столбцов в шаблоне опцию править не придётся.

SUBSUMCOLS=SumQuant,SumNoNDS,SumNDS,SumTotal

При включённых промежуточных итогах итоговая строка самой области пересчитывается на сумму последней страницы (так устроена печатная форма). Общий итог документа должен находиться в строке ниже области (например «Всего к оплате»).

Чтобы эта строка не «подхватила» промежуточный итог последней страницы, RangeBreaks принудительно замораживает формулы листа (как OnlyValues), если OnlyValues не задан в книге или на листе. Это сделано намеренно — для защиты сумм в документе.

Рекомендация. Всегда указывайте OnlyValues в A1 для документов с RangeBreaks —

так поведение будет предсказуемым.

PREHEADLEFT / PREHEADRIGHT можно задать двумя способами:

1) Формулой прямо в опции (как в примере выше). Формула пишется в русской локали (СЖПРОБЕЛЫ, ТЕКСТ), точка с запятой внутри формулы разбор не ломает.

2) Ссылкой на ячейку — если формула длинная, положите её в свободную ячейку (например A3) и укажите её адрес. Ячейка будет очищена после построения.

PREHEADLEFT=A3
PREHEADRIGHT=A4

Строка заголовка автоматически: без рамок, без переноса по словам, шрифт берётся из строки над шапкой шаблона, левый текст выравнивается влево, правый — вправо.

Про подвал. Если разрыв страницы приходится внутрь подвала (строка итогов + подписи), он должен уехать на новую страницу целиком, а с ним - ровно одна последняя строка данных включая строку итогов.

Опции, которых нет в этой таблице (SummaryAbove, DisableGrandTotals, SmartGroup, DeleteColumn, Chart, Pivot, Hide, Delete и другие), — на странице «Опции шаблонов Excel: группировка, итоги и остальные опции».

A1:

OnlyValues

Левая ячейка строки данных области TableRange:

FILTER=MIX_ID<>2

Левая нижняя ячейка области:

RowsFit

Служебная строка области:

Наименование:  GroupRowID
Количество:    Sum;GroupRowSum
Сумма:         Sum;GroupRowSum

Левая ячейка строки данных:

FILTER=MIX_ID=2

См. пример в разделе 7. RangeBreaks — разрывы страниц (подробно).

Если объединённые ячейки с адресом клиента и наименованием фирмы «обрезают» текст:

A2:

FitMergedCells\E8,E9,E10,E11