Таблиця термометрів Excel - безкоштовне завантаження та створення

Цей підручник покаже, як створити діаграму термометрів у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

Графік термометрів - це спеціальна модифікація стовпчастої діаграми. Стовпи розташовані один над одним і візуально нагадують термометр, який заповнюється, коли ви просуваєтеся до своєї мети.

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

Але проста діаграма термометрів просто більше не зрізає гірчицю (давай, це 2022 рік!).

У цьому поглибленому покроковому навчальному посібнику ви дізнаєтесь, як побудувати надзвичайно універсальну діаграму цілей термометра з чудовою міткою, яка працює навіть тоді, коли ваші результати перевищують заявлену мету.

Просто перевірте:

Як ви незабаром побачите, побудова вражаючої діаграми термометрів вимагає чимало зусиль. Ось чому ми розробили надбудову Chart Creator-інструмент для створення розширених діаграм Excel за кілька кліків.

Отже, перейдемо до дрібниць.

Починаємо

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

Магазин заробив 21 734 доларів доходу, що трохи перевищило вашу початкову мету (пізніше ви можете покласти пляшку шампанського).

При цьому подивіться на набір даних для діаграми:

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

Набір даних має два стовпці:

• Оригінальні дані: Цей стовпець представляє ваші фактичні дані, якими б вони не були. Просто залиште як є.
• Входи діаграми: Тут і відбувається вся магія. Таблиця містить усі спеціальні значення, які ви будете використовувати для побудови діаграми. Також важлива примітка: категорії слід упорядковувати точно так само, як показано на скріншоті вище!

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

На жаль, немає іншого способу обійти це, але наведені нижче інструкції повинні полегшити його дотримання.

Отже, подорож починається!

Крок №1: Налаштуйте таблицю помічників.

Оскільки сполучна ланка тримає все це разом, кожен з елементів у другій таблиці має своє призначення. Давайте докладніше розглянемо цих поганих хлопчиків.

Цільовий дохід: Ця точка даних повинна відповідати цілі ((В7). Так просто.

Формула загального доходу: Ця функція IF запобігає укладання маркерів даних один на одного неправильним чином, якщо результат перевищує мету:

1	= IF (B6 <= B7, B6, B7)

Ось декодована версія на основі вихідних даних:

1	= IF ({Всього} <= {Ціль}, {Всього}, {Ціль})

Переклад на основну мову людини: якщо результат (В6) менше або дорівнює цілі (В7), формула повертає значення результату (В6)-у нашому випадку все, що падає нижче позначки 20 тисяч доларів. І навпаки, якщо результат (В6) перевищує мету (В7), формула повертає цільове значення (В7).

Швидка порада: натисніть кнопку «Формат облікового номера » кнопка (Вкладка "Домашня сторінка" > Група номерів), щоб відобразити вихід функції як валюту.

Досягнуто: Це Функція IF обчислює, чи ваша мета перевиконана чи ні. Коли це відбувається, формула віднімає мету (В7) від результату (В6) і повертає те, що залишилося. В іншому випадку формула просто показує нуль:

1	= IF (B7 <= B6, B6-B7, 0)

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

1	= IF ({Ціль} <= {Всього}, {Всього}-{Ціль}, 0)

Прогрес: Це досить зрозуміло. Нам потрібно виміряти прогрес у відсотках від мети для нашої надзвичайної марки. Просто поділіть результат (В6) за ціллю (В7) та відформатуйте результат у відсотках, клацніть на «Процентний стиль » кнопка (Вкладка "Домашня сторінка" > Група номерів).

1

= В6/В7

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

1	= {Усього}/{цілі}

Мітка: Глазур на торті. Цей об'єднує числові та відсоткові значення, що представляють прогрес у досягненні вашої мети, і чітко їх форматує. Для цього ми перейдемо до функції ТЕКСТ (новачки в Excel, підготуйте для впливу):

1	= TEXT (B6, "$#0.00") & "(" & TEXT (E5, "## 0%") & ")"

І декодована версія:

1	= TEXT ({Всього}, "{Форматувати значення як валюту}") & "(" & TEXT ({Прогрес}, "{Форматувати значення у відсотках}") & ")"

Якщо це здається лякаючим, перегляньте статтю, за посиланням, що стосується цієї функції.

Ось як це виглядає на практиці:

Вау, найгірше позаду. Щоб підсумувати все, погляньте ще раз на формули:

Налаштування діаграми	Формула
Формула загального доходу	= IF (B6 <= B7, B6, B7)
Досягнуто	= IF (B7 <= B6, B6-B7, 0)
Прогрес	= В6/В7
Мітка	= ТЕКСТ (В6, “$#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. Виберіть значення Label (E6).

Тепер нам потрібно це трохи прикрасити. Зробіть етикетку жирною, пофарбуйте її в білий колір та змініть розмір шрифту, щоб естетично покращити елемент (Вкладка "Домашня сторінка" > Група шрифтів).

Крок №7: Змінити мітку даних.

Після того, як ви встановили мітку, натисніть її до верхнього кінця відповідного маркера даних. Клацніть правою кнопкою миші на ярлику даних і виберіть "Форматувати мітки даних.”

Потім, у Параметри міток вкладка, під Положення мітки, виберіть "Inside End.”

Крок #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!