Дашборд в Excel: как собрать и где он упирается
Дашборд в Excel можно собрать за вечер, и до определённого объёма это разумный выбор. Разбираем порядок сборки и границу, за которой файл начинает отнимать больше времени, чем экономит.
Опубликовано
Каким должен быть исходник
Девять десятых проблем со сводными таблицами приходят из самой выгрузки. Она должна быть плоской: одна строка на одну операцию, и ничего больше.
- Заголовки в одну строку, каждый свой Два столбца с именем «Сумма» превратятся в «Сумма» и «Сумма2», и через месяц вы не вспомните, где какая.
- Даты датами, суммы числами Выравнивание по левому краю у даты означает, что Excel считает её текстом. Группировка по месяцам с таким столбцом не заработает.
- Без объединённых ячеек Объединение относится к оформлению для печати. В данных оно оставляет пустые ячейки, и сводная теряет строки.
- Без итогов и пустых строк внутри Строка «Итого» посреди выгрузки попадёт в сводную наравне с обычными и удвоит сумму.
Порядок сборки
Дальше разберём на примере выгрузки продаж из 1С: дата, контрагент, менеджер, номенклатура, количество, сумма, себестоимость.
- Превратите диапазон в таблицу Встаньте в любую ячейку и нажмите Ctrl+T. Теперь новые строки будут попадать в сводные сами, без правки диапазона.
- Добавьте расчётные столбцы прямо в таблицу Маржа = сумма − себестоимость. Месяц = функция ТЕКСТ от даты с форматом «ГГГГ-ММ», чтобы месяцы шли по порядку. Считать это внутри сводной сложнее, чем в источнике.
- Постройте сводные на отдельном листе Вставка / Сводная таблица. Четыре штуки: по месяцам, по менеджерам, по клиентам, по номенклатуре. Кладите их на один лист с запасом пустых строк между ними. Он останется рабочим, смотреть на него не придётся.
- Сделайте по диаграмме на каждую Гистограмма для сравнения, график для динамики. Круговых лучше избегать: доли в 12% и 15% на глаз неразличимы.
- Добавьте срезы Вставка / Срез, по периоду и по менеджеру. Каждый срез откройте правой кнопкой, «Подключения к отчётам», и отметьте все сводные разом, иначе он будет управлять одной.
- Соберите лист-обложку
Скопируйте на него диаграммы и срезы, спрячьте сетку
(Вид / снять «Сетка»), поставьте крупные числа формулой
ПОЛУЧИТЬ.
ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ. Рабочие листы скройте. - Проверьте обновление Данные / Обновить всё. Если после подстановки свежей выгрузки цифры не поменялись, значит сводная смотрит на старый диапазон.
Что ломается чаще всего
| Симптом | Причина |
|---|---|
| Даты не группируются по месяцам | столбец пришёл текстом; лечится «Текст по столбцам» с указанием формата даты |
| Сумма больше, чем в 1С | в выгрузке остались строки «Итого» или дубли из-за повторной вставки |
| Новые строки не попадают в отчёт | источник остался обычным диапазоном |
| Срез управляет одной сводной из четырёх | не отмечены остальные в «Подключениях к отчётам» |
| Файл открывается минуту | формулы массива по всему столбцу, условное форматирование на миллион ячеек |
| У коллеги другие цифры | файл разошёлся копиями по почте |
Где Excel упирается
У листа предел 1 048 576 строк, но упирается всё раньше. Сводная по таблице от 300 до 500 тысяч строк думает по несколько секунд на каждое нажатие среза, файл разрастается до 50, а то и 80 МБ, а автосохранение начинает подвешивать окно.
Объём при этом остаётся самой понятной границей, но не самой болезненной. Мешают обычно четыре других:
- Свежесть Кто-то должен выгрузить и вставить данные. Пропустили день, панель показывает позавчера, и никто об этом не знает.
- Второй источник Пока данные из одной программы, всё сходится. Как только рядом появляется CRM со своими названиями контрагентов, сведение превращается в отдельную работу на каждую выгрузку.
- Права Показать менеджеру его сделки и скрыть чужие в файле нельзя. Либо он видит всё, включая зарплаты и наценки, либо не видит ничего.
- Версии Файл разъезжается копиями, и на совещании выясняется, что у троих три разных выручки за один месяц.
Что делать, когда упёрлись
Следующая ступень остаётся внутри Excel и стоит ноль рублей. Power Query забирает выгрузку из папки или из базы и чистит её по записанным шагам. Руками больше не трогаете. Power Pivot держит миллионы строк в сжатой модели и связывает несколько таблиц по ключу. На этой связке живут вполне серьёзные отчёты.
Переходить к отдельной панели имеет смысл, когда появляется одно из трёх: второй источник данных, доступ по ролям для сотрудников или обновление без участия человека. Дальше разговор идёт про сервер и права доступа.
Как выглядит результат, видно на демо-стендах: там любую цифру можно разобрать до документа.
Частые вопросы
Можно ли обновлять Excel из 1С автоматически?
Да, через Power Query с подключением к 1С по OData или к выгрузке в папке. Тогда обновление сводится к кнопке «Обновить всё». Полностью без человека это заработает, если файл лежит на сервере с запуском по расписанию.
Excel или Power BI?
Excel быстрее на старте: сборка занимает вечер, учить никого не надо. BI-системы выигрывают на объёме и на раздаче доступов, но требуют человека, который в них работает постоянно. Power BI российским компаниям больше не продают, выбирают среди российских систем. Для одного источника и десятка отчётов разница невелика.
Сколько времени занимает сборка в Excel?
Первая версия занимает от четырёх до восьми часов: вечер, если выгрузка чистая, и рабочий день, если её надо приводить в порядок. Основное время уходит на приведение данных в порядок и на согласование, какая цифра считается правильной.
Можно ли раздать такой файл менеджерам?
Технически да, практически не стоит. В файле видно всё: себестоимость, наценки, результаты коллег. Разграничить это внутри книги нельзя.
Кто это писал
Кирилл Петров, автор Приборки. Переношу управленческие отчёты из Excel на дашборды поверх 1С и CRM, проекты веду сам.