Sut i gymharu dwy restr yn Excel

Mae Excel yn rhaglen brosesu data effeithlon. Ac un o'r dulliau dadansoddi gwybodaeth yw cymharu dwy restr. Os cymharwch ddwy restr yn Excel yn gywir, bydd trefnu'r broses hon yn hawdd iawn. Mae’n ddigon dilyn rhai o’r pwyntiau a fydd yn cael eu trafod heddiw. Mae gweithrediad ymarferol y dull hwn yn dibynnu'n llwyr ar anghenion yr unigolyn neu'r sefydliad ar adeg benodol. Felly, dylid ystyried nifer o achosion posibl.

Cymharu dwy restr yn Excel

Wrth gwrs, gallwch chi gymharu dwy restr â llaw. Ond bydd yn cymryd amser hir. Mae gan Excel ei becyn cymorth deallus ei hun a fydd yn caniatáu ichi gymharu data nid yn unig yn gyflym, ond hefyd i gael gwybodaeth nad yw mor hawdd ei chael â'ch llygaid. Tybiwch fod gennym ddwy golofn gyda chyfesurynnau A a B. Mae rhai gwerthoedd yn cael eu hailadrodd ynddynt.

Ffurfio'r broblem

Felly mae angen i ni gymharu'r colofnau hyn. Mae'r drefn ar gyfer cymharu dwy ddogfen fel a ganlyn:

1. Os yw celloedd unigryw pob un o'r rhestrau hyn yr un peth, a bod cyfanswm y celloedd unigryw yr un peth, a'r celloedd yr un peth, yna gellir ystyried y rhestrau hyn yr un peth. Nid yw'r drefn y mae'r gwerthoedd yn y rhestr hon yn cael eu pentyrru yn gymaint o bwys.
2. Gallwn siarad am gyd-ddigwyddiad rhannol o restrau os yw'r gwerthoedd unigryw eu hunain yr un peth, ond mae nifer yr ailadroddiadau yn wahanol. Felly, gall rhestrau o'r fath gynnwys nifer wahanol o elfennau.
3. Mae'r ffaith nad yw'r ddwy restr yn cyfateb yn cael ei nodi gan set wahanol o werthoedd unigryw.

Mae'r tri chyflwr hyn ar yr un pryd yn amodau ein problem.

Datrysiad y broblem

Gadewch i ni gynhyrchu dwy ystod ddeinamig i'w gwneud hi'n haws cymharu rhestrau. Bydd pob un ohonynt yn cyfateb i bob un o'r rhestrau.

I gymharu dwy restr, gwnewch y canlynol:

1. Mewn colofn ar wahân, rydym yn creu rhestr o werthoedd unigryw sy'n benodol i'r ddwy restr. Ar gyfer hyn rydym yn defnyddio'r fformiwla: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Rhaid ysgrifennu'r fformiwla ei hun fel fformiwla arae.
2. Gadewch i ni benderfynu faint o weithiau mae pob gwerth unigryw yn digwydd yn yr arae ddata. Dyma'r fformiwlâu ar gyfer gwneud hyn: =COUNTIF(Rhestr1,D5) a =COUNTI(Rhestr2,D5).
3. Os yw nifer yr ailadroddiadau a nifer y gwerthoedd unigryw yr un peth ym mhob rhestr sydd wedi'u cynnwys yn yr ystodau hyn, yna mae'r swyddogaeth yn dychwelyd y gwerth 0. Mae hyn yn nodi mai XNUMX% yw'r cyfatebol. Yn yr achos hwn, bydd penawdau'r rhestrau hyn yn cael cefndir gwyrdd.
4. Os yw'r holl gynnwys unigryw yn y ddwy restr, yna dychwelir gan fformiwlâu =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) bydd y gwerth yn sero. Os nad yw E1 yn cynnwys sero, ond mae gwerth o'r fath wedi'i gynnwys yng nghelloedd E2 a F2, yna yn yr achos hwn bydd yr ystodau'n cael eu cydnabod fel rhai cyfatebol, ond dim ond yn rhannol. Yn yr achos hwn, bydd penawdau'r rhestrau cyfatebol yn troi'n oren.
5. Ac os bydd un o'r fformiwlâu a ddisgrifir uchod yn dychwelyd gwerth nad yw'n sero, bydd y rhestrau'n gwbl anghydnaws.

Dyma'r ateb i'r cwestiwn o sut i ddadansoddi colofnau ar gyfer cyfatebiadau gan ddefnyddio fformiwlâu. Fel y gwelwch, gyda'r defnydd o swyddogaethau, gallwch chi weithredu bron unrhyw dasg nad yw, ar yr olwg gyntaf, yn gysylltiedig â mathemateg.

Profi Enghreifftiol

Yn ein fersiwn ni o'r tabl, mae tri math o restrau o bob math a ddisgrifir uchod. Mae wedi cyfateb yn rhannol ac yn gyfan gwbl, yn ogystal â heb fod yn cyfateb.

I gymharu data, rydym yn defnyddio'r ystod A5:B19, lle rydym bob yn ail yn mewnosod y parau hyn o restrau. Ynglŷn â beth fydd canlyniad y gymhariaeth, byddwn yn deall wrth liw'r rhestrau gwreiddiol. Os ydynt yn hollol wahanol, yna bydd yn gefndir coch. Os yw rhan o'r data yr un peth, yna melyn. Yn achos hunaniaeth gyflawn, bydd y penawdau cyfatebol yn wyrdd. Sut i wneud lliw yn dibynnu ar beth yw'r canlyniad? Mae hyn yn gofyn am fformatio amodol.

Dod o hyd i wahaniaethau mewn dwy restr mewn dwy ffordd

Gadewch i ni ddisgrifio dau ddull arall ar gyfer dod o hyd i wahaniaethau, yn dibynnu a yw'r rhestrau'n gydamserol ai peidio.

Opsiwn 1. Rhestrau Cydamserol

Mae hwn yn opsiwn hawdd. Tybiwch fod gennym restrau o'r fath.

I benderfynu faint o weithiau nad oedd y gwerthoedd yn cydgyfeirio, gallwch ddefnyddio'r fformiwla: =SUMPRODUCT(—(A2:A20<>B2:B20)). Os cawn ni 0 o ganlyniad, mae hyn yn golygu bod y ddwy restr yr un peth.

Opsiwn 2: Rhestrau Cymysg

Os nad yw'r rhestrau yn union yr un fath yn nhrefn y gwrthrychau sydd ynddynt, mae angen i chi gymhwyso nodwedd fel fformatio amodol a lliwio gwerthoedd dyblyg. Neu defnyddiwch y swyddogaeth COUNTIF, gan ddefnyddio yr ydym yn pennu sawl gwaith y mae elfen o un rhestr yn digwydd yn yr ail.

Sut i gymharu 2 golofn fesul rhes

Pan fyddwn yn cymharu dwy golofn, yn aml mae angen i ni gymharu gwybodaeth sydd mewn rhesi gwahanol. I wneud hyn, bydd y gweithredwr yn ein helpu ni OS. Gadewch i ni edrych ar sut mae'n gweithio'n ymarferol. I wneud hyn, rydym yn cyflwyno nifer o sefyllfaoedd enghreifftiol.

Enghraifft. Sut i gymharu 2 golofn ar gyfer cyfatebiadau a gwahaniaethau mewn un rhes

I ddadansoddi a yw'r gwerthoedd sydd yn yr un rhes ond colofnau gwahanol yr un peth, rydym yn ysgrifennu'r swyddogaeth IF. Mewnosodir y fformiwla ym mhob rhes a osodir yn y golofn ategol lle bydd canlyniadau prosesu data yn cael eu harddangos. Ond nid oes angen ei ragnodi ym mhob rhes o gwbl, dim ond ei gopïo i mewn i gelloedd sy'n weddill yn y golofn hon neu ddefnyddio'r marciwr awtolenwi.

Dylem ysgrifennu fformiwla o'r fath i ddeall a yw'r gwerthoedd yn y ddwy golofn yr un peth ai peidio: =IF(A2=B2, “Cyfateb”, “”). Mae rhesymeg y swyddogaeth hon yn syml iawn: mae'n cymharu'r gwerthoedd yng nghelloedd A2 a B2, ac os ydynt yr un peth, mae'n dangos y gwerth "Cyd-ddigwyddiad". Os yw'r data yn wahanol, nid yw'n dychwelyd unrhyw werth. Gallwch hefyd wirio'r celloedd i weld a oes cyfatebiaeth rhyngddynt. Yn yr achos hwn, y fformiwla a ddefnyddir yw: =IF(A2<>B2, “Ddim yn cyfateb”, “”). Mae'r egwyddor yr un peth, yn gyntaf cynhelir y gwiriad. Os yw'n ymddangos bod y celloedd yn cwrdd â'r maen prawf, yna dangosir y gwerth “Nid yw'n cyfateb”.

Mae hefyd yn bosibl defnyddio'r fformiwla ganlynol yn y maes fformiwla i arddangos "Match" os yw'r gwerthoedd yr un peth, a "Peidiwch â chyfateb" os ydynt yn wahanol: =IF(A2=B2; “Cyfateb”, “Ddim yn cyfateb”). Gallwch hefyd ddefnyddio'r gweithredwr anghydraddoldeb yn lle'r gweithredwr cydraddoldeb. Dim ond trefn y gwerthoedd a fydd yn cael eu harddangos yn yr achos hwn fydd ychydig yn wahanol: =IF(A2<>B2, “Ddim yn cyfateb”, “cyd-ddigwydd”). Ar ôl defnyddio'r fersiwn gyntaf o'r fformiwla, bydd y canlyniad fel a ganlyn.

Mae'r amrywiad hwn o'r fformiwla yn ansensitif i achosion. Felly, os yw'r gwerthoedd mewn un golofn yn wahanol yn unig gan eu bod wedi'u hysgrifennu mewn priflythrennau, yna ni fydd y rhaglen yn sylwi ar y gwahaniaeth hwn. I wneud y gymhariaeth yn achos sensitif, mae angen i chi ddefnyddio'r swyddogaeth yn y meini prawf EXACT. Mae gweddill y dadleuon yn cael eu gadael heb eu newid: =IF(EXACT(A2,B2), “Match”, “Unigryw”).

Sut i gymharu colofnau lluosog ar gyfer gemau mewn un rhes

Mae'n bosibl dadansoddi'r gwerthoedd yn y rhestrau yn ôl set gyfan o feini prawf:

1. Dewch o hyd i'r rhesi hynny sydd â'r un gwerthoedd ym mhobman.
2. Dewch o hyd i'r rhesi hynny lle mae gemau mewn dwy restr yn unig.

Edrychwn ar rai enghreifftiau o sut i symud ymlaen ym mhob un o'r achosion hyn.

Enghraifft. Sut i ddod o hyd i gyfatebiadau mewn un rhes mewn colofnau lluosog o dabl

Tybiwch fod gennym gyfres o golofnau sy'n cynnwys y wybodaeth sydd ei hangen arnom. Rydym yn wynebu'r dasg o bennu'r rhesi hynny lle mae'r gwerthoedd yr un peth. I wneud hyn, mae angen i chi ddefnyddio'r fformiwla ganlynol: =IF(AND(A2=B2,A2=C2), “match”, ” “).

Os oes gormod o golofnau yn y tabl, yna does ond angen i chi ei ddefnyddio ynghyd â'r swyddogaeth IF gweithredwr COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"cyfateb";” “). Mae'r rhif a ddefnyddir yn y fformiwla hon yn nodi nifer y colofnau i'w gwirio. Os yw'n wahanol, yna mae angen ichi ysgrifennu cymaint ag sy'n wir ar gyfer eich sefyllfa.

Enghraifft. Sut i ddod o hyd i gyfatebiadau mewn un rhes mewn unrhyw 2 golofn mewn tabl

Gadewch i ni ddweud bod angen i ni wirio a yw'r gwerthoedd mewn un rhes yn cyfateb mewn dwy golofn o'r rhai yn y tabl. I wneud hyn, mae angen i chi ddefnyddio'r swyddogaeth fel amod OR, lle bob yn ail ysgrifennwch gydraddoldeb pob un o'r colofnau i'r llall. Dyma enghraifft.

Rydym yn defnyddio'r fformiwla hon: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Efallai y bydd sefyllfa pan fydd llawer o golofnau yn y tabl. Yn yr achos hwn, bydd y fformiwla yn enfawr, a gall gymryd llawer o amser i ddewis yr holl gyfuniadau angenrheidiol. I ddatrys y broblem hon, mae angen i chi ddefnyddio'r swyddogaeth COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Llinyn unigryw”; “Ddim yn llinyn unigryw”)

Gwelwn fod gennym ddwy swyddogaeth i gyd COUNTIF. Gyda'r un cyntaf, byddwn bob yn ail yn pennu faint o golofnau sy'n debyg i A2, a gyda'r ail un, rydym yn gwirio nifer y tebygrwydd â gwerth B2. Os byddwn, o ganlyniad i gyfrifo yn ôl y fformiwla hon, yn cael gwerth sero, mae hyn yn dangos bod pob rhes yn y golofn hon yn unigryw, os yn fwy, mae tebygrwydd. Felly, os byddwn yn cael gwerth sero o ganlyniad i gyfrifo â dwy fformiwla ac ychwanegu'r canlyniadau terfynol, yna dychwelir y gwerth testun "Llinyn unigryw", os yw'r rhif hwn yn fwy, ysgrifennir nad yw'r llinyn hwn yn unigryw.

Sut i gymharu 2 golofn yn Excel ar gyfer gemau

Nawr gadewch i ni gymryd enghraifft. Gadewch i ni ddweud bod gennym fwrdd gyda dwy golofn. Mae angen i chi wirio a ydynt yn cyfateb. I wneud hyn, mae angen i chi gymhwyso'r fformiwla, lle bydd y swyddogaeth hefyd yn cael ei ddefnyddio IF, a'r gweithredwr COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Dim yn cyfateb yng ngholofn B”, “Mae yna gyfatebiaethau yng ngholofn B”)

Nid oes angen unrhyw gamau pellach. Ar ôl cyfrifo'r canlyniad gan y fformiwla hon, rydym yn cael os yw gwerth trydydd dadl y swyddogaeth IF matsys. Os nad oes rhai, yna cynnwys yr ail ddadl.

Sut i gymharu 2 golofn yn Excel ar gyfer gemau ac amlygu gyda lliw

Er mwyn ei gwneud hi'n haws adnabod colofnau cyfatebol yn weledol, gallwch chi eu hamlygu â lliw. I wneud hyn, mae angen i chi ddefnyddio'r swyddogaeth "Fformatio Amodol". Gadewch i ni weld yn ymarferol.

Darganfod ac amlygu cyfatebiaethau yn ôl lliw mewn colofnau lluosog

I bennu'r cyfatebiadau a'u hamlygu, yn gyntaf rhaid i chi ddewis yr ystod ddata y bydd y gwiriad yn cael ei wneud ynddo, ac yna agor yr eitem "Fformatio Amodol" ar y tab "Cartref". Yno, dewiswch “Gwerthoedd Dyblyg” fel y rheol dewis celloedd.

Ar ôl hynny, bydd blwch deialog newydd yn ymddangos, lle yn y rhestr naid ar y chwith rydym yn dod o hyd i'r opsiwn "Ailadrodd", ac yn y rhestr dde rydym yn dewis y lliw a ddefnyddir ar gyfer y dewis. Ar ôl i ni glicio ar y botwm “OK”, bydd cefndir pob cell sydd â thebygrwydd yn cael ei ddewis. Yna dim ond cymharu'r colofnau â llygad.

Darganfod ac amlygu llinellau cyfatebol

Mae'r dechneg ar gyfer gwirio a yw llinynnau'n cyfateb ychydig yn wahanol. Yn gyntaf, mae angen inni greu colofn ychwanegol, ac yno byddwn yn defnyddio'r gwerthoedd cyfunol gan ddefnyddio'r & gweithredwr. I wneud hyn, mae angen i chi ysgrifennu fformiwla o'r ffurflen: =A2&B2&C2&D2.

Rydym yn dewis y golofn a grëwyd ac yn cynnwys y gwerthoedd cyfunol. Nesaf, rydym yn perfformio'r un dilyniant o gamau gweithredu a ddisgrifir uchod ar gyfer y colofnau. Bydd llinellau dyblyg yn cael eu hamlygu yn y lliw a nodir gennych.

Gwelwn nad oes dim byd anodd wrth chwilio am ailadroddiadau. Mae Excel yn cynnwys yr holl offer angenrheidiol ar gyfer hyn. Mae'n bwysig ymarfer cyn rhoi'r holl wybodaeth hon ar waith.