Помогни ни да направим Uroci.net по - богат! Добави урок

Оцветяване (форматиране) на клетки и редове в Ексел 2003

oldstar   трудност:    видян: 42347

 

Предварително уточнения:
1. Въпреки, че урока е написан за Ексел 2003, написаното тук е валидно и за 2007, независимо от различния интерфейс. Разликата е, че при 2007 има много по-големи възможности за оцветяване и форматиране, което може да бъде предмет на отделен урок.
2. Тук са ползвани няколко формули), но резултата може да се постигне и чрез други формули или макроси.
3. Тук (ако не е показано друго) за улеснение се приема, че първите клетки на колоните са А1 и В1
4. В частта при формулите са използвани абсолютни и относителни адреси на клетки, което предполага познание за значението им. За тези, на които не им е ясно, могат да ползват директно формулите, като се съобразят с адресите в примерите и ги приложат в техния случай, като направят съответните промени.


Малко информация за условното форматиране


Условното конвертиране (Conditional formatting) е инструмент в Ексел, с който се променя форматирането на клетки, редове или колони чрез форматиращи стилове или формули – промяна на шрифта или цвета му или оцветяване на клетките и/или редовете и колоните при зададено условие или определен критерии. 
В Ексел 2003 могат да се поставят най-много 3 условия и критерии при оцветяване или форматиране на клетки. Те не трябва да си противоречат или припокриват. В противен случай Conditional Formatting изпълнява само първото валидно. 

Conditional Formatting има две опции- Cell Value is и Formula is

 

 

 

А. Чрез първата опция (Cell Value is-Стойността на клетката е) се извършва най-простото условно форматиране с готови дефинирани условия. А те са: 
- between –между
- not between – не между
- equal to – равно на
- not equal to – не е равно на
- greater than – по-голямо от
- less than – по-малко от
- greater than or equal to – по-голямо или равно на
- less than or equal to – по-малко или равно на

 

 

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

 

 

Б) При втората опция (Formula is-Формулата е) форматирането се извършва при задаването на условия, които могат да се изразят чрез формули. Тук може да се използват относителни и абсолютни адреси. На практика почти всяка формула (не 100%) може да се ползва при условното конвертиране за оцветяване или форматиране, ако е изписана точно. 
След поставяне на условията, натискаме Format (появява се прозорчето за форматиране) и там правим настройките за цвят на шрифт или клетка..Ако искаме да оцветим клетката, от Pattern избираме цвят или само Pattern.
Ако искаме да поставим ново условие, натискаме Add и правим съответните настройки. 


ОЦВЕТЯВАНЕ/ФОРМАТИРАНЕ ЧРЕЗ CELL VALUE IS

 

1. Форматиране на отделна клетка с данни.
Нека в клетка С1 да е числото 20 - може да бъде самостоятелнa или да е резултат от формула. При всяка смяна на стойността (20), клетката трябва да изглежда различно. 
- Маркираме клетката с числото 20 и Format – Conditional Formatting –Cell Value is
- Избираме Less than, пишем 15 (по-малко от 15) и натискаме Format. Правим настройките на шрифта или цвета на клетката.
- Натискаме Add за новото условие. Пак избиране Cell Value is – Greater than (по-голямо от), пишем 20 (по-голямо от 20) и натискаме Format. Правим същото, както по-горе за форматирането.
Сега за всички стойности над 20 и под 15 клетката променя цвета си или шрифта, в зависимост от конвертирането

 

 

Това може да се прилага напр. при оценяване на резултати от медицински изследвания (горна/долна граница) или при други гранични стойности.

 

 

2. Нека в клетка С1 има число, което е резултат от формула на стойности в клетките А1 и В1, напр. формулата за С1 е =А1*В1. При всяка промяна на тази формула, (дори резултата в С1 да е верен), клетката/шрифта се оцветява.
- Маркираме клетката с резултата - С1. Format – Conditional Formatting –Cell Value is
- Избираме Not Equal to (Не е равно на) и изписваме формулата. Натискаме Format. Правим настройките на шрифта или цвета на клетката. 

 

 

 

 

3. Търсене в база данни.

Чрез Conditional Formatting може да се търси определени стойности, по-големи или по-малки от посочена (за колона с числови стойности) или намиране на текстов низ (по-просто - определен текст)
В примера по-долу се вижда резултата след форматирането (две колони с цифри и две имена)

 

 

 

В първия случай маркираме колона А (без антетката) - Format – Conditional Formatting –Cell Value is
- избираме Greater than 39 и настройваме форматирането.
- натискаме Add за ново условие. Този път избираме Less than 39 и друго форматиране. При потвърждение клетките със стойности над 39 се оцветяват с един цвят, а тези под-с друг. Клетката с 39 остава неоцветена. Ако искаме да включим и 39 в избора, при двете условия избираме съответно Greater than or equal to и Less then or equal to.

Във втория случай маркираме колона D (без антетката) - Format – Conditional Formatting –Cell Value is. Избираме Equal to (равно на) и пишем Иван. При потвърждаване клетката с Иван се оцветява –дори в списъка има два пъти Иван. Ако вместо Петър, напишем в клетката Иван, тя автоматично се оцветява. (Същото важи и ако променим някои от стойностите в колона А) Заб. Ако имаме две текстови колони (напр. имена и местоживеене), за да открием Иван от София, маркираме двете колони и посочване две отделни условия.- едно за Иван. Add- второ за София. Може да се оцветят в един цвят и при покритието на двете колони намираме точно посоченото.

 

 

ОЦВЕТЯВАНЕ/ФОРМАТИРАНЕ ЧРЕЗ FORMULA IS

 

1. Форматиране на отделна празна клетка. 
Нека да е С1. Там като резултат от формула трябва да се впише число. В зависимост от стойността му (напр, положителна, 0, отрицателна) клетката трябва да променя цвета си.  
- Посочваме клетката. Format-Conditional Formatting-Formula is 
- Написваме формула за положително число(>0), като посочваме абсолютния адрес на на клетката ($С$1) или формулата става =$С$1>0. Чрез Format избираме цвят
- Натискаме Add за новото условие. Пак избиране Formula is. Написваме формулата =$С$1=0 и избираме друг цвят. Добавяме ново условие (Add) и там пишем формулата за отрицателно число =$С$1<0 и избираме 
Сега клетката остава бяла, но при всяко вписано число променя цвета си. Стойностите за условията могат да са различни от примера, в зависимост от търсения резултат.

2. Сравняване на списъци или колони с числа(данни) за проверка на липсващи данни в един от тях.. Нека двата списъка са в колони А и В (начало А1 и В1). Сравняваме А с В. Има доста формули за сравняване. Ще ползвам една от тях.
- Маркираме списъка в колона В. Format-Conditional Formatting-Formula is. Изписваме формулата =countif(A:A;B1)=0
- Натискаме Format и правим съответните настройки за цвят. При потвърждение липсващите данни в колона А са оцветени в колона В. Ако искаме, можем да направил и обратна проверка, като променим стойностите във формулата.

3. Повторения в две колони
а) в две колони – сравнение дали има данни намиращи се в двата списъка (повтарят се)
Това е действие обратно на горното. Използваме същата формула, но малко изменена. Сега тя става =countif(A:A;B1). Всички стойности, които с повтарят в единия списък, се оцветяват в другия.

Бонус: Ако двете колони са дълги и въпреки оцветяването на дублиращите файлове не можем да се оправим, ето едно улеснение:
Маркираме съседната първа клетка на колоната B, т.е.. C1 и в лентата за формули въвеждаме =match(B1;A$1:A$11;0), т.е. първата клетка на едната колона (В), целия списък в колона А, 0. Трябва да се внимава с адресите на клетките (как са написани –относителни и абсолютни). След това копираме тази формула надолу по цялата колона (или придърпваме кръстчето на клетката надолу). Във втория пример (с имената) се процедира по същия начин Получаваме интересен резултат: (Заб. Тази формула може да се ползва и без Conditional Formatting)

 

 

 

Символът #N/A (not available- не е налично) показва, че стойността няма съответствие в другата колона. Цифрите показват на кое място (ред) е съответствието в другата колона. Формулата не открива повторения в самата колона, а сравнява двете.

 

b). Повторения в една колона
Маркираме колоната. Format-Conditional Formatting-Formula is
=countif($A$1:$A$6;$A1)>1. Всички повтарящи се данни повече от 1 път се оцветяват. Ако искаме да елиминираме повтарящи се и да оставим само „оригинала”, т.е. първото число, чиито повторения се повяват нататък, използваме формулата =COUNTIF($A$1:$A2;A1)>1. 
Каква е разликата? 

 

 

Във първия случай са маркирани всички повтарящи се (само 2 не се повтаря), а във втория са маркирани всички, които повтарят въведена стойност-един или повече пъти.

 

ОЦВЕТЯВАНЕ/ФОРМАТИРАНЕ НА РЕДОВЕ

 


С Conditional Formatting може да оцветим цял ред, сравнявайки стойности. Ето пример:
а) трябва да установим в кои месеци прихода е бил по-голям от 50 х.
б) Трябва да маркираме месеците, в които прихода надвишава разхода


 

Маркираме цялата таблица (без антетката). Format-Conditional Formatting-Formula is
а) За първия случай изписваме формулата
= $B2>50. (абсолютен адрес на колоната и относителен адрес на реда). Колона В-приход. Избираме цвят и потвърждаваме.Редовете (месеците), в които прихода е над 50 хил. се оцветяват
б) За втория случай изписваме формулата 
  =$B2>$C2 (абсолютни адреси на колоните и относителни адреси на реда). Колона В-приход, колона С-разход. Избираме цвят и потвърждаваме. Месеците, в които прихода надвишава разхода се оцветяват.

 

 

 

Внимавайте с адресите на клетките (абсолютни и относителни)!

 

Това са по-основни приложения на Conditional Formatting (уловно форматиране) в Ексел 2003. Надявам се урока да ви е полезен.


автор:oldstar




Коментари (2)

axilia на 22.03 2013 в 13:28ч.
Интересувам се има ли формула която да сумира оцветени клетки?
oldstar на 12.04 2013 в 18:30ч.
Има. При 2003 и 2007/10 е различно. Очаквай публикация

Регистрирайте се, за да добавите коментар


Калдейта ЕООД - © 2003-2010. Всички права запазени.
Препоръчваме: Национален Бизнес | Bomba.bg | IT Новини | Диплома.бг | TRAVEL туризъм | Реферати | AmAm.bg | Иде.ли | Курсови работи | Фото Форум | Spodeli.net | Фото-Култ | Atol.bg | Elmaz.com | MobileBulgaria.com | Казанлък.Com