Kuidas võrrelda kahte loendit Excelis

Excel on tõhus andmetöötlusprogramm. Ja üks teabeanalüüsi meetodeid on kahe loendi võrdlemine. Kui võrdlete Excelis kahte loendit õigesti, on selle protsessi korraldamine väga lihtne. Piisab vaid mõne punkti järgimisest, mida täna arutatakse. Selle meetodi praktiline rakendamine sõltub täielikult inimese või organisatsiooni vajadustest konkreetsel hetkel. Seetõttu tuleks kaaluda mitmeid võimalikke juhtumeid.

Kahe loendi võrdlemine Excelis

Muidugi saate kahte loendit käsitsi võrrelda. Aga see võtab kaua aega. Excelil on oma intelligentne tööriistakomplekt, mis võimaldab teil andmeid mitte ainult kiiresti võrrelda, vaid saada ka teavet, mida pole nii lihtne silmaga kätte saada. Oletame, et meil on kaks veergu koordinaatidega A ja B. Mõned väärtused korduvad neis.

Probleemi sõnastamine

Seega peame neid veerge võrdlema. Kahe dokumendi võrdlemise protseduur on järgmine:

1. Kui kõigi nende loendite kordumatud lahtrid on samad ja unikaalsete lahtrite koguarv on sama ja lahtrid on samad, võib neid loendeid pidada samadeks. Selle loendi väärtuste virnastamise järjekord ei oma suurt tähtsust.
2. Nimekirjade osalisest kokkulangemisest saame rääkida siis, kui unikaalsed väärtused ise on samad, kuid korduste arv on erinev. Seetõttu võivad sellised loendid sisaldada erinevat arvu elemente.
3. Asjaolu, et need kaks loendit ei ühti, näitab erinev unikaalsete väärtuste komplekt.

Kõik need kolm tingimust on samaaegselt meie probleemi tingimused.

Probleemi lahendus

Loome loendite võrdlemise hõlbustamiseks kaks dünaamilist vahemikku. Igaüks neist vastab igale loendile.

Kahe loendi võrdlemiseks tehke järgmist.

1. Eraldi veerus loome mõlema loendi jaoks omaste unikaalsete väärtuste loendi. Selleks kasutame valemit: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Valem ise tuleb kirjutada massiivivalemina.
2. Teeme kindlaks, mitu korda iga kordumatu väärtus andmemassiivis esineb. Siin on valemid selle tegemiseks: =LOENDIF(loend1,D5) ja =COUNTI(loend2,D5).
3. Kui nii korduste arv kui ka kordumatute väärtuste arv on kõigis nendes vahemikes sisalduvates loendites samad, tagastab funktsioon väärtuse 0. See näitab, et vaste on XNUMX%. Sel juhul omandavad nende loendite pealkirjad rohelise tausta.
4. Kui kogu kordumatu sisu on mõlemas loendis, tagastatakse valemitega =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) väärtus on null. Kui E1 ei sisalda nulli, kuid selline väärtus sisaldub lahtrites E2 ja F2, siis sel juhul tunnistatakse vahemikud sobivaks, kuid ainult osaliselt. Sel juhul muutuvad vastavate loendite pealkirjad oranžiks.
5. Ja kui üks ülalkirjeldatud valemitest tagastab nullist erineva väärtuse, on loendid täiesti mittevastavad.

See on vastus küsimusele, kuidas analüüsida vastete veerge valemite abil. Nagu näete, saate funktsioonide kasutamisega rakendada peaaegu kõiki ülesandeid, mis esmapilgul pole matemaatikaga seotud.

Näidistestimine

Meie tabeli versioonis on iga ülalkirjeldatud tüüpi kolme tüüpi loendid. Sellel on osaliselt ja täielikult sobivus, aga ka mittevastavus.

Andmete võrdlemiseks kasutame vahemikku A5:B19, kuhu vaheldumisi sisestame need loendipaarid. Sellest, milline on võrdluse tulemus, saame aru algsete loendite värvi järgi. Kui need on täiesti erinevad, on see punane taust. Kui osa andmetest on samad, siis kollane. Täieliku identiteedi korral on vastavad pealkirjad rohelised. Kuidas teha värvi sõltuvalt sellest, milline on tulemus? See nõuab tingimusvormingut.

Kahe loendi erinevuste leidmine kahel viisil

Kirjeldame veel kahte meetodit erinevuste leidmiseks, olenevalt sellest, kas loendid on sünkroonsed või mitte.

Valik 1. Sünkroonsed loendid

See on lihtne variant. Oletame, et meil on sellised loendid.

Et määrata, mitu korda väärtused ei lähenenud, võite kasutada valemit: =SUMMA(—(A2:A20<>B2:B20)). Kui saime tulemuseks 0, tähendab see, et kaks loendit on samad.

2. valik: segatud loendid

Kui loendid ei ole neis sisalduvate objektide järjekorras identsed, peate rakendama funktsiooni, näiteks tingimusvormingu ja värvima duplikaatväärtusi. Või kasutage funktsiooni COUNTIF, mille abil määrame, mitu korda ühes loendis olev element teises esineb.

Kuidas võrrelda kahte veergu ridade kaupa

Kui võrdleme kahte veergu, peame sageli võrdlema erinevatel ridadel olevat teavet. Selleks aitab meid operaator KUI Vaatame, kuidas see praktikas töötab. Selleks esitame mitu illustreerivat olukorda.

Näide. Kuidas võrrelda 2 veergu vastete ja erinevuste jaoks ühes reas

Et analüüsida, kas samas reas, kuid erinevates veergudes olevad väärtused on samad, kirjutame funktsiooni IF. Valem sisestatakse igale reale, mis on paigutatud abiveergu, kus kuvatakse andmetöötluse tulemused. Kuid seda pole üldse vaja igale reale ette kirjutada, lihtsalt kopeerige see selle veeru ülejäänud lahtritesse või kasutage automaatse täitmise markerit.

Peaksime üles kirjutama sellise valemi, et mõista, kas mõlema veeru väärtused on samad või mitte: =IF(A2=B2, "Sobimine", ""). Selle funktsiooni loogika on väga lihtne: see võrdleb väärtusi lahtrites A2 ja B2 ning kui need on samad, kuvatakse väärtus "Kohtub". Kui andmed on erinevad, ei tagasta see väärtust. Samuti saate kontrollida lahtreid, et näha, kas nende vahel on sobivus. Sel juhul kasutatakse järgmist valemit: =IF(A2<>B2, “Ei sobi”, “”). Põhimõte on sama, kõigepealt tehakse kontroll. Kui selgub, et lahtrid vastavad kriteeriumile, kuvatakse väärtus "Ei sobi".

Samuti on võimalik valemiväljal kasutada järgmist valemit, et kuvada nii „Sobib”, kui väärtused on samad, ja „Ei sobi”, kui need on erinevad: =IF(A2=B2; “Sobib”, “Ei sobi”). Võrdsuse operaatori asemel võite kasutada ka ebavõrdsuse operaatorit. Ainult sel juhul kuvatavate väärtuste järjekord on veidi erinev: =IF(A2<>B2, “Ei sobi”, “Kattub”). Pärast valemi esimese versiooni kasutamist on tulemus järgmine.

See valemi variatsioon ei ole tõstutundlik. Seega, kui ühes veerus olevad väärtused erinevad teistest ainult selle poolest, et need on kirjutatud suurtähtedega, siis programm seda erinevust ei märka. Võrdluse tõstutundlikuks muutmiseks peate kriteeriumides kasutama funktsiooni TÄIELIK. Ülejäänud argumendid jäetakse muutmata: =IF(TÄPNE(A2,B2), "Sobimine", "Unikaalne").

Kuidas võrrelda mitut veergu vastete jaoks ühes reas

Loendites olevaid väärtusi on võimalik analüüsida terve hulga kriteeriumide järgi:

1. Leidke need read, millel on kõikjal samad väärtused.
2. Otsige üles need read, kus on vasteid ainult kahes loendis.

Vaatame mõnda näidet selle kohta, kuidas kõigil neil juhtudel edasi toimida.

Näide. Kuidas leida ühest reast vasteid tabeli mitmes veerus

Oletame, et meil on rida veerge, mis sisaldavad meile vajalikku teavet. Meie ees on ülesanne määrata need read, mille väärtused on samad. Selleks peate kasutama järgmist valemit: =IF(JA(A2=B2,A2=C2), "sobib", " ").

Kui tabelis on liiga palju veerge, peate seda lihtsalt funktsiooniga koos kasutama IF operaator COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”sobivus”;” “). Selles valemis kasutatav number näitab kontrollitavate veergude arvu. Kui see erineb, peate kirjutama nii palju, kui see on teie olukorra jaoks tõsi.

Näide. Kuidas leida vasteid ühest reast tabeli kahes veerus

Oletame, et peame kontrollima, kas ühe rea väärtused kattuvad kahes tabeli veerus. Selleks peate funktsiooni tingimusena kasutama OR, kus vaheldumisi kirjutage iga veeru võrdsus teisega. Siin on näide.

Kasutame seda valemit: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Võib tekkida olukord, kus tabelis on palju veerge. Sel juhul on valem tohutu ja kõigi vajalike kombinatsioonide valimine võib võtta palju aega. Selle probleemi lahendamiseks peate kasutama funktsiooni COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "Unikaalne string"; "Pole kordumatu string"

Näeme, et kokku on meil kaks funktsiooni COUNTIF. Esimesega määrame vaheldumisi, kui paljudel veergudel on sarnasus A2-ga, ja teisega kontrollime sarnasuste arvu B2 väärtusega. Kui selle valemiga arvutamise tulemusel saame nullväärtuse, näitab see, et kõik selle veeru read on kordumatud, kui rohkem, siis on sarnasusi. Seega, kui kahe valemiga arvutamise ja lõpptulemuste liitmise tulemusena saame nullväärtuse, siis tagastatakse tekstiväärtus “Unikaalne string”, kui see arv on suurem, siis kirjutatakse, et see string ei ole unikaalne.

Kuidas võrrelda vastete leidmiseks Excelis kahte veergu

Nüüd võtame näite. Oletame, et meil on kahe veeruga tabel. Peate kontrollima, kas need sobivad. Selleks tuleb rakendada valemit, kus kasutatakse ka funktsiooni IFja operaator COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Veerus B vasteid pole", "Veerus B on vasteid"

Edasised toimingud pole vajalikud. Pärast selle valemiga tulemuse arvutamist saame kui funktsiooni kolmanda argumendi väärtuse IF tikud. Kui neid pole, siis teise argumendi sisu.

Kuidas võrrelda Excelis kahte veergu vastete leidmiseks ja värviga esiletõstmiseks

Sobivate veergude visuaalse tuvastamise hõlbustamiseks saate need värviga esile tõsta. Selleks peate kasutama funktsiooni "Tingimuslik vormindamine". Vaatame praktikas.

Vastete otsimine ja esiletõstmine värvi järgi mitmes veerus

Vastete määramiseks ja esiletõstmiseks peate esmalt valima andmevahemiku, milles kontrollimine läbi viiakse, ja seejärel avama vahekaardil "Kodu" üksuse "Tingimuslik vormindamine". Seal valige lahtri valimise reegliks "Duplicate Values".

Pärast seda ilmub uus dialoogiboks, kus vasakpoolses hüpikloendis leiame valiku “Kordamine” ja paremas loendis valime värvi, mida valikuks kasutatakse. Pärast nupu "OK" klõpsamist valitakse kõigi sarnasustega lahtrite taust. Seejärel võrrelge veerge silma järgi.

Sobivate joonte leidmine ja esiletõstmine

Keelte sobivuse kontrollimise tehnika on veidi erinev. Esiteks peame looma täiendava veeru ja seal kasutame kombineeritud väärtusi, kasutades operaatorit &. Selleks peate kirjutama järgmise vormi valemi: =A2&B2&C2&D2.

Valime veeru, mis loodi ja sisaldab kombineeritud väärtusi. Järgmisena teostame sama toimingute jada, mida on ülalpool veergude puhul kirjeldatud. Duplikaatread tõstetakse esile teie määratud värviga.

Näeme, et korduste otsimises pole midagi rasket. Excel sisaldab selleks kõiki vajalikke tööriistu. Enne kõigi nende teadmiste ellu viimist on oluline lihtsalt harjutada.