Ikheli eliphelele ku-Excel: 2 Indlela efakazelwe

Njengoba wazi, kunezinhlobo ezimbili zokukhuluma kumatafula we-Excel: Isihlobo kanye ne-ABSOLULE. Esimweni sokuqala, ireferensi iyahlukahluka ekuqondisweni kokukopisha inani lokushintsha okuhlobene, kanti okwesibili, lihleliwe futhi lapho ukukopishwa kuhlala kungashintshiwe. Kepha ngokuzenzakalelayo, wonke amakheli e-Excel aphelele. Ngasikhathi sinye, imvamisa kunesidingo sokusebenzisa ikheli eliphelele (eliguquliwe). Ake sibheke ukuthi yiziphi izindlela lokhu kungenziwa.

Ukusetshenziswa kwekheli eliphelele

Enkulumweni ephelele esingase siyidinge, ngokwesibonelo, esimweni lapho sikopisha ifomula, ingxenye eyodwa yazo equkethe ukuguquguquka okuboniswe ngenani lezinombolo, kanti owesibili unenani eliqhubekayo. Okungukuthi, le nombolo idlala indima ye-coeffrent eqhubekayo, lapho umsebenzi othile okufanele wenziwe khona (ukuphindaphinda, ukuhlukaniswa, njll.) Yenombolo yokuguquguquka.

E-Excel, kunezindlela ezimbili zokusetha ikheli elihleliwe: ngokwakha isixhumanisi esiphelele nangosizo lomsebenzi we-FVS. Ake sibheke ngayinye yalezi zindlela ngokuningiliziwe.

Indlela 1: Isixhumanisi esiphelele

Vele, indlela edume kakhulu futhi esetshenziswa kaningi yokwakha ikheli eliphelele ukusetshenziswa kwezixhumanisi eziphelele. Izethenjwa eziphelele zinomehluko hhayi kuphela osebenza, kodwa futhi synntax. Ikheli elihlobene ne-syntax enjalo:

= A1.

Ekhelini elinqunyelwe ngaphambi kwenani lokuxhumanisa libeka uphawu lwedola:

= $ A $ 1

I-Dollar Sign ingafakwa ngesandla. Ukuze wenze lokhu, udinga ukufaka isikhombisi ngaphambi kwenani lokuqala lezixhumanisi zekheli (elivundlile) elisetolingeni noma efomula emugqeni. Okulandelayo, ekwakhekeni kwekhibhodi ekhuluma isiNgisi, chofoza ukhiye we- "4" ecaleni eliphezulu (ngokhiye we- "Shift"). Kulapho lapho uphawu lwedola lutholakala. Ngemuva kwalokho udinga ukwenza inqubo efanayo nezixhumanisi ze-vertical.

Kunendlela esheshayo. Udinga ukusetha isikhombisi esitokisini lapho ikheli litholakala khona bese uchofoza kukhiye womsebenzi we-F4. Emva kwalokho, isibonakaliso sedola sizovela ngaso leso sikhathi ngasikhathi ngaphambi kokuxhuma ngokuvundlile nangokuthe mpo ikheli leli kheli.

Manje ake sibheke ukuthi ikheli eliphelele lisetshenziswa kanjani ekusebenziseni ngokusebenzisa izixhumanisi eziphelele.

Thatha itafula lapho kubalwa khona umholo wabasebenzi. Ukubalwa kwenziwa ngokuphindaphinda ubukhulu bomholo wabo uqobo ku-coefficied ehleliwe, okufanayo kubo bonke abasebenzi. I-coefficient yakho uqobo itholakala esitokisini seqabunga elihlukile. Sibhekene nomsebenzi wokubala inkokhelo yabo bonke abasebenzi ngokushesha okukhulu ngangokunokwenzeka.

1. Ngakho-ke, esitokisini sokuqala sekholomu yeholo, sethula ifomula ngokuphindaphinda izinga lomsebenzi ofanele kwinhlawulo. Endabeni yethu, le formula inalolu hlobo:

   = C4 * g3



2. Ukubala umphumela ophelile, chofoza ukhiye we-ENTER kukhibhodi. Umphumela uboniswa esitokisini esiqukethe ifomula.



3. Sibala inani leholo lesisebenzi sokuqala. Manje sidinga ukukwenza kuyo yonke eminye imigqa. Vele, ukuhlinzwa kungabhalwa kwikholamu ngayinye "yeholo" ikholamu ngesandla, kwethula ifomula elifanayo elinokuchitshiyelwa kwe-offset, kepha sinomsebenzi ngokushesha okukhulu ukubala, futhi okokufaka okwenziwe ngesandla kuzothatha omkhulu isikhathi sesikhathi. Futhi kungani uchitha imizamo ekufakweni kwesandla, uma ifomula imake nje ikopishwe kwamanye amaseli?

   Ukukopisha ifomula, sisebenzisa ithuluzi elinjalo njengophawu lokugcwalisa. Siba isikhombisi ekhoneni elingezansi kwesokudla seseli, lapho liqukethe. Ngasikhathi sinye, isikhombisi uqobo kufanele siguqule sibe uphawu lo mfanekiso kakhulu wokugcwalisa ngesiphambano. Cindezela inkinobho yegundane kwesobunxele bese udonsa isikhombisi phansi kuze kube sekupheleni kwetafula.



4. Kepha, njengoba sibona, esikhundleni sokubalwa okulungile kwamaholo kwabanye abasebenzi, sathola ama-zeros athile.



5. Sibheka ukuthi yini imbangela yalo mphumela. Ukuze wenze lokhu, sigqamisa iseli lesibili kwikholomu yeholo. Intambo yefomula ikhombisa inkulumo ehambisanayo kuleli ngliya. Njengoba ukwazi ukubona, i-Factor yokuqala (C5) ihambelana nezinga lalowo msebenzi esilindelwe yiholo lakhe. Hlanganisa ukuthutha uma kuqhathaniswa neseli langaphambilini ngenxa yezakhiwo zokuhlobana. Kodwa-ke, ngokuqondile leli cala, lokhu kuyadingeka kithina. Ngenxa yalokhu kuphindaphindeka kokuqala, izinga lomsebenzi esiwudingayo. Kepha ukuxhumanisa ukuthutha kwenzeka ngesici sesibili. Futhi manje ikheli laso lisho ukuthi lingekho ku-coefficient (1.28), kodwa esitokisini esingenalutho ngezansi.

   

   Lesi yisizathu sokubala iholo labasebenzi abalandelayo abavela kulolo hlu belitholakale lungalungile.

6. Ukuze silungise isimo, kudingeka sishintshe ukubhekana nokukhulunywa kwesiphindaphindi sesibili ngesihlobo sokuhleliwe. Ukuze senze lokhu, sibuyela esitokisini sokuqala sekholomu "yeholo", sigqamisa. Okulandelayo, sithuthela entanjeni yamafomula lapho inkulumo esidinga khona iboniswa khona. Sigqamisa isikhombisi isici sesibili (G3) bese sicindezela ukhiye othambile kwikhibhodi.



7. Njengoba sibona, kwavela isibonakaliso sedola eduze kwezixhumanisi zesibili, futhi lokhu, njengoba sikhumbula, kuyisici sokukhuluma ngokuphelele. Ukubonisa umphumela esikrinini esichofoza kukhiye wokungena.



8. Manje, njengakuqala, sibiza umaki ogcwalisiwe ngokufaka isikhombisi esiya engxenyeni engezansi efanelekile yento yokuqala yekholomu yeholo. Cindezela inkinobho yegundane kwesobunxele bese uyidonsa.



9. Njengoba sibona, kulokhu ukubalwa kwenziwa kahle futhi inani lemiholo yabo bonke abasebenzi bebhizinisi ibalwa kahle.



10. Bheka ukuthi ifomula lalikopishwa kanjani. Ukuze wenze lokhu, sabela into yesibili yekholomu yeholo. Sibheka le nkulumo etholakala efomula emugqeni. Njengoba sibona, izixhumanisi iyisici sokuqala (C5), okuyinto namanje isihlobo, kwashukumisela kuqhathaniswa iseli odlule komunye iphuzu phansi thwi. Kodwa isici sesibili ($ G $ 3), ekhuluma esiphila okwenziwe fixed ayizange ithuthuke.

I-Excel futhi isebenzisa lokho okubizwa ngokuthi yikheli elihlanganisiwe. Kulokhu, kungaba ikholamu noma intambo iqoshwe ekhelini elehlukile. Lokhu kutholakala ngendlela yokuthi uphawu lwedola lubekwe kuphela ngaphambi nje kokukodwa kwekheli izixhumanisi. Nasi isibonelo sesixhumanisi esixubile esixubekile:

= A $ 1

Leli kheli libuye libhekwa njengelixubekile:

= $ A1

Okungukuthi, abangabantwana ngokuphelele ekhuluma e isixhumanisi okuxubile isetshenziswa kuphela omunye Ukudidiyela amagugu amabili.

Ake sibone ukuthi onjalo isixhumanisi okuxubile singasebenza umkhuba ngesibonelo etafuleni iholo elifanayo abasebenzi ibhizinisi.

1. Njengoba singabona, ngaphambili sakwenza ukuze zonke izixhumanisi yesibili Okuphidaphindayo babe ngokuphelele ekhuluma. Kodwa isibalo let it uma amanani kumele lulungiswe kulokhu? Njengoba sibona, lapho ukukopisha, ukwehla okuthe mpo kwenzeka, futhi izixhumanisi ezivundlile zihlala zingashintshiwe. Ngakho-ke, kungenzeka kakhulu ukufaka ikheli eliphelele kuphela kwizixhumanisi zomugqa, futhi izixhumanisi zekholomu kufanele zishiywe njengoba zinjalo okuzenzakalelayo - Isihlobo.

   Sabela into yokuqala yekholomu yeholo kanye nentambo yefomula, senza lokhu okungenhla. Sithola ifomula ngohlobo olulandelayo:

   = C4 * g $ 3

   Njengoba sikwazi ukubona, ukubhekisisa okulungiselelwe ku-Multiplier yesibili kusebenza kuphela maqondana nezixhumanisi zentambo. Ukubonisa umphumela kuselula chofoza inkinobho ethi Faka.



2. Ngemuva kwalokho, ngophawu lokugcwalisa, kopisha le formula ebangeni lamaseli, elitholakala ngezansi. Njengoba ukwazi ukubona, ukubalwa kwamaholo kubo bonke abasebenzi kwenziwa kahle.



3. Sibheka ukuthi ifomula elikopishelwe liboniswa kanjani esitokisini sesibili sekholomu esikwenzayo ukukhohlisa. Njengoba kungabhekwa emugqeni wefomula, ngemuva kokukhethwa kwalesi sakhi seshidi, yize iqiniso lokuthi ikheli eliphelele lesici sesibili lalihle kakhulu, izixhumanisi zokuxhumanisa ikholomu akwenzekanga. Lokhu kungenxa yokuthi senze ukukopisha hhayi ngokuvundlile, kodwa ngokuqondile. Ukube besikopisha ngokuvundlile, khona-ke esimweni esifanayo, ngokuhlukile, bekuzodingeka senze ukulungiswa okuhleliwe kwezixhumanisi zekholomu, futhi imigqa le nqubo izokhethwa.

Isifundo: Izixhumanisi Eziphelele Nokulingana ku-Efpelle

Indlela 2: Umsebenzi wesibili

Ngendlela yesibili yokuhlela ukubhekisisa okuphelele etafuleni le-Excel ukusetshenziswa kwe-OPPER DVSL. Lesi sici sibhekisa eqenjini labasebenzi abasebenza ngaphakathi "kanye nama-arrays". Umsebenzi wayo ukwakha ireferensi esitokisini esibekiwe ngokukhishwa komphumela kuleyo nto yephepha lapho opharetha ngokwawo. Ngasikhathi sinye, isixhumanisi sinamathiselwe kwizixhumanisi ezinamandla kakhulu kunanini lapho usebenzisa uphawu lwedola. Ngakho-ke, kwesinye isikhathi kuthathwa ukufona izikhombo usebenzisa i-Deltnsyl "Super". Lo opharetha une-syntax elandelayo:

= DWARF (isixhumanisi__enanechair; [A1])

Umsebenzi unezimpikiswano ezimbili, owokuqala wawo onesimo esigunyaziwe, kanti owesibili awekho.

Isixhumanisi sokuphikisana "esitokisini" kubhekiselwa kwinto yeshidi le-Excel Element ngendlela yombhalo. Okusho ukuthi, lesi yisixhumanisi esivamile, kepha isiboshwa ngezingcaphuno. Lokhu yikho ngqo okukuvumela ukuthi uqinisekise izakhiwo zekheli elihluke ngokuphelele.

Ingxabano "A1" iyakhethwa futhi isetshenziswa emacaleni angavamile. Isicelo saso siyadingeka kuphela lapho umsebenzisi ekhetha enye inketho yokulungisa ehlukile, hhayi ukusetshenziswa okujwayelekile kokuxhumanisa ngohlobo "A1" (amakholomu anokuqokwa kwama-alfabhethi, futhi imigqa iyidijithali). Enye indlela ikhombisa ukusetshenziswa kwesitayela se- "R1c1", lapho amakholomu, njengezintambo, aboniswa ngamanani. Ungashintshela kule ndlela yokusebenza ngewindi le-Excel Parameters. Khona-ke, usebenzisa DVSSL opharetha, njengoba i-agumenti "A1" kumele icaciswe ukubaluleka "Lala". Uma usebenza kwimodi yokubonisa ye-e ejwayelekile, njengaboningi labasebenzisi, bese kuba yingxabano "A1" Ungacacisa inani "iqiniso". Kodwa-ke, leli nani lisho ukuthi ngokuzenzakalelayo, kulula kakhulu ngokujwayelekile kulokhu, impikiswano "A1" ayicacisi.

Ake sibheke ukuthi ikheli eliphelele lizosebenza kanjani, lihlelwe umsebenzi we-DVRSL, esibonelweni setafula lethu le-Salary.

1. Siveza ukukhetha kwento yokuqala yekholomu yeholo. Sibeka uphawu "=". Njengoba khumbulani, isici sokuqala ecacisiwe ukubala ifomula kufanele zethulwe ikheli isihlobo. Ngakho-ke, vele uchofoze esitokisini esiqukethe inani elihambisanayo lomholo (C4). Ukulandela indlela ikheli layo elikhonjiswa ngayo entweni ukukhombisa umphumela, chofoza inkinobho "yokuphindaphinda" (*) kwikhibhodi. Ngemuva kwalokho sidinga ukuya ekusebenziseni i-opharetha i-DVSSLL. Yenza isithonjana esithi "Incress Function".



2. Efasiteleni elisebenzayo le-Master of Master, iya esigabeni "izixhumanisi kanye nama-arrays". Phakathi kohlu olunegama lwamagama, sabela igama elithi "DVSSL". Bese uchofoza inkinobho ethi "KULUNGILE".



3. Ukwenza kusebenze kwezimpikiswano zezimpikiswano ze-opharetha ze-DVSSL zenziwa. Siqukethe izinkambu ezimbili ezihambelana nezimpikiswano zalo msebenzi.

   Sibeka isikhombisi kusixhumanisi esitokisini. Vele uchofoze ukuthi isici ishidi, lapho Coefficient itholakala ukubala iholo (G3). Ikheli lizovela ngokushesha enkundleni ye-Bussage Windon. Ukube besibhekene nomsebenzi ojwayelekile, khona-ke kula makheli kungabhekwa kugcwaliswe, kepha sisebenzisa umsebenzi we-FVS. Njengoba sikhumbula, amakheli kuwo kufanele abe nohlobo lombhalo. Ngakho-ke, phendukisa izixhumanisi ezitholakala endimeni yewindi, izingcaphuno.

   Njengoba sisebenza esezingeni Ukudidiyela ekubonisweni mode, bese emkhakheni "A1" shiya kungenalutho. Chofoza inkinobho ethi "KULUNGILE".



4. Isicelo senza ukubala nokukhipha umphumela ku-sheet element equkethe ifomula.



5. Manje senza ukukopisha leli formula kuwo wonke amanye amaseli ekholomu yeholo ngomaki wokugcwalisa, njengoba senza ngaphambili. Njengoba ubona, yonke imiphumela yabalwa kwesokudla.



6. Ake sibheke ukuthi ifomula liboniswa kanjani kwelinye lamangqamuzana, lapho lalikopishwa khona. Sigqamisa into yesibili yekholomu bese sibheka intambo yefomula. Njengoba ubona, isici sokuqala, okuyinkomba yesihlobo, isishintshe izixhumanisi zayo. Ngasikhathi sinye, ukuphikisana kwesici sesibili, esimelwe umsebenzi wokugcwalisa, sahlala singashintshiwe. Kulokhu, kwakusetshenziswa inqubo elungiselelwe elungiselelwe.

Isifundo: I-Operator Deltnsil e-Ethelela

Ukubhekana ngokuphelele namatafula we-Excel kunganikezwa ngezindlela ezimbili: ukusetshenziswa komsebenzi DHRSL kanye nokusetshenziswa kwezixhumanisi eziphelele. Kulokhu, umsebenzi uhlinzeka ngokubopha okuqinile ekhelini. Ikheli elihlukile eliphelele lingasetshenziswa futhi kusetshenziswa izixhumanisi ezixubile.