Практическое занятие №29

Практическое занятие №29 (1И)

Тема: Excel Power Pivot модели данных

Цель работы: формирование теоретических и практических навыков представления данных с использованием Excel Power Pivot

Оборудование: компьютер с установленной операционной системой Windows, MS Excel

Время работы 2 часа

Ход работы

Шаг 1. Скачиваем таблицу с исходными данными по ссылке https://cloud.mail.ru/public/1aWV/gssawKDP5

Шаг 2. Превращаем все таблицы в "умные" и даём им имена

Для загрузки таблиц в Power Pivot они должны быть "умными" (динамическими). Для этого с каждой таблицей проделываем следующее:

1. Выделяем любую ячейку таблицы

2. Жмем сочетание клавиш Ctrl+T или выбираем Главная - Форматировать как таблицу (Home - Format as Table).

3. В открывшемся окне проверяем корректность выделения диапазона (особенно для таблицы плана!) и включена ли галочка Таблица с заголовками (My table has headers) и жмем ОК.

4. На вкладке Конструктор (Design) в левом верхнем углу даем таблице осмысленное имя вместо стандартных безликих Таблица1,2,3...

Называем наши таблицы, соответственно:

• таблПродажи

• таблТовары

• таблГеография

• таблКалендарь

• таблПлан

Шаг 3. Грузим первые 4 таблицы в Power Pivot

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

В старых версиях эта кнопка называлась Связанная таблица (Linked table).

В итоге все наши таблички должны загрузиться в открывшееся окно Power Pivot на отдельные вкладки:

Шаг 4. Доводим до ума таблицу План

Прежде, чем загрузить в Модель данных Power Pivot таблицу с плановыми значениями, её нужно сначала подрихтовать: убрать в ней пустые строки и итоги, развернуть в плоскую, заполнить пустые ячейки в первом столбце городами и т.д. Проще и легче всего это проделать с помощью надстройки Power Query.

Сначала загрузим таблицу с планами в редактор запросов Power Query, используя кнопку Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или на вкладке Power Query (если у вас старая версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

Затем в открывшемся окне Power Query делаем следующее:

1. Удаляем все пустые строки с null через Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows).

2. Удаляем строки с итогами, сняв соответствующую галочку в фильтре по столбцу Товар.

3. Удаляем ненужный последний столбец ИТОГО, щелкнув по его заголовку правой кнопкой мыши - Удалить (Remove).

4. Заполняем пустые ячейки в первом столбце названиями городов из вышестоящих ячеек, щелкнув по заголовку столбца Город правой и выбрав Заполнить - Вниз (Fill - Down).

5. Разворачиваем 12 столбцов-месяцев в два: название месяца и его значение. Для это выделяем первых два столбца Город и Товар (удерживая клавишу Ctrl), щёлкаем по их заголовку правой и выбираем команду Отменить свёртывание других столбцов (Unpivot Other Columns).

6. Чтобы преобразовать текстовые названия месяцев в нормальную даты - идём на хитрость:

• Добавляем перед датами единички через пробел с помощью команды Преобразование - Формат - Добавить префикс (Transform - Format - Add prefix)

• Аналогично добавляем после дат 2019 через Преобразование - Формат - Добавить суффикс (Transform - Format - Add suffix)

• Теперь, когда текст в этом столбце стал уже гораздо больше похож на дату, конвертируем всё его содержимое в даты, используя выпадающий список типов в шапке столбца:
  
  

7. Столбец Атрибут переименовываем в Дата (двойным щелчком по заголовку столбца).

8. Чтобы не путать исходную таблицу плана с преобразованной, изменим имя запроса на таблПлан2 в правой панели Power Query (впоследствии это будет именем таблицы в Power Pivot).

9. Выгружаем готовую таблицу в Модель данных Power Pivot, используя команды Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...) и выбираем затем в следующем окне опцию Только создать подключение (Only create connection) плюс, самое главное (!), включаем флажок Добавить эти данные в модель данных (Add this data to Data Model):

После этого наша последняя таблица таблПлан должна загрузиться в окошко Power Pivot.

Шаг 5. Связываем таблицы

Теперь пришло время выполнить одно из самых важных действий - связать все имеющиеся у нас таблицы в единую модель, чтобы впоследствии иметь возможность строить сводную по всей модели, будто это одна таблица.

Для связывания в окне Power Pivot лучше переключиться в режим диаграммы с помощью кнопки Главная - Представление диаграммы (Home - Diagram View) или значком Диаграмма (Diagram) в правом нижнем углу окна. Прямоугольные окошки таблиц можно перетащить за строку заголовка и разложить любым удобным вам образом.

Связь делается очень просто: хватаем мышью столбец в одной из соединительных таблиц (таблТовары, таблГеография, таблКалендарь), тянем и бросаем на соответствующий столбец в таблицах таблПродажи и таблПлан2:

Главный принцип: тянем от таблиц-справочников (Товары, География, Календарь) к таблицам факта и плана. Делаем 6 связей - каждый справочник должен быть связан двумя связями с таблицами плана и продаж. В итоге должна получиться вот такая картина:

Обратите особое внимание на положение единичек и звёздочек на концах связей - это как раз и есть те самые связи "один-ко-многим", где звёздочка обозначает множество вхождений одного и того же элемента, а единичка - уникальность.

Если всё получилось, то сохраняем файл и выдыхаем - дело почти сделано.

Шаг 6. Строим сводную

Теперь на основе созданной модели данных можно построить сводную - для этого в окне Power Pivot выбираем команду Главная - Сводная таблица - Сводная таблица (Home - Pivot table - Pivot table). Мы автоматически вернёмся в Excel, где увидим привычную панель для построения сводной таблицы в правой части экрана, но в ней будут видны уже все таблицы, а не только текущая (как обычно):

Теперь можно знакомым уже образом перетащить мышью нужные нам поля из таблиц в области сводной таблицы.

Главные принципы здесь такие:

• В области строк, столбцов и фильтра можно бросать только поля из таблиц-справочников (таблГеография, таблКалендарь, таблТовары).

• В область значений, где идут вычисления, можно закидывать только поля из таблиц факта и плана (таблПродажи, таблПлан2)

Например, можно накидать так:

Чтобы по столбцам даты шли не с шагом один день, а покрупнее - щёлкаем по любой дате в сводной правой кнопкой мыши и выбираем команду Группировать по (Group by), а затем любой нужный уровень группировки:

В итоге должно получиться что-то уже очень похожее на то, что нам требуется:

Шаг 7. Добавляем меры для вычислений

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

Сумма по полю Выручка := SUM(таблПродажи[Выручка])

Но контролировать процесс создания неявных мер мы не можем - Excel сам решает как её назвать, какую именно функцию (SUM или COUNT) использовать и т.д. Поэтому лучше создавать явные меры для сводной самостоятельно - в этом случае мы сможем контролировать все их параметры.

Для этого на вкладке Power Pivot выберем команду Меры - Создать меру (Measure - New measure) и в открывшемся окне задаём:

Здесь:

• Имя таблицы - место для хранения меры (можно выбрать любую таблицу - это не играет роли).

• Название меры - придумываем и вводим любое удобное название (можно на русском).

• Описание - по желанию.

• Формула - вводим формулу, по которой будет вычисляться мера. Можно использовать функции из встроенного в Power Pivot языка DAX (кнопка fx).

• Проверить формулу - чекает вашу формулу на предмет ошибок и выдаёт рекомендации по их исправлению.

• В нижней части окна можно сразу же задать числовой формат для меры, чтобы потом по 100 раз не настраивать его в сводной (как это бывает с обычными неявными мерами).

Повторяем процесс еще два раза:

• Создаем меру с именем Факт с формулой =SUM('таблПродажи'[Выручка]) и числовым форматом без копеек и с разделителем.

• Создаём меру Отклонение, которая использует две предыдущих созданных меры по формуле =[Факт]/[План]-1 и процентным форматом

Добавленные меры появятся в правой панели сводной таблицы с характерным значком:

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

Обновляется вся созданная красота (модель данных Power Pivot, запрос Power Query и сама сводная) одним движением - на вкладке Данные (Data) с помощью кнопки Обновить все (Refresh All) или сочетания клавиш Ctrl+Alt+F5.

Контрольные вопросы

1.            Преимущества надстройки Power Pivot над обычными таблицами Excel