Ungaqhathanisa kanjani izinhlu ezimbili ku-Excel

I-Excel wuhlelo olusebenzayo lokucubungula idatha. Futhi enye yezindlela zokuhlaziya ulwazi ukuqhathanisa izinhlu ezimbili. Uma uqhathanisa kahle izinhlu ezimbili ku-Excel, ukuhlela le nqubo kuzoba lula kakhulu. Kwanele nje ukulandela amanye amaphuzu okuzoxoxwa ngawo namuhla. Ukuqaliswa okungokoqobo kwale ndlela kuncike ngokuphelele ezidingweni zomuntu noma inhlangano ngesikhathi esithile. Ngakho-ke, izimo eziningana ezingenzeka kufanele zicatshangelwe.

Ukuqhathanisa izinhlu ezimbili ku-Excel

Kunjalo, ungakwazi ukuqhathanisa izinhlu ezimbili ngesandla. Kodwa kuzothatha isikhathi eside. I-Excel inekhithi yayo yamathuluzi ehlakaniphile ezokuvumela ukuthi uqhathanise idatha hhayi ngokushesha nje kuphela, kodwa futhi uthole imininingwane okungelula ukuyithola ngamehlo akho. Ake sithi sinamakholomu amabili anezixhumanisi A no-B. Amanye amanani aphindwa kuwo.

Ukwakhiwa kwenkinga

Ngakho sidinga ukuqhathanisa lawa makholomu. Inqubo yokuqhathanisa imibhalo emibili imi kanje:

1. Uma amaseli ayingqayizivele alolu hlu ngalunye afana, futhi inani eliphelele lamaseli ahlukile liyafana, namaseli ayafana, khona-ke lezi zinhlu zingabhekwa njengezifanayo. Ukuhleleka okustakiwe amanani akulolu hlu akunandaba kangako.
2. Singakhuluma ngokuhlangana okuyingxenye kohlu uma amanani ahlukile ngokwawo afana, kodwa inani lokuphindaphinda lihlukile. Ngakho-ke, izinhlu ezinjalo zingaqukatha inani elihlukile lezakhi.
3. Iqiniso lokuthi lezi zinhlu ezimbili azifani kuboniswa isethi ehlukile yamanani ahlukile.

Zonke lezi zimo ezintathu kanyekanye ziyizimo zenkinga yethu.

Isixazululo senkinga

Masikhiqize ububanzi obubili obuguqukayo ukuze kube lula ukuqhathanisa izinhlu. Ngayinye yazo izohambisana nohlu ngalunye.

Ukuze uqhathanise izinhlu ezimbili, yenza lokhu okulandelayo:

1. Kwikholomu ehlukile, sakha uhlu lwamanani ahlukile aqondile kuzo zombili izinhlu. Kulokhu sisebenzisa ifomula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Ifomula ngokwayo kufanele ibhalwe njengefomula yamalungu afanayo.
2. Ake sinqume ukuthi inani ngalinye eliyingqayizivele lenzeka kangaki ohlwini lwedatha. Nanka amafomula okwenza lokhu: =COUNTIF(Uhlu1,D5) kanye =COUNTI(Uhlu2,D5).
3. Uma kokubili inani lokuphindaphinda kanye nenani lamanani ahlukile kufana kuzo zonke izinhlu ezifakwe kulobu bubanzi, umsebenzi ubuyisela inani elingu-0. Lokhu kukhombisa ukuthi umdlalo u-XNUMX%. Kulokhu, izihloko zalolu hlu zizothola ingemuva eliluhlaza.
4. Uma konke okuqukethwe okuhlukile kukuzo zombili izinhlu, bese kubuyiswa ngamafomula =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) inani lizoba uziro. Uma i-E1 ingenawo uziro, kodwa inani elinjalo liqukethwe kumaseli E2 no-F2, khona-ke kulesi simo ububanzi buzobonwa njengokufana, kodwa kancane. Kulokhu, izihloko zezinhlu ezihambisanayo zizoba owolintshi.
5. Futhi uma eyodwa yamafomula achazwe ngenhla ibuyisela inani elingelona uziro, izinhlu ngeke zifaniswe ngokuphelele.

Lena yimpendulo yombuzo wokuthi ahlaziywa kanjani amakholomu amameshi kusetshenziswa amafomula. Njengoba ungabona, ngokusetshenziswa kwemisebenzi, ungakwazi ukusebenzisa cishe noma yimuphi umsebenzi, lapho uqala nje, awuhlobene nezibalo.

Ukuhlola Isibonelo

Enguqulweni yethu yethebula, kunezinhlobo ezintathu zezinhlu zohlobo ngalunye oluchazwe ngenhla. Inokumesha kancane futhi ngokuphelele, kanye nokungafani.

Ukuze siqhathanise idatha, sisebenzisa ububanzi A5:B19, lapho sifaka khona lezi zipheya zohlu ngokuhlukile. Mayelana nokuthi kuzoba yini umphumela wokuqhathanisa, sizoqonda ngombala wezinhlu zokuqala. Uma behluke ngokuphelele, khona-ke kuyoba isizinda esibomvu. Uma ingxenye yedatha ifana, bese iphuzi. Endabeni yobunikazi obuphelele, izihloko ezihambisanayo zizoba luhlaza. Indlela yokwenza umbala kuye ngokuthi umphumela uyini? Lokhu kudinga ukufometha okunemibandela.

Ukuthola umehluko ezinhlwini ezimbili ngezindlela ezimbili

Ake sichaze ezinye izindlela ezimbili zokuthola umehluko, kuye ngokuthi izinhlu ziyavumelana yini noma cha.

Inketho 1. Uhlu Oluvumelanayo

Lena inketho elula. Ake sithi sinezinhlu ezinjalo.

Ukunquma ukuthi zingaki izikhathi amanani angazange ahlangane, ungasebenzisa ifomula: =SUMPRODUCT(—(A2:A20<>B2:B20)). Uma sithole u-0 njengomphumela, lokhu kusho ukuthi lezi zinhlu ezimbili ziyefana.

Inketho 2: Uhlu Oluxubekile

Uma izinhlu zingafani ngokuhleleka kwezinto eziziqukethe, udinga ukusebenzisa isici esifana nokufometha okunemibandela futhi ufake umbala amanani ayimpinda. Noma sebenzisa umsebenzi COUNTIF, sisebenzisa esinquma ukuthi i-elementi esohlwini olulodwa ivela kangaki kolwesibili.

Ungaqhathanisa kanjani amakholomu angu-2 umugqa ngomugqa

Uma siqhathanisa amakholomu amabili, ngokuvamile sidinga ukuqhathanisa ulwazi olusemigqa ehlukene. Ukwenza lokhu, opharetha uzosisiza IF. Ake sibheke ukuthi isebenza kanjani ekusebenzeni. Ukuze senze lokhu, sethula izimo ezimbalwa zemifanekiso.

Isibonelo. Ungaqhathanisa kanjani amakholomu angu-2 omentshisi kanye nomehluko kumugqa owodwa

Ukuhlaziya ukuthi amanani asemugqeni ofanayo kodwa amakholomu ahlukene ayafana, sibhala umsebenzi. IF. Ifomula ishuthekwa emugqeni ngamunye obekwe kukholomu eyisiza lapho imiphumela yokucubungula idatha izoboniswa. Kodwa akudingekile nakancane ukuyichaza kumugqa ngamunye, vele uyikopishele kumaseli asele ale kholomu noma usebenzise umaka wokuqedela ngokuzenzakalela.

Kufanele sibhale phansi ifomula enjalo ukuze siqonde ukuthi amanani kuwo womabili amakholomu ayafana noma cha: =IF(A2=B2, “Fanisa”, “”). I-logic yalo msebenzi ilula kakhulu: iqhathanisa amanani kumaseli A2 no-B2, futhi uma afana, abonisa inani elithi "Coincide". Uma idatha ihlukile, ayibuyiseli noma yiliphi inani. Ungahlola futhi amaseli ukuze ubone ukuthi kukhona okufanayo phakathi kwawo. Kulokhu, ifomula esetshenziswayo ithi: =IF(A2<>B2, “Akufani”, “”). Isimiso siyefana, okokuqala isheke liyenziwa. Uma kuvela ukuthi amaseli ahlangabezana nombandela, kusho ukuthi inani elithi “Akufani” liyaboniswa.

Kungenzeka futhi ukusebenzisa ifomula elandelayo kunkambu yefomula ukuze ubonise kokubili okuthi “Fanisa” uma amanani afana, kanye nokuthi “Awafani” uma ehlukile: =IF(A2=B2; “Fanisa”, “Akufani”). Ungasebenzisa futhi u-opharetha wokungalingani esikhundleni sika-opharetha wokulingana. Ukuhleleka kuphela kwamanani azoboniswa kuleli cala kuzohluka kancane: =IF(A2<>B2, “Akufani”, “Kwaqondana”). Ngemva kokusebenzisa inguqulo yokuqala yefomula, umphumela uzoba kanje.

Lokhu kuhluka kwefomula akuzweli. Ngakho-ke, uma amanani kukholamu eyodwa ehluka kwamanye kuphela ngoba ebhalwe ngosonhlamvukazi, uhlelo ngeke luwubone lo mehluko. Ukuze wenze ukuqhathanisa kuzwele, udinga ukusebenzisa umsebenzi kumibandela ISEXOLO. Ezinye izimpikiswano zishiywe zingashintshiwe: =IF(QINISO(A2,B2), “Fanisa”, “Okuhlukile”).

Ungaqhathanisa kanjani amakholomu amaningi kumameshi emugqeni owodwa

Kungenzeka ukuhlaziya amanani ohlwini ngokuya ngesethi yonke yemibandela:

1. Thola leyo migqa enamanani afanayo yonke indawo.
2. Thola leyo migqa lapho kukhona okufanayo ezinhlwini ezimbili nje.

Ake sibheke izibonelo ezimbalwa zendlela yokuqhubeka esimweni ngasinye salezi zimo.

Isibonelo. Indlela yokuthola okufanayo kumugqa owodwa kumakholomu amaningi ethebula

Ake sithi sinochungechunge lwamakholomu aqukethe ulwazi esiludingayo. Sibhekene nomsebenzi wokunquma leyo migqa lapho amanani afana khona. Ukuze wenze lokhu, udinga ukusebenzisa ifomula elandelayo: =IF(FUTHI(A2=B2,A2=C2), “fanisa”, ” “).

Uma kunamakholomu amaningi kakhulu etafuleni, udinga nje ukuwasebenzisa kanye nomsebenzi IF Opharetha COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”imeshi”;” “). Inombolo esetshenziswe kule fomula ikhombisa inani lamakholomu okufanele lihlolwe. Uma ihluka, kufanele ubhale okuningi njengoba kunjalo ngesimo sakho.

Isibonelo. Ungawathola kanjani amameshi emugqeni owodwa kunoma yimaphi amakholomu angu-2 ethebula

Ake sithi sidinga ukuhlola ukuthi amanani emugqeni owodwa ayafana yini kumakholomu amabili kulawo asethebulani. Ukuze wenze lokhu, udinga ukusebenzisa umsebenzi njengesimo OR, lapho ngokushintshana bhala ukulingana kwekholomu ngayinye kwenye. Nasi isibonelo.

Sisebenzisa le fomula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Kungase kube nesimo lapho kunamakholomu amaningi etafuleni. Kulokhu, ifomula izoba yinkulu, futhi kungase kuthathe isikhathi esiningi ukukhetha zonke izinhlanganisela ezidingekayo. Ukuze uxazulule le nkinga, udinga ukusebenzisa umsebenzi COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Iyunithi yezinhlamvu eyingqayizivele”; “Ayiyona iyunithi yezinhlamvu eyingqayizivele”)

Siyabona ukuthi isiyonke sinemisebenzi emibili COUNTIF. Ngeyokuqala, sinquma ngokuhlukile ukuthi mangaki amakholomu afana ne-A2, futhi ngeyesibili, sihlola inani lokufana nenani le-B2. Uma, ngenxa yokubala ngale fomula, sithola inani elinguziro, lokhu kubonisa ukuthi yonke imigqa kule kholomu ihlukile, uma ngaphezulu, kukhona ukufana. Ngakho-ke, uma ngenxa yokubala ngamafomula amabili nokwengeza imiphumela yokugcina sithola inani elinguziro, khona-ke inani lombhalo elithi “Iyunithi yezinhlamvu eyingqayizivele” liyabuyiselwa, uma le nombolo inkulu, kubhaliwe ukuthi le yunithi yezinhlamvu ayiyona into ehlukile.

Ungaqhathanisa kanjani amakholomu angu-2 ku-Excel ukuze uthole okufanayo

Manje ake sithathe isibonelo. Ake sithi sinetafula elinamakholomu amabili. Udinga ukuhlola ukuthi ziyahambisana yini. Ukuze wenze lokhu, udinga ukusebenzisa ifomula, lapho umsebenzi uzophinde usetshenziswe khona IF, kanye nomsebenzisi COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Akukho okufanayo kukholomu B”, “Kukhona okufanayo kukholomu B”)

Asikho esinye isenzo esidingekayo. Ngemva kokubala umphumela ngale fomula, sithola uma inani lempikiswano yesithathu yomsebenzi IF okufanayo. Uma zingekho, khona-ke okuqukethwe kwempikiswano yesibili.

Ungaqhathanisa kanjani amakholomu angu-2 ku-Excel ukuze uthole okufanayo futhi ugqanyiswe ngombala

Ukwenza kube lula ukuhlonza amakholomu afanayo, ungawagqamisa ngombala. Ukuze wenze lokhu, udinga ukusebenzisa umsebenzi we- "Conditional Formatting". Ake sibone ngokusebenza.

Ukuthola nokugqamisa okufanayo ngombala kumakholomu amaningi

Ukuze unqume okufanayo futhi uwagqamise, kufanele uqale ukhethe ibanga ledatha lapho ukuhlola kuzokwenziwa khona, bese uvula into ethi “Ukufometha Okunemibandela” kuthebhu ethi “Ekhaya”. Lapho, khetha "Izimpinda Zamanani" njengomthetho wokukhetha iseli.

Ngemuva kwalokho, kuzovela ibhokisi lengxoxo elisha, lapho ohlwini olungakwesokunxele lwe-pop-up sithola inketho ethi "Ukuphinda", futhi ohlwini olungakwesokudla sikhetha umbala ozosetshenziselwa ukukhetha. Ngemva kokuchofoza inkinobho ethi "KULUNGILE", ingemuva lawo wonke amaseli afanayo azokhethwa. Bese uqhathanisa amakholomu ngeso.

Ukuthola nokugqamisa imigqa emeshayo

Indlela yokuhlola ukuthi iyunithi yezinhlamvu iyahambelana yini ihluke kancane. Okokuqala, sidinga ukwakha ikholomu eyengeziwe, futhi lapho sizosebenzisa amanani ahlanganisiwe sisebenzisa & no-opharetha. Ukuze wenze lokhu, udinga ukubhala ifomula yefomu: =A2&B2&C2&D2.

Sikhetha ikholomu eyakhiwe futhi iqukethe amanani ahlanganisiwe. Okulandelayo, senza ukulandelana kwezenzo okufanayo okuchazwe ngenhla kumakholomu. Imigqa eyimpinda izogqanyiswa embaleni owucacisayo.

Siyabona ukuthi akukho okunzima ekubhekeni ukuphindaphinda. I-Excel iqukethe wonke amathuluzi adingekayo kulokhu. Kubalulekile ukuzilolongela nje ngaphambi kokusebenzisa lonke lolu lwazi.