Содержание:
Срезы в сводных таблицах можно использовать не только классическим способом — для фильтрации исходных данных, но и для переключения между разными типами вычислений в области значений:
Реализовать это очень просто – достаточно пары формул и вспомогательной таблицы. Что ж, все это мы будем делать не в обычной сводке, а в сводке, построенной по модели данных Power Pivot.
Шаг 1. Подключение надстройки Power Pivot
Если вкладки надстройки Power Pivot не отображаются в Excel, сначала необходимо ее включить. Для этого есть два варианта:
- Tab разработчик - кнопка COM-надстройки (Разработчик — надстройки COM)
- Файл – Параметры – Надстройки – Надстройки COM – Перейти (Файл — Параметры — Надстройки — COM-надстройки — Перейти)
Если это не поможет, попробуйте перезапустить Microsoft Excel.
Шаг 2. Загрузите данные в модель данных Power Pivot.
В качестве исходных данных у нас будет две таблицы:
Первая — это таблица с продажами, по которой мы в дальнейшем будем строить сводку. Вторая — вспомогательная таблица, куда вносятся названия кнопок будущего среза.
Обе эти таблицы нужно преобразовать в «умные» (динамические) с помощью сочетания клавиш. Ctrl+T или команда Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу) и желательно на вкладке дать им вменяемые имена Конструктор (Дизайн). Пусть это будет, например, Продажа и Услуги.
После этого каждую таблицу по очереди нужно загрузить в Модель данных – для этого используем на вкладке PowerPivot кнопка Добавить в модель данных (Добавить в модель данных).
Вычисляемые поля в сводной таблице по модели данных называются меры. Давайте создадим меру, которая будет отображать имя нажатой кнопки на будущем срезе. Для этого в любой нашей таблице выделите любую пустую ячейку нижней панели вычислений и введите в строку формул следующую конструкцию:
Здесь на первом месте стоит название меры (Нажатая кнопка), а затем после двоеточия и знака равенства формула для ее расчета с помощью функции ЦЕННОСТИ: DAX встроен в Power Pivot.
Если повторить это не в Power Pivot, а в Power BI, то двоеточие не нужно и вместо него ЦЕННОСТИ: вы можете использовать ее более современный аналог – функцию ВЫБРАННОЕЗНАЧЕНИЕ.
На ошибки в нижней части окна, которые появляются после ввода формулы, мы не обращаем внимания — они возникают потому, что у нас еще нет сводки и среза, в котором что-то нажимается.
Следующим шагом будет создание меры для разных вариантов расчета в зависимости от значения предыдущей меры. Нажатая кнопка. Здесь формула немного сложнее:
Разберем по частям:
- Функция ВЫКЛЮЧАТЕЛЬ — аналог вложенного IF — проверяет выполнение заданных условий и возвращает разные значения в зависимости от выполнения некоторых из них.
- Функция ИСТИННЫЙ() – выдает логическую «истину», чтобы условия, проверяемые позже функцией ПЕРЕКЛЮЧАТЕЛЬ, работали только в том случае, если они выполнены, т.е. истина.
- Затем мы проверяем значение показателя нажатой кнопки и рассчитываем итоговый результат для трёх разных вариантов — как сумма стоимости, средний чек и количество уникальных пользователей. Для подсчета уникальных значений используйте функцию DISTINCTCOUNT, а для округления – КРУГЛЫЙ.
- Если ни одно из трех вышеперечисленных условий не выполнено, то выводится последний аргумент функции ПЕРЕКЛЮЧЕНИЕ — мы задаем его как пустышку с помощью функции ПУСТОЙ().
Шаг 5. Построение резюме и добавление среза
Осталось вернуться из Power Pivot в Excel и построить там сводную таблицу для всех наших данных и показателей. Для этого в окне Power Pivot на Основной команда выбора вкладки Таблица результатов (Главная страница — Сводная таблица).
Тогда:
- Мы бросаем поле Продукт Из таблицы Продажа в район Ряды (Ряды).
- Бросать поле туда Результат Из таблицы Услуги.
- Щелкните правой кнопкой мыши на поле Результати выбери команду Добавить как фрагмент (Добавить как срез).
- Бросаем вторую меру Заключение Из таблицы Услуги в район Наши ценности (Ценности).
Вот, собственно, и все хитрости. Теперь вы можете нажать на кнопки среза — и итоги в сводной таблице перейдут на нужную вам функцию.
Красота 🙂
- Преимущества сводной модели данных
- Анализ плана и факта в сводной таблице в Power Pivot
- Создайте базу данных в Excel с помощью надстройки Power Pivot.