Подразбиращи се формати за форматиране в Excel

Добавянето на условно форматиране в Excel ви позволява да приложите различни опции за форматиране към клетка или диапазон от клетки, които отговарят на определени условия, които сте задали.

Опциите за форматиране се прилагат само когато избраните клетки отговарят на тези зададени условия.

Опциите за форматиране, които могат да бъдат приложени, включват промени в шрифта и цвета на фона, стиловете на шрифта, границите на клетките и добавянето на форматиране на номера към данните.

От Excel 2007 Excel има няколко вградени опции за често използвани условия, като намиране на числа, които са по-големи или по-малки от определена стойност или намиране на числа, които са над или под средната стойност .

В допълнение към тези предварително зададени опции е възможно също да се създадат персонализирани правила за условно форматиране , като се използват формули на Excel, за да се тестват условията, определени от потребителя.

Прилагане на няколко правила

Повече от едно правило може да се приложи към същите данни, за да се тестват различни условия. Например, бюджетните данни могат да имат определени условия, които да прилагат промени във форматирането, когато се изразходват определени нива - например 50%, 75% и 100% - от общия бюджет.

При такива обстоятелства Excel първо определя дали различните правила са в конфликт и ако е така, програмата следва определен ред от предимства, за да определи кое условно форматиращо правило се прилага към данните.

Пример: Намиране на данни, които надвишават 25% и 50% увеличение с условно форматиране

В следващия пример ще бъдат приложени две правила за условно форматиране за обем на клетките от B2 до B5.

Както може да се види на изображението по-горе, ако някое от горните условия е вярно, цветът на фона на клетката или клетките в диапазона B1: B4 ще се промени.

Правилата, използвани за постигането на тази задача,

= (А2-В2) / А2> 25% = (А2-В2) / А2> 50%

ще бъде въведен чрез диалоговия прозорец за условно форматиране на нов формат.

Въвеждане на данните за урока

  1. Въведете данните в клетки A1 до C5, както е показано на изображението по-горе

Забележка: Стъпка 3 от урока ще добави формули към клетки C2: C4, които показват точната процентна разлика между стойностите в клетките A2: A5 и B2: B5, за да се провери точността на условните правила за форматиране.

Настройване на правилата за форматиране на кондензацията

Използване на формули за условно форматиране в Excel. © Тед Френски

Както бе споменато, условните правила за форматиране, които проверяват за двете условия, ще бъдат въведени, като се използва диалоговият прозорец Ново форматиране на условно форматиране.

Настройване на условното форматиране, за да се установи над 25% увеличение

  1. Маркирайте клетки В2 до В5 в работния лист.
  2. Кликнете върху раздела Начало на лентата.
  3. Кликнете върху иконата за условно форматиране в лентата, за да отворите падащото меню.
  4. Изберете ново правило, за да отворите диалоговия прозорец Ново форматиране, както е показано на изображението по-горе.
  5. В горната половина на диалоговия прозорец кликнете върху последната опция: Използвайте формула, за да определите кои клетки да се форматират.
  6. В долната половина на диалоговия прозорец кликнете върху стойностите на формат, където тази формула е вярна: ред.
  7. Въведете формулата : = (A2-B2) / A2> 25% в предоставеното място
  8. Кликнете върху бутона Форматиране, за да отворите диалоговия прозорец Форматиране на клетки.
  9. В този диалогов прозорец кликнете върху раздела Попълване и изберете син цвят за попълване.
  10. Натиснете OK два пъти, за да затворите диалоговите прозорци и да се върнете към работния лист.
  11. На този етап цветът на фона на клетките B3 и B5 трябва да бъде син.

Настройване на условното форматиране, за да се установи над 50% увеличение

  1. Докато клетките B2 до B5 все още са избрани, повторете стъпки от 1 до 6 по-горе.
  2. Въведете формулата: = (A2-B2) / A2> 50% в предоставеното място.
  3. Кликнете върху бутона Форматиране, за да отворите диалоговия прозорец Форматиране на клетки.
  4. Кликнете върху раздела Попълване и изберете червен цвят за попълване.
  5. Натиснете OK два пъти, за да затворите диалоговите прозорци и да се върнете към работния лист .
  6. Цветът на фона на клетката B3 трябва да е синьо, което показва, че процентната разлика между числата в клетките А3 и В3 е по-голяма от 25%, но по-малка или равна на 50%.
  7. Цветът на фона на клетката B5 трябва да се промени в червено, което означава, че процентната разлика между числата в клетките А5 и В5 е по-голяма от 50%.

Проверка на условните правила за форматиране

Проверка на условните правила за форматиране. © Тед Френски

Изчисляване на% разликата

За да се провери дали въведените правила за условно форматиране са правилни, можем да въведем формули в клетки C2: C5, които ще изчислят точния процент на разликата между числата в диапазоните A2: A5 и B2: B5.

  1. Кликнете върху клетката С2, за да станете активната клетка.
  2. Въведете формулата = (A2-B2) / A2 и натиснете клавиша Enter на клавиатурата.
  3. Отговорът от 10% трябва да се появи в клетка C2, което показва, че числото в клетка А2 е с 10% по-голямо от числото в клетка B2.
  4. Възможно е да се наложи да промените форматирането на клетка C2, за да покажете отговора като процент.
  5. Използвайте дръжката за запълване, за да копирате формулата от клетка C2 до клетки C3 до C5.
  6. Отговорите за клетки С3 до С5 трябва да бъдат: 30%, 25% и 60%.
  7. Отговорите в тези клетки показват, че създадените условни правила за форматиране са верни, тъй като разликата между клетките А3 и В3 е по-голяма от 25%, а разликата между клетките А5 и В5 е по-голяма от 50%.
  8. Клетката B4 не променя цвета, защото разликата между клетките А4 и В4 е равна на 25%, а правилото за условно форматиране уточнява, че е необходим процент, по-голям от 25%, за да се промени цветът на фона в синьо.

Ред на предимство при условни правила за форматиране

Условни правила за форматиране на Excel. © Тед Френски

Прилагане на правилата за конфигуриране на условните формати

Когато се прилагат множество правила за същия диапазон от данни, Excel първо определя дали правилата са в конфликт.

Конфликтните правила са онези, при които избраните опции за форматиране за всяко правило не могат да бъдат приложени едновременно към едни и същи данни .

В примера, използван в този настойнически, конфликтът на правилата, тъй като и двата правила използват една и съща опция за форматиране - тази за промяна на цвета на фона на клетката.

В ситуация, при която второто правило е вярно (разликата в стойността е по-голяма от 50% между две клетки), тогава първото правило (разликата в стойността е по-голяма от 25%) също е вярно.

Ред на предимство на Excel

Тъй като една клетка не може едновременно да има червен и син фон, Excel трябва да знае кое условно форматиращо правило трябва да се прилага.

Правилото, което се прилага, се определя от класацията на Excel, която гласи, че правилото, което е по-високо в списъка в диалоговия прозорец "Условни правила за форматиране", има предимство.

Както е показано на изображението по-горе, второто правило, използвано в този урок (= (A2-B2) / A2> 50%) е по-високо в списъка и следователно има предимство пред първото правило.

В резултат на това цветът на фона на клетката B5 се променя на червено.

По подразбиране новите правила се добавят в горната част на списъка и следователно имат по-висок приоритет.

За да промените реда на приоритет, използвайте бутоните със стрелки нагоре и надолу в диалоговия прозорец, както е посочено в изображението по-горе.

Прилагане на неконфликтни правила

Ако две или повече условни правила за форматиране не са в противоречие, и двете се прилагат, когато условието, което всяко правило тества, става истинно.

Ако първото правило за условно форматиране в нашия пример (= (A2-B2) / A2> 25%) форматира диапазона от клетки В2: В5 със синя граница вместо със син фон, двете условни правила за форматиране няма да са в конфликт след и двата формата могат да бъдат приложени, без да се намесва с другия.

В резултат клетката В5 ще има синя граница и червен фон, тъй като разликата между числата в клетките А5 и В5 е по-голяма от 25 и 50 процента.

Условно форматиране срещу регулярно форматиране

В случай на конфликт между правилата за условно форматиране и ръчно приложените опции за форматиране правилото за условно форматиране винаги има предимство и ще бъде приложено вместо ръчно добавени опции за форматиране.

Ако първоначално цветът на жълтия фон беше приложен към клетките В2 до В5 в примера, след като бяха добавени условните правила за форматиране, само клетките В2 и В4 ще останат жълти.

Тъй като въведените условни правила за форматиране се отнасят за клетки B3 и B5, техните цветове на фона се променят съответно от жълто на синьо и червено.