Ukuqhathanisa amafayela angu-2 ku-Excel ukuze uthole umehluko

Umsebenzisi ngamunye angase ahlangabezane nesimo lapho edinga ukuqhathanisa amathebula amabili. Nokho, njengendlela yokugcina, wonke umuntu kufanele aqhathanise amakholomu amabili. Yebo, kunjalo, ukusebenza ngamafayela e-Excel kulula kakhulu futhi kunethezekile. Uxolo, lesi akusona isiqhathaniso. Yebo, ukuhlelwa okubonakalayo kwetafula elincane kungenzeka, kodwa lapho inani lamaseli lingena ezinkulungwaneni, kufanele usebenzise amathuluzi okuhlaziya engeziwe.

Ngeshwa, induku yomlingo ayikavulwa ekuvumela ukuthi uqhathanise ngokuzenzakalelayo lonke ulwazi komunye nomunye ngokuchofoza okukodwa. Ngakho-ke, kufanele usebenze, okungukuthi, ukuqoqa idatha, ucacise amafomula adingekayo futhi wenze ezinye izenzo ezikuvumela ukuba wenze ukuqhathanisa okungenani kancane.

Ziningi izenzo ezinjalo. Ake sibheke ezinye zazo.

Iyini inhloso yokuqhathanisa amafayela e-Excel?

Kungaba nenani elikhulu lezizathu zokuthi kungani amafayela amaningi e-Excel aqhathaniswa. Ngokushesha noma kamuva, umsebenzisi ngamunye ubhekana nesidingo esinjalo, futhi akanayo imibuzo enjalo. Isibonelo, ungase ufune ukuqhathanisa idatha evela emibikweni emibili yezindawo ezihlukene ukuze ubone ukuthi izimali zehlile noma zehlile.

Noma-ke uthisha udinga ukubona ukuthi yibaphi abafundi abaxoshwa enyuvesi ngokuqhathanisa ukubunjwa kweqembu labafundi ngonyaka odlule kanye nalo nyaka.

Kungaba nenani elikhulu lezimo ezinjalo. Kodwa ake siqhubeke nokuzilolonga, ngoba isihloko siyinkimbinkimbi.

Zonke Izindlela Zokuqhathanisa Amathebula ama-2 ku-Excel

Nakuba isihloko siyinkimbinkimbi, silula. Yebo, ungamangali. Iyinkimbinkimbi ngoba yakhiwe izingxenye eziningi. Kodwa lezi zingxenye ngokwazo kulula ukuziqonda nokwenza. Ake sibheke ukuthi ungaqhathanisa kanjani amaspredishithi amabili e-Excel, ngokuqondile ekusebenzeni.

Ifomula Yokulingana kanye Nokuhlolwa Kwamanga-Iqiniso

Ake siqale, kunjalo, ngendlela elula kakhulu. Le ndlela yokuqhathanisa imibhalo ingenzeka, futhi ngaphakathi kwebanga elibanzi. Awukwazi ukuqhathanisa amanani wombhalo kuphela, kodwa futhi nezinombolo. Ake sithathe isibonelo esincane. Ake sithi sinezigaba ezimbili ezinamaseli efomethi yenombolo. Ukuze wenze lokhu, mane ubhale ifomula yokulingana =C2=E2. Uma kuvela ukuthi ayalingana, okuthi “TRUE” kuzobhalwa kuseli. Uma behluka, KUTHI AMANGA. Ngemva kwalokho, udinga ukudlulisa le fomula kubo bonke ububanzi usebenzisa umaka wokuqedela ngokuzenzakalela.

Manje umehluko ubonakala ngeso lenyama.

Ukugqamisa Amanani Ahlukile

Ungenza futhi amanani ahluke kwamanye agqanyiswe ngombala okhethekile. Lona futhi umsebenzi olula kakhulu. Uma kwanele ukuthi uthole umehluko phakathi kwezigaba ezimbili zamanani noma wonke amathebula, udinga ukuya kuthebhu ethi "Ekhaya", bese ukhetha into ethi "Thola futhi gqamisa" lapho. Ngaphambi kokuthi uyichofoze, qiniseka ukuthi ugqamisa isethi yamaseli agcina imininingwane ukuze iqhathaniswe. 

Kumenyu evelayo, chofoza kumenyu "Khetha iqembu lamaseli ...". Okulandelayo, iwindi lizovuleka lapho sidinga khona ukukhetha umehluko ngemigqa njengombandela.

Ukuqhathanisa amathebula angu-2 kusetshenziswa ukufometha okunemibandela

Ukufometha okunemibandela kuyindlela elula kakhulu futhi, okubalulekile, esebenzayo evumela ukuthi ukhethe umbala ozogqamisa inani elihlukile noma elifanayo. Ungathola le nketho kuthebhu ethi Ikhaya. Lapho ungathola inkinobho enegama elifanele futhi ohlwini oluvelayo, khetha "Phatha imithetho". Kuzovela umphathi womthetho, lapho sidinga ukukhetha imenyu ethi "Dala Umthetho".

Okulandelayo, ohlwini lwemibandela, sidinga ukukhetha leyo lapho ithi kudingeka sisebenzise ifomula ukuze sinqume amaseli azofomethwa ngendlela ekhethekile. Encazelweni yomthetho, udinga ukucacisa ifomula. Esimweni sethu, lokhu kungu-=$C2<>$E2, ngemva kwalokho siqinisekisa izenzo zethu ngokucindezela inkinobho ethi “Fometha”. Ngemuva kwalokho, sibeka ukubonakala kweseli futhi sibone ukuthi siyayithanda yini ngefasitela elincane elikhethekile elinesampula. 

Uma konke kuvumelana, chofoza inkinobho ethi "KULUNGILE" futhi uqinisekise izenzo.

Kumphathi Wemithetho Yokufometha Okunemibandela, umsebenzisi angathola yonke imithetho yokufometha esebenzayo kule dokhumenti. 

COUNTIF umsebenzi + imithetho yokuqhathanisa ithebula

Zonke izindlela esizichazile ekuqaleni zilungele lawo mafomethi anefomethi efanayo. Uma amatafula ayengazange a-odwe ngaphambilini, khona-ke indlela engcono kakhulu ukuqhathanisa amatafula amabili usebenzisa umsebenzi COUNTIF kanye nemithetho. 

Ake sicabange ukuthi sinezigaba ezimbili ezinolwazi oluhluke kancane. Sibhekene nomsebenzi wokuziqhathanisa nokuqonda ukuthi iyiphi inzuzo ehlukile. Okokuqala udinga ukuyikhetha ebangeni lokuqala bese uye kuthebhu "Ekhaya". Lapho sithola into eyayijwayeleke ngaphambilini “Ukufometha Okunemibandela”. Sakha umthetho futhi sibeka umthetho wokusebenzisa ifomula. 

Kulesi sibonelo, ifomula injengoba kukhonjisiwe kulesi sithombe-skrini.

Ngemva kwalokho, sibeka ifomethi njengoba kuchazwe ngenhla. Lo msebenzi uhlaziya inani eliqukethwe kuseli C1 futhi ubheka ububanzi obushiwo kufomula. Ihambisana nekholomu yesibili. Kudingeka sithathe lo mthetho futhi siwukopishe kulo lonke uhla. Hooray, wonke amaseli anamanani angaphindi ayagqanyiswa.

Umsebenzi we-VLOOKUP wokuqhathanisa amathebula angu-2

Kule ndlela, sizocubungula umsebenzi VPR, ehlola ukuthi ingabe kukhona okufanayo emathebula amabili. Ukuze wenze lokhu, udinga ukufaka ifomula eboniswe esithombeni esingezansi bese uyidlulisela kulo lonke uhla olusetshenziselwa ukuqhathanisa.

Lo msebenzi uphindaphinda inani ngalinye futhi ubona ukuthi azikho yini izimpinda ukusuka kukholomu yokuqala ukuya kweyesibili. Nokho, ngemva kokwenza yonke imisebenzi, leli nani libhalwa kuseli. Uma ingekho, khona-ke sithola iphutha elithi #N/A, elanele ukuqonda ngokuzenzakalelayo ukuthi yiliphi inani elingeke lifane.

Umsebenzi we-IF

Umsebenzi onengqondo IF - lena enye indlela enhle yokuqhathanisa izigaba ezimbili. Isici esiyinhloko sale ndlela ukuthi ungasebenzisa kuphela ingxenye yohlu oluqhathaniswayo, hhayi itafula lonke. Lokhu konga izinsiza zakho kokubili ikhompuyutha kanye nomsebenzisi.

Ake sithathe isibonelo esincane. Sinamakholomu amabili - A kanye no-B. Kudingeka siqhathanise olunye ulwazi olukuwo omunye nomunye. Ukuze senze lokhu, sidinga ukulungiselela enye ikholomu yesevisi C, lapho kubhalwe khona ifomula elandelayo.

Ukusebenzisa ifomula esebenzisa imisebenzi IF, IFERROR и OKUNINGI KUDANWE ungakwazi ukuphindaphinda phezu kwazo zonke izici ezifiselekayo zekholomu A, bese kuba kukholamu B. Uma itholakale kumakholomu B kanye no-A, bese ibuyiselwa kuseli elihambisanayo.

I-VBA macro

I-macro iyinkimbinkimbi kakhulu, kodwa futhi indlela ethuthuke kakhulu yokuqhathanisa amatafula amabili. ezinye izinketho zokuqhathanisa ngokuvamile azinakwenzeka ngaphandle kwemibhalo ye-VBA. Zikuvumela ukuthi wenze inqubo ngokuzenzakalelayo futhi wonge isikhathi. Yonke imisebenzi edingekayo yokulungiswa kwedatha, uma ihlelwe kanye, izoqhubeka yenziwa.

Ngokusekelwe enkingeni ezoxazululwa, ungabhala noma yiluphi uhlelo oluqhathanisa idatha ngaphandle kokungenelela komsebenzisi.

Ungaqhathanisa kanjani amafayela ku-Excel

Uma umsebenzisi uzibekele umsebenzi (kahle, noma unikezwe eyodwa) ukuqhathanisa amafayela amabili, khona-ke lokhu kungenziwa ngezindlela ezimbili ngesikhathi esisodwa. Owokuqala usebenzisa umsebenzi okhethekile. Ukuze usebenzise le ndlela, landela imiyalelo:

1. Vula amafayela ofuna ukuwaqhathanisa.
2. Vula ithebhu "Buka" - "Iwindi" - "Buka eceleni".

Ngemva kwalokho, amafayela amabili azovulwa kudokhumenti eyodwa ye-Excel.

Okufanayo kungenziwa ngamathuluzi ajwayelekile weWindows. Okokuqala udinga ukuvula amafayela amabili kumafasitela ahlukene. Ngemva kwalokho, thatha iwindi elilodwa bese ulihudulela ohlangothini lwesobunxele kakhulu kwesikrini. Ngemuva kwalokho, vula iwindi lesibili bese ulihudulela kwesokudla kakhulu. Ngemva kwalokho, amafasitela amabili azobe ehlangene. 

Ukufometha okunemibandela ukuze kuqhathaniswe amafayela angu-2 e-excel

Ngokuvamile ukuqhathanisa amadokhumenti kusho ukuwaveza eduze komunye nomunye. Kodwa kwezinye izimo, kungenzeka ukwenza ngokuzenzakalelayo le nqubo usebenzisa ukufometha okunemibandela. Ngayo, ungabheka ukuthi kukhona yini umehluko phakathi kwamashidi. Lokhu kukuvumela ukuthi wonge isikhathi esingasetshenziselwa ezinye izinjongo.

Okokuqala, sidinga ukudlulisa amashidi aqhathanisiwe kudokhumenti eyodwa. 

Ukuze wenze lokhu, udinga uchofoze kwesokudla eshidini elifanele, bese uchofoza inkinobho ethi "Hambisa noma kopisha" kumenyu ye-pop-up. Okulandelayo, kuzovela ibhokisi lengxoxo lapho umsebenzisi angakhetha khona idokhumenti okufanele leli shidi lifakwe kulo.

Okulandelayo, udinga ukukhetha wonke amaseli oyifunayo ukuze ubonise wonke umehluko. Indlela elula yokwenza lokhu iwukuchofoza iseli engenhla kwesokunxele, bese ucindezela inhlanganisela yokhiye Ctrl + Shift + End.

Ngemva kwalokho, iya efasiteleni lokufometha elinemibandela bese udala umthetho omusha. Njengombandela, sisebenzisa ifomula efanelekile esimweni esithile, bese sibeka ifomethi.

ukunakwa: amakheli amaseli kufanele akhonjiswe lawo akwelinye ishidi. Lokhu kungenziwa ngemenyu yokufaka yefomula.

Ukuqhathanisa idatha ku-Excel emashidini ahlukene

Ake sithi sinohlu lwabasebenzi olubalula namaholo abo. Lolu hlu lubuyekezwa njalo ngenyanga. Lolu hlu lukopishelwe eshidini elisha.

Ake sithi sidinga ukuqhathanisa amaholo. Kulesi simo, ungasebenzisa amathebula avela kumakhasi ahlukene njengedatha. Sizosebenzisa ukufometha okunemibandela ukuze sigqamise umehluko. Konke kulula.

Ngokufometha okunemibandela, ungenza iziqhathaniso ezisebenzayo ngisho noma amagama abasebenzi ehlelwe ngendlela ehlukile.

Ungaqhathanisa kanjani amashidi ama-2 kusipredishithi se-excel

Ukuqhathaniswa kolwazi olutholakala emashidini amabili kwenziwa kusetshenziswa umsebenzi OKUNINGI KUDANWE. Njengepharamitha yayo yokuqala, kunepheya lamanani okufanele uwabheke eshidini elinesibopho senyanga ezayo. Kalula nje, Mashi. Singakhetha ububanzi obubukiwe njengeqoqo lamaseli ayingxenye yobubanzi obuqanjiwe, ehlanganiswe ngababili.

Ngakho ungakwazi ukuqhathanisa izintambo ngokwemibandela emibili - isibongo kanye nomholo. Well, noma yimuphi omunye, kuchazwe umsebenzisi. Kuwo wonke amameshi angatholakala, inombolo ibhalwa kuseli lapho kufakwa khona ifomula. Ku-Excel, leli nani liyohlala liyiqiniso. Ngakho-ke, ukuze ukufometha kusetshenziswe kulawo maseli abehlukene, udinga ukufaka esikhundleni saleli nani AMANGA, usebenzisa umsebenzi =HAYI().

Ithuluzi lokuqhathanisa lesipredishithi

I-Excel inethuluzi elikhethekile elikuvumela ukuthi uqhathanise amaspredishithi futhi ugqamise izinguquko ngokuzenzakalelayo. 

Kubalulekile ukuqaphela ukuthi leli thuluzi litholakala kuphela kulabo basebenzisi abathenge amahhovisi e-Professional Plus.

Ungayivula ngokuqondile kuthebhu ethi “Ekhaya” ngokukhetha into ethi “Qhathanisa Amafayela”.

Ngemuva kwalokho, kuzovela ibhokisi lengxoxo lapho udinga ukukhetha inguqulo yesibili yencwadi. Ungaphinda ufake ikheli le-inthanethi lapho leli bhuku likhona.

Ngemva kokukhetha izinguqulo ezimbili zedokhumenti, sidinga ukuqinisekisa izenzo zethu ngokhiye othi KULUNGILE.

Kwezinye izimo, iphutha lingase lenziwe. Uma ivela, ingase ibonise ukuthi ifayela livikelwe ngephasiwedi. Ngemva kokuchofoza okuthi KULUNGILE, uzocelwa ukuthi uyifake. 

Ithuluzi lokuqhathanisa libukeka njengamaspredishithi e-Excel amabili eduze kwelinye ngaphakathi kwewindi elifanayo. Kuye ngokuthi ulwazi lwengeziwe, lususiwe, noma kube khona ushintsho kufomula (kanye nezinye izinhlobo zezenzo), izinguquko zigqanyiswa ngemibala ehlukene. 

Indlela yokuhumusha imiphumela yokuqhathanisa

Kulula kakhulu: izinhlobo ezahlukene zomehluko ziboniswa ngemibala ehlukene. Ukufometha kungadlulela kukho kokubili ukugcwaliswa kweseli kanye nombhalo ngokwawo. Ngakho-ke, uma idatha ifakwe esitokisini, khona-ke ukugcwaliswa kuluhlaza. Uma okuthile kungacaci, isevisi ngokwayo iba nezimpawu ezibonisa ukuthi yiluphi uhlobo loshintsho olugqanyiswe ngamuphi umbala.