Привет! Сегодня будем опять учиться работать с самой крутой программой в мире! Программой, которая нужна всем и каждому.
Мое знакомство с Excel долгое время ограничивалось простыми таблицами и базовыми формулами. Но однажды, мне поставили задачу: сделать так, чтобы данные по продажам были не просто кучей цифр, а живым, наглядным отчетом, где можно было бы одним кликом посмотреть статистику по любому региону, менеджеру или товару.
«Мне нужен дашборд, как в дорогих системах, но чтобы все было в нашем Excel», — задача звучала как-то так. Я запаниковал, заказ во фрилансе был довольно жирным и терять его (ну и репутацию) не особо хотелось.
Но в счастью, все оказалось решаемым. Excel уже давно все это умеет. И вот как я это сделал.
Что такое дашборд и зачем он нужен?
Начну с прелюдии. Представьте приборную панель в автомобиле. Вы одним взглядом видите скорость, уровень топлива, обороты двигателя. Дашборд в Excel — это такая же «приборная панель» для ваших данных.
Он объединяет ключевые метрики (KPI) на одном листе и, что самое главное, позволяет их фильтровать «на лету». Хотите увидеть данные только за 3-й квартал по клиентам из Москвы? Пожалуйста — два клика, и все графики и цифры мгновенно перестроились.
Шаг 1: Подготовка «чистых» данных — фундамент всего
Это самый скучный, но самый важный этап. Ваши исходные данные должны быть в формате умной таблицы.
- Выделите любую ячейку в вашем массиве данных.
- Нажмите Ctrl + T и подтвердите.
- Почему это важно? Умная таблица автоматически расширяется при добавлении новых строк, с ней удобно работать, и она станет идеальным источником для всех последующих элементов.
Главное правило: одна строка — одна запись (одна продажа, один визит, одна транзакция). Никаких объединенных ячеек, пустых строк и столбцов.
Шаг 2: Волшебные инструменты: срезы и временная шкала
Вот она, магия без программирования! После создания умной таблицы на ленте появляется вкладка «Конструктор таблиц».
Срезы
Это визуальные кнопки-фильтры. Выбираете на вкладке «Вставка» -> «Срез». Excel предложит выбрать поля из вашей таблицы (например, «Регион», «Менеджер», «Товарная группа»). Появятся стильные кнопки. Кликая по ним, вы фильтруете всю исходную таблицу.
Временная шкала
То же самое, но для дат. Вставьте ее через меню «Срез», если у вас есть столбец с датами. Появится удобный ползунок для выбора периода: год, квартал, месяц, день. Невероятно наглядно!
Секретная мощь: Один срез можно привязать сразу к нескольким таблицам или диаграммам! После создания среза кликните на него правой кнопкой мыши, выберите «Связи отчетов...» и отметьте все сводные таблицы и диаграммы, которые должны реагировать на этот фильтр. Это и есть основа динамики.
Шаг 3: Сводные таблицы и сводные диаграммы — сердце любого дашборда
Срезы управляют данными, а визуализируют их сводные таблицы и диаграммы. Они гибкие и мгновенно реагируют на фильтры.
- Создайте сводную таблицу на основе вашей умной таблицы (Вставка -> Сводная таблица).
- Перетащите нужные поля в области «Строки», «Столбцы» и «Значения». Например, «Менеджер» в строки, а «Сумма продаж» в значения.
- На основе этой сводной таблицы тут же постройте диаграмму (Анализ сводной таблицы -> Сводная диаграмма). Гистограмма, линейная, круговая — выбирайте под задачу.
Важный лайфхак: Создавайте несколько разных сводных таблиц и диаграмм на одном листе, каждая из которых показывает свой срез данных: общий объем, динамику, структуру. И привязывайте их ко всем срезам.
Шаг 4: Динамические заголовки и KPI
Чтобы ваши графики не висели в воздухе, им нужны понятные подписи и ключевые цифры.
- Заголовок диаграммы: Не пишите его вручную. Сделайте его динамическим. Создайте в отдельной ячейке текст с помощью функции СЦЕПИТЬ или &. Например: ="Динамика продаж по регионам: " & ТЕКСТ(МАКС(Таблица1[Дата]);"мммм. гггг"). Эта формула автоматически подставит в заголовок последнюю дату из вашей таблицы.
- Ключевые показатели (KPI): Вынесите в отдельные блоки главные цифры: «Общая выручка», «Средний чек», «Количество сделок». Используйте для их расчета функции для работы с умными таблицами, например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Эта функция уникальна — она учитывает только видимые после фильтрации строки. Формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица1[Выручка]) покажет сумму выручки, которая будет меняться при применении срезов!
Шаг 5: Компоновка и дизайн — создаем единое пространство
Теперь собираем весь пазл.
- Отведите под дашборд отдельный чистый лист. Назовите его «Дашборд».
- Аккуратно разместите на нем:
- 1. Блок с KPI (ваши «цифры»).
- 2. Срезы и временную шкалу (лучше сгруппировать их в одном углу).
- 3. Ваши сводные диаграммы и таблицы.
- Уберите всю сетку: вкладка «Вид» -> снимите галочку с «Сетка». Так дашборд будет выглядеть профессионально.
- Используйте заливку фона и рамки, чтобы визуально разделить зоны. Но без фанатизма — ничего не должно отвлекать от данных.
Шаг 6: Финальные штрихи
- Защита листа: Чтобы случайно не сдвинули выровненные элементы, защитите лист (Рецензирование -> Защитить лист). Разрешите только выделение ячеек, но не их изменение.
- Обновление: Когда вы добавите новые данные в исходную умную таблицу, просто обновите все сводные таблицы (правой кнопкой по любой из них — «Обновить»), и дашборд покажет актуальную информацию.
Что у меня получилось в итоге?
Я создал лист, на котором есть:
- Три ключевых показателя в крупных цветных блоках.
- Срезы по «Региону» и «Товарной группе» и временная шкала.
- Гистограмма с динамикой продаж по месяцам.
- Круговая диаграмма с долей менеджеров.
- И все это связано. Кликаю на срез «Центр» и временную шкалу «Q3» — и все цифры и графики мгновенно показывают картину только по центральному региону за 3-й квартал.
Не скажу что это будет просто для новичков, но для тех, кто уже успел поработать с Excel - сложного ничего нет.
Почему? Потому что Excel — это не просто таблицы, это мощный инструмент визуализации, который ждет, когда вы начнете использовать его возможности.
Начните с простой умной таблицы и одного среза. Вы увидите, как ваши данные оживут, и вам сразу захочется большего. Немного практики и вы набьете руку! Удачи в создании вашего первого дашборда в самой лучшей программе в мире!
Статьи по теме:
Это только часть статей, все остальные вы всегда можете найти у меня на канале, в рубрике, которая так и называется - "Excel". Сейчас там 25 написанных статей. Цель - довести их количество хотя-бы до сотни, так как тема интересна и вам, моим подписчикам, и мне лично.
🔔🔔🔔 Было полезно? Ставьте лайк, подписывайтесь на наш канал и телеграмм, в котором мы публикуем анонсы новых курсов, новости, акции и скидки на обучении более чем 50 российских онлайн-школ.
С нами вы всегда будете в курсе того, что происходит на российском рынке онлайн-образования!