Диаграмма типа термометр в эксель
Если Вы активно работаете с диаграммами в Excel, то наверняка знаете, что их в программе представлено достаточно много. Графики, гистограммы, линейчатые, круговые, лепестковые и т.д. Подходящий вариант для своих данных можно найти практически всегда. Однако, некоторые задачи визуализации решить стандартными средствами не получится.
Одна из самых распространенных проблем - отсутствие красивой и наглядной диаграммы для графического отображения единственного показателя (или, на языке диаграмм, единственной точки данных). Под это описание подходит, например, показатель KPI. Он обычно выражается в % в диапазоне от 0 до 100. Ни одна из встроенных диаграмм сходу не дает красивого и наглядного отображения такого показателя.
Но если проявить немного фантазии и навыков владения Excel, то можно создать собственные наглядные типы диаграмм на основе стандартных, которые красиво и легко решат поставленную задачу. Разберем построение трех типов таких диаграмм: Термометр , Круговая шкала и Спидометр . Если Вы не знаете как выполнить какое-то действие, указанное в статье, то можете посмотреть подробнейший пошаговый видеоурок по этой теме со всеми объяснениями . Видео найдете в конце статьи . Итоговый результат будет выглядеть вот так:
Термометр
Построение любой диаграммы начинается с подготовки исходных данных. В нашем случае у каждой диаграммы будет одна главная точка данных - показатель выполнения KPI в процентах от 0 до 100 . Для наглядности мы также создадим цветную шкалу, которая будет обозначать зоны выполнения KPI (Плохо - Нормально - Хорошо). Это будет второй набор данных.
Таким образом, для построения диаграммы Термометр нам нужны следующие небольшие таблички с данными:
Обратите внимание на таблицу для шкалы. Проценты в ней указаны по следующему принципу:
- Плохая зона - первые 30% (то есть от 0% до 30%)
- Нормальная зона - следующие 40% (то есть от 30% до 70%)
- Хорошая зона - последние 30% (то есть от 70% до 100%)
Общая сумма процентов, очевидно, должна быть равна 100%.
Построение диаграммы
Теперь по этим данным можно строить диаграмму. Выделяем таблицу со шкалой и строим " Нормированную гистограмму с накоплением ". Скорее всего, Excel построит три одинаковых столбца для каждого ряда: Плохо, Норма и Хорошо . Чтобы они приняли вид шкалы, надо выделить диаграмму и на вкладке " Конструктор " выбрать " Строка/столбец ". После выполнения этой команды шкала обретет нормальный вид.
Далее нужно убрать с нее все лишнее: название, подпись ряда данных. Также необходимо настроить цвет каждой зоны на шкале, и ширину диаграммы в целом и самой шкалы. Для изменения ширины самой шкалы, кликаем на ней правой кнопкой мыши, выбираем " Формат ряда данных " и меняем значение ползунка " Боковой зазор ". В итоге получится примерно следующее:
Теперь нужно на шкалу термометра добавить наш показатель KPI. Копируем его из нужной ячейки, выделяем диаграмму и нажимаем Ctrl+V . Скорее всего новые данные сольются с уже имеющейся шкалой и добавятся сверху зеленой зоны. Придется их донастроить вручную. Для начала меняем цвет самого верхнего сектора шкалы на черный. Затем кликаем правой кнопкой мыши на верхней части зеленой шкалы и выбираем " Изменить тип диаграммы для ряда ". В этом окне надо будет установить для нового добавленного ряда галочку " Вспомогательная ось " и тип диаграммы - " Гистограмма с группировкой ".
После нажатия кнопки OK нужно будет обязательно удалить вспомогательную ось с процентами и настроить ширину черного столбца ("ртутной полоски" нашего термометра). Останется только кликнуть на этом столбце правой кнопкой мыши и выбрать " Добавить подписи данных ". Диаграмма-термометр готова!
Круговая шкала
Для построение диаграммы с круговой шкалой нам, помимо самого показателя KPI, также понадобятся дополнительные данные для построения шкалы. В данном случае все просто. Нам нужен столбец из единичек. Количество единиц = количество делений на нашей круговой шкале. Для создания двадцати делений нам нужен будет столбец из двадцати единиц. Кроме того, понадобится не только показатель выполнения KPI, но и количество процентов, которых не хватило до 100%. Его вычислить просто: 1 - KPI . Исходные данные имеют такой вид:
Построение диаграммы
Построение начинается с создания шкалы. Выделяем столбец с единицами и строим кольцевую диаграмму. Удаляем всё лишнее: название и легенду. По умолчанию, диаграмма будет иметь очень пестрый вид. Каждый из 20 секторов будет окрашен в свой цвет.
Нужно будет вручную перекрасить шкалу в правильные цвета. Кликаете на кольцо правой кнопкой мыши и применяете нужную заливку сразу ко всем секторам. Затем придется задать вручную цвет для некоторых секторов. Итак, у нас 20 секторов. Мы знаем, что первые 30% - плохая зона. То есть первые 20 * 30% = 6 секторов будут красные. Следующие 8 секторов (40%) - будут желтые. Последние 6 секторов - зеленые.
Чтобы покрасить один сектор, кликните на кольцо, чтобы выделились все сектора. Затем кликните еще раз прямо по нужному сектору. Выделится только он. Измените его заливку. Затем, нажимая Tab , перемещайтесь на каждый следующий сектор и выбирайте нужные цвета. В итоге должно получиться вот так:
Теперь добавим на шкалу второе кольцо поверх первого. У него будет всего 2 зоны: выполненный KPI и невыполненный KPI. Копируем два числа из таблицы, выделяем диаграмму и жмем Ctrl+V . Появится второе (внешнее) кольцо на диаграмме. Кликаете на него правой кнопкой мыши и выбираете " Изменить тип диаграммы для ряда ". В окне изменения типа для Ряд2 включаете галочку " Вспомогательная ось ". Второе кольцо наложится сверху на первое.
Останется поменять заливку верхнего кольца. Для зоны выполненного KPI полностью убираете всю заливку, а для зоны невыполненного - ставите белую и задаете прозрачность в районе 20% (это делается на панели " Формат точки данных "). Также не лишним будет вставить внутрь кольца надпись, которая будет отображать процент выполнения KPI числом (для большей наглядности).
Сегодня разберём, как в Excel создать довольно популярную диаграмму "Торнадо" или, как ее еще называют, Шторм-диаграмму. Выглядит она вот так:
Разберем пошагово процесс построения.
1) Подготовка исходных данных
Берём любую табличку, в которой необходимо сравнить ДВЕ ГРУППЫ данных между собой, по ОДНОЙ КАТЕГОРИИ :
Для того, чтобы данные на диаграмме отображались ПРАВИЛЬНО , одной из групп изменяем значения на отрицательные.
В любой свободной ячейке пишем -1; копируем ее , далее выделяем диапазон первой группы, нажимаем ПКМ (правую кнопку мыши) -> Специальная вставка. -> Специальная вставка. Откроется окно Специальная вставка. Там нужно отметить пункт Значение [блок Вставить] и пункт Умножить [блок Операции].
Наши данные, стали Отрицательными
Чтобы данные были отрицательными, но при этом на Диаграмме отображались без МИНУСОВ, нужно изменить числовой формат ячеек.
- Выделяем наш ОТРИЦАТЕЛЬНЫЙ диапазон -> ПКМ(правую кнопку мыши)-> Формат Ячеек
- Данные остались отрицательными, но отображаются без минусов
2. Построение диаграммы
Теперь наши Данные ГОТОВЫ и можно переходить к самой Диаграмме. Выделяем ВЕСЬ диапазон -> Вставка -> Гистограмма -> Линейчатая с Накоплением
3. Форматирование диаграммы
Чтобы диаграмма стала похожа на показанную в начале статьи, нужно ее отформатировать.
Перенесём Вертикальную ось, чтобы она не отвлекала. Для этого делаем двойной клик левой кнопкой мыши по Горизонтальной оси -> появится окно ФОРМАТ ОСИ -> Параметры оси -> Максимальное значение по оси [блок Вертикальная ось пересекает]. Теперь ось будет располагаться в правой части диаграммы.
Добавляем Подписи данных. Выбираем Ряд Зелёные Яблоки -> кликаем на Зелёный плюсик справа [блок Элементы Диаграмм] -> Подписи данных -> У края, внутри .
Повторяем всё то же самое с Рядом Красные Яблоки, но . Подписи данных -> У основания, внутри
Изменяем цвет Рядов в соответствии с названием группы. Выбираем Ряд Красные Яблоки -> кликаем ПКМ -> Заливка -> выбираем Красный цвет. Повторяем тоже самое с Рядом Зелёные Яблоки, только цвет, соответственно, зелёный. В конце меняем цвет шрифта подписи данных на тот, который будет различим на фоне заливки.
Готово! Получаем информативную диаграмму, позволяющую сравнить 2 ряда данных по одно категории.
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Создать диаграмму термометра в 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 с помощью удобной функции
Kutools for Excel предоставляет различные специальные типы диаграмм, которых нет в Excel, например Пулевая диаграмма, Целевая и фактическая диаграмма, График промокания и так далее. С его простым инструментом- График термометра, вы можете создать диаграмму термометра на основе значений ячеек или значений, введенных вручную, а метки оси Y могут отображаться в виде процентов или обычных чисел по мере необходимости. Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!
Видео: Создание диаграммы термометра в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Вы когда-нибудь представляли себе, как создать диаграмму целей термометра в Excel? Это руководство покажет вам подробные шаги по созданию диаграммы целей термометра в Excel.
Создать диаграмму целей термометра
Например, у вас есть данные, необходимые для создания диаграммы целей термометра, и выполните следующие действия.
1. Выберите данные и щелкните Вставить > Колонка > Столбец с накоплением 100%. Смотрите скриншот:
Наконечник: Вы также можете выбрать столбец с накоплением по мере необходимости.
2. Нажмите Дизайн > Переключить строку / столбец. Смотрите скриншот:
3. Выберите легенды на диаграмме, нажмите Удалить ключ, чтобы удалить их.
Внимание: В Excel 2013 легенды не добавляются по умолчанию, поэтому вы можете просто пропустить этот шаг.
4. Щелкните правой кнопкой мыши на Весь проект серия (красная), затем выберите Форматировать ряд данных из контекстного меню.
5. В появившемся диалоговом окне / панели выполните следующие операции:
(1) Нажмите Варианты серий раздел и перетащите стрелку под Ширина зазора налево, пока 0%.
(2) Нажмите Заполнять раздел и проверьте Без заливки;
(3) Нажмите выделяющий цвет раздел, проверка Сплошная линия, и выберите нужный цвет в цвет выпадающий список.
(4) Нажмите Стили границ раздел и введите 2pt в Ширина текстовое окно.
В Excel 2013 Форматировать ряд данных хлеб,
(1) Щелкните значок Варианты серий вкладка и введите 0% в Ширина зазора текстовое окно;
(2) Щелкните значок Заливка и линия вкладка, проверка Без заливки вариант в Заполнять раздел; следующий в Граница раздел проверьте Сплошная линия выберите цвет границы, нажав кнопку цвет и введите 2pt в Ширина текстовое окно.
6. Закройте диалоговое окно, удалите горизонтальную ось и щелкните макет > Сетки > Основные горизонтальные линии сетки > все. Смотрите скриншот:
В Excel 2013 щелкните Дизайн > Добавить элемент диаграммы > Сетки > Первичный Главный Горизонтальный.
7. Теперь вы видите, что цвет верхней границы зеленый, щелкните границу и щелкните Формат > Форма чтобы выбрать нужный вам цвет. Смотрите скриншот:
8. Теперь измените размер области диаграммы по своему усмотрению.
9. Нажмите Вставить > Формы, и выберите Овал фигуру, затем нарисуйте овал, чтобы перекрыть диаграмму, см. снимок экрана:
10. Щелкните на Формат > Форма, затем выберите цвет, соответствующий цвету вашей диаграммы. Смотрите скриншот:
Теперь диаграмма целей термометра создана.
Создайте диаграмму термометра в Excel за 3 шага с помощью мощного инструмента диаграмм (рекомендуется)
Встроенная функция создания диаграммы термометра настолько сложна и требует много времени, однако Графики группа Kutools for Excel обеспечивает быстрый способ создания диаграммы термометра, для чего достаточно всего 3 щелчков мышью.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс > Графики > Прогресс > График термометра.
2. в График термометра диалоговом окне выберите текущие значения и целевые значения по мере необходимости, затем выберите один тип диаграммы, который вы хотите создать в Тип вертикальной оси .
3. Нажмите Ok, то была создана диаграмма термометра. Разве это не легко?
Наконечник:
1. Если вы впервые используете этот инструмент, вы можете нажать на Образец кнопку, чтобы показать, как работает этот инструмент.
2. Вы можете изменить цвет диаграммы по своему усмотрению под Формат меню.
В качестве одной из измерительных диаграмм диаграмма термометра обычно используется для сравнения текущего значения и целевого значения в процентах. здесь я представлю График термометра of Kutools for Excel, который поможет вам максимально просто создать диаграмму термометра в Excel.
Создайте диаграмму термометра в Excel
Вы можете использовать График термометра функция для быстрого создания диаграммы термометра в Excel.
1. Нажмите Кутулс > Графики > Прогресс > График термометра для включения этой функции.
2. В График термометра диалоговое окно, настройте следующим образом:
(1) В текущая стоимость поле, выберите ячейку, содержащую текущее значение;
(2) В Целевое значение поле, выберите ячейку, содержащую целевое значение;
(3) В Тип вертикальной оси раздел, пожалуйста, проверьте Авто Выбор or Процент как вам нужно.
3, Нажмите Ok кнопка. Теперь сразу создается диаграмма термометра.
If Процент проверяется в Тип вертикальной оси раздел: | If Авто Выбор проверяется в Тип вертикальной оси раздел: |
Заметки
1. Если вы выяснили процентное соотношение текущего значения к целевому значению, вы можете указать текущее значение, используя только процентное соотношение, и щелкнуть Ok кнопку, чтобы получить диаграмму термометра.
2. Если и текущее, и целевое значения являются процентами, вы можете указать текущее и целевое значения обычным образом и щелкнуть Ok кнопку, чтобы создать диаграмму термометра.
3. Вы также можете ввести текущее значение и целевое значение вручную, указать тип вертикальной оси и щелкнуть Ok кнопку, чтобы получить диаграмму термометра.
4, Вы можете нажать Пример в График термометра диалоговое окно, чтобы открыть лист образцов. Однако эта операция закроет текущий диалог.
Демо: Создать диаграмму термометра в Excel
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
Читайте также: