Ограничения, присущие сводным таблицам Excel 2010 при форматировании кодом VBA

Как и при создании сводных таблиц с помощью пользовательского интерфейса, в процессе формирования сводных таблиц с помощью кода VBA следует учитывать ряд ограничений, рассматриваемых в настоящей статье.

Заполнение пустых ячеек в области данных

Многих пользователей раздражает наличие пустых ячеек в области данных сводной таблицы. Соответствующий пример приведен на рис. 12.4, где в регионе Север отсутствуют продажи оборудования под названием 10-Minute Dial Lighted Timer. Соответствующая ячейка пуста; уместнее было бы отобразить в ней значение 0. Для отображения нулей в пустых ячейках можно воспользоваться настройкой Для пустых ячеек отображать (For Empty Cells Show), находящейся в окне Параметры сводной таблицы (PivotTable Options). Для отображения этого окна выберите контекстную вкладку Параметры (Options) и щелкните на кнопке Параметры. Эту же задачу выполняет следующая строка кода:

PT.NullString = "0"

Обратите внимание на то, что средство записи макросов Excel всегда заключает нули в кавычки. Независимо от того, укажете ли вы «0» либо о, пустые ячейки в области данных сводной таблицы будут всегда заполнены числовыми нулями.

Заполнение пустых ячеек в области строк

В Excel 2010 появилась давно ожидаемая настройка, позволяющая заполнять пустые ячейки в расположенных слева столбцах сводной таблицы (путем повторения названий строк). Эта проблема появляется в случае, если в области строк отображаются два или большее количество полей. По умолчанию вместо повторения надписей, таких как Грили, духовки и СВЧ-печи, в левой колонке в этом случае остаются пустые ячейки (см. рис. 12.4). Помимо команд интерфейса, для решения этой проблемы в Excel 2010 можно воспользоваться следующим кодом:

РТ.RepeatAllLabels xlRepeatLabels

Невозможность перемещения или изменения части отчета

Несмотря на всю эффективность сводных таблиц, они имеют некоторые ограничения. Вы не сможете перемешать или изменять только часть сводной таблицы, даже если это каталог радиотелефонов или список производителей мобильной техники и аксессуаров. Попытайтесь, к примеру, запустить макрос, который удаляет столбец, содержащий значение Общий итог (Grand Total). Макрос тут же известит вас о том, что возникла ошибка 1004. Чтобы обойти указанное ограничение, можно воспользоваться одной из следующих двух стратегий. Первая стратегия заключается в том, чтобы найти эквивалентные команды в интерфейсе Excel, используемом для создания и изменения сводных таблиц. В частности, с помощью команд Excel можно выполнить следующие задачи:

  • удаление столбца общих итогов;
  • удаление строки общих итогов;
  • добавление пустых строк между разделами;
  • сокрытие промежуточных итогов для внешних полей строк.

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

Управление итогами

Изначально в состав любой сводной таблицы входят одна строка и один столбец общих итогов. С помощью команд интерфейса Excel 2010 можно скрыть один либо оба этих элемента. Для удаления столбца общих итогов, отображаемого в правой части сводной таблицы, воспользуйтесь следующим кодом:

PT.ColumnGrand = False

Для удаления строки общих итогов применяется такой код:

PT.RowGrand = False

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

Знаете пи вы, что сводная таблица может включать несколько строк промежуточных итогов? Как правило, эти возможности не используются. Если же нужно ими воспользоваться, перейдите в диалоговое окно Параметры поля (Field Settings) и выберите одну из функций, применяемых для вычисления промежуточных итогов: Сумма (Sum), Количество (Count), Среднее (Average), Максимум (Мах), Минимум (Min) и т.д.

Для сокрытия промежуточных итогов, отображаемых для поля, свойству Subtotals следует присвоить массив, состоящий из 12 значений False. Первое значение False отключает отображение автоматических промежуточных итогов, второе значение False — отображение промежуточных итогов, сформированных с помощью функции Сумма, третье значение False отключает отображение промежуточных итогов, сформированных с помощью функции Количество, и т.д. Следующая строка кода отменяет отображение промежуточного итога Категория оборудования.

	РТ.PivotFields("Категория оборудования").Subtotals = Array( _
	False, False,  False,  False,  False,  False,  False,  False, _
	False,  False,  False, False)

Для сокрытия первого промежуточного итога применяется другая методика. С ее помощью можно отключить отображение других 11 промежуточных итогов. Затем можно отключить отображение первого промежуточного итога, чтобы гарантировать отключение всех промежуточных итогов.

PT.PivotFields("Категория оборудования").Subtotals(1)  = True
РТ.PivotFields{"Категория оборудования").Subtotals(1)  = False
Top