Условно форматиране в Excel: Инструкции и употреба

Използвайте тези инструкции, за да създадете различни формати

Условното форматиране в Microsoft Excel е полезен инструмент за подобряване на таблиците, отчетите или клетките, за подчертаване на област или няколко колони, за анализ на данни, за изясняване на разликите или за идентифициране на тенденциите. За това се използват ленти с данни, набори от символи или цветни скали. В зависимост от посочените условия, дисплеят на клетките се променя или диапазонът от клетки се форматира. Можете да създадете свой собствен за многобройните интегрирани условия.

Условното форматиране има смисъл и ви помага

Условното форматиране в Excel може да се приложи по много начини, независимо дали става въпрос за таблица в Excel, определен диапазон от клетки или отчет за обобщена таблица. Предлагат се различни действия и шаблони за формати. Решаващото предимство за потребителите на Excel е, че специални стойности или резултати могат да бъдат подчертани чрез цветно кодиране. Ако стойността се промени, форматирането се регулира автоматично - в зависимост от условията.

информация: Обобщената таблица („Обобщена таблица“) е специална таблица, с която може да се изчисли, обобщи и анализира голямо количество данни. Освен това можете бързо да правите сравнения и да идентифицирате тенденциите в данните от изследванията.

Условно форматиране със съществуващи правила за числа, текстово съдържание или дати

Ако форматирате с оператор за сравнение, клетките могат да бъдат намерени по -лесно. Можете да направите стойностите по -видими, например по -големи или по -малки от определена стойност или определени стойности, които лежат между горна и долна граница. Това означава, че желаните или търсените данни се показват в цвят. Бързо или разширено форматиране са достъпни за редактиране. С разширеното форматиране можете да работите с формули, например за автоматично маркиране на колона в Excel в цвят.

Примери:

• Служител на склад би искал да подчертае всички запаси под 15 броя с цветен фон.
• Всички градове с повече от 150 000 жители трябва да бъдат отбелязани в таблица.
• Производствена компания иска да подчертае в червено всички стоки, произведени между 1 януари 2022-2023 г. и 31 януари 2022-2023 г.

Бърз формат:

Избор на една или повече клетки.

Кликнете върху „Старт“ в раздела.

Отидете до стрелката до бутона „Условно форматиране“ в „Стил“.

Кликнете върху „Правила за маркиране на клетки“.

Изберете желаната команда, напр. Б. по -голямо от, по -малко от, между, дата и т.н.

Въведете стойности и изберете формат.

Разширено форматиране:

1. Изберете една или повече клетки в отчет в таблица или обобщена таблица.

2. Отидете в раздела „Начало“, в групата Стилове щракнете върху стрелката до „Условно форматиране“.

3. Кликнете върху „Управление на правилата“: Появява се диалоговият прозорец „Мениджър за правила за условно форматиране“.

4. Изберете действие:

   • Кликнете върху „Ново правило“: Появява се диалоговият прозорец „Ново правило за форматиране“.
   • За да промените формат, уверете се, че в списъка „Показване на правилото за форматиране за“ е избран правилния работен лист или таблица.
   • В полето „Отнася се за“ отидете на „Свиване на диалоговия прозорец“; след това изберете новия диапазон от клетки в работния лист.
   • Щракнете върху бутона „Разгъване на диалоговия прозорец“.
   • Изберете правилото и кликнете върху „Редактиране на правило“: Появява се диалоговият прозорец „Редактиране на правило за форматиране“.

5. За да промените обхвата за полета в областта на стойността "Прилагане на правило към" на отчет в обобщена таблица, направете следното:

   • Избор: Кликнете върху „Само тези клетки“
   • Съответстващо поле: Кликнете върху „Всички клетки с еднакви полета“
   • Поле за стойност: Кликнете върху „Всички клетки“

6. Под „Избор на тип правило“ кликнете върху „Форматиране само на клетки, които съдържат“

Сега можете да извършвате действия под „Редактиране на описанието на правилото“ в полето на списъка „Форматиране на клетки само с“:

Форматиране по номер, дата или час:

За да направите това, изберете стойност на клетката и оператор за сравнение и въведете номера, датата или часа. пример: За „Между“ въведете 300 и 500 или изберете „Равно на“ и въведете дата.

Можете да използвате формула, за да получите обратно число, дата или час.

Форматиране по текст:

Изберете „Специфичен текст“ и „Релационен оператор“.

Въведете текст, напр. B. на "съдържа" думата "злато" или на "започва с" префикс, който искате да търсите.

Могат да бъдат въведени максимум 255 знака.

Стойностите могат да бъдат върнати с помощта на формули. За да направите това, формулата трябва да започне с (=). Невалидните формули не са форматирани. Препоръчително е първо да се тества формулата за грешки.

Форматиране по дата:

• Изберете дата и дата за сравнение, напр. Б. Вчера, следващата седмица или този месец

След това: За да зададете формат, кликнете върху „Форматиране“, ще се появи диалоговият прозорец „Форматиране на клетки“. Изберете желания формат на числото, формат за запълване, рамка или шрифт, щракнете върху „OK“.

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

Условно форматиране с бази данни, цветови схеми и набори от символи

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

Форматирайте клетки с ленти с данни

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

Бърз формат:

Маркирайте желаната област (клетка, таблица или цял лист).

В раздела Начало щракнете върху бутона Условно форматиране.

Отидете на „лента с данни“ и изберете попълване.

Форматирайте клетки с цветни рамки

Цветовите скали помагат за по -лесно класифициране на различна информация и за бързо сравняване на съдържанието им помежду си. В зависимост от цветовия нюанс се показват високи или ниски стойности. Можете да форматирате клетки с 2- или 3-цветна скала.

2-цветна скала

Бърз формат:

Изберете клетка, таблица или цял лист.

В раздела Начало щракнете върху Условно форматиране.

Отидете на "цветова скала" и кликнете върху желания формат на 2-цветна скала. Горният цвят означава по -високи стойности, долният цвят - по -ниски стойности.

3-цветна скала

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

Бърз формат:

Изберете клетка, таблица или цял лист.

В раздела Начало щракнете върху Условно форматиране.

Отидете на „цветова скала“ и щракнете върху желания 3-цветен формат. Горният цвят означава по -високи стойности, долният цвят - по -ниски стойности.

Форматирайте клетки с набори от символи

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

Бърз формат:

Изберете клетка, таблица или цял лист.

В раздела Начало щракнете върху Условно форматиране.

Отидете на „Символни набори“ и изберете желания набор от символи

Създайте сами правила за форматиране

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

Маркирайте диапазона от клетки, които да бъдат условно форматирани.

Отидете на „Условно форматиране“ в раздела „Начало“.

Изберете „Ново правило“ и потвърдете с „OK“.

Ако не можете да намерите необходимата опция за вашето собствено правило за форматиране, логична формула може да ви помогне. Например, можете да използвате формула за автоматично оцветяване на клетките на Excel веднага щом съдържанието е налично. Обратно, празни клетки могат да бъдат условно форматирани.

Пример: Прилагането на формула ви позволява да сравнявате данни извън избрания диапазон. Това дори включва данни, които са на различен работен лист в същата работна книга. Или можете да сравните стойностите с резултат, върнат от функция. Формулата трябва да бъде логична и да връща „true“ или „false“.

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

Инструкции стъпка по стъпка за създаване на правило

1. В раздела „Начало“, група „Стилове“ отидете до стрелката до „Условно форматиране“ и кликнете върху „Управление на правилата“. Появява се диалоговият прозорец Мениджър на правила за условно форматиране.

2. Възможни са следните действия:

   • Ако искате да добавите условен формат, отидете на „Ново правило“.
   • Промяна на условен формат:
   • • Проверете дали сте на правилния работен лист.
     • Първо в Прилага се за щракнете върху Свиване на диалоговия прозорец. След това изберете новия диапазон от клетки и отидете на „Разгъване на диалоговия прозорец“.
     • Изберете желаното правило, кликнете върху „Редактиране на правило“. Появява се „Редактиране на правило за форматиране“.

3. Под „Избор на тип правило“ кликнете върху „Използвайте формула, за да определите клетките, които ще бъдат форматирани“.

   • Под „Редактиране на описанието на правилото“ въведете формула в „Форматиране на стойности, за които това правило е вярно“. Формулата започва с (=) и трябва да връща „true“ (1) или „false“ (0).
   • Щракнете върху Форматиране, за да се покаже диалоговият прозорец Форматиране на клетки.
   • Сега изберете номера, шрифта, формата на запълване и т.н., които трябва да се използват, ако стойността на клетката е вярна, и потвърдете с "OK". Избраните формати се показват в "Визуализация".

Например формулата COUNTIF връща логически стойности. Ако се използва тази формула, могат да се определят дублиращи се стойности или съдържание, което се среща с определена честота, може да бъде подчертано в цвят. По този начин могат да бъдат намерени и подчертани в цвят уникални предмети. Други формули, които могат да се използват за условно форматиране, са

• REST: така че редуващите се цветове на линиите могат да бъдат зададени автоматично
• ИДЕНТИЧНО: С тази формула данните, които търсите, могат да бъдат маркирани в таблица на Excel, която съответства на определена стойност
• ISERROR: автоматично маркира празни клетки в таблицата на Excel или прави стойностите на грешките да изчезнат от работната книга
• NOT и ISNUMBER: Форматира всички Excel клетки, които не съдържат числа
• MAX: най -голямата стойност на клетката е маркирана в таблицата
• MIN: позволява условното форматиране на най -малката стойност в списъка на Excel, така че минимумът да може да се види с един поглед
• НАЛЯВО: в зависимост от низ от символи в началото, клетките на Excel се форматират автоматично и им се дава еднакъв цвят на текста
• LARGE и LEFT: разрешаване на оцветяване на клетка с ново съдържание
• НАДЯСНО: Клетките могат да бъдат форматирани въз основа на последния знак на низа
• Днес
• МЕСЕЦ или ГОДИНА: ви позволява да разделите списъка на Excel на месеци или години; С YEAR стойностите на датата на текущата година също могат да бъдат маркирани и годишните стойности могат да бъдат подчертани
• ИЛИ и СЕДМИЦА: подчертава стойностите според деня от седмицата в цвят

Пример за създаване на правило за И и ИЛИ проверки:

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

Първото правило: Купувачът на жилище иска да провери дали планираната покупка на имот е в съответствие с неговия бюджет. Той може да внесе до 80 000 евро от собствените си средства като авансово плащане и да обслужва месечна вноска по заем от максимум 1200 евро. Ако и авансовото плащане, и вноската по заема отговарят на тези изисквания, съответните клетки са маркирани в зелено.

Второ правило: Ако авансовото плащане или вноската не отговарят на изискванията, съответните клетки са маркирани в червено.

Формулата за създаване на първото правило, използвайки зеления цвят, е следната:

= И (АКО ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Формулата за създаване на второ правило с червен цвят е следната:

= ИЛИ (АКО ($ B $ 4 = 1200,1))

Прехвърляне и изтриване на условно форматиране

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

Прехвърляне на условно форматиране

За да копирате условното форматиране и да го поставите в други клетки или диапазони от клетки, използвайте бутона „Прехвърляне на формат“.

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

Сега отидете на "Старт" и след това "Прехвърляне на формат". Курсорът се появява като четка.

Можете да добавите условно форматиране към клетка или диапазон от клетки, като преместите четката над клетката.

С ESC прекратявате използването на четката.

Все още можете да използвате четката, за да вмъкнете условно форматиране, като щракнете двукратно върху „Прехвърляне на формат“.

Като алтернатива условното форматиране може да се копира с помощта на клавишните комбинации и да се постави в друга клетка на Excel.

Ясно условно форматиране

Прави се разлика между процес на изтриване на работен лист и процес на изтриване в диапазон от клетки.

Изтриване на работен лист или в целия работен лист

Отидете на „Условно форматиране“ в раздела „Начало“.

След това кликнете върху „изтриване на правила“

След това кликнете върху „Изтриване на правила в целия лист“.

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

Изтриване в диапазона от клетки

Маркирайте клетки, които съдържат желаното условно форматиране.

Кликнете върху бутона „Лупа за анализ на четенето“ (бутонът се намира най -долу вдясно до избраните данни).

Отидете на „Изтриване на формат“.

Намерете и изтрийте едни и същи условни формати в целия работен лист

Щракнете върху клетката с подходящия условен формат.

В раздела „Начало“ щракнете върху стрелката до „Намиране и избор“, след което кликнете върху „Избор на съдържание“.

Кликнете върху „Условни формати“.

Под „Проверка на данни“ отидете на „Същото“ (това ще избере всички клетки със същото условно форматиране).

В раздела „Старт“ отидете на „Условно форматиране“, след това „Изтриване на правила“ и „Изтриване на правила в избрани клетки“.

Условно форматиране в отчета на обобщената таблица

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

• Някои условни формати не работят в полета в диапазона от стойности на отчет с обобщена таблица.
• Ако промените оформлението на отчета на обобщената таблица по някакъв начин, условното форматиране ще продължи, докато полетата в основните данни не бъдат премахнати.
• Условното форматиране за полета в диапазона от стойности може да се основава на йерархията на данните. Обхватът на това условно форматиране се определя от следващото по -ниско или следващото по -високо ниво на йерархията в редове за една или повече колони или в колони за един или повече редове.
• Обхватът на условното форматиране за полета в обхвата на стойностите може да бъде определен с помощта на три метода:

• • избор
  • Съответстващо поле
  • Поле за стойност

Методът по подразбиране е избор.

Определяне на площ чрез избор

Отидете на „Прилагане на правилото за форматиране към“.

Щракнете или върху диалоговия прозорец Ново правило за форматиране, или върху диалоговия прозорец Редактиране на правило за форматиране.

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

Определяне на площ чрез съответно поле

Този метод работи, ако имате

• искате да избегнете необходимостта да избирате много несвързани елементи
• искат условно да форматират няколко полета в диапазона от стойности за ниво йерархия на данни
• Искате да изключите частични резултати

Определяне на област чрез поле за стойност

Този метод работи, ако имате

• искате да избегнете необходимостта да избирате много несвързани елементи
• искат условно да форматират няколко полета в диапазона от стойности за всички нива на йерархия
• Искате да включите общи и частични резултати.

Заключение: Условното форматиране в Excel улеснява проследяването на данните

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

Още статии за условно форматиране

• Как автоматично да генерирате стрелки в списъци на Excel
• Показване на отрицателни стойности на време в таблици на Excel
• Цветно отрицателни проценти червено
• Разпознайте отрицателните проценти с един поглед
• Форматирайте числа с персонализиран формат в цвят
• Форматирайте Excel клетки, които нямат посочената стойност
• Автоматично съхранявайте клетки със съответните формати на валута
• Маркирайте клетки в цвят с помощта на макро функция
• Използвайте условно форматиране и маркирайте данни, които са по -големи или равни на третата по големина клетка
• Обратни стойности на истината