Nosacītā formatējuma pievienošana programmā Excel ļauj lietot dažādas formatēšanas opcijas šūnai vai šūnu diapazonam, kas atbilst konkrētiem jūsu iestatītajiem nosacījumiem. Šādu nosacījumu iestatīšana var palīdzēt sakārtot izklājlapu un atvieglot tās skenēšanu. Formatēšanas opcijas, kuras varat izmantot, ietver fonta un fona krāsas izmaiņas, fontu stilus, šūnu apmales un skaitļu formatējuma pievienošanu datiem.
Excel programmā ir iebūvētas opcijas bieži lietotiem apstākļiem, piemēram, tādu skaitļu atrašanai, kas ir lielāki vai mazāki par noteiktu vērtību, vai tādu skaitļu atrašanai, kas ir virs vai zem vidējās vērtības. Papildus šīm iepriekš iestatītajām opcijām varat arī izveidot pielāgotas nosacījumformatēšanas kārtulas, izmantojot Excel formulas.
Šie norādījumi attiecas uz Excel 2019, 2016, 2013, 2010 un Excel for Microsoft 365.
Vairāku nosacījumu piemērošana programmā Excel
Varat piemērot vairāk nekā vienu noteikumu vieniem un tiem pašiem datiem, lai pārbaudītu dažādus apstākļus. Piemēram, budžeta datiem var būt iestatīti nosacījumi, kas piemēro formatējuma izmaiņas, kad tiek sasniegti noteikti tēriņu līmeņi, piemēram, 50%, 75% un 100% no kopējā budžeta.
Šādos apstākļos programma Excel vispirms nosaka, vai dažādie noteikumi ir pretrunā, un, ja tā, programma ievēro noteiktu prioritātes secību, lai noteiktu, kurš nosacījumformatēšanas noteikums jāpiemēro datiem.
Datu atrašana, kas pārsniedz 25% un 50% pieaugumu
Šajā piemērā divi pielāgoti nosacījumformatēšanas noteikumi tiks piemēroti diapazonam cells B2 līdz B5.
- Pirmais noteikums pārbauda, vai dati šūnās A2:A5 ir lielāki par atbilstošo vērtību B2:B5 par vairāk nekā 25%.
- Otrais noteikums pārbauda, vai tie paši dati A2:A5 pārsniedz atbilstošo vērtību B2:B5 par vairāk nekā 50%.
Kā redzams augstāk esošajā attēlā, ja kāds no iepriekš minētajiem nosacījumiem ir patiess, mainīsies šūnas vai šūnu fona krāsa diapazonā B1:B4.
- Datiem, kuru atšķirība ir lielāka par 25%, šūnas fona krāsa mainīsies uz zaļu.
- Ja atšķirība ir lielāka par 50%, šūnas fona krāsa mainīsies uz sarkanu.
Šā uzdevuma veikšanai izmantotie noteikumi tiks ievadīti, izmantojot dialoglodziņu New Formatting Rule. Sāciet, ievadot parauga datus šūnās A1 līdz C5, kā redzams attēlā iepriekš.
Pamācības pēdējā daļā šūnām C2:C4 pievienosim formulas, kas parāda precīzu procentuālo atšķirību starp vērtībām šūnās A2:A5 un B2:B5; tas ļaus mums pārbaudīt nosacījumu formatēšanas noteikumu precizitāti.
Nosacītā formatēšanas noteikumu iestatīšana
Pirmkārt, mēs izmantosim nosacījumu formatējumu, lai atrastu 25 procentu vai lielāku pieaugumu.
Funkcija izskatīsies šādi:
=(A2-B2)/A2>25%
- Darblapā iezīmējiet šūnas B2 līdz B5.
- Noklikšķiniet uz Sākums cilnes no lentes.
- Lai atvērtu nolaižamo izvēlni, noklikšķiniet uz Nosacījuma formatēšana ikonas lentē.
- Izvēlieties New Rule, lai atvērtu dialoglodziņu New Formatting Rule.
-
Sadaļā Atlasiet kārtulas veidu, noklikšķiniet uz pēdējās opcijas: Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt.
- Ierakstiet iepriekš norādīto formulu zemāk esošajā laukā Formatējiet vērtības, ja šī formula ir patiesa:
- Noklikšķiniet uz pogas Format, lai atvērtu dialoglodziņu. Noklikšķiniet uz cilnes Aizpildīt un izvēlieties krāsu.
- Noklikšķiniet uz OK, lai aizvērtu dialoglodziņus un atgrieztos darblapā.
- šūnu B3 un B5 fona krāsai vajadzētu mainīties uz jūsu atlasīto krāsu.
Tagad mēs izmantosim nosacījumu formatējumu, lai atrastu pieaugumu par 50 procentiem vai vairāk. Formula izskatīsies šādi:
- Atkārtojiet pirmās piecas iepriekš minētās darbības.
- Ierakstiet iepriekš norādīto formulu zemāk esošajā laukā Formatējiet vērtības, ja šī formula ir patiesa:
-
Noklikšķiniet uz pogas Format, lai atvērtu dialoglodziņu. Noklikšķiniet uz cilnes Aizpildīt un izvēlieties citu krāsu, nekā to izdarījāt iepriekšējās darbības.
- Noklikšķiniet uz OK, lai aizvērtu dialoglodziņus un atgrieztos darblapā.
šūnas B3 fona krāsai jāpaliek nemainīgai, norādot, ka procentuālā atšķirība starp skaitļiem šūnās A3 unB3 ir lielāks par 25 procentiem, bet mazāks vai vienāds ar 50 procentiem. šūnas B5 fona krāsai vajadzētu mainīties uz jauno jūsu atlasīto krāsu, norādot, ka procentuālā atšķirība starp skaitļiem šūnās A5 un B5 ir lielāks par 50 procentiem.
Nosacītā formatēšanas noteikumu pārbaude
Lai pārbaudītu, vai ievadītie nosacījumformatēšanas noteikumi ir pareizi, mēs varam ievadīt formulas šūnās C2:C5, kas aprēķinās precīzu procentuālo atšķirību starp skaitļiem diapazonosA2:A5 un B2:B5.
Formula šūnā C2 izskatās šādi:
=(A2-B2)/A2
- Noklikšķiniet uz šūnas C2, lai padarītu to par aktīvo šūnu.
- Ierakstiet iepriekš minēto formulu 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 šūna B2.
- Var būt nepieciešams mainīt formatējumu šūnā C2, lai parādītu atbildi procentos.
- Izmantojiet aizpildīšanas rokturi, lai kopētu formulu no šūnas C2 uz cells C3 uz C5.
- Atbildēm šūnām C3 līdz C5 jābūt 30%, 25% un 60%.
Atbildes šajās šūnās parāda, ka nosacījumformatēšanas noteikumi ir precīzi, jo atšķirība starp šūnām A3 un B3 ir lielāka par 25 procenti, un starpība starp šūnām A5 un B5 ir lielāka par 50 procentiem.
Šūna B4 nemainīja krāsu, jo atšķirība starp šūnām A4 un B4 ir vienāda 25 procenti, un mūsu nosacījumformatēšanas kārtula noteica, ka, lai mainītu fona krāsu, ir nepieciešama procentuālā daļa, kas lielāka par 25 procentiem.
Nosacītā formatējuma prioritātes secība
Kad vienam un tam pašam datu diapazonam lietojat vairākus noteikumus, programma Excel vispirms nosaka, vai kārtulas ir pretrunā. Konfliktējoši noteikumi ir tie, kuros abas formatēšanas opcijas nevar lietot vieniem un tiem pašiem datiem.
Mūsu piemērā noteikumi ir pretrunā, jo abi izmanto vienu un to pašu formatēšanas opciju - mainot fona šūnas krāsu.
Situācijā, kad otrais noteikums ir patiess (vērtības atšķirība starp divām šūnām ir lielāka par 50 procentiem), tad arī pirmais noteikums (vērtības atšķirība ir lielāka par 25 procentiem) ir patiess.
Tā kā šūnai vienlaikus nevar būt divu dažādu krāsu foni, programmai Excel ir jāzina, kurš nosacījumformatēšanas noteikums tai ir jāpiemēro.
Excel prioritātes secībā ir norādīts, ka vispirms tiek lietots noteikums, kas atrodas augstāk dialoglodziņa Nosacītā formatēšanas kārtulu pārvaldnieka sarakstā.
Kā parādīts attēlā, otrais šajā apmācībā izmantotais noteikums ir augstāks sarakstā, un tāpēc tam ir prioritāte pār pirmo noteikumu. Rezultātā šūnas B5 fona krāsa ir zaļa.
Pēc noklusējuma jaunie noteikumi nonāk saraksta augšdaļā; lai mainītu secību, dialoglodziņā izmantojiet bultiņu pogas Uz augšu un Leju.
Nekonfliktējošu noteikumu piemērošana
Ja divas vai vairākas nosacījumformatēšanas kārtulas nav pretrunā, abas tiek lietotas, kad nosacījums, ko pārbauda katra kārtula, kļūst patiess.
Ja mūsu piemēra pirmajā nosacījumformatēšanas kārtulā tika formatēts šūnu diapazons B2:B5 ar oranžu apmali, nevis oranžu fona krāsu, abas nosacītās formatēšanas kārtulas konflikts, jo abus formātus var lietot, netraucējot otram.
Nosacījuma formatējums pret parasto formatējumu
Konfliktu gadījumā starp nosacījumformatēšanas kārtulām un manuāli lietotajām formatēšanas opcijām, nosacījumformatēšanas kārtulai vienmēr ir prioritāte, un tā tiks lietota visu manuāli pievienoto formatēšanas opciju vietā.
