Как собрать дашборд в Excel из сводной таблицы, диаграмм и кнопок

Задача руководителя простая: открыть один лист с дашбордом и за минуту понять, выполнен ли план, какой регион просел и куда идёт выручка. Справка Microsoft описывает сборку такой панели от одной таблицы-источника: данные без пропущенных строк и столбцов, где каждая строка — одна запись, затем сводные таблицы и сводные диаграммы^[1].

Путь к первой панели выглядит так. Сначала выберите три–пять вопросов, на которые она отвечает, и инструменты под них. Затем освойте базу — таблицу-источник и сводную, попробуйте сборку на учебных данных в тренажёре и примените её к своим данным из отчётов. Главная ошибка новичка — начинать дашборд с оформления: красивые плитки без связанных фильтров быстро превращаются в картинку, которую никто не обновляет.

Ключевые факты: дашборд в Excel

• Дашборд в Excel — наглядное представление ключевых метрик на одном листе, чтобы быстро просмотреть и проанализировать данные^[1].
• На панели срез после вставки связан только со сводной, из которой его создали; остальные подключают через Report Connections^[1].
• Функция СУММЕСЛИМН для карточек KPI принимает до 127 пар диапазонов и условий^[2].
• Шкала дат на панели отличается от кнопочного фильтра тем, что задаёт непрерывный период, а не набор значений^[3].
• Сводные на листе панели не должны перекрываться, поэтому под каждую оставляют место для роста^[1].
• Чтобы панель показала свежие цифры, справка Microsoft советует запускать Refresh All — эта команда обновляет каждую сводную книги разом^[4].

Что такое дашборд в Excel и из чего он состоит

Дашборд в Excel собирает главные показатели и графики одного отчёта на одном листе. Справка Microsoft строит пример из четырёх сводных таблиц и диаграмм на одном источнике данных и добавляет к ним срезы и временную шкалу^[1]. Программировать для этого ничего не нужно: каждый элемент — штатный инструмент Excel.

Панель отличается от обычного отчёта тем, что все её элементы смотрят на один источник и меняются вместе. Щелчок по срезу «Север» перестраивает подключённые сводные и их диаграммы, а карточки KPI пересчитываются формулами. Поэтому сначала проектируют связи, а уже потом цвета и шрифты.

Дашборд в Excel умеет то, чего не умеет обычный отчёт: показывать один и тот же набор цифр в разных разрезах по щелчку, не копируя листы. Руководитель выбирает регион — меняются и таблица лидеров, и график динамики, и структура продаж. Дальше по странице — данные, сборка, KPI и фильтры, а подробности каждого инструмента вынесены на отдельные страницы тренажёра.

Перед сборкой панели набросайте макет на бумаге: сверху карточки KPI, под ними пара графиков, сбоку фильтры. Такой эскиз сразу показывает, сколько сводных понадобится и какие поля войдут в каждую. Удобно держать три листа: «Данные» с таблицей-источником, «Сводные» с промежуточными отчётами и сам лист панели.

Разобравшись с устройством, переходим к данным и числу показателей — от них зависит всё остальное.

Сколько показателей выносить на дашборд и как подготовить данные

Дашборд держится на одной таблице-источнике, и справка Microsoft задаёт ей три условия: данные оформлены таблицей Excel, в них нет пропущенных строк и столбцов, каждая строка — одна запись^[1]. Пример источника на листе «Данные»:

Правила, без которых панель начнёт показывать неверные цифры:

• одна строка — одна операция, без промежуточных итогов и пустых строк-разделителей;
• даты хранятся настоящими датами, а не текстом, иначе временная шкала их не увидит;
• одно поле — один столбец: регион отдельно, менеджер отдельно;
• в шапке нет объединённых ячеек, у таблицы одна строка заголовков;
• в столбце сумм только числа: прочерк или «нет данных» превращают столбец в текстовый.

Если источник собирается из нескольких выгрузок, сначала сведите их в один лист с одинаковыми столбцами: панель на одном источнике проще подключать к общим фильтрам.

Число показателей решает, будут ли смотреть панель. Три–пять карточек и два–три графика читаются за минуту; десяток плиток и графиков превращает лист в ещё один отчёт, где главное теряется. Каждая карточка должна отвечать на вопрос, который руководитель реально задаёт: сколько продали, выполнен ли план, где провал, кто лидирует.

Оформление таблицей Excel даёт второй плюс: новые строки попадают в источник без ручного расширения диапазона, а сводные подхватывают их при обновлении.

Однако подготовленные данные — ещё не панель: сборка устроена иначе, чем обычный лист, и идёт строго по шагам.

Как собрать дашборд по шагам: сводные, диаграммы, фильтры

Дашборд собирают по шагам: таблица-источник, сводные таблицы, сводные диаграммы и фильтры. Английская справка Microsoft называет этот путь для Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365^[1].

1. Выделите ячейку таблицы-источника и вставьте сводную на новый лист: Insert → PivotTable → New Worksheet.
2. Настройте первую — главную — сводную, затем скопируйте её на свободные места листа и перестройте копии под другие разрезы.
3. Оставьте между сводными пустое место: справка предупреждает, что сводные не могут перекрываться и им нужен запас для роста^[1].
4. Для каждой сводной вставьте сводную диаграмму: PivotTable Analyze → Tools → PivotChart, затем выберите тип — в примере справки это комбинированная диаграмма.
5. Добавьте фильтры: PivotTable Analyze → Filter → Insert Slicer или Insert Timeline.
6. Подключите каждый срез ко всем сводным через Slicer → Report Connections.
7. Перенесите диаграммы и срезы на лист панели и снимите флажки Headings и Gridlines на вкладке View^[1].

Копии главной сводной удобнее новых: они уже смотрят на тот же источник, и подключение фильтров не потребует перестройки. Шестой шаг пропускают чаще всего. Справка прямо пишет, что срез связан только со сводной, из которой его создали^[1], поэтому без Report Connections остальные графики на щелчок не реагируют.

Перед отправкой коллегам справка советует проверить каждый срез и временную шкалу. Делиться готовым файлом она предлагает через группу Microsoft (Microsoft Group); для обычной рассылки годится и копия книги.

Соберите панель с карточками KPI, сводной и срезом в тренажёре: урок по шагам проверит формулы карточек, сводную по каналам, гистограмму и срез по категории.

Справка описывает тот же порядок и для версий с Mac: срезы там доступны в Excel для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac^[5]. Названия вкладок ниже даны по английской справке — в русском интерфейсе они переведены.

Разобравшись со сборкой, переходим к карточкам KPI — единственной части панели, которую считают формулами.

Карточки KPI на формулах: пример расчёта плана

Карточка KPI на дашборде — формула на листе, а не элемент сводной таблицы. Сумма за период удобно считается функцией СУММЕСЛИМН: справка Microsoft приводит синтаксис СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; …) и разрешает до 127 пар диапазонов и условий^[2].

Формулы карточек для источника выше:

• выручка за период в H1: =СУММЕСЛИМН(C2:C6;A2:A6;">="&G1;A2:A6;"<="&G2) — вернёт 1005000;
• выполнение плана в H2: =H1/G3 с процентным форматом — 84%;
• подпись в H3: =ЕСЛИ(H2>=1;"план выполнен";"до плана "&ТЕКСТ(1-H2;"0%")) — до плана 16%;
• число заказов в H4: =СЧЁТЕСЛИМН(A2:A6;">="&G1;A2:A6;"<="&G2) — 3.

Результаты указаны для данных из таблицы-источника выше. Границы периода вынесены в ячейки G1 и G2, поэтому смена месяца не требует правки формул: достаточно ввести новые даты. Функция работает в Excel 2016, Excel 2019, Excel 2021, Excel 2024, Microsoft 365 и в Excel Web App^[2]. Средний чек — частное выручки и числа заказов: =H1/H4 вернёт 335000.

Карточку оформляют крупным числом и короткой подписью: что показано и за какой период. Подпись из ЕСЛИ сразу говорит, выполнен ли план, и избавляет читателя от расчёта в уме. Дашборд в Excel с такими карточками читается сверху вниз: сначала итог, затем графики с объяснением, откуда он взялся.

Карточки не реагируют на срезы сводных: формулы считают по листу «Данные», а не по отфильтрованному отчёту. Если карточка должна меняться вместе с фильтром, ссылайтесь на ячейку итога сводной или вынесите регион в отдельную ячейку-условие.

Остаётся понять, чем управлять панелью: срезом, шкалой или фильтром отчёта.

Срез или временная шкала: чем фильтровать панель

Срез и временная шкала превращают дашборд в интерактивный: щелчок меняет все подключённые сводные. Срез фильтрует по отдельным значениям поля, а временная шкала — по периоду с уровнями годы, кварталы, месяцы и дни^[3].

Общий фильтр связывается только со сводными на том же источнике данных^[5]. Поэтому все сводные панели строят из одной таблицы-источника: иначе в списке Report Connections часть отчётов просто не появится.

Фильтр отчёта внутри сводной работает только для неё одной, поэтому для общей панели он годится редко: читатель не поймёт, почему один график изменился, а соседний нет. Шкалу дат и фильтр по тому же полю дат совмещают только после флажка Allow multiple filters per field в параметрах сводной^[3].

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

Именно поэтому до рассылки панели стоит проверить не только фильтры, но и границы самого подхода.

Когда дашборд в Excel не подойдёт

Дашборд в Excel не подойдёт, если графики построены на разных источниках данных или цифры должны обновляться без участия человека. Обновление сводных справка описывает как команду: Refresh All для всех отчётов книги, Alt+F5 для выбранного и флажок Refresh data when opening the file для обновления при открытии^[4].

• Если графики питаются из разных таблиц, общий срез их не объединит: он подключается только к сводным с тем же источником^[5].
• Когда данные меняются каждую минуту, а файл открывают редко, панель покажет вчерашние цифры, пока её не обновят командой Refresh All.
• Если сводные стоят вплотную, при росте одна упрётся в другую: справка требует оставлять место, потому что сводные не могут перекрываться^[1].
• При работе в Google Таблицах панель собирают другими средствами: срез добавляют через Data → Add a slicer, и он не влияет на формулы с теми же данными^[7].
• Если карточки KPI должны следовать за срезом, формулы на листе этого не сделают без ссылок на итоги сводных.

Загрузка из внешних систем и чистка данных — отдельный шаг подготовки источника. Файл для десятков людей с разными правами неудобно рассылать копиями: версии расходятся, и каждый смотрит свои цифры. Справка Microsoft для совместного доступа предлагает группу Microsoft^[1] — тогда все открывают один файл.

Для небольшого отдела панель на сводных закрывает большинство ежемесячных вопросов: сколько продали, где отставание, кто лидирует. Создание самих отчётов описано на странице про сводные таблицы с нуля, выбор графиков — на странице о типах диаграмм под задачу; остальные темы — среди уроков тренажёра по анализу данных и отчётам.