Диаграмма термометра Excel - скачать бесплатно и как создать

В этом руководстве будет показано, как создать диаграмму термометра во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

График термометра - это особая модификация столбчатой ​​диаграммы с накоплением. Столбцы расположены друг над другом и визуально напоминают градусник, который заполняется по мере продвижения к цели.

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

Но простая диаграмма термометра больше не помогает справиться с горчицей (да ладно, это 2022 год!).

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

Просто проверьте это:

Как вы вскоре увидите, создание впечатляющей диаграммы термометра требует огромных усилий. Вот почему мы разработали надстройку Chart Creator, инструмент для создания расширенных диаграмм Excel всего за несколько кликов.

Итак, перейдем к мелочам.

Начиная

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

Выручка от магазина составила 21 734 доллара, что немного превысило вашу первоначальную цель (вы можете выпить бутылку шампанского позже).

При этом взгляните на набор данных для диаграммы:

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

Набор данных состоит из двух столбцов:

• Исходные данные: Этот столбец представляет ваши фактические данные, какими бы они ни были. Просто оставьте все как есть.
• Входные данные диаграммы: Здесь происходит все волшебство. Таблица содержит все специальные значения, которые вы будете использовать для построения диаграммы. Также важное замечание: категории должны быть упорядочены точно так же, как показано на скриншоте выше!

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

К сожалению, другого выхода нет, но приведенные ниже инструкции должны упростить выполнение.

Итак, путешествие начинается!

Шаг № 1: Настройте вспомогательную таблицу.

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

Целевой доход: Эта точка данных должна соответствовать значению цели (B7). Так просто.

Формула общего дохода: Эта функция ЕСЛИ предотвращает неправильное наложение маркеров данных друг на друга в случае, если результат превышает цель:

1	= ЕСЛИ (B6 <= B7, B6, B7)

Вот декодированная версия на основе исходных данных:

1	= ЕСЛИ ({Итого} <= {Цель}, {Итого}, {Цель})

Перевод на основной человеческий язык: если результат (B6) меньше или равно цели (B7), формула возвращает значение результата (B6) - в нашем случае все, что ниже отметки в 20 тысяч долларов. И наоборот, если результат (B6) превышает цель (B7) формула возвращает значение цели (B7).

Совет: щелкните значок «Формат бухгалтерского номера » кнопка (Вкладка "Главная" > Группа номеров) для отображения вывода функции в виде валюты.

Перевыполнено: Этот Функция ЕСЛИ рассчитывает, перевыполнена ли ваша цель. Каждый раз, когда это происходит, формула вычитает цель (B7) от результата (B6) и возвращает то, что осталось. В противном случае формула просто показывает ноль:

1	= ЕСЛИ (B7 <= B6; B6-B7; 0)

Расшифрованная версия:

1	= ЕСЛИ ({Цель} <= {Итого}, {Итого} - {Цель}, 0)

Прогресс: Это довольно понятно. Нам нужно измерить прогресс в процентах от цели для нашего ультрамодного лейбла. Просто разделите результат (B6) по цели (B7) и отформатируйте результат в процентах, щелкнув значок «Процент стиля » кнопка (Вкладка "Главная" > Группа номеров).

1

= B6 / B7

Расшифрованная версия:

1	= {Всего} / {Цель}

Этикетка: Глазурь на торте. Он объединяет числовые и процентные значения, представляющие прогресс в достижении вашей цели, и аккуратно их форматирует. Чтобы осуществить это, обратимся к функции ТЕКСТ (новички Excel, готовьтесь к удару):

1	= ТЕКСТ (B6, "$ # 0.00") & "(" & ТЕКСТ (E5, "## 0%") & ")"

И расшифрованная версия:

1	= ТЕКСТ ({Итого}, "{Форматировать значение как валюту}") & "(" & ТЕКСТ ({Прогресс}, "{Форматировать значение как проценты}") & ")"

Если это кажется сложным, ознакомьтесь со статьей, посвященной этой функции, по ссылке выше.

Вот как это выглядит на практике:

Уф, худшее уже позади. Подводя итог, взгляните еще раз на формулы:

Настройка диаграммы	Формула
Формула общего дохода	= ЕСЛИ (B6 <= B7, B6, B7)
Перевыполнено	= ЕСЛИ (B7 <= B6; B6-B7; 0)
Прогресс	= B6 / B7
Этикетка	= ТЕКСТ (B6, «$ # 0,00 ″) &» (»& ТЕКСТ (E5,« ## 0% ») &«) »

Оказавшись там, переходите к следующему шагу.

Шаг № 2: Создайте столбчатую диаграмму с накоплением.

1. Выделите диапазон ячеек E2: E4 в Ценить
2. Выберите Вставлять
3. Щелкните значок «Вставить столбец или гистограмму" значок.
4. Выбирать "Столбец с накоплением.”

Шаг № 3: Сложите ряды данных друг над другом.

Когда появится диаграмма, измените порядок рядов данных:

1. Щелкните диаграмму.
2. Переключитесь на Дизайн
3. Выбирать "Переключить строку / столбец.”

Шаг №4: Измените цвета маркеров данных.

Чтобы превратить диаграмму во что-то похожее на градусник, начните с изменения цвета маркеров данных. Сначала сделайте маркер данных для серии 1 «Целевой доход» (E2) прозрачным и обведите его границами.

Щелкните правой кнопкой мыши маркер данных, представляющий «Целевой доход» (E2- нижняя часть диаграммы) и выберите «Форматировать ряд данных.”

На всплывающей панели задач выполните следующие действия:

1. Перейдите к Заливка и линия
2. Под "Наполнять," Выбрать "Без заливки.”
3. Под "Граница," выбирать "Сплошная линия.”
4. Под "Граница, "Щелкните Цвет заливки значок, чтобы открыть цветовую палитру и выбрать красный.
5. Установить Ширина ценность для 2 пт.

Раскрасьте другие маркеры данных следующим образом:

1. Для серии 2 «Формула общей выручки» (E3):
   1. Выберите маркер данных (E3) с Форматировать ряд данных панель задач все еще открыта.
   2. Под "Наполнять," выбирать "Сплошная заливка.”
   3. Щелкните значок Цвет заливки значок и выберите красный.
2. Для серии 3 «Превышен» (E4):
   1. Выберите маркер данных (E4) с Форматировать ряд данных панель задач все еще открыта.
   2. Под "Наполнять," выбирать "Сплошная заливка.”
   3. Щелкните значок Цвет заливки значок и выберите светло-зеленый.

Шаг № 5: Добавьте метку данных на диаграмму.

Пришло время вставить красивую метку данных (E6), на которое у нас ушло столько крови, пота и слез.

Щелкните правой кнопкой мыши «Формула общего дохода для серии 2» (тот, с красной заливкой) и выберите «Добавить метки данных.”

Шаг № 6: Измените значение метки данных.

Свяжите метку со значением во второй таблице.

1. Дважды щелкните этикетку.
2. Тип “=” в Панель формул.
3. Выберите значение метки (E6).

Теперь нам нужно немного его приукрасить. Сделайте этикетку жирной, раскрасьте ее в белый цвет и измените размер шрифта, чтобы улучшить эстетический вид элемента (Вкладка "Главная" > Группа шрифтов).

Шаг № 7: Переставьте метку данных.

После настройки метки переместите ее к верхнему краю соответствующего маркера данных. Щелкните правой кнопкой мыши метку данных и выберите «Отформатируйте метки данных.”

Затем в Параметры метки вкладка под Положение ярлыка, выбирать "Внутренний конец.”

Шаг № 8: Переместите серию 1 «Целевой доход» на вторичную ось.

Чтобы вылепить из диаграммы форму термометра, вам необходимо получить Серия 1 «Целевой доход» (E2) в правильном положении.

1. Щелкните правой кнопкой мыши на Серия 1 «Целевой доход» (прозрачный в самом низу).
2. Выбирать "Форматировать ряд данных.”

На появившейся панели задач под Сюжетная серия на, выбирать "Вторичная ось.”

Шаг № 9: Удалите вторичную ось.

Вторичная ось сделала свое дело, поэтому она нам больше не нужна.

Щелкните правой кнопкой мыши дополнительную ось (столбец чисел справа) и выберите «Удалить.”

Шаг № 10: Измените диапазоны шкалы первичной оси.

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

Щелкните правой кнопкой мыши масштаб главной оси и выберите «Ось формата.”

Установить Минимальные границы ценность для -10000. Почему? Это освободит место под лампочкой термометра.

Это значение различается в зависимости от данных вашей диаграммы. Хорошее практическое правило: используйте номер своей цели и разделите его на два.

Например, предположим, что ваш целевой доход составляет 100 000 долларов. Тогда твой Минимальные границы ценность будет -50000.

Шаг № 11: Измените числовой формат шкалы первичной оси и добавьте отметки.

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

С Ось формата панель задач все еще открыта, под Параметры оси, перейдите к Числа раздел и сделайте следующее:

1. Для "Категория," Выбрать ""
2. Тип "$#,##0.00;;$0”В Код формата поле и щелкните Добавлять.

Также под Отметки, выбирать "Внутри" от "Основной тип" выпадающее меню.

Теперь переключитесь на Заливка и линия таб. Измените цвет меток на черный и установите Ширина ценность для 1,5 пт.

Шаг № 12: Удалите заголовок диаграммы, линии сетки и горизонтальную ось.

Удалите элементы диаграммы, не имеющие практического значения: заголовок диаграммы, линии сетки и горизонтальную ось. Щелкните правой кнопкой мыши каждый элемент и выберите «Удалить.”

Шаг № 13: Измените ширину промежутка ряда данных на вторичной оси.

По мере того, как вы наносите последние штрихи на диаграмму, уменьшите длину трубки термометра. Начните с серии, расположенной на вторичной оси (Серия 1 “Целевой доход”).

Щелкните правой кнопкой мыши «Целевой доход» серии 1 (E2), открыть Форматировать ряд данных область задач и установите Ширина зазора ценность для 250%. Увеличьте процент, если хотите еще больше похудеть.

Шаг № 14: Измените ширину промежутка ряда данных на первичной оси.

После этого еще раз щелкните край красной трубки, чтобы выбрать «Серия 2».Формула общего дохода” (E3), размещенный на главной оси. Установите то же самое Ширина зазора ценить (250%).

Шаг №15: Добавьте грушу термометра на диаграмму.

В качестве окончательной настройки создайте и добавьте на диаграмму грушу термометра.

1. Переключитесь на Вставлять
2. Выбрать "Формы" кнопка.
3. Выбирать "Овальный.”

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

И последнее. Скопируйте круг, выделив его и нажав Ctrl + C. Затем выберите диаграмму и нажмите Ctrl + V чтобы вставить кружок в диаграмму термометра. Наконец, измените размер и положение лампочки, чтобы она вписывалась в картинку.

Итак, вот оно. Теперь вы готовы создавать захватывающие дух диаграммы термометра в Excel!