Lapho wenza imisebenzi ethile e-Excel, kwesinye isikhathi kufanele ubhekane namatafula amaningana, nawo ahlobene. Okungukuthi, idatha kusuka etafuleni elilodwa idonswa kwelinye futhi lapho iguqulwa, amanani atholakala kuwo wonke amabanga wethebula ahlobene.
Amatafula axhunyiwe alula kakhulu ukuwasebenzisela ukucubungula inani elikhulu lolwazi. Ukubeka yonke imininingwane ethebuleni linye, ngaphandle kwalokho, uma kungeyona into eyodwa, akululanga kakhulu. Kunzima ukusebenza nezinto ezinjalo futhi uzifune. Inkinga ekhonjisiwe yenzelwe ukuqeda amatafula axhumene, imininingwane phakathi kwayo esatshalaliswa, kepha ngasikhathi sinye ixhunyiwe. Amabanga wethebula elixhunyiwe angafakwa ngaphakathi kweshidi elilodwa noma incwadi eyodwa, kodwa futhi angatholakala ezincwadini ezihlukile (amafayela). Lezi zindlela ezimbili zokugcina zivame ukusetshenziswa kakhulu ekusebenzeni, ngoba inhloso yalobu buchwepheshe ukungaqongeleli idatha, futhi ukuyifaka ekhasini elilodwa akuxazululi inkinga. Masifunde ukuthi ungakha kanjani futhi ungasebenza kanjani ngalolu hlobo lokuphathwa kwedatha.
Ukwakha Amathebula Axhunyiwe
Okokuqala, ake sigxile embuzweni wokuthi yiziphi izindlela ezinethuba lokwakha ubudlelwano phakathi kwamabala etafula ahlukene.
Indlela yoku-1: hlanganisa ngokuqondile amatafula ngefomula
Indlela elula yokubopha idatha ukusebenzisa amafomula abhekisela kwamanye amabanga wethebula. Ibizwa ngokuthi ukubopha ngokuqondile. Le ndlela iyabonakala, ngoba ngayo ukuxhumanisa kwenziwa ngendlela ecishe ifane nokwakha izixhumanisi zedatha eyodwa kuluhlu lwethebula.
Ake sibheke ukuthi, ngokwesibonelo, isibopho singakhiwa kanjani ngokubopha ngokuqondile. Sinamatafula amabili kumashidi amabili. Etafuleni elilodwa, abakhokhelwayo babalwa besebenzisa ifomula ngokuphindaphindeka inani labasebenzi ngenhlokomo eyodwa yabo bonke.
Eshidi lesibili kukhona uhlu lwetafula, oluqukethe uhlu lwabasebenzi ngemholo yabo. Uhlu lwabasebenzi kuzo zombili lezi zimo luvezwa ngokulandelana okufanayo.
Udinga ukuthi uqiniseke ukuthi imininingwane esezintweni ezisuka ekhasini lesibili idonswa kumaseli ahambisanayo wokuqala.
- Eshidi lokuqala, khetha iseli yokuqala kwikholamu Ibhidi. Sibeka uphawu kuyo "=". Okulandelayo, chofoza enqamulelayo "Ishidi 2", etholakala ohlangothini lwesobunxele lwesixhumi esibonakalayo se-Excel ngaphezulu kwebha yomumo
- Kuhamba kuya endaweni yesibili yedokhumenti. Sichofoza kuseli lokuqala kukholamu Ibhidi. Bese uchofoza inkinobho Ngena kukhibhodi ukufaka idatha esitokisini lapho isibonakaliso besilungiswa khona ngaphambili okulinganayo.
- Bese kuba nokuguqulwa okuzenzakalelayo kushidi lokuqala. Njengoba ukwazi ukubona, isilinganiso sesisebenzi sokuqala kusuka etafuleni lesibili sidonswa kuseli elihambisanayo. Ngokubeka isikhombisi kuseli eliqukethe ukubheja, sibona ukuthi ifomula evamile isetshenziswa ukubonisa idatha esikrinini. Kepha ngaphambi kokuxhumanisa kweseli kusuka lapho kukhiqizwe khona idatha, kukhona isisho "Ishidi2!", okukhombisa igama lendawo yedokhumenti lapho betholakala khona. Ifomula ejwayelekile kithi ibukeka kanjena:
= Ishidi2! B2 - Manje udinga ukudlulisa imininingwane ngamanani abo bonke abanye abasebenzi bebhizinisi. Vele, lokhu kungenziwa ngendlela efanayo naleyo esiyiphelelise ngayo umsebenzi wokuqala, kodwa uma sinikezwe ukuthi zombili izinhlu zabasebenzi zihlelwe ngokulandelana okufanayo, umsebenzi ungenziwa lula futhi usheshise isixazululo sawo. Lokhu kungenziwa ngokumane kopisha ifomula ebangeni elingezansi. Ngenxa yokuthi izixhumanisi ezise-Excel zihlobene ngokuzenzakalelayo, lapho zikopishwa, amanani aguquliwe, yilokho esikudingayo. Inqubo yokukopisha ngokwayo ingenziwa kusetshenziswa umaka wokugcwalisa.
Ngakho-ke, beka ikhesa endaweni engezansi kwesokudla sento ngefomula. Ngemuva kwalokho, isikhombisi kufanele siguqulwe sibe umaki wokugcwalisa ngendlela yesiphambano esimnyama. Shaya inkinobho yegundane kwesobunxele bese uhudula isikhombisi ezansi kwekholamu.
- Yonke imininingwane esuka kwikholomu efanayo ivuliwe Ishidi 2 badonswa etafuleni Ishidi 1. Lapho ushintsha idatha iye Ishidi 2 bazoshintsha ngokuzenzakalela kokuqala.
Indlela 2: ukusebenzisa inqwaba yabasebenza ku-INDEX - SEARCH
Kepha kuthiwani uma uhlu lwabasebenzi ekuhlelweni kwetafula lungalingani? Kulokhu, njengoba kushiwo ngaphambili, enye yezinketho ukusungula ukuxhumana phakathi kwalawo maseli okufanele axhumeke ngesandla. Kepha lokhu kufanele kuphela amatafula amancane. Kumabanga amakhulu, inketho enjalo kuzothatha isikhathi esiningi ukusisebenzisa, futhi okubi kakhulu, uma sisebenza ngeke sisebenze. Kepha le nkinga ingaxazululwa kusetshenziswa inqwaba yabasebenza INDEX - SESHA. Ake sibheke ukuthi lokhu kungenziwa kanjani ngokuxhuma idatha kumugqa wethebula okuxoxwe ngawo ngendlela yangaphambilini.
- Khetha into yokuqala yekholamu Ibhidi. Iya ku Isici Wizardngokuchofoza isithonjana "Faka umsebenzi".
- In Umsebenzi wizadi eqenjini Izinkomba kanye nokufika thola bese uqokomisa igama INDEX.
- Lo opharetha unezinhlobo ezimbili: ifomu lokusebenza ngokusebenza kwama-arrays nereferensi. Esimweni sethu, inketho yokuqala iyadingeka, ngakho-ke, ewindini elilandelayo lokukhetha ifomu elivula, likhethe bese uchofoza inkinobho "Kulungile".
- Kuvulwe iwindi lokuphikisana nomsebenzi INDEX. Umsebenzi walesi msebenzi ukukhipha inani elikuhlu olukhethiwe kulayini ngenombolo ebekiwe. Ifomula yokusebenza evamile INDEX yilokhu:
= INDEX (uhlu; umugqa_unombolo; [ikholomu_number])Array - impikiswano equkethe ikheli lobubanzi esizokhipha imininingwane ngalo ngenombolo yomugqa obekiwe.
Inombolo yomugqa -ukuphikisana, okuyisibalo salona layini. Kubalulekile ukwazi ukuthi inombolo yomugqa akufanele ichazwe ngokuhlobene nayo yonke idokhumenti, kodwa kuphela okuhlobene neqoqo elikhethiwe.
Inombolo Yekholomu - impikiswano ongayikhetha. Ngeke siyisebenzise ukuxazulula inkinga yethu ethile, futhi ngenxa yalokho akudingekile ukuchaza isisekelo sayo ngokwahlukana.
Faka ikhesa endle Array. Ngemuva kwalokho, iya Ishidi 2 futhi ubambe inkinobho yegundane kwesobunxele, khetha konke okuqukethwe ikholomu Ibhidi.
- Ngemuva kokuthi izixhumanisi ziboniswe ewindini le-opharetha, beka ikhesa enkundleni Inombolo yomugqa. Sizokhipha le ngxabano sisebenzisa opharetha SESHA. Ngakho-ke, sichofoza unxantathu, otholakala kwesobunxele bomugqa wokusebenza. Uhlu lwabasebenzisi abasanda kusetyenziswa luvulwa. Uma uthola igama phakathi kwabo "FUNA"khona-ke ungachofoza kukho. Ngaphandle kwalokho, chofoza entweni yokugcina ohlwini - "Ezinye izinto ...".
- Iwindi elijwayelekile liyaqala Umsebenzi Wizards. Sidlulela kuwo eqenjini elifanayo Izinkomba kanye nokufika. Okwamanje, khetha into esohlwini "FUNA". Chofoza inkinobho. "Kulungile".
- Iwindi lokuphikisana nomsebenzi liyasebenza SESHA. Umsebenzi ochaziwe uhloselwe ukubonisa inani levelu ngendlela ethile ethile ngegama layo. Ngenxa yalesi sici, sizokubala inombolo yomugqa yenani elithile lomsebenzi INDEX. I-Syntax SESHA kuvezwe okulandelayo:
= SEARCH (search_value; lookup_array; [match_type])"Ukufuna inani" - impikiswano equkethe igama noma ikheli leseli ebangeni lomuntu wesithathu elikuyo. Kuyisikhundla saleli gama ebangeni eliqondiwe okufanele libalwe. Esimweni sethu, ingxabano yokuqala izobhekiswa kumaseli kuvuliwe Ishidi 1lapho amagama abasebenza khona.
Kubukwa u-Array - impikiswano emelela ireferensi ekuhlelweni lapho inani elicacisiwe liseseshwa ukuthola indawo yalo. Ikheli lekholomu "lizodlala le ndima lapha."Igama lokuqala kuvuliwe Ishidi 2.
Uhlobo Lomdlalo -ukuphikisana, okungakhethwa, kepha, ngokungafani nesitatimende esedlule, sizodinga le ngxabano engakhethwa. Kubonisa ukuthi opharetha angafanisa kanjani inani lokusesha ne-array. Le ngxabano ingaba neyodwa yamanani amathathu: -1; 0; 1. Okokuhlelwa okungalawulwa, khetha "0". Le ndlela ilungele icala lethu.
Ngakho-ke, ake siqale ukugcwalisa izinkambu zewindi lokuphikisana. Faka ikhesa endle "Ukufuna inani"chofoza kuseli lokuqala lekholamu "Igama" kuvuliwe Ishidi 1.
- Ngemuva kokuthi izixhumanisi zibonisiwe, setha ikhesa enkundleni Kubukwa u-Array bese uchofoza isinqamuleli "Ishidi 2", etholakala ngaphansi kwewindi le-Excel ngaphezulu kwebha yesimo. Bamba inkinobho yegundane kwesobunxele bese ukhetha wonke amaseli kukholamu nesikhombisi "Igama".
- Ngemuva kokuthi izixhumanisi zabo ziboniswe enkundleni Kubukwa u-Arrayiya ensimini Uhlobo Lomdlalo bese usetha inombolo lapho kusuka kukhibhodi "0". Ngemuva kwalokho, sibuyela ensimini futhi Kubukwa u-Array. Iqiniso ukuthi sizokopisha ifomula, njengoba senzile ngendlela eyedlule. Ukushintshwa kwekheli kuzokwenzeka, kepha lapha sidinga ukulungisa izixhumanisi zokuhlela eziningi okubukwa. Akufanele kuxoshwe. Khetha izixhumanisi nesikhombisi bese ucindezela inkinobho yokusebenza F4. Njengoba ubona, uphawu lwedola luvele phambi kwezixhumanisi, okusho ukuthi isixhumanisi siguqukile ekubeni isihlobo kuye ngokuphelele. Bese uchofoza inkinobho "Kulungile".
- Umphumela uboniswa kuseli lokuqala lekholamu. Ibhidi. Kepha ngaphambi kokukopisha, sidinga ukulungisa enye indawo, okungukuthi ukuphikisana kokuqala komsebenzi INDEX. Ukuze wenze lokhu, khetha ingxenye yekholomu equkethe ifomula, bese uqhubekela emugqeni wamafomula. Sikhetha impikiswano yokuqala ka-opharetha INDEX (B2: B7) bese uchofoza inkinobho F4. Njengoba ubona, uphawu lwedola lwavela eduze kwezixhumanisi ezikhethiwe. Chofoza inkinobho Ngena. Ngokuvamile, ifomula yathatha ifomu elilandelayo:
= INDEX (Ishidi2! $ B $ 2: $ B $ 7; SEARCH (Ishidi1! A4; Ishidi2! $ A $ 2: $ A $ 7; 0)) - Manje ungakopisha usebenzisa umaki wokugcwalisa. Silibiza ngendlela efanayo nalesi esikhulume ngayo phambilini, futhi siyeluleke ekugcineni kobubanzi bethebula.
- Njengoba ubona, ngaphandle kokuthi i-oda lomugqa lamatafula amabili ahlobene alifani, noma kunjalo, wonke amanani atholakala ngokwamagama abasebenzi. Lokhu kufinyelelwe ngokusebenzisa inhlanganisela yabasebenza INDEX-SESHA.
Funda futhi:
Umsebenzi we-ExEX ku-Excel
Umsebenzi we-EXCEL ku-Excel
Indlela 3: yenza imisebenzi yezibalo ngemininingwane ehlobene
Ukuhlanganiswa kwedatha eqondile nakho kuhle ngoba kukuvumela ukuthi ungagcini nje ngokubonisa amanani aboniswa kwamanye amabanga wethebula kwelinye lamatafula, kodwa futhi wenze imisebenzi ehlukahlukene yezibalo kanye nabo (ukwengeza, ukuhlukanisa, ukukhipha, ukuphindaphinda, njll.).
Ake sibheke ukuthi lokhu kusebenza kanjani ekusebenzeni. Ake sikwenze lokho Ishidi 3 idatha yemholo ejwayelekile yenkampani izokhonjiswa ngaphandle kokudilizwa ngabasebenzi. Ukuze wenze lokhu, amanani ezisebenzi azokhishwa Ishidi 2, efingqiwe (kusetshenziswa umsebenzi ISUM) futhi ngandise ngokulingene usebenzisa ifomula.
- Khetha iseli lapho kuzoboniswa khona umphumela wokubala kwabakhokhelayo. Ishidi 3. Chofoza inkinobho. "Faka umsebenzi".
- Iwindi kufanele liqale Umsebenzi Wizards. Iya eqenjini "Izibalo" bese ukhetha igama lapho ISUM. Okulandelayo, chofoza inkinobho "Kulungile".
- Izimpikiswano zomsebenzi zihanjiswa efasiteleni ISUM, enzelwe ukubala isamba sezinamba ezikhethiwe. Ine-syntax elandelayo:
= SUM (inombolo1; inombolo2; ...)Izindima ezisewindini zihambelana nezimpikiswano zomsebenzi othile. Yize inani labo lingafinyelela ku-255, munye kuphela owanele inhloso yethu. Faka ikhesa endle "Inombolo1". Chofoza enqamulelayo "Ishidi 2" ngaphezulu kwebha yesimo.
- Ngemuva kokuthi sesithuthele engxenyeni efiselekayo yencwadi, khetha ikholomu okufanele ifingqwe. Senza lokhu ngesikhombisi ngenkathi siphethe inkinobho yegundane kwesobunxele. Njengoba ubona, izixhumanisi zendawo ekhethiwe ziboniswa ngokushesha emkhakheni wewindi lokuphikisana. Bese uchofoza inkinobho "Kulungile".
- Ngemuva kwalokho, sithuthela ngokuzenzakalelayo Ishidi 1. Njengoba ukwazi ukubona, inani eliphelele lamabhidi ezisebenzi selivele libonisiwe entweni ehambisanayo.
- Kepha akupheleli lapho. Njengoba sikhumbula, iholo libalwa ngokuphindaphindeka kwenani lesilinganiso ngento ethile. Ngakho-ke, siphinda sikhethe iseli lapho inani elifingqiwe litholakala khona. Ngemuva kwalokho sidlulela emugqeni wamafomula. Faka kwifomula kuyo uphawu lokuphindaphindeka (*), bese uqhafaza ku-element lapho kutholakala khona inkomba yokusebenza kahle Ukuze wenze ukubala, chofoza inkinobho Ngena kwikhibhodi. Njengoba ukwazi ukubona, uhlelo lwabalwa inani lamaholo ebhizinisi.
- Buyela ku Ishidi 2 futhi ushintshe isilinganiso sanoma yimuphi umsebenzi.
- Ngemuva kwalokho, siphinde siqhubekele ekhasini ngenani eliphelele. Njengoba ukwazi ukubona, ngenxa yoshintsho etafuleni elixhunyiwe, umphumela womholo ophelele waphinda wazwakala kabusha.
Indlela 4: ukufaka ngokwezifiso
Ungaxhumanisa ukuhlelwa kwetafula ku-Excel usebenzisa ukufaka okhethekile.
- Sikhetha amanani azodinga "ukudonswa" kwelinye itafula. Esimweni sethu, yilolu uhla lwekholomu Ibhidi kuvuliwe Ishidi 2. Sichofoza kucezu olukhethiwe ngenkinobho yegundane elifanele. Kuluhlu oluvulayo, khetha Kopisha. Enye indlela enqamulelayo yekhibhodi I-Ctrl + C. Ngemuva kwalokho sithuthela Ishidi 1.
- Njengoba sesithuthele endaweni yencwadi esiyidingayo, sikhetha amaseli esizodinga ukukhulisa amanani. Kithi, lokhu kuyikholomu Ibhidi. Sichofoza kucezu olukhethiwe ngenkinobho yegundane elifanele. Kumenyu yokuqukethwe kubhlokhi yethuluzi Faka Izinketho chofoza isithonjana Namathisela Isixhumanisi.
Kukhona futhi okunye. Ngeshwa, yiyona kuphela yezinhlobo ezindala ze-Excel. Kumenyu yokuqukethwe, hambisa phezulu "Faka okhethekile". Kumenyu eyengeziwe evulayo, khetha indawo enegama elifanayo.
- Ngemuva kwalokho, iwindows yokufaka ekhethekile iyavula. Chofoza inkinobho Namathisela Isixhumanisi ekhoneni elingaphansi kweseli.
- Noma ngabe iyiphi inketho oyikhethayo, amanani entengo eyodwa yetafula azofakwa kwelinye. Uma kushintshwa idatha emthonjeni, zizoshintsha ngokuzenzakalela ebangeni elifakiwe.
Isifundo: Faka ngokukhethekile ku-Excel
Indlela yesi-5: ukuxhumana phakathi kwamatafula ezincwadini eziningi
Ngaphezu kwalokho, ungahlela ukuxhumana phakathi kwezindawo zetafula ezincwadini ezahlukahlukene. Kusetshenziswa ithuluzi elikhethekile lokufaka. Izenzo zizofana ngokuphelele nalezo esicabange ngazo ngendlela edlule, ngaphandle kokuthi kuzodingeka uzulazule ngenkathi wenza amafomula hhayi phakathi kwezindawo zencwadi efanayo, kepha phakathi kwamafayela. Ngokwemvelo, zonke izincwadi ezihlobene kufanele zivulwe.
- Khetha ibanga ledatha ofuna ukuyidlulisela kwenye incwadi. Qhafaza kwesokudla kuso bese ukhetha isikhundla kumenyu esivulayo. Kopisha.
- Ngemuva kwalokho sidlulela encwadini lapho le datha izodinga ukufakwa khona. Khetha ibanga olifunayo. Qhafaza kwesokudla. Kumenyu yokuqukethwe eqenjini Faka Izinketho khetha into Namathisela Isixhumanisi.
- Ngemuva kwalokho, amanani azofakwa. Lapho idatha ekule ncwadi yomthombo ishintsha, uhlu lwethebula kusuka kubhuku lokusebenza luzolususa ngokuzenzakalelayo. Ngaphezu kwalokho, akudingekile ukuthi zombili izincwadi zivulelwe lokhu. Kwanele ukuvula incwadi yokusebenzela eyodwa kuphela, futhi izodonsa ngokuzenzakalelayo idatha kusuka kudokhumenti elixhunyiwe elixhunyiwe uma izinguquko zenziwe kuyo ngaphambili.
Kepha kufanele kwaziwe ukuthi kuleli cala okufakwayo kuzokwenziwa njengelungiselelo elingenakuphikwa. Lapho uzama ukuguqula noma isiphi iseli ngemininingwane efakiwe, umlayezo uqhamuka ukwazisa ukuthi akunakwenzeka ukwenza lokhu.
Izinguquko ekuhlelweni okunjalo okuhlotshaniswa nenye incwadi zingenziwa kuphela ngokuphula isixhumanisi.
Igebe phakathi kwamatafula
Kwesinye isikhathi udinga ukuphula ukuxhumana phakathi kwamabanga etafula. Isizathu salokhu kungaba yicala elichazwe ngenhla, lapho udinga ukushintsha uhlu olufakiwe kusuka kwenye incwadi, noma ukumane nje ukwenqabele umsebenzisi ukuthi idatha esetafuleni elilodwa ivuselelwa ngokuzenzakalelayo kwenye.
Indlela yoku-1: ukwephula ukuxhumana phakathi kwezincwadi
Ungaphula ukuxhumana phakathi kwezincwadi kuwo wonke amaseli ngokwenza cishe umsebenzi owodwa. Ngasikhathi sinye, idatha esezitokisini izosala, kepha izobe isivele ingamanani angaqukeki, angathembeki kwamanye amadokhumenti.
- Encwadini, lapho kudonswa khona amanani avela kwamanye amafayela, iya kuthebhu "Idatha". Chofoza isithonjana "Shintsha Ezokuxhumana"etholakala kubambo kubhokisi lamathuluzi Ukuxhumana. Kumele kwaziwe ukuthi uma incwadi yamanje ingenazixhumanisi namanye amafayela, le nkinobho ayisebenzi.
- Iwindi lokushintsha izixhumanisi liqalisiwe. Sikhetha ifayili esifuna ukuphula ngalo ukuxhumana kusuka ohlwini lwezincwadi ezihlobene (uma kukhona eziningana). Chofoza inkinobho Phula isixhumanisi.
- Kuvulwa iwindi lolwazi, okukhona kulo isixwayiso ngemiphumela yezenzo ezengeziwe. Uma uqinisekile ukuthi uzokwenzenjani, bese uchofoza inkinobho "Ukuqhekeka izibopho".
- Ngemuva kwalokho, zonke izixhumanisi eziya kufayela elicacisiwe kudokhumenti yamanje zizothathelwa indawo ngamanani wokubala.
Indlela 2: Faka Amagugu
Kepha indlela engenhla ifanelekile kuphela uma udinga ukusula ngokuphelele zonke izixhumanisi phakathi kwezincwadi ezimbili. Yini okufanele uyenze uma udinga ukuhlukanisa amatafula ahlobene ahambelana nefayela elifanayo? Ungakwenza lokhu ngokukopisha imininingwane bese uyinamathisela endaweni efanayo namanani. Ngendlela, ngendlela efanayo, ungaphula ukuxhumana phakathi kwamazinga wedatha ngayinye yezincwadi ezahlukene ngaphandle kokuphula ukuxhumana okuvamile phakathi kwamafayela. Ake sibheke ukuthi le ndlela isebenza kanjani ekusebenzeni kwayo.
- Khetha ibanga esifuna ukususa kulo isixhumanisi kwelinye ithebula. Sichofoza kukho inkinobho yegundane elifanele. Kumenyu evulayo, khetha Kopisha. Esikhundleni salezi zenzo, ungathayipha enye inhlanganisela yezinkinobho ezishisayo I-Ctrl + C.
- Ngaphezu kwalokho, ngaphandle kokususa okukhethiwe kucezu olufanayo, chofoza futhi kulo. Kulokhu ohlwini lwezenzo, chofoza isithonjana "Amanani"elibekwe eqenjini leThuluzi Faka Izinketho.
- Ngemuva kwalokho, zonke izixhumanisi ebangeni elikhethiwe zizothathelwa indawo ngamanani we-static.
Njengoba ukwazi ukubona, e-Excel kunezindlela namathuluzi okuxhumanisa amatafula ambalwa ndawonye. Ngasikhathi sinye, idatha ye-tabular ingaba kwamanye amashidi ngisho nakwezincwadi ezahlukahlukene. Uma kunesidingo, lokhu kuxhumana kungaphulwa kalula.
