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

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

ИНСТРУКЦИЯ ПО ДОБАВЛЕНИЮ ТЕКСТОВЫХ ПОЛЕЙ В СВОДНУЮ ТАБЛИЦУ

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

Шаг 1. Включаем надстройку Power Pivot.

Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:

Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.

Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.

Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.

Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.

Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.

Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).

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

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

Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).

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

Цвета:=

COUNTROWS(VALUES('Остатки'[Цвет])).

Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).

Функция COUNTROWS() подсчитывает в таблице количество строк.

К мере применяются фильтры сводной таблицы (учитываются ее разрезы).

Рассмотрим, как считает написанная мера.

В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).

Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.

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

Цвета:=

VALUES('Остатки'[Цвет]).

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

Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.