Amathebula ahlobene ku-Excel: Imiyalo enemininingwane

Lapho wenza imisebenzi ethile ku-Excel kwesinye isikhathi kufanele ubhekane namatafula ambalwa ahlobene nawo. Okungukuthi, imininingwane evela etafuleni elilodwa iqiniswa kwabanye futhi amanani kuwo wonke amatafula ahlobene ayaphinde aguqulwe lapho eshintshiwe.

Amathebula ahlobene avumelanayo elula ukusebenzisa ukuphatha inani elikhulu lolwazi. Beka lonke ulwazi etafuleni elilodwa, ngaphandle kwalokho, uma kungenjalo homogeneous, hhayi elula kakhulu. Kunzima ukusebenza ngezinto ezinjalo futhi uzifune. Inkinga ecacisiwe iklanyelwe nje ukuqeda amatafula ahlobene, imininingwane phakathi okusatshalaliswa, kepha ngasikhathi sinye ixhunyiwe. Amatafula ahlobene nawo angaba ngaphakathi kweshidi elilodwa noma incwadi eyodwa, kepha futhi azotholakala ezincwadini ezihlukile (amafayela). Izinketho ezimbili zokugcina ekusebenzeni zivame ukusetshenziswa kakhulu, ngoba inhloso yalobu buchwepheshe ukumane nje ukusuka ekuqongeleleni kwedatha, futhi ukunikwa kwabo ekhasini elilodwa akuxazululi ngokuyisisekelo. Ake sifunde ukuthi ungayakha kanjani nokuthi ungasebenza kanjani ngohlobo olunjalo lokuphathwa kwedatha.

Ukwakha amatafula ahlobene

Okokuqala, ake sigxile embuzweni, lapho kungenzeka khona ukudala ukuxhumana phakathi kwamatafula ahlukahlukene.

Indlela 1: Ifomula eqondile yokubopha ifomula

Indlela elula yokubopha idatha ukusetshenziswa kwamafomula lapho kukhona izinkomba kwamanye amatafula. Ibizwa ngokuthi ukubopha okuqondile. Le ndlela inembile, ngoba uma ibopha cishe nje ukudala izinkomba zedatha kuhlu olulodwa lwetafula.

Ake sibheke ukuthi isibonelo ungawakha kanjani ukuxhumana ngokubopha okuqondile. Sinamatafula amabili kumashidi amabili. Etafuleni elifanayo, iholo libalwa kusetshenziswa ifomula ngokuphindaphindeka kwamanani ezinkampani ukuze ahlanganiswe ne-coeffeccing eyodwa.

Eshidi lesibili kukhona uhla lwetafula lapho kunohlu lwabasebenzi abanomholo wabo. Uhlu lwabasebenzi kuzo zombili lezi zigameko lwethulwa nge-oda elilodwa.

Kuyadingeka ukwenza leyo datha ekubhekeni kusuka eshidi lesibili liqinisa amaseli ahambisanayo okuqala.

1. Eshidini lokuqala, sabela iseli lokuqala lekholomu "yokubheja". Sibeka uphawu kuyo "=". Okulandelayo, chofoza kulebula "weshidi 2", okubekwa engxenyeni yesobunxele yesibonisi se-Excel ngaphezulu kwebha yesimo.



2. Kukhona ukunyakaza endaweni yesibili yedokhumenti. Chofoza kuseli lokuqala kukholamu "yokubheja". Bese uchofoza inkinobho ethi Enter kwikhibhodi ukufaka idatha esitokisini lapho isibonakaliso "esilinganayo" phambilini safakwa khona.



3. Bese kuba noguquko oluzenzakalelayo ekhasini lokuqala. Njengoba sikwazi ukubona, ukubaluleka kwesisebenzi sokuqala kusuka etafuleni lesibili kudonswa esitokisini esihambisanayo. Ngokufaka isikhombisi esitokisini esiqukethe ukubheja, siyabona ukuthi ifomula ejwayelekile isetshenziselwa ukubonisa idatha esikrinini. Kepha phambi kwezixhumanisi zeseli, ukusuka lapho idatha ikhishwa khona, kukhona inkulumo ethi "Uhlu2!", Okubonisa igama lendawo yedokhumenti lapho zitholakala khona. Ifomula elijwayelekile esimweni sethu libukeka kanjena:

   = Uhlu2! B2



4. Manje udinga ukudlulisa idatha mayelana namanani abo bonke abasebenzi bebhizinisi. Kuyiqiniso, lokhu kungenziwa ngendlela efanayo nathi sikugcwalise ngayo umsebenzi wesisebenzi sokuqala, kepha sibheka ukuthi zombili izinhlu zabasebenzi zitholakala ngokulandelana okufanayo, umsebenzi ungahle ube lula futhi usheshelwe yisinqumo sakhe. Lokhu kungenziwa ngokumane ukopishe ifomula ebangeni elingezansi. Ngenxa yokuthi izinkomba ze-Excel zihlobene, lapho zikopisha izindinganiso zazo, amanani aguqukayo asuselwa esikudingayo. Inqubo yekhophi ngokwayo ingenziwa kusetshenziswa umaki wokugcwalisa.

   Ngakho-ke, sibeka isikhombisi endaweni engezansi efanelekile yento ngefomula. Emva kwalokho, ikhesa kufanele liguqulele kuMarker wokugcwalisa ngesiphambano esimnyama. Senza i-clamp yenkinobho yegundane kwesobunxele bese sidonsa isikhombisi kwinombolo yekholomu.



5. Yonke imininingwane evela kwikholomu efanayo eshidini 2 yadonswa etafuleni ephepheni 1. Lapho idatha ishintsha eshidini 2, izoshintsha ngokuzenzakalelayo ekuqaleni.

Indlela 2: Isebenzisa ukunyanya kwe-operators Index - Sesha

Kepha yini okufanele uyenze uma uhlu lwabasebenzi emihlaleni yethebula lungekho ngokulandelana okufanayo? Kulokhu, njengoba kushiwo ngaphambili, enye yezinketho ukufaka ubudlelwano phakathi kwalelo lawo maseli okufanele ahlotshaniswe ngesandla. Kepha kufanelekile ngaphandle kwamatafula amancane. Okwemabanga amakhulu, le nketho izothatha isikhathi esiningi ekuqalisweni, futhi okubi kakhulu - ekusebenzeni ngokuvamile ngeke kube okungenangqondo. Kepha le nkinga ingaxazululwa kusetshenziswa isixuku sabahlinzeki Index - Sesha. Ake sibheke ukuthi kungenziwa kanjani ngokulima imininingwane ematafuleni azokwakheka ngayo ingxoxo ngendlela edlule.

1. Sigqamisa into yokuqala yekholomu "yokubheja". Iya kwimisebenzi Wizard ngokuchofoza isithonjana esithi "Insert Function".



2. E-Wizard yemisebenzi eqenjini "izixhumanisi kanye nama-arrays" Sithola futhi sabela igama elithi "Index".



3. Lo opharetha unamafomu amabili: ifomu lokusebenza ngokusebenza nereferensi. Esimweni sethu, inketho yokuqala iyadingeka, ngakho-ke ewindini elilandelayo lokukhetha ifomu elivula, likhethe bese uqhafaza inkinobho ethi "KULUNGILE".



4. Inkomba yezimpikiswano yomsebenzisi iqala ukusebenza. Umsebenzi womsebenzi obekiwe umphumela wenani elitholakala ebangeni elikhethiwe kulayini ngenombolo ebekiwe. I-General formula opharetha Index enje:

   = Inkomba (uhlu; inombolo_ame; [inombolo_stolbits])

   I- "ARRAY" yingxabano equkethe uhla lobubanzi lapho sizokhipha khona imininingwane ngenombolo yomugqa ocacisiwe.

   "Inombolo Yomugqa" yingxabano engokwesibalo salomugqa. Kubalulekile ukwazi ukuthi inombolo yomugqa kufanele icaciswe ukuthi ingahlobene nalo lonke idokhumenti, kodwa ihlobene kuphela ne-array eyabelwe.

   "Inombolo yekholomu" yingxabano okukhethwa kukho. Ukuxazulula ngokuqondile umsebenzi wethu, ngeke sikusebenzise, ​​ngakho-ke akudingekile ukuchaza ngokwahlukana.

   Sibeka isikhombisi ensimini "ye-array". Ngemuva kwalokho, iya ephepheni 2 futhi, ubambe inkinobho yegundane kwesobunxele, khetha konke okuqukethwe kwikholamu "rate".



5. Ngemuva kokuthi izixhumanisi ziboniswa ewindini le-opharetha, sibeka ikhesa ensimini "yomugqa". Sizoyikhipha le ngxabano sisebenzisa opharetha wokusesha. Ngakho-ke, chofoza unxantathu otholakala ngakwesokunxele sentambo yokusebenza. Uhlu lwama-opharetha asanda kusetshenziswa luvulwa. Uma uthola igama elithi "inkampani yokusesha" phakathi kwazo, ungachofoza kuyo. Esimweni esiphambene, chofoza iphuzu lakamuva lohlu - "Eminye imisebenzi ...".



6. Iwindows Wizard Ejwayelekile Wizard iqala. Iya kuyo eqenjini elifanayo leqembu "kanye nama-arrays". Kulesi sikhathi ohlwini, khetha into ethi "Inkampani Yokucinga". Yenza ngokuchofoza inkinobho ethi "KULUNGILE".



7. Ukwenza kusebenze kwezimpikiswano zezimpikiswano zoMsebenzisi wosesho kwenziwa. Umsebenzi obekiwe wenzelwe ukukhipha inombolo yenani ngohlu oluthile ngegama layo. Sibonga ngalesi sici esibala inani lentambo yenani elithile lomsebenzi womsebenzi. I-syntax yebhodi yokusesha yethulwe:

   = Ibhodi Lokucinga (search_name; ukubukwa kwe -__nassive; [uhlobo_Station])

   "Abafiswayo" yingxabano equkethe igama noma ikheli leseli lobubanzi beqembu lesithathu lapho itholakala khona. Kuyisikhundla saleli gama ebangeni eliqondiwe futhi kufanele kubalwe. Kuthu, iqhaza lengxabano yokuqala lizobhekiswa kumaseli eshidi 1, lapho abasebenzi batholakala khona.

   "I-ARRAME Bright" yingxabano, okuyinkomba ku-array, eyenza ukusesha kwenani elibekiwe lokunquma isikhundla salo. Sizoba nale ndima ukwenza ikheli lekholomu "igama" eshidini 2.

   "Uhlobo lokuqhathanisa" - Impikiswano engakhethwa, kepha, ngokungafani no-opharetha wangaphambilini, le ngxabano ongayikhetha izodingeka. Kubonisa ukuthi kufanele uqondanise kanjani opharetha inani elifunekayo elinamalungu afanayo. Le ngxabano ingaba nenye yamanani amathathu: -1; 0; 1. Okokuhlaziywa kwe-arrays, khetha inketho "0". Le nketho ilungele icala lethu.

   Ngakho-ke, qhubeka nokugcwalisa amasimu wewindi lengxabano. Sibeka isikhombisi ensimini "inani lezinkuthelo", chofoza kukholamu yokuqala "Igama" eshidi 1.



8. Ngemuva kokuba izixhumanisi zikhonjisiwe, setha isikhombisi endle "ekufakweni kohlu olukhulu lwensimu bese iya kulebula" weshidi 2 ", elitholakala ezansi kwewindows le-Excel ngenhla kwesimo. UClement inkinobho yegundane kwesobunxele futhi aqokomise isikhombisi wonke amaseli wekholomu "igama".



9. Ngemuva kokuthi izixhumanisi zabo ziboniswa ensimini ethi "Ukufakwa ohlwini okukhulu", iya ku-"hlobo lwemephu" "bese usetha inombolo" 0 "kusuka kukhibhodi. Emva kwalokho, siphinde sibuyele ensimini "sibheka uhlu". Iqiniso ngukuthi sizokwenza ukopisha ifomula, njengoba senza ngendlela edlule. Kuzoba khona ukushintshwa kwamakheli, kepha lapha izixhumanisi ze-array zibuke kudingeka sivikeleke. Akufanele asuke. Sigqamisa izixhumanisi ngesikhombisi bese uqhafaza kukhiye we-F4 function. Njengoba ubona, isibonakaliso sedola savela ngaphambi kokuxhumanisa, okusho ukuthi ukubhekiswa kuhlobo oluphenduke lube ngokuphelele. Bese uchofoza inkinobho ethi "KULUNGILE".



10. Umphumela uboniswa esitokisini sokuqala sekholomu "yokubheja". Kepha ngaphambi kokukopisha, kudingeka silungise enye indawo, okungukuthi inkomba yokuqala yokuphikisana. Ukuze wenze lokhu, khetha into yekholomu, equkethe ifomula, bese idlulela entanjeni yefomula. Ukwabela impikiswano yokuqala ye-Operator Index (B2: B7) bese uqhafaza inkinobho ye-F4. Njengoba ubona, isibonakaliso sedola savela eduze kwezixhumanisi ezikhethiwe. Chofoza ukhiye we-ENTER. Ngokuvamile, ifomula lathatha ifomu elilandelayo:

    = Index (Sheet2! $ B $ 2: $ B $ 7; IBHODI LOKUFUNDA (SHEBHEPE1! A4; Uhlu lwama- $ 2: $ A $ 7; 0))



11. Manje ungakopisha usebenzisa umaki wokugcwalisa. Siyibiza ngendlela efanayo nathi sikhulume ngayo phambilini, futhi selule kuze kube sekupheleni kwebanga le-tabular.



12. Njengoba ubona, naphezu kweqiniso lokuthi ukuhleleka kwezintambo kumatafula amabili ahlobene akuhambelani, noma kunjalo, wonke amanani aqiniswa ngokwezibalo zabasebenzi. Lokhu kufinyelelwe ngenxa yokusebenzisa inhlanganisela yabasebenzi benkomba yokusebenza.

Indlela 4: Ukufakwa okukhethekile

I-Tie tafula i-Excel nayo ingaba ngokusebenzisa ukufakwa okukhethekile.

1. Khetha amanani ofuna 'aqinise' kwelinye itafula. Kuso, lokhu yi- "BET" COLUNM RANICK eshidini 2. Chofoza kwingxenye ezinikezelwe ngenkinobho yegundane elifanele. Uhlu oluvula, khetha into ethi "Kopisha". Inhlanganisela ehlukile iyinhlanganisela ye-CTRL + C. Ngemuva kwalokho, sithuthela ephepheni 1.



2. Ukuthuthela endaweni encwadini oyidingayo, ukwaba amaseli lapho amanani azodinga ukuqiniswa khona. Kuthu, le yikholamu "yebhidi". Chofoza kwingxenye ezinikezelwe ngenkinobho yegundane efanele. Kumenyu yokuqukethwe ku- "Insert Parameters" yamathuluzi, chofoza isithonjana esithi "Insert Communication".

   

   Kukhona enye indlela. Yena, ngendlela, ukuphela kwenguqulo yakudala ye-Excel. Kumenyu yokuqukethwe, siletha isikhombisi entweni ethi "Ukufaka Okukhethekile". Kumenyu eyengeziwe evula, khetha isikhundla ngegama elifanayo.



3. Emva kwalokho, kuvulwa iwindi elikhethekile. Chofoza inkinobho ethi "Insert Communication" ekhoneni elingezansi kwesobunxele seseli.



4. Noma ngabe iyiphi inketho oyikhethayo, amanani avela kuhlu olulodwa lwetafula azofakwa kwelinye. Lapho uguqula idatha emthonjeni, izoshintsha ngokuzenzakalelayo ebangeni elifakiwe.

Isifundo: Ukufakwa okukhethekile ku-Excel

Indlela 5: Ukuxhumana phakathi kwamatafula ezincwadini eziningana

Ngaphezu kwalokho, ungahlela ukuxhumana phakathi kwezindawo zetafula ezincwadini ezihlukile. Lokhu kusebenzisa ithuluzi elikhethekile lokufaka. Izenzo zizofanela impela nalezo esizihlolisise ngendlela esedlule, ngaphandle kwalapho ukuzulazula ngesikhathi sefomula ngeke kube phakathi kwezindawo zencwadi eyodwa, kepha phakathi kwamafayela. Ngokwemvelo, zonke izincwadi ezihlobene nazo kufanele zivulwe.

1. Khetha uhla lwedatha okufanele ludluliselwe kwenye incwadi. Chofoza inkinobho yegundane kwesokudla bese ukhetha isikhundla esithi "Kopisha" kumenyu evulekile.



2. Ngemuva kwalokho sithuthela encwadini lapho le datha kufanele ifakwe. Khetha uhla olufunayo. Chofoza inkinobho yegundane kwesokudla. Kumenyu yokuqukethwe ku- "Faka izilungiselelo" Group, khetha i- "Insert Communication" into.



3. Ngemuva kwalokho, amanani azofakwa. Lapho ushintsha idatha encwadini yomthombo, uhlu lwethebula oluvela encwadini yokusebenza luzosiqinisa ngokuzenzakalelayo. Futhi akudingekile nakancane ukuqinisekisa ukuthi zombili lezi zincwadi zivulekile. Kwanele ukuvula eyodwa kuphela ku-Workbook, futhi kuzokonga ngokuzenzakalelayo idatha kusuka kudokhumenti elihlobene elivaliwe uma ngabe kukhona izinguquko ezedlule kulo.

Kepha kufanele kuqashelwe ukuthi kulokhu okususelwa ngaphakathi kuzokhiqizwa ngendlela yokuhleleka okungashintshiwe. Lapho uzama ukushintsha noma isiphi iseli ngedatha efakiwe, umyalezo uzoba ningizi ngokwazisa ngokuhluleka ukwenza lokhu.

Izinguquko ezihlotsheni ezinjalo ezihambisana nenye incwadi zingaphula kuphela ukuxhumana.

Izihloko eziqhekeka phakathi kwamatafula

Kwesinye isikhathi kuyadingeka ukwephula ukuxhumana phakathi kwamatafula. Isizathu salokhu kungaba okungenhla sichazwe lapho ufuna ukushintsha uhlu lufakwe kusuka kwenye incwadi nokumane kube yinto yokungabaza komsebenzisi ukuze idatha etafuleni elifanayo ivuselelwa ngokuzenzakalela kusuka kolunye.

Indlela 1: Ukuqhekeka Kwezokuxhumana phakathi kwezincwadi

Ukwephula ukuxhumana phakathi kwezincwadi kuwo wonke amaseli, ngokwenza empeleni ukusebenza okukodwa. Kulokhu, imininingwane emangqamuzaneni izosala, kepha izobe ivele ibe amanani avuselelwayo anganciphise kwamanye amadokhumenti.

1. Encwadini lapho amanani avela kwamanye amafayela aqiniswa, iya kuthebhu yedatha. Chofoza isithonjana se- "Change Links", esitholakala etheyipini ku- "Ukuxhumeka" kwamathuluzi. Kumele kwaziwe ukuthi uma incwadi yamanje ingenayo ukuxhumana namanye amafayela, khona-ke le nkinobho ayisebenzi.



2. Iwindi lokushintsha kwesixhumanisi sethulwa. Khetha kusuka ohlwini lwezincwadi ezihlobene (uma kukhona eziningana zazo) ifayela esifuna ukuphula ngalo uxhumano ngalo. Chofoza inkinobho ethi "Phula uxhumano".



3. Kuvulwa iwindi lolwazi, elinikeza isexwayiso mayelana nemiphumela yezenzo ezengeziwe. Uma unesiqiniseko sokuthi uzokwenza, chofoza inkinobho ethi "Break Communication".



4. Ngemuva kwalokho, zonke izinkomba zefayela elibekiwe kudokhumenti yamanje zizothathelwa indawo ngamanani ama-static.

Indlela 2: Ukufaka amanani

Kepha indlela engenhla ilungele kuphela uma udinga ukuphula ngokuphelele zonke izixhumanisi phakathi kwezincwadi ezimbili. Kuthiwani uma udinga ukunqamula amatafula ahlobene ngaphakathi kwefayela elifanayo? Ungakwenza lokhu ngokukopisha imininingwane, bese ufaka indawo efanayo namanani. Ngendlela, le ndlela ingaphulwa phakathi kwedatha yomuntu ngamunye yezincwadi ezahlukahlukene ngaphandle kokuphula ubudlelwano obujwayelekile phakathi kwamafayela. Ake sibheke ukuthi le ndlela isebenza kanjani ekusebenzeni.

1. Sigqamisa uhla lapho sifisa ukususa khona ukuxhumana nelinye itafula. Chofoza inkinobho yegundane kwesokudla. Kumenyu evulekile, khetha into ethi "Kopisha". Esikhundleni sezenzo ezibekiwe, ungashayela enye inhlanganisela yezinkinobho ezishisayo i-CTRL + C.



2. Okulandelayo, ngaphandle kokususa ukukhethwa kusuka kwingxenye efanayo, phinda uchofoze ngenkinobho yegundane elifanele. Ngalesi sikhathi ohlwini lwesenzo, chofoza isithonjana esithi "Inani", elithunyelwa eqenjini le-Insert Parameters.



3. Ngemuva kwalokho, zonke izinkomba kuhlu olunikezele zizothathelwa indawo ngamanani ama-static.

Njengoba ukwazi ukubona, i-Excel inezindlela namathuluzi okuhlanganisa amatafula amaningana phakathi kwawo. Ngasikhathi sinye, idatha ye-tabular ingaba kwamanye amashidi ngisho nasezincwadini ezihlukile. Uma kunesidingo, lokhu kuxhumana kungaphulwa kalula.