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

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

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

служебные опции листа и книги).

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

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

1. Сортировка. Область сортируется по колонкам группировки — если не задан GroupNoSort.
2. Промежуточные итоги. Для каждого уровня группировки выполняется команда Excel

«Промежуточные итоги» (Range.Subtotal). Она вставляет строки итогов и строит структуру

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

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

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

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

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

Group\MergeLabels\DisableSubtotals

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

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

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

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

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

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

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

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

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

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

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

• подытоги вложенных уровней считаются в пределах внешней группы, даже если у внешнего

уровня итоги отключены;

• строка общего итога остаётся — она управляется отдельной опцией DisableGrandTotals;
• если DisableSubtotals стоит у всех уровней, строка общего итога всё равно выводится,

но без подписи (подписывать единственную строку итогов незачем).

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

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

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

Group\MergeLabels\SummaryAbove

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

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

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

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

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

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

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

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

итогов свою формулу вместо той, что пишет Excel.

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

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

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

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

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

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

• Убрать — опция области DisableGrandTotals.
• Подпись («Общий итог») Excel пишет в колонку самого внешнего уровня группировки —

независимо от того, у каких уровней итоги отключены.

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

формулы в колонках агрегатов есть, текста нет. Если подпись нужна — напишите её в

  строке-образце, в нужной колонке: формулы и текст образца переносятся на строку итогов.

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

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

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

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

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

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

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

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

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

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

Отличия от Group:

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

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

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

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

GroupWithHeader\GroupCount=2

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

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

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

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

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

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

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

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

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

RowsFit

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

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

SummaryAbove

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

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

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

GroupNoSort

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