Hvernig á að bera saman tvo lista í Excel

Excel er skilvirkt gagnavinnsluforrit. Og ein af aðferðum upplýsingagreiningar er samanburður á tveimur listum. Ef þú berð saman tvo lista á réttan hátt í Excel verður mjög auðvelt að skipuleggja þetta ferli. Það er nóg að fylgjast með nokkrum af þeim atriðum sem verða rædd í dag. Hagnýt útfærsla þessarar aðferðar fer algjörlega eftir þörfum einstaklingsins eða stofnunarinnar á tilteknu augnabliki. Því ætti að skoða nokkur möguleg tilvik.

Að bera saman tvo lista í Excel

Auðvitað er hægt að bera saman tvo lista handvirkt. En það mun taka langan tíma. Excel hefur sitt eigið snjalla verkfærasett sem gerir þér kleift að bera saman gögn ekki aðeins fljótt heldur einnig til að fá upplýsingar sem ekki er svo auðvelt að fá með augum þínum. Segjum að við höfum tvo dálka með hnitunum A og B. Sum gildi eru endurtekin í þeim.

Mótun vandans

Við þurfum því að bera saman þessa dálka. Aðferðin við að bera saman tvö skjöl er sem hér segir:

1. Ef einstöku frumur hvers þessara lista eru þær sömu, og heildarfjöldi einstakra frumna er sá sami, og frumurnar eru þær sömu, þá geta þessir listar talist eins. Röðin sem gildunum á þessum lista er staflað í skiptir ekki svo miklu máli.
2. Við getum talað um að hluta tilviljun lista ef einstöku gildin sjálf eru þau sömu, en fjöldi endurtekninga er mismunandi. Þess vegna geta slíkir listar innihaldið mismunandi fjölda þátta.
3. Sú staðreynd að listarnir tveir passa ekki saman er gefið til kynna með mismunandi setti einstakra gilda.

Öll þessi þrjú skilyrði eru samtímis skilyrði vandamála okkar.

Lausn vandans

Við skulum búa til tvö kvik svið til að gera það auðveldara að bera saman lista. Hver þeirra mun samsvara hverjum lista.

Til að bera saman tvo lista skaltu gera eftirfarandi:

1. Í sérstökum dálki búum við til lista yfir einstök gildi sem eru sértæk fyrir báða lista. Til þess notum við formúluna: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Formúlan sjálf verður að vera skrifuð sem fylkisformúla.
2. Við skulum ákvarða hversu oft hvert einstakt gildi kemur fyrir í gagnafylkinu. Hér eru formúlurnar til að gera þetta: =COUNTIF(Listi1,D5) og =COUNTI(Listi2,D5).
3. Ef bæði fjöldi endurtekningar og fjöldi einstakra gilda er sá sami á öllum listum sem eru innifalin í þessum sviðum, þá skilar fallið gildinu 0. Þetta gefur til kynna að samsvörunin sé XNUMX%. Í þessu tilviki munu fyrirsagnir þessara lista fá grænan bakgrunn.
4. Ef allt einstakt efni er á báðum listum, þá er það skilað með formúlum =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) gildið verður núll. Ef E1 inniheldur ekki núll, en slíkt gildi er að finna í hólfum E2 og F2, þá verða sviðin í þessu tilviki viðurkennd sem samsvörun, en aðeins að hluta. Í þessu tilviki verða fyrirsagnir samsvarandi lista appelsínugular.
5. Og ef ein af formúlunum sem lýst er hér að ofan skilar gildi sem er ekki núll, munu listarnir vera algjörlega ósamræmandi.

Þetta er svarið við spurningunni um hvernig á að greina dálka fyrir samsvörun með formúlum. Eins og þú sérð, með notkun aðgerða, geturðu útfært næstum hvaða verkefni sem við fyrstu sýn tengist ekki stærðfræði.

Dæmi um prófun

Í útgáfu okkar af töflunni eru þrjár tegundir af listum af hverri gerð sem lýst er hér að ofan. Það hefur að hluta og algjörlega samsvörun, sem og ósamsvörun.

Til að bera saman gögn notum við bilið A5:B19, þar sem við setjum inn þessi listapör til skiptis. Um hvað verður niðurstaðan af samanburðinum munum við skilja eftir lit upprunalegu listanna. Ef þeir eru allt öðruvísi, þá verður það rauður bakgrunnur. Ef hluti gagnanna er eins, þá gulur. Ef um er að ræða fullkomið auðkenni verða samsvarandi fyrirsagnir grænar. Hvernig á að gera lit eftir því hver útkoman er? Þetta krefst skilyrts sniðs.

Að finna mun á tveimur listum á tvo vegu

Við skulum lýsa tveimur aðferðum til viðbótar til að finna mun, eftir því hvort listarnir eru samstilltir eða ekki.

Valkostur 1. Samstillir listar

Þetta er auðveldur kostur. Segjum að við höfum slíka lista.

Til að ákvarða hversu oft gildin runnu ekki saman geturðu notað formúluna: =SUMVARA(—(A2:A20<>B2:B20)). Ef við fengum 0 í kjölfarið þýðir þetta að listarnir tveir eru eins.

Valkostur 2: Stokkaðir listar

Ef listarnir eru ekki eins í röð hlutanna sem þeir innihalda, þarftu að nota eiginleika eins og skilyrt snið og lita tvítekin gildi. Eða notaðu aðgerðina COUNTIF, þar sem við ákveðum hversu oft þáttur úr einum lista kemur fyrir í þeim seinni.

Hvernig á að bera saman 2 dálka röð fyrir röð

Þegar við berum saman tvo dálka þurfum við oft að bera saman upplýsingar sem eru í mismunandi röðum. Til að gera þetta mun rekstraraðilinn hjálpa okkur IF. Við skulum skoða hvernig það virkar í reynd. Til að gera þetta kynnum við nokkrar lýsandi aðstæður.

Dæmi. Hvernig á að bera saman 2 dálka fyrir samsvörun og mismun í einni röð

Til að greina hvort gildin sem eru í sömu röð en mismunandi dálkar séu þau sömu, skrifum við fallið IF. Formúlan er sett inn í hverja röð sem er sett í aukadálkinn þar sem niðurstöður gagnavinnslu verða birtar. En það er alls ekki nauðsynlegt að ávísa því í hverri röð, afritaðu það bara í þær frumur sem eftir eru í þessum dálki eða notaðu sjálfvirka útfyllingarmerkið.

Við ættum að skrifa niður slíka formúlu til að skilja hvort gildin í báðum dálkum eru þau sömu eða ekki: =IF(A2=B2, „Passa“, „“). Rökfræðin í þessari aðgerð er mjög einföld: hún ber saman gildin í hólfum A2 og B2, og ef þau eru þau sömu sýnir hún gildið „Sameira“. Ef gögnin eru önnur skila þau ekki neinu gildi. Þú getur líka athugað frumurnar til að sjá hvort það sé samsvörun á milli þeirra. Í þessu tilviki er formúlan sem notuð er: =IF(A2<>B2, "Sammast ekki við", ""). Meginreglan er sú sama, fyrst er eftirlitið framkvæmt. Ef það kemur í ljós að frumurnar uppfylla viðmiðið, þá birtist gildið "Samkvæmir ekki".

Það er líka hægt að nota eftirfarandi formúlu í formúlureitnum til að birta bæði „Passa“ ef gildin eru þau sömu og „Passast ekki“ ef þau eru ólík: =IF(A2=B2; „Passa“, „Passast ekki“). Þú getur líka notað ójöfnuðsvirkjann í staðinn fyrir jafnréttisaðgerðina. Aðeins röð gildanna sem birtast í þessu tilfelli verður aðeins öðruvísi: =IF(A2<>B2, „Passast ekki“, „Saman“). Eftir að hafa notað fyrstu útgáfu formúlunnar verður niðurstaðan sem hér segir.

Þessi breytileiki formúlunnar er ónæmur fyrir hástöfum. Þess vegna, ef gildin í einum dálki eru aðeins frábrugðin öðrum að því leyti að þau eru skrifuð með hástöfum, mun forritið ekki taka eftir þessum mun. Til að gera samanburðinn há- og hástöfumnæman þarftu að nota fallið í viðmiðunum NÁKVÆMLEGA. Restin af röksemdum er óbreytt: =EF(EXACT(A2,B2), „Passa“, „Einstakt“).

Hvernig á að bera saman marga dálka fyrir samsvörun í einni röð

Það er hægt að greina gildin á listunum í samræmi við allt sett af viðmiðum:

1. Finndu þessar línur sem hafa sömu gildi alls staðar.
2. Finndu þessar línur þar sem það eru samsvörun í aðeins tveimur listum.

Við skulum skoða nokkur dæmi um hvernig á að halda áfram í hverju þessara mála.

Dæmi. Hvernig á að finna samsvörun í einni röð í mörgum dálkum töflu

Segjum að við höfum röð dálka sem innihalda þær upplýsingar sem við þurfum. Við stöndum frammi fyrir því verkefni að ákvarða þær línur þar sem gildin eru þau sömu. Til að gera þetta þarftu að nota eftirfarandi formúlu: =EF(OG(A2=B2,A2=C2), “passa”, “ “).

Ef það eru of margir dálkar í töflunni, þá þarftu bara að nota það ásamt aðgerðinni IF rekstraraðila COUNTIF: =EF(COUNTIF($A2:$C2,$A2)=3;"samsvörun";" "). Talan sem notuð er í þessari formúlu gefur til kynna fjölda dálka sem á að athuga. Ef það er mismunandi, þá þarftu að skrifa eins mikið og satt er fyrir aðstæður þínar.

Dæmi. Hvernig á að finna samsvörun í einni röð í hvaða 2 dálkum sem er í töflu

Segjum að við þurfum að athuga hvort gildin í einni röð passa í tveimur dálkum frá þeim í töflunni. Til að gera þetta þarftu að nota aðgerðina sem skilyrði OR, þar sem til skiptis skrifa jafnræði hvers dálka við annan. Hér er dæmi.

Við notum þessa formúlu: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Það getur komið upp sú staða að það sé mikið af dálkum í töflunni. Í þessu tilviki verður formúlan risastór og það getur tekið langan tíma að velja allar nauðsynlegar samsetningar. Til að leysa þetta vandamál þarftu að nota aðgerðina COUNTIF: =EF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Einstakur strengur“; „Ekki einstakur strengur“)

Við sjáum að samtals höfum við tvær aðgerðir COUNTIF. Með þeim fyrri ákveðum við til skiptis hversu margir dálkar eru líkir A2 og með þeim seinni athugum við fjölda líkinga með gildi B2. Ef við fáum núllgildi, vegna útreiknings með þessari formúlu, gefur það til kynna að allar línur í þessum dálki séu einstakar, ef fleiri eru líkindi. Þess vegna, ef við fáum núllgildi sem afleiðing af því að reikna með tveimur formúlum og bæta við endanlegum niðurstöðum, þá er textagildinu „Einstakt strengur“ skilað, ef þessi tala er hærri, er skrifað að þessi strengur sé ekki einstakur.

Hvernig á að bera saman 2 dálka í Excel fyrir samsvörun

Nú skulum við taka dæmi. Segjum að við höfum töflu með tveimur dálkum. Þú þarft að athuga hvort þeir passa saman. Til að gera þetta þarftu að beita formúlunni, þar sem aðgerðin verður einnig notuð IF, og rekstraraðila COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Engar samsvörun í dálki B", "Það eru samsvörun í dálki B")

Ekki er þörf á frekari aðgerðum. Eftir að hafa reiknað út niðurstöðuna með þessari formúlu fáum við ef gildi þriðju röksemdar fallsins IF eldspýtur. Ef þær eru engar, þá innihald seinni röksemdarinnar.

Hvernig á að bera saman 2 dálka í Excel fyrir samsvörun og auðkenna með lit

Til að auðvelda þér að bera kennsl á samsvarandi dálka sjónrænt er hægt að auðkenna þá með lit. Til að gera þetta þarftu að nota „skilyrt snið“ aðgerðina. Við skulum sjá í reynd.

Að finna og auðkenna samsvörun eftir lit í mörgum dálkum

Til að ákvarða samsvörunina og auðkenna þær, verður þú fyrst að velja gagnasviðið þar sem athugunin verður framkvæmd og opna síðan hlutinn „Skilyrt snið“ á flipanum „Heim“. Þar skaltu velja „Afrit gildi“ sem frumvalsreglu.

Eftir það birtist nýr svargluggi, þar sem í vinstri sprettigluggalistanum finnum við valkostinn „Endurtekið“ og í hægri listanum veljum við litinn sem verður notaður við valið. Eftir að við smellum á „Í lagi“ hnappinn verður bakgrunnur allra frumna sem eru líkir valinn. Svo berðu bara saman dálkana eftir augum.

Að finna og auðkenna samsvarandi línur

Tæknin til að athuga hvort strengir passa er aðeins öðruvísi. Fyrst þurfum við að búa til viðbótardálk og þar munum við nota sameinuðu gildin með því að nota & rekstraraðilann. Til að gera þetta þarftu að skrifa formúlu af forminu: =A2&B2&C2&D2.

Við veljum dálkinn sem var búinn til og inniheldur sameinuð gildi. Næst framkvæmum við sömu röð aðgerða og lýst er hér að ofan fyrir dálkana. Tvíteknar línur verða auðkenndar í þeim lit sem þú tilgreinir.

Við sjáum að það er ekkert erfitt að leita að endurtekningum. Excel inniheldur öll nauðsynleg verkfæri til þess. Það er mikilvægt að æfa sig aðeins áður en þú setur alla þessa þekkingu í framkvæmd.