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 :
Lūk, šīs izklājlapas diapazonam B4: F8 piemērotā noteikuma rezultāts:
Š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.
2. Izveidojiet nosacījuma formatēšanas kārtulu un atlasiet opciju Formula
3. Ievadiet formulu, kas atgriež TRUE vai FALSE.
4. Iestatiet formatēšanas opcijas un saglabājiet noteikumu.
The ISODD funkcija atgriež patiesību tikai nepāra skaitļiem, aktivizējot kārtulu:
kā sākt jaunu līniju tajā pašā šūnā
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 formuluIzceliet 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'
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:
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
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
Šī 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:
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.
Š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:
Š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:
Izmantotā formula ir šāda:
= ISNUMBER ( SEARCH ($F,B2))
Lai iegūtu sīkāku informāciju un pilnu skaidrojumu, skatiet:
- Raksts: Kā izcelt šūnas, kurās ir konkrēts teksts
- Raksts: Kā izcelt rindas, kurās ir konkrēts teksts
- Video: Kā izveidot meklēšanas lodziņu, lai izceltu datus
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 .
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:
- 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.
- Nosacījuma formatēšanas kritērijiem nevar izmantot noteiktas formulu konstrukcijas, piemēram, savienības, krustojumus vai masīva konstantes.
- 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