Полезная пошаговая инструкция для всех маркетологов! Связываем много таблиц в Excel с помощью Power Query и Power Pivot

Зачем маркетологу разбираться в Power Query и Power Pivot?

Если вы работаете с данными, вам известны следующие проблемы:

Power Query и Power Pivot решают эти проблемы, вызывая следующее:

✅ Автоматически объединять данные из разных таблиц.
✅ Создавать удобные отчеты без ручных программ.
✅ Работать с указанными объемами информации без перегрузки Excel.

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

Мы будем работать со следующими таблицами:

Таблица №1 "Справочник" – содержит список продуктов, их тип и цена.

Таблица №2 "Статистика" – данные о продажах (дата, товар, количество продаж).

Таблица №3 "Календарь" – список всех дат (нужен для корректной работы временных функций), который мы заранее подготовили.

Календарь состоит в основном из дат, которые идут последовательно. Главное чтобы у нас были все даты, которые потенциально могут попасться у нас в Таблице «Статистика». Подойдет любой календарь с датами.

Объединить эти таблицы в одну, создать связь между ними и составить сводный отчет.

Перед загрузкой в Power Query необходимо преобразовать таблицы в умные таблицы .

1. Выделите диапазон данных.

2. Перейдите во вступление Главная → Форматировать как таблицу.

3. Выберите стиль и нажмите → ОК.

4. Дайте таблице понятное имя (в поле «Имя таблицы»):

Переименовываем слово "Таблица" в нужное имя таблицы → "СправочникПродуктов"

Далее нам необходимо проделать аналогичную работу с другими таблицами (Таблица №2 "Статистика", Таблица №3 "Календарь", а именно:

1. Создаем умную таблицу

2. Даем Имя таблицам:

Таблица №2 "Статистика" → "Статистика"

Таблица №3 "Календарь" - "Календарь"

Итого у нас получается 3 умных таблиц:
- "СправочкикПродуктов"
- "Статистика"
- "Календарь".

Теперь таблицы готовы для импорта.

Создаем файл Excel и открывает его, в котором будем делать отчет.

Теперь добавляем таблицы в Power Query .

1. Заходим во вкладку Данные → Получить данные → Из файла → Из книг Excel.

2. Выбираем файл таблицей и загружаем в Power Query.

3. В появившемся окне "Навигатор" выбираем нашу умную таблицу "СправочнткПродуктов", нажимаем → Загрузить

Умная таблица "СправочникПродуктов" загрузилась в Power Query

4. Теперь нужно сделать подключение к данной таблице.

В окне справа, где «Запросы и подключения» - жмем на нашу таблицу "СправочникПродуктов" → правая кнопка мыши → Загрузить в…

В окне "Импорт данный" выбираем → "Только создать подключение" → ставим галочку "Добавить эти данные в модель данных" → жмем Ок.

Далее Жмем → Ок.

У нас создалось подключение Power Query с нашей таблицей.

5. Удаляем вкладку "СправочникПродуктов".

6. Повторяем этот процесс (ШАГ №3) для всех таблиц: "Статистика" и "Календарь".

Добавляем умные таблицы "Статистика" и "Календарь" в Power Query и создаем подключение.

Теперь нам нужно соединить таблицы, чтобы они «понимали» друг друга.

1. Переходим во вкладку Power Pivot → Управление

Если Power Pivot не включен, то нужно перейти на вкладку Файл → Параметры → Надстройки. В поле Управление → Надстройки COM → Перейти. Выбрать Microsoft Power Pivot for Excel → Добавить.

2. Открывается окно Power Pivot, где мы видим загруженные таблицы.

3. Переходим во вкладку Конструктор → Создание связей.

4. В сплывающем окне "Создание связи" выбираем взаимосвязанные таблицы и столбцы, и создаем связи:

Таблица "СправочникПродуктов" [Продукт] → Таблица "Статистика" [Продукт]

и еще связь

Таблица "Календарь" [Дата]→ Таблица "Статистика" [Дата]

Все нужные связи созданы. Теперь Excel понимает, как видимость данных между собой.

Созданные связи также можно поменять и изменить в графе Конструктор → Управление связями.

5. Далее нажимаем → Файл → Сохранить → закрываем окно Power Pivot.

Допустим мы хотим в таблицу "Статистика" добавить данные "Тип продуктов" и "Цена" из таблицы "СправочникПродуктов", сейчас покажу как это сделать.

1. Открывает Power Pivot → Управление.

2. Заходим в таблицу (вкладку) "Статистика"

3. Для добавления столбца "Тип продукта" в графе "Добавление столбца" вводим формулу:

Функция RELATED в DAX (Power Pivot) используется для извлечения связанного значения из другой таблицы , если между таблицами установлена связь «один ко многим» (1 :M )

Например:
RELATED(Таблица[Столбец])
где Таблица – имя таблицы, из которой ты хочешь получить данные.
где Столбец – конкретный столбец из этой таблицы.

❗ функция RELATED работает, только если между таблицами есть связь .

и в другой столбец добавляем "Цену" и пишем формулу:

получаем следующее

4. Нажимаем Файл → Сохранить → закрываем окно.

1. В окне "Запросы и подключения" → выбираем нашу доработанную таблицу → правая кнопка мыши → Загрузить в…

2. В окне "Импорт Данных" → выбираем Таблица → Имеющийся лист → Ок.

Все готово!

1. Переходим во вставке Вставка → Сводная таблица → Из модели данных → выбираю в Существующий лист и размещаем сводную таблицу.

Теперь мы можем работать со сводной таблицей и делать себе разные отчеты.

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

Для примера я сделаю простой отчет:

Тип продуктов → в строках
Продукты → в строках
Продажи → в значении
Дата (из Календаря) → в строках

Теперь у нас есть динамический отчет, который автоматически обновляется при сохранении данных.

🔹Power Query помогает автоматизировать загрузку данных.

🔹Power Pivot Позволяет устанавливать связи между таблицами и делать сложные расчеты.

🔹Сводные таблицы дают удобный способ анализа данных.

💡Практическое применение:

Если вы работаете с маркетинговыми данными и хотите вручную копировать таблицы , обязательно настройте Power Query и Power Pivot ! 🚀

Всем Добра!=)