Excel

Nosacījuma formatēšana ar formulām (10 piemēri)

Conditional Formatting With Formulas

Ātrā sākums | Piemēri | Traucējummeklēšana | Apmācība

Nosacījuma formatēšana ir fantastisks veids, kā ātri vizualizēt datus izklājlapā. Izmantojot nosacīto formatējumu, varat veikt tādas darbības kā izcelšanas datumi nākamajās 30 dienās, atzīmēt datu ievades problēmas, izcelt rindas, kurās ir populārākie klienti, parādīt dublikātus un daudz ko citu.





Programma Excel tiek piegādāta ar lielu skaitu sākotnējo iestatījumu, kas atvieglo jaunu noteikumu izveidi bez formulām. Tomēr jūs varat arī izveidot kārtulas ar savām pielāgotajām formulām. Izmantojot savu formulu, jūs pārņemat nosacījumu, kas aktivizē noteikumu, un varat izmantot tieši to loģiku, kas jums nepieciešama. Formulas nodrošina maksimālu jaudu un elastību.

Piemēram, izmantojot iestatījumu “Vienāds ar”, ir viegli izcelt šūnas, kas vienādas ar “ābolu”.





Bet ko tad, ja vēlaties izcelt šūnas, kas vienādas ar “ābolu”, “kivi” vai “laimu”? Protams, katrai vērtībai varat izveidot noteikumu, taču tas rada daudz nepatikšanas. Tā vietā varat vienkārši izmantot vienu noteikumu, kura pamatā ir formula ar VAI funkcija :

Noteikums, lai izceltu x, y vai z



Lūk, šīs izklājlapas diapazonam B4: F8 piemērotā noteikuma rezultāts:

Nosacījuma formatēšana ar funkciju VAI

Šeit ir izmantota precīza formula:

 
= OR (B4='apple',B4='kiwi',B4='lime')

Ātrs sākums

Uz formulas balstītu nosacījuma formatēšanas kārtulu var izveidot četrās vienkāršās darbībās:

1. Atlasiet šūnas, kuras vēlaties formatēt.

Atlasiet formatējamās šūnas

2. Izveidojiet nosacījuma formatēšanas kārtulu un atlasiet opciju Formula

Atlasiet formulas opciju

3. Ievadiet formulu, kas atgriež TRUE vai FALSE.

Ievadiet formulu attiecībā pret aktīvo šūnu

4. Iestatiet formatēšanas opcijas un saglabājiet noteikumu.

Iestatiet formatēšanas opcijas

The ISODD funkcija atgriež patiesību tikai nepāra skaitļiem, aktivizējot kārtulu:

kā sākt jaunu līniju tajā pašā šūnā

Funkcija ISODD atgriež nepāra skaitļiem TRUE, aktivizējot kārtulu

Video: Kā pielietot nosacījuma formatējumu ar formulu

Mēs arī piedāvājam video apmācība par šo tēmu .

Formulas loģika

Formulām, kurās tiek izmantots nosacījuma formatējums, ir jāatgriež TRUE vai FALSE vai skaitļu ekvivalenti. Šeit ir daži piemēri:

teksta vērtību excel sauc arī par a
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Visas iepriekš minētās formulas atgriež TRUE vai FALSE, tāpēc tās lieliski darbojas kā nosacījuma formatēšanas aktivizētājs.

Ja nosacījumu formatējums tiek lietots šūnu diapazonam, ievadiet šūnu atsauces attiecībā uz atlases pirmo rindu un kolonnu (t.i., augšējo kreiso šūnu). Triks, lai saprastu, kā darbojas nosacījuma formatēšanas formulas, ir vizualizēt to pašu formulu, kurai tiek piemērota katra atlasītā šūna , šūnu atsauces tiek atjauninātas kā parasti. Iedomājieties, ka ievadījāt formulu atlases augšējā kreisajā šūnā un pēc tam kopējāt formulu visā atlasē. Ja jūs ar to cīnāties, skatiet sadaļu par Manekena formulas zemāk.

Formulu piemēri

Tālāk ir sniegti pielāgotu formulu piemēri, kurus varat izmantot, lai lietotu nosacījuma formatējumu. Dažus no šiem piemēriem var izveidot, izmantojot Excel iebūvētos priekšiestatījumus šūnu izcelšanai, taču pielāgotas formulas var pārsniegt iepriekš iestatītās vērtības, kā redzams zemāk.

Skatiet arī: Vairāk nekā 30 nosacītu formatēšanas formulu

Izceliet pasūtījumus no Teksasas

Lai iezīmētu rindas, kas attēlo pasūtījumus no Teksasas (saīsināti TX), izmantojiet formulu, kas bloķē atsauci uz F sleju:

 
=$F5='TX'

Izmantojiet formulu, lai iezīmētu rindas, kurās valsts =

Lai iegūtu sīkāku informāciju, skatiet šo rakstu: Iezīmējiet rindas ar nosacījuma formatējumu .

Video: Kā izcelt rindas ar nosacījuma formatējumu

Iezīmējiet nākamo 30 dienu datumus

Lai izceltu datumus, kas notiek nākamajās 30 dienās, mums ir nepieciešama formula, kas (1) nodrošina, ka datumi ir nākotnē, un (2) nodrošina, ka datumi ir 30 dienas vai mazāk no šodienas. Viens veids, kā to izdarīt, ir izmantot UN funkcija kopā ar Funkcija NOW kā šis:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

Pašreizējais datums ir 2016. gada 18. augusts, nosacījuma formatējums izceļ šādus datumus:

Nosacījuma formatējums, lai izceltu nākamo 30 dienu datumus

The Funkcija NOW atgriež pašreizējo datumu un laiku. Sīkāku informāciju par šīs formulas darbību skatiet šajā rakstā: Iezīmējiet nākamo N dienu datumus .

Iezīmējiet kolonnu atšķirības

Ņemot vērā divas kolonnas, kurās ir līdzīga informācija, varat izmantot nosacītu formatējumu, lai pamanītu smalkas atšķirības. Formulējuma aktivizēšanai izmantotā formula ir šāda:

 
=$B4$C4

Nosacījuma formatējums kolonnu salīdzināšanai

Skatīt arī: šīs formulas versija, kurā tiek izmantota funkcija EXACT, lai salīdzinātu reģistrus un reģistrus .

Iezīmējiet trūkstošās vērtības

Lai izceltu vienā sarakstā trūkstošās vērtības, varat izmantot formulu, kuras pamatā ir Funkcija COUNTIF :

 
= COUNTIF (list,B5)=0

Iezīmējiet trūkstošās vērtības ar nosacījuma formatējumu

Šī formula vienkārši pārbauda katru vērtību A saraksts pret vērtībām nosauktajā diapazonā “saraksts” (D5: D10). Ja skaitlis ir nulle, formula atgriež vērtību TRUE un aktivizē kārtulu, kurā tiek izceltas vērtības A saraksts kas trūkst no B saraksts .

Video: Kā atrast trūkstošās vērtības, izmantojot COUNTIF

Izceliet īpašumus ar vairāk nekā 3 guļamistabām zem 350 000 USD

Lai šajā sarakstā atrastu īpašumus, kuros ir vismaz 3 guļamistabas, bet kuru vērtība ir mazāka par 300 000 USD, varat izmantot formulu, kuras pamatā ir funkcija AND:

 
= AND ($C5<350000,$D5>=3)

Dolāra zīmes ($) bloķē atsauci uz kolonnām C un D, ​​un UN funkcija tiek izmantots, lai pārliecinātos, ka abi nosacījumi ir PATIESI. Rindās, kurās funkcija AND atgriež vērtību TRUE, tiek lietots nosacījuma formatējums:

Nosacījuma formatējums, lai izceltu īpašumu sarakstus

Izcelt galvenās vērtības (dinamisks piemērs)

Lai gan programmā Excel ir iestatīti augstākās vērtības, šajā piemērā ir parādīts, kā to pašu darīt ar formulu un kā formulas var būt elastīgākas. Izmantojot formulu, mēs varam padarīt darblapu interaktīvu - kad vērtība F2 tiek atjaunināta, noteikums uzreiz reaģē un izceļ jaunas vērtības.

Dinamiska nosacījumu formatēšana augstākajām vērtībām

Šim noteikumam izmantotā formula ir šāda:

kā rakstīt makro programmā Excel 2007
 
=B4>= LARGE (data,input)

Kur “dati” ir nosauktais diapazons B4: G11, un “ievade” ir nosauktais diapazons F2. Šajā lapā ir detaļas un pilns skaidrojums .

Ganta diagrammas

Ticiet vai nē, jūs pat varat izmantot formulas, lai izveidotu vienkāršas Ganta diagrammas ar nosacītu formatējumu:

Nosacījuma formatējuma izmantošana, lai izveidotu Ganta diagrammu

Šajā darblapā tiek izmantoti divi noteikumi, viens joslām un otrs nedēļas nogales ēnojumam:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

Šajā rakstā ir izskaidrota stieņu formula , un šajā rakstā ir izskaidrota nedēļas nogales ēnošanas formula .

Vienkāršs meklēšanas lodziņš

Viens foršs triks, ko varat darīt ar nosacījuma formatējumu, ir vienkārša meklēšanas lodziņa izveide. Šajā piemērā kārtula izceļ šūnas B slejā, kurās ir šūnā F2 ierakstīts teksts:

Nosacījuma formatēšanas meklēšanas lodziņš

Izmantotā formula ir šāda:

 
= ISNUMBER ( SEARCH ($F,B2))

Lai iegūtu sīkāku informāciju un pilnu skaidrojumu, skatiet:

Traucējummeklēšana

Ja nevarat pareizi aktivizēt nosacījuma formatēšanas noteikumus, visticamāk, ir problēma ar formulu. Vispirms pārliecinieties, vai formulu sākāt ar vienādības zīmi (=). Ja aizmirstat šo darbību, programma Excel klusībā pārvērš visu formulu tekstā, padarot to bezjēdzīgu. Lai to labotu, vienkārši noņemiet Excel pēdiņas, kas pievienotas abās pusēs, un pārliecinieties, vai formula sākas ar vienādu (=).

Ja jūsu formula ir ievadīta pareizi, taču tā neaktivizē kārtulu, iespējams, jums vajadzēs padziļināties. Normāli, varat izmantot taustiņu F9, lai pārbaudītu rezultātus formulā, vai izmantot funkciju Novērtēt, lai izietu cauri formulai. Diemžēl jūs nevarat izmantot šos rīkus ar nosacījuma formatēšanas formulām, bet varat izmantot tehniku, ko sauc par “fiktīvajām formulām”.

Manekena formulas

Fiktīvās formulas ir veids, kā pārbaudīt nosacījuma formatēšanas formulas tieši darblapā, lai jūs varētu redzēt, ko viņi faktiski dara. Tas var ievērojami ietaupīt laiku, ja cenšaties panākt, lai šūnu atsauces darbotos pareizi.

Īsumā, jūs ievadāt to pašu formulu šūnu diapazonā, kas atbilst jūsu datu formai. Tādējādi redzēsim katras formulas atdotās vērtības, un tas ir lielisks veids, kā vizualizēt un saprast, kā darbojas uz formulām balstīta nosacītā formatēšana. Lai iegūtu detalizētu skaidrojumu, skatiet šo rakstu .

Izmantojiet fiktīvas formulas, lai pārbaudītu nosacījuma formatēšanas formulas

Video: Pārbaudiet nosacījuma formatējumu ar fiktīvām formulām

Ierobežojumi

Ir daži ierobežojumi, kas saistīti ar formulu balstītu nosacītu formatējumu:

  1. Jūs nevarat lietot ikonas, krāsu skalas vai datu joslas ar pielāgotu formulu. Jūs varat izmantot tikai standarta šūnu formatējumu, ieskaitot skaitļu formātus, fontu, aizpildījuma krāsu un apmales opcijas.
  2. Nosacījuma formatēšanas kritērijiem nevar izmantot noteiktas formulu konstrukcijas, piemēram, savienības, krustojumus vai masīva konstantes.
  3. Jūs nevarat atsaukties uz citām darbgrāmatām nosacījuma formatēšanas formulā.

Dažreiz jūs varat strādāt ap 2. un 3. vietu. Iespējams, varēsit pārvietot formulas loģiku darblapas šūnā un pēc tam atsaukties uz šo šūnu formulā. Ja mēģināt izmantot masīva konstanti, mēģiniet izveidot nosauktu diapazonu.

Vairāk CF formulu resursu

  • Vairāk nekā 30 nosacījuma formatēšanas formulu piemēri
  • Video apmācība ar prakses darblapām
Autors Deivs Bruns


^