Таблица с термометри на 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 000 долара. Обратно, ако резултатът (В6) надвишава целта (В7), формулата връща стойността на целта (В7).

Бърз съвет: щракнете върху „Формат на счетоводен номер ” бутон (Раздел Начало > Група номера), за да покажете изхода на функцията като валута.

Постигнато: Това IF функция изчислява дали целта Ви е постигната или не. Когато това се случи, формулата изважда целта (В7) от резултата (В6) и връща останалото. В противен случай формулата просто показва нула:

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

Декодираната версия:

1	= IF ({Цел} <= {Общо}, {Общо}-{Цел}, 0)

Напредък: Този е доста обясним. Трябва да измерваме напредъка като процент от целта за нашия ултрафанзивен лейбъл. Просто разделете резултата (В6) от целта (В7) и форматирайте изхода като процент, щракнете върху „Процентен стил " бутон (Раздел Начало > Група номера).

1

= B6/B7

Декодирана версия:

1	= {Общо}/{цел}

Етикет: Черешката на тортата. Този комбинира числовите и процентните стойности, представляващи напредъка към постигането на вашата цел, и ги форматира добре. За да го издърпаме, ще се обърнем към функцията TEXT (начинаещи в Excel, подгответе се за въздействие):

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

И декодираната версия:

1	= TEXT ({Общо}, "{Форматиране на стойността като валута}") & "(" & TEXT ({Прогрес}, "{Форматиране на стойността като проценти}") & ")"

Ако това изглежда обезсърчително, вижте статията, свързана по -горе, обхващаща функцията.

Ето как изглежда на практика:

Уау, най -лошото е зад гърба ни. За да обобщим всичко, хвърлете втори поглед върху формулите:

Настройка на диаграма	Формула
Формула на общите приходи	= IF (B6 <= B7, B6, B7)
Постигнато	= IF (B7 <= B6, B6-B7, 0)
Напредък	= B6/B7
Етикет	= ТЕКСТ (B6, “$#0.00 ″) &” (”& TEXT (E5,“ ## 0%”) &“) ”

Веднъж там, преминете към следващата стъпка.

Стъпка 2: Създайте подредена колонна диаграма.

1. Маркирайте диапазона от клетки E2: E4 в Стойност
2. Изберете Вмъкване
3. Щракнете върху „Вмъкване на колона или стълбовидна диаграма”Икона.
4. Избирам "Подредена колона.”

Стъпка #3: Подредете сериите данни една върху друга.

След като диаграмата се появи, пренаредете серията от данни:

1. Кликнете върху диаграмата.
2. Превключете към Дизайн
3. Избирам "Превключване на ред/колона.”

Стъпка #4: Променете цветовете на маркера за данни.

За да превърнете диаграмата в нещо, наподобяващо термометър, започнете с промяна на цвета на маркерите за данни. Първо направете маркера за данни за Серия 1 „Целеви приходи“ (E2) прозрачен и поставете гранични линии около него.

Щракнете с десния бутон върху маркера за данни, представляващ „Целевите приходи“ (E2-долната част на диаграмата) и изберете „Форматиране на серия от данни.”

В прозореца със задачи, който се показва, направете следното:

1. Придвижете се до Fill & Line
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" в Код за форматиране поле и щракнете Добавяне.

Също така, под Отбележете марки, избирам "Вътре" от "Основен тип" падащо меню.

Сега преминете към Fill & Line раздел. Променете цвета на отметките на черен и задайте Ширина стойност до 1,5 точки.

Стъпка #12: Премахнете заглавието на диаграмата, линиите на мрежата и хоризонталната ос.

Премахнете елементите на диаграмата, които нямат практическа стойност: заглавието на диаграмата, линиите на мрежата и хоризонталната ос. Щракнете с десния бутон върху всеки елемент и изберете „Изтрий.”

Стъпка #13: Променете ширината на пролуката на сериите данни по вторичната ос.

Докато поставяте последните щрихи в диаграмата си, отслабнете тръбата на термометъра. Започнете със серията, поставена на вторичната ос (Серия 1 “Целеви приходи”).

Щракнете с десния бутон върху Серия 1 „Целеви приходи“ (E2), отвори Форматиране на серия от данни панела на задачите и задайте Ширина на пролуката стойност до 250%. Увеличете процента, ако искате да го отслабнете повече.

Стъпка #14: Променете ширината на пролуката на сериите от данни по основната ос.

След това щракнете още веднъж върху ръба на червената тръба, за да изберете Серия 2 “Формула на общите приходи” (E3), поставени върху основната ос. Задайте същото Ширина на пролуката стойност (250%).

Стъпка #15: Добавете крушката на термометъра към диаграмата.

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

1. Превключете към Вмъкване
2. Избери "Форми”Бутон.
3. Изберете „Овал.”

Задръж Преместване докато рисувате, за да създадете перфектен кръг. След това щракнете с десния бутон върху кръга и в менюто, което се показва, променете Контур на формата и Попълване на форма цветове до червено.

Само едно последно нещо. Копирайте кръга, като го изберете и натиснете Ctrl + C. След това изберете диаграмата и натиснете Ctrl + V за да поставите кръга в диаграмата на термометъра. Накрая преоразмерете и регулирайте положението на крушката, за да я побере в картината.

И така, ето го. Вече сте готови да създавате спиращи дъха диаграми на термометри в Excel!