Този урок ще покаже как да създавате диаграми на Excel с условно форматиране във всички версии на Excel: 2007, 2010, 2013, 2016 и 2022.
Условното форматиране е практиката на присвояване на персонализирано форматиране на Excel клетки-цвят, шрифт и т.н. въз основа на посочените критерии (условия). Функцията помага при анализиране на данни, намиране на статистически значими стойности и идентифициране на модели в даден набор от данни.
Но за съжаление Excel не предлага вградени инструменти за прилагане на условно форматиране към диаграми на Excel.
Въпреки това, с капка магия за електронни таблици, никой проблем не е непреодолим. В този урок ще научите как да създадете динамична диаграма с условно форматиране, където начертаните точки от данни се форматират автоматично въз основа на съответните им действителни стойности.
Като пример, погледнете тази колонна диаграма, разбиваща работата на мениджър продажби за период от осем месеца. Забележете как цветът на колоната автоматично се регулира в зависимост от нейната основна стойност:
Приготвяме се да започнем
Помислете за този примерен набор от данни, съдържащ данни за продажбите:
Преди да започнем, преместете вашия набор от данни така, че да имате три празни реда над таблицата с вашите необработени данни. Редовете ще се използват за задаване на правила за условно форматиране.
Стъпка 1: Подгответе данните от диаграмата.
За да накарат нещата да работят, трябва да добавите някои допълнителни данни към диаграмата към уравнението. За ваше удобство надникнете как трябва да изглеждат данните ви в диаграмата до края на подготвителната фаза:
Първата ни стъпка е дефинирането на граничните стойности (условия), които трябва да разделят действителните стойности на множество категории, което прави възможно да ги начертаем като отделни серии от данни.
От горната снимка на екрана например можете да видите, че данните за продажбите за даден месец трябва да попаднат в диапазона от 90 000 до 150 000 долара, за да се считат за задоволителни.
За да създадете правилата, използвайте трите празни реда над набора от данни:
- Ред 1 (етикет): Използвайте тези клетки за етикетиране на правилата за форматиране.
- Ред 2 (минимална стойност): Стойностите в този ред определят долната граница за всяко условие.
- Ред 3 (макс. Стойност): По същия начин тези стойности определят горните граници.
С този метод можете да създадете колкото искате правила за условно форматиране-просто се уверете, че диапазоните на стойностите не се припокриват.
Докато продължавате да настройвате диаграмата си, проектирайте персонализирани елементи от легендата на диаграмата, които ще предоставят контекст за диаграмата. Въведете тази формула в C4 и го копирайте в D4 като плъзнете дръжката за запълване:
| 1 | = C1 & ": от" & TEXT (C2, "$#, ##") & "към" & TEXT (C3, "$#, ##") |
Формулата обединява добре изглеждащ динамичен етикет въз основа на предварително зададените правила за условно форматиране. Функцията TEXT форматира стойностите като валута. Но ако вашият тип данни се различава, използвайте тази формула вместо това:
| 1 | = C1 & ": от" & TEXT (C2, "#, ##") & "към" & TEXT (C3, "#, ##") |
Или този, когато работите с проценти:
| 1 | = C1 & ": от" & TEXT (C2, "#%") & "до" & TEXT (C3, "#%") |
Накрая намерете стойностите на диаграмата. Въведете следната формула C5, копирайте го до C12 и до съответстващите клетки в колона D:
| 1 | = IF (AND (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Формулата сравнява дадена действителна стойност в колона Б срещу посочените гранични стойности с помощта на функциите IF и AND. Ако стойността попада в диапазона, тя се копира в тази колона. В противен случай формулата връща грешката #N/A, за да предотврати нанасянето на една и съща стойност няколко пъти.
Стъпка 2: Настройте колонна диаграма.
След като сте събрали всички данни от диаграмата, настройте обикновена колонна диаграма или лентова диаграма като алтернатива:
- Маркирайте всички данни в диаграмата, с изключение на колоните, съдържащи действителните стойности и правилата, като задържите Ctrl ключ (A4: A12 и C4: D12).
- Отидете на Вмъкване раздел.
- Изберете „Вмъкване на колона или стълбовидна диаграма.”
- Избирам "Клъстерна колона/Клъстерна лента.”
В резултат на това ще получите диаграма с точки от данни, нанесени въз основа на правилата за условно форматиране-точно както е обещано:
Стъпка #3: Променете стойностите на припокриване и ширина на пролуката.
Преди да затворите този урок, отстранете проблема с неправилно поставените колони, причинени от невидимите празни стойности (грешките #N/A), нанесени на диаграмата.
Щракнете с десния бутон върху някоя от колоните и изберете „Форматиране на серия от данни”От контекстното меню, което се появява.
В прозореца на задачите променете позицията и ширината на колоните:
- Превключете към Опции за серия раздел.
- Промяна “Припокриване на сериите" да се "100%.”
- Нагласи Ширина на пролуката да се "60%.”
Стъпка #4: Регулирайте цветовата схема.
Накрая добавете последните щрихи. Без да затваряте Форматиране на серия от данни панел на задачите, променете цветовата схема на диаграмата:
- Отидете на Fill & Line раздел.
- Под „Попълнете," избирам "Твърдо пълнене.”
- Щракнете върху „Цвят на запълване”Икона и изберете зелено от цветовата палитра.
След като преоцветите останалите серии от данни, променете заглавието на диаграмата и вашата напълно динамична диаграма с условно форматиране е готова за работа!
