Ungayiguqula kanjani inombolo ibe umbhalo bese ubuyela emuva ku-Excel

Omunye wemisebenzi ejwayelekile ebhekene nabasebenzisi baka-Excel ukuguqula izinkulumo zamanani zibe yifomethi yombhalo bese emuva. Lo mbuzo uvame ukukwenza uchithe isikhathi eside uma umsebenzisi engazi i-algorithm ecacile yokwenza okuthile. Ake sibheke ukuthi ungaxazulula kanjani imisebenzi yomibili ngezindlela ezahlukahlukene.

Ukuguqulwa kombhalo ngefomu lombhalo

Wonke amaseli e-Excel anefomethi ethile ecacisa uhlelo, ukuthi ungakubheka kanjani lokhu noma le nkulumo. Isibonelo, noma ngabe amanani abhalwe kuyo, kepha ifomethi isethwe embhalweni, uhlelo lokusebenza luzobabheka njengombhalo olula, futhi ngeke lukwazi ukwenza ukubalwa kwezibalo ngedatha enjalo. Ukuze i-Excel ibone izinombolo ezifana nenombolo, kufanele zibhalwe endaweni eshidi ngefomethi evamile noma yenombolo.

Okokuqala, cabanga ngezinketho ezahlukahlukene zokuxazulula inkinga yokuguqulwa kwezinombolo ngefomu lombhalo.

Indlela 1: Ukufomatha ngemenyu yokuqukethwe

Imvamisa, abasebenzisi benza ukufometha kwezinombolo zibe umbhalo ngemenyu yokuqukethwe.

1. Sazisa lezo zinto zephepha lapho ufuna ukuguqula idatha ibe umbhalo. Njengoba singabona, kuthebhu yamakhaya, kubha yamathuluzi "kuboniswa evikini elikhethekile, imininingwane ekhethekile lezi zinto zinefomethi evamile, okusho ukuthi izinombolo ezibhalwe kuzo zitholwa uhlelo njengenombolo .



2. Ngokuchofoza inkinobho yegundane kwesokudla ekukhetheni nasemenyuni evula, khetha isikhundla se- "Cell format ...".



3. Ewindini lokufomatha elivula, liye kwithebhu ethi "Inombolo" uma ivulekile kwenye indawo. Ku-Izilungiselelo block "amafomethi ezinombolo", khetha isikhundla "sombhalo". Ukugcina izinguquko, chofoza inkinobho ethi "Kulungile" ezansi kwewindi.



4. Njengoba sibona, ngemuva kwalokhu kusebenza ngokukhohlisa, imininingwane iboniswa emkhakheni okhethekile amaseli aguqulwa abe yifomu lombhalo.



5. Kepha uma sizama ukubala i-autosumimu, izokhonjiswa esitokisini esingezansi. Lokhu kusho ukuthi ukuguqulwa bekungazenzi ngokuphelele. Lesi ngesinye sama-chips we-Excel. Uhlelo alukwenzi luqede ukuguqulwa kwedatha kuyindlela enembile kunazo zonke.



6. Ukuze siqede ukuguqulwa, sidinga ukuphindwe kabili ngenhla kwembumbulu yegundane ukubeka ikhesa kwisakhi ngasinye sokwehlukana bese ucindezela ukhiye we-ENTER. Ukwenza lula umsebenzi esikhundleni sokuchofoza kabili, ungasebenzisa ukhiye wokusebenza kwe-F2.



7. Ngemuva kokwenza le nqubo nawo wonke amaseli wendawo, imininingwane ebangelo izobonwa uhlelo njengezinkulumo zemibhalo, futhi, ngakho-ke, i-autosumma izoba ngu-zero. Ngaphezu kwalokho, njengoba ubona, ekhoneni eliphezulu lesokunxele lamaseli lizopendwe ngotshani. Kubuye kube uphawu oluqondile lokuthi izakhi lapho izinombolo zitholakala khona ziguqulwa zibe mbukiso. Yize lesi sici asihlali siphoqelekile futhi kwezinye izimo akukho phasi elinjalo.

Isifundo: Ungayishintsha kanjani ifomethi ku-Excel

Indlela 2: Amathuluzi ku-Ribbon

Ungaguqula futhi inombolo ibe yifomu lombhalo ngokusebenzisa amathuluzi etheyipini, ikakhulukazi, usebenzisa inkambu ukubonisa ifomethi lapho ingxoxo ephakeme ngayo.

1. Khetha idatha yezezalo lapho udinga ukuguqula ibe yifomu lombhalo. Ukuba sethebhu yasekhaya, chofoza isithonjana ngesimo sonxantathu ngakwesokudla kwenkambu lapho kuboniswa khona isimo. Itholakala kwithuluzi "lenombolo".



2. Uhlu lwezinketho zokufomatha, sikhetha "umbhalo".



3. Ngaphezu kwalokho, njengasendleleni eyedlule, sifaka njalo isikhombisi entweni ngayinye yobubanzi ngokuchofoza kabili inkinobho yegundane noma ngokucindezela ukhiye we-F2, bese uqhafaza ukhiye we-ENTER.

Idatha iguqulwa inketho yombhalo.

Indlela 3: Usebenzisa umsebenzi

Enye inketho yokuguqula idatha yezinombolo ezivivinyweni ku-Accelel ukusetshenziswa komsebenzi okhethekile obizwa ngokuthi - umbhalo. Le ndlela izohambisana, okokuqala, uma ufuna ukudlulisa izinombolo njengombhalo kwikholomu ehlukile. Ngaphezu kwalokho, izokonga isikhathi ekuguqulweni uma inani ledatha likhulu kakhulu. Ngemuva kwakho konke, uzovuma ukuthi ukunamathisela iseli ngalinye kububanzi bezinkulungwane noma izinkulungwane zemigqa akuyona indlela engcono kakhulu yokuphuma.

1. Faka isikhombisi entweni yokuqala yebanga lapho umphumela wokuguqulwa uzoboniswa khona. Chofoza isithonjana se- "Insert Function", esiseduzane nomugqa wefomula.



2. Iwindows Wizard Wisard iqala. Esigabeni esithi "Umbhalo" sabela into "Umbhalo". Ngemuva kwalokho, chofoza inkinobho ethi "Kulungile".



3. Kuvulwa iwindi lezimpikiswano lomqhubi. Lesi sici sine-syntax elandelayo:

   = Umbhalo (inani; ifomethi)

   Iwindi elivulekile linamasimu amabili ahambelana nalezi zingxabano: "Value" kanye "nefomethi".

   Ensimini "yenani", udinga ukucacisa inombolo eguquliwe noma isixhumanisi esisekweni elikhona lapho litholakala khona. Kuthu, lokhu kuzoba yinkomba yento yokuqala yohlobo lwenombolo yebanga elisetshenzisiwe.

   Ensimini "yefomethi", udinga ukucacisa inketho yokubonisa. Isibonelo, uma singena "0", inketho yombhalo endaweni ekhishwayo izokhonjiswa ngaphandle kwezimpawu zedesimali, noma ngabe zisemthonjeni. Uma siletha "0,0", umphumela uzoboniswa nge-decimal eyodwa, uma "0.00", bese unama-amabili, njll.

   Ngemuva kwawo wonke amapharamitha adingekayo afakiwe, chofoza inkinobho ethi "Kulungile".



4. Njengoba sikwazi ukubona, inani lento yokuqala yebanga elicacisiwe laboniswa esitokisini, esabelwe esigabeni sokuqala sale ncwadi. Ukuze udlulise amanye amanani, udinga ukukopisha ifomula ezintweni eziseduze nezeshidi. Sisungula isikhombisi esiya engxenyeni engezansi efanelekile yento equkethe ifomula. Isikhombisi siguqulwa sibe uphawu lokugcwalisa, ukuba nesiphambano esincane. Cindezela inkinobho yegundane kwesobunxele bese uhudula kumaseli angenalutho afana nebanga lapho idatha yomthombo itholakala khona.



5. Manje lonke uhla lugcwele idatha edingekayo. Kepha akupheleli lokho. Eqinisweni, zonke izakhi zebanga elisha ziqukethe amafomula. Sabela le ndawo bese sichofoza isithonjana esithi "Kopisha", esitholakala kuthebhu yasekhaya ku-TOLT RIBBon kuya kwi-Exchange Buffer.



6. Ngaphezu kwalokho, uma sifuna ukugcina womabili amabhendi (umthombo futhi siguqulwe), ungasusi ukukhethwa endaweni, okuqukethe amafomula. Chofoza inkinobho yegundane kwesokudla. Uhlu lwezenzo zethulwe. Khetha isikhundla esithi "Ukufaka Okukhethekile" kukho. Phakathi kwezinketho zesenzo ohlwini oluvula, khetha "amanani namafomethi ezinombolo".

   

   Uma umsebenzisi efuna ukubuyisela idatha yomthombo esikhundleni sezenzo ezibekiwe, kuyadingeka ukuyikhetha futhi wenze ukufakwa kufakwe indlela efanayo nangaphezulu.



7. Kunoma ikuphi, idatha kwifomu lombhalo izofakwa ebangeni elikhethiwe. Uma usakhetha ukufaka ukufaka endaweni yomthombo, amaseli aqukethe amafomula angahlanzwa. Ukuze wenze lokhu, zikhethe ngokuchofoza inkinobho yegundane efanele bese ukhetha isikhundla esithi "Sula Okuqukethwe".

Kule nqubo yokuguqula kungabhekwa ngaphezulu.

Isifundo: Imisebenzi yeWizard ku-Excel

Ukuguqulwa kombhalo kunombolo

Manje ake sibheke ukuthi yiziphi izindlela ongayenza ngazo impendulo, okungukuthi ungaguqula umbhalo ube yinombolo e-Excel.

Indlela 1: Ukuguqulwa kusetshenziswa isithonjana sephutha

Kulula futhi kusheshe ukuguqula inketho yombhalo usebenzisa isithonjana esikhethekile esibika iphutha. Lesi sithonjana sinombono umaki wokuzibandakanya, obhalwe kusithonjana sesithonjana. Kuvela lapho ukhetha amaseli anamaki ekhoneni eliphezulu kwesobunxele, umbala oluhlaza olwadingidwa ngathi phambilini. Lolu phawu alukhombisi ukuthi imininingwane eselinambithekayo empeleni iyiphutha. Kepha izinombolo ezitholakala esitokisini ezinefomu lombhalo zidala izinsolo zohlelo ukuthi imininingwane kungenzeka ingalungile. Ngakho-ke, uma kwenzeka, ubamba ukuze umsebenzisi anaka. Kepha ngeshwa, amamaki anjalo awahlali anikezwa noma ngabe izinombolo zethulwe njalo ngefomu lombhalo, ngakho-ke indlela echaziwe ayilungele wonke amacala.

1. Khetha iseli equkethe inkomba eluhlaza ngephutha okungenzeka. Chofoza isithonjana esivela.



2. Uhlu lwezenzo luyavula. Khetha kuyo inani "Guqula kunombolo".



3. Encwadini enikezelwe, imininingwane izoguqulwa ngokushesha ibe ukubukeka kwezinombolo.

Uma amanani anjalo wombhalo, okufanele aguqulwe, hhayi noyedwa, bese kusethi, khona-ke kuleli cala ungasheshisa inqubo yokuguqula.

1. Silwa lonke uhla lapho idatha yombhalo itholakala khona. Njengoba ubona, i-pictogram yavela eyodwa kuyo yonke indawo, hhayi iseli ngalinye ngokwahlukana. Chofoza kuyo.



2. Kuvulwe vele ulujwayele Uhlu lwe-US. Njengoba okokugcina, khetha "ukuguqula kunombolo" yenombolo.

Yonke imininingwane ye-array izoguqulwa ibe ngumbono oshiwo.

Indlela 2: Ukuguqulwa kusetshenziswa iwindi lokufomatha

Ngokuqondene nokuguqula idatha kusuka kuhlobo lwenombolo kuya embhalweni, kukhona amandla okubuyisela ukuguqulwa ngewindi lokufometha.

1. Khetha ibanga eliqukethe izinombolo kunguqulo yombhalo. Chofoza inkinobho yegundane kwesokudla. Kumenyu yokuqukethwe, khetha isikhundla esithi "Ifomethi Yeseli ...".



2. Ukufometha iwindi kuyabaleka. Njengasesikhathini esedlule, iya kuthebhu ethi "Numbe,". Eqenjini elithi "Izinombolo Zamafomethi", kudingeka sikhethe amanani azokuvumela ukuthi uguqule umbhalo ube yinombolo. Lokhu kufaka phakathi izinto ezijwayelekile "kanye" nezinombolo ". Noma yini oyikhethile, uhlelo luzobheka izinombolo ezingeniswe esitokisini njengezinombolo. Sikhiqiza ukukhetha bese sichofoza inkinobho. Uma ukhethe inani elithi "Izinombolo", ngakwesokunene sewindows icala amandla okuguqula isethulo senombolo: Setha inani lezindawo zedesimali ngemuva kwekhekhe, setha abahlukanisi phakathi kokukhishwa kwemali. Ngemuva kokuthi isethaphu yenziwe, cindezela inkinobho ethi "Kulungile".



3. Manje, njengasesimweni sokuguqula inombolo embhalweni, sidinga ukudlula kuwo wonke amaseli ngokufaka isikhombisi kulowo nalowo bese sichofoza inkinobho ethi ENTER ngemuva kwalokho.

Ngemuva kokwenza lezi zenzo, wonke amanani ohlu olukhethiwe aguqulwa ezinhlotsheni esizidingayo.

Indlela 3: Ukuguqulwa ngokusebenzisa amathuluzi we-tape

Ungahumusha idatha yombhalo ibe nezinombolo ngokusebenzisa inkambu ekhethekile kubha yamathuluzi.

1. Sigqamisa ububanzi obekufanele baguqulwe. Iya kuthebhu "Yasekhaya" ethepheni. Chofoza enkundleni ngokukhethwa kwefomethi eqenjini "Nenombolo". Khetha into "yezinombolo" noma "evamile".



2. Okulandelayo, uma nje sichaza ngendlela echazwe yi-US iseli ngalinye lendawo eguquliwe lisebenzisa i-F2 bese ufaka okhiye.

Amanani ebangeni azoguqulwa kusuka kumbhalo kuya ezinhlamvwini.

Indlela 4: Ukusetshenziswa kwefomula

Futhi ukuguqula amanani wombhalo kunombolo, ungasebenzisa amafomula akhethekile. Cabanga ukuthi ungakwenza kanjani ukukwenza.

1. Esitokisini esingenalutho, etholakala ngokuhambisana nento yokuqala yobanga, okufanele iguqulwe, faka uphawu "olulinganayo" (=) nophawu oluphindwe kabili (-). Okulandelayo, chaza ikheli lento yokuqala yebanga eliguqukayo. Ngakho-ke, ukuphindisela okuphindwe kabili yi- "-1". Njengoba wazi, ukubuyabuyelela kwe- "minus" ku "Minus" kunika "Plus". Lokho wukuthi, sithola inani elifanayo kwiseli eliqondiwe ukuthi ekuqaleni, kepha vele ngefomu lezinombolo. Le nqubo ibizwa nge-Double Binary Dencial.



2. Sichofoza kukhiye wokungena, ngemuva kwalokho sithola inani elilungisiwe elilungiselelwe. Ukuze usebenzise le formula yawo wonke amanye amabanga obubanzi, sisebenzisa umaki wokugcwalisa, osetshenziswa ngaphambili yithi ngombhalo wombhalo.



3. Manje sinebanga eligcwele amanani ngamafomula. Siyiqokomise bese uchofoza inkinobho ethi "Kopisha" kuthebhu yasekhaya noma usebenzise inhlanganisela ye-CTRL + C.



4. Khetha indawo yomthombo bese uqhafaza kuyo chofoza kwesokudla. Uhlu lomongo olwenziwe aculiwe, dlula ezintweni "Faka okukhethekile" kanye "namanani namafomethi ezinombolo".



5. Yonke imininingwane ifakwa kwifomu esilidingayo. Manje usungasusa uhla lwe-Transit lapho ifomula ephindwe kanambambili yokuphika kanambambili itholakala khona. Ukuze senze lokhu, sabela le ndawo, ngenkinobho yegundane efanele, shayela imenyu yokuqukethwe bese ukhetha isikhundla "esicacile" kuyo.

Ngendlela, ukuguqula amanani ngale ndlela, akudingekile nakancane ukusebenzisa ukuphindaphindeka kabili kabili ku- "-1". Ungafaka noma iyiphi enye isenzo se-arithmetic engaholeli ekushintsheni kwamanani (ukwengeza noma ukususa kwe-zero, ukwenziwa kokwakhiwa kwezinga lokuqala, njll.)

Isifundo: Ungayenza kanjani i-autocomplete ku-Excel

Indlela 5: Ukusetshenziswa kokufakwa okukhethekile

Le ndlela elandelayo ngomgomo wokusebenza ifana kakhulu neyedlule ngomehluko kuphela ongadingi ukudala ikholomu eyengeziwe.

1. Kunoma yisiphi iseli engenalutho eshidini, faka inombolo "1". Ngemuva kwalokho siyiqokomisa bese uchofoza isithonjana se- "Copy" esijwayelekile ethepheni.



2. Khetha indawo eshidini elizoguqulwa. Chofoza inkinobho yegundane kwesokudla. Kumenyu evula, chofoza kabili entweni ethi "Ukufaka Okukhethekile".



3. Ewindini elisezingeni eliphakeme lokufaka, setha ukushintshwa kwebhulokhi "ekusebenzeni" kwisikhundla esithi "SUPPLPPL". Ukulandela lokhu sicindezela inkinobho ethi "KULUNGILE".



4. Ngemuva kwalesi senzo, wonke amanani wendawo ekhethiwe azoguqulwa abe yizinombolo. Manje, uma ufisa, ungasusa inombolo "1", esasisetshenziselwa ukuguqula izinhloso.

Indlela 6: Isebenzisa ithuluzi "Umbhalo Ikholomu"

Enye inketho lapho ungaguqula khona umbhalo ngefomu lezinombolo ukusetshenziswa kwethuluzi "lombhalo". Kunengqondo ukuthi usebenzise lapho, esikhundleni sekhefana, iphuzu lisetshenziswa njengehlukanisi sezimpawu zedesimali, nanjengehlukaniswa lokudilizwa esikhundleni se-apostrophe. Le nketho ibonwa kwi-Excel ekhuluma isiNgisi, njengenombolo yezinombolo, kepha enguqulo yolimi lwesiRussia yalolu hlelo, wonke amanani aqukethe izimpawu ezingenhla athathwa njengombhalo. Vele, ungadlulisa idatha ngesandla, kepha uma kukhona abaningi babo, kuzothatha isikhathi esiningi, ikakhulukazi ngoba kungenzeka ukwenza ikhambi elisheshayo lenkinga.

1. Sabela ucezu lweshidi, okuqukethwe okudingeka uguqule. Iya kuthebhu "yedatha". Ethempelini lensimbi ku- "Ukusebenza nedatha" block ngokuchofoza isithonjana se- "Columm Umbhalo".



2. Yethula imibhalo emihle. Ewindini lokuqala, sicela wazi ukuthi ukushintshwa kwefomethi yedatha kumi engxenyeni ethi "NABANYE NAMAHHALA". Ngokuzenzakalelayo, kufanele kube kulesi sikhundla, kepha ngeke kube ngaphezu kokuhlola ukubheka isimo. Bese uchofoza inkinobho "elandelayo".



3. Ewindini lesibili, sishiya nakho konke kungashintshiwe bese uqhafaza inkinobho "elandelayo".



4. Kepha ngemuva kokuvula iwindi lesithathu, i-Wizard yombhalo idinga ukuchofoza inkinobho ethi "Imininingwane eminingi".



5. Kuvulwa iwindi lokucushwa kombhalo olwengeziwe. Ensimini "isihlukanisi sengxenye ephelele nengxenye", sibeka iphuzu, futhi esigabeni esithi "ukuhlukaniswa kwabakhiphekayo" - Isiphambulweni. Ngemuva kwalokho senza okukodwa inkinobho ethi "KULUNGILE".



6. Sibuyela ewindini lesithathu le-Wizard yombhalo bese uchofoza inkinobho ethi "Qede".



7. Njengoba ubona, ngemuva kokwenza lezi zenzo, inombolo ithathe ifomethi ejwayelekile kwinguqulo yolimi lwesiRussia, okusho ukuthi baguqulwe ngasikhathi sinye besuka kwimininingwane yombhalo ibe yinombolo.

Indlela 7: Ukusetshenziswa kweMacros

Uma kaningi kufanele uguqule izindawo ezinkulu zedatha kusuka kwifomethi yombhalo kunombolo yezinombolo, khona-ke kunencazelo yale njongo ukurekhoda i-macro ekhethekile ezosetshenziswa uma kunesidingo. Kepha ukuze wenze lokhu, okokuqala, udinga ukufaka ama-macros kanye nephaneli kanjiniyela kunguqulo yakho ye-Excel, uma namanje akwenziwanga.

1. Iya kuthebhu kanjiniyela. Sichofoza isithonjana kwitheyipu eyisisekelo ebonakalayo, etholakala eqenjini le- "Code".



2. Isihleli seMacros esijwayelekile siyasebenza. Shayela noma ukopishe inkulumo elandelayo kuyo:

   I-Sub Text_v_)

   Ukukhetha.Nnumberformat = "Okuvamile"

   Ukukhetha.Value = Ukukhetha.Value.

   Qeda sub.

   Ngemuva kwalokho, vala umhleli ngokucindezela inkinobho yokuvala ejwayelekile ekhoneni eliphezulu kwesokudla kwewindi.



3. Khetha ucezu ekhasini elizoguqulwa. Chofoza isithonjana seMacros, esitholakala kuthebhu kanjiniyela eqenjini lekhodi.



4. Ama-macros aqoshwe enguqulweni yakho yohlelo lwe-macro. Sithola i-macro enegama elithi "umbhalo_v_ch), qokomisa bese uchofoza inkinobho ethi" Run ".



5. Njengoba sibona, kunokuguqulwa kwenkulumo yombhalo ngefomethi yezinombolo.

Isifundo: Ungawakha kanjani i-macro ku-Excel

Njengoba ubona, kunezindlela ezimbalwa impela zokuguqula izinombolo ze-Excel ezibhalwe kunguqulo yezinombolo ngendlela yombhalo nangaphandle. Ukukhethwa kwendlela ethile kuya ngezinto eziningi. Okokuqala, lo msebenzi. Ngemuva kwakho konke, ngokwesibonelo, ungaguqula ngokushesha inkulumo yombhalo nabadidiyeli bangaphandle kube yinombolo kuphela ngokusebenzisa ithuluzi "lombhalo". Isici sesibili esithinta ukukhetha kwezinketho yivolumu nemvamisa yezinguquko ezenziwayo. Isibonelo, uma uvame ukusebenzisa izinguquko ezinjalo, kunengqondo ukurekhoda i-macro. Futhi isici sesithathu yinto elula yomsebenzisi.