Какая функция заложена в вычисляемый объект в сводных таблицах excel

Настройка вычислений в сводных таблицах

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum) , а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count) .

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

…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as) :

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

. то получим сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т.е. – динамика продаж:

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще — щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By) :

. и Дополнительные вычисления (Show Data as) :

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

% от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу:

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

Excel DAX – вычисляемые поля / показатели

Вычисляемое поле в таблице в модели данных – это поле, полученное по формуле DAX. В более ранних версиях Power Pivot вычисленное поле называлось мерой. В Excel 2013 оно было переименовано в вычисляемое поле. Тем не менее, он переименован обратно в измерение в Excel 2016. Если вы обращаетесь к какой-либо документации, вы можете наблюдать смешивание этих двух терминов. Обратите внимание, что термины вычисляемое поле и мера являются синонимами. В этом уроке мы используем термин вычисляемое поле.

Понимание вычисляемых полей

Вычисляемое поле – это формула, созданная специально для использования в сводной таблице (или сводной диаграмме).

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

Ниже приводится разница между вычисляемым полем и вычисляемым столбцом.

Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Сохранение рассчитанного поля

Рассчитанное поле будет сохранено вместе с исходной таблицей в модели данных. Он отображается в списке полей Power PivotTable или Power PivotChart как поле в таблице.

Использование вычисляемого поля

Чтобы использовать вычисляемое поле, вы должны выбрать его из списка Поля сводной таблицы. Вычисленное поле будет добавлено в область ЗНАЧЕНИЯ, и будет вычислена формула, используемая для вычисляемого поля. Результат создается для каждой комбинации полей строки и столбца.

Расчетное поле – пример

Рассмотрим следующую модель данных для данных Олимпийских игр –

Как видно на снимке экрана выше, в таблице «Результаты» есть поле «Медаль», в котором содержатся значения – золото, серебро или бронза для каждой строки, содержащей комбинацию «Спорт – Событие – Страна – Дата». Предположим, вы хотите подсчет медалей для каждой страны, затем вы можете создать вычисляемое поле Medal Count по следующей формуле DAX –

Создание вычисляемого поля в таблице

Чтобы создать вычисляемое поле Medal Count в таблице Results, сделайте следующее –

Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Как видно на скриншоте выше, вычисленное поле появляется в выбранной ячейке, показывая значение 34,094. Это число – общее количество строк в таблице результатов. Следовательно, это не имеет большого смысла на первый взгляд. Как обсуждалось ранее, реальное использование вычисляемого поля можно увидеть, только добавив его в Power PivotTable или Power PivotChart.

Использование вычисленного поля в сводной таблице Power

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

• Нажмите на сводную таблицу на ленте в окне Power Pivot.
• Нажмите на сводную таблицу в раскрывающемся списке.

Откроется диалоговое окно «Создать сводную таблицу».

• Нажмите на существующую таблицу.
• Выберите место, где вы хотите разместить сводную таблицу.

Будет создана пустая сводная таблица.

• Щелкните таблицу «Результаты» в списке «Поля сводной таблицы».
• Нажмите на поля – Страна и Количество медалей.

Как вы можете заметить, счетчик медалей добавляется в область ЗНАЧЕНИЙ, а страна – в область строк. Сводная таблица создается со значениями поля Страна, появляющимися в строках. И для каждой строки рассчитывается и отображается значение количества медалей. Таким образом, вычисляемое поле оценивает используемую формулу DAX и отображает значения.

• Добавьте поле Спорт из таблицы результатов в область ROWS.

Как вы можете видеть на скриншоте выше, количество медалей рассчитывается для каждой страны – в зависимости от вида спорта и промежуточного итога для самой страны.

Вот как DAX дополняет функции Power.

Типы вычисляемых полей

Существует два типа вычисляемых полей – неявные и явные.

Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

Создание неявного вычисляемого поля

Неявное вычисляемое поле может быть создано двумя способами, оба на панели Power PivotTable Fields.

Создание неявного вычисляемого поля в списке полей сводной таблицы

Вы можете создать поле Count of Medal из поля Medal в списке полей сводной таблицы следующим образом:

• Отмените выбор поля «Количество медалей».
• Щелкните правой кнопкой мыши на поле Медаль.
• Нажмите Add to Values ​​в раскрывающемся списке.

Количество медалей появляется в области значений. Столбец Количество медалей будет добавлен в сводную таблицу.

Создание неявного вычисляемого поля в области VALUES

Вы можете создать неявное рассчитанное поле -% родительской строки в области «Значения», чтобы выразить количество медалей по каждому виду спорта, выигранному страной, в процентах от общего числа медалей, выигранных этой страной.

• Нажмите стрелку вниз в поле Count of Medal в области VALUES.
• Нажмите Настройки поля значения в раскрывающемся списке.

Откроется диалоговое окно «Настройки поля значений».

• Введите% Медали в поле Пользовательское имя.
• Перейдите на вкладку «Показать значения как».
• Щелкните поле под Показать значения как.
• Нажмите% от общего количества родительских строк.

• Нажмите кнопку «Числовой формат».

Откроется диалоговое окно «Формат ячеек».

• Нажмите Процент.
• Введите 0 в десятичных разрядах.
• Нажмите ОК.
• Нажмите кнопку ОК в диалоговом окне «Параметры поля значений».
• Выберите Не показывать промежуточные итоги.

Вы создали еще одно неявное рассчитанное поле% Медали, и, как вы можете заметить, для каждой страны отображается процент медалей в зависимости от вида спорта.

Недостатки неявного вычисляемого поля

Неявные вычисляемые поля легко создавать. Фактически вы создавали их даже в сводных таблицах и сводных диаграммах Excel. Но у них есть следующие недостатки –

Они изменчивы . Это означает, что если вы отмените выбор поля, которое вы использовали для вычисляемого поля, оно будет удалено. Если вы хотите отобразить его снова, вы должны снова создать его.

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

Они изменчивы . Это означает, что если вы отмените выбор поля, которое вы использовали для вычисляемого поля, оно будет удалено. Если вы хотите отобразить его снова, вы должны снова создать его.

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

С другой стороны, явные вычисляемые поля будут сохранены вместе с таблицей и будут доступны всякий раз, когда вы выбираете эту таблицу.

Создание явного вычисляемого поля

Вы можете создать явное вычисляемое поле двумя способами:

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе – Создание вычисляемого поля в таблице.

Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе – Создание вычисляемого поля в таблице.

Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

Создание явного вычисляемого поля из ленты PowerPivot

Чтобы создать явное вычисляемое поле из ленты PowerPivot, выполните следующие действия:

• Откройте вкладку POWERPIVOT на ленте в своей книге.
• Нажмите кнопку «Рассчитанные поля» в области «Расчеты».
• Нажмите Новое вычисляемое поле в раскрывающемся списке.

Откроется диалоговое окно «Вычисленное поле».

• Заполните необходимую информацию, как показано на следующем снимке экрана.

• Нажмите кнопку Проверить формулу.
• Нажмите ОК, только если в формуле нет ошибок.

Как вы можете заметить, вы можете определить категорию и формат вычисляемого поля в этом диалоговом окне. Кроме того, вы можете использовать функцию IntelliSense, чтобы понять использование функций и использовать функцию автозаполнения, чтобы легко заполнять названия функций, таблиц и столбцов. Подробнее о функции IntelliSense см. Главу « Формулы DAX» .

Финансы в Excel

Обработка больших объемов данных. Часть 3. Сводные таблицы

Содержание
Использование сводных таблиц
Выборка уникальных значений
Суммирование значений
Двухмерный анализ
Многомерный анализ
Работа с данными
Обновление данных
Работа с результатами анализа
Версии интерфейса сводных таблиц
Внутренняя организация интерфейса сводных таблиц
Кэш сводной таблицы
Объекты VBA
Виды источников данных
Диапазоны
Запросы к базе данных
OLAP-кубы
PowerPivot

Вложения:

nwdata_cube.zip	[OLAP-куб]	57 kB
nwdata_pivot.xls	[Примеры сводных отчетов]	483 kB
nwdata_pivot.zip	[Версии сводных отчетов]	292 kB

Третья статья, посвященная обработке больших объемов данных с помощью Excel, описывает преимущества использования сводных таблиц. Вообще, эта статья должна была быть первой в цикле, если говорить о пользе того или иного метода работы. Действительно, интерфейс сводных таблиц специально создавался для анализа больших объемов данных, которые могут храниться не только в диапазонах электронных таблиц, но и во внешних источниках данных. Понимание принципов работы и практическое использование сводных таблиц позволяет существенно оптимизировать повседневную работу экономистов. Повышение уровня анализа данных, в свою очередь, ведет к улучшению управляемости компании и принятию верных управленческих решений менеджерами различных уровней.

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

Здесь остановимся подробнее на конкретных методах обработки данных при помощи интерфейса сводных таблиц. В качестве примера используйте файл nwdata_pivot.xls.

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

Выборка уникальных значений

Одной из самых популярных задач, решаемой при помощи сводной таблицы – это выборка уникальных значений из списка или массива данных. Использование интерфейса сводной таблицы позволяет решить эту задачу самым «элегантным» способом – без использования формул.

В примере на листе Выборка показан список стран и количество упоминаний в массиве данных.

В поле данных необходимо, чтобы стоял вид операции – «количество». Этот параметр позволяет обрабатывать в области данных сводной таблицы нечисловые поля исходных данных. Альтернативой операции подсчета количества служит стандартная функция COUNTIF. Сформировать набор уникальных значения только с помощью формул в принципе тоже возможно (см. часть 1), но это потребует очень сложных формул с вычисляемой адресацией. То есть, использование сводной таблицы в данной задаче – это самый оптимальный способ решения.

Суммирование значений

Другая популярная задача для применения интерфейса сводных таблиц – это получение итоговых значений по уникальным записям массива данных.

В примере на листе Сумма сформированы итоговые данные по заказам по каждой стране:

Вид операции «Сумма» в поле данных допускает использование только числовых полей. Прочие виды агрегации исходных данных на практике почти не используются.

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

Двухмерный анализ

Описанные ранее примеры демонстрируют анализ данных по одному критерию. Электронные таблицы позволяют наглядно представить данные в двух измерениях: по столбцам и по строкам. Сводные таблицы также имеют эти области отображения данных.

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

Суммирование по нескольким критериям допустимы и через стандартные функции Excel SUMIFS, SUMPRODUCT, а также функции обработки массивов (см. часть 1). Однако, такой вариант требует предварительно известные значения параметров — ключей выборки. Кроме того, расчет при помощи формул требует значительно больше времени, что на больших объемах данных может привести к большим потерям в производительности работы.

Многомерный анализ

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

Пример на листе Фильтр демонстрирует возможность просмотра данных по компаниям одной страны с использованием области фильтра сводной таблицы:

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

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

Примеры на листах pivot1 и pivot2 показывают варианты отображения одной и той же информации с использованием различных настроек измерений сводной таблицы.

Работа с данными

Обновление данных

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

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

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

Изменить диапазон-источник данных для существующей сводной таблицы можно через специальный диалог Excel 2007-2010. В предыдущих версиях Excel эта интерфейсная возможность реализована в «Мастере работы со сводными таблицами», в случае, когда он запущен из активной сводной таблицы. После открытия мастера необходимо вернуться на один шаг назад:

Исправления источника данных можно также произвести программным способом. Например, через окно вычислений редактора VBA (Immediate):

Чтобы не задумываться над корректностью размеров диапазона-источника данных сводной таблицы, можно изначально при построении задать диапазон строк с большим запасом. Например, зная, что предполагаемый объем строк не превышает 10000, можно сразу задать это значение в виде размера диапазона. Такая избыточность на практике не приведет к видимым замедлениям в работе интерфейса сводной таблицы. Пустые значения в измерениях отчета можно скрыть. Недостаток этого метода проявляется, в первую очередь, при работе с полями типа «дата». Стандартный интерфейс сводной таблицы позволяет реализовать различные группировки при работе с типом «дата» (по месяцам, по кварталам), но при наличии пустых значений эти возможности становятся недоступными, так как Excel определяет такой столбец как текстовый..

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

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

Макрос можно вызывать по событию Worksheet_Activate, либо настроить «горячую» клавишу.

Работа с результатами анализа

Сводная таблица располагается в диапазоне ячеек рабочего листа Excel. Написание формул рабочего листа в границах сводной таблицы не допускается как при вводе вручную, так и программными методами. Теоретически допустима работа с ячейками, располагающимися в пределах границ сводной таблицы, при помощи ссылок для внешних формул. Часто на практике используется также функция VLOOKUP для поиска по столбцу сводной таблицы. Этот способ необходимо использовать с большой осторожностью — интерфейс сводного отчета предполагает изменение положения отображаемых данных относительно прямоугольных координат рабочего листа без какого-либо влияния на источник этих данных. То есть, нет никакой гарантии, что указанная в формуле ссылка внутрь сводной таблицы будет отображать правильное значение при дальнейшей работе с файлом. При этом источник данных может не меняться.

Имеются альтернативные способы обработки результатов сводной таблицы:

1. Копирование и вставка значений сводной таблицы на другой лист (с использованием функции «Специальная вставка») с дальнейшим поиском дынных уже по этому сформированному диапазону ячеек. Нарушить целостность данных в пределах простой таблицы гораздо сложнее, чем в сводной. Очевидно, что главным недостатком этого способа работы, является использование ручных операций после каждого обновления источника данных.
2. Использовать возможности функции GETPIVOTDATA (Excel 2002 и более поздние версии). Данная функция предполагает доступ к данным не по координатам рабочего листа, а по измерениям сводной таблицы. Для источников данных типа OLAP-куб предусмотрены специальные функции доступа к данным и измерениям: CUBEVALUE, CUBEMEMBER и другие (Excel 2007-2010). Данный способ работы неудобен, а также существенно замедляет работу, если требуется получить много различных значений сводной таблицы.
3. Отказаться от сводной таблицы для получения результатов. Вместо этого использовать формулы рабочего листа (см. Часть 1). Этот способ, несмотря на сложность реализации, может оказаться самым удобным в том случае, если на результатах основываются другие вычисления, а источник данных часто обновляется.

Версии интерфейса сводных таблиц

В новом формате файла xlsx (Excel 2007-2010) существенно изменены возможности интерфейса сводных таблиц. В предыдущие версии интерфейса (97-2003) вносились только «косметические» изменения:

• Excel 2000 (9.0) – базовая версия интерфейса сводных таблиц.
• Excel XP (10.0) – новая функция GETPIVOTDATE
• Excel 2003 (11.0) – похоже, что вообще никаких изменений не вносилось
• Excel 2007 (12.0) – новая версия интерфейса сводных таблиц с поддержкой расширенных диапазонов. Улучшена производительность, изменен внешний вид интерфейса. Сохранена совместимость со старым форматом.
• Excel 2010 (14.0) – поддержка надстройки PowerPivot. Работа с обновляемыми OLAP-кубами.

Основные изменения в новом формате файла (2007-2010):

• В одном столбце могут располагаться несколько полей сводной таблицы, выделенных отступами (сжатая форма).
• Срезы сводной таблицы позволяют визуально отображать текущий фильтрующий набор значений.
• Измерения в области фильтра поддерживают множественный выбор.
• Элементы измерения могут быть скрыты/отображены через кнопки, расположенные в той же ячейке, что и сам заголовок.
• Появилось несколько новых параметров в свойствах поля и таблицы.
• Доступны стили сводных таблиц, позволяющие изменить внешний вид отчетов в любой момент времени.

Для лучшего понимания отличий скачайте и откройте файлы-примеров nwdata_pivot1.xlsx и nwdata_pivot2.xlsx (в арихиве nwdata_pivot.zip). В первом файле представлен отчет в старом формате, во втором – в новом, исходные данные одинаковые.

Внутренняя организация интерфейса сводных таблиц

Для лучшего понимания принципов работы сводной таблицы рассмотрим внутреннюю организацию интерфейса.

Кэш сводной таблицы

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

Данные в кэше обновляются при нажатии кнопки «Обновить» интерфейса сводной таблицы (кнопка на ленте или в контекстном меню), либо по заданному интервалу времени, если такая установка задана в параметрах. Режим вычислений Excel (автоматический или ручной) при этом никак не влияет на сводную таблицу.

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

Объекты VBA

Доступ к данным программными методами возможен на уровне объектов сводной таблицы — объект PivotTable. Другие объекты сводной таблицы отвечают за расположение и визуальное отображение элементов и данных. К ним относятся коллекции полей: PivotFields, ColumnFields, RowFields, PageFields, DataFields. Варианты значений полей доступны через коллекции объектов PivotItems.

Универсальная возможность обращения к данным непосредственно в кэш (объект PivotCache) почему-то не предусмотрена разработчиками Excel. Логика при этом не совсем понятна. Как уже отмечалось, данные кэша хранятся отдельно и их даже можно увидеть в файле формата xlsx, если открыть этот файл как zip-архив. В зависимости от типа источника данных можно попытаться использовать свойство SourceData (для сводных таблиц на основе диапазона) или Recordset (для источников типа «запрос к базе данных»).

Вычисляемые поля и объекты сводной таблицы (CalculatedFields, CalculatedItems) имеют собственный механизм расчетов и дерево зависимостей формул, не относящееся к формулам рабочего листа Excel. На практике мы рекомендуем по возможности избегать большого количества вычисляемых полей в сводных таблицах, так как это приводит к существенному замедлению расчетов. Для источников данных в виде диапазонов ячеек часто можно просто добавить столбец с обычной формулой в исходные данные, а для запросов к базам данных — добавить вычисления непосредственно в текст SQL-запроса.

Виды источников данных

Глобально можно разделить источники данных на 3 типа:

1. Диапазоны ячеек
2. Запросы к базе данных
3. OLAP-кубы и PowerPivot2010 как один из вариантов реализации OLAP-механизма.

Диапазоны

Первый вариант работы – самый распространенный на практике; предыдущие описания примеров относятся как раз к данным, хранящимся в диапазоне ячеек.

Стандартный интерфейс Excel не позволяет строить сводный отчет на основе нескольких диапазонов ячеек. Причина такого ограничения не очень понятна. Есть подозрение, что разработчики просто не могут предложить интуитивно-понятный интерфейс пользователя для решения данной задачи. Техническая реализация задачи не выглядит слишком сложной – требуется просто заполнить кэш данных. В разделе Надстройки нашего сайта представлено наше собственное решение для построения сложных сводных отчетов.

Запросы к базе данных

Запросы к базе данных могут быть реализованы с использованием различных технических механизмов: Microsoft Query, ADO, ODBC. Независимо от интерфейса доступа к данным объединяющим фактором этого варианта работы является заполнение кэша сводной таблицы непосредственно из внешнего источника. При дальнейшей работе со сводной таблицей запрос может быть выполнен повторно, после чего данные будут заново перенесены в кэш. Этот метод позволяет анализировать данные из внешних источников (учетных систем) в реальном времени. При разрыве связи с источником данных, анализ может производиться на последних данных, попавших в кэш.

OLAP-кубы

OLAP-куб предоставляет промежуточный уровень подготовки информации для многомерного анализа в сводных таблицах. Куб хранит информацию о доступных типах полей (измерение или данные), иерархические зависимости полей, агрегированные значения (промежуточные итоги) и другие вычисляемые элементы. Главным преимуществом использования кубов перед прямыми запросами в базу данных является высокая производительность, так как данные перемещаются и агрегируются в промежуточном хранилище. Очевиден и недостаток данного метода – данные OLAP-куба могут содержать неактуальную информацию, что зависит от настроек хранилища.

До версии Office 2007 простой OLAP-куб можно было подготовить при помощи Microsoft Query, но в последних версиях эту возможность по непонятным причинам отключили. Разработчики настоятельно рекомендуют использовать SQL Server Analysis Service для создания и настройки OLAP-кубов. Рекомендация полезная, но, во-первых, этот сервис входит в состав только платных версий SQL Server, а, во-вторых, требует серьезного изучения, как интерфейса, так и языка обработки MDX-запросов.

В примере к статье представлен архив nwdata_cube.zip с двумя файлами nwdata_cube.cub, nwdata_cube.xls. Обратите внимание на изменения в интерфейсе сводной таблицы при использовании OLAP-куба в качестве источника данных:

• Наличие иерархических измерений, нет возможности поменять родительский и дочерний элемент местами.
• Недопустимо перемещение измерений в область данных и наоборот.
• Промежуточные итоги отображаются для всех элементов, а не по текущему фильтру группы.

PowerPivot

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

С высокой вероятностью эта надстройка войдет в состав следующей версии Excel в качестве базовой функциональности. Мы очень надеемся посвятить описанию работы PowerPivot отдельную статью или даже цикл статей. На сегодняшний день PowerPivot + Excel являются, пожалуй, самым мощным инструментом для анализа больших объемов данных.