Переключение вычислений в сводной таблице срезами


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

Переключение вычислений в сводной срезами

Реализовать такое очень легко - потребуется лишь пара формул и вспомогательная таблица. Ну, и делать всё это мы будем не в обычной сводной, а в сводной построенной по Модели Данных Power Pivot.

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

Если вкладки надстройки Power Pivot не видно в вашем Excel, то сначала придётся её подключить. Для этого есть два варианта:

  • Вкладка Разработчик - кнопка Надстройки COM (Developer - COM Add-ins)
  • Файл - Параметры - Надстройки - Надстройки COM - Перейти (File - Options - Add-ins - COM-Add-ins - Go to)

Если это не поможет, то попробуйте перезапустить Microsoft Excel.

Шаг 2. Загружаем данные в Модель Данных Power Pivot

В качестве исходных данных у нас будут две таблицы:

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

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

Обе эти таблицы нужно конвертировать в "умные" (динамические) сочетанием клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table) и желательно дать им вменяемые имена на вкладке Конструктор (Design). Пусть это будут, например, Sales и Service.

После этого каждую таблицу по очереди нужно загрузить в Модель Данных - для этого используем на вкладке Power Pivot кнопку Добавить в модель данных (Add to Data Model).

Шаг 3. Создаем меру для определения нажатой на срезе кнопки

Вычисляемые поля в сводной таблице по Модели Данных называют мерами (measure). Создадим меру, которая будет выдавать название нажатой кнопки на будущем срезе. Для этого в любой из наших таблиц выделим любую пустую ячейку в нижней панели вычислений и введём в строку формул следующую конструкцию:

Первая мера для нажатой кнопки

Здесь сначала идёт имя меры (Нажатая кнопка), а затем после двоеточия и знака равно - формула, для её расчёта, использующая функцию VALUES встроенного в Power Pivot языка DAX.

Если вы будете повторять подобное не в Power Pivot, а в Power BI, то двоеточие не нужно и вместо VALUES можно использовать её более современный аналог - функцию SELECTEDVALUE.

На ошибки в нижней части окна, появляющиеся после ввода формулы, внимание пока не обращаем - они возникают, т.к. у нас нет пока сводной и среза, в котором что-то нажато.

Шаг 4. Создаем меру для вычисления по нажатой кнопке

Следующий шаг - создать меру для различных вариантов вычислений в зависимости от значения предыдущей меры Нажатая кнопка. Здесь формула будет чуть посложнее:

Вторая мера

Давайте разберём её по косточкам:

  1. Функция SWITCH - аналог вложенных друг в друга ЕСЛИ - проверяет выполнение заданных условий и возвращает разные значения в зависимости от выполнения некоторых из них.
  2. Функция TRUE() - выдаёт логическую "истину", чтобы проверяемые затем ниже функцией SWITCH условия срабатывали только в случае их выполнения, т.е. истинности.
  3. Затем мы проверяем значение меры Нажатая кнопка и вычисляем итоговый результат по трём разным вариантам - как сумму стоимости, средний чек и количество уникальных пользователей. Для подсчета уникальных значений используем функцию DISTINCTCOUNT, а для округления - ROUND.
  4. Если ни одно из перечисленных трёх условий не выполняется, то выводится последний аргумент функции SWITCH - его мы задаём как пустышку с помощью функции BLANK().

Шаг 5. Строим сводную и добавляем срез

Осталось вернуться из Power Pivot в Excel и построить там сводную таблицу по всем нашим данным и мерам. Для этого в окне Power Pivot на Главной вкладке выбираем команду Сводная таблица (Home - Pivot Table).

Затем:

  1. Закидываем поле Товар из таблицы Sales в область Строки (Rows).
  2. Закидываем туда же поле Результат из таблицы Service.
  3. Щёлкаем правой по полю Результат и выбираем команду Добавить как срез (Add as Slicer).
  4. Закидываем вторую меру Итог из таблицы Service в область Значений (Values).

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

Красота :)

Ссылки по теме

 


20.04.2022 20:24:16
Николай,
планируете ли Вы тренинги и статьи по работе с Power Automate и Power Apps?
30.04.2022 15:09:20
Николай большое спасибо за урок!
если Сервис и Продажи писать не по-английски а по-русски то у меня возникли значительные сложности так как формулы давали ошибки.
в итоге решил вопрос но времени потратил много.
А вот если все сделать в инглише а потом переименовать таблицы в Пивоте в рус версию то тогда переход на рус проходит отлично.
если возможно то просьба писать все что тока можно по-русски.
в любом случае спасибо за все что делаете для малограмотного народа типа меня!
Наверх