Сводная таблица с текстовыми полями в области значений
Данная статья содержит информацию о том, как с помощью надстройки 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). Одним из вариантов определения количества уникальных цветов будет мера, записанная следующим образом:
Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).
Функция COUNTROWS() подсчитывает в таблице количество строк.
К мере применяются фильтры сводной таблицы (учитываются ее разрезы).
В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).
Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.
Предположим, нами создана мера, которая выглядит следующим образом:
Мы получим сообщение об ошибке, так как в строках, где уникальных значений больше одного, результатом функции VALUES() также является более одного значения (рис 6).
Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.
Excel сводная таблица вычисляемое поле формулы • Вэб-шпаргалка для интернет предпринимателей!
После того, как вы подготовили данные для сводной таблицы, устанавливаем курсор в первый столбец в первую ячейку простой таблицы, далее заходим в меню «Вставка» и нажимаем кнопку «Сводная таблица»
О том, как записать формулу в вычисляемые поля сводной таблицы «Эксель» расскажу далее.
На основании таблицы, в которой изображены продажи товаров за несколько лет,
Таблица на листе в Excel
Сводная таблица в Эксель
Как строить сводные таблицы для бизнеса в Excel | Бизнес-школа LABA (ЛАБА)
Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, повторно выполняется команда Данные > Сводная таблица и она вызывает Мастера сводных таблиц, шаг 3.
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
В окне Список полей будут отражены названия всех столбцов исходной таблицы. Таким образом, поле — это просто столбец. Вычисляемое поле — это, по сути, вычисляемый столбец.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Как сделать сводную таблицу в Excel |
- Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
- В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
- Введите символ деления / в поле Формула
- В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
- Нажмите ОК
Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Например, формула вычисляемого поля =Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2, а не умножает каждое отдельное значение продаж на 1,2 с последующим суммированием полученных величин.
