Kaip palyginti du sąrašus „Excel“.

„Excel“ yra efektyvi duomenų apdorojimo programa. O vienas iš informacijos analizės metodų yra dviejų sąrašų palyginimas. Jei teisingai palyginsite du sąrašus programoje „Excel“, organizuoti šį procesą bus labai paprasta. Pakanka tik laikytis kai kurių punktų, kurie bus aptarti šiandien. Praktinis šio metodo įgyvendinimas visiškai priklauso nuo asmens ar organizacijos poreikių konkrečiu momentu. Todėl reikėtų apsvarstyti keletą galimų atvejų.

Dviejų sąrašų palyginimas programoje „Excel“.

Žinoma, galite palyginti du sąrašus rankiniu būdu. Bet tai užtruks ilgai. „Excel“ turi savo išmanųjį įrankių rinkinį, kuris leis ne tik greitai palyginti duomenis, bet ir gauti informaciją, kurią akimis gauti nėra taip paprasta. Tarkime, kad turime du stulpelius su koordinatėmis A ir B. Kai kurios reikšmės juose kartojasi.

Problemos formulavimas

Taigi turime palyginti šiuos stulpelius. Dviejų dokumentų palyginimo procedūra yra tokia:

1. Jei kiekvieno iš šių sąrašų unikalūs langeliai yra vienodi, o bendras unikalių langelių skaičius yra toks pat, o langeliai yra vienodi, tada šie sąrašai gali būti laikomi vienodais. Šiame sąraše esančių verčių sudėjimo tvarka nėra tokia svarbi.
2. Apie dalinį sąrašų sutapimą galime kalbėti, jei pačios unikalios reikšmės yra vienodos, bet skiriasi pakartojimų skaičius. Todėl tokiuose sąrašuose gali būti skirtingas elementų skaičius.
3. Tai, kad šie du sąrašai nesutampa, rodo skirtingą unikalių reikšmių rinkinį.

Visos šios trys sąlygos vienu metu yra mūsų problemos sąlygos.

Problemos sprendimas

Sugeneruokime du dinaminius diapazonus, kad būtų lengviau palyginti sąrašus. Kiekvienas iš jų atitiks kiekvieną iš sąrašų.

Norėdami palyginti du sąrašus, atlikite šiuos veiksmus:

1. Atskirame stulpelyje sukuriame unikalių verčių, būdingų abiem sąrašams, sąrašą. Tam naudojame formulę: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Pati formulė turi būti parašyta kaip masyvo formulė.
2. Nustatykime, kiek kartų kiekviena unikali reikšmė atsiranda duomenų masyve. Štai formulės, kaip tai padaryti: =COUNTIF(Sąrašas1,D5) ir =COUNTI(Sąrašas2,D5).
3. Jei pakartojimų skaičius ir unikalių reikšmių skaičius yra vienodi visuose sąrašuose, kurie yra įtraukti į šiuos diapazonus, funkcija grąžina reikšmę 0. Tai rodo, kad atitiktis yra XNUMX%. Tokiu atveju šių sąrašų antraštės įgaus žalią foną.
4. Jei visas unikalus turinys yra abiejuose sąrašuose, grąžinamas pagal formules =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) reikšmė bus lygi nuliui. Jei E1 nėra nulio, bet tokia reikšmė yra langeliuose E2 ir F2, tokiu atveju diapazonai bus atpažįstami kaip atitinkantys, bet tik iš dalies. Tokiu atveju atitinkamų sąrašų antraštės taps oranžinės spalvos.
5. Ir jei viena iš aukščiau aprašytų formulių grąžina vertę, kuri nėra nulis, sąrašai bus visiškai nesutampantys.

Tai atsakymas į klausimą, kaip analizuoti atitikmenų stulpelius naudojant formules. Kaip matote, naudodamiesi funkcijomis galite įgyvendinti beveik bet kokią užduotį, kuri, iš pirmo žvilgsnio, nesusijusi su matematika.

Testavimo pavyzdys

Mūsų lentelės versijoje yra trijų tipų sąrašai, kurių kiekvienas aprašytas aukščiau. Jis turi iš dalies ir visiškai atitinkantį, taip pat nederantį.

Norėdami palyginti duomenis, naudojame diapazoną A5:B19, į kurį pakaitomis įterpiame šias sąrašų poras. Apie tai, koks bus palyginimo rezultatas, suprasime pagal pradinių sąrašų spalvą. Jei jie visiškai skirtingi, tai bus raudonas fonas. Jei dalis duomenų yra vienodi, tada geltona. Visiškos tapatybės atveju atitinkamos antraštės bus žalios. Kaip padaryti spalvą, priklausomai nuo to, koks rezultatas? Tam reikalingas sąlyginis formatavimas.

Dviejų sąrašų skirtumų paieška dviem būdais

Aprašykime dar du metodus skirtumams rasti, priklausomai nuo to, ar sąrašai yra sinchroniniai, ar ne.

1 variantas. Sinchroniniai sąrašai

Tai lengvas pasirinkimas. Tarkime, kad turime tokius sąrašus.

Norėdami nustatyti, kiek kartų vertės nesutapo, galite naudoti formulę: =SUMMA PRODUKTAS(—(A2:A20<>B2:B20)). Jei gausime 0, tai reiškia, kad du sąrašai yra vienodi.

2 parinktis: sumaišyti sąrašai

Jei sąrašai nėra identiški juose esančių objektų tvarka, turite pritaikyti tokią funkciją kaip sąlyginis formatavimas ir nuspalvinti pasikartojančias reikšmes. Arba naudokite funkciją COUNTIF, pagal kurį nustatome, kiek kartų elementas iš vieno sąrašo atsiranda antrajame.

Kaip palyginti 2 stulpelius eilutę po eilutės

Kai lyginame du stulpelius, dažnai turime palyginti informaciją, kuri yra skirtingose ​​eilutėse. Norėdami tai padaryti, operatorius mums padės Jei Pažiūrėkime, kaip tai veikia praktiškai. Norėdami tai padaryti, pateikiame keletą iliustruojančių situacijų.

Pavyzdys. Kaip palyginti 2 stulpelius, atitinkančius ir skirtumus vienoje eilutėje

Norėdami išanalizuoti, ar reikšmės, esančios toje pačioje eilutėje, bet skirtinguose stulpeliuose, yra vienodos, rašome funkciją IF. Formulė įterpiama į kiekvieną eilutę, esančią pagalbiniame stulpelyje, kur bus rodomi duomenų apdorojimo rezultatai. Bet visai nebūtina jo nurodyti kiekvienoje eilutėje, tiesiog nukopijuokite į likusius šio stulpelio langelius arba naudokite automatinio užbaigimo žymeklį.

Turėtume užrašyti tokią formulę, kad suprastume, ar abiejų stulpelių reikšmės yra vienodos, ar ne: =IF(A2=B2, "Atitiktis", ""). Šios funkcijos logika labai paprasta: ji lygina vertes langeliuose A2 ir B2, o jei jos sutampa, rodo reikšmę „Sutampa“. Jei duomenys skiriasi, jie nepateikia jokios reikšmės. Taip pat galite patikrinti langelius, kad pamatytumėte, ar jie atitinka. Šiuo atveju naudojama formulė: =IF(A2<>B2, "Neatitinka", ""). Principas tas pats, pirmiausia atliekama patikra. Jei paaiškėja, kad langeliai atitinka kriterijų, rodoma reikšmė „Neatitinka“.

Taip pat formulės lauke galima naudoti šią formulę, kad būtų rodoma „Atitiktis“, jei reikšmės yra vienodos, ir „Neatitinka“, jei jos skiriasi: =IF(A2=B2; „Atitinka“, „Neatitinka“). Taip pat galite naudoti nelygybės operatorių, o ne lygybės operatorių. Tik reikšmių tvarka, kuri bus rodoma šiuo atveju, šiek tiek skirsis: =IF(A2<>B2, „Neatitinka“, „Sutampa“). Panaudojus pirmąją formulės versiją, rezultatas bus toks.

Šis formulės variantas neskiria didžiųjų ir mažųjų raidžių. Todėl, jei reikšmės viename stulpelyje skiriasi nuo kitų tik tuo, kad jos parašytos didžiosiomis raidėmis, programa šio skirtumo nepastebės. Kad palyginimui būtų skiriamos didžiosios ir mažosios raidės, kriterijuose turite naudoti funkciją TIKSLAS. Kiti argumentai paliekami nepakeisti: =JEI(TIKSLUS(A2,B2), „Atitiktis“, „Unikalūs“).

Kaip palyginti kelis stulpelius, kad atitiktų vienoje eilutėje

Sąrašų reikšmes galima analizuoti pagal daugybę kriterijų:

1. Raskite tas eilutes, kurių reikšmės visur vienodos.
2. Raskite tas eilutes, kuriose yra atitikčių tik dviejuose sąrašuose.

Pažvelkime į kelis pavyzdžius, kaip elgtis kiekvienu iš šių atvejų.

Pavyzdys. Kaip rasti atitikmenis vienoje eilutėje keliuose lentelės stulpeliuose

Tarkime, kad turime keletą stulpelių, kuriuose yra reikalinga informacija. Mes susiduriame su užduotimi nustatyti tas eilutes, kuriose reikšmės yra vienodos. Norėdami tai padaryti, turite naudoti šią formulę: =JEI(IR(A2=B2,A2=C2), "atitikimas", " ").

Jei lentelėje yra per daug stulpelių, tereikia jį naudoti kartu su funkcija IF operatorius COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"atitikimas";" "). Šioje formulėje naudojamas skaičius nurodo tikrintinų stulpelių skaičių. Jei jis skiriasi, turite parašyti tiek, kiek tinka jūsų situacijai.

Pavyzdys. Kaip rasti atitikmenis vienoje eilutėje bet kuriuose 2 lentelės stulpeliuose

Tarkime, kad turime patikrinti, ar vertės vienoje eilutėje sutampa dviejuose lentelės stulpeliuose. Norėdami tai padaryti, turite naudoti funkciją kaip sąlygą OR, kur pakaitomis parašykite kiekvieno stulpelio lygybę kitoms. Štai pavyzdys.

Mes naudojame šią formulę: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Gali būti situacija, kai lentelėje yra daug stulpelių. Tokiu atveju formulė bus didžiulė, o visų reikalingų derinių parinkimas gali užtrukti daug laiko. Norėdami išspręsti šią problemą, turite naudoti funkciją COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Unikali eilutė“; „Ne unikali eilutė“)

Matome, kad iš viso turime dvi funkcijas COUNTIF. Su pirmuoju pakaitomis nustatome, kiek stulpelių yra panašumo į A2, o su antruoju patikriname panašumų skaičių su B2 reikšme. Jei skaičiuodami pagal šią formulę gauname nulinę reikšmę, tai reiškia, kad visos šio stulpelio eilutės yra unikalios, o jei daugiau, yra panašumų. Todėl, jei skaičiuodami pagal dvi formules ir sudėjus galutinius rezultatus gauname nulinę reikšmę, tada grąžinama tekstinė reikšmė „Unikali eilutė“, jei šis skaičius didesnis, rašoma, kad ši eilutė nėra unikali.

Kaip palyginti 2 „Excel“ stulpelius, kad jie atitiktų

Dabar paimkime pavyzdį. Tarkime, kad turime lentelę su dviem stulpeliais. Turite patikrinti, ar jie sutampa. Norėdami tai padaryti, turite pritaikyti formulę, kurioje taip pat bus naudojama funkcija IF, ir operatorius COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "B stulpelyje nėra atitikmenų", "B stulpelyje yra atitikmenų"

Daugiau jokių veiksmų nereikia. Apskaičiavę rezultatą pagal šią formulę, gauname jei trečiojo funkcijos argumento reikšmę IF degtukai. Jei jų nėra, tada antrojo argumento turinys.

Kaip palyginti 2 stulpelius „Excel“, kad jie atitiktų ir paryškintų spalva

Kad būtų lengviau vizualiai atpažinti atitinkančius stulpelius, galite juos paryškinti spalva. Norėdami tai padaryti, turite naudoti funkciją „Sąlyginis formatavimas“. Pažiūrėkime praktiškai.

Atitikčių paieška ir paryškinimas pagal spalvą keliuose stulpeliuose

Norėdami nustatyti atitikmenis ir jas paryškinti, pirmiausia turite pasirinkti duomenų diapazoną, kuriame bus atliekamas patikrinimas, o tada skirtuke „Pagrindinis“ atidarykite elementą „Sąlyginis formatavimas“. Ten kaip langelių pasirinkimo taisyklę pasirinkite „Dubliuoti reikšmes“.

Po to atsiras naujas dialogo langas, kuriame kairiajame iššokančiame sąraše randame parinktį „Pakartojimas“, o dešiniajame sąraše pasirenkame spalvą, kuri bus naudojama pasirinkimui. Spustelėjus mygtuką „Gerai“, bus pasirinktas visų panašumų ląstelių fonas. Tada tiesiog palyginkite stulpelius akimis.

Sutampančių linijų paieška ir paryškinimas

Patikrinimo, ar stygos sutampa, technika šiek tiek skiriasi. Pirmiausia turime sukurti papildomą stulpelį, kuriame naudosime kombinuotas reikšmes naudodami & operatorių. Norėdami tai padaryti, turite parašyti formos formulę: =A2&B2&C2&D2.

Mes pasirenkame stulpelį, kuris buvo sukurtas ir kuriame yra sujungtos reikšmės. Toliau atliekame tą pačią veiksmų seką, kuri aprašyta aukščiau stulpeliams. Pasikartojančios linijos bus paryškintos jūsų nurodyta spalva.

Matome, kad nėra nieko sunku ieškoti pakartojimų. „Excel“ yra visi tam reikalingi įrankiai. Prieš taikant visas šias žinias, svarbu tiesiog pasipraktikuoti.