Использование VBA для создания сводных таблиц
3871

Отчет о доходах по рынкам сбыта и категориям оборудования

Типичный отчет должен содержать список рынков сбыта с показателями дохода за год по категориям оборудования. Этот отчет можно передать менеджерам, подытожив рынки сбыта, характеризуюшиеся наибольшим доходом от продажи оборудования. Представленный на рис. 12.8 отчет не является сводной таблицей, хотя создавший этот отчет макрос использовал сводную таблицу для суммирования данных. Окончательное завершение отчета осуществлялось с помощью обычных функций Excel, таких ПРОМЕЖУТОЧНЫЕ. ИТОГИ (Subtotal).

Рис. 12.8. Отчет начинается как сводная таблица, а завершается как обычный набор данных

Рис. 12.8. Отчет начинается как сводная таблица, а завершается как обычный набор данных

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

Рис. 12.9. Типичный запрос менеджера заключается в том, чтобы на основе данных транзакций создать отчет о продажах оборудования

Рис. 12.9. Типичный запрос менеджера заключается в том, чтобы на основе данных транзакций создать отчет о продажах оборудования

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

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

Ключ для CS к эффективному выводу данных состоит в использовании сводной таблицы. Несмотря на то что подведение итогов по данным — это «конек» сводных таблиц, они все же не лишены недостатков, а уровень представления данных оставляет желать лучшего. Но все это устраняется с помощью соответствующего макроса.

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

	PT.AddFields RowFields:=Array("Категория оборудования", _
		"Регион"), ColumnFields:="Дата"

'Настройка полей данных
	With PT.PivotFields("floxofl")
		.Orientation - xlDataField
		.Function = xlSum
		.Position = 1
		.NumberFormat = "# ##0"
	End With

На рис. 12.10 показана сводная таблица, созданная с указанными выше настройками.

Рис. 12.10. Сводная таблица, созданная с заданными по умолчанию настройками, весьма далека от совершенства

Рис. 12.10. Сводная таблица, созданная с заданными по умолчанию настройками, весьма далека от совершенства

Ниже описаны некоторые недостатки, присущие большинству сводных таблиц по умолчанию.

  • Режим структуры очень неудобен. На рис. 12.10 значение Мини-пекарни появляется в столбце Категория оборудования только один раз, после чего выводится 6 пустых ячеек. Это наихудший недостаток сводных таблиц, причем его никак нельзя устранить. Несмотря на то что выведенный раздел содержит ряд данных о продажах мини-пекарен, будет весьма неприятно, если раздел Мини-пекарни растянется на две или три страницы. Страница 2 начинается без каких-либо упоминаний о том, что она содержит отчет о продаже мини-пекарен. Если вы намерены перенастроить данные, то выведите значения Мини-пекарни в каждой строке.
  • Поскольку в исходном наборе данных содержатся сведения о продажах по дням, созданная по умолчанию сводная таблица будет включать более тысячи столбцов. Вряд ли целесообразно анализировать подобный отчет. Следует сгруппировать данные о ежедневных продажах по годам, воспользовавшись предоставляемыми сводными таблицами возможностями.
  • Отчет содержит пустые ячейки вместо нулевых значений. На рис. 12.10 ниже заголовка Мини-пекарни отображается диапазон пустых значений. Вместо пустых значений следует отображать нули.
  • Названия нескольких столбцов и полей не всегда удовлетворяют запросам пользователей. Например, заголовок Сумма по полю Доход раздражает большинство пользователей.
  • Некоторые надписи лишние. Например, заголовок Дата, отображенный в первой строке (см. рис. 12.10), явно не относятся к самому отчету.
  • Алфавитный порядок сортировки не всегда приемлем. Так, менеджеры по продажам часто настаивают на том, чтобы в верхней части списка были помещены названия наиболее выгодных рынков сбыта. Лучше отсортировать отчет в порядке убывания объемов продаж.
  • Оформление отчета, выполненное с помощью границ, оставляет желать лучшего. Программа Excel создала столько границ, что отчет иногда трудно понять.
  • Сводные таблицы не имеют разумной логики использования разрывов страниц. Например, если требуется создать отчет для каждого менеджера по отдельности, не существует быстрого метода вывода данных о каждой единице оборудования на новой странице.
  • Проблема нелогичности вставки разрывов страниц вызывает острое желание отказаться от строк с промежуточными суммами сводных таблиц и прибегнуть к функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ (Subtotal) для добавления промежуточных итогов с собственными разрывами страниц. В таком случае отключите строки промежуточных сумм сводных таблиц, выводимые в поле Категория оборудования, как показано на рис. 12.10. Эти строки автоматически выводятся при наличии двух или большего количества полей строк. Если в таблице имеется четыре поля строк, можно отключить автоматические промежуточные суммы для трех из них.

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

•
Top