Создание Диаграмма термометра в 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