Что используется в Excel для визуализации числовых данных
Обновлено: 21.11.2024
Иногда данные трудно усвоить. Вот тут-то и приходит на помощь визуализация данных или искусная работа с графиками. Вы можете использовать тот же инструмент, который вы, вероятно, уже используете для хранения и обработки ваших данных — Microsoft Excel — для визуализации тех же самых данных. Excel — отличный инструмент для визуализации данных.
Возможно, ваши данные Excel похожи на приведенные выше данные. Но числам может быть сложно передать тенденции, связанные с данными. Общие примеры визуализации данных с использованием диаграмм функций Excel, графиков, комбинаций и их производных. Такие диаграммы говорят тысячу слов, которые трудно найти в данных.
Создание базовых визуализаций данных в Excel
В Excel есть несколько инструментов визуализации. Несмотря на то, что множество вариантов может показаться ошеломляющим, вы можете извлечь большую пользу из самых простых диаграмм.
Примеры гистограмм и столбцов
Гистограммы и гистограммы понятны даже детям младшего и среднего школьного возраста. Более высокие столбцы (вертикальные) или более длинные столбцы (горизонтальные) описывают данные. Вы даже можете включить несколько данных для сравнения во времени или в других ситуациях.
Наши данные о продажах показывают доход от продуктов и услуг по месяцам. При переводе в столбчатую диаграмму, как указано выше, видно, что услуги всегда выше, чем продукты, но более изменчивы. Было бы трудно получить эту историю только по цифрам!
Те же данные можно преобразовать в гистограмму. В этом случае и, вероятно, с большинством временных рядов гистограммы действительно работают лучше всего.
Как создать гистограмму или гистограмму:
- Выберите свои данные
- Нажмите «Вставка» → «Диаграмма».
- Выберите столбец или полосу
В вашей версии Excel могут немного отличаться меню. Вы также можете щелкнуть значок, который выглядит как диаграмма или график.
Примеры линейных графиков
Линейные графики больше подходят для случаев, когда отдельные точки данных не так важны. Технический анализ может основываться на простом линейном графике, включая коэффициенты регрессии, значения наклона и экстраполяцию.
В приведенном выше примере линейного графика мы использовали те же данные, что и раньше, но есть несколько отличий: мы показываем общее количество продаж (вместо дохода) и его отображаются с течением времени таким образом, который может лучше объяснить тенденции в этом бизнесе.
Как создать линейный график:
- Выберите свои данные
- Нажмите «Вставка» → «Диаграмма».
- Выбрать строку
Примеры круговых диаграмм
Круговые диаграммы могут быть наиболее привлекательными из всех вариантов визуализации данных. В двух словах, круговые диаграммы представляют собой круглые изображения статистических пропорций с использованием делений. Чем больше кусок пирога, тем больше представленность и, в более общем плане, важность.
Используя другой «срез» тех же данных, мы можем увидеть, какие регионы принесли большую часть дохода.
Как создать круговую диаграмму:
- Выберите свои данные
- Нажмите «Вставка» → «Диаграмма».
- Выберите круговую диаграмму.
Примеры расширенной визуализации данных Excel
Усовершенствованная визуализация данных становится важной, когда простые диаграммы просто не подходят. Это может быть в том случае, когда между значениями нет большой разницы или вы хотите передать несколько значений одновременно.
Объединение диаграмм и графиков
Мы показали доход по месяцам в долларах. И мы показали продажи по месяцам, по количеству покупок. Каждый из них полезен по-своему, но более полная картина становится видна, когда мы объединяем их в одну визуализацию данных.
В июле и августе продаж много, но доход от продажи меньше. Эту историю было нелегко увидеть, пока мы не объединили линейный график и столбчатую диаграмму.
Как создать комбинированную диаграмму:
Расширенный тип комбинированной диаграммы недоступен в первом меню. Однако вы можете изменить тип, включив в него столбец и строку. Обратите внимание, что выбранный параметр имеет значения линии на второй оси Y, что позволяет отображать разные масштабы на одной диаграмме.
Создание диаграмм с накоплением
Еще один расширенный тип диаграммы — это диаграмма с накоплением.Здесь мы будем использовать те же данные, что и в нашей первой столбчатой диаграмме, но информация будет отображаться в меньшем пространстве, с одним столбцом в месяц.
Дополнительным преимуществом здесь является то, что нам проще показать общий доход за месяц. Таким образом, в столбце каждого месяца сообщаются три значения. Составная диаграмма – это расширенный вариант визуализации Excel, который действительно эффективен!
Как создать диаграмму с накоплением:
Как и в случае с комбинированной диаграммой, вам нужно заглянуть в меню типов диаграмм и выбрать их визуально. Конечно, с помощью дизайна диаграмм вы можете сделать гораздо больше, но Excel не может делать все визуализации, как мы сейчас увидим.
Визуализация данных Excel на карте
Один из лучших способов визуального представления данных – карта. Не все данные соответствуют этой модели, но если у вас есть места — адреса, города или почтовые индексы — их нанесение на карту имеет большой смысл. Excel не может сделать это сам, но, к счастью, картографический инструмент BatchGeo для Excel упрощает эту задачу.
Нанесение адресов в качестве маркеров карты
Чтобы создать карту, убедитесь, что в вашей электронной таблице Excel, Google Sheet или Numbers есть данные о местоположении. Возьмите приведенную выше таблицу об автомеханиках в районе Санта-Моники, которую мы использовали для создания следующей карты.
Как:
Сумма и средние данные в кластерах карты
Карты — отличный способ визуализации данных. Однако часто мы можем обнаружить, что имеем более четырех точек данных. Когда из-за «перегрузки маркерами» на вашей карте остаются сотни маркеров, из-за чего вы не можете четко видеть тенденции, вы можете суммировать и усреднять свои данные с помощью кластеризации карты.
Допустим, у вас есть данные, подобные приведенной выше карте, которые содержат данные о доходах домохозяйств в более чем 3000 округов США. Без кластеризации карты она будет казаться переполненной, и вам будет сложно визуализировать свои данные. Выполнив приведенные ниже действия, чтобы включить кластеризацию карты, вы снова сможете четко визуализировать свои данные.
Как:
Создайте свою карту
Независимо от того, какие визуализации Excel вы используете, вы можете добавить их на карту. Используйте наш картографический инструмент, чтобы создать собственную встраиваемую карту, или попробуйте включить в карту встроенные диаграммы, чтобы смешивать и сопоставлять визуализации.
Важно! В Excel для Microsoft 365 и Excel 2021 функция Power View удалена 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный интерфейс Power BI Desktop, который можно загрузить бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
В Power View в SharePoint 2013 и Excel 2013 можно быстро создавать различные визуализации данных, от таблиц и матриц до гистограмм, столбцов и пузырьковых диаграмм, а также наборов из нескольких диаграмм. Для каждой визуализации, которую вы хотите создать, вы начинаете с листа Power View, создавая таблицу, которую затем легко конвертируете в другие визуализации, чтобы найти ту, которая лучше всего иллюстрирует ваши данные.
Создать визуализацию
Создайте таблицу на листе Power View, отметив таблицу или поле в списке полей или перетащив поле из списка полей на лист. Power View рисует таблицу на листе, отображая фактические данные и автоматически добавляя заголовки столбцов.
Преобразуйте таблицу в визуализацию, выбрав тип визуализации на вкладке "Дизайн". В зависимости от данных в таблице доступны различные типы визуализации, чтобы обеспечить наилучшую визуализацию этих данных.
Совет. Чтобы создать другую визуализацию, запустите другую таблицу, щелкнув пустой лист перед выбором полей в разделе полей списка полей.
В этой статье
Примеры визуализаций, доступных в Power View
Слайсер фильтрует отчет на хлеб
Навигация по потоку плиток для плиток, в настоящее время на круассане
Карта в контейнере плитки, отфильтрованная до текущей плитки (круассан)
Линейная диаграмма в контейнере плитки, показывающая потребляемое количество и количество серверов, отфильтрованное по круассанам с января по декабрь
Множественные, отфильтрованные по хлебу и отсортированные в порядке убывания количества подаваемых блюд
Столбчатая диаграмма с фильтром по хлебу, показывающая количество обслуживаемых и потребляемых продуктов
Диаграммы
Power View предлагает несколько вариантов диаграммы: круговая, столбчатая, столбчатая, линейная, точечная и пузырьковая. Диаграммы могут иметь несколько числовых полей и несколько рядов.У вас есть несколько вариантов оформления диаграммы: отображение и скрытие меток, легенд и заголовков.
Диаграммы интерактивны: щелкая значения на одной диаграмме, вы:
Выделите это значение на этой диаграмме.
Отфильтровать по этому значению во всех таблицах, матрицах и плитках в отчете.
Выделите это значение на всех остальных диаграммах в отчете.
Диаграммы также интерактивны в настройках презентации, например, в режимах чтения и полноэкранном режиме в Power View в SharePoint или на листе Power View в книге Excel, сохраненной в службах Excel или просматриваемой в Microsoft 365.
Круговые диаграммы
Круговые диаграммы в Power View могут быть простыми или сложными. Вы можете создать круговую диаграмму, которая раскрывается при двойном щелчке по срезу, или круговую диаграмму, на которой отображаются вложенные срезы внутри более крупных цветных срезов. Вы можете перекрестно отфильтровать круговую диаграмму с другой диаграммой. Скажем, вы щелкаете полосу на гистограмме. Часть круговой диаграммы, относящаяся к этому столбцу, выделена, а остальная часть круговой диаграммы окрашена в серый цвет. Прочтите о круговых диаграммах в Power View.
Точечные и пузырьковые диаграммы
Точечные и пузырьковые диаграммы — отличный способ отобразить множество связанных данных на одной диаграмме. В точечных диаграммах по оси X отображается одно числовое поле, а по оси Y — другое, что позволяет легко увидеть взаимосвязь между двумя значениями для всех элементов диаграммы.
В пузырьковой диаграмме третье числовое поле определяет размер точек данных. Вы также можете добавить ось воспроизведения в точечную или пузырьковую диаграмму, чтобы просматривать данные по мере их изменения с течением времени.
Линейные, гистограммы и гистограммы
Линейные, гистограммы и гистограммы удобны для сравнения точек данных в одном или нескольких рядах данных. В линейных, гистограммах и столбчатых диаграммах по оси X отображается одно поле, а по оси Y — другое, что позволяет легко увидеть взаимосвязь между двумя значениями для всех элементов диаграммы.
Гистограммы
На гистограмме категории располагаются по вертикальной оси, а значения по горизонтальной оси. Рассмотрите возможность использования гистограммы в любом из этих случаев:
У вас есть один или несколько рядов данных, которые вы хотите построить.
Ваши данные содержат положительные, отрицательные и нулевые (0) значения.
Вы хотите сравнить данные по нескольким категориям.
Ярлыки осей слишком длинные.
В Power View вы можете выбрать один из трех подтипов гистограмм: с накоплением, с накоплением на 100% и с кластерами.
Столбчатые диаграммы
Данные, расположенные в столбцах или строках на листе, можно представить в виде гистограммы. Столбчатые диаграммы полезны для отображения изменений данных за определенный период времени или для иллюстрации сравнений между элементами. В гистограммах категории располагаются по горизонтальной оси, а значения — по вертикальной.
В Power View вы можете выбрать один из трех подтипов гистограммы: с накоплением, с накоплением на 100 % и с кластерами.
Линейные диаграммы
Линейные диаграммы равномерно распределяют данные о категориях по горизонтальной оси (категории), а все числовые данные по вертикальной оси (значения).
Рассмотрите возможность использования линейной диаграммы со шкалой времени вдоль горизонтальной оси. На линейных диаграммах даты отображаются в хронологическом порядке с определенными интервалами или базовыми единицами, такими как количество дней, месяцев или лет, даже если даты на листе расположены не по порядку или в одних и тех же базовых единицах.
Карты в Power View используют фрагменты карты Bing, поэтому вы можете масштабировать и панорамировать, как и на любой другой карте Bing. Добавление местоположений и полей ставит точки на карте. Чем больше значение, тем больше точка. Когда вы добавляете ряд с несколькими значениями, вы получаете круговые диаграммы на карте, где размер круговой диаграммы показывает размер итогового значения. Узнайте больше о картах в Power View.
Несколько: набор диаграмм с одинаковыми осями
С помощью мультипликаторов вы можете создать серию диаграмм с одинаковыми осями X и Y и расположить их рядом друг с другом, что упрощает одновременное сравнение множества различных значений. Множественные диаграммы иногда называют «решетчатыми диаграммами».
Матрицы
Матрица похожа на таблицу тем, что состоит из строк и столбцов. Но у матрицы есть следующие возможности, которых нет у таблицы:
Отображать данные без повторяющихся значений.
Отображать итоги и промежуточные итоги по строкам и столбцам
С помощью иерархии вы можете детализировать/детализировать
Свернуть и развернуть дисплей
Карточки
Таблицу можно преобразовать в серию карточек, отображающих данные из каждой строки таблицы в формате карточки, например каталожной карточки.
Плитки
Таблицу или матрицу можно преобразовать в плитки для интерактивного представления табличных данных. Тайлы — это контейнеры с динамической полосой навигации. Плитки действуют как фильтры — они фильтруют содержимое внутри плитки в соответствии со значением, выбранным на полосе вкладок. На плитку можно добавить несколько визуализаций, и все они будут отфильтрованы по одному и тому же значению. Вы можете использовать текст или изображения в качестве вкладок. Узнайте больше о плитках в Power View.
На этом изображении показано количество медалей, завоеванных странами, соревнующимися в конькобежном спорте.
Отчеты об анализе данных можно отображать в Excel несколькими способами. Однако, если результаты анализа данных можно визуализировать в виде диаграмм, выделяющих важные моменты в данных, ваша аудитория сможет быстро понять, что вы хотите спроецировать в данных. Это также оказывает положительное влияние на стиль презентации.
В этой главе вы узнаете, как использовать диаграммы Excel и функции форматирования Excel на диаграммах, которые позволят вам выделить результаты анализа данных.
Визуализация данных с помощью диаграмм
В Excel диаграммы используются для графического представления любого набора данных. Диаграмма — это визуальное представление данных, в котором данные представлены такими символами, как столбцы на линейчатой диаграмме или линии на линейной диаграмме. Excel предоставляет вам множество типов диаграмм, и вы можете выбрать тот, который соответствует вашим данным, или вы можете использовать параметр рекомендуемых диаграмм Excel, чтобы просмотреть диаграммы, настроенные для ваших данных, и выбрать один из них.
Дополнительную информацию о типах диаграмм см. в учебнике по диаграммам Excel.
В этой главе вы познакомитесь с различными методами, которые можно использовать с диаграммами Excel для более эффективного выделения результатов анализа данных.
Создание комбинированных диаграмм
Предположим, у вас есть целевая и фактическая прибыль за 2015–2016 финансовый год, полученная в разных регионах.
Для этих результатов мы создадим кластеризованную столбчатую диаграмму.
Как вы заметили, на этой диаграмме сложно представить быстрое сравнение между целевыми и фактическими показателями. Это не оказывает реального влияния на ваши результаты.
Лучший способ отличить два типа данных для сравнения значений — использовать комбинированные диаграммы. В Excel 2013 и более поздних версиях для той же цели можно использовать комбинированные диаграммы.
Используйте вертикальные столбцы для целевых значений и линию с маркерами для фактических значений.
- Перейдите на вкладку ДИЗАЙН под вкладкой ИНСТРУМЕНТЫ ДЛЯ ДИАГРАММ на ленте.
- Нажмите «Изменить тип диаграммы» в группе «Тип». Появится диалоговое окно "Изменить тип диаграммы".
Измените тип диаграммы для серии "Фактические" на "Линия с маркерами". Предварительный просмотр появится в разделе Пользовательская комбинация.
Отобразится ваша настроенная комбинированная диаграмма.
Как вы видите на диаграмме, целевые значения указаны в столбцах, а фактические значения отмечены вдоль линии. Визуализация данных стала лучше, поскольку она также показывает тенденцию ваших результатов.
Однако этот тип представления не работает, когда диапазоны данных двух ваших значений данных значительно различаются.
Создание комбинированной диаграммы с дополнительной осью
Предположим, у вас есть данные о количестве отгруженных единиц вашего продукта и о фактической прибыли за 2015–2016 финансовый год, полученной вами в разных регионах.
Если вы используете ту же комбинированную диаграмму, что и раньше, вы получите следующее —
На диаграмме данные о количестве единиц не видны, так как диапазоны данных значительно различаются.
В таких случаях можно создать комбинированную диаграмму с дополнительной осью, чтобы на основной оси отображался один диапазон, а на дополнительной оси — другой.
Появится диалоговое окно "Вставить диаграмму" с выделенным полем "Комбинация".
В поле "Тип диаграммы" выберите -
Линия с маркерами серийного номера единиц
Сгруппированный столбец для фактической прибыли серии
Отметьте флажок Secondary Axis справа от номера серии единиц и нажмите OK.
Предварительный просмотр диаграммы отображается в разделе "Пользовательская комбинация".
Комбинированная диаграмма отображается со вспомогательной осью.
Вы можете наблюдать значения фактической прибыли на основной оси и значения количества единиц на дополнительной оси.
Важное наблюдение на приведенной выше диаграмме относится к кварталу 3, где число проданных единиц больше, но фактическая полученная прибыль меньше. Вероятно, это можно отнести к расходам на рекламу, которые были понесены для увеличения продаж. Ситуация улучшилась в 4-м квартале с небольшим снижением продаж и значительным ростом фактической прибыли.
Различающие ряды и оси категорий
Предположим, вы хотите спрогнозировать фактическую прибыль, полученную за 2013–2016 годы.
Создайте кластеризованный столбец для этих данных.
Как вы заметили, визуализация данных неэффективна, так как годы не отображаются. Этого можно избежать, изменив год на категорию.
Удалите заголовок года в диапазоне данных.
Теперь год считается категорией, а не серией. Ваша диаграмма выглядит следующим образом —
Элементы диаграммы и стили диаграммы
Элементы диаграммы дают больше описаний вашим диаграммам, что помогает визуализировать ваши данные более осмысленно.
В правом верхнем углу диаграммы появляются три кнопки –
Подробное объяснение см. в учебнике по диаграммам Excel.
Линию тренда можно использовать для графического отображения тенденций в данных. Вы можете расширить линию тренда на диаграмме за пределы фактических данных, чтобы предсказать будущие значения.
Ярлыки данных
Excel 2013 и более поздние версии предоставляют различные варианты отображения меток данных. Вы можете выбрать одну метку данных, отформатировать ее по своему усмотрению, а затем использовать функцию «Клонировать текущую метку», чтобы скопировать форматирование в остальные метки данных на диаграмме.
Метки данных на диаграмме могут иметь различные эффекты, форму и размер.
Содержимое ячейки также можно отобразить как часть метки данных с помощью команды «Вставить поле метки данных».
Быстрый макет
Вы можете использовать Quick Layout, чтобы быстро изменить общий макет диаграммы, выбрав один из предопределенных вариантов макета.
- Нажмите на диаграмму.
- Перейдите на вкладку ДИЗАЙН в разделе ИНСТРУМЕНТЫ ДЛЯ ДИАГРАММ.
- Нажмите "Быстрый макет".
Будут отображаться различные возможные макеты. По мере того, как вы перемещаетесь по параметрам макета, макет диаграммы меняется на этот конкретный параметр.
Выберите макет, который вам нравится. Диаграмма будет отображаться с выбранным макетом.
Использование изображений в столбчатых диаграммах
Вы можете усилить представление данных, используя изображение вместо столбцов.
Нажмите столбец на столбчатой диаграмме.
В серии "Формат данных" нажмите "Заполнить".
В разделе "Вставить изображение из" укажите имя файла или, при необходимости, буфер обмена, если вы скопировали изображение ранее.
Выбранное изображение появится вместо столбцов на диаграмме.
Таблица диапазонов
Возможно, вам придется представить результаты опроса покупателей продукта из разных регионов. Band Chart подходит для этой цели. Ленточная диаграмма представляет собой линейную диаграмму с добавленной затененной областью для отображения верхней и нижней границ групп данных.
Предположим, что результаты вашего опроса клиентов из восточного и западного регионов по месяцам равны –
Здесь в данных 80 % — это Высокий уровень.
С помощью Band Chart вы можете отобразить результаты опроса следующим образом:
Создайте линейную диаграмму из ваших данных.
Измените тип диаграммы на −
Ваша диаграмма выглядит следующим образом.
Вы получите полосы вместо столбцов.
Чтобы сделать диаграмму более презентабельной –
- Добавить заголовок диаграммы.
- Настройка диапазона вертикальной оси.
- Измените цвета полос на зеленый-желтый-красный.
- Добавьте ярлыки к группам.
Конечным результатом является диаграмма диапазонов с определенными границами и результатами опроса, представленными по диапазонам. Из диаграммы можно быстро и ясно увидеть, что если результаты опроса для региона Запад удовлетворительны, то для региона Восток наблюдается спад в последнем квартале и они требуют внимания.
Таблица термометра
Если вам нужно представить целевое значение и фактическое значение, вы можете легко создать диаграмму термометра в Excel, которая выразительно показывает эти значения.
С помощью диаграммы термометра вы можете отображать свои данные следующим образом:
Расположите свои данные, как показано ниже –
Как вы заметили, правая колонка — это Target.
Как вы заметили, первичная ось и вторичная ось имеют разные диапазоны.
И для основной оси, и для дополнительной оси будут установлены значения от 0% до 100%. Целевой столбец скрывает фактический столбец.
- Щелкните правой кнопкой мыши видимый столбец (Цель)
- В разделе "Формат данных" выберите
- Нет заполнения для FILL
- Сплошная линия для ГРАНИЦЫ
- Синий для цвета
- В элементах диаграммы снимите флажок
- Ось → Основная горизонтальная
- Ось → Дополнительная вертикаль
- Сетка
- Название диаграммы
- Щелкните правой кнопкой мыши область диаграммы.
- В параметрах «Формат области диаграммы» выберите
- Нет заполнения для FILL
- Нет строки для ГРАНИЦЫ
Измените размер области диаграммы, чтобы она приняла форму термометра.
Вы получили диаграмму термометра, на которой показано фактическое значение по сравнению с целевым значением. Вы можете сделать эту диаграмму термометра более впечатляющей, отформатировав ее.
- Вставьте прямоугольник, наложенный на синюю прямоугольную часть диаграммы.
- В параметрах «Формат фигуры» выберите –
- Градиентная заливка для FILL
- Линейный для типа
- 180 0 для угла
- Вставьте овал внизу.
- Форматировать фигуру с теми же параметрами.
Результатом является диаграмма термометра, с которой мы начали.
Диаграмма Ганта
Диаграмма Ганта — это диаграмма, на которой ряд горизонтальных линий показывает объем работы, выполненной за определенные периоды времени, по отношению к объему работы, запланированному на эти периоды.
В Excel вы можете создать диаграмму Ганта, настроив тип гистограммы с накоплением, чтобы она отображала задачи, продолжительность задач и иерархию. На диаграмме Ганта в Excel в качестве единицы времени по горизонтальной оси обычно используются дни.
Рассмотрите следующие данные, где столбец —
- Задача представляет Задачи в проекте.
- Начало – количество дней, прошедших с Даты начала проекта.
- Длительность представляет продолжительность Задачи.
Обратите внимание, что началом любой задачи является начало предыдущей задачи + Продолжительность. Это тот случай, когда Задачи находятся в иерархии.
Каскадная диаграмма
Каскадная диаграмма — один из самых популярных инструментов визуализации, используемых в малых и крупных компаниях. Каскадные диаграммы идеально подходят для того, чтобы показать, как вы пришли к чистой стоимости, такой как чистая прибыль, путем разбивки кумулятивного эффекта положительных и отрицательных вкладов.
Excel 2016 поддерживает тип диаграммы "водопад".Если вы используете более ранние версии Excel, вы все равно можете создать каскадную диаграмму с помощью гистограммы с накоплением.
Столбцы имеют цветовую кодировку, чтобы вы могли быстро отличить положительные числа от отрицательных. Столбцы начального и конечного значений начинаются на горизонтальной оси, а промежуточные значения являются плавающими столбцами. Из-за такого вида каскадные диаграммы также называют мостовыми диаграммами.
Рассмотрите следующие данные.
Подготовить данные для каскадной диаграммы
Убедитесь, что столбец "Чистый денежный поток" находится слева от столбца "Месяцы" (это связано с тем, что вы не будете включать этот столбец при создании диаграммы)
Добавить 2 столбца — увеличение и уменьшение для положительных и отрицательных денежных потоков соответственно
Добавить столбец «Начало» — первый столбец на диаграмме с начальным значением в «Чистом денежном потоке»
Добавить столбец End — последний столбец на диаграмме с конечным значением в чистом денежном потоке
Добавить столбец с плавающей запятой, который поддерживает промежуточные столбцы
Вычислите значения для этих столбцов следующим образом
В столбце с плавающей запятой вставьте строку в начале и в конце. Поместите n произвольное значение 50000. Это просто для того, чтобы было немного места слева и справа от графика
Данные будут следующими.
Укажите название диаграммы. Отобразится диаграмма водопада.
Спарклайны
Спарклайны – это крошечные диаграммы, размещенные в отдельных ячейках, каждая из которых представляет строку выбранных вами данных. Они позволяют быстро увидеть тенденции.
Вы можете добавить спарклайны с помощью инструмента быстрого анализа.
- Выберите данные, для которых вы хотите добавить спарклайны.
- Оставьте пустой столбец справа от данных для спарклайнов.
Кнопка "Быстрый анализ" появляется в правом нижнем углу выбранных данных.
Нажмите кнопку "Быстрый анализ". Появится панель быстрого анализа с различными параметрами.
Нажмите SPARKLINES. Отображаемые параметры диаграммы основаны на данных и могут различаться.
Нажмите "Линия". Линейная диаграмма для каждой строки отображается в столбце справа от данных.
Сводные диаграммы
Сводные диаграммы используются для графического обобщения данных и изучения сложных данных.
Сводная диаграмма отображает ряды данных, категории и оси диаграммы так же, как и стандартная диаграмма. Кроме того, он также предоставляет вам интерактивные элементы управления фильтрацией прямо на диаграмме, чтобы вы могли быстро анализировать подмножество ваших данных.
Сводные диаграммы удобны, когда у вас есть данные в огромной сводной таблице или много сложных данных на листах, содержащих текст и числа. Сводная диаграмма может помочь вам разобраться в этих данных.
Сводную диаграмму можно создать из
- Сводная таблица.
- Таблица данных как отдельная таблица без сводной таблицы.
Сводная диаграмма из сводной таблицы
Чтобы создать сводную диаграмму, выполните следующие действия:
- Нажмите на сводную таблицу.
- Нажмите «АНАЛИЗ» в разделе «ИНСТРУМЕНТЫ ОБЪЕДИНЯЕМОЙ ИНСТРУМЕНТА» на ленте.
- Нажмите на сводную диаграмму. Появится диалоговое окно "Вставить диаграмму".
Выберите Clustered Column из параметра Column.
Нажмите "ОК". Отображается сводная диаграмма.
В сводной диаграмме есть три фильтра: "Регион", "Продавец" и "Месяц".
Выберите параметр «Управление фильтром регионов». Появится окно поиска со списком всех регионов. Рядом с пунктом «Регионы» появятся флажки.
Выберите варианты Восток и Юг.
Отфильтрованные данные отображаются как в сводной диаграмме, так и в сводной таблице.
Сводная диаграмма без сводной таблицы
Вы можете создать отдельную сводную диаграмму без создания сводной таблицы.
- Нажмите на таблицу данных.
- Перейдите на вкладку "Вставка".
- Нажмите "Сводная диаграмма" в группе "Диаграммы". Появится окно «Создать сводную диаграмму».
- Выберите таблицу/диапазон.
- Выберите место, где вы хотите разместить сводную диаграмму.
Вы можете выбрать ячейку на существующем рабочем листе или на новом рабочем листе. Нажмите "ОК".
Появляются пустая сводная диаграмма и пустая сводная таблица, а также список полей сводной диаграммы для построения сводной диаграммы.
Выберите поля для добавления в сводную диаграмму
Расположите поля, перетащив их в ФИЛЬТРЫ, ОБОЗНАЧЕНИЯ (СЕРИИ), ОСЬ (КАТЕГОРИИ) и ЗНАЧЕНИЯ
Используйте элементы управления фильтрами на сводной диаграмме, чтобы выбрать данные для размещения на сводной диаграмме
Один из самых ценных и обильных ресурсов, которыми располагает бизнес, — это данные. Поскольку ежеминутно и ежедневно генерируются огромные объемы данных, собранная информация может использоваться практически при каждом бизнес-решении, что часто приводит к благоприятным результатам.
На рынке существует множество инструментов визуализации данных, предназначенных для создания иллюстраций в коммерческих целях. К счастью, один из самых популярных и простых в использовании вариантов, скорее всего, уже установлен на вашем компьютере: Microsoft Excel.
Если у вас нет доступа к Microsoft Excel, рассмотрите возможность использования бесплатных вариантов, таких как Google Таблицы, для аналогичного, хотя и более ограниченного опыта.
Хотя Excel не является программным обеспечением для визуализации, это универсальный и мощный инструмент для профессионалов всех уровней, которые хотят анализировать и иллюстрировать наборы данных. Вот типы визуализаций данных, которые вы можете создать в Excel, и шаги, необходимые для этого, а также несколько советов, которые помогут вам в этом.
Бесплатная электронная книга: руководство для начинающих по данным и аналитике
Получите бесплатную электронную книгу уже сегодня.
Типы визуализации данных в Excel
Существуют различные методы визуализации данных, которые вы можете использовать в Excel, в зависимости от доступных вам данных и цели, которую вы пытаетесь достичь, в том числе:
- Круговые диаграммы
- Гистограммы
- Гистограммы
- Диаграммы с областями
- График рассеяния
Для иллюстрации больших или сложных наборов данных можно использовать другие методы визуализации. К ним относятся:
- Хронология
- Диаграммы Ганта
- Тепловые карты
- Выделить таблицы
- Маркированные диаграммы
Более сложные визуализации, например те, которые содержат графические элементы, такие как географические тепловые карты, могут быть недоступны для создания в Excel или требуют дополнительных инструментов.
Как создавать визуализации данных в Excel
Этапы создания визуализации данных в Excel зависят от выбранного вами типа графика или диаграммы. Для базовых визуализаций процесс во многом такой же. Для более сложных наборов данных и иллюстраций могут потребоваться дополнительные шаги.
Чтобы создать визуализацию данных в Excel, начните с создания упорядоченной электронной таблицы. Это должно включать ярлыки и ваш окончательный набор данных.
Затем выделите данные, которые вы хотите включить в визуальный элемент, включая ярлыки. Выберите «Вставить» в главном меню и выберите тип диаграммы или графика, который вы хотите создать. После того, как вы сделаете свой выбор, визуализация автоматически появится в вашей электронной таблице.
Щелкните правой кнопкой мыши диаграмму или график, чтобы изменить такие сведения, как заголовок, метки осей и цвета. При этом откроется всплывающее окно или боковая панель с параметрами для добавления легенды, настройки масштаба и изменения стилей и размеров шрифта.
Ваш браузер не может воспроизвести предоставленный видеофайл.
Советы по созданию визуализаций в Excel
1. Выберите правильный тип визуализации
Чтобы создать эффективную визуализацию данных, очень важно выбрать правильный тип диаграммы или графика. Учитывайте тип используемых вами данных, размер набора данных и предполагаемую аудиторию.
Несоответствие между типом используемых данных и визуальным элементом, используемым для их представления, может отрицательно сказаться на понимании информации зрителями. Например, от того, работаете ли вы с качественными или количественными данными, зависит, как вы должны отображать информацию.
Ваша целевая аудитория также влияет на то, насколько простой или сложной должна быть ваша иллюстрация. Например, при выступлении перед большой аудиторией или заинтересованными лицами высокого уровня полезно выделить в презентации основные тенденции и идеи, а не отдельные точки данных.
2. Удалить ненужные или неточные данные
В конечном счете, качество вашей визуализации зависит от качества используемых вами данных. По этой причине важно очистить данные после их сбора, чтобы удалить любую нерелевантную или неточную информацию.Этот процесс часто называют обработкой или очисткой данных.
Отказ от тщательной очистки данных перед их использованием может нанести ущерб их целостности и привести к неточным или вводящим в заблуждение визуализациям данных.
3. Укажите контекст для визуализации
При необходимости добавьте ключ или легенду и дополнительный контекст, чтобы помочь зрителям понять вашу иллюстрацию.
Например, рассмотрим тепловую карту, которая показывает частоту случаев заражения COVID-19 в определенном месте за определенный период. Чтобы четко понять представляемую информацию, зрители должны знать такие детали, как изучаемый период, источник данных и значение каждого цвета.
Этот контекст важен, поскольку помогает зрителям интерпретировать отображаемую информацию. Например, без ключа, четко определяющего систему окраски, было бы практически невозможно узнать, что означает каждый цвет, что делает тепловую карту бесполезной.
4. Расскажите историю
Наконец, ключом к созданию убедительной визуализации является использование данных, чтобы рассказать историю. Если данные иллюстрируют тенденцию или подтверждают гипотезу, ваша визуализация должна сделать это ясным. В конце концов, цель визуализаций – представить результаты таким образом, чтобы зрители легко их усвоили и поняли.
История не только делает визуализацию более интересной и увлекательной, но и помогает принимать решения на основе данных. Кроме того, это помогает заинтересованным сторонам понять суть ваших выводов и, в свою очередь, информирует их о процессах принятия решений.
Принятие бизнес-решений на основе данных
Визуализация данных — это мощный инструмент для решения деловых вопросов и принятия обоснованных решений. Научившись создавать эффективные иллюстрации, вы сможете делиться результатами с ключевыми заинтересованными сторонами и другими аудиториями в увлекательной и понятной форме.
Вам не обязательно быть аналитиком или специалистом по обработке и анализу данных, чтобы воспользоваться преимуществами визуализации данных. Специалисты любого уровня и опыта могут развивать навыки работы с данными, чтобы более эффективно общаться в своих организациях и принимать обоснованные решения.
Заинтересованы ли вы в улучшении своих аналитических навыков? Узнайте больше о бизнес-аналитике — одном из трех курсов, входящих в состав CORe, — который научит вас применять аналитические методы в Excel для решения реальных бизнес-задач.
Вы замечали, что люди вздыхают, когда вы открываете электронную таблицу, чтобы поделиться своими блестящими аналитическими данными? Может быть, это не вы — или ваша аудитория — виноваты. Может быть, вы страдаете от синдрома скучной и чрезмерно загруженной таблицы?
Вот 10 диаграмм, на которых можно представить данные в удобной форме, чтобы людям было легко понять, о чем вы говорите. Они не требуют, чтобы люди щурились и много думали; вместо этого эти диаграммы делают с вашими данными то же, что фотография делает со всем остальным: показывайте, а не рассказывайте.
Вы можете перестать утомлять всех одними и теми же кусками пирога. Попробуйте подать пончики или показать им водопад. Это веселее.
Спарклайны: наглядное отображение ваших данных
Диаграммы в ячейках — это своего рода проекционный дисплей для ваших данных, обеспечивающий немедленный визуальный контекст в электронных таблицах. Функция Sparkline, представленная в Excel 2013, позволяет выбирать данные из строк или столбцов ячеек и отображать строки, столбцы или удобные диаграммы выигрышей/проигрышей.
Это просто! Просто выберите диапазон ячеек рядом с данными, которые вы хотите отобразить, затем нажмите «Вставить» на ленте пользовательского интерфейса и нажмите «Линия» в группе «Спарклайны» (вы также можете нажать «Столбец» или «Выигрыш/проигрыш»). В диалоговом окне «Создание спарклайнов» щелкните текстовое поле «Диапазон данных» и выберите строки или столбцы данных, которые вы хотите отобразить. В текстовом поле «Диапазон местоположения» должны отображаться ячейки, используемые для хранения графиков спарклайнов, но вы можете настроить это, выделив текст в этом поле и с помощью мыши выбрав строку или столбец ячеек. Нажмите OK, и появятся диаграммы в ячейках, отображающие данные. Выберите ячейку диаграммы Sparkline, нажмите элемент «Дизайн» на ленте и поэкспериментируйте с параметрами форматирования, чтобы получить желаемый вид.
Выделяйте неудачи с помощью встроенных диаграмм
Спарклайны удобны.Мне особенно нравится параметр Win/Loss, который отображает иконографию и цвет в зависимости от того, является ли значение положительным или отрицательным. Используйте его, скажем, для нормализации региональных данных о продажах по сравнению с прогнозом и пометки отстающих. Но что, если вы хотите встроить визуальный контекст внутри самой ячейки, а не сбоку? Время для условного форматирования и диаграмм в ячейках!
Выберите диапазон данных, нажмите элемент «Условное форматирование» на ленте и нажмите «Наборы значков», а затем один из простых вариантов в разделе «Направление». Теперь в каждой ячейке данных отображается значок стрелки. Но они не выровнены относительно нуля (и это визуально довольно занято). Итак, выбрав диапазон, нажмите «Условное форматирование», «Управление правилами» и дважды щелкните только что созданное правило. В диалоговом окне «Редактировать описание правила» установите для двух верхних раскрывающихся списков «Значок» значение «Без значка ячейки», а для двух верхних элементов «Значение» — значение 0. Щелкните раскрывающееся меню «больше чем» и установите для первого значения «>», а для второго — « >=». Установите для обоих раскрывающихся списков «Тип» значение «Число». В самом нижнем элементе Icon установите для значка красную стрелку вниз. Нажмите ОК и еще раз ОК. Теперь все отрицательные ячейки (и только отрицательные) помечаются.
Положите свои способности на стол
Таблицы — одна из моих любимых функций в Excel благодаря мощной функции "Фильтр", которая позволяет сортировать и фильтровать даже самые большие наборы данных одним щелчком мыши. Но скрытое преимущество — это волшебство, которое оно дает вашим графикам. Например, добавьте новый столбец данных в конец таблицы, и связанная диаграмма автоматически расширится, чтобы добавить новый ряд данных. Красиво.
Чтобы преобразовать диапазон данных в таблицу, выберите соответствующие ячейки (включая строки заголовков и столбцы) и нажмите «Вставить», затем «Таблица» в меню ленты. Убедитесь, что флажок «Моя таблица имеет заголовки» установлен, затем нажмите «ОК». Бум! Данные получают удобное форматирование и добавляют раскрывающиеся элементы управления фильтром вдоль строки заголовка. Теперь, когда рядом с вашими данными отображается встроенная диаграмма, введите новый элемент строки заголовка и некоторые базовые данные в ячейках под ним. Как только вы нажмете «Ввод» в ячейке строки заголовка, ваша диаграмма изменит размер и отложит новый элемент на оси диаграммы. По мере добавления данных в каждую ячейку диаграмма обновляется для отображения новых данных.
Более того, таблицы привносят в ваши диаграммы полезную магию фильтров. Предположим, у вас есть таблица с данными о месячном бюджете 100 отделений на местах. Вы можете визуализировать только офисы, скажем, в Огайо, щелкнув раскрывающийся список «Фильтр» в столбце «Штат», затем сняв флажок «Выбрать все», прежде чем прокрутить список до Огайо и отметить его. Нажмите OK, и диаграмма обновится.
Солнечные диаграммы: намного лучше, чем круговая диаграмма!
Новый тип диаграммы "солнечные лучи" в Excel 2016 невероятно полезен для понимания взаимосвязи между данными, создания вложенных иерархий данных и демонстрации того, как детализированные элементы объединяются для заполнения больших групп данных. Думайте об этом как о многоуровневой круговой диаграмме.
Диаграммы "Солнечные лучи" в Excel делают свое дело, считывая структуру набора данных. Например, у нашей вымышленной компании есть три стратегические линейки продуктов (виджеты, контроллеры, соединители) и ведется электронная таблица, в которой отслеживаются продажи этих линеек продуктов на промышленных, специализированных и потребительских рынках. Далее они разбивают эти категории по отраслям (автомобильная, медицинская и т. д.). Как только данные будут размещены правильно, выберите соответствующие ячейки и нажмите «Вставка», «Диаграмма», «Рекомендуемые диаграммы» и перейдите на вкладку «Все диаграммы», затем нажмите «Солнечные лучи» в списке.
Каскадные диаграммы для чистых и понятных данных
В Office 2016 в арсенале Excel появился новый тип диаграмм — каскадная диаграмма. Он отлично подходит для демонстрации того, как положительные и отрицательные значения влияют на общую сумму, например, для отслеживания стоимости финансового портфеля с течением времени или визуализации доходов и расходов. В отличие от линейной диаграммы, которая показывает тенденции, каскадная диаграмма подчеркивает отдельные прибыли и убытки.
Чтобы создать каскадную диаграмму, создайте простой массив из двух столбцов, указав месяцы в левом столбце и суммы в долларах (положительные и отрицательные) в правом. Выберите массив, нажмите «Вставить» и щелкните значок «Рекомендуемые диаграммы». Затем щелкните вкладку «Все диаграммы» в диалоговом окне «Вставка диаграмм». Нажмите «Водопад» на правой панели и нажмите «ОК». Вас приветствует довольно плохо отформатированная диаграмма.
Далее мы ограничим эту ось Y, чтобы огромное значение Start не перекрывало другие данные. Щелкните правой кнопкой мыши метки оси Y и выберите Формат оси.На панели «Параметры оси» введите 15400 и нажмите «ОК». Затем щелкните элемент «Число» и в разделе «Категория» выберите «Валюта», а в появившемся поле «Десятичные знаки» введите 0 и нажмите «Ввод». Наконец, установите эту первую точку данных на диаграмме как элемент «Итого». Нажмите на первый столбец в диаграмме и нажмите на него еще раз, чтобы выделить только этот столбец (остальные должны выглядеть блеклыми). На панели «Формат данных» щелкните значок «Параметры серии» и установите флажок «Установить как общее». Выбранный столбец становится серым.
Ммм. пончики
Круговые диаграммы могут быть худшими. Им не хватает визуальной точности столбчатых диаграмм с накоплением, и они часто переполнены, из-за чего меньшие точки данных превращаются в непостижимые клинья. Более того, они вызывают злоупотребления в виде причудливых трехмерных пирогов и извлеченных фрагментов, которые могут сбить с толку незадачливых зрителей.
Вот почему вы можете подумать о том, чтобы подавать пончики в следующий раз, когда доберетесь до круговой диаграммы Excel. Как и круговые диаграммы, кольцевые диаграммы отлично подходят для визуализации вклада в общую сумму. Но дополнительное пустое пространство и менее распространенное использование делают их освежающей сменой темпа в корпоративном графике.
Чтобы создать кольцевую диаграмму, выберите свои данные, затем нажмите «Вставить», щелкните значок «Вставить круговую диаграмму» или «Кольцевую диаграмму» и нажмите «Кольцевая диаграмма». Чтобы настроить презентацию, щелкните правой кнопкой мыши тело диаграммы и выберите Формат ряда данных. В появившейся панели измените значение размера отверстия пончика примерно на 60%. (Я считаю, что значение по умолчанию 75 % слишком узкое.)
Создайте собственную диаграмму Ганта для отслеживания проектов
Гистограмму с накоплением можно превратить в удобную диаграмму Ганта. Создайте таблицу из четырех столбцов с пометками «Начало», «Этап», «Дата» и «По заданию» в верхней строке. В ячейках B2–B7 введите этапы проекта, такие как «Планирование», «Сборка», «Утверждение» и т. д. В ячейке A2 в разделе «Начало» введите дату начала для элемента первого этапа, затем в ячейках с D2 по D7 (в разделе «По заданию») введите число дней для завершения каждого этапа. В ячейке C2 введите «= A2» и отформатируйте ячейку как «Общий». Вы увидите число, например 43205, которое является кодом времени/даты Excel, необходимым для макета диаграммы. Наконец, в ячейке C3 введите «=C2+D2», затем скопируйте и вставьте эту формулу в ячейки с C4 по C7.
Выделите ячейки, которые вы ввели, от столбца B до D, и нажмите «Вставить». Щелкните значок гистограммы, а затем линейчатую диаграмму с накоплением. Теперь давайте сосредоточим внимание на графике. Щелкните правой кнопкой мыши метки оси X и выберите Формат оси. На панели «Параметры оси» щелкните элемент «Число» и в разделе «Категория» выберите «Дата» в раскрывающемся списке. В поле Тип выберите более короткий формат даты. Затем в верхней части панели в разделе «Границы» в текстовом поле «Минимум» введите значение из столбца C первого элемента в списке задач. Это должно быть что-то вроде 43205. Вы заметите, что этапы расположены в обратном порядке. Щелкните правой кнопкой мыши вертикальную ось и выберите Формат оси. На панели "Параметры оси" установите флажок "Категории в обратном порядке".
Покажите свою цель с помощью диаграммы термометра
Хотите визуализировать достижение цели? Как насчет сбора средств, пытающегося собрать 20 000 долларов? Для этого отлично подходит таблица термометра. Мы упростим задачу с помощью массива из четырех ячеек: цель и пожертвования слева, а также 20 000 и 16 425 долларов справа. Выберите ячейки, нажмите «Вставить» на ленте, щелкните значок «Столбчатая диаграмма», а затем щелкните элемент «Сгруппированная столбчатая диаграмма». Нажмите значок «Переключить строку/столбец» на ленте, чтобы в поле диаграммы отобразилась цифра «1» в метке оси X.
Щелкните правой кнопкой мыши меньшую панель "Пожертвования" и выберите "Формат ряда данных". Затем нажмите «Вторичная ось» на панели «Параметры серии». Полоса исчезнет за большой полосой цели, и появится вторичная метка оси Y. Щелкните правой кнопкой мыши левую ось, выберите «Формат оси», а затем введите 20000 в поле «Максимум» на панели «Параметры оси». Сделайте то же самое для правой (дополнительной) оси.
Очистите панель целей, щелкнув ее правой кнопкой мыши, щелкнув значок "Заливка и линия" на панели, а затем выбрав "Без заливки" и задав для параметра "Граница" черную линию. Отформатируйте панель «Пожертвования», указав цвет заливки по вашему выбору и установив для границы значение «Без линии». Очистите диаграмму, удалив правую ось и метки оси X. Кроме того, подтяните левую ось на панели «Ось формата». В параметрах оси установите для поля Число знаков после запятой значение 0. Добавьте галочки по своему усмотрению.
Диаграммы, которые меняются по мере ввода данных
Вот хитрость: диаграмма Excel, которая отображает определенные данные из большого массива на основе введенных вами данных.Вы делаете это с помощью (невероятно полезных) функций ПОИСКПОЗ и СМЕЩ, которые извлекают данные из массива и представляют их в ячейках, с которыми связана диаграмма. Предположим, у вас есть семь столбцов, представляющих годы, и 12 строк, представляющих торговых представителей, а ячейки содержат годовые суммы продаж.
В столбце справа от последнего столбца введите в каждую ячейку следующую формулу:
Это указывает Excel искать в строке заголовка массива значение, соответствующее году в ячейке J1, поэтому Excel может использовать функцию OFFSET, чтобы извлечь данные из этого года и отобразить их в ячейках в столбце I. Таким образом, если вы введете 2014 в ячейку J1, формулы в столбце I отобразят данные из ячеек под 2014 (в данном случае столбец E). Теперь все, что вам нужно сделать, это создать диаграмму, которая использует столбец A в качестве оси X и столбец I в качестве данных. Столбец обновится, чтобы отобразить данные за тот год, который вы вводите в ячейку J1. Изящно.
Другие способы динамической диаграммы
Хорошо, давайте сделаем еще один шаг и обновим линейную диаграмму на основе прокручиваемого списка. Чуть ниже того же массива данных, который мы использовали на последнем слайде, мы будем использовать удобные функции OFFSET и MATCH, чтобы добавить ячейки в строку 15 с формулами, которые извлекают данные о продажах на основе имени представителя, введенного в ячейку A18. Эти формулы находятся в ячейках с A15 по H15 и читаются следующим образом:
Выберите ячейки A1:H1 (значения по оси X) и ячейки A15:H15 (имя представителя и соответствующие данные о продажах), нажмите «Вставка», «Рекомендуемые диаграммы» и выберите «Линейная диаграмма» в верхней части списка. Теперь настройте раскрывающийся список для чтения имен из ячеек A2: A13. Нажмите A18, перейдите на вкладку «Данные» на ленте и нажмите «Проверка данных», а затем снова «Проверка данных». Щелкните «Список» в раскрывающемся списке «Разрешить», затем щелкните поле «Источник» и с помощью мыши выберите ячейки с A2 по A13. Нажмите «ОК». Теперь, когда вы нажимаете на A18, появляется раскрывающийся элемент управления. Нажмите на нее, и вы увидите прокручиваемый список имен всех ваших представителей. Выберите одно из них, и не только A18 обновится, чтобы отобразить это имя, но и все ячейки в строке 15 обновятся с данными этого повторения, а также обновится связанная диаграмма.
Читайте также: