Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как применить один и тот же фильтр к нескольким сводным таблицам?

Exceltip

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

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

В Excel можно определить несколько фильтров для одного и того же поля рабочего листа.

Устанавливаем фильтр по подписи

В нашем примере в сводной таблице представлены данные за разные месяцы с 2008 по 2013 год. Чтобы сравнить данные за первые шесть месяцев каждого года, необходимо определить фильтр подписи. Для этого нажмите на значок справа от поля Месяц и выберите в выпадающем меню Фильтры подписи -> Меньше….

фильтр по подписям сводной таблицы

В появившемся диалоговом окне Фильтры по подписи укажите настройку фильтра (в нашем случае — 7).

фильтр по подписям диалоговое окно

Предположим, что теперь вы хотите увидеть месяцы с наибольшим значением, вам нужно применить фильтр значений… к тому же полю. Снова нажмите на значок справа от поля Месяц, выберите в выпадающем меню Фильтры значений -> Топ 10….

фильтр по значениям сводной таблицы

В появившемся диалоговом окне фильтра «Top 10» задайте отображение первых трех наибольших значений.

фильтр по значениям диалоговое окно

В сводной таблице нет фильтра подписей, который показывает первые три месяца с наибольшим значением. Графики за месяцы 2, 6 и 9 можно увидеть ниже.

удалене фильтра по подписям

Меняем параметры фильтров сводной таблицы

Параметры сводной таблицы необходимо изменить, если вы хотите применить несколько фильтров к одному полю. Щелкните правой кнопкой мыши меню сводной таблицы и выберите Настройки сводной таблицы. Затем перейдите на вкладку Сводка и фильтры и установите флажок Разрешить несколько фильтров для поля.

параметры сводной таблицы

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

Читайте так же:
Как преобразовать текст в текстовом поле в содержимое ячейки в Excel?

Функции сводных таблиц в DAX: GROUPBY и SUMMARIZECOLUMNS в Power BI и Power Pivot

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы продолжим изучать DAX функции, создающие в Power BI и Power Pivot сводные таблицы, а конкретно, это GROUPBY и SUMMARIZECOLUMNS.

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

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция GROUPBY в Power BI и Power Pivot

GROUPBY () — создает сводную таблицу, сгруппированную по указанным столбцам (название столбцов конфигурируется из названия самой исходной таблицы и названия исходного столбца для группировки).

Также, часто я встречаю раздельное написание этой функции, как DAX GROUP BY, что неправильно…

  • ‘Таблица’ — исходная существующая таблица или табличное выражение, значения которых мы хотим сгруппировать
  • [Столбец] — столбец для группировки
  • «Имя столбца» — имя создаваемого столбца для значений группировки
  • Выражение — вычисляемое выражение для значений группировки

Выражение обязательно должно содержать статистическую DAX функцию формата X (SUMX, MAXX, AVERAGEX…), во входных параметрах которой, в качестве таблицы подставляется служебное выражение CURRENTGROUP ().

Читайте так же:
Как преобразовать разницу во времени между двумя значениями времени в число (часы / минуты / секунды) в Excel?

Рассмотрим все параметры GROUPBY, включая служебное выражение CURRENTGROUP, на примере формулы.

Создайте вычисляемую таблицу с использованием GROUPBY в модели данных для анализа примера.

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

Итак, в Power BI Desktop имеется исходная таблица «Продажи Менеджеров»:

Исходная таблица

Создадим во вкладке «Моделирование» вычисляемую таблицу на основе формулы с участием DAX функции GROUPBY:

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

( Во второй строке параметров) мы записали столбцы под названием [Отдел] и [Менеджер]. В результате все значения группируются следующим образом.

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

S UMX вложен в пятый параметр синтаксиса GROUPBY, и на его основе будет рассчитана совокупная сумма всех продаж по группам. В качестве входной таблицы в SUMX указывается CURRENTGROUP.

Таким образом, результатом выполнения формулы на основе функции DAX GROUPBY станет следующая сводная таблица:

Результат выполнения формулы на основе DAX функции GROUPBY

В этой созданной таблице мы можем собрать все данные по продажам для каждого менеджера в каждом отделе организации, используя GROUPBY.

В следующем примере мы рассмотрим другую функцию сводной таблицы.

DAX функция SUMMARIZECOLUMNS в Power BI и Power Pivot

SUMMARIZECOLUMNS () — очень похожая DAX функция на GROUPBY, а тем более на SUMMARIZE. Она также создает сводную таблицу, но, в данном случае, с возможностью фильтрации группируемых столбцов.

Синтаксис функции SUMMARIZECOLUMNS очень похож на синтаксис GROUPBY, который мы обсуждали выше, за исключением того, что

  • в первом параметре не нужно прописывать исходную таблицу;
  • в выражении мы можем использовать не только X функции, но и любые другие функции агрегирования
  • здесь появился еще один параметр — фильтр, по которому производится фильтрация столбцов для группировки
Читайте так же:
Как раскрасить диаграмму на основе цвета ячейки в Excel?

Рассмотрим пример формулы на основе DAX функции SUMMARIZECOLUMNS. Как и выше, пример мы будем рассматривать в Power BI на основе все той же исходной таблицы «Продажи Менеджеров»:

Исходная таблица

Создадим в Power BI Desktop во вкладке «Моделирование» новую вычисляемую таблицу и пропишем там следующую формулу с участием функции SUMMARIZECOLUMNS:

Чтобы сгруппировать значения, мы прописали столбцы [Отдел] и [Менеджер].

Во второй строке прописали фильтр, созданный на основе DAX функции FILTER. Данная функция фильтрует исходную таблицу «Продажи Менеджеров» по столбцу [Менеджер], где его значения не должны быть равны значению «Петров».

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

В четвертой строке само выражение агрегации основано на функции SUM, которая суммирует все продажи по категории группировки.

Запустив приведенную выше формулу в DAX с помощью функции SUMMARIZECOLUMNS, мы получаем следующую сводную таблицу:

Результат выполнения формулы на основе работы DAX функции SUMMARIZECOLUMNS

Как мы видим, была создана сводная таблица для менеджеров по продажам, но без менеджера Петрова, так как мы отфильтровали его в формуле SUMMARIZECOLUMNS.

Вот и все о функциях DAX для создания поворотных таблиц в Power BI и Power Pivot.

Пожалуйста, обратите внимание на статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функция GENERATESERIES

Таблица последовательных значений — DAX функция GENERATESERIES в Power BI и Power Pivot

Читайте так же:
Как разбить ось диаграммы в Excel?

DAX функция EARLIER в Power BI

DAX функция EARLIER в Power BI и Power Pivot

DAX функции DIVIDE, QUOTIENT и MOD в Power BI

Функции деления в DAX: DIVIDE, QUOTIENT и MOD для Power BI и Power Pivot

Ручная фильтрация

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

Ручная фильтрация

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

Нажмите на стрелку Стрелка внизв ячейке Метки столбцов.

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

Поисковая строка

Снимите галочку (Выбрать все) в верхней части списка значений.

Флажки значений, которые вы хотите видеть в сводной таблице, в данном случае февраль, должны быть отмечены.

(«Выбрать все») в верхней части списка значений должна быть снята.

Поставьте галочку напротив значений, которые вы хотите отобразить в сводной таблице, в данном случае Февраль, и нажмите OK.

Флажок

Сводная таблица отображает только те значения, которые относятся к выбранному значению поля Месяц — февраль. Вы можете видеть, что стрелка фильтрации меняется на значок Фильтр поискачтобы указать, что фильтр применяется. Поместите курсор на Фильтр поисказначок.

Значение поля месяца

Вы можете наблюдать за тем, что отображается, указывая на применение ручного фильтра в поле Месяц (Month).

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

Нажмите на Фильтр поисказначок.

Установите / снимите флажки значений.

Нажмите на Фильтр поисказначок.

Включает/выключает флажки для значений.

Значения полей могут появиться в списке не все, если не перетащить маркер в правом нижнем углу выпадающего списка. Если вы знаете значение, его можно ввести в поле поиска.

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

Читайте так же:
Как применить заморозить / разморозить панели сразу к нескольким листам?

Нажмите на стрелку Стрелка внизв ячейке Ярлыки строк.

Нажмите на стрелку Стрелка внизв ячейке Ярлыки строк.

Row Labels Cell

Список значений поля — Регион отображается. Это потому, что Регион находится на внешнем уровне Продавца в порядке размещения. У вас также есть дополнительная опция — Выбрать поле. Нажмите на поле «Выбрать поле».

Нажмите Продавец в раскрывающемся списке. Список значений поля — продавец будет отображаться.

Выберите все и снимите флажок (выбрать все) с поля, соответствующего Уолтерсу, Крису.

Нажмите Продавец в раскрывающемся списке. Список значений поля — продавец будет отображаться.

Снимите флажок (Выбрать все) и отметьте Уолтерс, Крис.

Проверьте Уолтерс

Сводная таблица отображает только те значения, которые связаны с выбранным значением поля Месяц — февраль и значение поля Продавец — Уолтерс, Крис.

Стрелка фильтрации для Метки строк также меняется на значок Фильтр поискачтобы указать, что фильтр применяется. Поместите курсор на Фильтр поисказначок на метках строк или метках столбцов.

Метки колонки

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

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

Размер имеет значение

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

Это «Как» помогло вам, или мы что-то пропустили? Мы хотели бы услышать ваш опыт сводной таблицы ниже!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector