Nosacījuma formatēšana programmā Excel – detalizēti ar piemēriem

Ja jums ir nepieciešams vizuāli parādīt informāciju, to ir ļoti ērti izdarīt, izmantojot nosacīto formatējumu. Lietotājs, kurš izmanto šo pakalpojumu, ietaupa milzīgu enerģijas un laika daudzumu. Lai iegūtu nepieciešamo informāciju, pietiek ar ātru faila apskati.

Kā veikt nosacījumu formatēšanu programmā Excel

Jūs varat atrast “Nosacījuma formatēšana” pašā pirmajā lentes cilnē, dodoties uz sadaļu “Stili”. 

Tālāk ar aci jāatrod bultiņas ikona nedaudz pa labi, jāpārvieto kursors uz to. Pēc tam tiks atvērti iestatījumi, kuros varat elastīgi konfigurēt visus nepieciešamos parametrus. 

Pēc tam jums ir jāizvēlas atbilstošais operators, lai salīdzinātu vēlamo mainīgo ar skaitli. Ir četri salīdzināšanas operatori — lielāks par, mazāks par, vienāds ar un starp. Tie ir norādīti noteikumu izvēlnē. 

Tālāk mēs norādījām skaitļu sēriju diapazonā A1:A11.

Formatēsim piemērotu datu kopu. Pēc tam atveram iestatījumu izvēlni “Nosacītā formatēšana” un iestatām nepieciešamos datu atlases kritērijus. Piemēram, mūsu gadījumā mēs izvēlēsimies kritēriju “Vairāk”. 

Tiks atvērts logs ar opciju kopu. Loga kreisajā daļā ir lauks, kurā jānorāda skaitlis 15. Labajā daļā ir norādīta informācijas izcelšanas metode, ja tā atbilst iepriekš norādītajam kritērijam. Rezultāts tiks parādīts nekavējoties.

Pēc tam pabeidziet konfigurāciju, nospiežot pogu Labi.

Kam paredzēts nosacījumformatējums?

Zināmā mērā var teikt, ka tas ir rīks, pēc kura apgūšanas darbs ar Excel vairs nebūs tāds pats. Tas ir tāpēc, ka tas padara dzīvi daudz vieglāku. Tā vietā, lai katru reizi manuāli iestatītu formatējumu šūnai, kas atbilst noteiktam nosacījumam, un arī mēģinātu pats pārbaudīt, vai tas atbilst šim kritērijam, jums vienkārši jāiestata iestatījumi vienreiz, un tad Excel visu darīs pati.

Piemēram, visas šūnas, kurās ir skaitlis, kas lielāks par 100, var padarīt sarkanu. Vai arī nosakiet, cik dienas ir atlikušas līdz nākamajam maksājumam, un pēc tam iekrāsojiet zaļā krāsā tās šūnas, kurās termiņš vēl nav pietiekami tālu. 

Ņemsim piemēru.

Šajā tabulā parādīti krājumi, kas ir noliktavā. Tam ir kolonnas. kā produktu, vidējais pārdošanas apjoms (mērīts vienībās nedēļā), krājums un cik nedēļas ir atlikušas šī produkta.

Tālāk iepirkumu vadītāja uzdevums ir noteikt tos amatus, kuru papildināšana ir nepieciešama. Lai to izdarītu, jāskatās uz ceturto kolonnu no kreisās puses, kurā reģistrē preču krājumus pa nedēļām.

Pieņemsim, ka kritērijs, lai noteiktu panikas cēloni, ir mazāks par 3 nedēļām. Tas norāda, ka mums ir jāsagatavo pasūtījums. Ja preču krājums ir mazāks par divām nedēļām, tad tas norāda uz nepieciešamību steidzami veikt pasūtījumu. Ja tabulā ir ļoti daudz pozīciju, manuāli pārbaudīt, cik nedēļas ir atlikušas, ir diezgan problemātiski. Pat ja izmantojat meklēšanu. Un tagad paskatīsimies, kā izskatās tabula, kas izceļ deficīta preces sarkanā krāsā.

Un patiešām, tajā ir daudz vieglāk orientēties. 

Tiesa, šis piemērs ir izglītojošs, kas, salīdzinot ar reālo ainu, ir nedaudz vienkāršots. Un ietaupītās sekundes un minūtes, regulāri izmantojot šādas tabulas, pārvēršas stundās. Tagad pietiek tikai aplūkot tabulu, lai saprastu, kuras preces ir deficīts, un nepavadīt stundas, analizējot katru šūnu (ja šādu preču vienību ir tūkstošiem).

Ja ir “dzeltenas” preces, tad jāsāk tās pirkt. Ja atbilstošā pozīcija ir sarkana, tad jums tas jādara nekavējoties. 

Pēc citas šūnas vērtības

Tagad apskatīsim tālāk sniegto praktisko piemēru.

Pieņemsim, ka mums ir šāda tabula, un mēs saskaramies ar uzdevumu izcelt rindas, kurās ir noteiktas vērtības. 

Tātad, ja projekts joprojām tiek izpildīts (tas ir, tas vēl nav pabeigts, tāpēc tas ir apzīmēts ar burtu “P”), tad mums ir jāpadara tā fons sarkans. Pabeigtie projekti ir atzīmēti ar zaļu krāsu.

Mūsu darbību secība šajā situācijā būs šāda:

1. Atlasiet vērtību diapazonu. 
2. Noklikšķiniet uz pogas "Nosacījuma formatēšana" - "Izveidot kārtulu".
3. Lūdzu, ņemiet vērā, ka mūsu gadījumā jums ir jāpiemēro formula noteikuma veidā. Tālāk mēs izmantojam funkciju IFlai izceltu atbilstošās līnijas. 

Pēc tam aizpildiet rindiņas, kā parādīts šajā attēlā.

Svarīgi! Uz virknēm ir jāatsaucas absolūti. Ja mēs atsaucamies uz šūnu, tad šajā gadījumā tā ir sajaukta (ar kolonnas fiksāciju).

Līdzīgi tiek izveidota kārtula tiem darba projektiem, kas līdz šim nav pabeigti. 

Šādi izskatās mūsu kritēriji.

Un visbeidzot, mums ir šāda datubāze.

Vairāki nosacījumi

Skaidrības labad ņemsim no tabulas diapazonu A1:A11, lai parādītu, kā reālā praktiskā piemērā varat izmantot vairākus nosacījumus, lai noteiktu šūnas formatējumu.

Paši nosacījumi ir šādi: ja skaitlis šūnā pārsniedz vērtību 6, tad tas tiek iezīmēts sarkanā krāsā. Ja tas ir zaļš, tad krāsa ir zaļa. Un visbeidzot lielākie skaitļi, vairāk nekā 20, tiks iezīmēti dzeltenā krāsā. 

Ir vairāki veidi, kā nosacījumformatēšana tiek veikta saskaņā ar vairākiem noteikumiem.

1. 1. metode. Mēs izvēlamies savu diapazonu, pēc kura nosacītā formatēšanas iestatījumu izvēlnē atlasiet tādu šūnu atlases noteikumu kā “Vairāk”. Cipars 6 ir rakstīts kreisajā pusē, bet formatējums ir iestatīts labajā pusē. Mūsu gadījumā pildījumam jābūt sarkanam. Pēc tam cikls tiek atkārtots divas reizes, bet jau ir iestatīti citi parametri – attiecīgi vairāk par 10 un zaļā krāsa un vairāk nekā 20 un dzeltenā krāsa. Jūs iegūsit šādu rezultātu.
   

   

2. 2. metode. Atveriet Excel rīka “Nosacītā formatēšana” galveno iestatījumu izvēlni. Tur atrodam izvēlni “Izveidot kārtulu” un ar peles kreiso taustiņu noklikšķiniet uz šī vienuma.
   

   

    Pēc tam atlasiet vienumu “Izmantot formulu…” (attēlā iezīmēts ar sarkanu rāmi) un iestatiet pirmo nosacījumu. Pēc tam noklikšķiniet uz Labi. Un tad cikls tiek atkārtots turpmākajiem apstākļiem tādā pašā veidā, kā aprakstīts iepriekš, bet tiek izmantota formula.

   

Ir svarīgi to ņemt vērā. Mūsu piemērā dažas šūnas vienlaikus atbilst vairākiem kritērijiem. Programma Excel atrisina šo konfliktu šādi: vispirms tiek piemērots iepriekš minētais noteikums. 

Un šeit ir ekrānuzņēmums labākai izpratnei.

Piemēram, mums ir skaitlis 24. Tas vienlaikus atbilst visiem trim nosacījumiem. Šajā gadījumā būs aizpildījums, kas atbilst pirmajam nosacījumam, neskatoties uz to, ka tas vairāk atbilst trešajam. Tāpēc, ja ir svarīgi, lai skaitļi virs 20 tiktu aizpildīti ar dzeltenu krāsu, pirmajā vietā ir jāievieto trešais nosacījums. 

Nosacītā datuma formatēšana

Tagad apskatīsim, kā nosacījumformatējums darbojas ar datumiem. Mums ir tik foršs klāsts.

Šajā gadījumā jums ir jāiestata nosacījumformatēšanas noteikums, piemēram, “Datums”.

Pēc tam tiek atvērts dialoglodziņš ar visu nosacījumu kopumu. Detalizēti ar tiem varat iepazīties šajā ekrānuzņēmumā (tie ir norādīti ekrāna kreisajā pusē saraksta veidā).

Mums vienkārši jāizvēlas piemērotais un jānoklikšķina uz pogas “OK”.

Redzam, ka tie datumi, kas attiecas uz pēdējo nedēļu šī diapazona sastādīšanas laikā, tika iezīmēti sarkanā krāsā. 

Izmantojot formulas

Standarta nosacījumformatēšanas noteikumu kopums ir diezgan liels. Taču situācijas ir dažādas, un ar standarta sarakstu var nepietikt. Šajā gadījumā varat izveidot savu formatēšanas kārtulu, izmantojot formulu. 

Lai to izdarītu, nosacījumformatēšanas izvēlnē noklikšķiniet uz pogas "Izveidot kārtulu" un pēc tam atlasiet ekrānuzņēmumā redzamo vienumu.

Rindas (pēc šūnas vērtības)

Pieņemsim, ka mums ir jāizceļ rinda, kurā ir šūna ar noteiktu vērtību. Šajā gadījumā mums ir jāveic tāda pati darbību secība kā iepriekš, bet virknēm. Tas nozīmē, ka varat izvēlēties, kuram diapazonam ir jāpiemēro nosacījumformatējums. 

Kā izveidot noteikumu

Lai izveidotu kārtulu, sadaļā “Nosacījuma formatēšana” ir jāizmanto atbilstošā opcija. Apskatīsim konkrētu darbību secību. 

Vispirms cilnes “Sākums” lentē ir jāatrod vienums “Nosacītā formatēšana”. To var atpazīt pēc raksturīgajām dažāda izmēra daudzkrāsainām šūnām, kurām blakus ir attēls ar izsvītrotu vienādības zīmi. 

Ir poga “Izveidot kārtulu”. Varat arī noklikšķināt uz pogas “Pārvaldīt noteikumus”, pēc kura tiks atvērts dialoglodziņš, kurā varat arī izveidot kārtulu, noklikšķinot uz atbilstošās pogas augšējā kreisajā stūrī. 

Šajā logā varat redzēt arī tos noteikumus, kas jau ir piemēroti.

Šūnu atlases noteikumi

Labi, mēs tik daudz runājam par to, kā izveidot noteikumus, bet kas tas ir? Šūnu atlases noteikums attiecas uz līdzekļiem, kurus programma ņem vērā, lai izlemtu, kā formatēt tām atbilstošās šūnas. Piemēram, nosacījumi var būt lielāki par noteiktu skaitli, mazāki par noteiktu skaitli, formulu un tā tālāk. Ir vesels to komplekts. Jūs varat iepazīties ar tiem un praktizēt to izmantošanu "smilšu kastē". 

Visu šūnu formatēšana, pamatojoties uz to vērtībām

Persona var neatkarīgi iestatīt diapazonu: visu rindu, visu dokumentu vai vienu šūnu. Programma var koncentrēties tikai uz vienas šūnas vērtību. Tas piešķir procesam elastību. 

Formatējiet tikai unikālas vai dublētās šūnas

Programmai ir tieši pievienots liels skaits noteikumu. Ar tiem var iepazīties izvēlnē “Nosacītā formatēšana”. Jo īpaši, lai formatējums attiektos tikai uz dublētām vērtībām vai tikai uz unikālām, ir īpaša opcija - "Vērtību dublikāti".

Izvēloties šo vienumu, tiek atvērts logs ar iestatījumiem, kurā var izvēlēties arī pretējo opciju – atlasīt tikai unikālas vērtības. 

Formatējiet vērtības diapazonā zem un virs vidējā

Lai formatētu vērtības, kas ir zemākas vai lielākas par vidējo, tajā pašā izvēlnē ir arī īpaša opcija. Bet jums ir jāizvēlas cita apakšizvēlne - "Pirmās un pēdējās vērtības izvēles noteikumi". 

Formatējot tikai pirmo un pēdējo vērtību

Tajā pašā apakšizvēlnē ir iespējams izcelt tikai pirmo un pēdējo vērtību ar īpašu krāsu, fontu un citām formatēšanas metodēm. Atbilstoši standartam ir iespēja atlasīt pirmo un pēdējo desmit elementu, kā arī 10% no kopējā šajā diapazonā iekļauto šūnu skaita. Bet lietotājs var patstāvīgi noteikt, cik šūnu viņam jāatlasa.

Formatējot tikai šūnas ar noteiktu saturu

Lai formatētu tikai šūnas ar noteiktu saturu, ir jāatlasa formatēšanas kārtula Vienāds ar vai Teksts satur. Atšķirība starp tām ir tāda, ka pirmajā gadījumā virknei pilnībā jāatbilst kritērijam, bet otrajā - tikai daļēji. 

Kā redzat, nosacījumformatēšana ir daudzfunkcionāla programmas Excel funkcija, kas sniedz milzīgu skaitu priekšrocību personai, kas to ir apguvusi. Mēs zinām, ka no pirmā acu uzmetiena tas viss var šķist sarežģīti. Bet patiesībā tas tiek pievilkts, tiklīdz tas izceļ noteiktu teksta daļu sarkanā krāsā (vai formatē to kā citādi), pamatojoties uz tā saturu vai saskaņā ar kādu citu kritēriju. Šī funkcija ir iekļauta Excel zināšanu pamatkomplektā, bez tās nav iespējams pat amatieru darbs ar izklājlapām un datu bāzēm.