0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Два самых простых способа создать динамический диапазон в диаграмме Excel

Как построить диаграмму по таблице в Excel: пошаговая инструкция

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

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

Как построить диаграмму по таблице в Excel?

  1. Создаем таблицу с данными. Исходные данные.
  2. Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы. Тип диаграмм.
  3. Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм. Тип гистограмм.
  4. После выбора определенного вида гистограммы автоматически получаем результат.
  5. Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы». График итоговые суммы.
  6. Сделаем подпись для вертикальной оси. Вкладка «Макет» — «Подписи» — «Названия осей». Выбираем вертикальную ось и вид названия для нее. Подпись вертикальной оси.
  7. Вводим «Сумма».
  8. Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения. Подписи данных.
  9. Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
  10. Изменим цвет и стиль. Измененный стиль графика.

Здесь мы выберем другой стиль графика (вкладка «Конструктор» — «Стили графика»).

Как добавить данные в диаграмму в Excel?

  1. Добавляем в таблицу новые значения — План. Добавлен показатели плана.
  2. Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
  3. Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» — «Легенда» — «Добавить легенду справа» (внизу, слева и т.д.). Получаем: Отображение показателей плана.
Читайте так же:
Как быстро создать уникальный список (удалить дубликаты) из заданного столбца в Excel?

Более сложный метод предполагает использование меню «Select Data Source» (открывается нажатием правой кнопки мыши «Select Data»).

Выбор источника данных.

При нажатии кнопки «Добавить» (элементы легенды) появляется новая строка, в которой можно выбрать диапазон данных.

Как поменять местами оси в диаграмме Excel?

  1. Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные». Выбрать данные.
  2. В открывшемся меню нажимаем кнопку «Строка/столбец».
  3. Значения для рядов и категорий поменяются местами автоматически. Результат.

Как закрепить элементы управления на диаграмме Excel?

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

  1. Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу». Форматировать как таблицу.
  2. В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы: Выпадающие списки.
  3. Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической: Динамическая диаграма.

На основе имеющейся информации можно создать «умную таблицу». Мы сразу же вставляем значения в таблицу, когда перед нами чистый лист: «Вставка» — «Таблица».

Как сделать диаграмму в процентах в Excel?

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

Исходные данные для данного примера:

  1. Выделяем данные A1:B8. «Вставка» — «Круговая» — «Объемная круговая». Объемная круговая.
  2. Вкладка «Конструктор» — «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами. Стили с процентами.
  3. Выбираем подходящий. Результат выбора.
  4. Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» — «Изменить тип диаграммы». Выбираем круговую с вторичной. Круговая с вторичной.
  5. Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных». Формат ряда данных.
  6. Задаем следующие параметры ряда: Параметры ряда.
  7. Получаем нужный вариант: Результат после настройки.
Читайте так же:
Как автоматически расширить панель формул в Excel?

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

Диаграмма Ганта — это способ представления информации в виде столбиков, иллюстрирующих деятельность в несколько этапов. Это красивая и простая техника.

  1. У нас есть таблица (учебная) со сроками сдачи отчетов. Таблица сдачи отчетов.
  2. Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
  3. Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
  4. Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» — «Использовать формулу для определения форматируемых ячеек».
  5. Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:

skachat-dashbord-csat-v-excel

Дашборд CSAT расчет индекса удовлетворенности клиентов в Excel.
Пример как сделать шаблон дашборда для формирования отчета по индексу удовлетворенности клиентов CSAT. Скачать готовый дашборд C-SAT для анализа индексов и показателей.

ejenedelnyy-grafik-2-taymfreyma

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

dashbord-skachat-v-excel

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

shablon-diagrammy-kpi

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

Для визуализации данных в Excel были разработаны диаграммы и графики.

Как создать диаграмму с интерактивными подписями данных в Excel

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

Как создать диаграмму с интерактивными подписями данных в Excel

  • Какова общая сумма ряда?
  • Какова доля каждого сегмента в ряду?
  • Каково процентное изменение по сравнению с предыдущим периодом?

Динамические подписи данных на гистограмме

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

Как создать график с динамическими подписями данных

  • Функция ТЕКСТ
  • Функция ВЫБОР
  • Сводная таблица
  • Срез
  • Гистограмма

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

Шаг 1: Создайте гистограмму с итоговыми данными

Как создать диаграмму с интерактивными подписями данных в Excel

Шаг 2: Расчет значений для подписей данных

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

Как создать диаграмму с интерактивными подписями данных в Excel

Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.

Шаг 3: Используйте формулу ТЕКСТ для формирования подписей

Надписи данных на графике обычно зависят от источника данных.

В Excel 2013 появилась новая функция под названием «Значения из ячеек». Эта функция позволяет указать диапазон, который будет использоваться для подписи данных.

Поскольку наши подписи данных переключаются между финансовым ($) и процентным (%) форматированием, нам нужен способ изменить форматирование чисел на диаграмме. В противном случае Excel отобразит десятичное число вместо процентного значения.

К счастью, это можно сделать с помощью функции TEXT. Функция ТЕКСТ позволяет взять число и указать формат, в котором оно должно быть отображено.

Как создать диаграмму с интерактивными подписями данных в Excel

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

Шаг 4: Использование функции ВЫБОР

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

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

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

Мы можем указать индекс с помощью функции SELECT (1,2,3). и она вернет значение, соответствующее этому индексу.

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

Как создать диаграмму с интерактивными подписями данных в Excel

Таким образом, срез будет определять индекс.

Шаг 5: Настройка подписей данных

Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию «Значения из ячеек», которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку «Выбрать диапазон» в меню Параметры подписи.

Как создать диаграмму с интерактивными подписями данных в Excel

Затем выберите диапазон, содержащий подписи данных для данной строки.

Как создать диаграмму с интерактивными подписями данных в Excel

Повторите этот шаг для каждого ряда гистограммы.

Шаг 6: Настройка сводной таблицы и среза

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

Как создать диаграмму с интерактивными подписями данных в Excel

Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

Как создать диаграмму с интерактивными подписями данных в Excel

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

Как создать диаграмму с интерактивными подписями данных в Excel

Форма нарезки может быть изменена в соответствии с планировкой вашего стола. Я изменил количество столбцов на 3, чтобы отобразить кнопки фрагментов горизонтально.

Как создать диаграмму с интерактивными подписями данных в Excel

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector