Iifomula zeFom

Ukongeza ifomathi emiselweyo kwi-Excel ikuvumela ukuba wenze iindlela ezahlukeneyo zokufomatha kwisisele okanye uluhlu lweeseli ezidibana neemeko ezithile ozibekayo.

Izinketho zokufomatha zisetyenziswa kuphela xa iiseli ezikhethiweyo zidibana nale miqathango.

Izinketho zokufomatha ezingasetyenziswa ziquka iinguqu kunye neenguqu zemibala yangaphandle, izitayela zefonti, imida yeseli, kunye nokongeza ifomathi yokufomatha kwiedatha.

Ukususela kwi-Excel 2007, i-Excel inenani lezakhelo ezikhethiweyo kwizimo eziqhelekileyo ezisetyenziswayo ezifana nokufumana amanani amakhulu kunawo okanye ngaphantsi kwexabiso elithile okanye ukufumana amanani angaphezulu okanye ngaphantsi kwexabiso eliphakathi .

Ukongezelela kwezi zikhetho zokubeka kuqala, kunokwenzeka kwakhona ukudala imigaqo yokufomathiza emiselweyo yenkqubo usebenzisa iifomula ze-Excel ukuvavanya izimo ezichazwe ngumsebenzisi.

Ukusebenzisa iMithetho emininzi

Umgaqo ongaphezu komnye unokusetyenziswa kwiedata ezifanayo ukuvavanya kwiimeko ezahlukeneyo. Ngokomzekelo, idatha yebhajethi ingaba neemeko ezibekwe ezisebenzayo utshintsho xa amazinga athile-njengama-50%, i-75% kunye ne-100%-mali-mali isetyenzisiweyo.

Kwiimeko ezinje, i-Excel yokuqala inquma ukuba imigaqo eyahlukeneyo imida, kwaye, ukuba kunjalo, le nkqubo ilandela umyalelo olandelelweyo wokubaluleka komgaqo-nkqubo wokufomatha osebenzayo kwi-data.

Umzekelo: Ukufumana idatha engaphezu kwama-25% kunye no-50% uwandisa ngeFomation Format

Kulo mzekelo olandelayo, imigaqo emibili yokwenza ifomathi emiselweyo iya kusetyenziswa kwibanga leeseli B2 ukuya kwi-B5.

• Umgaqo wokuqala uhlola ukuqinisekisa ukuba ngaba idatha kwiiseli ze-A2 ukuya ku-A5 zikhulu kunexabiso elilinganayo kwi-B2 ukuya kwi-B5 ngaphezulu kwama-25%.
• Umgaqo wesibini uhlola ukuqinisekisa ukuba ngaba idatha efana no-A2: A5 idlula ixabiso elihambelana neB2: B5 ngaphezulu kwama-50%.

Njengoko kunokubonwa kumfanekiso ongentla, ukuba enye yeemeko ezingentla ziyinyaniso, umbala wendawo yeseli okanye iiseli kwi-B1: B4 iya kutshintsha.

• ukuba idatha apho umda omkhulu kunama-25%, umbala wemibala wesangqa uya kutshintshela kuluhlaza.
• ukuba umahluko ukhulu kunama-50% umbala wesiqalo weseli uya kutshintsha ube obomvu.

Imithetho esetyenziswa ukufeza lo msebenzi,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

ziya kufakwa kusetyenziswa ibhokisi yencoko yebhokisi yencoko yebhokisi yencoko yokufometha.

Ukufaka i-Tutorial Data

1. Faka idatha kwiiseli A1 ukuya ku-C5 njengoko kuboniswa kumfanekiso ongentla

Qaphela: Inyathelo lesi-3 le-tutori liza kufaka iifomula kwiiseli C2: C4 ezibonisa ukungafani kwepesenti phakathi kwamaxabiso kwiiseli A2: A5 kunye ne-B2: B5 ukwenzela ukuba uhlolisise ukuchaneka kwemithetho yokufomatha imimiselo.

Ukubeka iMigaqo yokuFometha kweCondling

Njengoko kubonisiwe, imigaqo yokufomatha imimiselo enokujonga ukuba le miqathango mibini iya kufakwa isebenzisa ibhokisi yencoko yebhokisi yencoko yababini yokufometha.

Ukubeka ifomathi enemimiselo yokufumana ukukhuphuka okukhulu kunama-25%

1. Khanyisa iiseli B2 ukuya kwi-B5 kwiphepha lokusebenzela.
2. Cofa kwiThebhu yasekhaya yerbhoni.
3. Cofa kwi icon Yokwenza Umoya kwi- Ribbon ukuvula imenyu ehlayo.
4. Khetha Umgaqo omtsha ukuvula ibhokisi yencoko yebhokisi yencoko yoBuchule njengoko kuboniswa kumfanekiso ongentla.
5. Kwisiqingatha esiphezulu kwibhokisi yencoko, nqakraza kwindlela yokugqibela: Sebenzisa ifomula ukukhetha ukuba yeyiphi iiseli ukuba ifomathile.
6. Kwisiqingatha esingaphantsi kwibhokisi yencoko, nqakraza kwiimpawu zokufometha apho le fomyula yinyani: umgca.
7. Thayipha ifomu : = (A2-B2) / A2> 25% kwithuba elinikeziwe
8. Cofa kwiqhosha leFomathi ukuvula ibhokisi yencoko yencoko yefomathi.
9. Kulo bhokisi yencoko, nqakraza kwiTebhu yokugcwalisa uze ukhethe umbala ogcwala umbala.
10. Cofa Kulungile kabini ukuvala ibhokisi yencoko kwaye ubuyele kwiphepha lokusebenzela.
11. Kule ngongoma, umbala wesiqendu weeseli B3 kunye neB5 kufuneka ube luhlaza okwesibhakabhaka.

Ukubeka ifomathi enemimiselo yokufumana ukukhuphuka okungaphezulu kwama-50%

1. Ngeeseli zeB2 ukuya kwi-B5 zikhethiweyo, phinda amanyathelo 1 ukuya ku-6 ngaphezulu.
2. Thayipha ifomu: = (A2-B2) / A2> 50% kwithuba elinikeziwe.
3. Cofa kwiqhosha leFomathi ukuvula ibhokisi yencoko yencoko yefomathi.
4. Cofa kwiTayiti yokugcwalisa uze ukhethe umbala obomvu ogcwalisa.
5. Cofa Kulungile kabini ukuvala ibhokisi yencoko kwaye ubuyele kwiphepha lokusebenzela .
6. Umbala wesiqendu wesisele B3 kufuneka uhlale uluhlaza okwesibhakabhaka obonisa ukuba ukuhluka kwepesenti phakathi kwamanani kwii-A3 kunye ne-B3 ngaphezu kwe-25% kodwa ngaphantsi okanye kulingana no-50%.
7. Umbala wesiqendu seseli B5 kufuneka uguqulwe obomvu obonisa ukuba umahluko wepesenti phakathi kwamanani kwii-A5 kunye ne-B5 mkhulu kunama-50%.

Ukuhlola iMimiselo yokuFometha kwemiqathango

Ukubala i%%

Ukujonga ukuba imigaqo efomathiweyo engenisiweyo ifakiwe, sinokufakela iifomula kwiiseli C2: C5 eziza kubala ipesenti ekhoyo phakathi kwamanani kumanqanaba A2: A5 kunye neB2: B5.

1. Cofa kwiseli C2 ukuze wenze iseli esebenzayo.
2. Thayipha kwifomula = (A2-B2) / A2 uze ucinezela ukhiye wokungena kwikhibhodi.
3. Impendulo ye-10% kufuneka ibonakale kwiselfowuni C2, ebonisa ukuba inombolo yeseli ye-A2 i-10% enkulu kunenombolo kwiseli B2.
4. Kufuneka ukuba utshintshe ukufomatha kwiseli C2 ukuze ubonise impendulo njengepesenti.
5. Sebenzisa isibambo sokuzalisa ukukopisha ifomu kwiSeli C2 kwiiseli C3 ukuya kwiC5.
6. Iimpendulo zeeseli zeC3 ukuya ku-C5 zimele zibe: i-30%, i-25% kunye ne-60%.
7. Iimpendulo kulezi seteli zibonisa ukuba imigaqo yokufomatha imimiselo yenziwe ichanekile ukususela umahluko phakathi kweeseli A3 kunye neB3 zikhulu kunama-25% kwaye umehluko phakathi kweeseli A5 kunye ne-B5 likhulu kunama-50%.
8. I-Cell B4 ayitshintshe umbala ngenxa yokuba umehluko phakathi kweeseli A4 kunye ne-B4 lilingana nama-25%, kwaye umgaqo-nkqubo wokufomathiza ongokoqobo uchaze ukuba ipesenti ephezulu kunama-25% yayifuneka ukuba umbala ongasemva uguqulwe uhlaza.

Umyalelo woLungiso lweMimiselo yokuFometha imiqathango

Ukusebenzisa iMimiselo yokuFomathiza yeMimiselo engqubanayo

Xa imithetho emininzi isetyenziswe kwinqanaba leedata elifanayo, i-Excel yokuqala ikhetha ukuba imigaqo-mpikiswano.

Imithetho ephikisanayo yilawo apho ukhetho olukhethiweyo lokufomatha komgaqo ngamnye alukwazi ukusetyenziswa kwimiqulu efanayo.

Kulo mzekelo osetyenziswe kule khokelo, imigaqo yombambano kuba zombini imithetho isebenzisa indlela efanayo yokufometha - leyo yokutshintsha umbala weselfowuni.

Kwimeko apho umgaqo wesibili uyinyaniso (umlinganiselo wexabiso lukhulu kunama-50% phakathi kwamaseli amabini) ngoko umgaqo wokuqala (ukwahlula kwixabiso elingaphezu kwe-25%) liyinyaniso.

I-Excel's Order of Precedence

Ekubeni i-cell ayikwazi ukufumana imvelaphi ebomvu neyayibluhlaza ngexesha elifanayo, i-Excel kufuneka ikwazi ukuba yiyiphi imigaqo yokwenza ifomathi emiselweyo ifanele ifake isicelo.

Yiyiphi imigaqo esetyenziswayo imiselwe ngumyalelo we-Excel wokuqala, ochaza ukuba umthetho ophezulu eluhlu kwibhokisi yencoko yebhokisi yencoko yomphathi weMigaqo yoLuhlu lweMibandela.

Njengokuba kuboniswe kumfanekiso ongentla, umgaqo wesibini osetyenziswa kule khokelo (= (A2-B2) / A2> 50%) ephakamileyo eluhlu kwaye, ngoko ke, inokuqala ngaphezu komgaqo wokuqala.

Ngenxa yoko, umbala wangasemva weseli B5 utshintshile ube obomvu.

Ngokungagqibekanga, imithetho emitsha yongezwa phezulu kwoluhlu kwaye, ngoko ke, inokuqala.

Ukutshintsha umyalelo wokuqala usebenzisa iifayile eziphezulu ze-Down and Down phantsi kwibhokisi yencoko njengoko kuboniswe kumfanekiso ongentla.

Ukusebenzisa iMithetho engeyiyo iMibandela

Ukuba imigaqo emibili yefomathi yomgaqo-nkqubo ayinakuphikisana zombini iyasetyenziswa xa umqathango ngamnye ulawulo luvavanya.

Ukuba umgaqo wokuqala wokumisa ifomathi kumzekelo wethu (= (A2-B2) / A2> 25%) ifomathi uluhlu lweeseli B2: B5 kunye nomda ohlaza okomhlaza endaweni yombala ohlaza okomvu, zombini ifomathi zingasetyenziswa ngaphandle kokuphazamisana nomnye.

Ngenxa yoko, iseli B5 yayiza kuba nomda ohlaza okomvu kunye nombala obomvu obomvu, kuba umlinganiselo phakathi kwamanani kwii-A5 kunye ne-B5 mkhulu kunama-25 ne-50 ekhulwini.

Ukufomathika kwemiqathango kunye nokuFometha rhoqo

Kwimeko yeengxabano phakathi kwemigaqo yokufomatha enemiqathango kunye neendlela zokwenza ifomathi ezisetyenziswayo ngesandla, umgaqo-fomathi wemeko ubeka phambili kwaye uya kusetyenziswa endaweni yezokhetho zokufomathiza.

Ukuba umbala wesiqalo obomvu wawusetyenziselwa ekuqaleni kwiiseli B2 ukuya ku-B5 kumzekelo, xa imigaqo yokufomatha imimiselo yongezwa, kuphela iiseli B2 kunye ne-B4 ziya kuhlala ziphuzi.

Ngenxa yokuba imithetho efomathiweyo yokufomatha ifaka isicelo kwiiseli zeB3 kunye neB5, imibala yazo yangasemtshintshi iya kutshintsha kwimibala eluhlaza kwaye ibomvu ngokulandelanayo.