Как скрыть все промежуточные итоги в сводной таблице в Excel

Создание сводной таблицы Excel включает в себя промежуточные итоги по умолчанию. Это неизбежно приводит к трудночитаемому отчету с множеством цифр.

Вы можете вручную скрыть все промежуточные итоги выбрав в меню Конструктор ➜ Промежуточные итоги ➜ Не Показывать промежуточные суммы. Если вы хотите настроить автоматизированное построение сводных таблиц, то и данное действие лучше прописать в макросе.

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

ActiveSheet.PivotTables("Pvt1").PivotFields("Регион").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

Excel передает массив с 12 ложными параметрами. 12 параметров False нужно, потому что существует двенадцать видов промежуточных итогов — сумма, среднее, количество, минимум, максимум и т.д.

Альтернативный способ отключить промежуточные итоги – перевести первый параметр в True. Это автоматически переведет остальные 11 в False. После этого мы присваиваем и первому параметру значение False, скрывая все промежуточные итоги.

В этом примере кода, мы создаем первый промежуточный итог с параметром True, а затем меняем его на False. Это удаляет промежуточный итог.

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

1. Шаг 1 объявляет две переменные объекта. Этот макрос использует РТ в качестве контейнера памяти для сводной таблицы и использует PF в качестве контейнера памяти для полей. Это позволяет перебрать все сводные поля в сводной таблице. Этот макрос разработан таким образом, что мы определяем активную сводную таблицу на основе активной ячейки. То есть, активная ячейка должна быть внутри сводной таблицы для запуска этого макроса. Таким образом мы должны выделить сводную таблицу, прежде чем запустить макрос. 

2. Шаг 2 присваиваем активной сводной таблице переменную РТ. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевого диапазона. Если активная ячейка не находится внутри сводной таблицы, выдается ошибка. Именно поэтому макрос использует On Error Resume Next Statement. Это говорит Excel продолжить макрос, если есть ошибка. 

3. Шаг 3 проверяет переменную РТ - если она пустая, то выводим пользователю сообщение о том, что необходимо поместить курсор в сводную таблицу и выходим из процедуры.

4. Если макрос достигает Шага 4, значит активная ячейка была в сводной таблице. И макрос начинает циклом перебирать каждое поле сводной таблицы и отключать промежуточные итоги. Оператор For Each перебирает каждое поле сводной. После того, как все поля были оценены, макрос заканчивается. 

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

1. Активируйте редактор Visual Basic, нажав ALT + F11. 
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта. 
3. Выберите Insert➜Module. 
4. Введите или вставьте код во вновь созданном модуле.