====== Опции шаблонов Excel: группировка, итоги и остальные опции ======

Эта страница — **дополнение** к основной: «[[docs:settings:templates_option|Опции шаблонов Excel]]». Там описаны опции,
которые используются чаще всего. Здесь — то, чего там нет:

  * группировка (''Group'') со **всеми** параметрами и тем, как она устроена внутри;
  * опции области, управляющие итогами (''SummaryAbove'', ''DisableGrandTotals'' и другие);
  * оформление строк итогов — откуда берутся формат и цвет;
  * остальные опции пакета, не вошедшие в основную страницу (диаграммы, сводные таблицы,
    служебные опции листа и книги).

Правила записи опций (разделители '';'' и ''\'', перевод строки через **Alt+Enter**, регистр
не важен) — те же, что на основной странице, и здесь не повторяются.

===== 1. Как устроена группировка =====

Понимание механики экономит много времени: почти все «странности» группировки объясняются тем,
что её делает **сам Excel**, а шаблон только задаёт параметры.

==== Что происходит по шагам ====

  - **Сортировка.** Область сортируется по колонкам группировки — если не задан ''GroupNoSort''.
  - **Промежуточные итоги.** Для каждого уровня группировки выполняется команда Excel
    «Промежуточные итоги» (''Range.Subtotal''). Она вставляет строки итогов и строит **структуру**
    (те самые «плюсики» слева).
  - **Оформление.** На строки итогов накладываются формат и формулы **строки-образца** —
    нижней служебной строки области (см. раздел 4).
  - **Объединение подписей** (''mergelabels'') — ключ группы объединяется по строкам блока.
  - **Структура и свёртка** (''disableoutline'', ''collapse'').
  - **Общий итог** — его тоже делает Excel, одной строкой на всю область.

==== Три действующих лица ====

^Что ^Где задаётся ^Роль ^
|**Колонка-ключ**|''Group'' в служебной строке под колонкой|По её значениям данные делятся на группы. Колонок может быть несколько — это уровни, они идут **слева направо**|
|**Колонки агрегатов**|''Sum'', ''Avg'', ''Count'' и т. д. под колонками|Что именно считать в строках итогов|
|**Строка-образец**|нижняя служебная строка области|Её формат (и её формулы) переносятся на каждую строку итогов|

==== Подпись строки итога ====

Текст подписи формирует **Excel**, а не шаблон. Он зависит от функции агрегата и от языка Excel:

^Агрегат ^Подпись группы ^Подпись общего итога ^
|''Sum''|''Кабель ВВГ Итог''|''Общий итог''|
|''Count'' / ''CountNums''|''Кабель ВВГ Количество''|''Общее количество''|
|''Avg''|''Кабель ВВГ Среднее''|''Общее среднее''|

Подпись пишется **в колонку своего уровня** — ту, под которой стоит ''Group''.
Переадресовать её в другую колонку можно параметром ''placetocolumn'' (см. ниже).

>Если в колонке-ключе значение **пустое**, подпись получается просто «'' Итог''» — это нормально
>и означает «группа без значения ключа». Такие строки группируются отдельно и получают свои
>подытоги, как любые другие.

===== 2. Опция Group — все параметры =====

Ставится в служебной строке области, **под колонкой**, по которой группируем.
Параметры добавляются через ''\'' и могут сочетаться:

<code>
Group\MergeLabels\DisableSubtotals
</code>

^Параметр ^Что делает ^
|''Asc'' / ''Desc''|Направление предварительной сортировки по этой колонке|
|''MergeLabels''|Объединить ячейки ключа по строкам группы|
|''MergeLabels2''|То же, но в объединение входит и строка итогов этого уровня|
|''MergeLabels3''|Как ''MergeLabels2'', см. оговорку ниже|
|''WithHeader''|Выводить подпись группы **отдельной строкой над** блоком|
|''PlaceToColumn=N''|Писать подпись группы не в свою колонку, а в колонку N области|
|''Collapse''|Свернуть структуру до этого уровня|
|''DisableSubtotals''|У этого уровня **не** выводить строки итогов|
|''DisableOutline''|Не строить структуру («плюсики») по этому уровню|
|''PageBreaks''|Разрыв страницы после каждой группы этого уровня|
|''SummaryAbove'' / ''SummaryBelow''|Позиция строки итогов **именно этого** уровня|

==== MergeLabels, MergeLabels2, MergeLabels3 ====

Самый частый вопрос — чем они отличаются.

**''MergeLabels''** — объединяются ячейки ключа **по строкам данных** группы. Строка итогов
этого уровня в объединение **не входит**: в ней остаётся собственная подпись («Кабель ВВГ Итог»).

**''MergeLabels2''** — в объединение входит и **строка итогов** этого уровня. В объединённой
ячейке остаётся значение ключа (подпись итога при этом гасится, чтобы в объединении не всплыл
текст «Итог» вместо наименования).

**''MergeLabels3''** — в отчётах **с агрегатами** (там, где есть ''Sum'' и подобные) ведёт себя
так же, как ''MergeLabels2''. Разница проявляется только в отчётах **без агрегатов**: там
''MergeLabels2'' оставляет значение в объединённой ячейке, а ''MergeLabels3'' её очищает и при
этом строит структуру. Вариант экзотический — если не уверены, используйте ''MergeLabels2''.

>**Важно для уровня с ''DisableSubtotals''.** У такого уровня своих строк итогов нет, поэтому
>''MergeLabels'' и ''MergeLabels2'' дают **одинаковый** результат: включать в объединение нечего.

==== WithHeader ====

Подпись группы выводится **отдельной строкой над** блоком (как заголовок раздела), а не в
строке итогов. Строка вставляется и оформляется по строке-образцу.

Включение ''WithHeader'' хотя бы на одном уровне меняет режим у **всех** уровней выше него.

==== PlaceToColumn=N ====

Подпись группы пишется в колонку **N области** (не листа). Нумерация ведётся **с нуля**:
''PlaceToColumn=0'' — первая колонка области. Удобно, когда группируем по технической колонке
(например по ''GOOD_ID''), а показать наименование нужно в другой.

==== Collapse ====

Сворачивает структуру. Если поставить у вложенного уровня — свернёт **до этого уровня**.
Альтернатива с явным указанием глубины — опция области ''Outline\CollapseLevel=N''.

==== DisableSubtotals ====

Уровень участвует в группировке (сортировка, объединение подписей, структура), но строк итогов
по нему **нет**. Типовой случай: по товару нужны подытоги, а по разделу — только объединённая
подпись.

Что при этом важно знать:

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

==== DisableOutline ====

Отключает структуру (группировку строк «плюсиками») по этому уровню. Сами строки итогов
остаются. Нужно, когда документ уходит на печать и «плюсики» в нём не нужны.

==== PageBreaks ====

После каждой группы этого уровня ставится разрыв страницы. Не путать с опцией листа
''RangeBreaks'' — та режет страницы **по вместимости** и повторяет шапку; ''PageBreaks'' режет
**по смыслу**, на границах групп.

==== SummaryAbove / SummaryBelow у колонки ====

Позиция строки итогов **этого** уровня: над данными группы или под ними. Задаётся параметром
колонки и переопределяет общий режим области.

<code>
Group\MergeLabels\SummaryAbove
</code>

Типовой случай: общий режим — «итоги сверху» (так удобнее читать длинный отчёт), а у
самой вложенной подгруппы итог нужен снизу, сразу после её строк.

>Уровни без своего параметра берут режим области (''SummaryAbove'' / ''SummaryBelow'' у области,
>см. раздел 3). Если не задано ничего — итоги **снизу**, как в Excel по умолчанию.

===== 3. Опции области, управляющие итогами =====

Ставятся в **левой нижней** ячейке области (там же, где ''RowsFit'' и ''AutoFilter'').

^Опция ^Что делает ^
|''SummaryAbove''|Строки итогов **над** данными группы — для всей области|
|''SummaryBelow''|Строки итогов **под** данными (поведение по умолчанию)|
|''DisableGrandTotals''|Не выводить строку **общего** итога|
|''GroupNoSort''|Не сортировать перед группировкой|
|''Outline\CollapseLevel=N''|Свернуть структуру до уровня N|
|''AutoFilter''|Автофильтр по области (ставится по имени области, с группировкой совместим)|

''GroupNoSort'' можно поставить и шире — в **A1** (на всю книгу) или в **A2** (на лист).
Это удобно, когда отчёт заведомо приходит отсортированным из запроса.

>**Когда ''GroupNoSort'' не просто ускоряет, а необходим** — см. ограничение на три ключа
>сортировки в разделе 7.

===== 4. Оформление строк итогов =====

Строки итогов оформляются по **строке-образцу** — это **нижняя служебная строка области**
(та самая, в которой написаны ''Group'', ''Sum'' и прочие опции). Она не попадает в готовый
отчёт: после построения её удаляют, а её оформление уезжает на строки итогов.

Что переносится:

  * **формат** ячеек — шрифт, рамки, заливка, числовой формат, выравнивание;
  * **формулы** строки-образца — там, где её ячейка не пуста. Это позволяет положить в строку
    итогов свою формулу вместо той, что пишет Excel.

==== Свой цвет на каждый уровень ====

Заливка и цвет шрифта строки итогов берутся **из ячейки строки-образца в колонке своего уровня**.
Это значит:

  * покрасьте ячейку служебной строки **в колонке группы** — так будут выглядеть итоги группы;
  * покрасьте ячейку **в колонке подгруппы** — так будут выглядеть итоги подгруппы.

Строка **общего** итога оформляется по колонке самого внешнего уровня.

<code>
Колонка X (группа):     Group\MergeLabels      ← ячейку залить, скажем, серым
Колонка AG (подгруппа): Group\MergeLabels      ← ячейку залить светло-серым
Колонка M (сумма):      Sum
</code>

Итоги группы получатся серыми, итоги подгруппы — светло-серыми, остальной формат (рамки,
числовой формат, шрифт) — общий, из строки-образца.

===== 5. Что происходит с «неудобными» значениями ключа =====

^Случай ^Поведение ^
|**Пустое** значение ключа|Образует свою группу со своими подытогами. Подпись итога выглядит как «'' Итог''»|
|В ключе **формула**|Группировка идёт по **результату** формулы. Движок пересчитывает лист перед выполнением опций, поэтому «невычисленная» формула группировку больше не ломает|
|В ключе **ошибка** (''#ЗНАЧ!'', ''#Н/Д'')|Строки с ошибкой не роняют построение: такие значения сравниваются как текст. Но в отчёте ошибка останется видна — это дефект шаблона, его надо править|
|Разный **регистр** («Кабель» и «кабель»)|Разные группы: сравнение регистрозависимое|

===== 6. Общий итог =====

Строку общего итога делает Excel, одну на всю область, в том же режиме (сверху/снизу), что и
остальные итоги.

  * Убрать — опция области ''DisableGrandTotals''.
  * Подпись («Общий итог») Excel пишет в колонку **самого внешнего** уровня группировки —
    независимо от того, у каких уровней итоги отключены.
  * Если итоги отключены у **всех** уровней, строка общего итога выводится, но **без подписи**:
    формулы в колонках агрегатов есть, текста нет. Если подпись нужна — напишите её в
    строке-образце, в нужной колонке: формулы и текст образца переносятся на строку итогов.

===== 7. Ограничения и ловушки =====

==== Сортировка: три ключа, иначе не сортирует вовсе ====

Excel сортирует максимум по **трём** колонкам за один проход. Движок использует один проход,
поэтому:

>Если колонок с ''Sort'' / ''Asc'' / ''Desc'' **и** ''Group'' в сумме больше трёх —
>область не сортируется **вообще** (не «по первым трём», а совсем).

Это самая частая причина «группировка порвалась на части»: при четырёх уровнях группировки
данные приходят неотсортированными, и одна и та же группа встречается в отчёте несколько раз.

**Что делать:** сортировать данные **в запросе отчёта** (''ORDER BY'') и поставить области
''GroupNoSort''. Это и правильнее, и быстрее.

==== Произвольные области ====

''Group'', ''Sort'' и ''AutoFilter'' работают только в тех областях, где одна запись занимает
ровно **одну строку** шаблона. В произвольной области (запись на несколько строк, объединённые
ячейки) они молча ничего не делают. Для таких областей есть отдельная опция ''GroupWithHeader''
(раздел 9).

==== Глубина структуры ====

Структура Excel ограничена **8 уровнями**. Каждый уровень группировки с итогами занимает
уровень структуры. Практический потолок — 3–4 уровня; дальше отчёт всё равно нечитаем.

==== Вложенные области (master/detail) ====

Опции области (''RowsFit'', ''SummaryAbove'' и т. п.) указывайте у **самой внешней** области.

==== Порядок уровней ====

Уровни группировки идут **слева направо** по колонкам шаблона. Чтобы поменять их местами,
надо поменять местами колонки, а не порядок опций.

==== Сортировка и схлопывание строк ====

''GroupRowID'' / ''GroupRowSum'' (схлопывание одинаковых строк, см. основную страницу) работают
**до** подсчёта итогов. То есть итоги считаются уже по схлопнутым строкам — это обычно и нужно.

===== 8. SmartGroup =====

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

Отличия от ''Group'':

  * предварительной сортировки **нет** по умолчанию (как если бы всегда стоял ''GroupNoSort'');
  * объединение подписей (''MergeLabels'') включено **по умолчанию**;
  * подытоги строятся собственным кодом, а не командой Excel «Промежуточные итоги».

Параметры: ''MergeLabels'', ''MergeLabels2'', ''DisableSubtotals'', ''PageBreaks''.
Параметры ''WithHeader'', ''DisableOutline'', ''DisableGrandTotals'' в коде **зарезервированы**,
но не реализованы — рассчитывать на них не стоит.

>Для новых шаблонов используйте обычную ''Group'': она отлажена, покрыта параметрами и ведёт
>себя предсказуемо. ''SmartGroup'' остаётся для совместимости со старыми шаблонами.

===== 9. GroupWithHeader =====

Опция **области** для **произвольных** областей — там, где одна запись занимает несколько строк
шаблона и обычная ''Group'' не работает.

<code>
GroupWithHeader\GroupCount=2
</code>

''GroupCount=N'' — сколько первых строк блока считаются заголовком группы (по умолчанию 1).
Итоги берутся из колонок, помеченных ''Sum'', ''Avg'' и т. д.

>Опция редкая и в шаблонах НЭП почти не встречается. Перед применением проверьте результат
>на макете: поведение сильно зависит от геометрии области.

===== 10. Прочие опции, которых нет в основной странице =====

==== Итоги ====

^Опция ^Где ^Что делает ^
|''Sum\TotalOfRow=N''|столбец|Для **произвольной** области: считать итог только по тем строкам, которые порождены N-й строкой блока шаблона. По умолчанию 1|

==== Лист и книга ====

^Опция ^Где ^Что делает ^
|''Hide''|A2|Скрыть лист в готовом отчёте|
|''SheetHide''|A2|Скрыть лист «жёстко» (не виден в списке скрытых листов Excel)|
|''Delete''|A2|Удалить лист из готового отчёта. Удобно для листов-справочников, которые нужны только при построении|
|''AutoScale''|A1 / A2|Подогнать масштаб печати под ширину страницы|
|''AutoSafe''|A1 / A2|Автосохранение книги после построения|
|''ShowPivotBar''|A1|Показать панель сводных таблиц|

==== Область и столбец ====

^Опция ^Где ^Что делает ^
|''OnlyValues''|столбец|Заморозить формулы только в **одном столбце** области|
|''RowsFit'' / ''RowsFit2''|A1 / A2|Подгон высоты строк не по области, а на весь лист или всю книгу|
|''ColsFit''|A1 / A2 / столбец|Подгон ширины: на книгу, на лист или на отдельный столбец|
|''DeleteColumn''|столбец|Удалить столбец из готового отчёта. Для технических колонок (ключи, служебные вычисления), которые нужны при построении, но не нужны получателю|
|''Outline\CollapseLevel=N''|область|Свернуть структуру до уровня N|
|''Chart\Name=ИмяДиаграммы''|область|Построить диаграмму по данным области|

===== 11. Сводные таблицы (Pivot) =====

Отдельный пакет опций. Опция области:

<code>
Pivot\Name=ИмяСводной\Dst=Лист2!A1\RowGrand\ColumnGrand
</code>

^Параметр ^Что делает ^
|''Name=''|Имя создаваемой сводной таблицы|
|''Dst=''|Куда её поместить (адрес ячейки, можно на другом листе)|
|''DataToRows''|Поля данных — в строки, а не в столбцы|
|''RowGrand'' / ''ColumnGrand''|Общие итоги по строкам / по столбцам|
|''MergeLabels''|Объединять подписи|
|''NoPreserveFormatting''|Не сохранять форматирование при обновлении|
|''Refresh=СводнаяN''|Обновить перечисленные существующие сводные таблицы|
|''RefreshChart=ДиаграммаN''|Обновить перечисленные сводные диаграммы|

Роль каждой колонки задаётся в служебной строке области:

^Опция ^Роль колонки в сводной ^
|''Row''|В строки|
|''Column''|В столбцы|
|''Data''|В значения|
|''Page''|В фильтр страницы|

В сводной таблице также работают ''Group'' (с параметрами ''Asc'', ''Desc'', ''Collapse'')
и ''GroupNoSort''.

===== 12. Диагностика: группировка «не работает» =====

^Симптом ^Проверьте ^
|Группы повторяются по нескольку раз|Данные не отсортированы. Колонок с ''Sort''+''Group'' больше трёх → сортировки не было вообще. Сортируйте в запросе + ''GroupNoSort''|
|Группировки нет совсем|Область произвольная (запись занимает больше одной строки) — ''Group'' в таких не работает|
|Подытогов нет, а подписи объединились|У уровня стоит ''DisableSubtotals''|
|Нет ни одного агрегата в строках итогов|Не указан ни один ''Sum'' / ''Avg'' / … под колонками|
|Строки итогов «съели» формат данных|Проверьте строку-образец: именно её формат переносится на итоги|
|Итоги не того цвета|Цвет берётся из ячейки строки-образца **в колонке своего уровня**, а не из всей строки|
|Объединённая подпись показывает «Итог» вместо наименования|Так ведёт себя ''MergeLabels2'' в старых сборках; в актуальной подпись итога гасится автоматически|
|Отчёт строится заметно дольше|Переопределение позиции итогов по уровням (''SummaryAbove'' / ''SummaryBelow'' у колонки) требует перестановки строк. Если разницы в позиции не нужно — не задавайте параметр|

===== 13. Готовые сочетания =====

==== Отчёт по разделам и товарам, итоги снизу ====

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

<code>
Раздел:      Group\MergeLabels2
Товар:       Group
Количество:  Sum
Сумма:       Sum
</code>

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

<code>
RowsFit
</code>

==== Длинный отчёт: итоги сверху, свёрнутый по разделам ====

<code>
Раздел:      Group\MergeLabels2\Collapse
Товар:       Group
Сумма:       Sum
</code>

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

<code>
SummaryAbove
</code>

==== Разделы — только подписью, итоги только по товару ====

<code>
Раздел:      Group\MergeLabels\DisableSubtotals\DisableOutline
Товар:       Group
Сумма:       Sum
</code>

==== Печатная форма: каждый раздел с новой страницы ====

<code>
Раздел:      Group\MergeLabels2\PageBreaks
Товар:       Group
Сумма:       Sum
</code>

==== Данные уже отсортированы запросом ====

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

<code>
GroupNoSort
</code>

Обязательно, если уровней группировки и сортировки в сумме больше трёх.