Excel-də iki siyahını necə müqayisə etmək olar

Excel effektiv məlumat emal proqramıdır. İnformasiyanın təhlili üsullarından biri də iki siyahının müqayisəsidir. Excel-də iki siyahını düzgün müqayisə etsəniz, bu prosesi təşkil etmək çox asan olacaq. Bu gün müzakirə olunacaq bəzi məqamları izləmək kifayətdir. Bu metodun praktiki tətbiqi tamamilə şəxsin və ya təşkilatın müəyyən bir andakı ehtiyaclarından asılıdır. Buna görə də bir neçə mümkün hal nəzərdən keçirilməlidir.

Excel-də iki siyahının müqayisəsi

Əlbəttə ki, iki siyahını əl ilə müqayisə edə bilərsiniz. Ancaq bu, çox vaxt aparacaq. Excel-in öz intellektual alət dəsti var ki, bu da sizə məlumatları nəinki tez müqayisə etməyə, həm də gözlərinizlə əldə etmək o qədər də asan olmayan məlumatları əldə etməyə imkan verəcək. Tutaq ki, A və B koordinatları olan iki sütunumuz var. Onlarda bəzi dəyərlər təkrarlanır.

Problemin formalaşdırılması

Beləliklə, bu sütunları müqayisə etməliyik. İki sənədin müqayisəsi proseduru belədir:

1. Bu siyahıların hər birinin unikal xanaları eynidirsə və unikal xanaların ümumi sayı eynidirsə və xanalar eynidirsə, bu siyahıları eyni hesab etmək olar. Bu siyahıdakı dəyərlərin yığılma sırası o qədər də vacib deyil.
2. Unikal dəyərlərin özləri eyni olsa da, təkrarların sayı fərqli olarsa, siyahıların qismən üst-üstə düşməsi haqqında danışa bilərik. Buna görə də, belə siyahılar fərqli sayda elementlərdən ibarət ola bilər.
3. İki siyahının uyğun gəlməməsi faktı fərqli unikal dəyərlər dəsti ilə göstərilir.

Bütün bu üç şərt eyni zamanda bizim problemimizin şərtləridir.

Problemin həlli

Siyahıları müqayisə etməyi asanlaşdırmaq üçün iki dinamik diapazon yaradaq. Onların hər biri siyahıların hər birinə uyğun olacaq.

İki siyahını müqayisə etmək üçün aşağıdakıları edin:

1. Ayrı bir sütunda hər iki siyahıya xas olan unikal dəyərlərin siyahısını yaradırıq. Bunun üçün formuladan istifadə edirik: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Düsturun özü massiv düsturu kimi yazılmalıdır.
2. Hər bir unikal dəyərin verilənlər massivində neçə dəfə baş verdiyini müəyyən edək. Bunu etmək üçün düsturlar bunlardır: =COUNTIF(Siyahı1,D5) və =COUNTI(Siyahı2,D5).
3. Bu diapazonlara daxil olan bütün siyahılarda həm təkrarların sayı, həm də unikal dəyərlərin sayı eynidirsə, funksiya 0 dəyərini qaytarır. Bu, uyğunluğun XNUMX% olduğunu göstərir. Bu halda, bu siyahıların başlıqları yaşıl fon alacaq.
4. Bütün unikal məzmun hər iki siyahıdadırsa, düsturlarla qaytarılır =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) dəyəri sıfır olacaq. E1-də sıfır yoxdursa, lakin belə bir dəyər E2 və F2 xanalarındadırsa, bu halda diapazonlar uyğun olaraq tanınacaq, ancaq qismən. Bu halda, müvafiq siyahıların başlıqları narıncı rəngə çevriləcək.
5. Və yuxarıda təsvir edilən düsturlardan biri sıfırdan fərqli bir dəyər qaytararsa, siyahılar tamamilə uyğun gəlməyəcək.

Bu, düsturlardan istifadə edərək uyğunluqlar üçün sütunları necə təhlil etmək sualına cavabdır. Gördüyünüz kimi, funksiyalardan istifadə etməklə, ilk baxışdan riyaziyyatla əlaqəli olmayan demək olar ki, hər hansı bir işi həyata keçirə bilərsiniz.

Nümunə Test

Cədvəlin bizim versiyamızda yuxarıda təsvir edilən hər bir növün üç növ siyahısı var. Onun qismən və tamamilə uyğunluğu, eləcə də uyğunsuzluğu var.

Verilənləri müqayisə etmək üçün biz A5:B19 diapazonundan istifadə edirik ki, biz bu siyahı cütlərini növbə ilə daxil edirik. Müqayisə nəticəsində nə olacağını, orijinal siyahıların rəngindən anlayacağıq. Əgər onlar tamamilə fərqlidirlərsə, o zaman qırmızı fon olacaq. Məlumatların bir hissəsi eynidirsə, sarıdır. Tam şəxsiyyət olduqda, müvafiq başlıqlar yaşıl olacaq. Nəticədən asılı olaraq rəngi necə etmək olar? Bu şərti formatlaşdırma tələb edir.

İki siyahıdakı fərqləri iki yolla tapmaq

Siyahıların sinxron olub-olmamasından asılı olaraq fərqləri tapmaq üçün daha iki metodu təsvir edək.

Seçim 1. Sinxron Siyahılar

Bu asan seçimdir. Tutaq ki, belə siyahılarımız var.

Dəyərlərin neçə dəfə yaxınlaşmadığını müəyyən etmək üçün düsturdan istifadə edə bilərsiniz: =MƏHSUL(—(A2:A20<>B2:B20)). Nəticə olaraq 0 aldıqsa, bu o deməkdir ki, iki siyahı eynidir.

Seçim 2: Qarışıq Siyahılar

Siyahılar onlarda olan obyektlərin sırasına görə eyni deyilsə, şərti formatlaşdırma kimi funksiyanı tətbiq etməli və dublikat dəyərləri rəngləndirməlisiniz. Və ya funksiyadan istifadə edin COUNTIF, ondan istifadə edərək bir siyahıdakı elementin ikincidə neçə dəfə baş verdiyini təyin edirik.

2 sütunu sətir-sətir necə müqayisə etmək olar

İki sütunu müqayisə edərkən, biz tez-tez müxtəlif sətirlərdə olan məlumatları müqayisə etməliyik. Bunun üçün operator bizə kömək edəcək IF. Bunun praktikada necə işlədiyinə nəzər salaq. Bunun üçün bir neçə illüstrativ vəziyyət təqdim edirik.

Misal. Bir sıradakı uyğunluqlar və fərqlər üçün 2 sütunu necə müqayisə etmək olar

Eyni cərgədə olan, lakin fərqli sütunlarda olan dəyərlərin eyni olub olmadığını təhlil etmək üçün funksiyanı yazırıq IF. Düstur məlumatların işlənməsinin nəticələrinin göstəriləcəyi köməkçi sütunda yerləşdirilən hər bir sıraya daxil edilir. Ancaq onu hər cərgədə yazmaq lazım deyil, sadəcə onu bu sütunun qalan xanalarına köçürün və ya avtomatik tamamlama markerindən istifadə edin.

Hər iki sütundakı dəyərlərin eyni olub olmadığını anlamaq üçün belə bir düstur yazmalıyıq: =ƏGƏR(A2=B2, “Uyğunluq”, “”). Bu funksiyanın məntiqi çox sadədir: A2 və B2 xanalarındakı dəyərləri müqayisə edir və əgər onlar eynidirsə, "Üst-üstə düşür" dəyərini göstərir. Məlumat fərqlidirsə, heç bir dəyər qaytarmır. Siz həmçinin xanaları yoxlaya bilərsiniz ki, onlar arasında uyğunluq olub-olmasın. Bu vəziyyətdə istifadə olunan düstur: =ƏGƏR(A2<>B2, “Uyğun deyil”, “”). Prinsip eynidir, əvvəlcə yoxlama aparılır. Hüceyrələrin meyara uyğun gəldiyi ortaya çıxarsa, "Uyğun deyil" dəyəri göstərilir.

Dəyərlər eyni olduqda “Uyğunluq” və fərqlidirsə, “Uyğun deyil” hər ikisini göstərmək üçün formula sahəsində aşağıdakı düsturdan istifadə etmək də mümkündür: =ƏGƏR(A2=B2; “Uyğundur”, “Uyğun deyil”). Bərabərlik operatoru əvəzinə bərabərsizlik operatorundan da istifadə edə bilərsiniz. Yalnız bu halda göstəriləcək dəyərlərin sırası bir qədər fərqli olacaq: =ƏGƏR(A2<>B2, “Uyğun deyil”, “Üst-üstə düşür”). Düsturun ilk versiyasını istifadə etdikdən sonra nəticə aşağıdakı kimi olacaq.

Düsturun bu dəyişməsi böyük hərflərə həssasdır. Buna görə də, bir sütundakı dəyərlər digərlərindən yalnız böyük hərflərlə yazıldığından fərqlənirsə, proqram bu fərqi görməyəcəkdir. Müqayisəni hərf-həssas etmək üçün meyarlardakı funksiyadan istifadə etməlisiniz EXACT. Qalan arqumentlər dəyişməz olaraq qalır: =ƏGƏR(DƏK(A2,B2), “Uyğunluq”, “Unikal”).

Bir sıra uyğunluqlar üçün birdən çox sütunu necə müqayisə etmək olar

Siyahılardakı dəyərləri bütün meyarlar dəstinə uyğun olaraq təhlil etmək mümkündür:

1. Hər yerdə eyni dəyərlərə malik olan cərgələri tapın.
2. Yalnız iki siyahıda uyğunluqların olduğu sətirləri tapın.

Bu halların hər birində necə davam edəcəyinə dair bir neçə nümunəyə baxaq.

Misal. Cədvəlin bir neçə sütununda bir cərgədə uyğunluğu necə tapmaq olar

Tutaq ki, bizə lazım olan məlumatları ehtiva edən bir sıra sütunlarımız var. Dəyərlərin eyni olduğu cərgələri müəyyən etmək vəzifəsi ilə qarşılaşırıq. Bunu etmək üçün aşağıdakı düsturdan istifadə etməlisiniz: =ƏGƏR(AND(A2=B2,A2=C2), “uyğundur”, ” “).

Cədvəldə çoxlu sütun varsa, onu funksiya ilə birlikdə istifadə etmək kifayətdir IF operator COUNTIF: =ƏGƏR(COUNTIF($A2:$C2,$A2)=3;”uyğunluq”;” “). Bu düsturda istifadə olunan rəqəm yoxlamaq üçün sütunların sayını göstərir. Əgər fərqlidirsə, vəziyyətinizə uyğun olan qədər yazmalısınız.

Misal. Cədvəlin istənilən 2 sütununda bir cərgədə uyğunluğu necə tapmaq olar

Deyək ki, bir sətirdəki dəyərlərin cədvəldəkilərdən iki sütunda uyğun olub-olmadığını yoxlamaq lazımdır. Bunun üçün funksiyanı şərt kimi istifadə etməlisiniz OR, burada növbə ilə sütunların hər birinin digərinə bərabərliyini yazın. Budur bir nümunə.

Bu düsturdan istifadə edirik: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Cədvəldə çoxlu sütunların olduğu bir vəziyyət ola bilər. Bu halda, formula böyük olacaq və bütün lazımi birləşmələri seçmək çox vaxt apara bilər. Bu problemi həll etmək üçün funksiyadan istifadə etməlisiniz COUNTIF: =ƏGƏR(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unikal sətir”; “Unikal sətir deyil”)

Ümumilikdə iki funksiyamızın olduğunu görürük COUNTIF. Birincisi ilə növbə ilə neçə sütunun A2 ilə oxşarlığını müəyyənləşdiririk, ikincisi ilə isə B2 dəyəri ilə oxşarlıqların sayını yoxlayırıq. Bu düsturla hesablama nəticəsində sıfır qiymət alırıqsa, bu, bu sütundakı bütün sətirlərin unikal olduğunu, daha çox olduqda oxşarlıqların olduğunu göstərir. Buna görə də, əgər iki düsturla hesablamaq və yekun nəticələri toplamaq nəticəsində sıfır qiymət alırıqsa, o zaman “Unikal sətir” mətn dəyəri qaytarılır, bu rəqəm daha böyükdürsə, bu sətir unikal olmadığı yazılır.

Uyğunluqlar üçün Excel-də 2 sütunu necə müqayisə etmək olar

İndi bir nümunə götürək. Tutaq ki, iki sütunlu bir cədvəlimiz var. Onların uyğun olub olmadığını yoxlamaq lazımdır. Bunu etmək üçün, funksiyanın da istifadə ediləcəyi düsturu tətbiq etməlisiniz IF, və operator COUNTIF: =ƏGƏR(COUNTIF($B:$B,$A5)=0, “B sütununda uyğunluq yoxdur”, “B sütununda uyğunluqlar var”)

Əlavə hərəkət tələb olunmur. Nəticəni bu düsturla hesabladıqdan sonra funksiyanın üçüncü arqumentinin qiymətini alırıq IF matçlar. Heç biri yoxdursa, ikinci arqumentin məzmunu.

Uyğunluqlar üçün Excel-də 2 sütunu necə müqayisə etmək və rənglə vurğulamaq olar

Uyğun sütunları vizual olaraq müəyyən etməyi asanlaşdırmaq üçün onları rənglə vurğulaya bilərsiniz. Bunu etmək üçün "Şərti Formatlaşdırma" funksiyasından istifadə etməlisiniz. Gəlin praktikada baxaq.

Çox sütunda rəngə görə uyğunluqların tapılması və vurğulanması

Uyğunluqları müəyyən etmək və onları vurğulamaq üçün əvvəlcə yoxlamanın aparılacağı məlumat diapazonunu seçməlisiniz və sonra "Ev" sekmesinde "Şərti Formatlaşdırma" maddəsini açmalısınız. Orada hüceyrə seçim qaydası olaraq "Dublikat dəyərlər" seçin.

Bundan sonra, sol pop-up siyahıda "Təkrarlanan" seçimini tapdığımız yeni bir informasiya qutusu görünəcək və sağ siyahıda seçim üçün istifadə ediləcək rəngi seçirik. "OK" düyməsini basdıqdan sonra oxşarlığı olan bütün xanaların fonu seçiləcək. Sonra sütunları gözlə müqayisə edin.

Uyğun xətlərin tapılması və vurğulanması

Sətirlərin uyğun olub olmadığını yoxlamaq üçün texnika bir qədər fərqlidir. Əvvəlcə əlavə bir sütun yaratmalıyıq və orada & operatorundan istifadə edərək birləşdirilmiş dəyərlərdən istifadə edəcəyik. Bunu etmək üçün formanın düsturunu yazmalısınız: =A2&B2&C2&D2.

Yaradılmış və birləşdirilmiş dəyərləri ehtiva edən sütunu seçirik. Sonra, sütunlar üçün yuxarıda təsvir edilən eyni hərəkət ardıcıllığını yerinə yetiririk. Dublikat xətlər qeyd etdiyiniz rəngdə vurğulanacaq.

Görürük ki, təkrarları axtarmaqda çətin bir şey yoxdur. Excel bunun üçün bütün lazımi alətləri ehtiva edir. Bütün bu bilikləri tətbiq etməzdən əvvəl sadəcə məşq etmək vacibdir.