Дашборд в Excel собирают за вечер, а поддерживают годами. Первая версия радует сводными таблицами, тремя диаграммами и срезами по менеджерам. Через какое-то время книга становится тяжелее, пересчитывается с паузой, а в понедельник утром два человека присылают разные итоги по выручке, потому что открыли разные копии файла.
Excel хватает, пока источник один, данные помещаются на лист, а дашборд смотрят один-три человека. Чем больше из этих условий нарушено, тем дороже обходится ручная поддержка. Собирать дашборд в Excel стоит правильно, даже если переход на BI уже в планах, потому что отлаженные показатели потом переедут в систему.
Что такое дашборд в Excel и из чего он собирается
Дашборд в Excel — это один лист, где несколько диаграмм и плиток с ключевыми цифрами отвечают на управленческие вопросы и пересчитываются из общей таблицы данных. От обычного отчета он отличается тем, что вывод виден за секунды. Все цифры собраны на одном экране.
Удобнее всего собирать такой дашборд в три слоя. Внизу лежат данные, то есть плоская таблица, где одна строка соответствует одной продаже, заявке или платежу. Над ней расчетный слой из сводных таблиц и формул, которые превращают строки в показатели. Сверху витрина с диаграммами, плитками и срезами, которыми читатель переключает период или менеджера.
Чаще всего проблемы с дашбордами в Excel начинаются в нижнем слое. Если данные приходят в разном формате, с объединенными ячейками, пустыми строками и датами в текстовом виде, сводные таблицы считают неверно, а красивая витрина показывает ошибку в увеличенном виде. Поэтому сборка начинается с данных и заканчивается диаграммами.
Когда дашборда в Excel хватает
Excel остается разумным выбором, когда задача разовая или стабильная и небольшая, и это нормально. Для первого дашборда компании он часто лучше BI. Покупок не требуется, формулы знакомы всем, кто работал в Excel, и прототип можно набросать за день, чтобы понять, какие показатели вообще нужны руководителю. Показатели, отлаженные в таблице, потом удобно перенести в BI, поэтому прототип редко пропадает зря.
Пять признаков, при которых таблицы справляются:
- источник данных один, например выгрузка из CRM, 1С или рекламного кабинета;
- данных немного, книга открывается и пересчитывается без пауз;
- дашборд смотрят один-три человека, которые не правят его одновременно;
- обновление раз в неделю или реже, и его делает один человек по понятной инструкции;
- всем читателям достаточно одного набора цифр, и разные права доступа не нужны.
Если выполняются все пять признаков, переход на BI принесет мало. Вы заплатите временем за миграцию и получите те же графики. Если не выполняется один признак, дашборд можно оставить в Excel, но стоит заранее выписать, где он сломается первым, чтобы переезд не застал врасплох. Когда не выполняются два признака и больше, пора планировать переход.
Чтобы признаки не оставались абстракцией, полезно примерить их к типовым ситуациям. Небольшой интернет-магазин с одной CRM и еженедельным срезом по каналам вполне живет на одном файле. Руководитель отдела продаж, который раз в неделю смотрит план и факт по менеджерам, обходится сводными таблицами и одним листом с плитками. Финансовый разбор раз в месяц по выгрузке из учета тоже удобнее собрать в таблице, потому что нагрузка редкая, а гибкость формул нужна. Во всех трех случаях источников немного, смотрящих мало, а обновление предсказуемо, и поэтому тяжелая система только добавит работы.
Готовый шаблон дашборда дает компоновку листа, а данные под него вы готовите сами. Структуру приходится подгонять под ваши выгрузки, и шаблон ломается на этом шаге, потому что в нем заложены одни названия столбцов, а в вашей выгрузке стоят другие. Берите шаблон как образец компоновки листа, а таблицу данных стройте под свои источники и свои определения показателей.
Как сделать дашборд в Excel по шагам
Порядок сборки такой. Сначала вопросы, потом данные, потом расчеты, и только в конце диаграммы. Если начать с диаграмм, на выходе получится набор картинок, которые нечем обновлять и которые через месяц разойдутся с реальными цифрами.
Шаг 1, выпишите пять вопросов, на которые дашборд должен отвечать. Например, сколько выручки набралось за период и как она идет к плану, какие каналы приводят заявки, где проседает конверсия. Показатели, которые не отвечают ни на один вопрос, на лист не попадают. Какие цифры выводить на экран, описано в материале про дашборд для бизнеса.
Шаг 2, соберите данные в одну плоскую таблицу. Заголовки стоят в первой строке, объединенных ячеек, пустых строк и промежуточных итогов нет, в каждом столбце хранится один тип значений. Оформите диапазон как умную таблицу, чтобы сводные таблицы и формулы сами подхватывали новые строки без правки диапазонов. Для дашборда продаж обычно хватает шести столбцов. Это дата, источник или канал, менеджер, сумма, статус сделки и идентификатор клиента, по которому потом склеиваются данные из других систем. Лишние столбцы тянут за собой лишние ошибки, поэтому добавляйте их, только когда появился вопрос, на который без них не ответить.
Шаг 3, подключайте выгрузки через Power Query. Начиная с Excel 2016 Power Query встроен на вкладку «Данные», в Excel 2010 и 2013 это отдельная надстройка. Он запоминает шаги очистки данных, например убрать лишние столбцы, привести даты к одному формату или склеить все файлы из папки. Когда приходит новая выгрузка, достаточно нажать «Обновить все», и шаги повторятся сами. Для дашборда из нескольких файлов это очень удобный инструмент, потому что он убирает ручное копирование, на котором легко ошибиться.
Шаг 4, постройте сводные таблицы и вынесите показатели в плитки. На отдельном листе соберите сводные таблицы с выручкой по месяцам, заявками по каналам и средним чеком по менеджерам. Плитки с ключевыми цифрами делайте ссылками на ячейки сводных таблиц. Ручной ввод оставит плитку со старым значением при очередном обновлении, и расхождение заметят не сразу. Показатели, которых нет в сводной таблице из коробки, например долю выполнения плана, считайте формулами рядом со сводной и тоже ссылайтесь на них из плиток.
Шаг 5, добавьте диаграммы и срезы. Динамику показывают линией, категории сравнивают столбцами. Подобрать диаграмму под вопрос поможет статья про визуализацию данных. Срезы и временную шкалу подключайте сразу к нескольким сводным таблицам, тогда один клик по менеджеру перестраивает весь лист.
Шаг 6, закройте лист от случайных правок и опишите порядок обновления. Защитите лист, оставив активными только срезы. Рядом напишите три строки о том, где лежат исходные файлы, что нажимать для обновления и кто за это отвечает. Без такой инструкции дашборд держится на одном человеке и пропадает вместе с его отпуском.
Теперь о ловушках, которые не видны в учебных примерах. При склейке выгрузок из разных систем ломаются ключи. Заказ в CRM и тот же заказ в 1С записаны с разными номерами, один клиент заведен дважды с разным написанием названия, даты в одной выгрузке приходят как текст, в другой как дата. Power Query приведет форматы к единому виду, но договариваться о том, какой номер считать главным, придется людям. Без этого сверка превращается в бесконечную ручную работу.
Вторая ловушка кроется в пересчете. Сводные таблицы обновляются отдельно от формул, и после загрузки новой выгрузки легко забыть обновить одну из них, а плитки на витрине продолжат показывать прежние цифры. Спасает простое правило, которое стоит записать в инструкцию. Обновлять нужно все связанное разом, а на самом дашборде вывести дату последнего обновления, чтобы читатель сразу видел, насколько свежи цифры.
Еще одна типовая ошибка внедрения связана с объемом. Диаграмм на листе набирается много, а смотрят две-три. Дашборд для руководителя должен помещаться на один экран, а остальное лучше убрать на отдельные листы для тех, кто захочет копнуть глубже.
Где Excel ломается: пять порогов
Excel деградирует постепенно, и признаки видны заранее. Больше всего дашборд в Excel стоит рабочего времени на обновление и сверку. Поэтому пороги ниже стоит сверять с реальными делами команды. Сколько из них уже сработало у вас?
Порог 1, размер данных. Лист Excel вмещает 1 048 576 строк и 16 384 столбца, и это жесткий предел. Но неудобно становится гораздо раньше, потому что книга с формулами по сотням тысяч строк открывается и пересчитывается заметно дольше обычного. Для больших объемов Excel умеет загружать данные в модель данных, но в 32-разрядной версии под нее может выделяться до 500–700 МБ, а при других загруженных моделях и надстройках меньше. 64-разрядная версия жестких ограничений на размер файла не накладывает.
Порог 2, число источников. Пока выгрузка одна, Power Query справляется спокойно. Когда в дашборд идут CRM, кабинеты рекламы, 1С и склад, а ключи у них разные, каждая новая связка превращается в отдельный проект по сверке. Здесь начинается сквозная аналитика, которую таблицы тянут плохо.
Порог 3, число пользователей и версий. Файл уходит по почте, и копий становится несколько. Кто-то правит формулу в своей версии, кто-то смотрит прошлую выгрузку. Дашборд, которому нельзя доверять, перестают открывать, и решения снова принимаются по памяти.
Порог 4, частота обновления. Если цифры нужны ежедневно, а обновление занимает час, оно выпадет первым, как только у сотрудника появятся срочные задачи. Данные начинают отставать, и руководитель принимает решения по вчерашней или позавчерашней картине.
Порог 5, права доступа. В таблице сложно показать менеджеру только его клиентов, а директору всю компанию. Приходится делать несколько файлов под разные роли, и каждый из них поддерживается отдельно.
| Порог | Как проявляется | Что делать, пока вы в Excel |
|---|---|---|
| Размер данных | Долгое открытие и пересчет книги | Вынести данные в модель данных, оставить на листе только итоги |
| Число источников | Растет время сверки, цифры расходятся | Договориться об общих ключах, подключать выгрузки через Power Query |
| Пользователи и версии | Несколько копий файла с разными цифрами | Одно место хранения, один ответственный за правки |
| Частота обновления | Данные устаревают, обновление откладывают | Зафиксировать расписание и инструкцию, назначить замену |
| Права доступа | Отдельные файлы под каждую роль | Свести роли к двум-трем и ограничить число файлов |
Отдельный риск таится в самих формулах. Ошибка в одной ссылке или в диапазоне, который не захватил свежие строки, молча искажает итог, и заметить ее без проверки трудно, потому что число выглядит правдоподобно. Чем больше листов связано между собой, тем дороже обходится такая проверка, и тем чаще ее пропускают.
Чтобы увидеть цену ручной поддержки, посчитайте ее по своим цифрам. Возьмем условный пример. Дашборд собирается из трех источников, на выгрузку и сверку каждого уходит по сорок минут, то есть два часа в неделю на один отчет и около ста часов в год. Если таких отчетов три, получается около трехсот часов, почти два месяца работы аналитика, которая не создает ничего нового и целиком состоит из копирования. Сколько часов на сверку уходит у вас?
Excel или BI: сравнение по критериям
BI-система закрывает именно те пороги, на которых ломаются таблицы. Она подключает источники напрямую, обновляется по расписанию и показывает каждому пользователю его срез. Об устройстве таких систем рассказано в статье про BI-систему.
| Критерий | Дашборд в Excel | BI-система |
|---|---|---|
| Источники данных | Файлы и несколько подключений, сверка вручную | Прямые интеграции с CRM, рекламой, учетом |
| Обновление | По кнопке, делает человек | По расписанию, без участия человека |
| Объем данных | До лимита листа, а на деле ниже | Данные лежат в хранилище, лимита листа нет |
| Доступ нескольких людей | Копии файла или общий файл с риском конфликтов | Одна версия, роли и права |
| Старт | За день, без бюджета | От нескольких дней, нужна настройка источников |
| Детализация | Ограничена сводной таблицей | Переход от показателя к сделке |
| Поиск аномалий и прогноз | Вручную формулами | Встроен в части систем |
Есть и разница, которой нет в таблице. Дашборд в Excel часто держится на одном человеке, который помнит, откуда берется каждая цифра и в каком порядке нажимать кнопки. Когда он уходит в отпуск или меняет работу, знание уходит вместе с ним. В BI та же логика записана в самой системе, и ее может прочитать и поправить другой сотрудник.
Выигрыш BI растет вместе с числом источников и пользователей, потому что сверка между ними идет без участия человека. Для одного человека с одной выгрузкой разница невелика, а для команды с несколькими людьми и системами она становится решающей. К тому же BI не отменяет Excel. Разовые расчеты, сценарии и прототипы показателей по-прежнему удобнее делать в таблице, а в систему уходит то, что нужно смотреть каждый день.
Условия покупки зарубежных BI-сервисов для российских компаний менялись, поэтому доступность и способ оплаты стоит уточнять у поставщика заранее. Есть российские платформы и сервисы с бесплатными тарифами, а для небольших команд подходят облачные решения, где не нужны свои серверы. Сравнивайте системы по тому, сколько источников подключается из коробки, кто будет поддерживать модель данных и как система показывает данные разным ролям.
Как связать источники и перенести дашборд из Excel в BI
Переход проходит спокойно, когда его планируют как перенос логики расчетов. Прежде всего сохраните определения показателей, потому что в BI формулы придется задавать заново. Что считается выручкой, какая заявка целевая и как считается конверсия, нужно записать до начала работ.
Порядок переноса:
- Проведите аудит отчетов. Выпишите, какие файлы и листы живы, кто ими пользуется и какие решения по ним принимаются. Часть отчетов можно не переносить вообще.
- Зафиксируйте определения показателей. Для каждой цифры достаточно одной строки с формулой, источником и периодом. Это основа для настройки BI и защита от споров о цифрах.
- Подключите источники по очереди. Начните с самого важного, например с CRM, затем добавляйте рекламные кабинеты и учет. Каждый подключенный источник убирает один ручной шаг.
- Запустите параллельный период. Две-четыре недели дашборд в BI и дашборд в Excel работают рядом, чтобы пройти хотя бы один цикл обновления, а расхождения разбираются до причины. Файл отключается только после того, как цифры совпали.
- Назначьте владельца дашборда. Один человек отвечает за определения показателей и за подключение новых источников, иначе в BI снова появятся разные версии цифр.
Старую книгу после переезда оставьте в архиве как эталон расчетов на случай спорных цифр и как площадку для быстрых гипотез, которые не стоит сразу вносить в общую систему. Дашборд, который смотрят каждый день, должен жить в одном месте и обновляться без участия человека.
Сроки зависят от числа источников и от того, насколько четко записаны определения. Один источник с понятными показателями подключается быстро, а пять систем с разными ключами требуют отдельной работы по сопоставлению клиентов и заказов. Закладывайте это время заранее, и переход не растянется на месяцы из-за споров о том, чья цифра правильная.
Если не хочется строить модель данных вручную, Analyzo подключает рекламные кабинеты, CRM и учет из коробки и сам ищет аномалии в показателях. Excel остается там, где он силен: в разовых расчетах и прототипах, а ежедневный дашборд обновляется без участия человека.
Часто задаваемые вопросы
Можно ли сделать дашборд в Excel для продаж без программирования? Можно. Для типового дашборда продаж хватает умной таблицы, сводных таблиц, диаграмм и срезов. Макросы и формулы массивов нужны только для нестандартных расчетов, а подключение выгрузок берет на себя Power Query.
Как часто нужно обновлять дашборд в Excel? Столько, сколько требуют решения. Для недельного планирования хватает обновления раз в неделю, а для ежедневных решений вручную дашборд не удержать, потому что обновление съедает время, которое проще отдать системе.
Чем отличается дашборд в Excel от обычного отчета? Отчет отвечает на вопрос один раз, дашборд пересчитывается при каждом обновлении данных и показывает ключевые цифры на одном экране. В Excel граница размытая, поэтому ориентир простой. Если файл приходится пересобирать заново, перед вами отчет.
Какой размер данных выдерживает Excel? Жесткий предел листа составляет 1 048 576 строк. Дашборд с формулами замедляется раньше, и тогда данные выносят в модель данных или в BI. В 32-разрядной версии Excel под модель данных может выделяться до 500–700 МБ.
Подойдут ли Google Таблицы вместо Excel? Для небольших дашбордов подойдут. Логика сборки та же, данные в плоской таблице, расчеты отдельно, витрина сверху, а совместная работа в одном файле удобнее. По тем же признакам можно оценить, хватит ли таких таблиц.
Что выбрать для небольшой команды, Excel или BI? Начните с Excel, пока источник один, а дашборд смотрят до трех человек. Когда сработали два порога из пяти, добавляйте BI.
Можно ли использовать готовый шаблон дашборда в Excel? Можно как образец компоновки. Таблицу данных все равно придется строить под ваши выгрузки, и именно на этом шаге шаблоны чаще всего ломаются.
Теряются ли расчеты при переходе на BI? Теряются только те, которые нигде не описаны. Если определения показателей зафиксированы до переноса, а параллельный период показал совпадение цифр, описанные расчеты переносятся полностью.
С чего начать сборку
Откройте текущий дашборд и отметьте, сколько из пяти порогов уже сработало. Это размер данных, число источников, число пользователей, частота обновления и права доступа. Ноль или один порог означает, что Excel справляется, и усилия лучше потратить на порядок в данных и на инструкцию по обновлению.
Два порога и больше означают, что пора планировать переход. Начните с аудита отчетов и списка определений показателей, подключите первый источник и запустите параллельный период. Решение о полном отказе от файла принимайте только после того, как цифры в двух системах совпали несколько обновлений подряд.
Подключите источники и соберите единый дашборд в Analyzo, а Excel оставьте для разовых расчетов и прототипов.