Топлинна карта в Excel е визуално представяне, което бързо ви показва сравнителен изглед на набор от данни.
Например в набора от данни по -долу мога лесно да забележа кои са месеците, когато продажбите са били ниски (подчертани в червено) в сравнение с други месеци.
В горния набор от данни цветовете се присвояват въз основа на стойността в клетката. Цветовата скала е от зелено до жълто до червено, като високите стойности получават зеления цвят, а ниските стойности получават червения цвят.
Създаване на топлинна карта в Excel
Въпреки че можете да създадете топлинна карта в Excel чрез ръчно цветно кодиране на клетките. Въпреки това ще трябва да го повторите, когато стойностите се променят.
Вместо ръчна работа, можете да използвате условно форматиране, за да маркирате клетки въз основа на стойността. По този начин, в случай че промените стойностите в клетките, цветът/форматът на клетката автоматично ще актуализира топлинната карта въз основа на предварително зададените правила при условно форматиране.
В този урок ще научите как да:
- Бързо създайте топлинна карта в Excel, като използвате условно форматиране.
- Създайте динамична топлинна карта в Excel.
- Създайте топлинна карта в обобщени таблици на Excel.
Да започваме!
Създаване на топлинна карта в Excel с помощта на условно форматиране
Ако имате набор от данни в Excel, можете ръчно да маркирате точки от данни и да създадете топлинна карта.
Това обаче би било статична топлинна карта, тъй като цветът няма да се промени, когато промените стойността в клетка.
Следователно условното форматиране е правилният начин, тъй като прави цвета в клетката да се промени, когато промените стойността в нея.
Да предположим, че имате набор от данни, както е показано по -долу:
Ето стъпките за създаване на топлинна карта, използвайки тези данни:
- Изберете набора от данни. В този пример това ще бъде B2: D13.
- Отидете на Начало -> Условно форматиране -> Цветови скали. Той показва различни цветови комбинации, които могат да се използват за подчертаване на данните. Най -често срещаната цветова скала е първата, където клетките с високи стойности са подчертани в зелено, а ниските в червено. Обърнете внимание, че когато задържите мишката върху тези цветови скали, можете да видите визуализацията на живо в набора от данни.
Това ще ви даде топлинна карта, както е показано по -долу:
По подразбиране Excel присвоява червен цвят на най -ниската стойност и зеления цвят на най -високата стойност, а всички останали стойности получават цвят въз основа на стойността. Така че има градиент с различни нюанси на трите цвята въз основа на стойността.
Какво ще стане, ако не искате градиент и искате да показвате само червено, жълто и зелено. Например, искате да подчертаете всички стойности, по -малки от 700, в червено, независимо от стойността. Така и 500, и 650 получават същия червен цвят, тъй като са по -малко от 700.
Да го направя:
- Отидете на Начало -> Условно форматиране -> Цветови скали -> Още опции.
- В диалоговия прозорец Ново правило за форматиране изберете „3-цветна скала“ от падащото меню Формат на стила.
- Сега можете да посочите минималната, средната точка и максималната стойност и да й присвоите цвета. Тъй като искаме да маркираме всички клетки със стойност под 700 в червено, променете типа на Number и стойност на 700.
- Щракнете върху OK.
Сега ще получите резултата, както е показано по -долу. Обърнете внимание, че всички стойности под 700 получават същия нюанс на червен цвят.
Дума на предпазливост: Докато условното форматиране е чудесен инструмент, за съжаление, той е нестабилен. Това означава, че когато има някаква промяна в работния лист, условното форматиране се преизчислява. Въпреки че въздействието може да бъде незначително върху малките набори от данни, това може да доведе до бавна работна книга на Excel при работа с големи набори от данни.
Създаване на динамична топлинна карта в Excel
Тъй като условното форматиране зависи от стойността в клетка, веднага щом промените стойността, условното форматиране се преизчислява и променя.
Това дава възможност да се направи динамична топлинна карта.
Нека разгледаме два примера за създаване на топлинни карти с помощта на интерактивни контроли в Excel.
Пример 1: Топлинна карта с помощта на лента за превъртане
Ето един пример, при който топлинната карта се променя веднага щом използвате лентата за превъртане, за да промените годината.
Този тип динамични топлинни карти могат да се използват в табла за управление, където имате ограничения на пространството, но все пак искате потребителят да има достъп до целия набор от данни.
Щракнете тук, за да изтеглите шаблона за топлинна карта
Как да създадете тази динамична топлинна карта?
Ето пълния набор от данни, който се използва за създаване на тази динамична топлинна карта.
Ето стъпките:
- В нов лист (или в същия лист) въведете имената на месеците (просто копирайте го поставете от оригиналните данни).
- Отидете на Developer -> Controls -> Insert -> Scroll Bar. Сега щракнете навсякъде в работния лист и той ще вмъкне лента за превъртане. (щракнете тук, ако не можете да намерите раздела за програмисти).
- Щракнете с десния бутон върху лентата за превъртане и щракнете върху Контрол на формата.
- В диалоговия прозорец Контрол на формата направете следните промени:
- Минимална стойност: 1
- Максимална стойност 5
- Клетъчна връзка: Sheet1! $ J $ 1 (Можете да кликнете върху иконата вдясно и след това ръчно да изберете клетката, която искате да свържете към лентата за превъртане).
- Щракнете върху OK.
- В клетка B1 въведете формулата: = INDEX (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1+COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Преоразмерете и поставете лентата за превъртане в долната част на набора от данни.
Сега, когато промените лентата за превъртане, стойността в Sheet1! $ J $ 1 ще се промени и тъй като формулите са свързани с тази клетка, тя ще се актуализира, за да покаже правилните стойности.
Освен това, тъй като условното форматиране е променливо, веднага щом стойността се промени, тя също се актуализира.
Гледайте видео - Динамична топлинна карта в Excel
Пример 2: Създаване на динамична топлинна карта в Excel с помощта на бутони за радио
Ето още един пример, където можете да промените топлинната карта, като направите избор на бутон за избор:
В този пример можете да маркирате горните/долните 10 стойности въз основа на избора на бутон за радио/опция.
Щракнете тук, за да изтеглите шаблона за топлинна карта
Създаване на топлинна карта в обобщена таблица на Excel
Условното форматиране в обобщените таблици работи по същия начин, както при всички нормални данни.
Но има нещо важно, което трябва да знаете.
Нека да дам пример и да ви покажа.
Да предположим, че имате обобщена таблица, както е показано по -долу:
За да създадете топлинна карта в тази обобщена таблица на Excel:
- Изберете клетките (B5: D14).
- Отидете на Начало -> Условно форматиране -> Цветови скали и изберете цветовата скала, която искате да приложите.
Това незабавно би създало топлинната карта в обобщената таблица.
Проблемът с този метод е, че ако добавите нови данни в бекенда и опресните тази обобщена таблица, условното форматиране няма да се приложи към новите данни.
Например, когато добавих нови данни в задната част, коригирах изходните данни и опреснях обобщената таблица, можете да видите, че условното форматиране не се прилага към нея.
Това се случва, когато приложихме условното форматиране само за клетки B5: D14.
Ако искате тази топлинна карта да бъде динамична, така че да се актуализира при добавяне на нови данни, ето стъпки:
- Изберете клетките (B5: D14).
- Отидете на Начало -> Условно форматиране -> Цветови скали и изберете цветовата скала, която искате да приложите.
- Отново отидете на Начало -> Условно форматиране -> Управление на правилата.
- В Мениджъра на правила за условно форматиране щракнете върху бутона Редактиране.
- В диалоговия прозорец Редактиране на правилото за форматиране изберете третата опция: Всички клетки, показващи стойности „Продажби“ за „Дата“ и „Клиент“.
Сега условното форматиране ще се актуализира, когато промените данните на задната част.
Забележка: Условното форматиране изчезва, ако промените полетата за ред/колона. Например, ако премахнете полето Дата и го приложите отново, условното форматиране ще бъде загубено.
