Nosacījuma formatēšana programmā Excel: instrukcijas un lietošana

Izmantojiet šos norādījumus, lai izveidotu dažādus formātus

Nosacījuma formatēšana programmā Microsoft Excel ir noderīgs rīks, lai tabulas, atskaites vai šūnas padarītu pamanāmākas, iezīmētu apgabalu vai vairākas kolonnas, analizētu datus, noskaidrotu atšķirības vai noteiktu tendences. Šim nolūkam tiek izmantotas datu joslas, simbolu kopas vai krāsainas skalas. Atkarībā no norādītajiem nosacījumiem šūnu displejs tiek mainīts vai šūnu diapazons tiek formatēts. Jūs varat izveidot savu daudzajiem integrētajiem nosacījumiem.

Nosacīta formatēšana ir jēga un palīdz jums

Nosacījuma formatējumu programmā Excel var izmantot daudzos veidos, neatkarīgi no tā, vai tā ir Excel tabula, noteikts šūnu diapazons vai rakurstabulas pārskats. Jums ir pieejamas dažādas darbības un formātu veidnes. Excel lietotāju izšķirošā priekšrocība ir tā, ka īpašas vērtības vai rezultātus var izcelt ar krāsu kodējumu. Ja vērtība mainās, formatējums tiek automātiski pielāgots - atkarībā no apstākļiem.

info: PivotTable ("Pivot Table") ir īpaša tabula, ar kuru var aprēķināt, apkopot un analizēt lielu datu apjomu. Turklāt jūs varat ātri veikt salīdzinājumus un noteikt pārbaudes datu tendences.

Nosacījuma formatējums ar esošajiem noteikumiem attiecībā uz cipariem, teksta saturu vai datumiem

Ja formatējat ar salīdzināšanas operatoru, šūnas var atrast vieglāk. Jūs varat padarīt vērtības redzamākas, piemēram, lielākas vai mazākas par noteiktu vērtību vai noteiktas vērtības, kas atrodas starp augšējo un apakšējo robežu. Tas nozīmē, ka vēlamie vai meklētie dati tiek parādīti krāsā. Rediģēšanai ir pieejama ātra vai uzlabota formatēšana. Izmantojot paplašināto formatējumu, varat strādāt ar formulām, piemēram, lai automātiski atzīmētu Excel kolonnu krāsā.

Piemēri:

• Noliktavas darbinieks vēlas izcelt visus krājumus zem 15 gabaliem ar krāsainu fonu.
• Visas pilsētas, kurās ir vairāk nekā 150 000 iedzīvotāju, ir jāatzīmē tabulā.
• Ražošanas uzņēmums visas preces, kas ražotas laikā no 2022-2023. līdz 2022. gada 1. janvārim līdz 2022-2023. gada 31. janvārim, vēlas izcelt sarkanā krāsā.

Ātrais formāts:

Vienas vai vairāku šūnu izvēle.

Cilnē noklikšķiniet uz "Sākt".

Stila sadaļā dodieties uz bultiņu blakus pogai "Nosacītā formatēšana".

Noklikšķiniet uz "Noteikumi šūnu izcelšanai".

Izvēlieties vajadzīgo komandu, piem. B. lielāks par, mazāks, starp, datumu utt.

Ievadiet vērtības un izvēlieties formātu.

Paplašināts formatējums:

1. Tabulā vai rakurstabulas pārskatā atlasiet vienu vai vairākas šūnas.

2. Atveriet cilni "Sākums", grupā Stili noklikšķiniet uz bultiņas blakus vienumam "Nosacītā formatēšana".

3. Noklikšķiniet uz "Pārvaldīt noteikumus": tiek parādīts dialoglodziņš "Nosacījuma formatēšanas noteikumu pārvaldnieks".

4. Atlasiet darbību:

   • Noklikšķiniet uz "Jauns noteikums": Tiek parādīts dialoglodziņš "Jauns formatēšanas noteikums".
   • Lai mainītu formātu, pārliecinieties, vai saraksta lodziņā "Rādīt formatēšanas kārtulu" ir atlasīta pareizā darblapa vai tabula.
   • Laukā "Attiecas uz" dodieties uz "Sakļaut dialoglodziņu"; pēc tam darblapā atlasiet jauno šūnu diapazonu.
   • Noklikšķiniet uz pogas "Izvērst dialoglodziņu".
   • Atlasiet kārtulu un noklikšķiniet uz Rediģēt kārtulu: Tiek parādīts dialoglodziņš Rediģēt formatēšanas kārtulu.

5. Lai mainītu lauku diapazonu rakurstabulas atskaites vērtību laukā "Lietot kārtulu", rīkojieties šādi:

   • Atlase: Noklikšķiniet uz "Tikai šīs šūnas"
   • Atbilstošais lauks: Noklikšķiniet uz "Visas šūnas ar vienādiem laukiem"
   • Vērtības lauks: Noklikšķiniet uz "Visas šūnas"

6. Sadaļā “Atlasīt kārtulas veidu” noklikšķiniet uz “Formatēt tikai šūnas, kas satur”

Tagad jūs varat veikt darbības sadaļā "Rediģēt kārtulas aprakstu" saraksta laukā "Formatēt šūnas tikai ar":

Formatēt pēc numura, datuma vai laika:

Lai to izdarītu, atlasiet šūnas vērtību un salīdzināšanas operatoru un ievadiet numuru, datumu vai laiku. piemērs: Laukā “Starp” ievadiet 300 un 500 vai atlasiet “Vienāds ar” un ievadiet datumu.

Varat izmantot formulu, lai atgūtu skaitļa, datuma vai laika vērtību.

Formatēt pēc teksta:

Atlasiet "Konkrēts teksts" un "Relāciju operators".

Ievadiet tekstu, piem. B. uz "satur" vārdu "zelts" vai uz "sākas ar" prefiksu, kuru vēlaties meklēt.

Var ievadīt ne vairāk kā 255 rakstzīmes.

Vērtības var atgriezt, izmantojot formulas. Lai to izdarītu, formulai jāsākas ar (=). Nederīgas formulas nav formatētas. Vispirms ir ieteicams pārbaudīt formulu par kļūdām.

Formatēt pēc datuma:

• Izvēlieties datumu un salīdzināšanas datumu, piem. B. Vakar, nākamajā nedēļā vai šomēnes

Pēc tam: Lai norādītu formātu, noklikšķiniet uz "Formatēt", parādās dialoglodziņš "Formatēt šūnas". Izvēlieties vēlamo ciparu formātu, aizpildījuma formātu, rāmi vai fontu, noklikšķiniet uz "Labi".

Ir iespējams izvēlēties vairākus formātus. Laukā "Priekšskatījums" varat redzēt, kuri formāti ir atlasīti.

Nosacīta formatēšana ar datu bāzēm, krāsu shēmām un simbolu kopām

Šūnu saturu un vērtības var vizuāli uzlabot, izmantojot datubāzes, krāsu skalas vai simbolu kopas, un padarīt tās pievilcīgākas un skaidrākas. Ja šūnas diapazona vērtības jāsalīdzina viena ar otru, ar šo nosacīto formatējumu ir vieglāk.

Formatējiet šūnas ar datu joslām

Datu joslas atvieglo šūnu salīdzināšanu. Šūnas vērtību norāda joslas garums. Jo augstāka vērtība, jo garāka datu josla, jo zemāka vērtība, jo īsāka josla. Tas ļauj ātri noteikt augstās vai zemās vērtības.

Ātrais formāts:

Atzīmējiet vajadzīgo apgabalu (šūnu, tabulu vai visu lapu).

Cilnē Sākums noklikšķiniet uz pogas Nosacījuma formatēšana.

Dodieties uz "datu joslu" un atlasiet pildījumu.

Formatējiet šūnas ar krāsainiem rāmjiem

Krāsu skalas palīdz vieglāk klasificēt dažādu datu informāciju un var ātri salīdzināt to saturu. Atkarībā no krāsas toņa tiek parādītas augstas vai zemas vērtības. Jūs varat formatēt šūnas ar 2 vai 3 krāsu skalu.

2 krāsu skala

Ātrais formāts:

Atlasiet šūnu, tabulu vai visu lapu.

Cilnē Sākums noklikšķiniet uz Nosacījuma formatēšana.

Dodieties uz "krāsu skalu" un noklikšķiniet uz vēlamā divu krāsu skalas formāta. Augšējā krāsa apzīmē augstākas vērtības, apakšējā krāsa - zemākas vērtības.

3 krāsu skala

Trešā krāsa ļauj vizualizēt vidējās vērtības- papildus augstās un zemās vērtības krāsu attēlojumam kā 2 krāsu skalā.

Ātrais formāts:

Atlasiet šūnu, tabulu vai visu lapu.

Cilnē Sākums noklikšķiniet uz Nosacījuma formatēšana.

Dodieties uz "krāsu skalu" un noklikšķiniet uz vēlamā 3 krāsu skalas formāta. Augšējā krāsa apzīmē augstākas vērtības, apakšējā krāsa - zemākas vērtības.

Formatējiet šūnas ar simbolu kopām

Izmantojot simbolu kopu, datus var apskatīt un klasificēt atsevišķās kategorijās. Simbols apzīmē noteiktu vērtību diapazonu. Nosacījuma formatējums programmā Excel piedāvā dažādu krāsu simbolus: bultiņas, punktus, krustiņus, izsaukuma zīmes un daudz ko citu.

Ātrais formāts:

Atlasiet šūnu, tabulu vai visu lapu.

Cilnē Sākums noklikšķiniet uz Nosacījuma formatēšana.

Dodieties uz "Simbolu kopas" un izvēlieties vajadzīgo simbolu kopu

Formatēšanas noteikumus izveidojiet pats

Ja nevarat atrast piemērotu iespēju Excel piedāvātajā nosacītajā formatēšanā, varat viegli izveidot individuālus formatēšanas noteikumus.

Atzīmējiet nosacīti formatējamo šūnu diapazonu.

Cilnē Sākums atveriet sadaļu "Nosacītā formatēšana".

Atlasiet "Jauns noteikums" un apstipriniet ar "Labi".

Ja nevarat atrast vajadzīgo opciju savam formatēšanas noteikumam, var palīdzēt loģiska formula. Piemēram, varat izmantot formulu, lai automātiski krāsotu Excel šūnas, tiklīdz saturs ir pieejams. Savukārt tukšas šūnas var nosacīti formatēt.

Piemērs: Izmantojot formulu, varat salīdzināt datus, kas atrodas ārpus atlasītā diapazona. Tas pat ietver datus, kas atrodas citā darblapā citā darblapā. Vai arī varat salīdzināt vērtības ar kādas funkcijas atgrieztu rezultātu. Formulai jābūt loģiskai un jāatgriež “true” vai “false”.

Iezīmējot šūnas tieši darblapā, jūs varat ievadīt šūnu atsauces formulā. Šīs ir absolūtas šūnu atsauces. Tomēr programma Excel var arī pielāgot šūnu atsauces katrai šūnai atlasītajā apgabalā. Ja vēlaties, atlasiet relatīvās šūnu atsauces.

Soli pa solim instrukcijas kārtulas izveidošanai

1. Cilnes "Sākums" grupā "Stili" dodieties uz bultiņu blakus "Nosacījuma formatēšana" un noklikšķiniet uz "Pārvaldīt noteikumus". Tiek parādīts dialoglodziņš Nosacījuma formatēšanas noteikumu pārvaldnieks.

2. Ir iespējamas šādas darbības:

   • Ja vēlaties pievienot nosacījuma formātu, dodieties uz "Jauns noteikums".
   • Nosacījuma formāta maiņa:
   • • Pārbaudiet, vai atrodaties pareizajā darblapā.
     • Vispirms sadaļā Attiecas uz noklikšķiniet uz Sakļaut dialoglodziņu. Pēc tam atlasiet jauno šūnu diapazonu un dodieties uz "Izvērst dialoglodziņu".
     • Atlasiet vēlamo noteikumu, noklikšķiniet uz "Rediģēt kārtulu". Parādās “Rediģēt formatēšanas kārtulu”.

3. Sadaļā “Atlasīt kārtulas veidu” noklikšķiniet uz “Izmantot formulu, lai noteiktu formatējamās šūnas”.

   • Sadaļā “Rediģēt kārtulas aprakstu” ievadiet formulu “Formāta vērtības, kurām šī kārtula ir patiesa”. Formula sākas ar (=), un tai jāatgriež “true” (1) vai “false” (0).
   • Noklikšķiniet uz Formatēt, lai parādītu dialoglodziņu Formatēt šūnas.
   • Tagad atlasiet numuru, fontu, aizpildījuma formātu utt., Kas jāizmanto, ja šūnas vērtība ir patiesa, un apstipriniet ar "Labi". Atlasītie formāti tiek parādīti sadaļā "Priekšskatījums".

Piemēram, COUNTIF formula atgriež loģiskās vērtības. Ja tiek izmantota šī formula, var noteikt dublētas vērtības vai noteiktā frekvencē sastopamu saturu var izcelt krāsā. Tādā veidā unikālus priekšmetus var arī atrast un izcelt krāsās. Citas formulas, ko var izmantot nosacījuma formatēšanai, ir

• ATPŪTA: tāpēc mainīgas līniju krāsas var iestatīt automātiski
• IDENTISKI: Izmantojot šo formulu, meklētos datus var atzīmēt Excel tabulā, kas atbilst noteiktai vērtībai
• KĻŪDA: automātiski atzīmē tukšās šūnas Excel tabulā vai novērš kļūdu vērtības no darbgrāmatas
• NOT un ISNUMBER: formatē visas Excel šūnas, kurās nav skaitļu
• MAX: lielākā šūnas vērtība ir iezīmēta tabulā
• MIN: ļauj nosacīti formatēt vismazāko vērtību Excel sarakstā, lai minimāli varētu redzēt īsumā
• KREISIS: atkarībā no rakstzīmju virknes sākumā Excel šūnas tiek automātiski formatētas un tām tiek piešķirta vienota teksta krāsa
• LARGE un LEFT: ļauj iekrāsot šūnu ar jaunu saturu
• PAREIZI: Šūnas var formatēt, pamatojoties uz virknes pēdējo rakstzīmi
• ŠODIEN: ar to tikšanās var formatēt krāsās, izmantojot luksofora metodi, vai arī var izcelt datuma vērtības, kuras drīzumā būs atjauninātas
• MĒNESIS vai GADS: ļauj sadalīt Excel sarakstu mēnešos vai gados; Izmantojot YEAR, var atzīmēt arī kārtējā gada datuma vērtības un izcelt gada vērtības
• VAI un NEDĒĻAS DIENA: krāsas izceļ atbilstoši nedēļas dienai

Piemērs, kā izveidot kārtulu UN un VAI pārbaudēm:

Šajā piemērā nosacījuma formatēšanai tiek izmantotas divas vērtības. Ja pirmais noteikums nav spēkā, otrais noteikums tiek piemērots automātiski.

Pirmais noteikums: Mājas pircējs vēlas pārbaudīt, vai plānotais īpašuma pirkums atbilst viņa budžetam. Viņš kā pirmo iemaksu var ienest līdz pat 80 000 eiro pašu kapitāla un apkalpot ikmēneša aizdevuma iemaksu, kas nepārsniedz 1200 eiro. Ja gan pirmā iemaksa, gan aizdevuma iemaksa atbilst šīm prasībām, atbilstošās šūnas ir atzīmētas ar zaļu krāsu.

Otrais noteikums: Ja pirmā iemaksa vai iemaksa neatbilst prasībām, atbilstošās šūnas ir atzīmētas ar sarkanu krāsu.

Formula pirmā noteikuma izveidošanai, izmantojot zaļo krāsu, ir šāda:

= UN (JA ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Formula, kā izveidot otro noteikumu, izmantojot sarkano krāsu, ir šāda:

= VAI (JA ($ B $ 4 = 1200,1))

Pārsūtīt un dzēst nosacījuma formatējumu

Nosacījuma formatējumu var kopēt un piemērot citiem vai jauniem darblapas datiem vai izdzēst vēlreiz.

Pārsūtīt nosacījuma formatējumu

Lai kopētu nosacīto formatējumu un ielīmētu to citās šūnās vai šūnu diapazonos, izmantojiet pogu "Pārsūtīt formātu".

Atlasiet šūnas ar nosacīto formatējumu, kuras vēlaties kopēt un ielīmēt citās šūnās, un noklikšķiniet uz tām.

Tagad dodieties uz "Sākt" un pēc tam uz "Pārsūtīšanas formāts". Kursors parādās kā birste.

Šūnai vai šūnu diapazonam varat pievienot nosacītu formatējumu, pārvietojot otu virs šūnas.

Ar ESC jūs pārtraucat otas lietošanu.

Jūs joprojām varat izmantot suku, lai ievietotu nosacījuma formatējumu, veicot dubultklikšķi uz "Pārsūtīšanas formāts".

Alternatīvi, nosacījuma formatējumu var kopēt, izmantojot īsinājumtaustiņus, un ielīmēt citā Excel šūnā.

Notīrīt nosacījuma formatējumu

Izšķir dzēšanas procesu darblapā un dzēšanas procesu šūnu diapazonā.

Dzēst darblapā vai visā darblapā

Cilnē Sākums atveriet sadaļu "Nosacītā formatēšana".

Pēc tam noklikšķiniet uz "dzēst noteikumus"

Pēc tam noklikšķiniet uz "Dzēst noteikumus visā lapā".

Varat arī izdzēst nosacījuma formatējumu, izmantojot dialoglodziņu "Atlasīt saturu".

Dzēst šūnu diapazonā

Iezīmējiet šūnas, kurās ir vēlamais nosacījuma formatējums.

Noklikšķiniet uz pogas "Lasīšanas analīzes palielināmā stikla lasīšana" (poga atrodas labajā apakšējā stūrī blakus atlasītajiem datiem).

Dodieties uz "Dzēst formātu".

Atrodiet un dzēsiet tos pašus nosacījuma formātus visā darblapā

Noklikšķiniet uz šūnas ar atbilstošu nosacījuma formātu.

Cilnē "Sākums" noklikšķiniet uz bultiņas blakus "Atrast un atlasīt", pēc tam noklikšķiniet uz "Atlasīt saturu".

Noklikšķiniet uz "Nosacījuma formāti".

Sadaļā "Datu pārbaude" dodieties uz "Tas pats" (tas atlasīs visas šūnas ar vienādu nosacīto formatējumu).

Cilnē "Sākt" atveriet "Nosacījuma formatēšana", pēc tam - "Dzēst kārtulas" un "Dzēst kārtulas atlasītajās šūnās".

Nosacījuma formatējums PivotTable pārskatā

Izmantojot PivotTable pārskatu, lietotāji var ātri apkopot lielu datu apjomu. Šūnu diapazons, Excel izklājlapa vai PivotTable pārskats - nosacījuma formatēšana būtībā visur darbojas vienādi. Tikai PivotTable pārskatā ir dažas īpašas funkcijas, kas jums jāzina:

• Daži nosacījuma formāti nedarbojas laukos rakurstabulas atskaites vērtību diapazonā.
• Ja kaut kādā veidā maināt rakurstabulas pārskata izkārtojumu, nosacījuma formatējums saglabāsies, līdz tiks noņemti pamatā esošo datu lauki.
• Nosacījuma formatējums laukiem vērtību diapazonā var būt balstīts uz datu hierarhiju. Šī nosacītā formatējuma diapazonu nosaka nākamais zemākais vai nākamais augstākais hierarhijas līmenis rindās vienai vai vairākām kolonnām vai kolonnām vienai vai vairākām rindām.
• Nosacījuma formatējuma diapazonu laukiem vērtību diapazonā var definēt, izmantojot trīs metodes:

• • atlase
  • Atbilstošais lauks
  • Vērtības lauks

Noklusējuma metode ir atlase.

Apgabala definīcija pēc atlases

Dodieties uz “Lietot formatēšanas kārtulu”.

Noklikšķiniet uz dialoglodziņa Jauns formatēšanas noteikums vai Rediģēt formatēšanas kārtulu.

Šī metode ir piemērota blakus esošai lauku virknei vērtību diapazonā, piem. B. reģiona apsekojuma dati, kā arī vērtību diapazonā esošās lauku rindas, kas nav blakus, piemēram, dažādu reģionu aptauju dati dažādos datu hierarhijas līmeņos.

Apgabala definīcija, izmantojot atbilstošu lauku

Šī metode darbojas, ja jums ir

• vēlaties izvairīties no daudzu nesaistītu vienumu atlases
• vēlaties nosacīti formatēt vairākus datu hierarhijas līmeņa vērtību diapazona laukus
• Vēlaties izslēgt daļējus rezultātus

Apgabala definīcija, izmantojot vērtību lauku

Šī metode darbojas, ja jums ir

• vēlaties izvairīties no daudzu nesaistītu vienumu atlases
• vēlaties nosacīti formatēt vairākus laukus vērtību diapazonā visiem hierarhijas līmeņiem
• Vēlaties iekļaut kopsummas un daļējus rezultātus.

Secinājums: Nosacījuma formatēšana programmā Excel atvieglo datu izsekošanu

Ja vēlaties Excel tabulā izcelt noteiktas datu vērtības krāsā, nosacījuma formatējums programmā Excel ir ideāls. Nosacījuma formatējuma piemērošanai ir divas iespējas. Vai nu izmantojiet Excel nodrošinātos noteikumus, lai krāsaini izceltu teksta saturu, skaitliskās vērtības vai datumus. Vai arī jūs pats varat definēt noteikumu ar dažādiem nosacījumiem, saskaņā ar kuriem ir jāatzīmē attiecīgās vērtības. Jūs varat viegli un efektīvi salīdzināt datus savā starpā, izmantojot datu joslas, krāsu joslas un simbolu kopas. Ja dizaina iespējas vairs nav vajadzīgas, varat tās noņemt ar dažiem klikšķiem.

Vairāk rakstu par nosacījuma formatējumu

• Kā automātiski ģenerēt bultiņas Excel sarakstos
• Parādiet negatīvās laika vērtības Excel tabulās
• Krāsu negatīvie procenti sarkani
• Īsumā atpazīstiet negatīvos procentus
• Formatējiet ciparus ar pielāgotu formātu krāsās
• Formatējiet Excel šūnas, kurām nav norādītās vērtības
• Automātiski saglabāt šūnas ar atbilstošajiem valūtas formātiem
• Atzīmējiet šūnas krāsā, izmantojot makro funkciju
• Izmantojiet nosacītu formatējumu un iezīmējiet datus, kas ir lielāki vai vienādi ar trešo lielāko šūnu
• Apgrieztās patiesības vērtības