Excel

Kundisyon sa pag-format na may mga pormula (10 mga halimbawa)

Conditional Formatting With Formulas

Mabilis na Pagsisimula | Mga halimbawa | Pag-troubleshoot | Pagsasanay

Ang kondisyon na pag-format ay isang kamangha-manghang paraan upang mabilis na mailarawan ang data sa isang spreadsheet. Sa may kondisyon na pag-format, maaari kang gumawa ng mga bagay tulad ng mga highlight na petsa sa susunod na 30 araw, mga problema sa pagpasok ng data ng flag, i-highlight ang mga hilera na naglalaman ng mga nangungunang customer, nagpapakita ng mga duplicate, at marami pa.





Ang mga barko ng Excel na may isang malaking bilang ng mga 'preset' na ginagawang madali upang lumikha ng mga bagong panuntunan nang walang mga formula. Gayunpaman, maaari ka ring lumikha ng mga panuntunan gamit ang iyong sariling pasadyang mga formula. Sa pamamagitan ng paggamit ng iyong sariling pormula, sakupin mo ang kundisyon na nagpapalitaw ng isang panuntunan, at maaaring mailapat nang eksakto ang lohika na kailangan mo. Binibigyan ka ng mga formula ng maximum na lakas at kakayahang umangkop.

Halimbawa, gamit ang presetang 'Equal to', madaling i-highlight ang mga cell na katumbas ng 'mansanas'.





Ngunit paano kung nais mong i-highlight ang mga cell na katumbas ng 'apple' o 'kiwi' o 'lime'? Oo naman, maaari kang lumikha ng isang panuntunan para sa bawat halaga, ngunit iyan ay isang maraming problema. Sa halip, maaari mo lamang gamitin ang isang panuntunan batay sa isang pormula sa O pagpapaandar :

Isang panuntunan upang mai-highlight ang x, y, o z



Narito ang resulta ng panuntunang inilapat sa saklaw na B4: F8 sa spreadsheet na ito:

Kondisyunal na pag-format gamit ang pagpapaandar ng OR

Narito ang eksaktong ginamit na pormula:

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

Mabilis na pagsisimula

Maaari kang lumikha ng isang panuntunan na may kundisyon na panuntunan sa pag-format ng kondisyon sa apat na madaling hakbang:

1. Piliin ang mga cell na nais mong i-format.

Piliin ang mga cell upang mai-format

2. Lumikha ng isang panuntunan sa kondisyon na pag-format, at piliin ang pagpipiliang Formula

Piliin ang pagpipilian sa formula

3. Magpasok ng isang pormula na nagbabalik TUNAY o MALI.

Ipasok ang formula na may kaugnayan sa aktibong cell

4. Itakda ang mga pagpipilian sa pag-format at i-save ang panuntunan.

Itakda ang mga pagpipilian sa pag-format

Ang Pagpapaandar ng ISODD nagbabalik lamang ng TRUE para sa mga kakaibang numero, na nagpapalitaw ng panuntunan:

kung paano gumawa ng isang kung pahayag

Ang pagpapaandar ng ISODD ay nagbabalik ng TUNAY para sa mga kakaibang numero, na nagpapalitaw ng panuntunan

Video: Paano mag-apply ng kondisyong pag-format sa isang formula

Nag-aalok din kami pagsasanay sa video sa paksang ito .

Formula lohika

Ang mga pormula na naglalapat ng kondisyunal na pag-format ay dapat magbalik ng TUNAY o MALI, o mga katumbas na bilang. Narito ang ilang mga halimbawa:

paano mag-apply ng isang pasadyang format sa excel
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Ang mga pormula sa itaas ay bumalik sa TUNAY o MALI, kaya't gumagana silang perpekto bilang isang pag-trigger para sa kondisyong pag-format.

Kapag ang kondisyong pag-format ay inilapat sa isang saklaw ng mga cell, ipasok ang mga sanggunian ng cell na patungkol sa unang hilera at haligi sa pagpipilian (ibig sabihin, ang itaas na kaliwang cell). Ang trick upang maunawaan kung paano gumagana ang mga kondisyunal na pormula sa pag-format ay upang mailarawan ang parehong formula na inilalapat bawat cell sa pagpipilian , na may mga sanggunian sa cell na na-update tulad ng dati. Isipin na ipinasok mo ang formula sa itaas na kaliwang cell ng napili, at pagkatapos ay nakopya ang formula sa buong pagpipilian. Kung nakikipagpunyagi ka rito, tingnan ang seksyon sa Dummy Formula sa ibaba.

Mga Halimbawa ng Formula

Nasa ibaba ang mga halimbawa ng mga pasadyang formula na maaari mong gamitin upang mailapat ang kondisyong pag-format. Ang ilan sa mga halimbawang ito ay maaaring malikha gamit ang mga built-in na preset ng Excel para sa pag-highlight ng mga cell, ngunit ang mga pasadyang pormula ay maaaring lumampas sa mga preset, tulad ng nakikita mo sa ibaba.

Tingnan din ang: Higit sa 30 Mga Pormula ng Kondisyunal na Pag-format

I-highlight ang mga order mula sa Texas

Upang mai-highlight ang mga hilera na kumakatawan sa mga order mula sa Texas (pinaikling TX), gumamit ng isang pormula na nagkukulong sa sanggunian sa haligi F:

 
=$F5='TX'

Gumamit ng isang formula upang mai-highlight ang mga hilera kung saan estado =

Para sa karagdagang detalye, tingnan ang artikulong ito: I-highlight ang mga hilera na may kondisyon na pag-format .

Video: Paano i-highlight ang mga hilera na may kondisyong pag-format

I-highlight ang mga petsa sa susunod na 30 araw

Upang mai-highlight ang mga petsa na nagaganap sa susunod na 30 araw, kailangan namin ng isang pormula na (1) tinitiyak na ang mga petsa ay nasa hinaharap at (2) tinitiyak na ang mga petsa ay 30 araw o mas mababa pa mula ngayon. Ang isang paraan upang magawa ito ay ang paggamit ng AT pagpapaandar kasama ang NGAYONG pag-andar ganito:

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

Sa isang kasalukuyang petsa ng Agosto 18, 2016, ang mga kondisyon na pag-format ng kondaktibo na mga petsa ay sumusunod:

Kundisyon sa pag-format upang mai-highlight ang mga petsa sa susunod na 30 araw

Ang NGAYONG pag-andar ibinalik ang kasalukuyang petsa at oras. Para sa mga detalye tungkol sa kung paano gumagana ang formula na ito, tingnan ang artikulong ito: I-highlight ang mga petsa sa susunod na N araw .

I-highlight ang mga pagkakaiba sa haligi

Dahil sa dalawang haligi na naglalaman ng katulad na impormasyon, maaari mong gamitin ang kondisyon na pag-format upang makita ang mga banayad na pagkakaiba. Ang pormulang ginamit upang ma-trigger ang pag-format sa ibaba ay:

 
=$B4$C4

Kundisyon sa pag-format upang ihambing ang mga haligi

Tingnan din: isang bersyon ng pormulang ito na gumagamit ng function na EXACT upang gumawa ng isang paghahambing na sensitibo sa kaso .

I-highlight ang mga nawawalang halaga

Upang mai-highlight ang mga halaga sa isang listahan na nawawala mula sa isa pa, maaari kang gumamit ng isang formula batay sa Pag-andar ng COUNTIF :

 
= COUNTIF (list,B5)=0

I-highlight ang mga nawawalang halaga sa kondisyunal na pag-format

Ang formula na ito ay simpleng sumusuri sa bawat halaga sa Listahan A laban sa mga halaga sa pinangalanang saklaw na 'listahan' (D5: D10). Kapag ang bilang ay zero, ang formula ay magbabalik TUNAY at magti-trigger ng panuntunan, na nagha-highlight ng mga halaga sa Listahan A nawawala yan sa Listahan B .

Video: Paano makahanap ng mga nawawalang halaga sa COUNTIF

I-highlight ang mga katangian na may 3+ silid-tulugan sa ilalim ng $ 350k

Upang makahanap ng mga pag-aari sa listahang ito na mayroong hindi bababa sa 3 mga silid-tulugan ngunit mas mababa sa $ 300,000, maaari kang gumamit ng isang pormula batay sa pagpapaandar ng AND:

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

Ang mga palatandaan ng dolyar ($) ay naka-lock ang sanggunian sa mga haligi C at D, at ang AT pagpapaandar ay ginagamit upang matiyak na ang parehong mga kondisyon ay TUNAY. Sa mga hilera kung saan nagbabalik TUNAY ang pagpapaandar ng AND, inilalapat ang kondisyunal na pag-format:

Kundisyon na pag-format upang mai-highlight ang mga listahan ng pag-aari

I-highlight ang mga nangungunang halaga (pabago-bagong halimbawa)

Bagaman may mga preset ang Excel para sa 'nangungunang mga halaga', ipinapakita ng halimbawang ito kung paano gawin ang parehong bagay sa isang pormula, at kung paano maaaring maging mas may kakayahang umangkop ang mga formula. Sa pamamagitan ng paggamit ng isang formula, maaari naming gawing interactive ang worksheet - kapag na-update ang halaga sa F2, agad na tumutugon ang panuntunan at nai-highlight ang mga bagong halaga.

Dynamic na kondisyon na pag-format para sa mga nangungunang halaga

Ang formula na ginamit para sa panuntunang ito ay:

kung paano magbukod ng mga duplicate sa excel
 
=B4>= LARGE (data,input)

Kung saan ang 'data' ay pinangalanang saklaw B4: G11, at 'input' ang pinangalanang saklaw F2. Ang pahinang ito ay mayroon mga detalye at isang buong paliwanag .

Mga tsart ng Gantt

Maniwala ka man o hindi, maaari mo ring gamitin ang mga formula upang lumikha ng mga simpleng tsart ng Gantt na may kondisyon na pag-format tulad nito:

Paggamit ng kondisyong pag-format upang lumikha ng isang tsart ng Gantt

Gumagamit ang worksheet na ito ng dalawang mga panuntunan, isa para sa mga bar, at isa para sa shade ng katapusan ng linggo:

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

Ipinapaliwanag ng artikulong ito ang formula para sa mga bar , at ipinapaliwanag ng artikulong ito ang formula para sa pag-shade ng katapusan ng linggo .

Simpleng kahon para sa paghahanap

Ang isang cool na trick na magagawa mo sa may kondisyon na pag-format ay ang pagbuo ng isang simpleng box para sa paghahanap. Sa halimbawang ito, ang isang panuntunan ay nagha-highlight ng mga cell sa haligi B na naglalaman ng teksto na na-type sa cell F2:

Kahon sa paghahanap na may kondisyon na pag-format

Ang ginamit na pormula ay:

 
= ISNUMBER ( SEARCH ($F,B2))

Para sa higit pang mga detalye at isang buong paliwanag, tingnan ang:

Pag-troubleshoot

Kung hindi mo maalis sa tama ang iyong mga panuntunan sa kondisyon na pag-format, malamang na may problema sa iyong pormula. Una, tiyaking sinimulan mo ang formula na may katumbas na pag-sign (=). Kung nakalimutan mo ang hakbang na ito, tahimik na i-convert ng Excel ang iyong buong formula sa teksto, ginagawa itong walang silbi. Upang ayusin, alisin lamang ang mga dobleng quote na idinagdag ng Excel sa magkabilang panig at tiyakin na ang formula ay nagsisimula sa katumbas (=).

Kung ang iyong pormula ay naipasok nang tama, ngunit hindi nagpapalitaw ng panuntunan, maaaring humukay ka ng kaunti pa. Normal lang, maaari mong gamitin ang F9 key upang suriin ang mga resulta sa isang formula, o gamitin ang tampok na Suriin upang lumakad sa isang pormula. Sa kasamaang palad, hindi mo magagamit ang mga tool na ito na may kondisyon na mga pormula sa pag-format, ngunit maaari mong gamitin ang isang diskarteng tinatawag na 'dummy formulas'.

Dummy Formula

Ang mga form na dummy ay isang paraan upang subukan ang iyong mga kondisyon na pormula sa pag-format nang direkta sa worksheet, upang makita mo kung ano talaga ang ginagawa nila. Maaari itong maging isang malaking tagatipid ng oras kapag nakikipaglaban ka upang makakuha ng wastong paggana ng mga sanggunian sa cell.

Sa madaling sabi, inilalagay mo ang parehong formula sa isang saklaw ng mga cell na tumutugma sa hugis ng iyong data. Hayaan mong makita mo ang mga halagang ibinalik ng bawat formula, at mahusay na paraan upang mailarawan at maunawaan kung paano gumagana ang kondisyunal na batay sa pormula na gumagana. Para sa isang detalyadong paliwanag, tingnan ang artikulong ito .

Gumamit ng mga dummy formula upang suriin ang mga kondisyon na pormula sa pag-format

Video: Subukan ang kondisyon na pag-format sa mga dummy formula

Mga limitasyon

Mayroong ilang mga limitasyon na kasama ng kondisyunal na pag-format na batay sa formula:

  1. Hindi ka maaaring maglapat ng mga icon, kaliskis ng kulay, o mga data bar na may pasadyang formula. Limitado ka sa karaniwang pag-format ng cell, kasama ang mga format ng numero, font, punan ang kulay, at mga pagpipilian sa hangganan.
  2. Hindi ka makakagamit ng ilang partikular na konstruksyon ng formula tulad ng mga unyon, interseksyon, o mga parating na pamantayan para sa mga pamantayan sa pag-format ng kondisyon.
  3. Hindi mo ma-refer ang iba pang mga workbook sa isang kondisyon na pormula sa pag-format.

Maaari kang magtrabaho minsan sa paligid ng # 2 at # 3. Maaari mong ilipat ang lohika ng pormula sa isang cell sa worksheet, pagkatapos ay sumangguni sa cell na iyon sa pormula. Kung sinusubukan mong gumamit ng isang pare-pareho na array, subukang lumikha ng isang pinangalanang saklaw sa halip.

Mas maraming mapagkukunan ng formula ng CF

  • Higit sa 30 mga halimbawang halimbawa ng pormula ng pag-format
  • Pagsasanay sa video kasama ang mga worksheet ng pagsasanay
May-akda na si Dave Bruns


^