Isalathisi esipheleleyo kwi-Excel - inkcazo, imizekelo.

Naluphi na, kwiphakheji eyisiseko "iMicrosoft Office" iqukethe i- Excel spreadsheet enamandla (Excel). Uluhlu lwemisebenzi ekhoyo kulo luyamangalisa: ukusuka kwimeko yokuhlunga kunye nokucoca idatha kunye nokuphela kwesithuba sokwakha kwisishwankathelo sabo sishwankathelo. Kwaye akunakwenzeka ukuba ucinge nayiphi na idatha egcinwe kumhleli ngaphandle kweefomula kunye nemigqa ebalwa ngokusebenzisa iifomula.

Iifomula kwi "Excel"

Nayiphi na ifom umyalelo oqulethe inkcazo yezenzo ezithatha ngayo iseli. Kwaye xa umsebenzisi ehlangabezana nesidingo sokukopisha, "welula" ifomula kuwo wonke umgca okanye ikholomu, uyaqonda ukuba kwezinye iimeko inxalenye yolu hlobo okanye ifomu kufuneka ihlale ingaguqukanga. Ngoko umsebenzisi ufunda ukuba kukho iingxelo ezipheleleyo kunye nezihambelanayo kwi-Excel . Cinga ngezi ngcamango ngokungakumbi.

Isalathiso esichaphazelekayo

Khumbula, yintoni isalathisi esinxulumene ne-Excel, elula. Olu hlobo lwekhonkco lukhethwa ngokusilelayo kwaye luguqula ixabiso lalo xa ludonsa (ukukopisha) ifomu kumaseli angqiniweyo kungakhathaliseki ukuba ukukopisha kunye namacwecwe okanye imigqa.

Makhe siqwalasele umzekelo olula. Yakha isethi encinane yedatha, equkethe iikholamu ezimbini: "Umqeshwa" kunye "nomvuzo":

Kwimimiselo ye "Excel" sibeka isicatshulwa se-A1 yesalathisi - oku kuthethwa ngokufanelekileyo kwaye kuyaqondakala kubasebenzisi abaninzi. Apha A, B, C ngamagama ekholomu, kwaye imigqa ibalwa. Ngaloo ndlela, iseli kunye nedatha "i-Zakirova EM" idilesi - A4. Olu luhlu oluncinane luya kufuneka xa siqala ukuqonda iingxelo kwiifomula.

Ngoku makhe sicinge ukuba sifuna ukubala imivuzo yomqeshwa ngamnye ngenxa yemali yomvuzo. Faka uphawu olulinganayo kwiseli C2 uze ufake ifom elandelayo (ngokusekelwe kwingcaciso yokuba umvuzo ungama-40% womvuzo): = B2 * 100/40. Nantsi iB2 ngumvuzo womqeshwa wokuqala etafileni. Cindezela Faka kwaye uhambise isalathisi semouse ukuya kumgca osezantsi ongaphantsi kweseli C2, elinde ukuba isalathisi sithathe ifom yomgca omnyama omnyama. Gcina phantsi iqhosha le mouse, donsela ifom phantsi ukuya ekupheleni kwetafile, oko kukuthi, kwiseli C7 (ungatshintsha le nyathelo ngokuchofoza kabini ngakwesokudla kweseli). Ikholamu ngokuzenzekelayo ihlala yile nkcukacha elandelayo:

Iifomula zedatha yeseli ziza kuba ngale ndlela:

Njengoko ubona, xa solule i-formula ngaphantsi, igama lekholomu lahlala lingatshintshi (B), kodwa inamba yomgca yatshintsha ngokulandelana. Ngokufanayo, ngokukopisha i-formula eqondekileyo, sithola ixabiso elifanayo kumqolo xa inombolo yekholomu iguquka. Ngako oko, isalathisi sabizwa ngokuba "isihlobo" - iikopi zefomula yokuqala engenayo iya kutshintsha isalathisi esichaphazelekayo kwisimo sayo kwinqanaba leeseli zamaphepha.

Njengoko uyakubona, akunzima kunzima ukuqonda ukuba isalathisi esinxulumene ne-Excel.

Ngoku siphendukela kule mizekelo elandelayo.

Ikhonkco iphela

Isalathiso esingenanto kwi-Excel yilona hlobo oluqhelekileyo lweekhonkco. Kule meko, xa ukukopisha ifomu, umqolo kunye nekholomu zichazwe kwifom.

Ngokuqinisekileyo, ukubhekisela kwedwa kuphela akusetyenziswanga yedwa, kuba akuyiyo ingqiqo ukuyikopisha. Ngako oko, olu hlobo luqhelekileyo kwiifomula ezidibeneyo, apho inxalenye yazo ibhekisele kuyo, ezinye zizalana.

Makhe siqalise ngomzekelo omnye wesethi yedatha - ukunyuka kwenyanga yomvuzo okufanayo kubo bonke abasebenzi:

Ngako oko, kuyimfuneko ukutshintsha kwakhona iseli kunye nokubala komvuzo C2, ngoku kuya kuba nefomula elandelayo:

Xa sicinezela u-Enter, sibona ukuba kule seteli umvuzo ulungiswe ngokufanelekileyo. Kodwa xa sandisa i-formula kubo bonke abasebenzi, umvuzo wabo awuyi kubuyiselwa kwakhona, kuba isalathisi esetyenzisiweyo isetyenzisiweyo ukuthatha ixabiso kwiG4..G8, apho akukho nto ikhoyo. Ukuze uphephe imeko enjalo, kuyimfuneko ukuba i-reference reference isetyenziswe kwi-Excel. Ukulungisa ikholomu okanye umgca ongafanele utshintshe xa ukopisha ifomu, kufuneka ubeke uphawu lwe dollar ($) ecaleni kwegama lekholomu okanye inombolo yomgca.

Makhe sitshintshe indlela yethu yokulandelayo:

Kwaye xa siyikopisha, umvuzo wonke wabasebenzi uya kubalwa:

Elandelana nomxholo othi "i-reference reference" kwi-Excel, umxholo wekhonkco uhlala udibaniswe.

Ukuxubusha okuxubileyo

Cinga olu hlobo lombhalo. Ikhonkco edibeneyo yinqununu, xa ikopishwe, iguqula inombolo yekholomu xa inamba yomgca ingatshintshi okanye ngenye indlela. Kule meko, isibonakaliso se dola sibekwe phambi kwenombolo yomgca (A $ 1) okanye phambi kwenombolo yekholomu ($ A1) - oko kukuthi, phambi kwelungu elingeke liguqulwe.

Ikhonkco edibeneyo isetyenziswe ngokuphindaphindiweyo kunokubhekiselele ngokupheleleyo. Ngokomzekelo, nangona mzekelo ongaphambili, sinokuyifakela ngokulula ifomula = (B2 + $ G $ 3) * 100/40 nge = (B2 + G $ 3) * 100/40 kwaye ufumane umphumo ofanayo kuba sikopishe ifom , Kunye nenombolo yekholomu nayiphi na imeko iya kuhlala ingatshintshi. Yaye oku akukhankanyi iimeko xa ufuna ngokwenene ukulungisa kuphela inani lomqolo okanye ikholomu, kwaye ushiye konke okukhoyo ukuze utshintshe.

Inqaku elithandekayo

Kuya kuba lunomdla ukufunda ukuba i-reference epheleleyo kwi-Excel ingasetwa kuphela ngokucacisa isibonakaliso sedoli phambi kwenombolo yomgca kunye / okanye inombolo yekholomu. "I-Excel" ikuvumela ukuba ukhethe uhlobo lweekhonkco ngokucindezela ukhiye weF4 emva kokucacisa idilesi yeseli - ngexesha lokuqala unxibelelwano litshintshile ukusuka kwisiqhelo ukuya kwesinye, okwesibini - ukudibanisa nenombolo yomgca ongaguqukiyo, okwesithathu - ukuxutyaniswa nenombolo yekholomu ehleliweyo, ngokufanelekileyo Ukuqakraza ikhonkco kwakhona kuya kuthatha ifom yohlobo. Sitshintsha imbonakalo yekhonkco kwi-Excel yinto elula kakhulu, kuba akukho mfuneko yokubhenela ekutshintsheni umxholo kwibhodibhodi.