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

Безплатни 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% от случаите.

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

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

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

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

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

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

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

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

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

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

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

    Имам един ред 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:

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

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

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