Amafomu Okufometha Ngemibandela ye-Excel

Ukwengeza ukufometha okunemibandela ku-Excel kukuvumela ukuba usebenzise okukhethwa kukho kokufometha okuhlukile kuseli noma ububanzi bamaseli ahlangabezana nezimo ezithile ozibekayo.

Izinketho zokufometha zisetshenziswa kuphela uma amaseli akhethiwe ehlangabezana nalezi zimo ezibekiwe.

Izinketho zokufomatha ezingasetshenziswa zifaka izinguquko zombala wefonti nesimo sangemuva, izitayela zefonti, imingcele yamaseli, nokwengeza ukufometha kwenombolo yedatha.

Kusukela ku-Excel 2007, i-Excel inezinketho eziningi ezakhelwe ngaphakathi kwezimo ezisetshenziselwa ngokuvamile ezifana nokuthola izinombolo ezinkulu kunazo zonke noma ezincane kunenani elithile noma ukuthola izinombolo ezingenhla noma ngezansi kwezindleko ezijwayelekile .

Ngaphezu kwalokhu okukhethwa kukho ngaphambilini, kungenzeka futhi ukudala imithetho yokufometha enemibandela esebenzayo usebenzisa amafomu e-Excel ukuhlola izimo ezichazwe ngumsebenzisi.

Ukusebenzisa imithetho eminingi

Ukubusa okungaphezu kweyodwa kungasetshenziswa kudatha efanayo ukuhlola izimo ezahlukene. Isibonelo, idatha yesabelomali ingahle ibe nemibandela ebekiwe efaka izinguquko zokufometha lapho amazinga athile - njengama-50%, 75%, kanye no-100% - isabelomali esiphelele esetshenziswa.

Ezimweni ezinjalo, i-Excel kuqala inquma uma imithetho ehlukene ingqubuzana, futhi, uma kunjalo, lolu hlelo lulandela umyalo ohleliwe wokubheka ukuthi yimiphi imithetho yokufometha enemibandela esetshenziselwa idatha.

Isibonelo: Ukuthola Idatha engaphezu kwe-25% no-50% ikhula ngokufometha okunemibandela

Esikhathini esilandelayo, imithetho emibili yokufometha yenkambiso yenkambiso izosetshenziselwa ububanzi bamaseli B2 kuya ku-B5.

• umthetho wokuqala uhlola ukuthi ngabe idatha kumaseli A2 kuya ku-A5 mkhulu kunenani elihambelanayo ku-B2 kuya ku-B5 ngaphezulu kuka-25%.
• umthetho wesibili uhlola ukuthi ngabe idatha efanayo ku-A2: A5 idlula inani elihambelanayo ku-B2: B5 ngama-50%.

Njengoba kungabonakala esithombeni esingenhla, uma ngabe ezinye zezimiso ezingenhla ziyiqiniso, umbala wangemuva weseli noma amaseli ebangeni B1: B4 uzashintsha.

• ukuze uthole idatha lapho umehluko omkhulu kunama-25%, umbala wesizinda sangemuva uzotjhuguluka ube luhlaza okwesibhakabhaka.
• uma umehluko ukhulu kunama-50% umbala wesizinda sangasese uzoguquka ube obomvu.

Imithetho esetshenziselwa ukufeza lo msebenzi,

izofakwa ngokusebenzisa ibhokisi lokubumbana lokufometha le- New Formatting Rule .

Ukufaka idatha yokufundisa

1. Faka idatha kumaseli A1 kuya ku-C5 njengoba kuboniswa esithombeni ngenhla

Qaphela: Isinyathelo sesi-3 sesifundo sizofaka amafomula kumaseli e-C2: C4 abonisa umehluko wephesenti ngqo phakathi kwamagugu kuma-cell A2: A5 no-B2: B5 ukuze uhlole ukuchithwa kwemithetho yokufometha enemibandela.

Ukubeka Imithetho Yokufometha Kwe-Conditional

Njengoba kukhonjisiwe, imithetho yokufometha enemibandela ehlolisisa lezi zimo ezimbili izongenwa ngokusebenzisa ibhokisi lokungena lokufometha leNew Formatting Rule lokufometha.

Ukusetha ukufometha okunemibandela ukuthola ukwanda okukhulu kuka-25%

1. Gqamisa amaseli B2 kuya ku-B5 ekhasini lokusebenzela.
2. Chofoza kuthebhu yaseKhaya ye-ribbon.
3. Chofoza kusithonjana sokufometha okunemibandela kwi- ribbon ukuvula imenyu yehla.
4. Khetha Umthetho Omusha ukuvula ibhokisi lokungena lokubamba kabusha le-New Formatting njengoba kuboniswe esithombeni ngenhla.
5. Engxenyeni ephezulu yebhokisi lebhokisi, chofoza okukhethwa kukho kokugcina: Sebenzisa ifomula ukucacisa ukuthi yimaphi amangqamuzana ukufometha.
6. Ngxenye engezansi yebhokisi lebhokisi, chofoza kumanani wefomathi lapho leli fomula liyiqiniso: umugqa.
7. Thayipha ifomula : = (A2-B2) / A2> 25% endaweni esinikeziwe
8. Chofoza inkinobho yefomathi ukuze uvule ibhokisi lengxoxo yefomathi yefomathi.
9. Kulobhokisi lebhokisi, chofoza ithebhu yokugcwalisa bese ukhetha umbala ogcwele ohlaza okwesibhakabhaka.
10. Chofoza OK ngokuphindwe kabili ukuvala amabhokisi engxoxo bese ubuyela ekhasini lokusebenzela.
11. Kuleli phuzu, umbala wangemuva wamaseli B3 no-B5 kufanele ube luhlaza okwesibhakabhaka.

Ukusetha ukufometha okunemibandela ukuthola ukwanda okungaphezu kuka-50%

1. With amangqamuzana B2 kuya B5 okwamanje akhethiwe, phinda izinyathelo 1 kuya 6 ngenhla.
2. Thayipha ifomula: = (A2-B2) / A2> 50% endaweni esinikeziwe.
3. Chofoza inkinobho yefomathi ukuze uvule ibhokisi lengxoxo yefomathi yefomathi.
4. Chofoza ku- Gcwalisa ithebhu bese ukhetha umbala ogcwele obomvu.
5. Chofoza OK ngokuphindwe kabili ukuvala amabhokisi engxoxo bese ubuyela ekhasini lokusebenzela .
6. Umbala ongemuva weseli B3 kufanele usabalalisa okwesibhakabhaka obonisa ukuthi umehluko wephesenti phakathi kwezinombolo kumaseli A3 no-B3 mkhulu kunama-25% kodwa ngaphansi noma okulingana no-50%.
7. Umbala wangemuva weseli B5 kufanele ushintshe obomvu obonisa ukuthi umehluko wephesenti phakathi kwezinombolo kumaseli A5 no-B5 mkhulu kunama-50%.

Ukuhlola imithetho yokufometha okunemibandela

Ibala% umehluko

Ukuze uhlole ukuthi imithetho yokufometha enemibandela ifakiwe, singakwazi ukufaka amafomula kumaseli C2: C5 azobala inani lephesenti eliqondile phakathi kwezinombolo ezigabeni A2: A5 no-B2: B5.

1. Chofoza kuseli C2 ukuze wenze iseli esebenzayo.
2. Thayipha kwifomula = (A2-B2) / A2 bese ucindezela ukhiye we-Enter ekhibhodi.
3. Impendulo engu-10% kufanele ibonakale esitokisini C2, ebonisa ukuthi inombolo e-cell A2 ingu-10% emikhulu kunomboro kuseli B2.
4. Kungadingeka ukuthi ushintshe ukufometha kwiseli C2 ukuze ubonise impendulo njengephesenti.
5. Sebenzisa isibambo sokugcwalisa ukukopisha ifomu kusuka kuseli C2 kumaseli C3 kuya ku-C5.
6. Izimpendulo zamaseli C3 kuya ku-C5 kufanele zibe: 30%, 25%, no-60%.
7. Izimpendulo kulawa maseli zibonisa ukuthi imithetho yokufometha enemibandela yenziwe kahle ngoba umehluko phakathi kwamaseli A3 no-B3 mkhulu kuno-25% kanti umehluko phakathi kwamaseli A5 no-B5 mkhulu kunama-50%.
8. I-Cell B4 ayizange ishintshe umbala ngoba umehluko phakathi kwamaseli A4 no-B4 ulingana no-25%, futhi umthetho wethu wokufometha okunemibandela wachaza ukuthi amaphesenti amakhulu kunama-25% adingekayo ukuze umbala wangemuva uguqule kuluhlaza okwesibhakabhaka.

I-Order of Precedence yeMithetho Yokufometha Ngemibandela

Ukusebenzisa Imithetho Yokufometha Emibandela Engqubuzanayo

Uma imithetho eminingi isetshenziselwa uhla olufanayo lwemininingwane, i-Excel kuqala inquma uma imithetho iphikisana.

Imithetho ephikisanayo yilabo lapho izinketho zokufometha ezikhethiwe zomthetho ngamunye azikwazi ukusetshenziswa kokubili idatha efanayo.

Esikhathini esisetshenziswe kulesi sifundo, imithetho yezingxabano kusukela kokubili imithetho isebenzisa indlela efanayo yokufometha - leyo yokushintsha umbala weselula yangemuva.

Esikhathini lapho umthetho wesibili uqinisile (umehluko wokubaluleka ukhulu kunama-50% phakathi kwamaseli amabili) khona-ke umthetho wokuqala (umehluko wokubaluleka ukhulu kunama-25%) nakho kuyiqiniso.

I-Excel Order of Precedence

Njengoba iseli lingenaso isizinda sangemuva nesibhakabhaka ngesikhathi esifanayo, i-Excel idinga ukwazi ukuthi yikuphi ukubaluleka kokufometha okunemibandela okufanele kuyenze.

Imuphi umthetho osebenzayo otholwe yi-oda ye-Excel yokuqala, okusho ukuthi umbuso ophezulu ohlwini ohlwini lwebhokisi lengxoxo yeMenenja yeMithetho yePhasimende yokuPhatha ngokuPhezulu kunokuqala.

Njengoba kuboniswe esithombeni esingenhla, umthetho wesibili osetshenziswe kule tutorial (= (A2-B2) / A2> 50%) uphezulu ohlwini futhi, ngenxa yalokho, unokuqala ngaphezu komthetho wokuqala.

Ngenxa yalokho, umbala wangemuva weselula B5 ushintshwe ube obomvu.

Ngokuzenzakalelayo, imithetho emisha yengezwe phezulu kuhlu futhi, ngakho-ke, ibe nokuqala okuphezulu.

Ukushintsha ukuhlelwa kokuqala sebenzisa izinkinobho ze-Up and Down ezisebhokisini lebhokisi njengoba zikhonjiswe esithombeni ngenhla.

Ukusebenzisa imithetho engekho engqubuzanayo

Uma imithetho emibili yokufometha enemibandela engahambisani nayo isetshenziswe kokubili uma isimo ngasinye sivivinyo sihlola.

Uma umgomo wokuqala wokufometha esimweni sethu (= (A2-B2) / A2> 25%) ifometha ububanzi bamaseli B2: B5 nomngcele ohlaza okwesibhakabhaka esikhundleni sombala wangemuva ohlaza okwesibhakabhaka, imithetho emibili yokufometha emibandela ayengeyona ingxabano kusukela kokubili amafomethi angasetshenziswa ngaphandle kokuphazamisa omunye.

Ngenxa yalokho, iseli B5 liyoba nomngcele ohlaza okwesibhakabhaka nombala obomvu obomvu, ngoba umehluko phakathi kwezinombolo kumaseli A5 no-B5 mkhulu kunamaphesenti angu-25 no-50.

Ukufometha okunemibandela nokuFometha njalo

Endabeni yokuphikisana phakathi kwemithetho yokufometha okunemibandela kanye nezinketho zokufometha ezisebenzisekayo, umthetho wokufometha okunemibandela njalo uqala phambili futhi uzosetshenziswa kunoma yikuphi okukhethwa kukho okufakiwe okufakwe ngesandla.

Uma umbala wesizinda ophuzi ekuqaleni wawusetshenziselwa amaseli B2 kuya ku-B5 kusibonelo, uma imithethonqubo yokufometha enemibandela ingeziwe, kuphela amaseli B2 no-B4 ahlale ephuzi.

Ngenxa yokuthi imithetho yokufometha enemibandela ifakiwe isebenza kumaseli B3 no-B5, imibala yabo yangemuva ingashintsha kusuka ophuzi ube obomvu nokubomvu ngokulandelanayo.