Использование функций работы со временем
В DAX существует набор функций работы со временем. Для использования этих функций требуется специальная таблица, включающая даты (календарная таблица). Любая таблица может включать поле даты. Дата, отображаемая в исходном наборе данных, будет появляться в единственной строке таблицы дат. Если нужны дополнительные столбцы, идентифицирующие дату как относящуюся к определенному году, месяцу или дню недели, добавьте эти столбцы в календарную таблицу. Существует множество способов формирования подобных таблиц, но пользователи Excel обычно создают их в среде Excel:
1. Перейдите к исходному набору данных. Выберите столбец, включающий поле даты, и скопируйте его.
2. Перейдите к пустому листу и вставьте даты в столбец А.
3. Выделив столбец А, выполните команду Данные g Удалить дубликаты и щелкните на кнопке ОК.
4. При необходимости добавьте дополнительные столбцы, например, Год, Номер дня недели, День недели, Номер месяца и Месяц (рис. 36). Формулы, показанные в верхней части рисунка, демонстрируют, каким образом вычисляется каждый столбец.
5. Преобразуйте диапазон данных в таблицу, нажав комбинацию клавиш Ctrl+T.
6. С помощью контекстной вкладки Конструктор, относящейся к набору контекстных вкладок Работа с таблицами, выберите для создаваемой таблицы название Календарь.
Рис. 36. Исходные данные для создания календарной таблицы в Excel
Начиная с этого раздела и до конца заметки используются данные из Excel-файла Пример календарь.xlsx.
Добавление данных в таблицу PowerPivot и выполнение форматирования.В этом заключительном примере главы будет рассмотрено несколько дополнительных действий, направленных на улучшение заключительного отчета. Начнем с добавления таблиц Продажи и Календарь в PowerPivot:
1. Выделите ячейку в таблице Продажи.
2. На вкладке PowerPivot в Excel щелкните на значке Добавить в модель данных.
3. Выберите ячейку в таблице Календарь.
4. На вкладке PowerPivot в Excel щелкните на значке Добавить в модель данных.
5. На вкладке PowerPivot в Excel щелкните на значке Управление; перейдите в окно PowerPivot.
Теперь выполним форматирование этих двух таблиц в окне PowerPivot:
1. В таблице Продажи выберите столбец Дата.
2. В окне PowerPivot выберите вкладку В начало. Обратите внимание на группу Форматирование. По умолчанию уже выбран правильный формат Дата, но само форматирование даты некорректно. Раскройте список Формат и выберите значение *14.03.2001. (Это не ошибка; именно так выглядит пункт раскрывающегося списка.)
3. Выберите столбец Продажи в таблице Продажи.
4. На вкладке В начало выберите формат Валюта. Чтобы устранить знаки после запятой, дважды щелкните на значке Уменьшить число десятичных разрядов.
5. Снова выберите столбец Дата в таблице Продажи.
6. На вкладке Конструктор щелкните на значке Создание связи.
7. В диалоговом окне Создание связи первые два поля заполнены значениями Продажи и Дата. Раскройте список Связанная таблица подстановки и выберите пункт Календарь. В раскрывающемся списке Связанный столбец подстановки автоматически появится значение Дата (рис. 37). Щелкните ОК для создания связи.
8. Выберите рабочий лист Календарь в окне PowerPivot.
9. Выделите столбец Дата.
10. Перейдите на вкладку В Начало и измените формат даты на *14.03.2001.
11. Выделив столбец Дата, перейдите на вкладку Конструктор. Раскройте список Пометить как таблицу дат и выберите пункт Пометить как таблицу дат. На экране появится диалоговое окно Пометить как таблицу данных. В этом окне уже выбрано корректное поле Дата. Щелкните на кнопке ОК. Данное действие нужно для создания фильтров по дате в списке полей сводной таблицы. Результат показан на рис. 39.
Рис. 37. Создание связи между таблицами Продажи и Календарь
В PowerPivot не поддерживается автоматическая сортировка по пользовательским спискам.Я большой поклонник PowerPivot и пишу книги соответствующей тематики с 2009 года. И в первой книге по PowerPivot я несколько раз упоминал о том, что в PowerPivot не выполняется автоматическая сортировка по названиям месяцев (в последовательности «январь, февраль, март...»). В сводных таблицах PowerPivot названия месяцев сортируются по алфавиту (апрель, август, июль...), и эта проблема не устранена даже в версии PowerPivot for Excel 2013.
В обычных сводных таблицах, использующих стандартный кеш, выполняется автоматическая сортировка всех полей на основе пользовательских списков. Но, увы, эта сортировка недоступна для сводных таблиц PowerPivot. Чтобы преодолеть это:
1. В окне PowerPivot выберите таблицу Календарь.
2. Выделите одну из ячеек в столбце День недели.
3. На вкладке В начало в окне PowerPivot в группе Сортировка и фильтрация щелкните на значке Сортировка по столбцам.
4. В диалоговом окне Сортировка по столбцу выберите сортировку дня недели по номеру дня недели (рис. 38). Щелкните ОК.
5. Выделите ячейку в столбце Месяц. Повторите пп. 3 и 4, но на сей раз выберите сортировку столбца Месяц по номеру месяца.
Рис. 38. Сортировка по дням недели
Создание сводной таблицы с расширенными возможностями.В окне PowerPivot выберите таблицу Продажи. На вкладке В начало раскройте список Сводная таблица и выберите пункт Сводная таблица. Вы вернетесь в Excel и в списке полей сводной таблицы отобразятся поля таблицы Календарь и таблицы Продажи. Установите указатель мыши над полем Дата и раскройте список. Выберите пункт Фильтры по дате, позволяющий получить доступ ко всем фильтрам по дате, которые используются в обычных сводных таблицах (рис. 39).
Рис. 39. Благодаря тому, что таблица Календарь была объявлена как таблица дат, появилась возможность получать доступ ко всем фильтрам по дате
Выполните следующие действия, чтобы добавить поля в сводную таблицу:
1. Перетащите поле День недели из таблицы Календарь в область КОЛОННЫ.
2. Перетащите поле Продажи из таблицы Продажи в область ЗНАЧЕНИЯ.
3. Выберите контекстную вкладку Анализ и щелкните на значке Вставить срез.
4. Добавьте срез для поля День недели.
5. На контекстной вкладке Параметры набора контекстных вкладок Инструменты для среза выберите для параметра Столбцы значение 7.
6. На срезе выберите только рабочие дни.
Результат только что выполненных действий показан на рис. 40. Поскольку была выполнена сортировка дней недели по номерам, дни недели в окне среза отображаются в правильной последовательности.
Рис. 40. Дни недели в окне среза отображаются в корректной последовательности
Если вы видите окно PowerPivot впервые, вряд ли вы будете впечатлены его возможностями. Если же вы работали с PowerPivot в предыдущих версиях Excel, то сможете по достоинству оценить корректную сортировку дней недели.
«Умные» функции времени.После выполнения предварительной работы пришло время вплотную заняться «умными» функциями времени. Начните с реорганизации сводной таблицы, изображенной на рис. 40:
1. На контекстной вкладке Анализ выполните команду Очистить g Очистить всё.
2. Откройте таблицу Календарь и перетащите поле Дата в область СТРОКИ.
3. Откройте таблицу Продажи и перетащите поле Продажи в область ЗНАЧЕНИЯ.
4. Добавьте срез для поля Год.
5. В окне среза Год выберите значение 2015.
В результате будет получена сводная таблица, показанная на рис. 41. Объем продаж за 2 июня 2015 года составил 937 долларов.
Рис. 41. Начните с создания простого отчета о продажах
А сейчас мы создадим вычисляемые поля DAX, с помощью которых сравним объем продаж текущего дня с объемом продаж такого же дня предыдущего года. Ранее уже упоминалось, что эта задача не столь уж проста, поскольку к ячейке С9 применен фильтр, отображающий записи с датой 2.06.2015. Поэтому нужно отменить фильтр по полю даты, а затем применить новый фильтр, который позволит отобразить записи прошлого года.
Чтобы отменить фильтр, примененный к полю даты, необходимо воспользоваться функцией CALCULATE. Но что делать, если нужно отобразить записи, соответствующие прошлому году? Воспользуйтесь одной из более чем 30 «умных» функций времени, поддерживаемых в DAX, а именно — функцией DATEADD.
Чтобы выбрать точно такой же день ровно год назад, воспользуйтесь формулой DATEADD ('Календарь'[Дата];–1;year). В качестве третьего аргумента может использоваться day, month либо year. Учтите, что эти аргументы являются перечисляемыми (т.е. при программировании в Excel определяются в качестве глобальных переменных, которые при выполнении программы преобразуется в числовой код), поэтому не заключаются в кавычки подобно текстовым аргументам.
Функция DATEADD используется при создании многих формул, вычисляющих периоды времени. Например, чтобы просмотреть дневной объем продаж, который имел место три месяца назад, воспользуйтесь формулой =DATEADD('Календарь'[Дата];–3;month). И не забывайте о том, что функция DATEADD — всего лишь одна из 34 «умных» функций, предназначенных для работы со временем. Например, еще одна функция из этой категории используется в формуле DATESMTD ('Календарь'[Дата]), которая отображает все даты вплоть до текущего дня месяца.
Предположим, что в вашей модели данных столбец даты находится в таблице Продажи и в таблице Календарь. «Умные» функции времени будут всегда корректно работать, если используется ссылка на поле 'Календарь'[Дата]. И они же будут работоспособны лишь в 10% случаев, если сослаться на поле 'Продажи'[Дата]. Если хотите поломать голову над сложной задачей, попробуйте создать формулу DAX со ссылкой на поле 'Продажи'[Дата]. После проверки корректности синтаксиса на экране появится бодрое сообщение об отсутствии ошибок, и тем не менее сводная таблица отображает некорректные результаты. Я не могу обнаружить причину подобного странного поведения формулы. Но если формула будет применена к полю 'Календарь'[Дата], будут получены корректные результаты.
Итак, в вашем распоряжении имеется функция DATEADD, которая позволяет идентифицировать дату, которая ровно на год отстоит от даты, указанной в строке сводной таблицы. Чтобы отобразить объем продаж на эту дату, воспользуйтесь функцией CALCULATE, которая переопределяет существующие неявные фильтры. Поскольку в строке 9 сводной таблицы используется неявный фильтр, отображающий записи, соответствующие дате 2 июня 2015 года, воспользуйтесь следующей формулой, включающей функцию CALCULATE:
=CALCULATE([Сумма по столбцу Продажи];DATEADD('Календарь'[Дата];-1;year))
Выполните следующие действия:
1. В окне Excel выберите вкладку PowerPivot и выполните команду Вычисляемые поля g Создание вычисляемого поля. Откроется окно Вычисляемое поле (рис. 42).
2. Присвойте полю имя ПродажиЗаПоследнийГод.
3. Введите формулу
=CALCULATE([Сумма по столбцу Продажи];DATEADD('Календарь'[Дата];-1;year))
4. В группе Категория выберите Валюта. Установите Десятичные разряды 0. Выберите Символ – $ Английский (США).
5. Щелкните на кнопке Проверить формулу, чтобы убедиться в корректности формулы. Если из-за отображаемой на экране подсказки результаты проверки формулы не видны, щелкните в поле Описание. Щелкните ОК, чтобы завершить создание вычисляемого поля.
6. Вы вернетесь в окно Excel. Кликните в Списке полей сводной таблицы в таблице Календарь на поле ПродажиЗаПоследнийГод. Поле добавиться в область ЗНАЧЕНИЯ.
Рис. 42. Использование «умной» функции для расчета объема продаж за прошлый год
Чтобы завершить анализ, добавьте второе вычисляемое поле, которое будет использоваться для подсчета динамики изменений в процентах. Для этого выполните следующие действия:
1. В окне Excel выберите вкладку PowerPivot и выполните команду Вычисляемые поля g Создание вычисляемого поля.
2. Присвойте полю имя ПроцентноеИзменение.
3. Введите формулу =[Сумма по столбцу Продажи]/[ПродажиЗаПоследнийГод]–1.
4. В группе Категория выберите Число. Установите Десятичные разряды 1. Выберите Формат – Процент.
5. Повторите пп. 5 и 6 предыдущей инструкции.
Получившаяся сводная таблица показана на рис. 43.
Рис. 43. Сводная таблица с двумя вычисляемыми полями на основе формул DAХ
Теперь можно удалить из сводной таблицы поля Продажи и ПродажиЗаПоследнийГод, оставив поле ПроцентноеИзменение и добавить имя продавцов (поле Торговец) в область КОЛОННЫ (рис. 44). В каждой ячейке сводной таблицы показана динамика продаж по сравнению с прошлым годом (странно, но мне не встречалось, чтобы анализировали ежедневную динамику).
Рис. 44. Сводная таблица с вычисляемым полем ПроцентноеИзменение
Чтобы получить таблицу, показанную на рис. 44, выполните следующие действия:
1. В ячейке G9 (и в некоторых других) отображается сообщение об ошибке, причиной появления которого является отсутствие продаж в предыдущем году (и, следовательно, деление на 0). Кликните правой кнопкой мыши на любой ячейке сводной таблицы и в контекстном меню выберите Параметры сводной таблицы (рис. 45). Перейдите на вкладку Макет и формат и установите флажок Для ошибок отображать и в соответствующее поле введите символы --.
2. Выделите числовые значения в сводной таблице (диапазон С9:Н38). Перейдите на вкладку Главная, и выполните команду Условное форматирование g Наборы значков и выберите набор из двух треугольников и одного прямоугольника. Если хотите, отредактируйте правила форматирования, установленные Excel по умолчанию.
Рис. 45. Укажите, как отображать ошибки
Чтобы получить дополнительные сведения о DAX, посетите блог Роба Колли http://www.powerpivotpro.com/ (я купил книжку Колли, так что предвижу замечательное чтение).