Сегодня покупают

Power BI из Excel: как построить первый отчёт и настроить автоматическое обновление

Power BI из Excel: как построить первый отчёт и настроить автоматическое обновление

Типичный первый проект Power BI начинается не с базы данных, а с обычного Excel. Компания уже ведёт продажи, расходы, обращения или складские остатки в таблице, но еженедельный отчёт приходится собирать вручную: обновлять формулы, перестраивать диаграммы, копировать данные и менять периоды.

Power BI позволяет отделить данные от самого отчёта. Excel продолжает выступать источником, Power Query отвечает за очистку, модель хранит структуру и вычисления, а визуализации автоматически пересчитываются после обновления.

Разберём полный процесс от исходной книги Excel до опубликованного отчёта Power BI: подготовку таблицы, Power Query, DAX, визуализации, Refresh, Power BI Service, OneDrive/SharePoint и on-premises data gateway. Инструменты для аналитики и офисной работы представлены в разделе программного обеспечения AplTech.ru.

Сначала подготовьте нормальный источник данных

Представим отчёт по продажам. Для аналитики нужна таблица такого типа:

OrderDate  | Manager | Region | Product  | Qty | Revenue | Cost
-----------|---------|--------|----------|-----|---------|------
01.11.2026 | Maria   | Moscow | Laptop   | 2   | 210000  | 180000
01.11.2026 | Ivan    | Kazan  | Monitor  | 3   | 135000  | 108000
02.11.2026 | Maria   | Moscow | SSD      | 5   | 85000   | 60000
02.11.2026 | Pavel   | Perm   | Printer  | 1   | 42000   | 35000

Главное правило - одна строка должна представлять одну запись одинакового уровня детализации. Не нужно вставлять итоговые строки после каждого менеджера или месяца: Power BI самостоятельно выполнит агрегирование.

Также желательно:

  • не использовать объединённые ячейки;
  • не оставлять пустые заголовки;
  • не размещать несколько таблиц вплотную на одном листе;
  • не добавлять вручную строки «ИТОГО»;
  • не смешивать числа и текст в одном столбце;
  • не раскрашивать данные вместо отдельного признака.

Почему цвет ячейки - плохой способ хранить данные

Например, сотрудник отмечает просроченные заказы красной заливкой. Для человека это понятно, но для Power BI намного полезнее отдельный столбец:

Order | Deadline   | Status
------|------------|---------
1001  | 12.11.2026 | On time
1002  | 13.11.2026 | Overdue
1003  | 15.11.2026 | On time

Тогда можно фильтровать, считать и визуализировать статус без попыток интерпретировать оформление Excel.

Преобразуйте диапазон Excel в Table

Выделите исходные данные и нажмите Ctrl + T. Проверьте флажок «Таблица с заголовками» и присвойте объекту понятное имя, например Sales.

Это даёт несколько преимуществ:

  • новые строки становятся частью таблицы автоматически;
  • Power BI видит понятное имя источника;
  • меньше риска случайно подключиться к лишним ячейкам;
  • структура становится предсказуемой.

Подключение Excel к Power BI Desktop

В Power BI Desktop выберите:

Home → Get data → Excel workbook.

После выбора файла появится Navigator. В нём Power BI может показать как листы, так и Excel Tables.

Для подготовленного файла выбираем Sales и нажимаем Transform Data.

Мы специально не используем сразу Load, потому что данные лучше проверить до попадания в модель.

Power Query - повторяемый конвейер подготовки данных

Power Query Editor не просто редактирует текущую копию таблицы. Каждое действие сохраняется как Applied Step.

Например:

SOURCE
Sales.xlsx
   │
   ↓
Promoted Headers
   ↓
Changed Type
   ↓
Removed Empty Rows
   ↓
Removed Columns
   ↓
Filtered Status
   ↓
Renamed Columns
   │
   ↓
POWER BI MODEL

Через неделю Excel изменится, но Power BI выполнит ту же последовательность заново. Поэтому не приходится вручную очищать каждую новую выгрузку.

Что проверить в Power Query

Для первой модели особенно важны четыре вещи.

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

Пустые строки. Они редко несут полезную информацию и могут мешать анализу.

Ошибочные значения. Например, в числовом столбце внезапно появляется текст нет данных.

Ненужные столбцы. Чем меньше лишнего попадает в модель, тем проще с ней работать.

После проверки выберите Close & Apply.

Импортированные данные и сам Excel - это не одно и то же

При стандартном режиме Import данные загружаются внутрь модели Power BI.

Sales.xlsx
    │
    ↓ Refresh
Power Query
    │
    ↓
Power BI model
    │
    ↓
Visuals

Поэтому если Excel изменился после последнего обновления, открытый отчёт не обязательно мгновенно узнает об этом. Нужно выполнить Refresh.

Первый DAX: считаем основные показатели

Для отчёта по продажам создадим measures вместо того, чтобы постоянно бросать сырые столбцы в визуализации.

Выручка:

Total Revenue =
SUM(Sales[Revenue])

Себестоимость:

Total Cost =
SUM(Sales[Cost])

Прибыль:

Profit =
[Total Revenue] - [Total Cost]

Маржинальность:

Margin % =
DIVIDE(
    [Profit],
    [Total Revenue]
)

DIVIDE() удобнее обычного оператора деления, потому что корректнее обрабатывает ситуацию, когда знаменатель равен нулю.

Как собрать страницу отчёта

Для первой страницы достаточно нескольких действительно полезных визуализаций.

┌────────────────────────────────────────────┐
│ Revenue       Profit       Margin          │
├──────────────────────┬─────────────────────┤
│                      │                     │
│ Revenue by month     │ Revenue by region   │
│                      │                     │
├──────────────────────┴─────────────────────┤
│ Top products                               │
├────────────────────────────────────────────┤
│ Filters: Date | Region | Manager           │
└────────────────────────────────────────────┘

Главный принцип: один график должен отвечать на один конкретный вопрос. Не нужно добавлять визуализацию только потому, что она есть в списке Power BI.

Карточки

Три Card visual сверху можно использовать для:

  • Total Revenue;
  • Profit;
  • Margin %.

Так руководитель сразу видит основные KPI, а детализацию изучает ниже.

Динамика продаж

Line chart:

  • X-axis - OrderDate;
  • Y-axis - Total Revenue.

После этого легко увидеть рост, падение и сезонные изменения.

Регионы и товары

Для сравнения категорий удобно использовать горизонтальный Bar chart.

Например:

  • Y-axis - Region;
  • X-axis - Total Revenue.

Второй график можно построить по Product и отсортировать по выручке.

Как работают фильтры

Slicer с регионом, менеджером или датой изменяет контекст фильтра. Measures автоматически пересчитываются.

Все данные
Revenue = 25 000 000


Region = Moscow
Revenue = 9 800 000


Region = Moscow
Manager = Maria
Revenue = 4 250 000

Формула Revenue при этом остаётся одной и той же. Меняется только контекст вычисления.

Обновление локального отчёта

Пусть вчера Excel содержал 5 000 операций. Сегодня бухгалтер добавил ещё 300 строк и сохранил файл.

В Power BI Desktop нажимаем Refresh.

Дальше всё происходит автоматически:

Excel
5 300 строк
     ↓
Power Query
повторяет Applied Steps
     ↓
Model
5 300 строк
     ↓
DAX
пересчитывает measures
     ↓
Report
обновляет графики

Ручное редактирование диаграмм не требуется.

Новые строки безопаснее новых столбцов

При ежедневном обновлении источника желательно сохранять его схему.

Если вчера было:

Date | Manager | Region | Revenue | Cost

то завтра лучше оставить те же названия.

Если Revenue внезапно переименовать в SalesAmount, Power Query может остановиться на шаге, который ожидает старое имя столбца.

Как исправлять источник, если файл переехал

Если Excel перенесли в другую папку, запрос продолжает искать старый путь.

Например:

Было:
C:\Reports\Sales.xlsx

Стало:
D:\BI\Data\Sales.xlsx

Нужно изменить настройки источника или шаг Source в Power Query, а не создавать весь отчёт заново.

Publish: перенос отчёта в Power BI Service

После сохранения PBIX нажмите Publish и выберите workspace.

В Power BI Service появятся как минимум две важные сущности:

  • report - страницы с визуализациями;
  • semantic model - модель, данные, relationships и measures.

Пользователь обычно открывает report, но обновляется прежде всего semantic model.

Почему опубликованный отчёт сам не читает C:\Sales.xlsx

Это фундаментальный момент.

Power BI Service работает в облаке Microsoft. Ваш диск C: находится на вашем компьютере.

Power BI Service
Microsoft Cloud

          ✗

C:\Users\Ivan\Reports\Sales.xlsx
локальный компьютер

Облачный сервис не может самовольно открыть локальный файл. Для такого сценария нужен gateway.

On-premises data gateway

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

Power BI Service
       │
       ↓
Data Gateway
       │
       ├── local Excel
       ├── network folder
       ├── SQL Server
       └── другие локальные источники

Если компьютер с gateway выключен в момент scheduled refresh, обновление может завершиться ошибкой.

OneDrive и SharePoint избавляют от локального файла

Для небольшого Excel-источника часто удобнее перенести файл в OneDrive for Business или SharePoint Online и настроить Power BI на облачный путь.

Преимущества:

  • нет зависимости от диска конкретного сотрудника;
  • не нужно держать пользовательский компьютер включённым;
  • проще совместная работа;
  • есть история версий файла;
  • Power BI Service может обращаться к облачному источнику напрямую.

Важное изменение: старую загрузку локальной книги Excel лучше не использовать

Microsoft больше не поддерживает старый сценарий, при котором локальную книгу Excel загружали непосредственно в Power BI workspace и затем настраивали для неё refresh.

Для нового проекта рациональнее:

Вариант 1

Excel
  ↓
Power BI Desktop
  ↓
Publish semantic model


Вариант 2

Excel
OneDrive / SharePoint Online
  ↓
Power BI

Это важный момент, потому что в интернете осталось много старых инструкций с интерфейсом и схемами обновления, которые больше не являются рекомендуемым способом.

Scheduled refresh

В настройках semantic model укажите расписание. Например:

06:00
обновление перед рабочим днём

12:00
обновление после первой половины дня

18:00
вечерний срез

На shared capacity можно настроить до восьми scheduled refresh в сутки. На Premium, Premium Per User или Fabric capacity - до 48 в сутки.

Для Excel, который меняется один раз ночью, нет смысла ставить обновление каждый час.

Сначала обновляется источник, потом Power BI

Очень распространённая ошибка расписания:

01:00 Power BI Refresh

02:00 ERP обновила Excel

Результат:
утренний отчёт показывает вчерашние данные.

Правильнее:

01:00 ERP обновила Excel
       ↓
01:30 файл сохранён / синхронизирован
       ↓
02:00 Power BI Refresh
       ↓
утром доступны свежие данные

Для источников в OneDrive и SharePoint также учитывайте время синхронизации облачного файла.

Refresh history - первое место при ошибке

Если утром отчёт остался старым, не нужно сразу перестраивать модель. Откройте историю обновлений semantic model.

Проверьте:

  • запускался ли refresh;
  • завершился ли он успешно;
  • какой источник дал ошибку;
  • работают ли credentials;
  • доступен ли gateway;
  • не изменилась ли схема Excel.

Когда Excel уже пора заменить базой данных

Power BI прекрасно работает с Excel как первым источником. Но если файл превращается в критичную систему с десятками сотрудников и постоянными изменениями, источник становится слабым местом.

Признаки:

  • файл регулярно блокируется;
  • кто-то случайно удаляет строки;
  • несколько отделов делают собственные копии;
  • выгрузка стала огромной;
  • данные на самом деле уже существуют в CRM или ERP;
  • Excel формируется только ради последующего импорта в Power BI.

В такой ситуации Power BI лучше подключать ближе к первичному источнику: SQL-базе, корпоративной системе, API или облачному хранилищу.

Минимальная архитектура рабочего отчёта

EXCEL TABLE
    ↓
POWER QUERY
очистка и преобразование
    ↓
DATA MODEL
таблицы и связи
    ↓
DAX
меры и KPI
    ↓
REPORT
визуализации
    ↓
POWER BI SERVICE
публикация
    ↓
SCHEDULED REFRESH
автоматическое обновление

Это уже полноценный BI-процесс. Excel остаётся простым источником, а логика очистки, вычислений и визуализации хранится в Power BI и повторяется автоматически.

Частые вопросы о Power BI из Excel

Нужно ли устанавливать Power BI для работы с Excel?

Для создания полноценного отчёта удобнее использовать бесплатное приложение Power BI Desktop для Windows. В нём выполняются подключение к Excel, Power Query, моделирование, DAX и создание визуализаций.

Зачем преобразовывать диапазон Excel в таблицу?

Excel Table имеет стабильное имя и автоматически расширяется при добавлении новых строк. Это делает источник Power BI предсказуемее и удобнее для регулярного обновления.

Power BI автоматически увидит изменения в Excel?

В Power BI Desktop данные обновляются после Refresh. После публикации автоматическое обновление настраивается для semantic model в Power BI Service и зависит от расположения и типа источника.

Когда нужен Power BI Gateway?

Gateway нужен, когда Power BI Service должен получать данные из источника, доступного только внутри локальной сети или на локальном компьютере. Для корректно подключённых облачных источников OneDrive или SharePoint Online локальный gateway обычно не требуется.

Можно ли обновлять отчёт без открытия Power BI Desktop?

Да. После публикации в Power BI Service можно настроить scheduled refresh semantic model. Для локального источника при этом должен быть доступен настроенный gateway.

Что лучше для Power BI: Excel или база данных?

Excel отлично подходит для первого отчёта и небольших наборов данных. Когда источник становится критичным, большим или многопользовательским, надёжнее подключать Power BI к базе данных, ERP, CRM или другому первичному источнику.

Читать также