Глава 26. Продвинутые вычисляемые столбцы в Power Pivot

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

Вычисляемые столбцы не являются сильной стороной DAX. Меры – это да! (поэтому почти вся книга посвящена им). А вот столбцы есть и в обычном Excel, поэтому, по большому счету, здесь мало что добавишь. На самом деле, вычисляемые столбцы в Power Pivot даже немного сложнее, чем обычные столбцы Excel, потому что Power Pivot не хватает ссылки в стиле "A1". А начнем мы с относительно простых трюков.

Ris. 26.1. Prodazhi v zavisimosti ot temperaturnogo diapazona

Рис. 26.1. Продажи в зависимости от температурного диапазона

Скачать заметку в формате Word или pdf, примеры в формате Excel

Группировка по значениям

Наш любимый пример группировки – объем продаж в зависимости от температуры. Мы импортируем таблицу данных о температуре (по дням), связываем ее с таблицей продаж, а затем выводим в сводную таблицу меру [Sales per Day], в зависимости от температурного диапазона (рис. 26.1). Вот формула, которую мы используем в вычисляемом столбце TempRange таблицы Temperature:

Ris. 26.2. Stolbets gruppirovki temperatury po diapazonam

Рис. 26.2. Столбец группировки температуры по диапазонам (значения градусов в столбце AvgTemp указано по шкале Фаренгейта; t°С = (tF – 32)/1,8; таким образом, холодно – ниже 4°С, прохладно = 4–10°С, тепло = 10–21°С, жарко – более 21°С)

Что не так с нашей сводной таблицей на рис. 26.1? Диапазоны отсортированы по алфавиту, а хотелось бы по возрастанию температур. Попробуем отсортировать столбец TempRange по значениям температуры в столбце AvgTemp. Для этого в Power Pivot перейдите в таблицу Temperature, выделите столбец TempRange, и кликните кнопку Сортировка по столбцам. В открывшемся окне выберите Сортировать по: AvgTemp.

Ris. 26.3. Sortirovka diapazonov stolbtsa TempRange po znacheniyam temperatury v stolbtse AvgTemp

Рис. 26.3. Сортировка диапазонов столбца TempRange по значениям температуры в столбце AvgTemp

Это приводит к ошибке:

Ris. 26.4. Dlya sortirovki kazhdomu znacheniyu v stolbtse TempRange dolzhno sootvetstvovat tolko odno znachenie v stolbtse AvgTemp

Рис. 26.4. Для сортировки каждому значению в столбце TempRange должно соответствовать только одно значение в столбце AvgTemp; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Ну что ж, добавим еще один столбце в таблицу Temperature с уникальным кодом диапазона:

Ris. 26.5. Kandidat na rol sortirovochnogo stolbtsa

Рис. 26.5. Кандидат на роль сортировочного столбца – TempRangeSeguence

Повторите сортировку (см. рис. 26.3). Но теперь установите Сортировать по: TempRangeSeguence. Получим желаемый результат:

Ris. 26.6. Teper diapazony vystroilis v pravilnom poryadke

Рис. 26.6. Теперь диапазоны выстроились в правильном порядке

Сортировка по вычисляемому ключу

Рассмотрим столбец QtrYearLabel в таблице Periods:

Ris. 26.7. Obratite vnimanie kak kazhdomu znacheniyu v stolbtse QtrYearLabel sootvetstvuet neskolko znachenij v stolbtse PeriodID

Рис. 26.7. Обратите внимание, как каждому значению в столбце QtrYearLabel соответствует несколько значений в столбце PeriodID

Если мы хотим «правильно» сортировать по столбцу QtrYearLabel, столбец PeriodID не подходит, и нам нужен новый вычисляемый столбец. Но на этот раз функция SWITCH() не спасет нас (поняли, почему?). Нам нужен какой-то шаблон для расчета уникальной последовательности кварталов. Например, такой: [Year] * 4 + [Qtr]. А в общем виде: <Year column> * <number of periods per year> + <period column>. Здесь периодом может быть квартал (4 за год), месяц (12), неделя (52), семестр (2) и т.д.

Ris. 26.8. QtrSequence unikalnyj stolbets identifikator dlya sortirovki stolbtsa QtrYearLabel

Рис. 26.8. QtrSequence – уникальный столбец-идентификатор для сортировки столбца QtrYearLabel

Суммирование в таблице поиска

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

=CALCULATE(SUM(Sales[SalesAmt]))

Ris. 26.9. Funktsiya CALCULATE prevrashhaet kontekst filtra v kontekst stroki

Рис. 26.9. Функция CALCULATE превращает контекст фильтра в контекст строки, и возвращает общий объем продаж для каждого продукта

Усложним задачу: добавим в таблицу Products столбец с объемом продаж, соответствующим общим продажам для категории, к которой принадлежит продукт.

Использование функции EARLIER()

Это сложная для понимания функция, но, чтобы начать ее использовать, достаточно познакомиться с шаблоном:

Благодаря этой формуле в столбце TotalCaregorySales в каждой строке выводится общий объем продаж всех продуктов соответствующей категории (рис. 26.10). Для аксессуаров он один, для велосипедов – другой, но для одной и той же категории – одинаков.

Ris. 26.10. Kazhdaya stroka v sootvetstvuyushhej kategorii vozvrashhaet summu prodazh vsej kategorii

Рис. 26.10. Каждая строка в соответствующей категории возвращает сумму продаж всей категории

Обратите внимание, что в формуле в строке 4 нам не нужно использовать снятие фильтра с помощью ALL(Products), как мы делали ранее. Дело в том, что ранее у нас была формула меры, которая наследует фильтры из сводной таблицы. А сейчас у нас формула вычисляемого столбца, для которой не существует исходного контекста фильтра (столбцы в Power Pivot не имеют контекста фильтра). Таким образом, нет необходимости очищать фильтр с помощью ALL().

Прежде чем разобраться в том, какое условие проверяется в строке 5, еще раз проговорим, как действует описанные выше формула. Функция CALCULATE суммирует продажи по всем строкам, отобранным функцией FILTER. Мы могли бы просто суммировать =SUM(Sales[SalesAmt]), но тогда в каждой строке таблицы Products было бы одно и то же число. Функция SUM() не знает контекста строки. А вот CALCULATE использует контекст строки, и возвращает сумму только по тому ProductKey, который представлен в строке.

Функция FILTER не использует контекст строки, т.е. любое условие внутри нее будет работать на всей таблице Products. При этом функция FILTER – итератор, т.е. она будет проходить таблицу Products строка за строкой. Функция EARLIER как бы выходит наружу этой вложенности (FILTER внутри CALCULATE), и использует контекст строки функции CALCULATE. Таким образом, внутри функции FILTER берется вся таблица Products, а затем строка за строкой отбираются те, для которых категория совпадает с той, что в строке, для которой рассчитывается вся сумма CALCULATE(SUM(…);FILTER(…)).

Проиллюстрируем картинками. CALCULATE, скажем, для строки с ProductKey=597, задает контекст строки Category = "Bikes":

Ris. 26.11. Ishodnyj kontekst stroki

Рис. 26.11. Исходный контекст строки

Вложенная внутрь функции CALCULATE функция FILTER (рис. 26.12) по мере прохождения по таблице Products оценивает условие для каждой строки и, если сравнение дает ИСТИНА, то включает эту строку в возвращаемую таблицу.

Ris. 26.12. Iterativnaya funktsiya FILTER prohodya po tablitse Products sozdaet novyj kontekst stroki

Рис. 26.12. Итеративная функция FILTER, проходя по таблице Products, проверяет условие

Например, когда FILTER находится в строке ProductKey=234 (рис. 26.12), условие в формуле в строке 5 сравнивает Products[Category] = "Clothing" (текущая строка функции-итератора FILTER), с контекстом строки для CALCULATE. Именно в этом назначение функции EARLIER() – выйти из контекста строки вложенной функции, и обратиться к контексту строки внешней функции.

Для строки ProductKey=234 контекст строки внутри и вне FILTER не совпадает, и строка не будет возвращена, чтобы принять участие в подсчете продаж велосипедов:

Ris. 26.13. Funktsiya EARLIER pomogaet nam perejti k vneshnemu kontekstu stroki

Рис. 26.13. Функция EARLIER помогает нам перейти к «внешнему» контексту строки, или, как говорят, к контексту строки, существующему на один уровень выше текущей вложенности

Если все это слишком сложно, не переживайте. Просто применяйте шаблон. Понимание придет с практикой.

Простейшее условие: table[column] = EARLIER(table[column]). Где знак равенства можно заменить на иной оператор сравнения: >, <, <=, >=, <>. Вы также можете использовать несколько условий, перечисленных через && (логическое И) или || (логическое ИЛИ).

Скользящее среднее

Подход, описанный выше, был использован нами в одном медико-биологическом исследовании дыхания крыс))

Обратите внимание на несколько условий, которые перечислены с использованием логического И – &&. Первое условие, как и в предыдущем примере – только строки для выбранной крысы должны быть подсчитаны, иначе мы учтем чье-то чужое дыхание. Второе и третье условия говорят «считайте только пять строк, до меня, и пять строк, сразу после меня». Т.е., отбираемый диапазон составляет 11 строк. В результате формула возвращает наименьшее значение в текущем «окне» из 11 строк. На основе этого другой вычисляемый столбец – MatchesMin – может определить, соответствует ли значение в столбце Value текущей строки минимуму из 11 строк.

Ris. 26.14. Nahozhdenie pikov

Рис. 26.14. Нахождение пиков

Ris. 26.15. Otrazhenie pikov v svodnoj tablitse

Рис. 26.15. Отражение пиков в сводной таблице

Дополнительные материалы на эту тему, см. http://ppvt.pro/PkSniff, http://ppvt.pro/Peak2Freq, http://ppvt.pro/FzzyTime.

Вычисляемые столбцы являются статическими

Вычисляемые столбцы являются статическими, значения вычисляются и сохраняются в столбце. Есть только два события, которые запускают вычисление / пересчет вычисляемого столбца:

  • Определение или переопределение: когда вы пишите (или редактируете) формулу для вычисляемого столбца и нажимаете Enter.
  • Обновление данных: при обновлении таблицы Power Pivot, содержащей вычисляемый столбец.

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

Давайте посмотрим, как изменяются числа в сводной (рис. 26.16):

  1. Вычисляемый столбец SalesPerProduct = CALCULATE(SUM(Sales[SalesAmt]))
  2. Мера [SalesPerProduct Measure]=CALCULATE(SUM(Sales[SalesAmt]))

Ris. 26.16. Shodstvo mezhdu vychislyaemymi stolbtsom i merami tolko poverhnostnoe

Рис. 26.16. Сходство между вычисляемыми столбцом и мерами только поверхностное

Фильтр в срезе не влияет на вычисляемый столбец, но изменяет значение меры.

Затраты ресурсов на вычисляемые столбцы

Вычисляемые столбцы «пожирают» большой объем оперативной памяти, и могут снижать скорость работы. Это может ощущаться при изменении координат / срезов в сводной таблице. Мы ранее обсудили это в главе о производительности.

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

Некоторые виды вычисляемых столбцов могут съедать очень много оперативной памяти. Вспомните наш пример нахождения пиков. Формула написана для выделения предыдущих и следующих пяти строк плюс текущей строки. Но чтобы найти эти 11 строк, Power Pivot каждый раз просматривает всю таблицу, по одной строке за раз, и решает, принадлежит ли каждая строка к этим одиннадцати.

В обычном Excel этой цели служит относительная ссылка. В Power Pivot нет ссылок в стиле А1, и с относительным позиционированием Power Pivot справляется хуже, чем Excel. Если в таблице миллион строк, то для расчета каждой из них в вычисляемом столбце потребуется миллион итераций. А это уже 1012 операций!


Прокомментировать