Kako primerjati dva seznama v Excelu

Excel je učinkovit program za obdelavo podatkov. In ena od metod analize informacij je primerjava dveh seznamov. Če pravilno primerjate dva seznama v Excelu, bo organiziranje tega postopka zelo enostavno. Dovolj je le slediti nekaterim točkam, o katerih bomo danes razpravljali. Praktična izvedba te metode je v celoti odvisna od potreb osebe ali organizacije v določenem trenutku. Zato je treba upoštevati več možnih primerov.

Primerjava dveh seznamov v Excelu

Seveda lahko dva seznama primerjate ročno. Vendar bo trajalo dolgo. Excel ima lastno inteligentno orodje, ki vam bo omogočilo ne le hitro primerjavo podatkov, temveč tudi pridobivanje informacij, ki jih ni tako enostavno dobiti z očmi. Recimo, da imamo dva stolpca s koordinatama A in B. Nekatere vrednosti se v njih ponavljajo.

Formulacija problema

Zato moramo te stolpce primerjati. Postopek za primerjavo dveh dokumentov je naslednji:

1. Če so unikatne celice vsakega od teh seznamov enake in je skupno število unikatnih celic enako in so celice enake, se ti seznami lahko štejejo za enake. Vrstni red, v katerem so vrednosti na tem seznamu zložene, ni tako pomemben.
2. O delnem sovpadanju seznamov lahko govorimo, če so same edinstvene vrednosti enake, vendar je število ponovitev drugačno. Zato lahko takšni seznami vsebujejo različno število elementov.
3. Dejstvo, da se seznama ne ujemata, je označeno z različnimi nizi edinstvenih vrednosti.

Vsi ti trije pogoji so hkrati pogoji našega problema.

Rešitev problema

Ustvarimo dva dinamična obsega za lažjo primerjavo seznamov. Vsak od njih bo ustrezal vsakemu od seznamov.

Če želite primerjati dva seznama, naredite naslednje:

1. V ločenem stolpcu ustvarimo seznam edinstvenih vrednosti, ki so specifične za oba seznama. Za to uporabimo formulo: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Sama formula mora biti zapisana kot matrična formula.
2. Ugotovimo, kolikokrat se posamezna edinstvena vrednost pojavi v podatkovnem nizu. Tukaj so formule za to: =COUNTI(Seznam1,D5) in =COUNTI(Seznam2,D5).
3. Če sta število ponovitev in število edinstvenih vrednosti enaka na vseh seznamih, ki so vključeni v te obsege, potem funkcija vrne vrednost 0. To pomeni, da je ujemanje XNUMX%. V tem primeru bodo naslovi teh seznamov pridobili zeleno ozadje.
4. Če je vsa edinstvena vsebina na obeh seznamih, vrnejo formule =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) vrednost bo enaka nič. Če E1 ne vsebuje ničle, vendar je taka vrednost vsebovana v celicah E2 in F2, bodo v tem primeru obsegi prepoznani kot ujemajoči se, vendar le delno. V tem primeru bodo naslovi ustreznih seznamov oranžni.
5. In če ena od zgoraj opisanih formul vrne vrednost, ki ni enaka nič, se seznama popolnoma ne ujemajo.

To je odgovor na vprašanje, kako analizirati stolpce za ujemanja z uporabo formul. Kot lahko vidite, lahko z uporabo funkcij izvedete skoraj vsako nalogo, ki na prvi pogled ni povezana z matematiko.

Primer testiranja

V naši različici tabele obstajajo tri vrste seznamov za vsako zgoraj opisano vrsto. Ima delno in popolno ujemanje, pa tudi neujemanje.

Za primerjavo podatkov uporabimo obseg A5:B19, v katerega izmenično vstavljamo te pare seznamov. O tem, kaj bo rezultat primerjave, bomo razumeli po barvi prvotnih seznamov. Če sta popolnoma drugačna, bo to rdeče ozadje. Če je del podatkov enak, potem rumeno. V primeru popolne istovetnosti bodo ustrezni naslovi zeleni. Kako narediti barvo glede na rezultat? To zahteva pogojno oblikovanje.

Iskanje razlik v dveh seznamih na dva načina

Opišimo še dve metodi za iskanje razlik, odvisno od tega, ali so seznami sinhroni ali ne.

Možnost 1. Sinhroni seznami

To je enostavna možnost. Recimo, da imamo takšne sezname.

Če želite ugotoviti, kolikokrat se vrednosti niso zbližale, lahko uporabite formulo: =SUMPRODUCT(—(A2:A20<>B2:B20)). Če smo kot rezultat dobili 0, to pomeni, da sta seznama enaka.

2. možnost: premešani seznami

Če seznami niso enaki glede na vrstni red predmetov, ki jih vsebujejo, morate uporabiti funkcijo, kot je pogojno oblikovanje in obarvanje podvojenih vrednosti. Ali pa uporabite funkcijo COUNTIF, s katerim določimo, kolikokrat se element iz enega seznama pojavi na drugem.

Kako primerjati 2 stolpca vrstico za vrstico

Ko primerjamo dva stolpca, moramo pogosto primerjati informacije, ki so v različnih vrsticah. Pri tem nam bo pomagal operater ČE. Poglejmo si, kako deluje v praksi. Da bi to naredili, predstavljamo več ilustrativnih situacij.

Primer. Kako primerjati 2 stolpca za ujemanja in razlike v eni vrstici

Za analizo, ali so vrednosti, ki so v isti vrstici, vendar v različnih stolpcih, enake, napišemo funkcijo IF. Formula se vstavi v vsako vrstico v pomožnem stolpcu, kjer bodo prikazani rezultati obdelave podatkov. Vendar ga sploh ni treba predpisati v vsaki vrstici, samo kopirajte ga v preostale celice tega stolpca ali uporabite oznako za samodokončanje.

Takšno formulo bi morali zapisati, da bi razumeli, ali so vrednosti v obeh stolpcih enake ali ne: =ČE(A2=B2, “Ujemanje”, “”). Logika te funkcije je zelo preprosta: primerja vrednosti v celicah A2 in B2 in če sta enaki, prikaže vrednost »Covpada«. Če so podatki drugačni, ne vrne nobene vrednosti. Prav tako lahko preverite celice, da vidite, ali obstaja ujemanje med njimi. V tem primeru je uporabljena formula: =IF(A2<>B2, “Ne ujema se”, “”). Načelo je enako, najprej se izvede pregled. Če se izkaže, da celice ustrezajo kriteriju, se prikaže vrednost »Ne ujema se«.

Prav tako je mogoče uporabiti naslednjo formulo v polju s formulo za prikaz tako »Ujemanje«, če so vrednosti enake, kot »Ne ujemanje«, če so različne: =IF(A2=B2; “Ujemanje”, “Ne ujemanje”). Namesto operatorja enakosti lahko uporabite tudi operator neenakosti. Samo vrstni red vrednosti, ki bodo prikazane v tem primeru, bo nekoliko drugačen: =IF(A2<>B2, “Ne ujema se”, “Sovpada”). Po uporabi prve različice formule bo rezultat naslednji.

Ta različica formule ne razlikuje med velikimi in malimi črkami. Torej, če se vrednosti v enem stolpcu razlikujejo od drugih le v tem, da so napisane z velikimi črkami, program te razlike ne bo opazil. Če želite, da bo primerjava občutljiva na velike in male črke, morate uporabiti funkcijo v kriterijih Točno. Ostali argumenti ostanejo nespremenjeni: =ČE(TOČNO(A2,B2), “Ujemanje”, “Edinstveno”).

Kako primerjati več stolpcev za ujemanja v eni vrstici

Vrednosti na seznamih je mogoče analizirati glede na celo vrsto meril:

1. Poiščite tiste vrstice, ki imajo povsod enake vrednosti.
2. Poiščite tiste vrstice, kjer so ujemanja na samo dveh seznamih.

Oglejmo si nekaj primerov, kako postopati v vsakem od teh primerov.

Primer. Kako najti ujemanja v eni vrstici v več stolpcih tabele

Recimo, da imamo vrsto stolpcev, ki vsebujejo informacije, ki jih potrebujemo. Soočeni smo z nalogo, da določimo tiste vrstice, v katerih so vrednosti enake. Če želite to narediti, morate uporabiti naslednjo formulo: =ČE(AND(A2=B2,A2=C2), “ujema se”, ” “).

Če je v tabeli preveč stolpcev, jo morate uporabiti skupaj s funkcijo IF operater COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”ujema se”;” “). Številka, uporabljena v tej formuli, označuje število stolpcev, ki jih je treba preveriti. Če se razlikuje, potem morate napisati toliko, kot je res za vašo situacijo.

Primer. Kako najti ujemanja v eni vrstici v poljubnih dveh stolpcih tabele

Recimo, da moramo preveriti, ali se vrednosti v eni vrstici ujemajo v dveh stolpcih s tistimi v tabeli. Če želite to narediti, morate uporabiti funkcijo kot pogoj OR, kjer izmenično zapišemo enakost vsakega od stolpcev drugemu. Tukaj je primer.

Uporabljamo to formulo: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Lahko pride do situacije, ko je v tabeli veliko stolpcev. V tem primeru bo formula ogromna in lahko bo trajalo veliko časa, da izberete vse potrebne kombinacije. Če želite rešiti to težavo, morate uporabiti funkcijo COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Edinstven niz”; “Needinstven niz”)

Vidimo, da imamo skupno dve funkciji COUNTIF. S prvim izmenično ugotavljamo, koliko stolpcev ima podobnost z A2, z drugim pa preverjamo število podobnosti z vrednostjo B2. Če kot rezultat izračuna po tej formuli dobimo vrednost nič, to pomeni, da so vse vrstice v tem stolpcu edinstvene, če jih je več, obstajajo podobnosti. Torej, če kot rezultat izračuna z dvema formulama in seštevanja končnih rezultatov dobimo ničelno vrednost, potem se vrne besedilna vrednost "Unikatni niz", če je ta številka večja, je zapisano, da ta niz ni edinstven.

Kako primerjati 2 stolpca v Excelu za ujemanja

Zdaj pa vzemimo primer. Recimo, da imamo tabelo z dvema stolpcema. Preveriti morate, ali se ujemajo. Če želite to narediti, morate uporabiti formulo, kjer bo uporabljena tudi funkcija IF, in operater COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “V stolpcu B ni zadetkov”, “V stolpcu B so zadetki”)

Nadaljnji ukrepi niso potrebni. Po izračunu rezultata s to formulo dobimo if vrednost tretjega argumenta funkcije IF tekme. Če jih ni, potem vsebina drugega argumenta.

Kako primerjati 2 stolpca v Excelu za ujemanja in označiti z barvo

Za lažje vizualno prepoznavanje ujemajočih se stolpcev jih lahko označite z barvo. Če želite to narediti, morate uporabiti funkcijo »Pogojno oblikovanje«. Poglejmo v praksi.

Iskanje in označevanje ujemanj po barvi v več stolpcih

Če želite določiti ujemanja in jih označiti, morate najprej izbrati obseg podatkov, v katerem bo opravljeno preverjanje, nato pa na zavihku »Domov« odpreti postavko »Pogojno oblikovanje«. Tam kot pravilo za izbiro celic izberite »Podvojene vrednosti«.

Po tem se prikaže novo pogovorno okno, v katerem na levem pojavnem seznamu najdemo možnost “Ponavljanje”, na desnem seznamu pa izberemo barvo, ki bo uporabljena za izbor. Ko kliknemo gumb »V redu«, bo izbrano ozadje vseh celic s podobnostmi. Nato samo primerjajte stolpce na oko.

Iskanje in označevanje ujemajočih se vrstic

Tehnika za preverjanje, ali se nizi ujemajo, je nekoliko drugačna. Najprej moramo ustvariti dodaten stolpec in tam bomo uporabili kombinirane vrednosti z uporabo & operatorja. Če želite to narediti, morate napisati formulo v obliki: =A2&B2&C2&D2.

Izberemo stolpec, ki je bil ustvarjen in vsebuje združene vrednosti. Nato izvedemo isto zaporedje dejanj, kot je opisano zgoraj za stolpce. Podvojene vrstice bodo označene z barvo, ki jo določite.

Vidimo, da ni nič težko iskati ponovitev. Excel vsebuje vsa potrebna orodja za to. Pomembno je le vaditi, preden vse to znanje prenesete v prakso.