Excela

Testați formatarea condiționată cu formule false

Test Conditional Formatting With Dummy Formulas

Pornire rapidă | Exemple | Depanare | Instruire

Dacă ați aplicat vreodată formatarea condiționată cu propria formulă, știți că cel mai greu este să vă asigurați că formula funcționează efectiv.





Problema este că zona formulelor dintr-o regulă de formatare condiționată nu este foarte prietenoasă. Nu primiți referințe celulare evidențiate, nu primiți completarea automată a funcției ... naiba .... nici măcar nu primiți sfaturi despre ecran.

Prin urmare, este greu să „vezi” dacă o formulă va funcționa până după salvarea regulii. În caz contrar, trebuie să utilizați încercări și erori:





  1. Editați regula
  2. Editați formula folosind „cea mai bună presupunere”
  3. Salvați regula pentru a vedea ce se întâmplă
  4. Repetați după cum este necesar

Acest lucru nu este foarte distractiv și poate fi foarte frustrant atunci când întâmpinați o problemă dificilă.

Din fericire, există o soluție ușoară: formule fictive.



O modalitate mai bună - testați cu formule fictive

Cu formule de formatare condiționate mai complicate, cheia este să testați mai întâi regula cu formule „fictive”, înainte de a crea regula. Acest lucru poate părea la început imposibil - cum puteți testa o formulă de formatare condițională fără a aplica un format condițional?

Trucul este înțelegerea faptului că vă puteți gândi la formatarea condiționată ca la o „suprapunere” a formulelor invizibile care stau deasupra celulelor. Când o formulă din suprapunere returnează TRUE pentru o celulă dată, se aplică formatarea.

Deci, pentru a testa o regulă de formatare condiționată, trebuie doar să construiți un set de formule „fictive” pe foaia de lucru care simulează suprapunerea.

cum tratează excel criteriile introduse pe același rând?

Îmi place să pun formulele de test pe partea datelor, aliniate cu rândurile. Acest lucru facilitează configurarea și potrivirea referințelor.

Apoi, pur și simplu scrieți prima formulă făcând referire la celula din stânga sus în date. Aceasta va fi celula activă atunci când este creată regula de formatare condițională.

Video: Testați formatarea condiționată cu formule false

Exemplul 1 - Formula simplă

De exemplu, spuneți că aveți numere într-un tabel și doriți să evidențiați valori peste 100.

Notă: Excel conține o „presetare” de formatare condiționată care va evidenția valorile „mai mari decât”, deci nu este necesar să utilizați o formulă pentru a face acest lucru. Folosim doar o formulă de bază ca exemplu.

Problemă - evidențiați valori peste 100 cu o regulă de formatare condiționată

Avem o mulțime de spațiu în dreapta, așa că vom adăuga formulele noastre false. În celula H4, adăugați prima formulă. În acest caz, dorim să folosim:

 
=B4>100

De ce B4? Deoarece B4 corespunde celulei active pe care o vom avea atunci când definim regula reală de formatare condițională.

Acum copiați formula peste și în jos. Trebuie doar să copiați câte rânduri doriți să testați. În acest caz, cu un set mic de date, putem testa cu ușurință toate rândurile.

Copiați formulele peste și în jos

Observați că obținem o valoare ADEVĂRATĂ sau FALSĂ în fiecare celulă. Dacă verificăm câteva referințe, puteți vedea că fiecare formulă evaluează o celulă din date, relativ la B4. Toate referințele la B4 s-au schimbat, deoarece B4 a fost introdus ca o adresă relativă.

Verificarea referințelor formulelor

Verificarea referințelor - fiecare formulă se referă la o celulă față de B4

Acum imaginați-vă pur și simplu aceste rezultate transpuse direct deasupra datelor. Oriunde vedeți o valoare ADEVĂRATĂ, va fi aplicată formatarea condițională:

Formulele fictive arată ADEVĂRAT unde se va aplica formatarea
Observați că valorile ADEVĂRATE marchează corect valorile> 100 din date (evidențiate manual)

Cum introduceți un subsol în Excel

Formula falsă arată bine, așa că hai să o încercăm într-o regulă de formatare condiționată.

Mai întâi, copiați formula în celula din stânga sus a formulelor fictive - acesta este H4 în acest caz.

Copiați prima formulă din setul fictiv

Apoi, selectați datele și definiți o nouă regulă de formatare condițională.

Selectați datele și începeți o nouă regulă de formatare condițională

Date selectate - rețineți că celula activă este B4

Lipiți formula în casetă și setați formatul.

Formula falsă lipită, regulă gata de salvare

Gata pentru salvarea noii reguli

Succes! Toate celulele cu valori peste 100 evidențiate:

Formatul condițional final, cu formule false eliminate

Formatarea condițională finală aplicată cu o formulă, cu formulele inexacte eliminate.

Exemplul 2 - o formulă mai complicată

Acesta a fost un exemplu simplu, deci să încercăm aceeași abordare cu o formulă mai complicată.

Să creăm o regulă care evidențiază rândurile dintr-un tabel pe baza valorii dintr-o coloană. În acest caz, vom evidenția sarcinile cu prioritatea „A”.

Problemă - evidențiați sarcinile cu prioritate de

Trebuie să evidențiați toate rândurile cu prioritatea „A”

Aceasta este o problemă clasică în formatarea condiționată. Formula va necesita o referință mixtă, dar referințele mixte pot fi greu de înțeles atunci când nu puteți vedea referințe pe foaia de lucru. Cu toate acestea, folosind formule fictive, putem testa și perfecționa cu ușurință o regulă.

La fel ca înainte, primul pas este să ne dăm seama unde să punem formulele de testare. Avem suficient spațiu în dreapta, așa că vom începe în celula G5.

Întrucât dorim să evidențiem sarcinile cu prioritatea „A”, vom folosi această formulă pentru a începe:

 
=B5='A'

După ce copiez formulele peste și în jos, iată ce avem:

Formule fictive - prima încercare

Nu va merge - doar valorile din coloana B vor fi evidențiate (umbrire portocalie aplicată manual)

Observați că obținem un rezultat al TRUE, unde prioritatea este „A”, dar numai pentru valorile din coloana B. Este un început bun, dar va evidenția doar celulele din prima coloană.

Trebuie să ajustăm formula astfel încât să revină ADEVĂRAT pentru întregul rând. Pentru a face acest lucru, trebuie să folosim o referință mixtă în formulă pentru a bloca coloana. Formula revizuită este:

 
=$B5='A'

Când copiez această nouă formulă în gama noastră de testare, obținem ceea ce avem nevoie:

Formule fictive - a doua încercare - funcționează!

Cu coloana blocată, obținem un rând întreg de TRUE atunci când prioritatea este „A” (umbrire portocalie aplicată manual)

Vedeți cum vor funcționa formulele fictive? Imaginați-le ca pe o suprapunere a datelor în sine.

Acum să creăm regula de formatare condiționată. Mai întâi, selectați datele:

Date selectate - nota că celula activă este B5

Date selectate și gata să creeze o nouă regulă (rețineți că celula activă este B5)

introduceți lista derulantă în Excel

În cele din urmă, să creăm regula, folosind formula din stânga sus:

Formula lipită, o nouă regulă gata de salvare

Formula lipită din G5

După cum puteți vedea, noua regulă funcționează perfect prima dată.

Format final - rânduri evidențiate, formule fictive eliminate

Formatarea condiționată funcționează așa cum era de așteptat (formulele fictive eliminate)

Concluzie

Data viitoare când trebuie să aplicați formatarea condiționată cu o formulă mai complicată, configurați formule fictive lângă date și reglați fin formula până când obțineți valori ADEVĂRATE acolo unde aveți nevoie de ele. Lucrând direct pe foaia de lucru, aveți acces complet la toate instrumentele de formulă Excel și puteți depana cu ușurință și regla formula până când funcționează perfect.

Vedeți aici mai multe formule condiționate de formatare Autor Dave Bruns Atașamente Fişier Exemplu de regulă de testare CF 1.xlsx Fişier Testați exemplul regulii CF2.xlsx


^