Ungawushintsha kanjani umbala womugqa ku-Excel ngokwemibandela, kuye ngezimo

Kulesi sihloko, uzofunda ukuthi ungashintsha kanjani ngokushesha ingemuva lomugqa ngokusekelwe kunani elithile kusipredishithi. Nansi imihlahlandlela kanye namafomula ahlukahlukene ombhalo nezinombolo kudokhumenti.

Ngaphambilini, sixoxe ngezindlela zokushintsha umbala ongemuva weseli ngokusekelwe embhalweni noma enanini lezinombolo kuwo. Izincomo zizophinde zethulwe lapha mayelana nendlela yokugqamisa imigqa edingekayo ezinguqulweni zakamuva ze-Excel, ngokusekelwe kokuqukethwe kweseli elilodwa. Ngaphezu kwalokho, lapha uzothola izibonelo zamafomula asebenza ngokulinganayo kuwo wonke amafomethi weseli angaba khona.

Ungakushintsha kanjani ukubukeka komugqa ngokusekelwe enombolweni kuseli ethile

Isibonelo, unedokhumenti evuliwe enethebula lezivumelwano lenhlangano elifana naleli.

Ake sithi udinga ukugqamisa imigqa emithunzi ehlukene, ugxile kulokho okubhalwe esitokisini kukholamu ye-Qty, ukuze uqonde ngokucacile ukuthi yikuphi ukuthengiselana okunenzuzo kakhulu. Ukuze ufinyelele lo mphumela, udinga ukusebenzisa umsebenzi othi "Ukufometha Okunemibandela". Landela imiyalelo yesinyathelo ngesinyathelo:

1. Khetha amaseli ofuna ukuwafometha.
2. Dala isimiso esisha sokufometha ngokuchofoza into efanelekile kumenyu yokuqukethwe evela ngemva kokuchofoza inkinobho ethi "Ukufometha Okunemibandela" kuthebhu "Ekhaya".

1. Ngemuva kwalokho, kuzovela ibhokisi lengxoxo lapho udinga ukukhetha khona isilungiselelo "sebenzisa ifomula ukuze unqume amaseli afomethiwe." Okulandelayo, bhala ifomula elandelayo: =$C2>4 ebhokisini elingezansi. Ngokwemvelo, ungakwazi ukufaka ikheli lakho leselula kanye nombhalo wakho, futhi ufake esikhundleni sokuthi > sayina ngokuthi < noma =. Ngaphezu kwalokho, kubalulekile ukuthi ungakhohlwa ukubeka uphawu lwe-$ phambi kwereferensi yeseli ukuze ulilungise lapho ukopisha. Lokhu kwenza kube nokwenzeka ukuhlanganisa umbala womugqa kunani leseli. Uma kungenjalo, lapho ukopisha, ikheli "lizophuma".
2. Chofoza okuthi “Fometha” bese ushintshela kuthebhu yokugcina ukuze ucacise umthunzi owufunayo. Uma ungawathandi ama-shades aphakanyiswe uhlelo, ungahlala uchofoza okuthi “Eminye Imibala” bese ukhetha umthunzi owudingayo.
3. Ngemuva kokuqeda yonke imisebenzi, kufanele uchofoze kabili inkinobho ethi "OK". Ungakwazi futhi ukusetha ezinye izinhlobo zokufometha (uhlobo lwefonti noma isitayela esithile somngcele weseli) kwamanye amathebhu aleli windi.
4. Phansi kwefasitela kunephaneli yokubuka kuqala lapho ungabona khona ukuthi iseli izobukeka kanjani ngemva kokufometha.
5. Uma konke kukufanele, chofoza inkinobho ethi "KULUNGILE" ukuze usebenzise izinguquko. Konke, ngemva kokwenza lezi zenzo, yonke imigqa lapho amaseli aqukethe inombolo enkulu kuno-4 izoba luhlaza okwesibhakabhaka.

Njengoba ubona, ukushintsha umbala womugqa ngokusekelwe enanini leseli elithile kuwo akuwona umsebenzi onzima kakhulu. Ungasebenzisa futhi amafomula ayinkimbinkimbi ukuze ube nezimo kakhulu ekusebenziseni ukufometha okunemibandela ukuze ufinyelele lo mgomo.

Sebenzisa imithetho eminingi ngokubalulekile kwayo

Isibonelo sangaphambilini sibonise inketho yokusebenzisa umthetho wokufometha onemibandela owodwa, kodwa ungase ufune ukusebenzisa ezimbalwa ngesikhathi esisodwa. Yini okufanele uyenze esimweni esinjalo? Isibonelo, ungangeza umthetho ngokuya ngokuthi yimiphi imigqa enenombolo engu-10 noma ngaphezulu ezoqokonyiswa nge-pink. Lapha kuyadingeka ukuthi ngaphezu kwalokho ubhale phansi ifomula =$C2>9, bese ubeka izinto eziza kuqala ukuze yonke imithetho isetshenziswe ngaphandle kokungqubuzana.

1. Kuthebhu ethi “Ekhaya” eqenjini elithi “Izitayela”, udinga ukuchofoza “Ukufometha Okunemibandela” futhi kumenyu evelayo, khetha okuthi “Phatha Imithetho” ekugcineni kohlu.
2. Okulandelayo, kufanele ubonise yonke imithetho eqondene nale dokhumenti. Ukuze wenze lokhu, udinga ukuthola uhlu phezulu "Bonisa imithetho yokufometha", bese ukhetha into ethi "Leli shidi" lapho. Futhi, ngale menyu, ungamisa imithetho yokufometha yamaseli athile akhethiwe. Esimweni sethu, sidinga ukuphatha imithetho yawo wonke umbhalo.
3. Okulandelayo, udinga ukukhetha umthetho ofuna ukuwusebenzisa kuqala bese uwuyisa phezulu ohlwini usebenzisa imicibisholo. Uzothola umphumela onjalo.
4. Ngemva kokubeka izinto eziza kuqala, udinga ukuchofoza "KULUNGILE", futhi sizobona ukuthi imigqa ehambisanayo ishintshe kanjani umbala wayo, ngokusho kokuqala. Okokuqala, uhlelo luhloliwe ukuze lubone ukuthi ingabe inani kukholomu ye-Qty likhulu kuno-10, futhi uma kungenjalo, noma ngabe likhulu yini kuno-4.

Ukushintsha umbala womugqa wonke ngokusekelwe embhalweni obhalwe kuseli

Cabanga ukuthi ngenkathi usebenza nge-spreadsheet, unobunzima bokulandelela ngokushesha ukuthi yiziphi izinto esezilethiwe nokuthi yiziphi ezingakafiki. Noma mhlawumbe ezinye ziphelelwe yisikhathi. Ukuze wenze lo msebenzi ube lula, ungazama ukukhetha imigqa esekelwe embhalweni oseselini elithi “Delivery”. Ake sithi sidinga ukumisa le mithetho elandelayo:

1. Uma i-oda lidlulelwe yisikhathi ngemva kwezinsuku ezimbalwa, umbala ongemuva womugqa ohambisanayo uzoba owolintshi.
2. Uma izimpahla sezilethiwe, umugqa ohambisanayo uphenduka ube luhlaza.
3. Uma ukulethwa kwezimpahla sekwephuzile, khona-ke ama-oda ahambisanayo kufanele agqanyiswe ngokubomvu.

Ngamagama alula, umbala womugqa uzoshintsha kuye ngesimo sokuhleleka.

Ngokuvamile, i-logic yezenzo zama-oda alethiweyo nadlulelwe yisikhathi izofana nesibonelo esichazwe ngenhla. Kuyadingeka ukuthi unikeze amafomula ewindini lokufometha elinemibandela =$E2=»Lethiwe» и =$E2=»Okudlule Ifuneka» ngokulandelana. Umsebenzi onzima kakhulu wamadili azophelelwa yisikhathi phakathi nezinsuku ezimbalwa.

Njengoba sibona, inani lezinsuku lingahluka phakathi kwemigqa, lapho ifomula engenhla ingakwazi ukusetshenziswa.

Kulokhu kukhona umsebenzi =SESHA(“Ifuneka”, $E2)>0, lapho:

• ukuphikisana kokuqala kubakaki umbhalo oqukethwe kuwo wonke amaseli achaziwe,
• futhi i-agumenti yesibili yikheli leseli ofuna ukulizulazula ivelu yalo.

Enguqulweni yesiNgisi yaziwa ngokuthi =SESHA. Iklanyelwe ukusesha amaseli afana kancane nombuzo wokufakwayo.

Ithiphu: Ipharamitha >0 kufomula isho ukuthi akunandaba ukuthi umbuzo wokufakwayo ukuphi kumbhalo weseli.

Isibonelo, ikholomu ethi “Ukulethwa” ingase iqukathe umbhalo othi “Kuyaphuthuma, Kudingeka Emahoreni angu-6” futhi iseli elihambisanayo lisazofomethwa ngendlela efanele.

Uma kudingekile ukusebenzisa imithetho yokufometha emigqeni lapho iseli elingukhiye liqala khona ngegama olifunayo, kufanele ubhale okuthi =1 kufomula esikhundleni sokuthi >0. 

Yonke le mithetho ingabhalwa ebhokisini lengxoxo elihambisanayo, njengakusibonelo esingenhla. Ngenxa yalokho, uthola okulandelayo:

Ungawushintsha kanjani umbala weseli ngokusekelwe kunani kwelinye iseli?

Njengomugqa, izinyathelo ezingenhla zingasetshenziswa kuseli eyodwa noma ububanzi bamanani. Kulesi sibonelo, ukufometha kusetshenziswa kuphela kumaseli akukholomu "Inombolo ye-oda":

Indlela yokusebenzisa izimo eziningi zokufometha

Uma udinga ukusebenzisa imithetho embalwa yokufometha enemibandela kuyunithi yezinhlamvu, esikhundleni sokubhala imithetho ehlukene, udinga ukuyidala enamafomula. =NOMA or =I. Elokuqala lisho ukuthi “omunye wale mithetho uyiqiniso,” owesibili usho ukuthi “yomibili le mithetho iyiqiniso.”

Esimweni sethu, sibhala amafomula alandelayo:

=ИЛИ($F2=»Ifuneka Ezinsukwini Olungu-1», $F2=»Ifuneka Ngezinsuku Ezingu-3»)

=ИЛИ($F2=»Ifuneka Ezinsukwini Olungu-5», $F2=»Ifuneka Ngezinsuku Ezingu-7»)

Futhi ifomula =I ingasetshenziswa, ngokwesibonelo, ukuze uhlole ukuthi inombolo ekukholomu ye-Qty iyini. okukhulu noma kulingana no-5 futhi ngaphansi noma okulingana no-10.

Umsebenzisi angasebenzisa izimo ezingaphezu kwesisodwa kumafomula.

Manje uyazi ukuthi yini okudingeka uyenze ukuze ushintshe umbala womugqa ngokusekelwe kuseli ethile, uyaqonda ukuthi ubeka kanjani izimo eziningi futhi uzibeke phambili, nokuthi ungasebenzisa kanjani amafomula amaningi ngesikhathi esisodwa. Okulandelayo, udinga ukukhombisa umcabango.