Создайте флажок списка на листе Sales_Panel. Создайте список с диапазоном Prod_space!$B$3:$B$8. Установите ссылку на ячейку Prod_space!$I$5. Установите количество строк в списке равным 6 (рисунок 22).
Как сделать дашборд в excel
В серии «Приборные панели: Как это делают профессионалы» я расскажу о лучших способах отображения и представления информации с помощью Excel. Термин «приборная панель» в современном деловом мире относится к представлению бизнес-показателей, которые обеспечивают быстрый обзор состояния любого аспекта бизнеса или процесса (например, текущего финансового состояния, тенденций продаж, эффективности производства или просто визуализации конкретных данных). Поскольку не существует краткого и устоявшегося русского эквивалента этого термина, необходимо использовать термин на английском языке.
Часто предполагается, что приборная панель должна состоять из одной страницы, на которой отображается только основная и важная информация. Поэтому очень важно знать, как представить данные четко, кратко и эффективно. Вы также должны знать, что приборная панель должна содержать не столько необработанные данные, сколько статистически подготовленные данные, характеризующие содержание, качество и структуру анализируемых процессов.
Даже если в ваши задачи не входит разработка приборных панелей, эта тема будет очень полезна, поскольку любая хорошо выполненная работа должна быть хорошо представлена как руководству, так и коллегам. Независимо от того, что вы сделали: отправились ли вы в деловую поездку, провели проект по продажам или завершили проект, запишите свои результаты и представьте их в соответствующем виде. В противном случае существует 90-процентная вероятность того, что ваши усилия останутся незамеченными и неоцененными. Если вам пока нечем похвастаться, воспользуйтесь этими советами, чтобы выработать свой собственный стиль, который будет отличать коллег и клиентов.
Что мы изучим в этой статье?
- Как можно визуализировать данные при помощи таких средств, как «фигуры» и объекты «WordArt».
- Автоматическая сортировка таблиц по факту изменения одной ячейки. То есть, если ячейка A3 =1, то таблица сама (!), только при помощи формул сортируется по имени продукта, а если A3 изменяет своё значение на 2, то таблица пересортировывает себя по убыванию продаж.
- Рассмотрим очень интересный вариант получения мини-таблицы с полосой прокрутки, которая проецируется на большую таблицу на другом листе. Это крайне актуально в связи с тем, что места в любой dashboard всегда не хватает.
- И в качестве вишенки на торте, я покажу, как совместить сортировку и мини-таблицу, и обучить последнюю сортироваться, когда пользователь всего-лишь наводит указатель мыши на название колонки (ничего не нажимая!). Это выглядит очень эффектно и продвинуто.
Скачать. Не забудьте включить редактирование и включить макросы.
Фигуры и WordArt
Это очень простой совет, но о нем многие забывают. В Excel (а также в Word и PowerPoint) есть множество векторных фигур, которые можно использовать для рисования всевозможных вещей. У меня есть друг, который нарисовал очень хорошие технические чертежи в MS Office. Получилось очень красиво. Кроме того, многие элементы могут иметь текстовые метки, которые могут быть связаны с ячейками рабочего листа через пространство имен. А благодаря большому количеству эффектов оформления как самих фигур, так и текста внутри них, он становится незаменимым инструментом для придания визуальной привлекательности вашей приборной панели. Попробуйте — вам понравится!
Чтобы импортировать фигуру, перейдите на ленту Импорт и в разделе Иллюстрации найдите следующее
Мы рассмотрим, как шаг за шагом создать таблицу продаж в Excel. Статья полезна для тех, кто хочет познакомиться с этим мощным инструментом анализа данных.
Аналитика данных: как построить дашборд в Excel
ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCEL.
Приборная панель — это динамический отчет, состоящий из структурированного набора данных и их визуализации с помощью диаграмм, графиков и таблиц.
Основные задачи приборной панели:
Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям
Создание приборных панелей — задача не менее сложная, чем знание формул в Excel. По статистике, продвинутый пользователь Excel может освоить этот навык за 20 часов обучения и практики.
Для специалистов, работающих с отчетами, возможность создавать информационные панели стала скорее необходимостью, чем дополнительным преимуществом.
- представить набор данных максимально наглядным и понятным;
- держать под контролем ключевые бизнес―показатели;
- находить взаимосвязи, выявлять негативные и положительные тенденции, находить слабые места в организации рабочих процессов;
- давать оперативную сводку в режиме реального времени.
В большинстве случаев созданием приборной панели занимается аналитик, который обрабатывает огромное количество данных, компилирует их в красивую и понятную приборную панель и предоставляет ее клиенту. Это могут быть руководители, менеджеры по продажам, специалисты по персоналу, бухгалтеры, маркетологи.
Приборная панель помогает менеджерам по продажам управлять продажами. HR-специалисты — для отслеживания ключевых показателей, связанных с HR. Бухгалтеры найдут приборную панель полезной, поскольку она показывает финансовое состояние организации. Маркетологи — для анализа рекламных кампаний и оценки их эффективности. Руководитель сможет быстро оценить состояние ключевых показателей и принять управленческие решения.
Существует множество сервисов бизнес-аналитики, таких как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным является Excel.
Наиболее важным и интересным аспектом приборной панели является интерактивность.
Интерактивность можно настроить с помощью следующих приемов:
Для этого выберите наиболее популярный метод с помощью перекрестных таблиц.
Мы рекомендуем вам выполнить все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может быть достигнуто только чтением инструкций». Файл с данными по практике можно скачать здесь.
- срезы и временные шкалы в сводных таблицах ― эти инструменты упрощают фильтрацию данных и позволяют управлять дашбордом: например, можно более детально посмотреть данные по конкретному менеджеру или заказчику за определённый период времени или в разрезе каналов продаж.
- выпадающие списки, формулы и условное форматирование — использование таких приёмов удобно, когда много разных таблиц и построить сводные таблицы невозможно;
- спарклайны, мини-диаграммы в ячейках, тепловые карты в аналитических таблицах — такой способ чаще всего подходит для тактических целей специалистов или аналитиков, а не для стратегических целей руководителя.
Создаём классический дашборд для руководителя отдела продаж
Создание приборной панели начинается со сбора данных. На этом этапе важно сделать таблицы плоскими, чтобы впоследствии их можно было использовать для создания сводных таблиц для приборной панели.
Плоская таблица — это двумерная таблица данных, состоящая из столбцов и строк. Столбцы — это информационные атрибуты таблицы, строки — отдельные записи, состоящие из нескольких атрибутов.
Собираем данные
Пример плоской таблицы:
В приведенном выше примере атрибутами являются: «Название», «День», «Год», «Запасы», «Оборот (в тысячах рублей)», «Менеджер», «Клиент». Они размещаются в заголовке таблицы.
Эта таблица используется в качестве основы для создания таблицы инструментов для продаж.
Если вы знаете, для какой цели и для кого предназначена приборная панель, вам будет легче понять, какие показатели следует отображать. Это могут быть любые количественные показатели, которые важны для бизнеса: Прибыль, оборот, количество сотрудников, количество заявок, фонд заработной платы.
Вам также необходимо определиться с макетом — структурой — приборной панели. Для начала достаточно представить их на листе формата А4.
Выбираем макет дашборда и цели
Пример универсального стола
Помимо симметрии, важно учитывать логику расположения информационных блоков. Это связано с нашим восприятием: Мы привыкли читать слева направо, поэтому наиболее важные ключевые цифры должны быть расположены слева направо и сверху вниз, а менее важные — справа вниз:
— используя приведенную выше таблицу данных в качестве примера плоской таблицы.
Таблицы показывают продажи по месяцам, продуктам и запасам.
Вот как это должно работать:
Также создадим таблицу для ключевых показателей «оборот», «средний контроль» и «количество продаж»:
Построим несколько сводных таблиц по продажам
PowerView — это подключаемый модуль, который входит в состав Excel 2013. Вместе с PowerPivot подключаемый модуль PowerView позволяет создавать визуальные отчеты для ваших моделей данных в Excel.
Чтобы активировать надстройку, нажмите кнопку PowerView на вкладке Импорт в группе Отчеты.
Excel открывает новый лист PowerView (с именем PowerView1). В списке вкладок также появится новая вкладка PowerView, а в правой части экрана появится окно PowerView Fields.
Чтобы просмотреть данные в отчете PowerView, необходимо выбрать элементы на панели PowerView Fields. По умолчанию информация отображается в виде небольшой сводной таблицы. Вы можете изменить внешний вид дисплея, перейдя на вкладку Design в группе Switch Display со следующими параметрами:
Exceltip
На рисунке выше показан пример одного из типов визуальных отчетов, которые можно создавать с помощью надстройки PowerView. На карте данные об обороте представлены в виде кругов разного диаметра, показывающих относительный оборот по регионам. При наведении курсора на один из кружков Excel отображает текстовое поле с названием области и значением оборота.
Excel создает новый рабочий лист и вставляет в него сводную таблицу со всеми полями.
Гистограмма с накоплением). У вас должно быть такое Парето.
Я предлагаю вам сначала нарисовать дизайн стола на бумаге или на доске. Четко определите, какой смысл будет заключаться в таблице, а затем примите решение о том или ином типе диаграммы или отчета.
- Таблица отображает выбранные данные на листе PowerView в табличном представлении. Данный вид отображения информации устанавливается по умолчанию.
- Линейчатая диаграмма отображает информацию в виде одной из выбранных линейчатых диаграмм.
- Гистограмма позволяет представить данные в виде гистограммы.
- Другая диаграмма представляет информацию в виде одной из трех доступных диаграмм: строчная, точечная и круговая.
- Карта представляет выбранные наборы данных в виде круговых диаграмм с географической привязкой к карте.
В Excel нет формулы, которую можно использовать для быстрого создания таблицы. Таблица — это сочетание различных элементов, которые объединяются для визуализации информации. Думайте о приборной панели как о строительном наборе Lego: В Excel кубы — это формулы (SUMM, SUMMESLY, SUMMESLYN, SMESH, VPR), диаграммы, сводные таблицы, элементы управления и так далее.
Excel PowerView: пример создания простейшего дашборда
Чтобы продемонстрировать возможности надстройки PowerView, воспользуемся статистикой продаж компьютерной техники по регионам, которой мы уже пользовались при демонстрации работы надстройки GeoFlow (на самом деле PowerView работает практически с любыми видами данных, даже выгруженных из Azure Marketplace). Для этого выделите любую ячейку в таблице с данными и активируйте надстройку PowerView, как было описано ранее ( Вставка ->Отчеты ->Мы создадим две таблицы управления на основе финансовых данных и данных о продажах.
Уберите в панели Поля PowerView галочки с элементов Дата и Категория. На листе PowerView щелкните по заголовку поля таблицы Сумма, чтобы данные сортировались по убыванию. Измените внешний вид представления на гистограмму ( Конструирование ->Представление переключателя –>Гистограмма –>Вот что нам нужно получить:
Планирование панели
Рисунок 2. Финансовая панель.
Откройте файл Excel Готовые примеры и экспериментируйте
Вставьте новый лист. Назовите его Fin_space и постройте элементы таблицы один за другим (см. рисунок 2).
Панель Финансы
Сначала создайте агрегированную таблицу в листе Fin_space на основе необработанных данных из Finance (Рисунок 3). Затем добавьте гистограмму и среднее квартальное значение на том же листе. Удалите лишнее и добавьте метки данных. Уменьшите количество цифр в этикетках (подробнее см. таблицу ниже). Принцип Эдварда Тафти о минимизации количества элементов диаграммы, Перекрестные таблицы, Пользовательское форматирование чисел в Excel, Некоторые расширенные возможности форматирования). Вставьте новый лист. Назовите ее Fin_panel и переместите на нее график. Обратите внимание, что заголовок диаграммы не вводится, а является ссылкой на ячейку A1.
Рисунок 3. Сводный график единиц продукции за период.
На этом этапе я должен сказать, что эти сокровенные знания, которыми я хочу поделиться с вами, я позаимствовал у «великого шамана Excel» (как он сам себя называет). Жан МаркВойер (Канада) действительно должен многому научиться. Я с трудом различаю то, что собираюсь рассказать вам. Но все знания на 95% заимствованы, поэтому мы не должны расстраиваться, что не являемся первопроходцами. В современном мире проблема заключается не в том, чтобы научиться чему-то принципиально новому, а в том, чтобы овладеть уже имеющимися знаниями.
Итак — классификация по формуле. Давайте посмотрим на рабочий лист «Автосортировка» учебного примера. В ячейке A3 у нас находится индикатор типа классификации: 1 — Классификация по наименованию продукта, 2 — Классификация по уменьшению выручки. Исходная таблица, которую мы хотим отсортировать, находится в диапазоне K3:L22 и объявлена как номинальный диапазон rngOriginal. Таблица отсортированных типов находится в ячейках C3:D22. Столбцы F:I содержат вспомогательные типы.
На листе SortOnTheFly можно увидеть мини-таблицу по адресу E7:G11, где мы можем увидеть отсортированную таблицу из C3:D22 листа AutoSort с помощью полосы прокрутки (клавиатура не работает). В этом нам помогают ячейка A8, которая связана с полосой прокрутки, и диапазон N6:P10, в котором рассчитываются данные для отображения в мини-таблице.
Единиц за период
Данные в таблице N6:P10 выбираются (на примере ячейки O6) с помощью формулы =MEMBERSHIP($C3500;$N6;0) или =OFFSET($C3500;$N6;0). Ячейка N6 зависит от ячейки A8 (положение полосы прокрутки).
Мы подошли к бриллианту нашего обзора. Описанный трюк прост в исполнении, но почти никто его не знает. Мы узнали, как отсортировать нашу учебную таблицу, изменив ячейку A3. Теперь мы хотим, чтобы ячейка изменялась, когда мы просто наведем указатель мыши на символ сортировки! Здесь не поможет ни одно стандартное событие рабочего листа, только трюк, основанный на малоизвестном свойстве функции HYPERSSL
Сортировка формулами
Назначение столбцов и обсуждение их формул
- Столбец I с заголоком Row index не содержит формул, а просто пронумерован от 1 до 20. Он нам потребуется в дальнейших вычислениях.
- Столбец H с заголовком Num for sort содержит значения, которые используются для сортировки. Чем больше значение в ячейке этого столбца — тем выше ячейка располагается в отсортированной таблице. К примеру, ячейка H4 содержит формулу: =ВЫБОР($A$3;21-$I4;L4)+0.00001*ЧСТРОК($I$3:$I4) или для англоязычной версии =CHOOSE($A$3;21-$I4;L4)+0.00001*ROWS($I$3:$I4) То есть, в зависимости от значения ячейки $A$3 (1 или 2), ячейка принимает значение 21-$I4 или L4. Кроме этого прибаляется небольшая дробная константа, зависящая от относительной строки ячейки (чем больше строка, тем больше константа. Это нужно на случай, если оригинальная таблица будет содержать одинаковые значения продаж.). То есть смысл формулы в том, чтобы значение ячейки было тем выше, чем выше должна располагаться соответствущая ячейка оригинальной таблицы после сортировки.
- Столбец G с заголовком Needed order вычисляет последовательность, в которой должны отображаться строки оригинальной таблицы в соответствии с оценками, расчитанными в столбце H. Самое большое значение ячейки H получает индекс 1, следующее — 2, и так далее. Это реализуется при помощи полезной формулы =РАНГ($H3;$H$3:$H$22) или =RANK($H3;$H$3:$H$22). Приведем пример: если A3=2, то формула в G4 возвращает результат 11, что означает, что строка оригинальной таблицы K4:L4 (P2,504) должны во время сортировки быть на 11-й позиции.
- Столбец F с заголовком Real sort вычисляет, какую строку оригинальной таблицы отсортированная таблица должна взять для соответствующей строки. Например, значение в F4 равно 4. Это означает, что отсортированная таблица C3:D22 в своей второй строке (C4:D4) должно взять данные из 4-й строки оригинальной таблицы — то есть K6:L6. Делается это (для ячейки в G4) при помощи формулы: =ПОИСКПОЗ($I4;$G$3:$G$22;0) или =MATCH($I4;$G$3:$G$22;0).
- Отсортированная таблица в C:D извлекает данные из оригинальной при помощи простой формулы: =ИНДЕКС(rngOriginal;$F3;1) или =INDEX(rngOriginal;$F3;1). Второй столбец в качестве последнего параметра использует двойку. Надеюсь, ваш мозг не вскипел 🙂
Мини-таблица
Сортировка наведением мыши
