Переключение вычислений в сводной таблице срезами
Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
Реализовать такое очень легко - потребуется лишь пара формул и вспомогательная таблица. Ну, и делать всё это мы будем не в обычной сводной, а в сводной построенной по Модели Данных 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. Создаем меру для вычисления по нажатой кнопке
Следующий шаг - создать меру для различных вариантов вычислений в зависимости от значения предыдущей меры Нажатая кнопка. Здесь формула будет чуть посложнее:
Давайте разберём её по косточкам:
- Функция SWITCH - аналог вложенных друг в друга ЕСЛИ - проверяет выполнение заданных условий и возвращает разные значения в зависимости от выполнения некоторых из них.
- Функция TRUE() - выдаёт логическую "истину", чтобы проверяемые затем ниже функцией SWITCH условия срабатывали только в случае их выполнения, т.е. истинности.
- Затем мы проверяем значение меры Нажатая кнопка и вычисляем итоговый результат по трём разным вариантам - как сумму стоимости, средний чек и количество уникальных пользователей. Для подсчета уникальных значений используем функцию DISTINCTCOUNT, а для округления - ROUND.
- Если ни одно из перечисленных трёх условий не выполняется, то выводится последний аргумент функции SWITCH - его мы задаём как пустышку с помощью функции BLANK().
Шаг 5. Строим сводную и добавляем срез
Осталось вернуться из Power Pivot в Excel и построить там сводную таблицу по всем нашим данным и мерам. Для этого в окне Power Pivot на Главной вкладке выбираем команду Сводная таблица (Home - Pivot Table).
Затем:
- Закидываем поле Товар из таблицы Sales в область Строки (Rows).
- Закидываем туда же поле Результат из таблицы Service.
- Щёлкаем правой по полю Результат и выбираем команду Добавить как срез (Add as Slicer).
- Закидываем вторую меру Итог из таблицы Service в область Значений (Values).
Вот, собственно, и все хитрости. Теперь можно щёлкать по кнопкам среза - и итоги в сводной таблице будут переключаться на нужную вам функцию.
Красота :)
Ссылки по теме
- Преимущества сводной по Модели Данных
- План-факт анализ в сводной таблице на Power Pivot
- Создание базы данных в Excel с помощью надстройки Power Pivot
планируете ли Вы тренинги и статьи по работе с Power Automate и Power Apps?
если Сервис и Продажи писать не по-английски а по-русски то у меня возникли значительные сложности так как формулы давали ошибки.
в итоге решил вопрос но времени потратил много.
А вот если все сделать в инглише а потом переименовать таблицы в Пивоте в рус версию то тогда переход на рус проходит отлично.
если возможно то просьба писать все что тока можно по-русски.
в любом случае спасибо за все что делаете для малограмотного народа типа меня!
1. Мера [Нажатая кнопка], функция VALUES возвращает ошибку, если выбрано 2 и более значения, а пользователь легко может нажать в срезе "Сбросить фильтры" и будет неприятно удивлен ошибкой. В PowerBI действительно есть функция SELECTEDVALUE, которая исключает такую ошибку, только появилась она уже 2 года как и является краткой записью следующей конструкции:
2. Меру [Итог] можно также запись более элегантно:
А можно ли то же самое сделать не как меру, а как вычисляемый столбец?
Возможно ли проверять вхождение каждого варианта в этой таблице значений и в зависимости от этого делать вычисления?
Например с какой-то такой логикой:
Мера 1:= if ( "Значение1" in Values; sum("Столбец1"; 0) + if ( "Значение2" in Values; sum("Столбец2"; 0) + if ( "Значение3" in Values; sum("Столбец3"; 0)Т.е. не применять разные расчеты в зависимости от отбора. А суммировать различные столбцы в зависимости от выбранных значений в срезе.