Izindlela ezi-2 zokushintsha ingemuva ku-Excel

Kulesi sihloko, uzofunda izindlela ezimbili ezilula zokushintsha ingemuva lamaseli ngokusekelwe kokuqukethwe kwawo ezinguqulweni zakamuva ze-Excel. Uzoqonda futhi ukuthi yimaphi amafomula ongawasebenzisa ukushintsha umthunzi wamaseli noma amaseli lapho amafomula abhalwe ngokungalungile, noma lapho kungekho nhlobo ulwazi.

Wonke umuntu uyazi ukuthi ukuhlela isizinda seseli elula kuyinqubo elula. Chofoza "umbala wangemuva". Kodwa kuthiwani uma udinga ukulungiswa kombala ngokusekelwe kokuqukethwe kweseli okuthile? Ngingakwenza kanjani lokhu kwenzeke ngokuzenzakalelayo? Okulandelayo uchungechunge lolwazi oluwusizo oluzokwenza ukwazi ukuthola indlela efanele yokufeza yonke le misebenzi.

Ukushintsha kombala wengemuva leseli elinamandla

Umsebenzi: Unethebula noma isethi yamanani, futhi udinga ukuhlela umbala wangemuva wamaseli ngokusekelwe ekutheni iyiphi inombolo efakwe lapho. Kudingeka futhi uqinisekise ukuthi i-hue isabela ekushintsheni amanani.

Isixazululo: Kulo msebenzi, umsebenzi we-Excel “wokufometha okunemibandela” unikezwa amaseli ombala anezinombolo ezinkulu kuno-X, ngaphansi kuno-Y, noma phakathi kuka-X kanye no-Y.

Ake sithi unesethi yemikhiqizo ezifundazweni ezahlukene ngamanani ayo, futhi udinga ukwazi ukuthi iyiphi yayo ebiza ngaphezu kuka-$3,7. Ngakho-ke, sinqume ukugqamisa leyo mikhiqizo engaphezulu kwaleli xabiso ngokubomvu. Futhi amaseli anenani elifanayo noma elikhulu, kwanqunywa ukuthi agcobe ku-tint eluhlaza.

Qaphela: Isithombe-skrini sithathwe enguqulweni yango-2010 yohlelo. Kodwa lokhu akuthinti lutho, njengoba ukulandelana kwezenzo kuyafana, kungakhathaliseki ukuthi iyiphi inguqulo - yakamuva noma cha - umuntu oyisebenzisayo.

Ngakho nakhu okudingeka ukwenze (isinyathelo ngesinyathelo):

1. Khetha amaseli lapho i-hue kufanele ihlelwe khona. Ngokwesibonelo, ububanzi $B$2:$H$10 (amagama ekholomu kanye nekholomu yokuqala, ebonisa amagama ezifundazwe, azifakiwe kusampula).

I-2. Chofoza "Ikhaya" eqenjini "isitayela". Kuzoba khona into "Ukufometha Okunemibandela". Lapho futhi udinga ukukhetha “Umthetho Omusha”. Enguqulweni yesiNgisi ye-Excel, ukulandelana kwezinyathelo kungokulandelayo: “Ikhaya”, “Iqembu lezitayela”, “Ukufometha Okunemibandela > Umthetho Omusha».

3. Efasiteleni elivulekayo, khetha ibhokisi "Fometha kuphela amaseli aqukethe" (“Fometha kuphela amaseli aqukethe” enguqulweni yesiNgisi).

4. Phansi kwaleli windi ngaphansi kombhalo "Fometha kuphela amaseli ahlangabezana nalesi simo esilandelayo" (Fometha ngamaseli kuphela) unganikeza imithetho okuzokwenziwa ngayo ukufometha. Sikhethe ifomethi yenani elishiwo kumaseli, okufanele libe likhulu kuno-3.7, njengoba ubona esithombeni-skrini: 

5. Okulandelayo, chofoza inkinobho "Ifomethi". Kuzovela iwindi elinendawo yokukhetha umbala wangemuva kwesokunxele. Kodwa ngaphambi kwalokho, udinga ukuvula ithebhu "Gcwalisa" ("Gcwalisa"). Kulokhu, kubomvu. Ngemva kwalokho, chofoza inkinobho "OK".

6. Bese uzobuyela efasiteleni “Umthetho Omusha Wokufometha”, kodwa kakade ngaphansi kwaleli windi ungakwazi ukubuka kuqala ukuthi leli seli lizobukeka kanjani. Uma konke kuhamba kahle, kufanele uchofoze inkinobho ethi "OK".

Ngenxa yalokho, uthola okuthile okufana nalokhu:

Okulandelayo, sidinga ukwengeza isimo esisodwa, okungukuthi, ukushintsha ingemuva lamaseli anamanani angaphansi kuka-3.45 abe luhlaza. Ukuze wenze lo msebenzi, kufanele uchofoze “Umthetho Omusha Wokufometha” bese uphinda izinyathelo ezingenhla, isimo kuphela esidinga ukumiswa njenge “okuncane, noma okulinganayo” (enguqulweni yesiNgisi “ngaphansi noma kulingana no”, bese ubhala inani. Ekugcineni, udinga ukuchofoza inkinobho ethi “KULUNGILE”.

Manje ithebula lifomethwe ngale ndlela.

Ibonisa amanani entengo kaphethiloli aphakeme kakhulu futhi aphansi kakhulu ezifundeni ezihlukahlukene, futhi ungakwazi ngokushesha ukunquma lapho isimo sinethemba elikhulu (e-Texas, kunjalo).

Isincomo: Uma kunesidingo, ungasebenzisa indlela yokufometha efanayo, ungahleli ingemuva, kodwa ifonti. Ukuze wenze lokhu, efasiteleni lokufometha elivele esigabeni sesihlanu, udinga ukukhetha ithebhu "Ifonti" bese ulandela imiyalo enikezwe efasiteleni. Konke kucacile ngokunembile, futhi ngisho nomuntu oqalayo angakwazi ukukuthola.

Ngenxa yalokho, uzothola ithebula elifana naleli:

Ungagcina kanjani umbala weseli ufana ngisho noma inani lishintsha?

Umsebenzi: Udinga ukufaka umbala ingemuva ukuze lingalokothi lishintshe, ngisho noma ingemuva lishintsha ngokuzayo.

Isixazululo: thola wonke amaseli anenombolo ethile usebenzisa umsebenzi we-excel “Thola konke” "Thola Konke" noma isengezo "Khetha amaseli akhethekile" (“Khetha Amaseli Akhethekile”), bese uhlela ifomethi yeseli usebenzisa umsebenzi "Fometha amaseli" ("Fometha Amaseli").

Lesi ngesinye salezo zimo ezingavamile ezingafakwanga kubhukwana le-Excel, futhi ngisho naku-inthanethi, isixazululo sale nkinga singatholakala kuyaqabukela. Okungamangazi, ngoba lo msebenzi awujwayelekile. Uma ufuna ukuhlela ingemuva unaphakade ukuze lingashintshi kuze kube yilapho lilungiswa mathupha umsebenzisi wohlelo, kufanele ulandele imiyalelo engenhla.

Khetha wonke amaseli aqukethe isimo esithile

Kunezindlela ezimbalwa ezingenzeka, kuye ngokuthi hlobo luni lwenani elithile elizotholwa.

Uma udinga ukuqoka amaseli anenani elithile elinengemuva elikhethekile, udinga ukuya kuthebhu "Ikhaya" bese ukhetha "Thola bese ukhetha" - "Thola".

Faka amanani adingekayo bese uchofoza “Thola Konke”.

Usizo: Ungachofoza inkinobho “Izinketho” ngakwesokudla ukusizakala kwezinye izilungiselelo ezengeziwe: lapho ungasesha khona, ubuke kanjani, noma uhloniphe osonhlamvukazi nabancane, njalo njalo. Ungaphinda ubhale izinhlamvu ezengeziwe, njengenkanyezi (*), ukuze uthole yonke imigqa equkethe la manani. Uma usebenzisa uphawu lombuzo, ungathola noma yiluphi uhlamvu olulodwa.

Esibonelweni sethu sangaphambilini, uma besifuna ukuthola wonke amakhwothi kaphethiloli phakathi kuka-$3,7 no-$3,799, singahlunga umbuzo wethu wosesho.

Manje khetha noma yimaphi amanani atholwe uhlelo ngaphansi kwebhokisi lengxoxo bese uchofoza kwelinye lawo. Ngemva kwalokho, cindezela inhlanganisela yokhiye “Ctrl-A” ukuze ukhethe yonke imiphumela. Okulandelayo, chofoza inkinobho ethi "Vala". 

Nansi indlela yokukhetha wonke amaseli anamanani athile usebenzisa isici esithi Thola Konke. Esibonelweni sethu, sidinga ukuthola wonke amanani kaphethiloli angaphezu kuka-$3,7, futhi ngeshwa i-Excel ayikuvumeli ukuba wenze lokhu usebenzisa umsebenzi othi Thola bese Ufaka esikhundleni.

"Umphongolo woju" uvela lapha ngoba kukhona elinye ithuluzi elizosiza emisebenzini eyinkimbinkimbi kangaka. Ibizwa ngokuthi Khetha Amaseli Akhethekile. Lesi sengezo (esidinga ukufakwa ku-Excel ngokuhlukana) sizosiza:

• thola wonke amanani ebangeni elithile, isibonelo phakathi kuka -1 no-45,
• thola inani eliphezulu noma eliphansi kukholomu,
• thola umucu noma ububanzi,
• thola amaseli ngombala wangemuva nokunye okuningi.

Ngemva kokufaka isengezo, mane uchofoze inkinobho "Khetha ngevelu" (“Khetha Ngenani”) bese wenza ngcono umbuzo wosesho kuwindi le-addon. Esibonelweni sethu, sibheka izinombolo ezingaphezu kuka-3,7. Cindezela "Khetha" (“Khetha”), futhi ngomzuzwana uzothola umphumela ofana nalo:

Uma unentshisekelo kusengezo, ungalanda inguqulo yesilingo kokuthi isixhumanisi.

Ukushintsha ingemuva lamaseli akhethiwe ngewindi elithi “Fometha Amaseli”

Manje, ngemva kokuba wonke amaseli anenani elithile ekhethiwe kusetshenziswa enye yezindlela ezichazwe ngenhla, kusasele ukucacisa umbala wangemuva wawo.

Ukuze wenze lokhu, udinga ukuvula iwindi "Ifomethi yeseli"ngokucindezela ukhiye Ctrl + 1 (ungachofoza kwesokudla kumaseli akhethiwe bese uchofoza kwesokunxele entweni “yokufometha kweseli”) bese ulungisa ukufometha okudingayo.

Sizokhetha umthunzi we-orange, kodwa ungakhetha noma yikuphi okunye.

Uma udinga ukuhlela umbala wangemuva ngaphandle kokushintsha amanye amapharamitha wokubukeka, ungavele uchofoze "gcwalisa umbala" bese ukhetha umbala ofanelana nawe ngokuphelele.

Umphumela uyithebula elifana naleli:

Ngokungafani nendlela yangaphambilini, lapha umbala weseli ngeke ushintshe ngisho noma inani lihlelwa. Lokhu kungasetshenziswa, isibonelo, ukulandelela ukuguquguquka kwezimpahla eqenjini elithile lentengo. Inani labo lishintshile, kodwa umbala uhlala ufana.

Ukuhlela umbala wangemuva wamaseli akhethekile (angenalutho noma onamaphutha lapho ubhala ifomula)

Ngokufanayo nesibonelo sangaphambilini, umsebenzisi unekhono lokuhlela umbala wangemuva wamaseli akhethekile ngezindlela ezimbili. Kunezinketho ezimile neziguquguqukayo.

Ukusebenzisa Ifomula Yokuhlela Ingemuva

Lapha umbala weseli uzohlelwa ngokuzenzakalelayo ngokusekelwe enanini lawo. Le ndlela isiza abasebenzisi kakhulu futhi idingeka ku-99% wezimo.

Njengesibonelo, ungasebenzisa ithebula langaphambilini, kodwa manje amanye amaseli azobe engenalutho. Kudingeka sinqume ukuthi yiziphi ezingakuqukethe ukufundwa bese sihlela umbala wangemuva.

1. Kuthebhu "Ikhaya" udinga ukuchofoza "Ukufometha Okunemibandela" ->  “Umthetho Omusha” (kufana nesinyathelo sesi-2 sesigaba sokuqala “Shintsha umbala wangemuva ngokushintshashintshayo”.

2. Okulandelayo, udinga ukukhetha into "Sebenzisa ifomula ukuze unqume...".

3. Faka ifomula =Akunalutho() (ISBLANK enguqulweni), uma ufuna ukuhlela ingemuva leseli elingenalutho, noma =IsError() (ISERROR enguqulweni), uma udinga ukuthola iseli lapho kunefomula ebhalwe ngephutha. Njengoba kulesi simo sidinga ukuhlela amaseli angenalutho, sifaka ifomula =Akunalutho(), bese ubeka ikhesa phakathi kwabakaki bese uchofoza inkinobho eduze nenkambu yokufaka yefomula. Ngemva kwalezi zikhohlisi, uhla lwamaseli kufanele lukhethwe mathupha. Ngaphezu kwalokho, ungacacisa ububanzi ngokwakho, isibonelo, =I-IsBlank(B2:H12).

4. Chofoza inkinobho ethi “Fometha” bese ukhetha umbala wangemuva ofanele futhi wenze yonke into njengoba kuchazwe esigabeni 5 sengxenye ethi “Ukushintsha kombala wengemuva le-Dynamic cell”, bese uchofoza okuthi “KULUNGILE”. Lapho ungabona futhi ukuthi umbala weseli uzoba yini. Iwindi lizobukeka kanje.

5. Uma uthanda isizinda seseli, kufanele uchofoze inkinobho ethi "KULUNGILE", futhi izinguquko zizokwenziwa ngokushesha etafuleni.

Ushintsho olungaguquki lombala wangemuva wamaseli akhethekile

Kulesi simo, uma usunikezwe, umbala wangemuva uzoqhubeka uhlale unjalo, kungakhathaliseki ukuthi iseli lishintsha kanjani.

Uma udinga ukushintsha unomphela amaseli akhethekile (amaphutha angenalutho noma aqukethe), landela le miyalo:

1. Khetha idokhumenti yakho noma amaseli amaningana bese ucindezela u-F5 ukuze uvule iwindi elithi Go To, bese ucindezela inkinobho “Gqamisa”.
2. Ebhokisini lengxoxo elivulayo, khetha inkinobho ethi “Akunalutho” noma “Amaseli angenalutho” (kuye ngokuthi inguqulo yohlelo - noma isiNgisi) ukuze ukhethe amaseli angenalutho.
3. Uma udinga ukugqamisa amaseli anamafomula anamaphutha, kufanele ukhethe into "Amafomula" bese ushiya ibhokisi lokuhlola elilodwa eduze kwegama elithi “Amaphutha”. Ngokulandelayo kusukela kusithombe-skrini esingenhla, amaseli angakhethwa ngokwanoma yimiphi imingcele, futhi izilungiselelo ezichazweyo ngayinye ziyatholakala uma kunesidingo.
4. Ekugcineni, udinga ukushintsha umbala wangemuva wamaseli akhethiwe noma uwenze ngendlela oyifisayo nganoma iyiphi enye indlela. Ukuze wenze lokhu, sebenzisa indlela echazwe ngenhla.

Khumbula nje ukuthi izinguquko zokufometha ezenziwe ngale ndlela zizoqhubeka ngisho noma ugcwalisa izikhala noma ushintsha uhlobo lweseli olukhethekile. Yiqiniso, akunakwenzeka ukuthi othile uzofuna ukusebenzisa le ndlela, kodwa ngokwenza noma yini engenzeka.

Ungakuthola kanjani okuningi ku-Excel?

Njengabasebenzisi abakhulu be-Microsoft Excel, kufanele wazi ukuthi iqukethe izici eziningi. Abanye babo siyabazi futhi siyabathanda, kuyilapho abanye behlala bengaqondakali kumsebenzisi ojwayelekile, futhi inani elikhulu lama-blogger lizama ukukhanyisa okungenani kancane kubo. Kodwa kunemisebenzi evamile okuzodingeka ngamunye wethu ayenze, futhi i-Excel ayethuli ezinye izici noma amathuluzi ukuze enze izenzo ezithile eziyinkimbinkimbi.

Futhi isixazululo sale nkinga izengezo (ama-addon). Ezinye zazo zisakazwa mahhala, ezinye - ngemali. Kunamathuluzi amaningi afanayo angenza imisebenzi ehlukene. Isibonelo, thola izimpinda kumafayela amabili ngaphandle kwamafomula afihlekile noma amamakhro.

Uma uhlanganisa la mathuluzi nomsebenzi owumongo we-Excel, ungathola imiphumela emihle kakhulu. Isibonelo, ungathola ukuthi yimaphi amanani kaphethiloli ashintshile, bese uthola izimpinda efayeleni zonyaka odlule.

Siyabona ukuthi ukufometha okunemibandela kuyithuluzi eliwusizo elikuvumela ukuthi wenze ngokuzenzakalelayo umsebenzi kumathebula ngaphandle kwamakhono athile. Manje usuyazi ukuthi ugcwalisa kanjani amaseli ngezindlela eziningana, ngokusekelwe kokuqukethwe kwawo. Manje kusele kuphela ukukusebenzisa. Ngikufisela inhlanhla!