Форматиране, базирано на условия

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

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

Въпроси и отговори

  • Excel conditional formatting е страшно интересно

    Иванка Калева Чолакова:

    Благодаря, страшно интересно беше с това Conditional formatting!!!

    Иван Цукев:

    Радвам се, че урока ти е интересен :)

    Jasmina:

    Много полезен!


    Юлияна Борисова:

    Conditional formatting  в Excel 2013 е страхотен. Аз го използвам от старите версии на Excel и сега съм много силно впечатлена. Много полезен урок. Благодаря !

    Petia Nikolova:

    Наистина полезно и достатъчно ясно обяснение на възможностите. 

    Лидия Димитрова:

    О.ооо..аз съм се мъчила до днес! Много полезен урок.Благодаря!

    Невена Николова:

    Изключително полезен урок! Благодаря ви!

    Mila Georgieva-Petrova:

    Много интересен и полезен урок! Благодаря!

    Марина Станилова:

    И на мен ми е много полезен този урок. Всичките тези неща досега ги правех ръчно... а сега ще пестя много време и ще съм сигурна, че няма да има грешки от недоглеждане. Особено много ми допадна графичното изображение на стойностите с Data Bars.

  • Теодор Байчев:

    Това беше изключително и поучително видео! Благодаря Ви!

    Кирилка Станкова:

    Подкрепям това мнение.

  • Защо не се получава числото и не се закръгля правилно числото?

    Иван Гумнеров:

    Здравей,

    Работата ми изисква много често ползване на Ексел. Непрекъснато се сблъсквам със следния проблем: сборуването на числа, които са резултат на формула, съдържаща процент. Като лесен пример ще дам сбора на D2+D3 в m3v9-2xlsx.: 345+1 194 = 1 539, но Ексел дава 1 540 и толкова би изписал при сумиране на тези две клетки. Намерил съм решение с изключително сложна формула за изчисляване на процент. Моля, за Вашето решение!

    Извън въпроса: Комисиона е съществително и се пише с едно Н. Правилото за двойно Н се отнася за прилагателни.


    Краси Кръстев:

    Здравейте, в отговор на въпроса Ви използвайте функцията ROUNDDOWN, която е описана по - подробно в следващите уроци, иначе набързо работи по следния начин: на примера на картинката съм събрал позиция 1 и позиция 2 като в тях с функцията rounddown съм задал закръгляне на надолу на 0 единици след десетичната запетая след което пак с нея позиция 3 съм сумирал двете числа ( позиция 3 на картинката ) и съответно в позиция 4 може да видите и получения резултат.Надявам се да съм бил полезен.

    Иван Гумнеров:

    Много благодаря!

    Росица Божкова:

    =round(B2*C2;0) закръглява до цяло число D2=345 D3=1194 D3+D4=1539

    Виктория Данаилова:

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

    Краси Кръстев:

    @Виктория Данаилова - Здравейте, силно се надявам, че под " Форматирайте колоната за суми да ви дава резултатата до два знака след запетайката " - не означава това което съм показал на снимката, защото е погрешно разбиране.Едно да закръгляш числата до определен знак с Round едно е да задаваш в клетката колко знака след запетайката да се Показват.

    Vesela Hristova:

    Много полезен урок! Наскоро използвах условно форматиране за колони с 30000 клетки  и ми бе изключително удобно, но тук научих и много други неща, както и клавишната команда за селектиране на колона от таблицата, която, ако я знаех преди, щеше да ми спести доста време!

    Недялка:

    Благодаря, ето че научих още нещо!

    Альоша Исаев:

    Здравейте, ако правилно съм разбрал в примера на Краси се закръглят събираемите по зададен критерии, а в примера на Виктория Excel закръгля сумата.  

    Краси Кръстев:

    Ами не точно Виктория е дала пример за форматиране по - скоро което форматиране до втория знак не означава че числото реално е закръглено на стотна, ами просто се показва до стотна.Реално ексел може да работи с точност 31 знака след запетаята форматирайки ние не закръгляме....закръгля се с функцията Round ( може да се зададен нагоре или надоли да закръгля up или down ).Стана ли по - ясно Альоша или ако искаш малко снимки за да ти се изясни идеята.

    Альоша Исаев:

    Здравей Краси. При включена функция Roundup числото 15,26 се закръгля на 15,  а числото 17,52 на колко ще се закръгли на 18 или на 17. Същият въпрос е и за техния сбор 32,78, на колко се закръгля на 33 или на 32.

    Краси Кръстев:

    Здравей, Альоша.Най - най - краткото и семпло обяснение : Round закръгля на принципа на математическите  правила 17,52 = 17,5; 17,56 = 17,6 ;

     Ако е Roundup винаги ще е нагоре 17.52 = 17.6 ; 17,56 = 17,6 .
    Примера е в условие че имаш точност на закръгляне десетици ( един знак след запетаята ).За да стане малко по - ясно съм направил един файл в които са направени примера който ти дадох по - горе и още някои неща качвам го 
    тук : https://drive.google.com/file/d/0B7q74PlX2UYeWERMbE83UUxoNFE/view?usp=sharing . Изтегли и го прегледай.
    Надявам се да ти е полезен и да хванеш логиката, ако има нещо пак питай докато не стане ясно и изясним проблема.Тествай и ти това е един добър подход за да разбереш едно нещо :).
    Альоша Исаев:

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

    Краси Кръстев:
    Здравей, Альоша.
    Първо разгледай реалния пример - изтегли си файла без никакви корекции.
    Така както е зададен въпроса от Гумнеров изглежда наистина все едно ексел си прави какво иска, не е така всичко е логично и базиращо се на правила.
    Отвори си посочения от Гумнеров файл и гледайки първия отговор и този който ти ще опиша отдолу ще разбереш какъв е проблема.

    Така; 10 % от 3 452 лв е 345,20лв - мисля че тук ако не е закръглено няма как да е цяло число - това че се показва само 345 не означава че числото е С ТАЗИ  ТОЧНОСТ толкова може само да е форматирано така ТОВА НЕ ЗНАЧИ ЧЕ Е И ФОРМАТИРАНО ( затова в отговора ми по - горе посочих и разликата в закръгляне и само форматиране ) също така на 5972  -  20 % е 1 194,40 не е цяло число ако не го закръглим следователно за сбор  345,20 + 1194,40 = 1539,6 което като резултат вече ако тогава го закръгля ( на стъпка резултат между не закръглени стойности ) с Round до първия знак - Round ( число ; 0 ) - 1539,6 ще се получи 1540 лв ( логиката която описах в предходния отговор ) , ако обаче преди това 345,20 е закръглено на 345 с ROUND ( число;0 ), съответно същото и за  1194,40 ако е закръглено с Round ( число; 0 ) ще е на 1194 и какво се случва накрая 1194+345 = 1539 - това число ще се получи дори и да не закръгляме сумата с Round защото вече Excel 345,20 ( същото и за 1194,40 ) го разбира за 345.

    Надявам се сега конкретно говорейки с числа от примера да е станало по - ясно.Основния проблем идва от това че си мислил че форматирайки число означава че сме закръглили числото НЕ числото си се помни такова каквото си е :).
    Альоша Исаев:

    Здравей Краси. Всичко което го обясни до момента мие ясно и то се базира на математическите правила, което важи и за  ROUNDUP. Интересно ми е в какви случай се използва функцията ROUNDDOWN, която според мен нарушава тези правила, или аз греша.

    Краси Кръстев:

    ROUNDDOWN е на същата логика като ROUNDUP , само че в обратна посока.UP не е на база математически правила, както ти си написал  по - горе.UP  вижда до кой знак искаш да ти се закръгли числото и го закръгля по - нагоре, DOWN прави същото само че го закръгля надолу.Относно употребата на UP и DOWN ще се радвам наистина ако споделят за какво ги използват ... лично аз 99 % от случаите ползвам ROUND.

    Альоша Исаев:

    Здравей Краси, ще се прилагам твоя опит.

    Valqboneva:

    Краси, последното изречение, написано от професионалист, ме успокои. Аз също използвам ROUND в 99% от случаите.

    Kocho:

    "Относно употребата на UP и DOWN ще се радвам наистина ако споделят за какво ги използват ... "

    Пример за ROUNDDOWN:

    Машина трябва да премине преглед и профилактика след изработка на не повече от 50,000 детайла. Иначе рискуваме да се развали, от което фирмата ще претърпи големи загуби. Машината изработва средно 3,000 детайла на седмица. След колко седмици трябва да се извърши профилактиката, без да рискуваме? 50,000 / 3,000 = 16,67 седмици И за да не рискуваме излишно - закръгляваме надолу и се обаждаме на сервиза в 16-та седмица.

    Пример за използване на ROUNDUP:

    Таксуването за говорене по мобилен телефон е на започнати 60 секунди. Ако сме започнали разговор в 10:05:25 и сме приключили в 10:07:02 с ROUND ще сметнем 2 минути, но само с ROUNDUP ще изчислим времето, което ще ни таксува операторът коректно - 3 минути.

    Друг пример е с разходните норми. Примерно ако за 1 кв. м. площ са необходими 200 гр. боя, то за 110 кв.м трябват 22 кг. Но боята се продава само в разфасовки от по 5 кг. 22/5 = 4.4, но 4.4 кофи с боя не можем да купим. Тогава закръгляме нагоре, защото 4 пък ще са малко.

  • Защо клетката не се запълва изцяло като ползвам Data Bars?

    Иво Веселинов:

    Като ползвам Data Bars format style клетката със най-висока стойност не се запълва на 100%. Като разгледах подробно настройките не открих опция която да запълни цялата клетка като от видеото по-горе? Работя на версия 2007.

    Исмаил Исмаил:

    запълването на клетките е зададена като Gradient Fill (преливки). Трябва да избереш Solid Fill. 

    Иво Веселинов:

    Ами при 2007-ма версия не го намирам като опция, явно са го добавили после.

    Исмаил Исмаил:

    Проверих го във версия 2007 наистина не запълва клетката и опцията, която показах на картинките я няма в версия 2007. Поне аз не открих друго решение на въпроса.

    Георги Влайков:

    Здравей Иво работя с версия 2007 пробвах вашият вариант и проблема е че е избран цветовия диапазон който не запълва клетките напълно със съответния цвят. Може да се използва и ново правило което е обяснено доста подробно на този линк: https://www.youtube.com/watch?v=r1xA0eWaI1k

    Никола Петров:

    Едно предложение: Лек ден и успехи!

    Никола Петров:

    Лек ден и успехи!

  • Как да форматирам няколко реда едновременно,като клетката за условието се променя?

    Галин Ганчев:

    Имам един ред 8 с 5 клетки в които искам да въведа условно форматиране да оцветява в червено всички стойности над стойността в клетка L8.Ако стойността е по-малка или равна съответната клетка да остане неоцветена.
    Въпросът ми е как да форматирам всички редове по този начин,като например ред 9 условна клетка L9,ред 10 условна клетка L10  и така нататък.Може ли да се маркират няколко реда и да се форматира,а не да се прави ред по ред?

    Краси Кръстев:

    Здравей, ето тук има подобна тема : 


    http://aula.bg/question#id-112102400001 

    виж дали ще ти свърши работа, ако не пише пак ще помогнем тук във форума.
    Галин Ганчев:

    Благодаря,но не е това.Имам предвид маркирам петте клетки със стойности в ред 9 например и отивам на conditional formatting-Highlight cells rules-Greater than L9 и съм ОК.След това отивам на ред 10 и правя същото,но Greater than L10 и така надолу.Може ли това да се направи за всички редове едновременно без да се налага да ходя на всеки ред и да го маркирам едно по едно?

    Краси Кръстев:

    Аха 


    aми направи го просто като фиксираш само реда, т.е. слектираш ЦЕЛИЯ диапзаона под първата клетка  с която сравняваш ( да речем че под тази първа клетка на сравнение е L10 и М10 ) и в Formating Rule manager - a за сравнение задаваш не $L$9 ами $L9 и така за всяка следващата клетка в диапазона сравнителанта клетка ще става автоматично на L10, L11 респективно с L10 ще се сраавнява с L 9, L 11 с L 10 и така .

    Ето и една снимка с много просто пример за два реда как би изглеждала формулката ( снимка 1 ) 

    На снимка 1 показвам че втория ред се оцветява защото се сравнява с 10 с 20 и 10 е по - малко следва да се оцвети, А 11 от третия ред не е оцветено тъй като се сравнява с 10.


    На снимка 2 съм показал че ако променя на 5 третия ред то той е по - малък от 10 и се оцветява


    Галин Ганчев:

    Точно така е.Много благодаря!

  • Краси Кръстев:

    Здравейте,

     мисля че този казус лесно може да се реши чрез използването на Find and Реплаце ( в Модул 4 последния урок е показано действието на тази функция на Excel ). Специално за този случай следва "Бяло вино" да се замени с ....  нищо .Ако и след преглед на урока изпитвате затруднения пишете.
    Diana Siteva:

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

    Избор по редове или колони освен с Shift+Ctrl+стрелките става и с Shift+End+стрелка според желаната посока, нещо като "избери до края всичко до долу/вдясно/ и пр."

  • Какво е това велкъп? Извинявам се ,че въпроса не ми е свързан с урока,но скоро ще имам изпит и ми трябва бърз отговор.Благодаря!

    Megi:

    Какво е това велкъп? Извинявам се ,че въпроса не ми е свързан с урока,но скоро ще имам изпит и ми трябва бърз отговор.Благодаря!

    Daniela:

    Здравей Megi, Това е формулата Vlookup. Тя търси стойност в най-лявата колона от таблица и след това връща стойността в същата редица от колона, която ти определяш . По-простичко казано търси се съвпадение от първа таблица във втора, като се слагат стойностите от втората таблица в първата .

    Megi:

    Благодаря!А дали ще я разглеждаме по-подробно в някой урок и ако да в кой?

    Краси Кръстев:

    @Megi

    Здравей, Меги.

    Да има тук:

    http://aula.bg/Excel-avtomatichno-izvajdane-na-danni-ot-otdelni-lista

    в урок 3 Автоматично изваждане на данни от отделни листа - Vlookup от Модул 7 Използване на Excel като база данни и връзка с външни файлове

    Megi:

    Благодаря:)

  • Как да ползвам Conditional Formatting за да имам резултат форматирана клетка в два цвята , в зависимост от VLOOKUP функция

    Plamen Todorov:

    Здравейте, бих искал да знам как да ползвам Conditional Formatting за да имам като резултат форматирана клетка в два цвята - червено и зелено, в зависимост от VLOOKUP функция, например като VLOOKUP(B1,table_array,1,FALSE). Попаднах на формула =$B1=IFERROR(VLOOKUP($B1,LOCATIONS,1,FALSE),0) която форматира вярното в зелено, но не форматира клетките които са с резултат FALSE, както и оцветява празните клетки. Благодаря Пламен

    Димитринка Вълкова:

    Ако правилно съм разбрала, проверявате дали резултатът на Vlookup връща грешка (най-често е #N/A) или стойност. Опитайте като зададете две условия: 1. Проверявате дали стойността е число =ISNUMBER($B1). Функцията връща TRUE, ако е число (ще оцвети клетката - например в зелено), а FALSE, ако не е число. 2. Проверявате дали е грешен резултат =ISERROR($B1). Функцията връща TRUE, ако е някоя от грешките и включително #N/A и ще оцвети клетката, например в червено. Надявам се да свърши работа. Успех!

    Краси Кръстев:

    Здравей, Пламен.

    Ето едно видео с което ще разрешиш проблема:

    Video

    Ето и няколко теми, свързани с оцветяването на база условия:

    Как да оцветим клетки в Excel на базата на условия?

    Как да се оцветяват редове при зададено условие ?

    ще са ти доста полезни.

    :) Докато направя видеото, Дима малко ме е изпреварила с отговора ... говорим за едни и същи неща :)

    Никола Петров:

    Малко допълнение: след написването на клетката, в случая Е3, може да използваме и клавиша F9 - последователното му натискане сменя абсолютизирането на клетките! Лек ден и успехи!

    Никола Петров:

    Друго решение: За останалите стойности в диапазона избираме Дано да помогне! Лек ден и успехи!

    Plamen Todorov:

    Хора, благодаря! Такава приятна и полезна изненада не съм очаквал. Благодаря за вашия труд. Специално благодаря за видеото на Краси Кръстев!

    Краси Кръстев:

    Моля! Радвам се, че казусът е разрешен.