Сводная таблица с текстовыми полями в области значений
Данная статья содержит информацию о том, как с помощью надстройки 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?
Начнем с основ. Для того, чтобы создать выпадающий список потребуется список с данными и инструмент «Проверка данных».
Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.
Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.
[expert_bq id=»1570″]Чтобы структурировать информацию, необходимо нажать на треугольник с выпадающим списком возле заголовка и выбрать соответствующий пункт. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Конструкции могут оформляться с формулами, которые позволяют создавать конструкцию с автоподсчетом данных. Чтобы сумма считалась автоматически необходимо выбрать ячейку, в которой будет отображаться результат, ввести в нее «сумм», после чего поставить скобку и выделить участок, который необходимо суммировать, заем поставить вторую скобку и нажать Enter.
Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами
Одним из полезных свойств умной таблицы является растягивающийся диапазон. То есть, если мы будем добавлять новые строки, они автоматически будут попадать в выпадающий список. Например, добавим новый город – Санкт-Петербург. И вот, он уже появился в нашем первом выпадающем списке.
Перемещение столбцов
Не предусмотрена возможность вставлять строки или столбцы с вкладки ленты Конструктор. Это можно сделать пройдя по меню Главная –> Ячейки –> Вставить:
Рис. 3.8. Вставка столбца или строки с вкладки Главная
Как правило, удобнее щелкнуть правой кнопкой мыши нужное место в таблице, чтобы открыть контекстное меню:
Рис. 3.9. Вставка столбца или строки из контекстного меню правой кнопкой мыши
Если Excel не может вставить строки или столбцы из-за структуры данных на листе (например, одна или несколько таблиц ниже выходят за пределы левого или правого края расширяющейся таблицы), Excel отменяет вставку и отображает сообщение об ошибке.
[expert_bq id=»1570″]Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Автоматизировать данный процесс при построении имени также можно посредством функции:Как в Excel сделать выпадающий список: пошаговая инструкция
- Используйте небольшой значок, расположенный в правом нижнем углу таблицы (рис. 3.4). Перетащив маркер, можно изменить число строк или число столбцов Таблицы. Но не одновременно. Чтобы изменить число строк и число столбцов, используйте маркер дважды.
- Или активируйте вкладку Конструктор и кликните на кнопке Размер таблицы. Excel отобразит диалоговое окно Изменение размера таблицы (рис. 3.5). Введите диапазон с клавиатуры или выберите мышкой на листе и нажмите кнопку ОК. Можно выбрать любой диапазон, если выбрана хотя бы одна ячейка заголовка и одна ячейка тела. Новый диапазон должен перекрывать текущий диапазон таблицы.
При дальнейшей работе вспомогательный столбец F можно скрыть. Минус такого метода – отсутствие динамичности. Если мы добавим новый город и адрес, то они не появятся в созданных выпадающих списках. Но это решаемо!
