Что такое дашборды в excel
Аналитика данных: как построить дашборд в Excel
В бизнесе сложно добиться системного роста, если регулярно не отслеживать ключевые показатели, которые влияют на прибыльность компании. Для этого лучше всего подходят дашборды, в которых данные представлены в понятном виде, что существенно облегчает принятие решений.
Пошагово рассмотрим, как построить дашборд по продажам в Excel. Статья будет полезна всем, кто начинает знакомство с этим мощным инструментом аналитики данных.
ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCELLENT
Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям
Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.
Основные задачи дашборда:
Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.
Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.
Чаще всего созданием дашборда занимается аналитик — он обрабатывает огромные массивы данных, оформляет их в красивый и понятный дашборд и передаёт заказчику задачи. Это могут быть руководители, менеджеры по продажам, HR-специалисты, бухгалтеры, маркетологи.
Менеджерам по продажам дашборд помогает управлять продажами. HR-специалистам ― отслеживать основные метрики, связанные с трудовыми ресурсами. Для бухгалтера будет полезен дашборд о движении средств, который отражает финансовое состояние организации. Маркетологи анализируют рекламные кампании и оценивают их эффективность. Руководителю дашборд позволит быстро оценивать состояние ключевых показателей и принимать управленческие решения.
Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.
Главное и самое интересное в дашборде ― интерактивность.
Настроить интерактивность можно с помощью следующих приёмов:
Создаём классический дашборд для руководителя отдела продаж
Для этого выбираем наиболее популярный способ с помощью сводных таблиц.
Советуем проделать все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может появиться лишь в ходе чтения инструкций». Файл с данными для тренировки можно скачать здесь.
Собираем данные
Построение любого дашборда начинается со сбора данных. На этом этапе важно привести таблицы в плоский вид, чтобы в дальнейшем на их основе создавать сводные таблицы для дашборда.
Плоская таблица (flat table) ― двумерный массив данных, состоящий из столбцов и строк. Столбцы ― это информационные атрибуты таблицы, строки ― отдельные записи, состоящие из множества атрибутов.
Пример плоской таблицы:
В примере выше атрибуты — это «Наименование», «День», «Год», «Склад», «Продажи (тыс. руб)», «Менеджер», «Заказчик». Они вынесены в заголовок таблицы.
Эта таблица послужит основой для построения нашего дашборда по продажам.
Выбираем макет дашборда и цели
Если известно, для чего и для кого предназначен дашборд, легче понять, какие показатели должны выводиться на экран. Это могут быть любые количественные показатели, важные для организации: прибыль, продажи, численность сотрудников, количество заявок, фонд оплаты труда.
Также необходимо определиться с макетом — структурой — дашборда. Для начала достаточно будет прикинуть её на листе формата А4.
Пример универсальной структуры, которая подойдёт под любые задачи:
Количество информационных блоков может быть разным: это зависит от того, сколько метрик надо отразить на дашборде. Главное — соблюдать выравнивание по сетке.
Порядок и симметрия в расположении информационных блоков помогают восприятию и внушают больше доверия.
Помимо симметрии важно учитывать и логику расположения информационных блоков. Это связано с нашим восприятием: мы привыкли читать слева направо, поэтому наиболее важные метрики необходимо располагать слева направо и сверху и вниз, менее важные ― справа внизу:
Построим несколько сводных таблиц по продажам
— на основе таблицы с данными, приведённой выше в качестве примера плоской таблицы.
Таблицы будут показывать продажи по месяцам, по товарам и по складу.
Как сделать простой дашборд в Excel
Простой дашборд в Excel
14 апреля 2021 г. 1579
Введение
Дашборд – это умный отчет в режиме реального времени. Главное условие создания дашборда – наличие данных, которые можно анализировать. Благодаря такой инфопанели руководители и менеджеры имеют возможность отслеживать наиболее важные показатели в режиме реального времени и быстро реагировать. Конечно, не каждому бизнесу нужно отчитываться каждую секунду, в большинстве случаев это еженедельная или ежемесячная периодичность.
Для создания дашборда не нужно быть профессиональным аналитиком, достаточно иметь знания, которые позволят вам определять самые важные показатели из большого массива данных и отображать их на итоговой панели.
Золотые правила дашборда
Зачем нужен дашборд?
Чаще всего дашборды используются для визуализации бизнес-процессов, для отслеживания ключевых показателей эффективности, для сбора информации о состоянии веб-сайтов, для мониторинга производства. Список можно продолжать ещё, мы перечислили наиболее яркие примеры.
Данные есть в любой сфере деятельности. А если есть данные, то их можно и нужно проанализировать!
Интерактивный дашборд в Excel
В качестве примера мы решили рассмотреть достаточно актуальный, простой и общедоступный кейс – мониторинг продаж.
Для простейшего дашборда мы использовали стандартные виджеты – круговую диаграмму для категорий, карточки с показателями, столбчатую диаграмму для сравнения показателей, таблицу с рейтингом, а также задействовали фильтрацию данных, для возможности ежемесячного просмотра отчета.
В этом видео мы хотим показать вам, как легко визуализировать данные. Важно использовать правильные инструменты и следовать основным статистическим принципам
Рабочие файлы по ссылке ниже
Заключение
Тем, кому до сих пор сложно создавать подобные дашборды, мы рекомендуем собрать список всех имеющихся данных о вашем бизнесе, сформулировать свои пожелания и отправить их нам на mail@birdyx.ru. Мы составим полезные и наглядные сводки данных, которые изменят направление вашего бизнеса или жизнедеятельности к лучшему.
Зависимый выпадающий список в Excel и Google таблицах
20 июля 2021 г. 3719 MS Excel Video File Google Sheets
В этой статье мы расскажем, как создавать зависимые выпадающие списки в Excel и Google таблицах. Рассмотрим автоматическое обновление раскрывающегося списка при добавлении новых данных к исходным данным.
Простая CRM-система в Google таблицах
2 мая 2021 г. 1745 Google Sheets Code Video File
В этой статье мы рассмотрим что такое CRM-система и для чего ее используют, а также покажем, как создать простую CRM-систему в Google таблицах.
Красивая и современная CRM + динамический дашборд в Excel
10 декабря 2021 г. 140 MS Excel Video
Обзор умной, автоматизированной таблицы Excel для контроля бизнес показателей: динамика продаж, эффективность сотрудников, рассылки WhatsApp
Расшифровка показателей анализа состава тела
27 июня 2021 г. 735 Info Health
Подробная расшифровка показателей анализа состава тела. Важно правильно интерпретировать результаты для снижения рисков заболеваний и достижения результатов
Марк Мур. Дашборды в Excel
Дашборд или панель управления – это тщательно отобранная и визуально поданная информация, способствующая принятию качественных управленческих решений. Как правило, дашборды должны автоматически обновляться при добавлении/изменении данных. Дашборды используют сводные таблицы, динамические диаграммы и иные отчеты, консолидирующие данные. Настоящая заметка представляет собой краткий перевод книги
Скачать заметку в формате Word или pdf, готовые примеры и пошаговые инструкции в формате Excel
Moore, Mark. Mastering Excel: Building Dashboards
Уровни Excel
Чтобы сделать модели Excel максимально гибкими, я предлагаю использовать концепцию уровней. Гибкость подразумевает:
Уровень данных – это данные, импортированные из Oracle, SAP, … или набранные в Excel. Каждый столбец должен иметь заголовок и содержать схожие данные. Например, если столбец имеет заголовок Имя, то он должен содержать только имена. Не вставляйте идентификатор. Добавьте еще один столбец для идентификатора. Нет смысла экономить столбцы. Их в Excel 16 000, так что используйте столько, сколько вам нужно.
Данные будут ограничены первой полностью пустой строкой и первым полностью пустым столбцом. Не добавляйте пустые строки, чтобы сделать данные более удобочитаемыми, не вставляйте подитоги, не выделяйте ячейки цветом или иным образом. Данные не предназначены для того, чтобы их изучать. Они служат лишь для хранения. Изучать вы будете отчеты. Их мы и будем делать визуально привлекательными.
А вот что нужно сделать с данными, так это оформить их в виде таблицы (рис. 1). Это позволит обращаться к данным, как к единому массиву. Если вы добавите новые строки, ссылки обновятся автоматически. И вы по-прежнему будет обращаться ко всем данным сразу. Чтобы превратить данные в таблицу встаньте на любую ячейку внутри данных нажмите Ctrl+T (английское).
Рис. 1. Данные; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Уровень отчета – это то, что все видят. Он содержит диаграммы, сводные таблицы, кнопки, переключатели и т.п. Он красиво оформлен, включает логотипы и т.д. Это уровень, который печатается и отображается в презентации.
Уровень бизнес-логики – это то, что связывает уровень отчета с уровнем данных. Это формулы и иные средства вычисления, которые извлекают данные из уровня данных и преобразуют их в информацию.
Дашборды размещаются на уровне отчета. Дашборды преобразует необработанные факты (уровень данных) в информацию (уровень отчета). Пользователи могут выявить тенденции, степень достижения целевых показателей или определить проблемные области (проблемных сотрудников), которые заслуживают внимания.
Набор отчетов
Прежде чем вы начнете строить что-либо в Excel, подумайте о своей аудитории. Кто будет использовать панель? Что они захотят на ней увидеть? Не полагайтесь лишь на свою интуицию. Если есть возможность, спросите у пользователей. Их точка зрения важнее вашей. Не делайте в Excel что-то оригинальное, о чем вы недавно прочли, и считаете, что это круто. Вы можете подумать, что создадите имидж эксперта. Но, если вы не решите проблемы пользователя, ваши усилия будут бесполезны. И именно такой имидж может за вами закрепиться.
Панели, как правило, не существуют изолированно; они являются частью набора отчетов. Один дашборд не ответит на все вопросы. Возможно, нужна еще одна панель с более глубоким уровнем детализации. А затем еще и отчет, который раскроет нюансы, не отраженные на дашборде.
Когда следует использовать панель, а когда отчет? Панель предназначена для быстрого визуального отображения фактов. Отчет содержит более подробные данные в структурированном формате. Отчет может содержать панель, а вот панель не может включать отчет.
Различают три типа панелей.
Стратегические – панели высокого уровня, используемые менеджерами для отслеживания ключевых показателей. Такие панели не содержат деталей, а их структура изменяется редко. Например, для торговой компании KPI могут включать объем и рентабельность продаж, размер дебиторской задолженности.
Аналитические – панели, выявляющие тенденции и причины, лежащие в их основе. Анализ, как правило, охватывает несколько периодов времени и несколько переменных. Такие панели часто используют интерактивные элементы, добавляющие гибкости при анализе.
Операционные – панели, предоставляющие подробную информацию; часто в реальном времени. Они информируют пользователей о состоянии конкретного процесса и выявляют отклонения от нормы. Чтобы создать такие панели в Excel потребуется подключение к внешним данным.
Планирование панели
Для начала я предлагаю набросать дизайн панели на бумаге или доске. Поймите, какой смысл будет передавать панель, а уж потом сделайте выбор в пользу того или иного типа диаграммы или отчета.
В Excel нет формулы, которую можно использовать для быстрого создания панели. Панель – это сочетание различных элементов, объединенных для визуализации информации. Думайте о дашборде, как о конструкторе Лего. В Excel кубиками являются формулы (СУММ, СУММЕСЛИ, СУММЕСЛИМН, СМЕЩ, ВПР), диаграммы, сводные таблицы, элементы управления и др.
Мы построим две панели, основанные на финансовых данных и данных о продажах.
Панель Финансы
Вот что у нас должно получиться:
Рис. 2. Панель Финансы
Откройте Excel-файл Готовые примеры, и поэкспериментируйте с интерактивными элементами. Выберите квартал, и две диаграммы автоматически обновятся, а мини-отчет в левом нижнем углу изменит форматирование. Если вам интересно вникнуть в детали построения дашборда, откройте файл Пошаговые инструкции, и выполняйте действия, как описано далее.
Исходные данные (см. рис. 1 и лист Финансы) представляют собой непрерывную область, ограниченную пустым столбцом и пустой строкой. Данные преобразованы в таблицу. Исходные данные невозможно представить на диаграмме, поэтому их группировку выполните на отдельном листе. Я называю такие листы промежуточными. Именно на их основе строятся диаграммы. После завершения работы по созданию панели, листы с данными и промежуточные листы можно защитить от изменения, а затем скрыть, чтобы пользователи случайно не порушили их.
Вставьте новый лист. Назовите его Фин_промежут. Построим элементы панели один за другим (см. рис. 2).
Единиц за период
Для начала на основе исходных данных листа Финансы создайте сводную таблицу на листе Фин_промежут (рис. 3). Затем на том же листе вставьте гистограмму и срез по кварталам. Уберите лишние элементы, добавьте подписи данных. Уменьшите количество цифр в подписях (подробнее см. Принцип Эдварда Тафти минимизации количества элементов диаграммы, Срезы сводных таблиц, Пользовательский формат числа в Excel раздел Некоторые дополнительные возможности форматирования). Вставьте новый лист. Назовите его Фин_панель. Переместите на него диаграмму. Обратите внимание: заголовок диаграммы не набран, а является ссылкой на ячейку А1.
Рис. 3. Сводная диаграмма Единиц за период
Выручка по регионам
Выделите ранее созданную сводную диаграмму скопируйте ее в буфер, и вставьте на свободное место на листе Фин_промежут. Располагайте сводные таблицы приблизительно в том же положении, что и диаграммы на панели. Так будет удобнее управлять ими. Измените настройки новой сводной таблицы (рис. 4). Обратите внимание: новая сводная ссылается на тот же срез, что и первая. Вставьте и отформатируйте диаграмму. Вырежьте ее и перенесите на лист Фин_панель. Вырежьте и перенесите на лист Фин_панель срез.
Рис. 4. Выручка по регионам
Если срез не изменяет диаграмму, щелкните правой кнопкой мыши на срезе, выберите Подключение к отчетам, установите галочки напротив тех отчетов, к которым вы хотите подключить срез (рис. 5). Обратите внимание, что срез не обновляет диаграмму (не подключается к диаграмме). Срез обновляет сводную таблицу, на основании которой построена диаграмма. Отформатируйте срез так, чтобы он был ориентирован горизонтально.
Рис. 5. Подключение среза к сводным таблицам
Выручка по продуктам
Для разнообразия этот отчет сделан не на основе сводной таблицы, а с помощью формул. В отчете будет выделен квартал, выбранный срезом (если он один). Для начала создадим уникальный список продуктов. Перейдите на лист Финансы. Выделите столбец Е. Скопируйте его. Вставьте в столбец I. Пройдите по меню Данные –> Удалить дубликаты (рис. 6).
Рис. 6. Создание уникального списка продуктов
Вырежьте список продуктов, и вставьте его на лист Фин_панель под левой диаграммой. Небольшая проблема: названия продуктов не вписываются в столбец B. Если же вы увеличите ширину столбца B, это изменит размер диаграммы. Решение: установить размер диаграммы неизменным. Выделите диаграмму. Кликните на диаграмме правой кнопкой мыши и выберите Формат области диаграммы. Перейдите на закладку Размер и свойства, и в области Свойства установите переключатель в позицию Не перемещать и не изменять размеры (рис. 7). Повторите эти действия и для второй диаграммы.
Рис. 7. Как сделать размер диаграммы не зависящим от размера ячеек
Формула в ячейке С21 содержит смешанные ссылки. Она подготовлена для копирования по диапазону С21:F26. Этой же цели служит и выбор ссылок на целые столбцы (Финансы!$G:$G), а не на столбцы Таблицы (Финансы[Сумма]). Последние, к сожалению, поддерживают только формат относительных ссылок.
Рис. 8. Формула СУММЕСЛИМН() отчета
Квартал, выбранный в срезе, автоматически выделяется в отчете. Эта сделано с использованием условного форматирования. Значение в срезе нельзя непосредственно использовать при форматировании. Однако срез изменяет поле Фильтры сводные таблицы, которое можно использовать для форматирования отчета (рис. 9).
Рис. 9. Условное форматирование на основе номера квартала
Вот, какой вид мы хотим придать отчету:
Рис. 10. Фрагмент отчета, выделенный условным форматированием
На самом деле здесь не один, а три условных формата для ячеек: D20, D21:D26, D27. Поэтому нужно создать три различных правила для ячеек С20:F20, С21:F26 и С27:F27. Во-первых, выберите ячейки C20:F20 (или иные ячейки с номерами кварталов). Пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек (позиция 1 на рис. 11). Введите формулу (2):
Рис. 11. Формула условного форматирования для ячеек С20:F20
Как обычно, обратите внимание на формат ссылок. С20 – относительная; мы хотим, чтобы ссылка менялась при переходе к ячейке D20 и далее. Фин_промежут!$I$1 – абсолютная; мы хотим, чтобы значение одной из ячеек в диапазоне С20:F20 всегда сравнивалось с одной и той же ячейкой на листе Фин_промежут. Нажмите Формат (3), перейдите на закладку Граница, и выберите тип линии и тип границы (рис. 12). Перейдите на закладку Шрифт и выберите полужирный.
Рис. 12. Форматирование ячеек из диапазона С20:F20
Повторите манипуляции для диапазонов С21:F26 и С27:F27. Самое сложное во всем этом –формула. Если не догадались, то вот вам подсказка =C$20=Фин_промежут!$I$1. По этой формуле применение формата к диапазонам С21:F26 и С27:F27 основано на содержимом в ячейке С20. К сожалению, если выбрать на срезе более одного квартала, условное форматирование не работает.
Годовая выручка по регионам
Эта диаграмма просто отображает вклад каждого региона в годовую выручку. Поскольку данные основаны на полном годе, эта диаграмма не взаимодействует со срезом. Из-за того, что диаграмма использует данные за весь год, вы не можете скопировать одну из сводных диаграмм Фин_промежут. Любая копия будет использовать тот же кеш, что не позволит вам для одной сводной таблицы выбрать квартал, а для другой – все кварталы. Поэтому вы должны создать новую сводную на основе тех же исходных данных с листа Финансы. Вставьте круговую диаграмму, отформатируйте ее, а затем перенесите на лист Фин_панель (рис. 13).
Рис. 13. Годовая выручка
Навигация
Расположите на панели Финансы синюю кнопку для перехода на панель Продажи. Для этого перейдите на лист Фин_панель. Пройдите по меню Вставка –> Фигуры. Нажмите на фигуру по вашему выбору (я использовал закругленный прямоугольник, рис. 14). Нажмите и перетащите фигуру на лист. Придайте ей желаемый размер.
Рис. 14. Прямоугольник для кнопки перехода
Щелкните на фигуре правой кнопкой мыши. Выберите Изменить текст. Введите На панель Продажи. Отформатируйте текст, разместите его по центру кнопки. Создайте лист Панель_Продажи (если вы собираетесь создать ссылку на него, он должен существовать!). Щелкните правой кнопкой мыши на кнопке, выберите Гиперссылка. В появившемся окне выберите Место в документе, а затем укажите ячейку A1 на листе Панель_продаж (рис. 15).
Рис. 15. Настройка гиперссылки
Сделайте лист Фин_панель визуально более интересным. Больше похожим на лист бумаги, а не на лист Excel. Перейдите на вкладку Вид и снимите галочки с опций Сетка и Заголовки. То, что у вас приблизительно должно получиться изображено в начале заметки на рис. 2.
Панель Продажи
Панель Продажи будет основана на данных с листа Продажи. Чтобы не повторяться, на ней мы отработаем новые функции. Вот, что у вас должно получиться:
Рис. 16. Панель Продажи
Создайте в Excel новый лист, и назовите его Прод_промежут. На нем вы разместите сводные таблицы и иные элементы бизнес-логики. Лист Панель_Продажи уже существует. Вы его создали, когда занимались кнопкой навигации.
Начнем с отчета Продажи по кварталам. Визуально он похож на аналогичный отчет на панели Финансы, но будет реализован с помощью иной техники. Постройте табличку продаж по кварталам на листе Прод_промежут (рис. 17). Строки представляют собой уникальный список продуктов. Формулы в ячейках С3:F8 основаны на функции СУММЕСЛИМН (рис. 17).
Рис. 17. Продажи по кварталам
Для отражения этой таблицы на панели Продажи воспользуемся инструментом Камера (подробнее см. Марк Мур. Динамические диаграммы, раздел Инструмент Камера). Поместите Камеру на панель быстрого доступа. Выберите диапазон В1:F8 на листе Прод_промежут, кликните на Камере, перейдите на лист Панель_Продажи, кликните в любом месте. Появится изображение выделенного диапазона. Прелесть этого изображения заключается в том, что оно изменяется в соответствии с любыми изменениями оригинальной области: чисел, формата, рисунков, сетки… Обратите внимание, если выделить изображение, в строке формул отобразиться ссылка на оригинальную область (рис. 18). Изображение можно перемещать по листу, как единое целое; изменять его размеры. При этом ширина изображения не зависит от ширины столбцов.
Рис. 18. Изображение на листе Панель_Продажи
К сожалению, иногда изображения выдает ошибки, что связано с драйверами печати, установленными на некоторых компьютерах. Т.е., вы можете создать прекрасное изображение, а при открытии файла на другом ПК, оно будет дефектным.
Продажи по категориям
Круговая диаграмма будет показывать данные для одного квартала, выбранного с помощью переключателя (подробнее о последнем см. Марк Мур. Динамические диаграммы, раздел Инструмент Переключатель, Option Button). Создайте таблицу на листе Прод_промежут. В ячейке В12 будет храниться значение, соответствующее выбору Переключателя. Формула в ячейке С12 =»Q»&B12, преобразует выбор Переключателя в номер квартала. В ячейке С15 используется формула: =СУММЕСЛИМН(Продажи!$F:$F;Продажи!$E:$E;»Sales»; Продажи!$D:$D;$C$12;Продажи!$C:$C;$B15)
Рис. 19. Продажи по категориям
Вставьте круговую диаграмму. Свяжите ее название с ячейкой В13, в которой введите формулу =»Продажи за «&C12. Отформатируйте диаграмму, вырежьте ее и вставьте на лист Панель_Продажи.
Добавьте четыре Переключателя. Переименуйте их в Q1, Q2, … Порядок создания и именования имеет значение! Вставьте элемент управления Группа. Переименуйте его – Выберите квартал. Убедитесь, что все переключатели находятся полностью внутри группы (рис. 20).
Рис. 20. Группа охватывает все Переключатели
Щелкните правой кнопкой мыши на любой Переключатель выберите опцию Формат объекта, перейдите на вкладку Элемент управления и установите связь с ячейкой C12 листа Прод_промежут. Поскольку все кнопки являются частью группы, установка одной ссылки на ячейку устанавливает их все. (Не устанавливайте связь с ячейкой для второго Переключателя. Это может сбить его работу.)
Продукты по кварталам
Мы создадим диаграмму, на которой пользователь сможет выбрать продукт и число кварталов. Продукты будут выбираться с помощью выпадающего списка; а кварталы – полосой прокрутки. Данные, на которых основана диаграмма, – это фрагмент данных Выручка по продуктам (см. рис. 8). Разница в том, что сейчас диаграмма отображает лишь один ряд. Вы используете ранее созданную таблицу, и выберете из нее нужные данные на основе значений, возвращаемых элементами управления Выпадающий список и Полоса прокрутки (рис. 21). В двух ячейках (I3 и I5) вы храните значения, выбранные в полосе прокрутки и раскрывающемся списке. Значение в ячейке I7 определяется формулой =ИНДЕКС(B3:B8;I5), а значения в диапазоне J8:M8, формулой =ВПР($I7;$B$3:$F$8;СТОЛБЕЦ()-8;ЛОЖЬ)
Рис. 21. Бизнес-логика линейного графика
Создайте элемент управления Поле со списком на листе Панель_Продажи. Сформируйте список по диапазону Прод_промежут!$B$3:$B$8. Установите ссылку на ячейку Прод_промежут!$I$5. Укажите количество строк в списке 6 (рис. 22).
Рис. 22. Формат Поля со списком
Создайте горизонтальную полосу прокрутки на листе Панель_Продажи (рис. 23).
Рис. 23. Формат горизонтальной полосы прокрутки
На листе Прод_промежут выделите диапазон I6:M7. Вставьте линейный график. Для того, чтобы график использовал только кварталы, указанные в ячейке I3, создайте именованный диапазон ДанныеГрафика (пробелы в имени не допускаются). В поле Диапазон введите формулу =СМЕЩ(Прод_промежут!$J$7;;;1;Прод_промежут!$I$3), как на рис. 24.
Рис. 24. Именованный диапазон
Измените данные для линейного графика, чтобы он использовал в качестве значений не диапазон I7:M7, а именованный диапазон. Для этого кликните на графике правой кнопкой мыши, откройте Выбрать данные. В окне Выбор источника данных выберите ряд и кликните Изменить. В поле Значение укажите имя диапазона; предварите его именем листа (рис. 25).
Рис. 25. Теперь график ссылается на именованный диапазон
Протестируйте график, вводя значения от 1 до 4 в ячейку J3. Вырежьте диаграмму и вставьте ее на лист Панель_продажи. Над полосой прокрутки добавьте Надпись с инструкциями для пользователя. То, что очевидно для вас, как разработчика, может не быть очевидным для других пользователей. Небольшой трюк. Обычно Надпись содержит текст, но можно вставить в нее и формулу. Пройдите по меню Вставка –> Надпись. Нарисуйте прямоугольник Надписи над полосой прокрутки. Дважды кликните на границе Надписи. В строку формул введите ссылку на ячейку I9 листа Прод_промежут. Введите в ячейку I9 текст с указаниями для пользователя.
Добавьте заголовок панели, вставьте кнопку На панель Финансы, свяжите ее гиперссылкой с ячейкой А1 листа Фин_панель. Можете скрыть листы с исходными данными и промежуточными вычислениями, оставив только две панели.