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

Ja kādreiz esat izmantojis nosacīto formatējumu ar savu formulu, jūs zināt, ka visgrūtāk ir pārliecināties, ka formula patiešām darbojas.

Problēma ir tāda, ka formulas apgabals nosacījuma formatēšanas noteikumā nav ļoti draudzīgs. Jūs nesaņemat izceltas šūnu atsauces, jūs nesaņemat funkciju automātiskā pabeigšana ... heck .... jūs pat nesaņemat ekrāna padomus.

Tā rezultātā ir grūti “redzēt”, vai formula darbosies tikai pēc kārtulas saglabāšanas. Ja tā nav, jums jāizmanto izmēģinājumi un kļūdas:

1. Rediģējiet kārtulu
2. Rediģējiet formulu, izmantojot savu labāko minējumu
3. Saglabājiet noteikumu, lai redzētu, kas notiek
4. Atkārtojiet pēc vajadzības

Tas nav īpaši jautri, un tas var būt patiešām nomākts, ja rodas sarežģīta problēma.

Par laimi, ir vienkāršs labojums: fiktīvas formulas.

Labāks veids - pārbaudiet ar fiktīvām formulām

Izmantojot sarežģītākas nosacījuma formatēšanas formulas, galvenais ir pirms kārtulas izveidošanas pārbaudīt kārtulu ar “fiktīvajām” formulām. Sākumā tas var šķist neiespējami - kā jūs varat pārbaudīt nosacījuma formatēšanas formulu, nepiemērojot nosacījuma formātu?

Triks ir saprast, ka nosacīto formatējumu var uzskatīt par neredzamu formulu “pārklājumu”, kas atrodas šūnu augšpusē. Kad pārklājuma formula konkrētai šūnai atgriež TRUE, tiek lietots formatējums.

Tātad, lai pārbaudītu nosacījuma formatēšanas kārtulu, jums vienkārši jāizveido “fiktīvo” formulu kopa darblapā, kas simulē pārklājumu.

kā skaitīt Excel rakstzīmju skaitu

Man patīk testa formulas novietot datu malā, ierindojot tās rindās. Tas atvieglo atsauču iestatīšanu un saskaņošanu.

Pēc tam vienkārši uzrakstiet pirmo formulu, atsaucoties uz datu augšējo kreiso šūnu. Šī būs aktīvā šūna, kad tiks izveidots nosacījuma formatēšanas noteikums.

1. piemērs - vienkārša formula

Piemēram, pieņemsim, ka tabulā ir skaitļi un vēlaties izcelt vērtības virs 100.

Piezīme. Programmā Excel ir nosacījuma formatējums “iepriekš iestatīts”, kas izceļ vērtības “lielākas par”, tāpēc nav nepieciešams izmantot formulu, lai to izdarītu. Mēs kā piemēru izmantojam tikai pamata formulu.

Mums ir daudz vietas pa labi, tāpēc mēs pievienosim savas fiktīvās formulas. Šūnā H4 pievienojiet pirmo formulu. Šajā gadījumā mēs vēlamies izmantot:

 
=B4>100

Kāpēc B4? Tā kā B4 atbilst aktīvajai šūnai, kas mums būs, definējot faktisko nosacījuma formatēšanas noteikumu.

Tagad kopējiet formulu uz leju un uz leju. Jums ir nepieciešams nokopēt tik daudz rindu, cik vēlaties pārbaudīt. Šajā gadījumā, izmantojot nelielu datu kopu, mēs varam viegli pārbaudīt visas rindas.

Ņemiet vērā, ka katrā šūnā mēs iegūstam TRUE vai FALSE vērtību. Ja mēs pārbaudām dažas atsauces, jūs varat redzēt, ka katra formula novērtē datu šūnu, salīdzinot ar B4. Visas atsauces uz B4 ir mainījušās, jo B4 tika ievadīts kā relatīvā adrese.

Atsauču pārbaude - katra formula attiecas uz šūnu attiecībā pret B4

Tagad vienkārši iedomājieties, ka šie rezultāti tiek transponēti tieši virs datiem. Visur, kur redzat patiesu vērtību, tiks lietots nosacījuma formatējums:

Ņemiet vērā, ka TRUE vērtības datos pareizi atzīmē vērtības> 100 (manuāli iezīmētas)

kā ēnot katru otro rindu Excel 2016

Manekena formula izskatās labi, tāpēc izmēģināsim to nosacījuma formatēšanas noteikumā.

Vispirms nokopējiet formulu fiktīvo formulu augšējā kreisajā šūnā - šajā gadījumā tas ir H4.

Pēc tam atlasiet datus un definējiet jaunu nosacījuma formatēšanas kārtulu.

Dati atlasīti - ņemiet vērā, ka aktīvā šūna ir B4

Ielīmējiet formulu lodziņā un iestatiet formātu.

Gatavs saglabāt jauno kārtulu

Veiksmi! Visas šūnas ar vērtībām virs 100 ir izceltas:

Galīgā nosacītā formatēšana tiek izmantota, izmantojot formulu, noņemot fiktīvas formulas.

2. piemērs - sarežģītāka formula

Tas bija vienkāršs piemērs, tāpēc izmēģināsim to pašu pieeju ar sarežģītāku formulu.

Izveidosim kārtulu, kas izceļ tabulas rindas, pamatojoties uz vienas slejas vērtību. Šajā gadījumā mēs uzsvērsim uzdevumus ar prioritāti “A”.

Jāizceļ visas rindas ar prioritāti “A”

Tā ir klasiska problēma nosacījuma formatēšanā. Formulai būs nepieciešama jaukta atsauce, bet jauktās atsauces var būt grūti saprotamas, ja darblapā neredzat atsauces. Tomēr, izmantojot fiktīvas formulas, mēs varam viegli pārbaudīt un pilnveidot noteikumu.

Tāpat kā iepriekš, pirmais solis ir izdomāt, kur likt testa formulas. Mums ir daudz vietas pa labi, tāpēc mēs sāksim šūnā G5.

Tā kā mēs vēlamies izcelt uzdevumus ar prioritāti “A”, mēs izmantosim šo formulu, lai sāktu:

 
=B5='A'

Kad esmu nokopējis formulas uz leju un uz leju, mums ir šāds:

Nestrādās - tiks iezīmētas tikai vērtības B slejā (manuāli tiek lietots oranžs ēnojums)

Ņemiet vērā, ka mēs iegūstam rezultātu TRUE, kur prioritāte ir “A”, bet tikai B slejas vērtībām. Tas ir labs sākums, taču tas izceļ tikai pirmās slejas šūnas.

Mums ir jāpielāgo formula, lai tā atdotu TRUE visai rindai. Lai to izdarītu, mums ir jāizmanto jaukta atsauce formulā, lai bloķētu kolonnu. Pārskatītā formula ir šāda:

 
=$B5='A'

Kopējot šo jauno formulu visā mūsu testa diapazonā, mēs iegūstam to, kas mums nepieciešams:

Kad sleja ir bloķēta, mēs iegūstam veselu rindu TRUE, ja prioritāte ir “A” (manuāli tiek lietots oranžs ēnojums)

Redziet, kā darbosies fiktīvās formulas? Iedomājieties tos kā pašu datu pārklājumu.

Tagad izveidosim nosacījuma formatēšanas noteikumu. Vispirms izvēlieties datus:

Dati ir atlasīti un gatavi izveidot jaunu kārtulu (piezīme aktīvā šūna ir B5)

kā to izdarīt izvēles rūtiņas

Visbeidzot, izveidosim kārtulu, izmantojot formulu augšējā kreisajā stūrī:

Formula ielīmēta no G5

Kā redzat, jaunais noteikums pirmo reizi darbojas perfekti.

Nosacījuma formatējums darbojas kā paredzēts (fiktīvās formulas ir noņemtas)

Secinājums

Nākamreiz, kad jums būs jāpielieto nosacījuma formatējums ar sarežģītāku formulu, blakus datiem iestatiet fiktīvas formulas un precīzi noregulējiet formulu, līdz iegūstat PATIESAS vērtības tur, kur tās ir vajadzīgas. Strādājot tieši pie darblapas, jums ir pilnīga piekļuve visiem Excel formulu rīkiem, un jūs varat viegli novērst problēmu un pielāgot formulu, līdz tā darbojas perfekti.

Pārbaudiet CF noteikumu piemēru1.xlsx

Pārbaudiet CF noteikumu piemēru2.xlsx