Анализ данных Excel - Визуализация данных

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

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

Визуализация данных с помощью диаграмм

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

Дополнительные сведения о типах диаграмм см. В учебном пособии по диаграммам Excel.

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

Создание комбинированных диаграмм

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

Для этих результатов мы создадим кластерную столбчатую диаграмму.

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

Лучший способ различить два типа данных для сравнения значений - использовать комбинированные диаграммы. В Excel 2013 и версиях выше для той же цели можно использовать комбинированные диаграммы.

Используйте вертикальные столбцы для целевых значений и линию с маркерами для фактических значений.

• Щелкните вкладку ДИЗАЙН на вкладке ИНСТРУМЕНТЫ ДИАГРАММЫ на ленте.
• Щелкните Изменить тип диаграммы в группе Тип. Откроется диалоговое окно «Изменить тип диаграммы».

• Щелкните Комбо.

• Измените тип диаграммы для серии «Фактические» на «Линия с маркерами». Предварительный просмотр появится в разделе «Пользовательская комбинация».

• Щелкните ОК.

Будет отображена ваша индивидуальная комбинационная таблица.

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

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

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

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

Если вы используете ту же комбинационную диаграмму, что и раньше, вы получите следующее -

На графике данные No. of Units не отображается, поскольку диапазоны данных значительно различаются.

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

• Щелкните вкладку ВСТАВИТЬ.
• Нажмите Комбо в группе диаграмм.
• В раскрывающемся списке щелкните Создать настраиваемую комбинированную диаграмму.

Появится диалоговое окно «Вставить диаграмму» с выделенным «Комбо».

Для типа диаграммы выберите -

• Линия с маркерами для серийного количества блоков

• Кластерный столбец для фактической прибыли серии

• Установите флажок «Вторичная ось» справа от серийного номера единиц и нажмите «ОК».

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

Ваша комбинированная диаграмма отображается со вторичной осью.

Вы можете наблюдать значения Фактической прибыли на первичной оси и значения Кол-во единиц на вторичной оси.

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

Различающая серия и ось категорий

Предположим, вы хотите спрогнозировать фактическую прибыль, полученную в 2013-2016 годах.

Создайте кластерный столбец для этих данных.

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

Удалите год заголовка в диапазоне данных.

Теперь год рассматривается как категория, а не как серия. Ваш график выглядит следующим образом -

Элементы диаграммы и стили диаграммы

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

• Щелкните диаграмму

Рядом с правым верхним углом диаграммы появляются три кнопки -

• 
  Элементы диаграммы
• 
  Стили диаграмм
• 
  Фильтры диаграммы

Для подробного объяснения этого см. Учебник по диаграммам Excel.

• Щелкните Элементы диаграммы.
• Щелкните Ярлыки данных.

• Нажмите Стили диаграмм
• Выберите стиль и цвет, которые подходят вашим данным.

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

Метки данных

Excel 2013 и более поздние версии предоставляют вам различные варианты отображения меток данных. Вы можете выбрать одну метку данных, отформатировать ее по своему усмотрению, а затем использовать «Клонировать текущую метку», чтобы скопировать форматирование в остальные метки данных на диаграмме.

Ярлыки данных на диаграмме могут иметь эффекты различной формы и размера.

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

Быстрый макет

Вы можете использовать Quick Layout, чтобы быстро изменить общий макет диаграммы, выбрав один из предопределенных вариантов макета.

• Щелкните диаграмму.
• Щелкните вкладку ДИЗАЙН в разделе ИНСТРУМЕНТЫ ДИАГРАММЫ.
• Щелкните Quick Layout.

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

Выберите понравившийся макет. Диаграмма будет отображаться с выбранным макетом.

Использование изображений в столбчатых диаграммах

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

• Щелкните столбец на столбчатой ​​диаграмме.

• В серии данных формата нажмите Заливка.

• Выберите изображение.

• В разделе «Вставить изображение из» укажите имя файла или, при необходимости, буфер обмена, если вы скопировали изображение ранее.

Выбранное изображение появится вместо столбцов на диаграмме.

Диаграмма диапазона

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

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

Здесь в данных <50% - низкий, 50% - 80% - средний, а> 80% - высокий.

С помощью Band Chart вы можете отображать результаты своего опроса следующим образом:

Создайте линейную диаграмму из ваших данных.

Измените тип диаграммы на -

• Серии Восток и Запад для линии с маркерами.
• От низкого, среднего и высокого ряда до столбца с накоплением.

Ваш график выглядит следующим образом.

• Щелкните один из столбцов.
• Измените ширину зазора на 0% в Формате серии данных.

Вы получите полосы вместо столбцов.

Чтобы график был презентабельнее -

• Добавить заголовок диаграммы.
• Отрегулируйте диапазон вертикальной оси.
• Измените цвета полос на зеленый-желтый-красный.
• Добавьте метки к бэндам.

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

График термометра

Когда вам нужно представить целевое значение и фактическое значение, вы можете легко создать диаграмму термометра в Excel, которая четко показывает эти значения.

С помощью диаграммы термометра вы можете отображать свои данные следующим образом:

Расположите свои данные, как показано ниже -

• Выберите данные.
• Создайте кластерную столбчатую диаграмму.

Как вы заметили, правый столбец - это цель.

• Щелкните столбец на диаграмме.
• Нажмите Переключить строку / столбец на ленте.

• Щелкните правой кнопкой мыши целевой столбец.
• Щелкните Форматировать ряд данных.
• Щелкните Secondary Axis.

Как вы видите, первичная и вторичная оси имеют разные диапазоны.

• Щелкните правой кнопкой мыши основную ось.
• В параметрах оси формата в разделе Границы введите 0 для минимума и 1 для максимума.
• Повторите то же самое для вторичной оси.

И первичная ось, и вторичная ось будут установлены на 0% - 100%. Целевой столбец скрывает фактический столбец.

• Щелкните правой кнопкой мыши видимый столбец (цель)
• В серии данных формата выберите
  • Нет заполнения для FILL
  • Сплошная линия для ГРАНИЦЫ
  • Синий для цвета

• В элементах диаграммы снимите флажок
  • Ось → Первичная горизонтальная
  • Ось → Вторичная вертикаль
  • Gridlines
  • Заголовок диаграммы
• На диаграмме щелкните правой кнопкой мыши по основной вертикальной оси.
• В параметрах оси формата нажмите НАБОРЫ
• Для основного типа выберите Внутри

• Щелкните правой кнопкой мыши область диаграммы.
• В параметрах Форматировать область диаграммы выберите
  • Нет заполнения для FILL
  • Нет строки для BORDER

Измените размер области диаграммы, чтобы получить форму термометра.

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

• Вставьте прямоугольную форму поверх синей прямоугольной части диаграммы.
• В параметрах формата фигуры выберите -
  • Градиентная заливка для FILL
  • Линейный для типа
  • 180 ⁰ для угла
• Установите градиентные остановки на 0%, 50% и 100%.
• Для градиента останавливается на 0% и 100%, выберите черный цвет.
• Для градиента 50% выберите белый цвет.

• Вставьте овальную форму внизу.
• Отформатируйте фигуру с такими же параметрами.

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

Диаграмма Ганта

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

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

Рассмотрим следующие данные, в которых столбец -

• Задача представляет задачи в проекте
• Начало представляет количество дней с даты начала проекта.
• Продолжительность представляет собой продолжительность Задачи

Обратите внимание, что начало любой задачи - это начало предыдущей задачи + длительность. Это тот случай, когда Задачи находятся в иерархии.

• Выберите данные.
• Создать гистограмму с накоплением.

• Щелкните правой кнопкой мыши Start Series.
• В параметрах Форматировать ряд данных выберите Без заливки.

• Щелкните правой кнопкой мыши по оси категорий.
• В параметрах оси формата выберите «Категории» в обратном порядке.

• В элементах диаграммы снимите выделение
  • Legend
  • Gridlines
• Отформатируйте горизонтальную ось так, чтобы
  • Отрегулируйте диапазон
  • Основные отметки с интервалом в 5 дней
  • Незначительные отметки с интервалом в 1 день
• Отформатируйте ряд данных, чтобы они выглядели впечатляюще
• Дайте название диаграмме

График водопада

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

Excel 2016 предоставляет тип диаграммы водопада. Если вы используете более ранние версии Excel, вы все равно можете создать диаграмму водопада с помощью диаграммы с накоплением столбцов.

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

Рассмотрим следующие данные.

• Подготовьте данные для диаграммы водопада

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

• Добавьте 2 столбца - увеличение и уменьшение для положительного и отрицательного денежных потоков соответственно.

• Добавьте столбец Начало - первый столбец на диаграмме с начальным значением в чистом денежном потоке.

• Добавьте столбец Конец - последний столбец на диаграмме с конечным значением в Чистом денежном потоке.

• Добавьте столбец Float - который поддерживает промежуточные столбцы

• Вычислите значения для этих столбцов следующим образом

• В столбце Float вставьте строку в начало и в конец. Поместите n произвольное значение 50000. Это просто, чтобы оставить немного места слева и справа от диаграммы.

Данные будут следующими.

• Выберите ячейки C2: H18 (исключить столбец Чистый денежный поток)
• Создать столбчатую диаграмму с накоплением

• Щелкните правой кнопкой мыши Float Series.
• Щелкните Форматировать ряд данных.
• В параметрах Форматировать ряд данных выберите Без заливки.

• Щелкните правой кнопкой мыши «Отрицательная серия».
• Выберите Красный цвет заливки.

• Щелкните правой кнопкой мыши по положительной серии.
• Выберите зеленый цвет заливки.

• Щелкните правой кнопкой мыши Start Series.
• Выберите Цвет заливки как Серый.
• Щелкните правой кнопкой мыши на End Series.
• Выберите Цвет заливки как Серый.
• Удалите легенду.

• Щелкните правой кнопкой мыши любую серию
• В параметрах форматирования ряда данных выберите ширину зазора как 10% в разделе Параметры ряда.

Дайте название диаграмме. Отобразится диаграмма водопада.

Спарклайны

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

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

• Выберите данные, для которых вы хотите добавить спарклайны.
• Оставьте пустой столбец справа от данных для спарклайнов.

Кнопка «Быстрый анализ»

появляется в правом нижнем углу выбранных данных.

• Нажмите кнопку «Быстрый анализ»

  
  . Появится панель быстрого анализа с различными параметрами.

Нажмите SPARKLINES. Отображаемые параметры диаграммы основаны на данных и могут отличаться.

Нажмите Line. Линейная диаграмма для каждой строки отображается в столбце справа от данных.

Сводные диаграммы

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

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

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

Вы можете создать сводную диаграмму из

• Сводная таблица.
• Таблица данных как отдельная без сводной таблицы.

Сводная диаграмма из сводной таблицы

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

• Щелкните сводную таблицу.
• Нажмите АНАЛИЗИРОВАТЬ под ИНСТРУМЕНТАМИ Сводной таблицы на ленте.
• Нажмите на сводную диаграмму. Откроется диалоговое окно «Вставить диаграмму».

Выберите Clustered Column из опции Column.

Щелкните ОК. Отображается сводная диаграмма.

В сводной диаграмме есть три фильтра - Регион, Продавец и Месяц.

• Щелкните параметр управления фильтром области. Появится окно поиска со списком всех регионов. Флажки появляются рядом с регионами.

• Выберите параметры Восток и Юг.

Отфильтрованные данные отображаются как в сводной диаграмме, так и в сводной таблице.

Сводная диаграмма без сводной таблицы

Вы можете создать автономную сводную диаграмму без создания сводной таблицы.

• Щелкните таблицу данных.
• Щелкните вкладку Вставка.
• Щелкните Сводная диаграмма в группе диаграмм. Откроется окно «Создать сводную диаграмму».
• Выберите таблицу / диапазон.
• Выберите место, где вы хотите разместить сводную диаграмму.

Вы можете выбрать ячейку в самом существующем листе или в новом листе. Щелкните ОК.

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

• Выберите поля для добавления в сводную диаграмму

• Расположите поля, перетащив их в ФИЛЬТРЫ, ОБОЗНАЧЕНИЯ (СЕРИИ), ОСИ (КАТЕГОРИИ) и ЗНАЧЕНИЯ

• Используйте элементы управления фильтром на сводной диаграмме, чтобы выбрать данные для размещения на сводной диаграмме.

Excel автоматически создаст связанную сводную таблицу.