Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

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

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

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

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

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

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

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

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

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

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

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

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

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

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

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

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

Как Добавить Фильтр в Сводную Таблицу Эксель • Excel works

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

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Именно благодаря своим широким возможностям Сводные таблицы просто незаменимый инструмент для людей, работающих с большим количеством информации, которую периодически нужно структурировать и анализировать. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В этом случае встаем в любую из ячеек сводной таблицы (в панели вкладок появится блок Работа со сводными таблицами) и далее выбираем Анализ -> Данные -> Обновить:
Окно со вкладками PowerPivot

Сортировка и фильтрация данных в Excel

Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул:
=СРЗНАЧЕСЛИ(C2:C27;”Женский”;B2:B27)
=СРЗНАЧЕСЛИ(C2:C27;”Мужской”;B2:B27)

Создание сводной таблицы в Excel

Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:

В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.

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

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

Нажимаем OK и перед нами появляется следующий конструктор:

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

В правой части мы видим следующие элементы (список полей и области):

  • Список полей;
    Список всех заголовков столбцов исходной таблицы с данными.
  • Фильтры;
    Добавление дополнительного среза для детализации данных.
  • Строки;
    Поля таблицы вынесенные в строки.
  • Столбцы;
    Поля таблицы вынесенные в столбцы;
  • Значения.
    Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

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

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

Как сделать сводную таблицу в Excel?.

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: