Hanyoyin Kwatanta Table a Microsoft Excel

Kusan sau da yawa, masu amfani da Excel suna fuskantar aikin kwatanta tebur biyu ko jerin abubuwa don gano bambance-bambance ko abubuwan da ba a cikin su ba. Kowane mai amfani yana yin haƙuri da wannan aikin a hanyarsa, amma mafi yawan lokuta a mafi yawan lokaci ana kashewa don warware wannan batun, tunda ba duk hanyoyin fuskantar wannan matsalar ba ne. A lokaci guda, akwai algorithms da aka tabbatar da yawa waɗanda zasu ba ka damar kwatanta jerin abubuwa ko jerin abubuwan tebur a cikin ɗan gajeren lokaci tare da ƙaramin ƙoƙari. Bari mu bincika waɗannan zaɓuɓɓukan.

Duba kuma: Kwatanta takardu guda biyu a cikin MS Word

Hanyoyin kwatantawa

Akwai hanyoyi da yawa da za'a kwatanta tebur a cikin Excel, amma ana iya rarraba su duka zuwa manyan rukuni uku:

Dangane da wannan rarrabuwa, da farko, an zaɓi hanyoyin kwatanta, kazalika da takamaiman ayyuka da algorithms an ƙaddara don aikin. Misali, lokacin kwatanta a cikin littattafai daban-daban, kuna buƙatar buɗe fayilolin Excel guda biyu a lokaci guda.

Bugu da kari, ya kamata a faɗi cewa kwatanta wuraren tebur yana da ma'ana yayin da suke da tsari iri ɗaya.

Hanyar 1: dabara mai sauƙi

Hanya mafi sauki don kwatanta bayanai a cikin tebur biyu shine amfani da tsari mai daidaituwa mai sauƙi. Idan bayanan sun yi daidai, to, yana ba da alamar Gaskiya, kuma idan ba haka ba, to FALSE. Kuna iya kwatanta lambobi da bayanan rubutu. Rashin dacewar wannan hanyar ita ce cewa za'a iya amfani dashi kawai idan an umurce data a cikin tebur ko anyi daidai da juna, ayi aiki tare kuma suna da layi ɗaya. Bari mu ga yadda ake amfani da wannan hanyar a aikace tare da misalin tebur biyu da aka sanya akan takarda ɗaya.

Don haka, muna da tebur biyu masu sauƙi waɗanda ke da jerin ma'aikata da kuma albashin su. Wajibi ne a kwatanta jeri na ma'aikata da kuma gano daidaituwa tsakanin ginshiƙai inda aka sanya sunayen.

1. Don yin wannan, muna buƙatar ƙarin shafi a kan takardar. Muna shigar da alama a can "=". Sannan mun danna abu na farko da kake son kwatantawa a jerin farko. Mun sake sanya alamar "=" daga keyboard. Bayan haka, danna kan sashin farko na shafin da muke kwatantawa a tebur na biyu. Sakamakon nuni ne na nau'in mai zuwa:

   = A2 = D2

   

   Kodayake, hakika, a kowane yanayi, masu daidaitawa zasu bambanta, amma jigon zai kasance ɗaya.

2. Latsa maballin Shigardon samun sakamakon kwatantawa. Kamar yadda kake gani, lokacin kwatanta matakan farko na jerin guda biyun, shirin ya nuna mai nuna alama "GASKIYA", wanda ke nufin daidaita bayanai.



3. Yanzu muna buƙatar aiwatar da irin wannan aiki tare da sauran sel na allunan biyu a cikin sassan da muke kwatantawa. Amma zaka iya kwafa dabarar, wanda zai iya adana lokaci sosai. Wannan mahimmancin yana da mahimmanci musamman idan aka kwatanta jerin lambobi tare da layuka masu yawa.

   Ana yin aikin kwafin cikin sauƙin amfani da alamar cikawa. Mun hau kan kusurwar dama na sel, inda muka sami nuna alama "GASKIYA". A lokaci guda, ya kamata a canza shi zuwa gicciyen baƙar fata. Wannan ita ce alamar cika. Mun danna maɓallin linzamin kwamfuta na hagu kuma ja siginar lamba akan yawan layin a cikin kwatancen teburin.



4. Kamar yadda kake gani, yanzu a cikin ƙarin shafi duk sakamakon kwatancen bayanai a cikin ginshiƙai guda biyu na jerin teburin an nuna su. A cikin yanayinmu, bayanan akan layin guda ɗaya basu dace ba. Lokacin da aka kwatanta su, dabara yana samar da sakamakon KARYA. Ga duk sauran layin, kamar yadda muke gani, tsarin kwatancen ya samar da mai nuna alama "GASKIYA".



5. Bugu da kari, yana yiwuwa yin lissafin yawan bambance-bambancen amfani da tsari na musamman. Don yin wannan, zaɓi ɓangaren takardar inda za'a nuna shi. Saika danna alamar "Saka aikin".



6. A cikin taga Wizards na Aiki a cikin rukunin masu aiki "Ilmin lissafi" zaɓi sunan ZAMU CIGABA. Latsa maballin "Ok".



7. Ana kunna taga aikin aiki. ZAMU CIGABAwanda babban aikin shi shine kirga adadin abubuwan da aka zaba. Amma ana iya amfani da wannan aikin don dalilanmu. Ginin kalma mai sauki ne:

   = TAMBAYA (tsinkaye1; array2; ...)

   A cikin duka, ana iya amfani da adireshin har zuwa 255 amatsayi azaman muhawara. Amma a cikin yanayinmu, zamu yi amfani da tsararru biyu kawai, ƙari, azaman hujja ɗaya.

   Sanya siginan kwamfuta a cikin filin "Array1" sannan ka zavi kan takarda da kwatancen data a yankin farko. Bayan haka, sanya alamar a cikin filin ba daidai ba () ka zaɓa kwatancen kwatancen yanki na biyu. Abu na gaba, kunsa bayanin da ya haifar a cikin kwarjinin gabadaya wanda muka sanya haruffa biyu "-". A cikin yanayinmu, wannan magana ta juya:

   - (A2: A7D2: D7)

   Latsa maballin "Ok".



8. Mai ba da aiki ya ƙididdige ya kuma bayyana sakamakon. Kamar yadda kake gani, a cikin yanayinmu, sakamakon daidai yake da lamba "1", wato, yana nufin cewa an sami kuskure guda a cikin jerin kwatancen. Idan jerin suna daidai da juna, to sakamakon zai zama daidai da lambar "0".

Haka kuma, zaku iya kwatanta bayanai a cikin allunan da suke kan kan zanen gado daban daban. Amma a wannan yanayin, yana da kyau a lasafta layin da ke cikinsu. In ba haka ba, tsarin kwatancen kusan iri ɗaya ne kamar yadda aka bayyana a sama, ban da gaskiyar cewa idan ka shigar da dabara dole ka sauya tsakanin zanen gado. A cikin yanayinmu, magana za ta yi kama da haka:

= B2 = Sheet2! B2

Wannan shine, kamar yadda muke gani, kafin daidaitawar bayanan, waɗanda suke akan wasu zanen gado, banda inda aka nuna sakamakon kwatancin, lambar takardar da alamar alamar mamaki.

Hanyar 2: zaɓi ƙungiyoyin tantanin halitta

Ana iya yin kwatanta ta amfani da kayan aikin zaɓi na tantanin halitta. Hakanan za'a iya amfani dashi don kwatanta lissafin aiki tare da jerin jeri. Kari akan haka, a wannan yanayin, yakamata a sanya jerin abubuwan kusa da juna akan takardar.

1. Mun zaɓi abubuwan da aka kwatanta. Je zuwa shafin "Gida". Bayan haka, danna kan gunkin Nemo da Haskakawacce take akan kintinkiri a cikin akwatin kayan aiki "Gyara". Jerin yana buɗewa wanda zai zaɓi wuri "Zabi rukuni na sel ...".

   

   Bugu da kari, zamu iya zuwa taga da ake so don zabi gungun sel a wata hanyar. Wannan zaɓin zai kasance da amfani musamman ga waɗancan masu amfani waɗanda suka shigar da sigar shirin tun farkon Excel 2007, tunda hanyar ta maɓallin Nemo da Haskaka waɗannan aikace-aikacen ba su da goyan baya. Mun zabi hanyoyin da muke son kwatantawa, kuma danna madannin F5.

2. An kunna ƙaramin juyawa na taga. Latsa maballin "Zaɓi ..." a cikin ƙananan kusurwar hagu.



3. Bayan haka, kowane ɗayan zaɓi biyu ɗin da kuka zaɓa, an ƙaddamar da taga don zaɓar gungun sel. Saita canji zuwa wuri "Zaɓi layi da layi". Latsa maballin "Ok".



4. Kamar yadda kake gani, bayan wannan za a nuna mahimmancin daidaita layin tare da bambancin daban. Bugu da kari, kamar yadda za'a iya yin hukunci daga abinda ke cikin masarar dabara, shirin zai sanya ɗayan ɗayan sel wanda ke cikin layin da ba a daidaita ba.

Hanyar 3: Tsarin sharadi

Zaku iya kwatanta amfani da hanyar tsara yanayin. Kamar yadda yake a hanyar da ta gabata, wuraren da aka kwatanta ya kamata su kasance akan takardar aikin Excel guda ɗaya kuma a daidaita tare da juna.

1. Da farko dai, za mu zabi yankin teburin da za mu yi la’akari da babba, kuma a cikinsa muke neman bambance-bambance. Bari muyi na ƙarshe a tebur na biyu. Sabili da haka, mun zaɓi jerin ma'aikatan da ke ciki. Ta matsawa zuwa shafin "Gida"danna maballin Tsarin Yanayiwanda yake akan tef a cikin toshe Salo. Daga jerin zaɓuka, je zuwa Gudanar da Dokoki.



2. Ana kunna taga mai gudanar da mulki. Danna maballin a ciki Createirƙiri mulki.



3. A cikin taga da ke farawa, zaɓi matsayin Yi amfani da Dabarar. A fagen "Tsarin Kwayoyin" Rubuta wata dabara wacce ke dauke da adireshin sel farko na jerin layin da aka kwatanta, ta hanyar “ba daidai bane” () Wannan magana ce kawai zata fuskance wannan lokacin. "=". Bugu da kari, dole ne ayi amfani da cikakkiyar adreshin adireshin dukkan shiryayyun shafin a cikin wannan tsari. Don yin wannan, zaɓi dabara tare da siginan kwamfuta kuma danna maɓallin sau uku F4. Kamar yadda kake gani, alamar dala ta bayyana a kusa da duk adireshin shafi, wanda ke nufin juyawa hanyoyin shiga cikin cikakkar gaskiya. Don halinmu na musamman, tsari yana amfani da tsari mai zuwa:

   = $ A2 $ D2

   Mun rubuta wannan magana a cikin filin da ke sama. Bayan haka, danna maɓallin "Tsarin ...".



4. An kunna taga Tsarin Cell. Je zuwa shafin "Cika". Anan cikin jerin launuka mun dakatar da zabi akan launi wanda muke so mu canza waɗancan abubuwan inda bayanan bazai dace ba. Latsa maballin "Ok".



5. Komawa taga don ƙirƙirar tsarin tsarawa, danna maɓallin "Ok".



6. Bayan motsi ta atomatik zuwa taga Manajan Dokoki danna maballin "Ok" kuma a ciki.



7. Yanzu a cikin tebur na biyu, abubuwan da suke da bayanai waɗanda basu dace da ƙimar daidai yanki na tebur na farko za a fifita su a cikin launi da aka zaɓa ba.

Akwai kuma wata hanyar da za a yi amfani da tsari na tsari a aikin. Kamar zaɓuɓɓukan da suka gabata, yana buƙatar wurin duka ɓangarorin biyu idan aka kwatanta a kan takarda ɗaya, amma sabanin hanyoyin da aka bayyana a baya, yanayin don daidaitawa ko rarrabe bayanai ba zai zama wajibi ba, wanda ke bambanta wannan zaɓi daga waɗanda aka bayyana a baya.

1. Mun zaɓi wuraren da za a kwatanta.



2. Je zuwa shafin da ake kira "Gida". Latsa maballin Tsarin Yanayi. A Jerin da aka kunna, zabi matsayin Dokokin Zabi na Cell. A menu na gaba zamuyi zabi matsayi Mabudin dabi'u.



3. Tagan don saita zaɓi na ɗimbin lambobi yana farawa. Idan kayi komai yadda yakamata, to a wannan taga zai rage kawai danna maballin "Ok". Kodayake, idan ana so, a filin mai dacewa na wannan taga, zaku iya zaɓar launi daban-daban mai haske.



4. Bayan mun aiwatar da aikin da aka ƙaddara, duk abubuwan da ke maimaitawa za a fifita su a cikin launi da aka zaɓa. Wadancan abubuwan da basu dace ba zasu ci gaba da zanen su a asalin launi (fari da tsohuwa). Ta haka ne, zaka iya gani nan take menene bambanci tsakanin tsararraki.

Idan ana so, zaku iya, akasin haka, canza launin abubuwan da ba a daidaita ba, da waɗannan alamomin da suka dace, barin cika tare da launi iri ɗaya. Algorithm na ayyuka kusan iri ɗaya ne, amma a cikin saiti taga don nuna ƙimomin ɗabbai a farkon filin maimakon Kwafa ya kamata zaɓi "Babu banbanci". Bayan haka, danna maɓallin "Ok".

Saboda haka, ainihin waɗannan alamomin da basu dace ba za a fadada su.

Darasi: Tsarin Kayan Yanayi a Excel

Hanyar 4: dabara mai cakuda

Hakanan zaka iya kwatanta bayanai ta amfani da hadadden tsari dangane da aikin TAMBAYA. Amfani da wannan kayan aiki, zaku iya yin lissafin nawa kowane kashi daga zaɓin shafi na tebur na biyu ana maimaita ta a farko.

Mai aiki TAMBAYA yana nufin ƙungiyar ƙididdigar ayyukan ayyuka. Aikinta shine kirga yawan sel waɗanda dabi'un su suka cika yanayin da aka bayar. Ma’anonin wannan mai aiki kamar haka:

= COUNTIF (kewa; rarrabuwa)

Hujja "Range" wakiltar adireshin tsararrun da ake lissafta ƙididdigar abubuwa masu dacewa.

Hujja "Sharhin" yana sanya yanayin daidaitawa. A cikin halinmu, zai zama haɗakar takamaiman ƙwayoyin sel a farkon tebur.

1. Mun zabi kashi na farko na ƙarin shafin wanda za'a lissafta adadin ashana. Bayan haka, danna kan gunkin "Saka aikin".



2. Farawa Wizards na Aiki. Je zuwa rukuni "Na lissafi". Nemo suna a cikin jerin "COUNTIF". Bayan zabar shi, danna kan maɓallin "Ok".



3. Shafin Hujja Mai Furewa Mai Aiki TAMBAYA. Kamar yadda kake gani, sunayen filayen da ke wannan taga sun dace da sunayen muhawara.

   Sanya siginan kwamfuta a cikin filin "Range". Bayan haka, riƙe maɓallin linzamin kwamfuta na hagu, zaɓi duk ƙimar layin tare da sunayen teburin na biyu. Kamar yadda kake gani, masu daidaitawa nan da nan suka fadi zuwa filin da aka ƙayyade. Amma don dalilan mu, ya kamata a sanya wannan adireshin cikakke. Don yin wannan, zaɓi waɗannan masu gudanarwa a fagen kuma latsa madannin F4.

   Kamar yadda kake gani, hanyar haɗin yanar gizon ta ɗauki tsari cikakke, wanda ke nuna kasancewar alamun alamun dala.

   Daga nan sai a tafi filin "Sharhin"ta saita siginan kwamfuta a wurin. Mun danna farkon kashi tare da sunayen ƙarshe a zangon farko na tebur. A wannan yanayin, bar haɗin dangi. Bayan an nuna shi a filin, zaku iya danna maballin "Ok".



4. Sakamakon ya nuna a cikin takardar takardar. Ya yi daidai da lamba "1". Wannan yana nufin cewa a cikin jerin sunayen tebur na biyu, sunan ƙarshe "Grinev V.P.", wanda shine na farko a jerin jeri na farko, yana faruwa sau ɗaya.



5. Yanzu muna buƙatar ƙirƙirar magana mai kama don duk sauran abubuwan farko na tebur na farko. Don yin wannan, za mu kwafa ta amfani da alamar cikawa, kamar yadda muka yi a baya. Sanya siginan kwamfuta a cikin ƙananan dama na takardar takardar da ke ɗauke da aikin TAMBAYA, kuma bayan sauya shi zuwa alamar mai cikawa, riƙe maɓallin linzamin kwamfuta na hagu kuma ja siginar ƙasa.



6. Kamar yadda kake gani, shirin ya kirkiri daidaituwa ta hanyar kwatanta kowace sel na farko tebur tare da bayanan da ke cikin zangon tebur na biyu. A cikin yanayi hudu, sakamakon ya fito "1", kuma a yanayi biyu - "0". Wato, shirin ba zai iya samu a cikin tebur na biyu dabi'u biyu da ke cikin farkon tebur ba.

Tabbas, ana iya amfani da wannan magana, don kwatanta alamomin tabular, ta hanyar da ake amfani da ita, amma akwai damar inganta shi.

Mun tabbata cewa waɗannan dabi'un waɗanda suke cikin tebur ta biyu, amma ba sa cikin na farko, an nuna su cikin jerin daban.

1. Da farko dai, zamu dan sake amfani da tsari TAMBAYA, wato, muna sanya shi ɗaya daga cikin mahawara mai aiki IF. Don yin wannan, zaɓi tantanin farko wanda acikin sa yake aiki TAMBAYA. A cikin lamuran dabaru kafin ta, kara magana IF ba tare da ambato da kuma buɗe sashin ƙarfe. Na gaba, don sauƙaƙa mana aiki, zaɓi ƙimar a cikin masarar dabara IF kuma danna kan gunkin "Saka aikin".



2. Farashin muhawara na aiki zai bude IF. Kamar yadda kake gani, filin farko na taga ya riga ya cika da darajar mai aiki TAMBAYA. Amma muna buƙatar ƙara wani abu a wannan filin. Mun sanya siginan sigari a wurin kuma ƙara zuwa bayanin da ke gudana "=0" ba tare da ambato ba.

   Bayan haka, je filin "Ma'ana idan gaskiya ne". Anan zamuyi amfani da wani aikin da aka sanya - LADA. Shigar da kalmar LADA ba tare da kwatancen ba, sannan ka buɗe bra ɗin kuma ƙayyade abubuwan daidaitawa na sel farko tare da sunan ƙarshe a teburin na biyu, sannan sai a rufe maƙalar. Musamman, a cikin yanayinmu, a cikin filin "Ma'ana idan gaskiya ne" Bayani mai zuwa ya juya baya:

   LINE (D2)

   Yanzu mai aiki LADA zai ba da rahoton ayyuka IF yawan layin da takamaiman sunan mahaifa yake, kuma idan har aka gamsar da yanayin da aka ƙayyade a filin farko, aikin zai kasance IF zai nuna wannan lambar a cikin tantanin halitta. Latsa maballin "Ok".



3. Kamar yadda kake gani, sakamakon farko yana bayyana azaman KARYA. Wannan yana nufin ƙimar bata gamsar da yanayin mai aiki ba. IF. Wannan shine, sunan uba na farko yana nan a cikin duka jerin.



4. Yin amfani da alamar cikawa, muna kwafin bayanin mai aiki a hanyar da ta saba IF a kan gaba daya shafi. Kamar yadda kake gani, don matsayi biyu waɗanda suke a cikin tebur na biyu, amma ba a farkon ba, dabarar tana ba da lambobin layi.



5. Mun tashi daga yankin tebur zuwa dama kuma muna cike shafin da lambobi don tsari, fara daga 1. Yawan lambobi dole ne su dace da adadin layuka a cikin tebur na biyu da za a kwatanta. Don saurin aiwatar da lambobi, Hakanan zaka iya amfani da alamar cikawa.



6. Bayan haka, zaɓi sel na farko zuwa dama na shafi tare da lambobi kuma danna kan gunkin "Saka aikin".



7. Yana buɗewa Mayan fasalin. Je zuwa rukuni "Na lissafi" kuma zabi zabi sunan "KYAUTA". Latsa maballin "Ok".



8. Aiki KYAUTATAwanda aka bude taga gardamar, wanda aka shirya don nuna mafi karancin darajar da aka kayyade a cikin asusun.

   A fagen Shirya ambaci daidaitawa na kewayon ƙarin shafin "Lambar Wasanni"wanda muka canza a baya ta amfani da aikin IF. Mun sanya dukkan hanyoyin haɗin yanar gizo.

   A fagen "K" yana nuna asusun da ƙimar mafi ƙarancin buƙatar nuna. Anan mun nuna daidaitawar sel na farko tare da lamba, wanda muka kara kwanan nan. Mun bar adireshin dangi. Latsa maballin "Ok".



9. Mai aiki yana nuna sakamakon - lamba 3. Yana da mafi ƙanƙanta daga lambar lambobi marasa daidaitawa na jerin teburin. Yin amfani da alamar cikawa, kwafa dabarar har zuwa ƙasa sosai.



10. Yanzu, sanin lambobin layi na abubuwan da ke rikitarwa, zamu iya saka kwayar su darajarsu ta amfani da aikin INDEX. Zaɓi kashi na farko na takardar da ke ɗauke da tsari KYAUTATA. Bayan haka, je zuwa layin tsari da gaban sunan "KYAUTA" kara sunan INDEX ba tare da ambato ba, kai tsaye buɗe suturar kuma saka Semicolon (;) Sannan zaɓi sunan a layin dabara INDEX kuma danna kan gunkin "Saka aikin".



11. Bayan wannan, ƙaramin taga yana buɗewa wanda kuke buƙatar sanin ƙididdigar tunani yakamata ya sami aiki INDEX ko tsara don yin aiki tare da tsari. Muna buƙatar zaɓi na biyu. An sanya shi ta hanyar tsohuwa, don haka a wannan taga kawai danna maɓallin "Ok".



12. Farashin muhawara na aiki yana farawa INDEX. Wannan ma'aikacin yana nufin fitar da ƙimar da ke cikin takamammen tsararraki a cikin takamammen murɗin.

    Kamar yadda kake gani, filin Lambar layi riga ya cika da dabi'u na aiki KYAUTATA. Daga darajar da ta riga ta wanzu a can, ya kamata a rage bambanci tsakanin lambar lambar Excel da lambar ta ciki na yankin teburin. Kamar yadda kake gani, muna da kawai kan matakin ƙimar teburin. Wannan yana nuna cewa bambanci shine layi ɗaya. Sabili da haka, muna ƙara a cikin filin Lambar layi darajar "-1" ba tare da ambato ba.

    A fagen Shirya saka adireshin kewayon dabi'u na tebur na biyu. A lokaci guda, muna yin duk masu daidaitawa cikakke, wato, mun sanya a gaban su alamar dollar ta hanyar da muka bayyana a baya.

    Latsa maballin "Ok".



13. Bayan nuna sakamakon akan allo, muna mika aikin ta amfani da alamar cikawa zuwa kasan shafin. Kamar yadda kake gani, sunaye biyu waɗanda ke cikin tebur na biyu, amma ba su cikin na farko, ana nuna su da kewayon daban.

Hanyar 5: gwada kwatancen a cikin littattafai daban-daban

Lokacin kwatanta jeri a cikin littattafai daban-daban, zaku iya amfani da hanyoyin da ke sama, ban da waɗancan zaɓuɓɓuka inda zaku so sanya bangarorin teburin akan takarda ɗaya. Babban yanayin tsarin kwatancen a wannan yanayin shine buɗe windows of files guda biyu lokaci guda. Don juzu'in na Excel 2013 da kuma daga baya, har ma da nau'in kafin Excel 2007, babu matsaloli tare da wannan yanayin. Amma a cikin Excel 2007 da Excel 2010, don buɗe duka windows a lokaci guda, ana buƙatar ƙarin jan aikin. Yadda aka yi wannan an bayyana shi a cikin darasi dabam.

Darasi: Yadda ake bude Excel a cikin windows daban-daban

Kamar yadda kake gani, akwai hanyoyi da yawa da za a iya kwatanta tebur a tsakanin su. Wanne zaɓi don amfani da shi ya dogara da ainihin bayanan yanki wanda yake kusa da juna (akan takarda ɗaya, a cikin litattafai daban-daban, akan zanen gado daban-daban), da kuma yadda mai amfani yake so wannan kwatancen da aka nuna akan allon.