Yadda ake kwatanta lissafin biyu a cikin Excel

Excel ingantaccen tsarin sarrafa bayanai ne. Kuma daya daga cikin hanyoyin nazarin bayanai shine kwatanta lissafin guda biyu. Idan kun kwatanta lissafin guda biyu daidai a cikin Excel, tsara wannan tsari zai zama da sauƙi. Ya isa kawai mu bi wasu batutuwan da za a tattauna a yau. Aiwatar da wannan hanyar a aikace ya dogara kacokan akan bukatun mutum ko kungiya a wani lokaci. Saboda haka, ya kamata a yi la'akari da wasu lokuta masu yiwuwa.

Kwatanta lissafin biyu a cikin Excel

Tabbas, zaku iya kwatanta lissafin biyu da hannu. Amma zai ɗauki lokaci mai tsawo. Excel yana da nasa kayan aikin fasaha wanda zai baka damar kwatanta bayanai ba kawai cikin sauri ba, har ma don samun bayanan da ba su da sauƙin samun da idanunka. A ce muna da ginshiƙai biyu tare da daidaitawa A da B. Wasu dabi'u ana maimaita su a cikin su.

Samar da matsala

Don haka muna buƙatar kwatanta waɗannan ginshiƙai. Hanyar kwatanta takardu guda biyu kamar haka:

1. Idan sel na musamman na kowane ɗayan waɗannan jeriyoyin sun kasance iri ɗaya, kuma jimilar adadin ƙwayoyin halitta iri ɗaya ne, kuma sel iri ɗaya ne, to ana iya ɗaukar waɗannan lissafin iri ɗaya. Tsarin da aka tara kimar da ke cikin wannan jeri ba shi da mahimmanci.
2. Za mu iya magana game da m daidaituwa na lists idan na musamman dabi'u da kansu iri daya ne, amma yawan maimaita ya bambanta. Don haka, irin waɗannan lissafin na iya ƙunsar abubuwa daban-daban.
3. Gaskiyar cewa lissafin biyu ba su daidaita ba ana nuna su ta wani saiti na musamman na musamman.

Duk waɗannan sharuɗɗa guda uku a lokaci guda su ne yanayin matsalarmu.

Maganin matsalar

Bari mu samar da jeri biyu masu ƙarfi don sauƙaƙa kwatanta lissafin. Kowannen su zai yi daidai da kowane lissafin.

Don kwatanta lissafin guda biyu, yi abubuwa masu zuwa:

1. A cikin wani ginshiƙi daban, mun ƙirƙiri jerin ƙima na musamman waɗanda ke keɓance ga lissafin biyu. Don wannan muna amfani da dabarar: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Daftarin da kanta dole ne a rubuta shi azaman dabarar tsararru.
2. Bari mu ƙididdige sau nawa kowace ƙima ta musamman ke faruwa a cikin tsararrun bayanai. Ga hanyoyin yin haka: = COUNTIF (List1, D5) da = COUNTI (List2, D5).
3. Idan duka adadin maimaitawa da adadin ƙima na musamman iri ɗaya ne a cikin duk jerin da aka haɗa a cikin waɗannan jeri, to aikin ya dawo da ƙimar 0. Wannan yana nuna cewa wasan shine XNUMX%. A wannan yanayin, kanun labaran waɗannan jerin za su sami bangon kore.
4. Idan duk keɓaɓɓen abun ciki yana cikin jeridu biyu, sannan a mayar da su ta hanyar ƙira =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) darajar za ta zama sifili. Idan E1 bai ƙunshi sifili ba, amma irin wannan darajar yana ƙunshe a cikin sel E2 da F2, to, a cikin wannan yanayin za a gane jeri a matsayin daidaitawa, amma kawai a wani yanki. A wannan yanayin, taken lissafin da ya dace zai zama orange.
5. Kuma idan ɗaya daga cikin dabarun da aka kwatanta a sama ya dawo da ƙimar da ba ta sifili ba, jerin za su kasance gaba ɗaya ba daidai ba.

Wannan ita ce amsar tambayar yadda ake nazarin ginshiƙai don matches ta amfani da dabaru. Kamar yadda kake gani, tare da amfani da ayyuka, zaka iya aiwatar da kusan kowane aiki wanda, kallon farko, ba shi da alaka da ilimin lissafi.

Misali Gwajin

A cikin sigar mu ta tebur, akwai nau'ikan jeri uku na kowane nau'in da aka bayyana a sama. Yana da partially kuma gaba ɗaya daidaitawa, da kuma rashin daidaituwa.

Don kwatanta bayanai, muna amfani da kewayon A5:B19, wanda a cikinsa muke saka waɗannan nau'ikan lissafin. Game da abin da zai zama sakamakon kwatancen, za mu fahimta ta launi na jerin asali. Idan sun bambanta gaba daya, to, zai zama ja baya. Idan ɓangaren bayanan ɗaya ne, to rawaya. A cikin yanayin cikakken ainihi, taken da ya dace zai zama kore. Yadda za a yi launi dangane da abin da sakamakon yake? Wannan yana buƙatar tsara yanayin.

Nemo bambance-bambance a lissafin biyu ta hanyoyi biyu

Bari mu bayyana ƙarin hanyoyi biyu don gano bambance-bambance, dangane da ko lissafin suna aiki tare ko a'a.

Zabin 1. Lissafi masu aiki tare

Wannan zaɓi ne mai sauƙi. A ce muna da irin waɗannan jerin sunayen.

Don ƙayyade sau nawa ƙimar ba ta haɗu ba, zaku iya amfani da dabarar: = SUMPRODUCT (— (A2: A20<>B2:B20)). Idan muka sami 0 a sakamakon haka, wannan yana nufin cewa lissafin biyu iri ɗaya ne.

Zabin 2: Listocin da aka Shuffled

Idan lissafin ba ɗaya ba ne a cikin tsari na abubuwan da suka ƙunsa, kuna buƙatar amfani da fasali kamar tsara yanayin yanayi da canza launi kwafi. Ko amfani da aikin COUNTIF, ta yin amfani da wanda muke ƙayyade sau nawa wani abu daga lissafin ɗaya ya faru a cikin na biyu.

Yadda ake kwatanta ginshiƙai 2 a jere

Idan muka kwatanta ginshiƙai biyu, sau da yawa muna buƙatar kwatanta bayanan da ke cikin layuka daban-daban. Don yin wannan, mai aiki zai taimake mu IF. Bari mu dubi yadda yake aiki a aikace. Don yin wannan, muna gabatar da yanayi da yawa misalai.

Misali. Yadda ake kwatanta ginshiƙai 2 don matches da bambance-bambance a jere ɗaya

Don bincika ko ƙimar da ke cikin jeri ɗaya amma ginshiƙai daban-daban iri ɗaya ne, muna rubuta aikin IF. Ana shigar da dabarar a cikin kowane jere da aka sanya a cikin ginshiƙi na taimako inda za a nuna sakamakon sarrafa bayanai. Amma ba lallai ba ne a rubuta shi a kowane jere, kawai kwafa shi cikin ragowar sel na wannan ginshiƙi ko amfani da alamar autocomplete.

Ya kamata mu rubuta irin wannan dabara don fahimtar ko ƙimar da ke cikin ginshiƙan biyu ɗaya ne ko a'a: =IF(A2=B2, "Match", ""). Ma'anar wannan aikin yana da sauƙi: yana kwatanta dabi'u a cikin sel A2 da B2, kuma idan sun kasance iri ɗaya, yana nuna darajar "Coincide". Idan bayanan sun bambanta, ba zai dawo da wani ƙima ba. Hakanan zaka iya bincika sel don ganin ko akwai wasa a tsakanin su. A wannan yanayin, dabarar da aka yi amfani da ita ita ce: = IF (A2 <> B2, "Kada ku dace", ""). Ka'idar iri ɗaya ce, da farko ana aiwatar da rajistan. Idan ya juya cewa sel sun hadu da ma'auni, to ana nuna darajar "Ba ta dace ba".

Hakanan yana yiwuwa a yi amfani da dabara mai zuwa a cikin filin dabara don nuna duka "Match" idan ƙimar ɗaya ce, da kuma "Kada ku dace" idan sun bambanta: =IF (A2=B2; "Match", "Kada ku dace"). Hakanan zaka iya amfani da afaretan rashin daidaituwa maimakon ma'aikacin daidaito. Sai kawai tsarin ƙimar da za a nuna a cikin wannan yanayin zai ɗan bambanta: = IF (A2 <> B2, "Kada ku dace", "Kada ku daidaita"). Bayan amfani da sigar farko ta dabara, sakamakon zai kasance kamar haka.

Wannan bambance-bambancen dabara ba shi da hankali. Saboda haka, idan dabi'u a cikin wani shafi ya bambanta da wasu kawai a cikin cewa an rubuta su a cikin manyan haruffa, to shirin ba zai lura da wannan bambanci ba. Don yin kwatancen yanayin-m, kuna buƙatar amfani da aikin a cikin ma'auni SAURARA. Sauran gardama ba a canza su ba: =IF (daidai (A2, B2), "Match", "Na musamman").

Yadda ake kwatanta ginshiƙai da yawa don matches a jere ɗaya

Yana yiwuwa a yi la'akari da dabi'u a cikin lissafin bisa ga dukan tsarin ma'auni:

1. Nemo waɗancan layuka waɗanda suke da ƙima iri ɗaya a ko'ina.
2. Nemo waɗancan layuka inda akwai matches a cikin jeri biyu kawai.

Bari mu kalli ƴan misalan yadda ake ci gaba a kowane ɗayan waɗannan al'amura.

Misali. Yadda ake nemo matches a jere a cikin ginshiƙai da yawa na tebur

A ce muna da jerin ginshiƙai waɗanda ke ɗauke da bayanan da muke buƙata. Muna fuskantar aikin tantance waɗancan layuka waɗanda ƙimar iri ɗaya ce. Don yin wannan, kuna buƙatar amfani da dabara mai zuwa: =IF (DA (A2 = B2, A2 = C2), "match", "").

Idan akwai ginshiƙai da yawa a cikin tebur, to kawai kuna buƙatar amfani da shi tare da aikin IF sadarwarka COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"match";""). Lambar da aka yi amfani da ita a cikin wannan dabarar tana nuna adadin ginshiƙai don dubawa. Idan ya bambanta, to kuna buƙatar rubuta daidai gwargwadon yanayin ku.

Misali. Yadda ake nemo matches a jere guda a kowane ginshiƙai 2 na tebur

Bari mu ce muna buƙatar bincika idan ƙimar a jere ɗaya ta dace a cikin ginshiƙai biyu daga waɗanda ke cikin tebur. Don yin wannan, kuna buƙatar amfani da aikin azaman sharadi OR, inda a madadin haka rubuta daidaiton kowane ginshiƙan zuwa ɗayan. Ga misali.

Muna amfani da wannan dabara: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Za a iya samun yanayi lokacin da akwai ginshiƙai da yawa a cikin tebur. A wannan yanayin, dabarar za ta kasance mai girma, kuma yana iya ɗaukar lokaci mai yawa don zaɓar duk haɗin da ake buƙata. Don magance wannan matsala, kuna buƙatar amfani da aikin COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0;

Mun ga cewa gaba ɗaya muna da ayyuka biyu COUNTIF. Tare da na farko, zamu ƙayyade adadin ginshiƙai suna kama da A2, kuma tare da na biyu, muna bincika adadin kamanni tare da ƙimar B2. Idan, sakamakon ƙididdigewa ta wannan dabara, mun sami darajar sifili, wannan yana nuna cewa duk layuka a cikin wannan ginshiƙi na musamman ne, idan ƙari, akwai kamanceceniya. Sabili da haka, idan sakamakon ƙididdigewa ta hanyar ƙididdiga guda biyu da ƙara sakamakon ƙarshe mun sami darajar sifili, to, an dawo da ƙimar rubutu "Kirtani na musamman", idan wannan lambar ta fi girma, an rubuta cewa wannan kirtani ba ta musamman ba ce.

Yadda ake kwatanta ginshiƙai 2 a cikin Excel don matches

Yanzu bari mu dauki misali. Bari mu ce muna da tebur mai ginshiƙai biyu. Kuna buƙatar bincika idan sun dace. Don yin wannan, kuna buƙatar amfani da dabarar, inda za a yi amfani da aikin kuma IF, da mai aiki COUNTIF: =IF(COUNTIF($B:$B:$A5)=0, "Babu matches a shafi na B", "Akwai matches a shafi na B")

Ba a buƙatar ƙarin mataki. Bayan ƙididdige sakamakon ta wannan dabarar, muna samun idan darajar hujja ta uku na aikin IF matches. Idan babu, to abin da ke cikin hujja ta biyu.

Yadda ake kwatanta ginshiƙai 2 a cikin Excel don matches da haskaka da launi

Don sauƙaƙe don gano ginshiƙan da suka dace da gani, zaku iya haskaka su da launi. Don yin wannan, kuna buƙatar amfani da aikin "Tsarin Yanayi". Mu gani a aikace.

Nemo da haskaka matches ta launi a cikin ginshiƙai da yawa

Don tantance matches da kuma haskaka su, dole ne ka fara zaɓar kewayon bayanan da za a gudanar da rajistan, sannan ka buɗe abin da ake kira "Conditional Formatting" a shafin "Gida". A can, zaɓi "Duplicate Values" azaman ƙa'idar zaɓin tantanin halitta.

Bayan haka, wani sabon akwatin maganganu zai bayyana, wanda a cikin jerin fafutuka na hagu za mu sami zabin "Maimaitawa", kuma a cikin jerin dama za mu zaɓi launin da za a yi amfani da shi don zaɓin. Bayan mun danna maballin “Ok”, za a zabi bangon duk sel masu kamanceceniya. Sannan kawai kwatanta ginshiƙan da ido.

Nemo da haskaka layukan da suka dace

Dabarar bincika idan igiyoyin daidaitawa sun ɗan bambanta. Da farko, muna buƙatar ƙirƙirar ƙarin shafi, kuma a can za mu yi amfani da ƙimar haɗin gwiwa ta amfani da & afareta. Don yin wannan, kuna buƙatar rubuta tsari na tsari: =A2&B2&C2&D2.

Mun zaɓi ginshiƙin da aka ƙirƙira kuma ya ƙunshi ƙimar haɗin gwiwa. Na gaba, muna yin jerin ayyuka iri ɗaya waɗanda aka kwatanta a sama don ginshiƙan. Za'a haskaka layukan kwafi a cikin launi da kuka saka.

Mun ga cewa babu wani abu mai wahala wajen neman maimaitawa. Excel ya ƙunshi duk kayan aikin da ake buƙata don wannan. Yana da mahimmanci a yi aiki kawai kafin sanya duk wannan ilimin a aikace.