Izmantojot Excel nosacījuma formatējumu, varat pielietot dažādas formatēšanas opcijas šūnu vai šūnu diapazonam , kas atbilst noteiktiem jūsu iestatītajiem nosacījumiem.
Formatēšanas opcijas tiek izmantotas tikai tad, ja atlasītās šūnas atbilst šiem iestatītajiem nosacījumiem.
Iespējamās formatēšanas opcijas ietver fonta un fona krāsu izmaiņas, fontu stilus, šūnu robežas un datu formāta pievienošanu.
Kopš Excel 2007 programmā Excel ir vairākas iebūvētas opcijas vispārpieņemtiem nosacījumiem, piemēram, tādu skaitļu atrašanai, kas ir lielāki vai mazāki par noteiktu vērtību, vai skaitļu atrašanu, kas ir virs vai zem vidējās vērtības .
Papildus šīm iepriekš iestatītajām opcijām arī var izveidot pielāgotus nosacījuma formatēšanas noteikumus, izmantojot Excel formulas, lai pārbaudītu lietotāja norādītos apstākļus.
Vairāki noteikumi tiek piemēroti
Tiem pašiem datiem var piemērot vairāk nekā vienu noteikumu, lai pārbaudītu dažādus apstākļus. Piemēram, budžeta datiem var būt noteikti nosacījumi, kas piemēro formatēšanas izmaiņas, ja tiek iztērēti noteikti līmeņi - piemēram, 50%, 75% un 100% no kopējā budžeta.
Šādos apstākļos Excel vispirms nosaka, vai dažādie noteikumi ir pretrunā, un, ja tā, tad programma izpilda noteiktu prioritātes kārtību, lai noteiktu, kurš nosacījuma formatēšanas noteikums tiek piemērots datiem.
Piemērs: datu atrašana, kas pārsniedz 25% un 50% pieaugumu ar nosacīto formatējumu
Turpmākajā piemērā divi pielāgoti nosacījuma formatēšanas noteikumi tiks piemēroti šūnu B2 līdz B5 diapazonam.
- pirmais noteikums pārbauda, vai dati no šūnām no A2 līdz A5 ir lielāki par atbilstošo vērtību no B2 līdz B5 par vairāk nekā 25%.
- otrais noteikums pārbauda, vai tie paši dati A2: A5 pārsniedz attiecīgo vērtību B2: B5 par vairāk nekā 50%.
Kā redzams attēlā iepriekš, ja viens no iepriekš minētajiem nosacījumiem ir taisnība, šūnas vai šūnu fona krāsa diapazonā B1: B4 mainīsies.
- datiem, kuros starpība ir lielāka par 25%, šūnas fona krāsa mainās uz zilu.
- ja starpība ir lielāka par 50%, šūnas fona krāsa mainās uz sarkanu.
Noteikumi, ko izmanto, lai veiktu šo uzdevumu,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%tiks ievadīts, izmantojot nosacīto formatējumu Jauna formatēšanas kārtulas dialoglodziņš .
Ievadiet apmācības datus
- Ievadiet datus šūnās no A1 līdz C5, kā redzams attēlā iepriekš
Piezīme: apmācības 3. solī tiks pievienotas formulas šūnām C2: C4, kas parāda precīzu procentuālo starpību starp vērtībām šūnās A2: A5 un B2: B5, lai pārbaudītu nosacījuma formatēšanas noteikumu precizitāti.
Konfigurācijas formatēšanas noteikumu iestatīšana
Kā jau tika minēts, nosacījuma formatēšanas noteikumi, kas pārbauda abus nosacījumus, tiek ievadīti, izmantojot nosacījuma formātu New Formatting Rule dialoglodziņu .
Nosakiet nosacīto formatējumu, lai palielinātu vairāk par 25%
- Darblapā iezīmējiet šūnas no B2 līdz B5.
- Noklikšķiniet uz lentes cilnes Sākums .
- Lai atvērtu nolaižamo izvēlni, lenti pieskarieties ikonai Nosacījuma formatēšana .
- Izvēlieties Jauns noteikums, lai atvērtu dialoglodziņu Jauna formatēšanas kārtula, kā redzams attēlā, kas atrodas augšpusē.
- Dialoglodziņa augšējā pusē noklikšķiniet uz pēdējās opcijas: izmantojiet formulu, lai noteiktu, kuras šūnas formatēt.
- Dialoglodziņa apakšējā pusē noklikšķiniet uz formāta vērtības, kurās šī formula ir taisnība: rinda.
- Ierakstiet formulu : = (A2-B2) / A2> 25% paredzētajā vietā
- Noklikšķiniet uz pogas Format , lai atvērtu dialoglodziņu Format Cells.
- Šajā dialoglodziņā noklikšķiniet uz cilnes Aizpildīt un izvēlieties zilu aizpildījuma krāsu.
- Divreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņus un atgrieztos darblapā.
- Šajā brīdī šūnu B3 un B5 fona krāsa ir zila.
Nosakiet nosacīto formatējumu, lai palielinātu vairāk par 50%
- Ar joprojām atlasītajām šūnām B2 līdz B5 atkārtojiet no 1. līdz 6. pakāpi iepriekš.
- Ierakstiet formulu: = (A2-B2) / A2> 50% paredzētajā vietā.
- Noklikšķiniet uz pogas Format , lai atvērtu dialoglodziņu Format Cells.
- Noklikšķiniet uz cilnes Aizpildīt un izvēlieties sarkano aizpildījuma krāsu.
- Divreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņus un atgrieztos darblapā .
- Šūnu B3 fona krāsa joprojām ir zila, norādot, ka procentuālā starpība starp skaitļiem šūnās A3 un B3 ir lielāka par 25%, bet ir mazāka vai vienāda ar 50%.
- Šūnu B5 fona krāsai vajadzētu mainīt uz sarkanu, norādot, ka procentuālā starpība starp skaitļiem šūnās A5 un B5 ir lielāka par 50%.
Nosacītu formatēšanas noteikumu pārbaude
Aprēķina% starpību
Lai pārbaudītu, vai ievadītie nosacījuma formatēšanas noteikumi ir pareizi, mēs varam ievadīt formulas šūnās C2: C5, kas aprēķinās precīzu procentuālo starpību starp skaitļiem diapazonos A2: A5 un B2: B5.
- Noklikšķiniet uz šūnas C2, lai padarītu to par aktīvo šūnu.
- Ievadiet formulu = (A2-B2) / A2 un nospiediet tastatūras taustiņu Enter.
- Atbilde 10% jāparādās šūnā C2, norādot, ka skaitlis šūnā A2 ir par 10% lielāks nekā skaitlis B2 šūnā.
- Lai parādītu atbildi procentos, var būt nepieciešams mainīt formatējumu šūnā C2.
- Izmantojiet aizpildīšanas rokturi, lai kopētu formulu no C2 šūnas uz C3 līdz C5.
- Šūnu C3 līdz C5 atbildēm jābūt: 30%, 25% un 60%.
- Šajās šūnās sniegtās atbildes rāda, ka nosacītie formatēšanas noteikumi ir pareizi, jo atšķirība starp šūnām A3 un B3 ir lielāka par 25% un starpība starp šūnām A5 un B5 ir lielāka par 50%.
- Kamera B4 nemaina krāsu, jo atšķirība starp šūnām A4 un B4 ir 25%, un mūsu nosacītā formatēšanas noteikums noteic, ka fona krāsai zilā krāsā vajadzēja pārsniegt 25%.
Nosacījumu formēšanas noteikumu prioritātes secība
Konfliktu nosacītu formatēšanas noteikumu piemērošana
Ja vienam datu klāstam tiek piemēroti vairāki noteikumi, Excel vispirms nosaka, vai noteikumi ir pretrunā.
Pretrunīgie noteikumi ir tie, kur katram noteikumam izvēlētās formatēšanas opcijas nevar vienlaikus piemērot vieniem un tiem pašiem datiem .
Piemērā, kas tiek izmantots šajā apmācībā, noteikumi ir pretrunā, jo abos noteikumos tiek izmantota tāda pati formatēšanas opcija kā fona šūnu krāsas maiņa.
Situācijā, kad otrais noteikums ir taisnība (vērtības atšķirība starp divām šūnām ir lielāka par 50%), tad arī pirmais noteikums (vērtības atšķirība ir lielāka par 25%).
Excel prioritātes secība
Tā kā šūnam vienā un tajā pašā laikā nevar būt gan sarkans, gan zils fons, Excel ir jāzina, kurš nosacījuma formēšanas noteikums jāpiemēro.
Kuru likumu piemēro, nosaka pēc Excel prioritātes secības, kas nosaka, ka dialoglodziņa Nosacījumu formatēšanas noteikumu pārvaldnieks dialoglodziņā ir prioritāte.
Kā redzams attēlā iepriekš, šajā apmācībā izmantotais otrais noteikums (= (A2-B2) / A2> 50%) sarakstā ir lielāks, un tāpēc tam ir prioritāte pār pirmo noteikumu.
Tā rezultātā šūnas B5 fona krāsa tiek mainīta uz sarkanu.
Pēc noklusējuma saraksta augšpusē tiek pievienoti jauni noteikumi, tādēļ tiem ir augstāka prioritāte.
Lai mainītu prioritātes secību, dialoglodziņā augšup un lejup vērstās bultiņas pogas izmantojiet augšpusē redzamo attēlu.
Nekonfliktu noteikumu piemērošana
Ja divi vai vairāki nosacījuma formatēšanas noteikumi neizslēdz konfliktus, tie tiek piemēroti, kad katra noteikuma nosacījums tiek pārbaudīts, kļūst taisnība.
Ja mūsu piemērā pirmais nosacījuma formēšanas noteikums (= (A2-B2) / A2> 25%) formatēja šūnu B2: B5 diapazonu ar zilu robežu, nevis zilu fona krāsu, abi nosacījuma formatēšanas noteikumi nebūtu pretrunā, jo abi formāti var tikt piemēroti, netraucējot otru.
Tā rezultātā šūnai B5 būtu gan zilā, gan sarkanā krāsu fona krāsa, jo atšķirība starp skaitļiem šūnās A5 un B5 ir lielāka par 25 un 50 procentiem.
Nosacītais formatējums un regulāra formatēšana
Gadījumā, ja rodas konflikti starp nosacījuma formatēšanas noteikumiem un manuāli pielietotās formatēšanas opcijām, nosacījuma formatēšanas noteikums vienmēr ir prioritāte un tas tiks piemērots manuāli pievienoto formatēšanas iespēju vietā.
Ja dzeltenā fona krāsa sākotnēji tika pielietota paraugu šūnām B2 līdz B5, tad, kad tika pievienoti nosacījuma formatēšanas noteikumi, tikai šūnas B2 un B4 paliktu dzeltenas.
Tā kā nosacītie formatēšanas noteikumi tika piemēroti šūnām B3 un B5, to fona krāsas attiecīgi mainās no dzeltenas uz zilu un sarkanu.