Создание диаграммы-термометра в Excel

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

• Создание простой диаграммы-термометра в Excel
• Создание цветоизменяющейся диаграммы-термометра в Excel
• Создание простой диаграммы-термометра в Excel с помощью удобной функции
• Скачать пример файла диаграммы-термометра
• Видео: Создание диаграммы-термометра в Excel

Создание простой диаграммы-термометра в Excel

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

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

После подготовки данных выполните следующие шаги для создания диаграммы-термометра:

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

2. Гистограмма будет вставлена в лист. Затем щёлкните по этой диаграмме, выберите опцию Переключить строки/столбцы под вкладкой Конструктор, см. скриншот:

3. Вы получите новую диаграмму, как показано на скриншоте ниже:

4. Затем щёлкните правой кнопкой мыши второй столбец (целевой ряд) и выберите Формат ряда данных из контекстного меню, см. скриншот:

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

6. В диаграмме есть две вертикальные оси с разными значениями, поэтому щёлкните правой кнопкой мыши левую вертикальную ось и выберите Формат оси, см. скриншот:

7. В панели Формат оси, под значком Параметры оси, измените максимальное значение границы на 1 и минимальное значение на 0, см. скриншот:

8. Повторите этот шаг, чтобы изменить значения границ на 0 и 1 для вторичной оси. Теперь вы можете видеть, что как основная, так и вторичная ось установлены на 0% - 100%. См. скриншот:

9. Затем продолжайте щёлкать правой кнопкой мыши видимый ряд столбцов, выберите Формат ряда данных, см. скриншот:

10. В панели Формат ряда данных, под значком Заливка и линия, выберите Без заливки в разделе Заливка, выберите Сплошная линия в разделе Граница, а затем выберите синий цвет, который совпадает со столбцом, см. скриншот:

11. На этом этапе вам следует скрыть другие ненужные элементы, такие как заголовок диаграммы, линии сетки. Нажмите значок Элементы диаграммы, чтобы раскрыть окно, и затем снимите флажки с следующих операций:

• Оси > Основная горизонтальная, Вторичная горизонтальная
• Заголовок диаграммы
• Линии сетки

12. Затем щёлкните правой кнопкой мыши основную вертикальную ось и выберите Формат оси. В панели Формат оси, под значком Параметры оси, нажмите Деления и выберите Внутри из выпадающего списка Основной тип, см. скриншот:

13. Затем удалите границу с диаграммы, щёлкните правой кнопкой мыши область диаграммы и выберите Формат области диаграммы, см. скриншот:

14. В панели Формат области диаграммы нажмите значок Заливка и линия, затем выберите Без заливки и Без линии в разделах Заливка и Граница соответственно, см. скриншот:

15. Затем перетащите и измените размер диаграммы, чтобы она выглядела как термометр, см. скриншот:

16. В конце вы можете вставить круг в нижней части столбца. Нажмите Вставка > Фигуры > Овал, нарисуйте круг, придайте ему тот же цвет, что и у диаграммы-термометра, поместите его внизу серии столбцов, а затем отформатируйте его контур как без контура, и вы получите диаграмму-термометр, как показано на скриншоте ниже:

Создание цветоизменяющейся диаграммы-термометра в Excel

Иногда вы можете захотеть, чтобы цвет столбца диаграммы-термометра менялся при изменении фактического значения. Например, когда фактическое значение больше или равно 80%, цвет столбца оранжевый; когда фактическое значение находится между 50% и 80%, цвет столбца зелёный; если фактическое значение меньше или равно 50%, отображается синий столбец, как показано в демо ниже.

1. Сначала введите некоторые фиктивные данные, которые находятся в пределах указанного диапазона данных, в ячейки, как показано на скриншоте ниже:

2. Выберите диапазон A4:B7, затем нажмите Вставка > Вставить столбчатую или гистограммную диаграмму > Группированная гистограмма, и гистограмма будет вставлена в лист, см. скриншот:

3. Затем щёлкните по диаграмме, чтобы выбрать её, затем нажмите Переключить строки/столбцы под вкладкой Конструктор, и вы получите новую диаграмму, как показано на следующих скриншотах:

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

• Щёлкните правой кнопкой мыши по отличному ряду, затем нажмите Заливка фигуры из вкладки Формат, и выберите нужный цвет. Повторите тот же процесс для заливки цветом каждого ряда данных.
• Для последнего целевого ряда выберите Без заливки из выпадающего списка Заливка фигуры, а затем выберите цвет границы из выпадающего списка Контур фигуры.

5. Затем щёлкните любой ряд столбцов в диаграмме правой кнопкой мыши и выберите Формат ряда данных из контекстного меню, см. скриншот:

6. В панели Формат ряда данных, под значком Параметры ряда, измените Наложение рядов на 100%, теперь все ряды будут перекрывать друг друга, см. скриншот:

7. Затем щёлкните правой кнопкой мыши основную вертикальную ось и выберите Формат оси. В панели Формат оси, под значком Параметры оси, измените максимальное значение границы на 1 и минимальное значение на 0, см. скриншот:

8. Затем вам следует скрыть другие ненужные элементы и изменить деления основной вертикальной оси внутрь, как в шагах 10-11 первого метода. Наконец, измените размер диаграммы, чтобы она выглядела как термометр, см. скриншот:

9. После создания диаграммы-термометра на этом этапе вы должны вставить несколько формул в ячейки B4, B5, B6, чтобы заменить исходные фиктивные данные и сделать цвет диаграммы динамически изменяемым:

10. С этого момента, когда вы измените фактическое значение в ячейке A2, диаграмма-термометр будет динамически изменяться, как показано в демо ниже:

Создание простой диаграммы-термометра в Excel с помощью удобной функции

Скачать пример файла диаграммы-термометра

Видео: Создание диаграммы-термометра в Excel