Дашборд: как анализировать любые данные

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

Перейдите на лист с исходной таблицей;
Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи.

Скопируйте полученный список на лист, в котором будет размещен отчет;
Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel.

Также можно легко подсчитать количество партий каждого Товара:

Основные программы, в которых можно строить

Пусть первые графики Ганта и были начерчены на бумаге, сейчас же графики можно создать на компьютере разными способами. Можно сделать её в любой программе для создания таблиц. Например:

Microsoft Excel

Microsoft Excel — одна из самых популярных программ для создания таблиц. Чтобы сделать диаграмму Ганта, можно выбрать шаблон графика при создании нового файла.

Но для того, чтобы создать график Ганта вручную, используйте следующий алгоритм:

Нужно создать таблицу. Для начала вам нужны основные данные — имя задачи, дата выполнения и продолжительность (в днях). В столбце А пишите имя задачи, начиная со 2 строки. При этом, первую строку не заполняйте — так программа не будет использовать столбцы A и B как категорию осей.
После создания таблицы переходим к самой схеме Ганта. Для ее создания выделите нужный диапазон (всю вашу таблицу) и вставьте (через панель Вставить, где вы найдете все виды диаграмм) линейчатую гистограмму с накоплением. Нажмите кнопку ОК. Всё лишнее вручную удалите из вашего графика.
Выберите ось по вертикали и вызовите окошко «Формат оси».
Здесь найдите Параметры оси. Выберите флажки «Горизонтальная ось пересекает… для отображения даты в нижней части», а затем «обратный порядок категорий… по порядку, начиная с верхней». Выполнив эти действия, можете закрывать окно и переходить к следующему пункту.
Выбирайте нужный вам ряд исходников и вызывайте окно «Формат ряда».
Открыв это окно, в разделе Заливка выберите настройку Нет заливки, затем в настройках Цвет границы установите положение Нет линий. После этого можете закрывать окно. Так вы скроете ряды данных.
Далее вы сможете отредактировать формат графика вручную. Особенно удобно это делать в версиях Excel 2010 и выше.

LibreOffice Calc

Принцип создания графика Ганта в программе LibreOffice Calc очень схож с принципом её создания в Excel.

Как и в прошлой схеме, для начала сделайте таблицу с вашими исходниками — названия целей, даты начала и конца проекта и каждой задачи, сроки на выполнение.
Выделите нужный диапазон с данными (опять же, не стоит выделять названия типов сверху — только сами данные)
Создайте Линейную гистограмму с накоплением, как это делалось в прошлой программе.
По горизонтали нужно вывести даты. Для этого в окне «Формат оси», в подпункте «Числа» выберите формат даты. В подпункте Масштабировать убирайте флажки «Автоматически» возле показателей «Минимальное» и «Максимальное».
В появившихся окнах введите — для окна Минимальное — первый день (начало первой задачи), для окна Максимальное — последний день (окончание последней)
Остальные шаги так же проделайте вручную, например, удалите ненужные линии, оставив их без заливки.

OnlyOffice

OnlyOffice — одна из интернет-утилит для управления и планирования. Здесь создать график так же легко — точнее, он строится сам, после назначения всех нужных задач и дат непосредственно в структуру проекта. Сроки можно установить и изменить прямо на графике, перетащив нужную вам задачу мышью. Интерфейс программы понятен и прост.

Google Таблицы

Для создания аналога таблицы Ганта будем использовать условное форматирование:

Создайте таблицу с вашими данными и выставите все даты проекта справа от таблицы.
Далее выделяйте пустую область между введенными исходниками и всеми датами проекта. Там вы создадите вашу диаграмму. Нажмите ПКМ (правая кнопка мыши) и выберите пункт «Условное форматирование».
Найдите параметры «Форматирование ячеек» и вставьте туда формулу =И(E$1<=$C2;E$1>=$B2), где:

B2 — ячейка, где записана дата начала задачи
C2 — ячейка, где записана дата окончания задачи
E1 — самая первая ячейка дат вашего проекта

Программа сама сделает всё за вас. В итоге должна получиться таблица, похожая на нужную схему графика.

Из перечисленных выше способов самые простые (как последние три программы) подойдут для создания диаграмм Ганта в бытовых целях, таких как ремонт или переезд и их распланирование, а может и для маленьких проектов какой-либо фирмы. Но для больших бизнес-проектов следует использовать исключительно профессиональные утилиты, вроде Microsoft Excel.

После создания диаграммы, нужно внимательно за ней следить и регулярно обновлять, чтобы рационально учитывать рабочее время для каждой из задач.

Как создать дашборд в Excel?

Пример # 1

У нас есть данные учащихся с оценками по пяти предметам. Мы создадим интерактивную панель управления Excel, на которой мы сможем отметить студентов и посмотреть, имеют ли они право на получение награды или нет. Во-первых, мы должны четко понимать, что мы пытаемся сделать, поэтому давайте посмотрим на необработанные данные.

В таком формате будут вставлены необработанные данные. Согласно правилам, если общая сумма оценок студента превышает 400, он будет награжден или объявлен неуспешным. Итак, очень простая панель инструментов должна выглядеть так:

В столбце K будет указано, должен ли студент быть награжден или нет.

В столбце J Тип = G3

Нажмите Enter и перетащите его в конец данных.

Теперь в типе K3,

Нажмите Enter и перетащите его в конец данных.

Эта панель инструментов основана на предоставленных данных, но если мы изменим данные, панель также изменится. Например, повысьте оценки Викаса на английском с 65 до 88 и посмотрите на результат.

С Неудачно статус был изменен на Присуждено.

Приведенный выше пример был очень простой панелью инструментов, в которой использовались только функции sum и If excel.

Пример # 2

В этом примере мы создадим сложную панель управления Excel, используя множество функций, диаграмм и графиков. Во-первых, нам нужно иметь данные в Excel. Если данные из любого другого источника, нам необходимо импортировать их в Excel. Мы создадим два рабочих листа, в одном из которых будут необработанные данные, а в другом мы сделаем нашу панель мониторинга на основе этих необработанных данных.

Сначала мы создадим таблицы на основе предоставленных необработанных данных. Затем на основе этих таблиц мы создадим диаграммы и информационную панель.

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

Общая оценка производительности рассчитывается путем суммирования каждого параметра путем умножения на его вес. Затем мы покажем результативность сотрудника на основе его оценки производительности, а также покажем ключевые параметры, по которым сотрудникам необходимо работать. Мы можем создать эту панель управления Excel, создав таблицы и срезы, но мы будем использовать диаграммы, поскольку они более наглядны.

Рассчитайте результативность сотрудника, в ячейке Q2 запишите формулу

Выберите массив от B3 до F3.

После вставки запятой выберите массив от J3 до N3.

Поскольку СУММПРОИЗВ — это формула массива в Excel, нам нужно нажать CTRL + SHIFT + ВВОД, чтобы эта формула заработала.

В настоящее время оценка параметра равна нулю, поэтому оценка производительности отображается как ноль.

Перетащите формулу в ячейку Q6.

Теперь введите данные в Параметр.

Тогда оценка производительности изменится, как показано ниже.

Перейдите на рабочий лист информационной панели и в разделе «Вставка» в категории диаграмм щелкните любую диаграмму; в этом случае мы выберем линейный график.

Щелкните правой кнопкой мыши диаграмму и выберите данные.

Появится диалоговое окно.

В поле «Диапазон данных диаграммы» перейдите к таблице исходных данных и выберите раздел оценки производительности.

Нажмите OK, и диаграмма будет подготовлена на листе панели инструментов.

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

Сводная диаграмма в excel как сделать

На этом шаге мы рассмотрим использование сводных таблиц на различных примерах.

Пример 1. Использование сводных таблиц для консолидации данных

На рисунке 1 показаны три рабочих листа, каждый из которых содержит данные за месяц о продажах товаров в одном из магазинов крупной торговой сети. Наша цель — консолидировать эту информацию в одну сводную таблицу. В этом примере исходные данные содержатся в одной рабочей книге. Однако так бывает далеко не всегда. Данные, которые необходимо консолидировать, могут находиться в разных рабочих книгах.

Рис. 1. Рабочие листы, содержащие данные за месяц о продажах товаров

Для создания сводной таблицы выполните следующие действия.

Добавьте новый лист, назовите его Итоги.
Выберите команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.
В первом диалоговом окне мастера выберите переключатель В нескольких диапазонах консолидации и щелкните на кнопке Далее.
В следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 выберите переключатель Создать одно поле страницы. Щелкните на кнопке Далее.
Теперь определите диапазоны для консолидации. Первый диапазон — Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Щелкните на кнопке Добавить для добавления диапазона к списку Список диапазонов.
Повторите пункт 5 для двух других диапазонов. Щелкните на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.
В третьем диалоговом окне Мастер сводных таблиц и диаграмм щелкните на кнопке Готово.

В результате сводная таблица будет иметь вид:

Рис. 2. Сводная таблица

На четвертом шаге описанной процедуры в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 можно выбрать переключатель Создать поля страницы. Это позволит назначить имя каждому элементу в поле страницы.

Файл с данным примером можно взять здесь.

Пример 2. Создание диаграмм по сводной таблице

Сводная диаграмма — это диаграмма, связанная со сводной таблицей. При использовании Мастера сводных таблиц и диаграмм можно одновременно создавать как сводную таблицу, так и сводную диаграмму. Для этого в первом диалоговом окне Мастер сводных таблиц и диаграмм установите переключатель Сводная диаграмма (со сводной таблицей).

Если Вы в этом окне щелкните на кнопке Готово, то Excel создаст новый рабочий лист для сводной таблицы и лист диаграмм для сводной диаграммы. На этих листах будут представлены шаблоны сводной таблицы и сводной диаграммы соответственно. Путем перетаскивания кнопок полей из панели инструментов Список полей сводной таблицы в шаблоны Вы создадите структуру сводной таблицы и сводной диаграммы.

Например, надо создать сводную диаграмму для следующей сводной таблицы (рис. 3).

Рис. 3. Пример сводной таблицы

Применяя вышеописанные действия, сводная диаграмма может быть следующей:

Рис. 4. Пример сводной диаграммы

Диаграмму можно создавать на основе уже существующей сводной таблицы. Для этого надо щелкнуть на кнопке Мастер диаграмм на панели инструментов Сводные таблицы. Excel создаст сводную диаграмму на отдельном листе диарамм. При изменении сводной таблицы сводная диаграмма обновляется автоматически.

Сводные диаграммы всегда строятся на отдельном листе диаграмм. Чтобы преобразовать сводную диаграмму во внедренную диаграмму рабочего листа, перейдите на лист сводной диаграммы и выполните команду Диаграмма | Размещение. В открывшемся диалоговом окне Размещение диаграммы выберите переключатель имеющимся и рабочий лист, на который нужно поместить диаграмму. Щелкните на кнопке ОК.

Пример 3. Анализ данных, полученных в результате опроса общественного мнения

На рисунке 5 показана часть необработанных данных в форме, которая обычно используется для исследования опроса общественного мнения. В каждой записи представлен ответ одного опрашиваемого.

Рис. 5. Пример таблицы, содержащей данные опроса общественного мнения

На рисунке 6 показана сводная таблица, в которой вычислены средние баллы для каждого из 12 вопросов исследования в зависимости от пола опрашиваемого. Дополнительные поля строк позволяют легко просмотреть результаты по возрастным группам и по конкретным городам. Для более сложных сводных таблиц можно перетащить одно или оба поля страницы в область строки или столбца.

Рис. 6. Пример сводной таблицы

На рисунке 7 показан другой рабочий лист. Этот лист содержит 12 отдельных сводных таблиц — по одной для каждого из вопросов. В каждой сводной таблице отображена частота ответов и их процентное соотношение.

Рис. 7. Пример одной из 12 сводных таблиц

Файл с данным примером можно взять здесь.

На следующем шаге мы рассмотрим анализ данных с помощью сценария «что-если».

Как сделать простой дашборд в Excel

Дашборд – это умный отчет в режиме реального времени. Главное условие создания дашборда – наличие данных, которые можно анализировать. Благодаря такой инфопанели руководители и менеджеры имеют возможность отслеживать наиболее важные показатели в режиме реального времени и быстро реагировать. Конечно, не каждому бизнесу нужно отчитываться каждую секунду, в большинстве случаев это еженедельная или ежемесячная периодичность.

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

Золотые правила дашборда

Упрощайте подаваемую информацию, дашборд должен быть простым и понятным. Нет необходимости перегружать его избыточными данными
Используйте сравнение (например, изменение показателя за разные периоды, или сравнение плана и факта)
Используйте различные варианты визуализации данных. Таким образом, данные будут восприниматься проще и быстрее

Зачем нужен дашборд?

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

Данные есть в любой сфере деятельности. А если есть данные, то их можно и нужно проанализировать!

Интерактивный дашборд в Excel

В качестве примера мы решили рассмотреть достаточно актуальный, простой и общедоступный кейс – мониторинг продаж.

Создавать интерактивный дашборд мы будем в Microsoft Excel. Excel хоть и не предназначен для такого рода отчетов, но все-таки он обладает всеми необходимыми функциями для создания качественной инфопанели. Правда, крупным компаниям придется найти что-то более узкоспециализированное и функциональное. Потому как, анализировать большой поток данных и постоянно его обновлять в Excel — затруднительно.

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

В этом видео мы хотим показать вам, как легко визуализировать данные

Важно использовать правильные инструменты и следовать основным статистическим принципам

Создание дашбордов в Excel шаг за шагом

Так будет выглядеть готовый результат построения дашборда в Excel с возможностью интерактивного взаимодействия отчета с пользователем.

Чтобы создать свой такой же или подобный визуальный отчет в виде дашборда следует выполнить ряд последовательных действий в Excel.

В первую очередь создадим новую книгу с 3-ма листами:

Дашборд.
Данные.
Обработка.

Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:

После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».

После выбора инструмента рисуем прямоугольник размером на полторы ячейки и получился выпадающий список.

Теперь нам необходимо его настроить. Щелкаем правой кнопкой мышки по выпадающему списку и из появившегося контекстного меню выбираем опцию: «Формат объекта». После чего появилось окно «Формат элементов управления», которое следует заполнить параметрами так как показано ниже на рисунке:

Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:

Список отображает значения из диапазона первого столбца ячеек таблицы входящих данных ссылаясь в первом поле «Формировать список по диапазону:» по адресу Данные!$A$2:$A$13.
Второе поле «Связь с ячейкой:» позволяет указать ячейку куда будут возвращаться порядковые номера значений выпадающего списка. В данном случае они передаются в ячейку по адресу Обработка!$A$1. Например, если будет выбрано значение из нашего списка – «Март» тогда в ячейку A1 на листе «Обработка» передается число 3 для дальнейшей обработки.
«Количество строк списка:» — числовой параметр позволяет нам отображать выпадающий список без полосы прокрутки. Указав число 12, мы увеличили его размер на 12 записей, чего нельзя сделать с обычным выпадающим списком из проверки данных.

Готовый желаемый результат выглядит так:

Далее начинаем упорно работать с 3-тим листом «Обработка». На данном листе обрабатываются и подготавливаются все данные для вывода на дашборд. Будем двигаться с верху вниз. Сначала подготовим данные для верхних подписей. Для этого создаем табличку выборки показателей при условии полученного номера месяца, переданного выпадающим списком на лист «Обработка» в ячейку A1. В ячейке A2 определяем название месяца на основе полученного числа в ячейке A1, по формуле:

Делаем выборку из входящей таблицы на листе «Данные» для всех показателей с помощью функции =ВПР() скопировав формулу во все остальные ячейки:

Данные для верхних подписей показателей – подготовлены!

Что такое динамический и статический отчеты

Статические отчеты содержат информацию о конкретном периоде времени. Чаще всего их используют для анализа исторических данных.

Например, ежедневные отчеты показывают статистику за день и помогают понять, что произошло. Они имеют дело с данными из прошлого и показывают, как изменился процесс и на чем сосредоточиться для лучших результатов.

Ежедневные статические отчеты предоставят нужную информацию в нужное время сотрудникам или команде. Плюс гарантируют, что все получат одну версию правды – это поможет принимать совместные решения и делать выводы.

Ежемесячная или еженедельная отчетность – еще один хороший способ использовать исторические данные. Статические отчеты покажут сотрудникам и менеджерам моментальные снимки их производительности, чтобы они могли разрабатывать долгосрочную стратегию.

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

Динамические отчеты:

обновляют данные автоматически и без выходных;
доступны онлайн любому, у кого есть разрешение;
всегда актуальны;
ими легко делиться.

Динамическая отчетность помогает быстро реагировать на внезапные проблемы или изменения, благодаря доступу к данным по мере их появления. Это экономит время для всех: владельцев бизнеса, менеджеров, агентств и клиентов.

Панель инструментов в Excel

Дашборд в Excel — мощный инструмент для визуализации информации и может радикально улучшить способ использования. Порядок создания с Dashboard в Excel:

Выполнить планирование структуры.
Подготовить данные для ввода.
Привести данные к стандарту Excel с использованием их источников.
Подготовить, консолидировать, очистить и стандартизировать исходные данные.
Использовать эффективные способы анализа данных.
Создать интерактивную панель инструментов.

Шаблоны дашборд в Excel отлично подходят для множества задач и не только для показа достижений компании, их в достаточном количестве можно найти в интернете, подобрав параметры под пользовательские задачи.

Например, шаблон производительности центра обработки вызовов.

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