Ungaqhathanisa kanjani amakholomu amabili ku-Excel futhi ukhiphe izimpinda (gqamisa, faka umbala, hambisa)

Lesi sihloko sizokuthatha cishe imizuzu eyi-10 ukuze usifunde. Emizuzwini emi-5 elandelayo, ungakwazi ukuqhathanisa kalula amakholomu amabili ku-Excel futhi uthole ukuthi akhona yini amakhophi kuwo, uwasuse noma uwagqamise ngombala. Ngakho, isikhathi sesifikile!

I-Excel iwuhlelo lokusebenza olunamandla kakhulu futhi olupholile ngempela lokudala nokucubungula inani elikhulu ledatha. Uma unezincwadi zokusebenza ezimbalwa ezinedatha (noma ithebula elikhulu nje elilodwa), cishe ufuna ukuqhathanisa amakholomu angu-2, uthole amanani ayimpinda, bese wenza okuthile ngawo, isibonelo, susa, gqamisa noma usule okuqukethwe . Amakholomu angaba etafuleni elifanayo, asondelene noma angabi eduze, angase abekwe emashidini ama-2 ahlukene noma ngisho nasezincwadini ezahlukene.

Ake sithi sinamakholomu angu-2 anamagama abantu – amagama angu-5 ikholomu ngayinye A namagama ama-3 kukholamu B. Udinga ukuqhathanisa amagama kulawa makholomu amabili futhi uthole izimpinda. Njengoba uqonda, lena idatha engelona iqiniso, ethathwe njengesibonelo kuphela. Kumathebula angempela, sibhekene nezinkulungwane noma amashumi ezinkulungwane zamarekhodi.

Inketho A: womabili amakholomu aseshidini elifanayo. Ngokwesibonelo, ikholomu A kanye nekholomu B.

Inketho B: Amakholomu asemashidini ahlukene. Ngokwesibonelo, ikholomu A eshidini Ishidi2 kanye nekholomu A eshidini Ishidi3.

I-Excel 2013, 2010 kanye no-2007 inethuluzi elakhelwe ngaphakathi Susa izimpinda (Susa Izimpinda) kodwa ayinawo amandla kulesi simo njengoba ayikwazi ukuqhathanisa idatha kumakholomu angu-2. Ngaphezu kwalokho, ingasusa kuphela izimpinda. Azikho ezinye izinketho ezifana nokugqamisa noma ukushintsha imibala. Futhi iphuzu!

Okulandelayo, ngizokukhombisa izindlela ezingenzeka zokuqhathanisa amakholomu amabili ku-Excel, okuzokuvumela ukuthi uthole futhi ususe amarekhodi ayimpinda.

• Qhathanisa amakholomu angu-2 bese ubheka izimpinda usebenzisa amafomula
  
  • Inketho A: womabili amakholomu aseshidini elifanayo
  • Inketho B: Amakholomu asemashidini ahlukene noma ezincwadini zokusebenza ezahlukene
• Ukucutshungulwa kwezimpinda ezitholiwe
  
  • Bonisa imigqa eyimpinda kuphela kukholomu A
  • Shintsha umbala noma gqamisa izimpinda ezitholiwe
  • Susa izimpinda kukholomu yokuqala

Qhathanisa amakholomu angu-2 ku-Excel futhi uthole okufakiwe okuyimpinda usebenzisa amafomula

Inketho A: womabili amakholomu aseshidini elifanayo

1. Kuseli lokuqala elingenalutho (esibonelweni sethu, leli yiseli C1), sibhala ifomula elandelayo:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Kwifomula yethu A1 leli yiseli lokuqala lekholomu yokuqala esizoliqhathanisa. $B$1 и $B$10000 lawa amakheli amaseli okuqala nawokugcina ekholomu yesibili, esizokwenza ngawo ukuqhathanisa. Qaphela izinkomba eziphelele - izinhlamvu zekholomu nezinombolo zomugqa zandulelwa uphawu lwedola ($). Ngisebenzisa izinkomba eziphelele ukuze amakheli eseli ahlale efana lapho ngikopisha amafomula.

   Uma ufuna ukuthola izimpinda kukholamu B, shintsha izinkomba ukuze ifomula ibukeke kanje:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Kunalokho “kuphela"Futhi"okubili»Ungabhala amalebula akho, isibonelo, «Ayitholakali"Futhi"Kutholakale", noma ushiye kuphela"okubili' bese ufaka uhlamvu lwesikhala esikhundleni senani lesibili. Esimeni sakamuva, amaseli angatholakali amampinda azohlala engenalutho, futhi, ngikholwa ukuthi, lokhu kumelwa kwedatha kulungele kakhulu ukuhlaziywa okwengeziwe.

2. Manje ake sikopishe ifomula yethu kuwo wonke amaseli kukholamu C, kuze kube phansi kumugqa ongezansi, oqukethe idatha kukholomu A. Ukuze wenze lokhu, hambisa isikhombisi segundane ekhoneni elingezansi kwesokudla seseli C1, i-pointer izothatha uhlobo lwe-crosshair emnyama, njengoba kuboniswe esithombeni esingezansi:Chofoza futhi ubambe inkinobho yegundane kwesokunxele bese uhudulela phansi umngcele wozimele, ugqamisa wonke amaseli lapho ufuna ukufaka khona ifomula. Uma wonke amaseli adingekayo ekhethiwe, khulula inkinobho yegundane:

   

Ithiphu: Kumathebula amakhulu, ukukopisha ifomula kuzoshesha uma usebenzisa izinqamuleli zekhibhodi. Gqamisa iseli C1 bese ucindezela Ctrl + C (ukuze ukopishele ifomula ebhodini lokunamathisela), bese uchofoza Ctrl + Shift + End (ukukhetha wonke amaseli angangenalutho kukholamu C) bese ekugcineni ucindezela Ctrl + V (ukufaka ifomula kuwo wonke amaseli akhethiwe).

3. Kuhle, manje wonke amanani ayimpinda amakwe ngokuthi “okubili":

Inketho B: amakholomu amabili asemashidini ahlukene (ezincwadini zokusebenza ezihlukene)

1. Kuseli lokuqala lekholomu yokuqala engenalutho ekhasini lokusebenza Ishidi2 (kithi kuyikholomu B) faka ifomula elandelayo:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Lapha Ishidi3 igama leshidi lapho kukholamu yesi-2, futhi $A$1:$10000 amakheli eseli ukusuka koku-1 ukuya kokugcina kule kholomu yesi-2.

2. Kopisha ifomula kuwo wonke amaseli kukholamu B (kufana nenketho A).
3. Sithola lo mphumela:

Ukucutshungulwa kwezimpinda ezitholiwe

Kuhle, sithole okufakiwe kukholamu yokuqala nakho okukhona kukholamu yesibili. Manje kudingeka senze okuthile ngabo. Ukudlula kuwo wonke amarekhodi ayimpinda kuthebula mathupha kungasebenzi kahle futhi kuthatha isikhathi esiningi kakhulu. Kunezindlela ezingcono.

Bonisa imigqa eyimpinda kuphela kukholomu A

Uma amakholomu akho engenazo izihloko, kuzomele uwangeze. Ukuze wenze lokhu, beka ikhesa enombolweni emele umugqa wokuqala, futhi izophenduka ibe umcibisholo omnyama, njengoba kukhonjisiwe esithombeni esingezansi:

Chofoza kwesokudla bese ukhetha kumenyu yokuqukethwe Insertion (Faka):

Nikeza amagama kumakholomu, isibonelo, “Igama"Futhi"Phindaphinda?» Bese uvula ithebhu Idatha (Idatha) bese ucindezela Hlunga (Hlunga):

Ngemva kwalokho chofoza umcibisholo omncane ompunga eduze nokuthi “Phindaphinda?« ukuvula imenyu yokuhlunga; susa ukumaka zonke izinto ezikulolu hlu ngaphandle okubili, bese ucindezela OK.

Yilokho kuphela, manje ubona kuphela lezo zakhi zekholomu А, eziyimpinda kukholamu В. Kunamaseli anjalo amabili kuphela etafuleni lethu lokuqeqesha, kodwa, njengoba uqonda, ekusebenzeni kuzoba namanye amaningi.

Ukuze ubonise yonke imigqa yekholomu futhi А, chofoza uphawu lwesihlungi kukholamu В, manje esibukeka njengefaneli enomcibisholo omncane, bese ukhetha Khetha konke (Khetha konke). Noma ungenza okufanayo ngeRibhoni ngokuchofoza Idatha (Idatha) > Khetha & Hlunga (Hlunga & Hlunga) > Sula (Sula) njengoba kukhonjisiwe esithombeni esingezansi:

Shintsha umbala noma gqamisa izimpinda ezitholiwe

Uma amanothi "okubili” akwanele ngezinjongo zakho futhi ufuna ukumaka amaseli ayimpinda ngombala wefonti ohlukile, umbala wokugcwalisa noma enye indlela...

Kulokhu, hlunga izimpinda njengoba kukhonjisiwe ngenhla, khetha wonke amaseli ahlungiwe bese uchofoza Ctrl + 1ukuvula ibhokisi Fometha Amaseli (ifomethi yeseli). Njengesibonelo, ake siguqule umbala wokugcwalisa wamaseli emigqeni enezimpinda ube ophuzi ngokugqamile. Yiqiniso, ungashintsha umbala wokugcwalisa ngethuluzi Gcwalisa (Gcwalisa Umbala) ithebhu Ikhaya (Ekhaya) kodwa inzuzo yebhokisi lengxoxo Fometha Amaseli (Ifomethi Yeseli) ngokuthi ungakwazi ukumisa zonke izinketho zokufometha ngesikhathi esisodwa.

Manje ngeke nakanjani uphuthelwe yinoma yimaphi amaseli anamampinda:

Ikhipha amanani ayimpinda kusukela kukholomu yokuqala

Hlunga ithebula ukuze kuboniswe kuphela amaseli anamanani ayimpinda, bese ukhetha lawo maseli.

Uma amakholomu angu-2 owaqhathanisayo esemashidini ahlukene, okungukuthi, kumathebula ahlukene, chofoza kwesokudla ububanzi obukhethiwe bese ukhetha Susa umugqa (Susa umugqa):

Cindezela OKlapho i-Excel ikucela ukuthi uqinisekise ukuthi ngempela ufuna ukususa wonke umugqa weshidi bese usula isihlungi. Njengoba ubona, kusele imigqa enamanani ahlukile kuphela:

Uma amakholomu angu-2 eseshidini elifanayo, eduze komunye nomunye (okuseduze) noma kungasondelene (hhayi eduze), khona-ke inqubo yokukhipha izimpinda izoba yinkimbinkimbi kancane. Asikwazi ukususa wonke umugqa onamanani ayimpinda, njengoba lokhu kuzokhipha amaseli kukholamu yesibili futhi. Ngakho-ke ukushiya okufakiwe okuyingqayizivele kuphela kukholamu А, yenza lokhu:

1. Hlunga ithebula ukuze ubonise amanani ayimpinda kuphela bese ukhetha lawo maseli. Chofoza kwesokudla kuzo bese ukhetha kumenyu yokuqukethwe Sula okuqukethwe (cacile okuqukethwe).
2. Hlanza isihlungi.
3. Khetha wonke amaseli kukholamu А, eqala esitokisini A1 yonke indlela yehle iye ezansi equkethe idatha.
4. Chofoza Idatha (Idatha) bese ucindezela Hlunga u-A kuye ku-Z (Hlunga ukusuka ku-A kuye ku-Z). Ebhokisini lengxoxo elivulayo, khetha Qhubeka nokukhetha kwamanje (Hlunga phakathi kokukhethiwe okucacisiwe) bese uchofoza inkinobho Black (Ukuhlunga):
5. Susa ikholomu ngefomula, ngeke usayidinga, kusukela manje usunamanani ayingqayizivele kuphela.
6. Yilokho, manje ikholomu А iqukethe idatha eyingqayizivele kuphela engekho kukholamu В:

Njengoba ubona, ukususa izimpinda kumakholomu amabili ku-Excel usebenzisa amafomula akunzima kangako.