Yadda ake kwatanta ginshiƙai biyu a cikin Excel kuma cire kwafi (haske, canza launi, motsawa)

Wannan labarin zai ɗauki kimanin mintuna 10 don karantawa. A cikin mintuna 5 masu zuwa, zaku iya kwatanta ginshiƙai biyu cikin sauƙi a cikin Excel kuma gano idan akwai kwafi a cikinsu, share su ko haskaka su cikin launi. Don haka, lokaci ya yi!

Excel aikace-aikace ne mai ƙarfi da gaske don ƙirƙira da sarrafa bayanai masu yawa. Idan kuna da littattafan aiki da yawa tare da bayanai (ko babban tebur ɗaya kawai), to tabbas kuna son kwatanta ginshiƙai 2, nemo kwafin dabi'u, sannan kuyi wani abu tare da su, alal misali, share, haskaka ko share abubuwan da ke ciki. ginshiƙai na iya kasancewa a cikin teburi ɗaya, suna kusa ko a'a, ana iya kasancewa a kan zanen gado 2 ko ma a cikin littattafai daban-daban.

Ka yi tunanin muna da ginshiƙai 2 tare da sunayen mutane - sunaye 5 a kowane shafi A da sunaye 3 a cikin ginshiƙi B. Kuna buƙatar kwatanta sunayen da ke cikin waɗannan ginshiƙai biyu kuma ku nemo kwafi. Kamar yadda kuka fahimta, wannan ƙagaggun bayanai ne, waɗanda aka ɗauka kawai misali. A cikin tebur na ainihi, muna ma'amala da dubban ko ma dubun dubatar bayanan.

Wani zaɓi A: duka ginshiƙan suna kan takarda ɗaya. Misali, shafi A da shafi B.

Zabin B: ginshiƙan suna kan zanen gado daban-daban. Misali, shafi A a kan takardar Takardar2 da shafi A a kan takardar Takardar3.

Excel 2013, 2010 da 2007 suna da kayan aiki da aka gina a ciki Cire Kwafin (Cire Kwafi) amma ba shi da ƙarfi a cikin wannan yanayin saboda ba zai iya kwatanta bayanai a cikin ginshiƙai 2 ba. Bugu da ƙari, zai iya cire kwafi kawai. Babu wasu zaɓuɓɓuka kamar haskakawa ko canza launuka. Kuma nuni!

Na gaba, zan nuna muku hanyoyin da za a iya kwatanta ginshiƙai biyu a cikin Excel, wanda zai ba ku damar nemowa da cire kwafin bayanan.

• Kwatanta ginshiƙai 2 kuma nemi kwafi ta amfani da dabaru
  
  • Zaɓin A: ginshiƙan biyu suna kan takarda ɗaya
  • Zaɓin B: ginshiƙan suna kan zanen gado daban-daban ko a cikin littattafan aiki daban-daban
• Ana aiwatar da kwafin da aka samo
  
  • Nuna kwafin layuka a shafi na A
  • Canja launi ko haskaka kwafi da aka samo
  • Cire kwafi daga ginshiƙi na farko

Kwatanta ginshiƙai 2 a cikin Excel kuma nemo shigarwar da aka kwafi ta amfani da dabaru

Zaɓin A: ginshiƙan biyu suna kan takarda ɗaya

1. A cikin tantanin halitta na farko (a cikin misalinmu, wannan shine cell C1), muna rubuta dabara mai zuwa:

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

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

   

   A cikin tsarin mu A1 wannan ita ce tantanin halitta na farko na rukunin farko da za mu kwatanta. $B$1 и $B$10000 Waɗannan su ne adiresoshin sel na farko da na ƙarshe na shafi na biyu, waɗanda za mu yi kwatancen da su. Yi la'akari da cikakkiyar nassoshi - haruffan ginshiƙi da lambobi jere suna gaba da alamar dala ($). Ina amfani da cikakkun bayanai domin adiresoshin tantanin halitta su kasance iri ɗaya yayin yin kwafi.

   Idan kana son nemo kwafi a cikin ginshiƙi B, canza nassoshi domin tsarin ya yi kama da haka:

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

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

   Maimakon haka"Only"Da kuma"Kwafi» Kuna iya rubuta tambarin ku, misali, «Ba a samu ba"Da kuma"Found", ko barin kawai"Kwafi' kuma shigar da halin sarari maimakon ƙimar ta biyu. A cikin akwati na ƙarshe, ƙwayoyin da ba a samo kwafin su ba za su kasance fanko, kuma, na yi imani, wannan wakilcin bayanan ya fi dacewa don ƙarin bincike.

2. Yanzu bari mu kwafi dabararmu zuwa duk sel a cikin ginshiƙi C, har zuwa ƙasan layi, wanda ya ƙunshi bayanai a cikin ginshiƙi A. Don yin wannan, matsar da alamar linzamin kwamfuta zuwa kusurwar dama ta ƙasa ta tantanin halitta C1, mai nuni zai ɗauki nau'i na baƙar fata crosshair, kamar yadda aka nuna a hoton da ke ƙasa:Danna ka riƙe maɓallin linzamin kwamfuta na hagu sannan ka ja iyakar firam ɗin ƙasa, yana nuna duk sel inda kake son saka dabarar. Lokacin da aka zaɓi duk sel ɗin da ake buƙata, saki maɓallin linzamin kwamfuta:

   

tip: A cikin manyan teburi, kwafin dabara zai yi sauri idan kun yi amfani da gajerun hanyoyin madannai. Hana tantanin halitta C1 kuma latsa Ctrl + C (don kwafin dabarar zuwa allon allo), sannan danna Ctrl + Shift + Ƙarshe (don zaɓar duk sel marasa sarari a shafi na C) kuma a ƙarshe danna Ctrl + V (don saka dabarar cikin dukkan sel da aka zaɓa).

3. Mai girma, yanzu duk kwafin dabi'u ana yiwa alama "Kwafi":

Zaɓin B: ginshiƙai biyu suna kan zanen gado daban-daban (a cikin littattafan aiki daban-daban)

1. A cikin tantanin halitta na farko na ginshiƙi mara komai a kan takardar aiki Takardar2 (a yanayin mu shi ne shafi B) shigar da dabara mai zuwa:

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

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

   nan Takardar3 shine sunan takardar da shafi na 2 yake, kuma $1: $10000 adireshi ne na tantanin halitta daga na 1 zuwa na ƙarshe a cikin wannan shafi na 2.

2. Kwafi dabarar zuwa duk sel a cikin ginshiƙi B (daidai da zabin A).
3. Muna samun wannan sakamakon:

Ana aiwatar da kwafin da aka samo

Mai girma, mun sami shigarwar a cikin shafi na farko waɗanda kuma suke a cikin shafi na biyu. Yanzu muna bukatar mu yi wani abu da su. Yin tafiya cikin duk kwafin bayanan da ke cikin tebur da hannu ba shi da inganci kuma yana ɗaukar lokaci mai yawa. Akwai hanyoyi masu kyau.

Nuna kwafin layuka a shafi na A

Idan ginshiƙan ku ba su da rubutun kai, to kuna buƙatar ƙara su. Don yin wannan, sanya siginan kwamfuta akan lambar da ke wakiltar layin farko, kuma zai juya zuwa kibiya baƙar fata, kamar yadda aka nuna a cikin adadi na ƙasa:

Danna-dama kuma zaɓi daga menu na mahallin sa (Saka):

Bada sunaye ga ginshiƙan, misali, “sunan"Da kuma"Kwafi?» Sannan bude shafin data (Data) kuma danna Tace (Tace):

Bayan haka danna kan karamar kibiya mai launin toka kusa da “Kwafi?« don buɗe menu na tacewa; Cire duk abubuwan da ke cikin wannan jerin sai dai Kwafi, kuma latsa OK.

Wannan ke nan, yanzu kuna ganin waɗannan abubuwan ginshiƙi ne kawai А, waɗanda aka kwafi a cikin ginshiƙi В. Akwai nau'ikan nau'ikan nau'ikan nau'ikan guda biyu kawai a cikin teburin horar da mu, amma, kamar yadda kuka fahimta, a aikace za a sami ƙari da yawa daga cikinsu.

Don sake nuna duk layuka na shafi А, danna alamar tacewa a cikin ginshiƙi В, wanda yanzu yayi kama da mazurari tare da ƙaramin kibiya, kuma zaɓi Zaɓi duk (Zaɓi duka). Ko kuma kuna iya yin hakan ta hanyar Ribbon ta danna data (Data) > Zaɓi & Tace (Kira & Tace) > Sunny (Clear) kamar yadda aka nuna a hoton da ke ƙasa:

Canja launi ko haskaka kwafi da aka samo

Idan bayanin kula"Kwafi” bai isa ba don dalilan ku kuma kuna son yiwa kwafin sel masu launi daban-daban, launi mai cika ko wata hanyar daban…

A wannan yanayin, tace kwafin kamar yadda aka nuna a sama, zaɓi duk sel da aka tace sannan danna Ctrl + 1don buɗe maganganun Tsarin Sel (tsarin salula). A matsayin misali, bari mu canza launin cikar sel a cikin layuka tare da kwafi zuwa rawaya mai haske. Tabbas, zaku iya canza launi mai cika tare da kayan aiki Cika (Cika Launi) tab Gida (Gida) amma fa'idar akwatin maganganu Tsarin Sel (Tsarin Kwayoyin) a cikin wannan zaku iya saita duk zaɓuɓɓukan tsarawa a lokaci guda.

Yanzu ba shakka ba za ku rasa kowane sel masu kwafi:

Cire kwafin ƙima daga ginshiƙi na farko

Tace tebur domin kawai a nuna sel masu kwafin ƙima, sannan zaɓi waɗannan sel.

Idan ginshiƙan 2 da kuke kwatanta suna kan zanen gado daban-daban, wato, a cikin teburi daban-daban, danna-dama akan kewayon da aka zaɓa kuma zaɓi Share Layi (Cire layi):

latsa OKlokacin da Excel ya tambaye ku don tabbatar da cewa da gaske kuna son share duk layin layin sannan ku share tace. Kamar yadda kuke gani, layuka masu ƙima na musamman sun rage:

Idan ginshiƙai 2 suna kan takarda ɗaya, kusa da juna (masu kusa) ko ba kusa da juna ba (ba kusa ba), to tsarin cire kwafin zai zama mai rikitarwa. Ba za mu iya cire gaba dayan jeren tare da kwafin dabi'u ba, saboda wannan zai cire sel daga shafi na biyu kuma. Don haka don barin abubuwan shigarwa na musamman kawai a cikin ginshiƙi А, yi wannan:

1. Tace tebur don nuna dabi'u masu kwafi kawai kuma zaɓi waɗannan sel. Danna-dama akan su kuma zaɓi daga menu na mahallin Share abun ciki (bayyanannun abun ciki).
2. Tsaftace tace.
3. Zaɓi duk sel a cikin ginshiƙi А, farawa daga tantanin halitta A1 har zuwa kasa mai dauke da bayanai.
4. danna data (Data) kuma danna Tace A zuwa Z (Kasuwa daga A zuwa Z). A cikin akwatin maganganu da ke buɗewa, zaɓi Ci gaba da zaɓi na yanzu (Kira a cikin takamaiman zaɓi) kuma danna maɓallin Black (Rarraba):
5. Share ginshiƙi tare da dabara, ba za ku ƙara buƙatarsa ​​ba, daga yanzu kuna da ƙima na musamman.
6. Shi ke nan, yanzu shafi А ya ƙunshi bayanai na musamman waɗanda ba a cikin ginshiƙi kawai В:

Kamar yadda kake gani, cire kwafi daga ginshiƙai biyu a cikin Excel ta amfani da dabaru ba shi da wahala.