01 от 01
Excel Shading Редове / Колони Формула
По-голямата част от времето условното форматиране се използва за промяна на цветовете на клетки или шрифтове в отговор на данните, въведени в клетка, като например просрочена дата или бюджетен разход, който е твърде висок и обикновено това се прави с предварително зададени условия на Excel.
В допълнение към предварително зададените опции обаче е възможно също така да се създадат персонализирани правила за условно форматиране , като се използват формули на Excel, за да се тестват условията, определени от потребителя.
Една такава формула, която съчетава функциите MOD и ROW , може да бъде използвана за автоматично засенчване на алтернативни редове от данни, които могат да направят четенето на данни в големи работни листове много по-лесно.
Динамично засенчване
Друго предимство при използването на формулата за добавяне на реда за засенчване е, че засенчването е динамично, което означава, че се променя, ако броят на редове се промени.
Ако редовете са вмъкнати или изтрити, сензорът на реда се коригира, за да се запази шаблонът.
Забележка: Алтернативните редове не са единствената опция с тази формула. Като го промените леко, както е обсъдено по-долу, формулата може да засенчи всеки шаблон от редове. Тя дори може да се използва за засенчване на колони вместо редове, ако сте избрали така.
Пример: Формула за сянка на редове
Първата стъпка е да се подчертае обхватът на клетките, които трябва да бъдат засенчени, тъй като формулата засяга само тези избрани клетки.
- Отворете работен лист в Excel - за този урок ще работи празен работен лист
- Маркирайте набор от клетки в работния лист
- Кликнете върху раздела Начало на лентата
- Кликнете върху иконата Conditional Formatting, за да отворите падащото меню
- Изберете опция Ново правило, за да отворите диалоговия прозорец Ново форматиране
- Кликнете върху Използване на формула, за да определите кои клетки да форматирате опцията от списъка в горната част на диалоговия прозорец
- Въведете следната формула в полето под стойностите на формата, където тази стойност е вярна опция в долната половина на диалоговия прозорец = MOD (ROW (), 2) = 0
- Кликнете върху бутона Форматиране, за да отворите диалоговия прозорец Форматиране на клетки
- Кликнете върху раздела Попълване, за да видите опциите за цвета на фона
- Изберете цвят, който да използвате за засенчване на алтернативните редове от избрания диапазон
- Натиснете OK два пъти, за да затворите диалоговия прозорец и да се върнете към работния лист
- Алтернативните редове в избрания диапазон вече трябва да бъдат засенчени с избрания цвят на запълване на фона
Интерпретиране на формулата
Как тази формула се чете от Excel е:
- числото 2 във формулата определя, че моделът на засенчване повтаря всеки втори ред в избрания диапазон
- състоянието на = 0 във формулата определя, че първият ред в диапазона не е затъмнен - което се прави, защото този ред често съдържа заглавия, които имат собствено форматиране.
Какво прави MOD и ROW
Моделът зависи от функцията MOD във формулата. Какво прави MOD, разделя номера на ред (определен от функцията ROW) с второто число в скобите и връща остатъка или модула, както понякога се нарича.
В този момент условното форматиране поема и сравнява модула с номера след равен знак. Ако има съвпадение (или по-правилно, ако условието е TRUE) редът е затъмнен, ако числата от двете страни на знака за равенство не съвпадат, условието е FALSE и за този ред не се получава засенчване.
Например, в горното изображение, когато последният ред в избрания диапазон 18 е разделен на 2 от функцията MOD, остатъкът е 0, така че условието 0 = 0 е TRUE и редът е затъмнен.
Ред 17, от друга страна, когато е разделен на 2, оставя остатък от 1, който не е равен на 0, така че редът да остане неразкрит.
Засенчване на колони вместо реда
Както бе споменато, формулите, използвани за засенчване на алтернативни редове, могат да бъдат модифицирани, за да позволят и засенчване на колони. Промяната, която се изисква, е да използвате функцията COLUMN вместо функцията ROW във формулата. При това формулата ще изглежда така:
= MOD (COLUMN (), 2) = 0Забележка: Промените в формулата за рентгенови редове за промяна на шаблона за очертаване, изложени по-долу, се отнасят и за формулата за сянка на колони.
Променете формулата, променете шаблона за засенчване
Промяната на шаблона за засенчване се извършва лесно чрез промяна на двете числа във формулата.
- За да има засенчване на реда започва с първия ред вместо втория, в края на формулата, промяна = 0 до = 1 ;
- За да имате всеки трети или четвърти ред сенчести вместо алтернативни редове, променете 2 във формулата на 3 или 4.
Делителят не може да бъде нула или един
Числото в скобите се нарича делител, тъй като това е числото, което прави разделянето във функцията MOD. Ако си спомните, че сте в математически клас, разделяйки се на нула, не е разрешено и не е разрешено и в Excel. Ако се опитате да използвате нула в скобите вместо 2, като например:
= MOD (ROW (), 0) = 2няма да получите никаква сянка в диапазона.
Като алтернатива, ако се опитате да използвате номер едно за делителя, така че формулата да изглежда така:
= MOD (ред (), 1) = 0всеки ред в диапазона ще бъде затъмнен. Това се случва, защото всяко число, разделено на едно, оставя остатъка от нула и не забравяйте, че когато условието 0 = 0 е TRUE, редът се затъмнява.
Променете оператора, променете шаблона за засенчване
За да промените шаблона, променете условния или сравняващия оператор (равен знак), използван във формулата, до знака по-малък от знака (<).
Като променяте = 0 до <2 (по-малко от 2), например, два реда заедно могат да бъдат затъмнени. Направете това <3, а засенчването ще се извършва в групи от три реда.
Единственото предупреждение за използването на оператора, по-малко от оператора, е да се уверите, че номерът в скобите е по-голям от номера в края на формулата. Ако не, всеки ред в диапазона ще бъде затъмнен.