Как визуализировать данные в Excel

Обновлено: 03.07.2024

Отчеты об анализе данных можно отображать в 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 для решения реальных бизнес-задач.
      
      Когда я показываю людям точечный график, они первым делом говорят: "Круто, но как мне это сделать?" и на этой странице есть все ваши ответы. Время от времени я публикую сообщения в блогах с пошаговыми инструкциями о том, как создавать потрясающие визуализации прямо в Excel, и я собрал эти инструкции для вас прямо здесь.
      
      Вы найдете больше вариантов диаграмм с обновленными инструкциями по их созданию, а также мой инструмент выбора диаграмм для количественных показателей в моей книге "Эффективная визуализация данных".
      
      Точечный график
      
      Горизонтальные гантели
      
      Вертикальные гантели
      
      Перекрывающиеся полосы
      
      Расходящаяся линейка с накоплением
      
      Эталонная линия
      
      Леденец
      
      График наклона
      
      Диаграмма рельефа
      
      Пиктограмма
      
      Впритык
      
      Список диаграмм
      
      Гистограммы
      
      Спарклайны и индикаторы
      
      Небольшие несколько строк
      
      Точка и полоса
      
      Стандартное отклонение
      
      Горизонтальные гантели
      
      Пропорциональный график
      
      Skyrocket Dataviz с графическими направляющими!
      
      Graph Guides — это специально созданный годичный спринт из 50 учебных пособий Академии.
      
      Когда вы зарегистрируетесь, мы оценим ваши текущие данные, а именно набор навыков, разработаем для вас индивидуальный план обучения и будем поддерживать вас, пока вы прокладываете путь к новым талантам.
      
      Получить сенсацию.
      
      Хотите раз в две недели получать советы и рекомендации по лучшей визуализации данных и отчетности? Получайте мой супер полезный информационный бюллетень прямо в свой почтовый ящик. Мы никогда не будем делиться вашей информацией с кем-либо еще. Это было бы отвратительно.
      
      Вы замечали, что люди вздыхают, когда вы открываете электронную таблицу, чтобы поделиться своими блестящими аналитическими данными? Может быть, это не вы — или ваша аудитория — виноваты. Может быть, вы страдаете от синдрома скучной и чрезмерно загруженной таблицы?
      
      Вот 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 обновятся с данными этого повторения, а также обновится связанная диаграмма.
      
      Читайте также: