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.
Excel ir bijis 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.
01 no 04Vairāki noteikumi programmā Excel
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 attiecas uz formatējumu izmaiņām, 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.
Datu meklēšana, kas pārsniedz 25% un 50% pieaugumu
Turpmākajā piemērā divi pielāgoti nosacījuma formatēšanas noteikumi tiks piemēroti diapazonam no šūnas B2 uz B5.
- Pirmais noteikums pārbauda, vai dati ir pieejami šūnas A2 uz A5 ir lielāka par atbilstošo vērtību B2 uz B5 par vairāk nekā 25%.
- Otrais noteikums pārbauda, vai vienādi dati ir A2: A5 pārsniedz attiecīgo vērtību B2: B5 par vairāk nekā 50%.
Kā redzams attēlā iepriekš, ja kāds no iepriekš minētajiem nosacījumiem ir taisnība, šūnas vai šūnu fona krāsa diapazonā B1: B4 mainīšos.
- Datiem, kuros atšķirība ir lielāka par 25%, šūnas fona krāsa mainās uz zaļu krāsu.
- Ja starpība ir lielāka par 50%, šūnas fona krāsa mainās uz sarkanu.
Noteikumi, ko izmanto, lai veiktu šo uzdevumu, tiks ievadīti, izmantojot nosacīto formatējumu Jauns formatēšanas noteikums dialoglodziņš. Sāciet, ievadot parauga datus šūnās no A1 līdz C5, kā redzams attēlā, kas atrodas augšpusē
Iekš trešais solis no apmācības pievienos formulas šūnām C2: C4 kas parāda precīzu procentuālo starpību starp šūnu vērtībām A2: A5 un B2: B5 lai pārbaudītu nosacījuma formēšanas noteikumu precizitāti.
02 no 04Nosacīto formatēšanas noteikumu iestatīšana
Kā jau minēts, nosacījuma formatēšanas noteikumi, kas pārbauda abus nosacījumus, tiks ievadīti, izmantojot nosacīto formatējumu Jauns formatēšanas noteikums dialoglodziņš. Sāksim, iestatot mūsu pielāgoto nosacīto formatējumu:
Nosacīta formēšana, lai atrastu vairāk par 25 procentiem palielinājumu
= (A2-B2) / A2> 25%
- Izcelt šūnas B2 uz B5 darblapā.
- Noklikšķiniet uz Cilne Sākums no lente.
- Noklikšķiniet uz Nosacītā formatēšana ikona lente lai atvērtu nolaižamo sarakstu.
- Izvēlies Jauns pants lai atvērtu Jauns formatēšanas noteikums dialoglodziņš.
- No Stils: lodziņā atlasiet Klasisks iespēja.
- Otrajā nolaižamajā izvēlnē noklikšķiniet uz pēdējās opcijasLai noteiktu, kuras šūnas formatēt, izmantojiet formulu.
- Ierakstiet formula iepriekš norādītajā vietā.
- Noklikšķiniet uz Formāts ar: nolaižamajā izvēlnē atlasiet Gaiši sarkans aizpildījums ar tumši sarkanu tekstu iespēja.
- Klikšķis labi lai aizvērtu dialoglodziņus un atgrieztos darblapā.
- Šajā brīdī fona krāsa šūnas B3 un B5 vajadzētu būt zaļai.
Nosacījuma formēšana, lai atrastu vairāk nekā 50 procentu pieaugumu
= (A2-B2) / A2> 50%
Izcelt šūnas B2 uz B5 darblapā.
- Noklikšķiniet uz Cilne Sākums no lente.
- Noklikšķiniet uz Nosacītā formatēšana ikona lente lai atvērtu nolaižamo sarakstu.
- Izvēlies Jauns pants lai atvērtu Jauns formatēšanas noteikums dialoglodziņš.
- No Stils: lodziņā atlasiet Klasisks iespēja.
- Otrajā nolaižamajā izvēlnē noklikšķiniet uz pēdējās opcijasLai noteiktu, kuras šūnas formatēt, izmantojiet formulu.
- Ierakstiet formula iepriekš norādītajā vietā.
- Noklikšķiniet uz Formāts ar: nolaižamajā izvēlnē atlasiet Gaiši zaļa aizpilda ar tumši zaļo tekstu iespēja.
- Klikšķis labi lai aizvērtu dialoglodziņus un atgrieztos darblapā.
Fona krāsa šūnu B3 joprojām būtu zaļš, norādot, ka procentiem starpība starp skaitļiem šūnas A3 un B3 ir lielāks par 25 procentiem, bet ir mazāks vai vienāds ar 50 procentiem. Fona krāsa šūnu B5 vajadzētu mainīt uz sarkanu, norādot, ka procentuālā starpība starp skaitļiem iekšā šūnas A5 un B5 ir lielāks par 50 procentiem.
03 no 04Nosacītu formatēšanas noteikumu pārbaude
Lai pārbaudītu, vai nosacītie 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.
= (A2-B2) / A2
- Klikšķiniet uz šūnu C2 lai padarītu to par aktīvo šūnu.
- Ievadiet augstāk minēto formulu un nospiediet Ievadiet tastatūras taustiņš.
- Atbildei jābūt 10% šūnu C2, norādot, ka numurs ir šūna A2 ir par 10% lielāks nekā skaitlis šūnu B2.
- Var būt nepieciešams mainīt formatējumu šūnu C2 lai parādītu atbildi procentos.
- Izmantojiet aizpildiet rokturi lai kopētu formulu no šūnu C2 uz šūnas C3 uz C5.
- Atbildes par šūnas C3 uz C5 būtu 30%, 25% un 60%.
Šajās šūnās sniegtās atbildes rāda, ka nosacītie formatēšanas noteikumi ir pareizi, jo starpība starp šūnas A3 un B3 ir lielāks par 25 procentiem un starpību starp šūnas A5 un B5 ir lielāks par 50 procentiem.
Cell B4 nemainīja krāsu, jo starpība starp šūnas A4 un B4 ir vienāds ar 25 procentiem, un mūsu nosacītā formatēšanas noteikumā norādīts, ka fona krāsai ir jāpārvērš zaļā krāsā, ja tā ir lielāka par 25 procentiem.
04 no 04Nosacījuma formatēšanas prioritātes secība
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 starpība starp divām šūnām ir lielāka par 50 procentiem), arī pirmais noteikums (vērtības atšķirība ir lielāka par 25 procentiem) ir patiesa.
Excel prioritātes secība
Tā kā šūnam vienā un tajā pašā laikā nevar būt gan sarkans, gan zaļš 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 ir lielāks sarakstā, un tādēļ 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, izmantojiet Augšā un lejā bultiņu pogas dialoglodziņā, kā norādīts attēlā iepriekš.
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 pirmais nosacījuma formēšanas noteikums mūsu piemērā formatē šūnu diapazonu B2: B5 ar zaļo malu, nevis zaļu fona krāsu, divi nosacījuma formatēšanas noteikumi nebūtu pretrunā, jo abi formāti var tikt piemēroti, netraucējot otram.
Rezultātā, šūnu B5 būtu gan zaļa robeža, gan sarkana fona krāsa, jo atšķirība starp numuriem šūnas A5 un B5 ir lielāks 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 sākotnēji tika piemērota dzeltena fona krāsa šūnas B2 uz B5 piemēram, pēc nosacījuma formatēšanas noteikumu pievienošanas šūnas B2 un B4 paliktu dzeltenā krāsā. Tāpēc, ka nosacītie formatēšanas noteikumi tika piemēroti šūnām B3 un B5, to fona krāsa attiecīgi mainītos no dzeltenas uz zaļu un sarkanu.
