Mündəricat
Klassik vəziyyət: birinə birləşdirilməli olan iki siyahınız var. Üstəlik, ilkin siyahılarda həm unikal elementlər, həm də uyğun olanlar ola bilər (həm siyahılar arasında, həm də içəridə), lakin çıxışda dublikatsız (təkrarlar) bir siyahı əldə etməlisiniz:
Ənənəvi olaraq belə bir ümumi problemi həll etməyin bir neçə yoluna baxaq - ibtidai "alından" daha mürəkkəb, lakin zərifə qədər.
Metod 1: Dublikatları silin
Siz problemi ən sadə şəkildə həll edə bilərsiniz - hər iki siyahının elementlərini əl ilə birinə köçürün və sonra aləti yaranan dəstdə tətbiq edin. Dublikatları silin tabdan Tarix (Məlumat - Dublikatları Sil):
Mənbə siyahılarındakı məlumatlar tez-tez dəyişirsə, əlbəttə ki, bu üsul işləməyəcək - hər dəyişiklikdən sonra bütün proseduru yenidən təkrarlamalı olacaqsınız.
Metod 1a. dönmə cədvəli
Bu üsul, əslində, əvvəlkinin məntiqi davamıdır. Siyahılar çox böyük deyilsə və onlarda elementlərin maksimum sayı əvvəlcədən məlumdursa (məsələn, 10-dan çox deyilsə), onda siz birbaşa bağlantılar vasitəsilə iki cədvəli birinə birləşdirə, sağda olanları olan bir sütun əlavə edə bilərsiniz və nəticə cədvəlinə əsasən xülasə cədvəli qurun:
Bildiyiniz kimi, pivot cədvəli təkrarlara məhəl qoymur, buna görə də çıxışda dublikatsız birləşmiş siyahı alacağıq. 1 ilə köməkçi sütun yalnız Excel ən azı iki sütundan ibarət xülasə cədvəlləri qura bildiyi üçün lazımdır.
Orijinal siyahılar dəyişdirildikdə, yeni məlumatlar birbaşa bağlantılar vasitəsilə birləşdirilmiş cədvələ gedəcək, lakin pivot cədvəli əl ilə yeniləməli olacaq (sağ klikləyin - Yeniləyin və Saxlayın). Tez yenidən hesablamaya ehtiyacınız yoxdursa, digər variantlardan istifadə etmək daha yaxşıdır.
Metod 2: Massiv düsturu
Problemi düsturlarla həll edə bilərsiniz. Bu halda, nəticələrin yenidən hesablanması və yenilənməsi orijinal siyahılarda dəyişikliklər edildikdən dərhal sonra avtomatik və dərhal baş verəcəkdir. Rahatlıq və qısalıq üçün siyahılarımıza ad verək. 1 siyahı и 2 siyahıistifadə Adı meneceri nişanı formula (Formullar — Ad Meneceri — Yaradın):
Ad verdikdən sonra bizə lazım olan düstur belə görünəcək:
İlk baxışdan ürpertici görünür, amma əslində hər şey o qədər də qorxulu deyil. İcazə verin, Alt+Enter düymələri kombinasiyasından istifadə edərək bu düsturu bir neçə sətirdə genişləndirim və etdiyimiz kimi boşluqlarla girintilər edək, məsələn burada:
Buradakı məntiq belədir:
- INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) düsturu ilk siyahıdan bütün unikal elementləri seçir. Onlar bitən kimi #N/A xətası verməyə başlayır:
- INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) düsturu eyni şəkildə ikinci siyahıdan unikal elementləri çıxarır.
- Bir-birinə daxil olan iki İFERROR funksiyası əvvəlcə siyahı-1-dən, sonra isə siyahı-2-dən bir-birinin ardınca unikal olanların çıxışını həyata keçirir.
Qeyd edək ki, bu massiv düsturudur, yəni yazdıqdan sonra onu adi olmayan xanaya daxil etmək lazımdır. Daxil edin, lakin klaviatura qısayolu ilə Ctrl+ÜstKrkt+Daxil edin və sonra kənar ilə uşaq xanalara köçürün (daşıyın).
Excel-in İngilis versiyasında bu düstur belə görünür:
=SƏHƏR(IFERROR(INDEX(Siyahı1, MATCH(0, COUNTIF($E$1:E1, Siyahı1), 0)), İNDEKS(Siyahı2, MATCH(0, COUNTIF($E$1:E1, Siyahı2), 0)) ), "")
Bu yanaşmanın mənfi tərəfi odur ki, mənbə cədvəllərində çoxlu sayda (bir neçə yüz və ya daha çox) element varsa, massiv düsturları faylla işi nəzərəçarpacaq dərəcədə yavaşladır.
Metod 3. Power Query
Əgər mənbə siyahılarınızda çoxlu sayda element varsa, məsələn, bir neçə yüz və ya minlərlə, o zaman yavaş massiv düsturu əvəzinə, kökündən fərqli bir yanaşmadan, yəni Power Query əlavə alətlərindən istifadə etmək daha yaxşıdır. Bu əlavə standart olaraq Excel 2016-da quraşdırılmışdır. Əgər sizdə Excel 2010 və ya 2013 varsa, onu ayrıca yükləyə və quraşdıra bilərsiniz (pulsuz).
Fəaliyyət alqoritmi belədir:
- Quraşdırılmış əlavənin ayrı bir sekmesini açın Güc sorğusu (Excel 2010-2013 varsa) və ya sadəcə nişana keçin Tarix (Excel 2016 varsa).
- İlk siyahını seçin və düyməni basın Cədvəldən/Aralıqdan (Rəsmi/Cədvəldən). Siyahımızdan "ağıllı masa" yaratmaq barədə soruşduqda razılaşırıq:
- Yüklənmiş məlumatları və sorğunun adını görə biləcəyiniz sorğu redaktoru pəncərəsi açılır Cədvəl 1 (İstəsəniz özünüzə dəyişə bilərsiniz).
- Cədvəl başlığına iki dəfə klikləyin (word 1 siyahı) və adını hər hansı başqasına dəyişdirin (məsələn Xalq). Dəqiq nəyi adlandırmaq vacib deyil, ancaq icad edilmiş adı xatırlamaq lazımdır, çünki. ikinci cədvəli idxal edərkən daha sonra yenidən istifadə edilməli olacaq. Gələcəkdə iki cədvəlin birləşdirilməsi yalnız onların sütun başlıqları uyğunlaşdıqda işləyəcək.
- Yuxarı sol küncdə açılan siyahını genişləndirin bağlayın və yükləyin və seçin Bağlayın və yükləyin... (Bağlayın və Yükləyin...):
- Növbəti dialoq qutusunda (bir az fərqli görünə bilər – narahat olmayın) seçin Sadəcə əlaqə yaradın (Yalnız əlaqə yaradın):
- İkinci siyahı üçün bütün proseduru (2-6-cı bəndlər) təkrar edirik. Sütun başlığının adını dəyişdirərkən əvvəlki sorğuda olduğu kimi eyni addan (İnsanlar) istifadə etmək vacibdir.
- Nişandakı Excel pəncərəsində Tarix və ya tabda Güc sorğusu Seçmək Məlumat əldə edin - Sorğuları birləşdirin - Əlavə edin (Məlumat əldə et - Sorğuları birləşdir - Əlavə et):
- Görünən informasiya qutusunda açılan siyahılardan sorğularımızı seçin:
- Nəticədə, iki siyahının bir-birinin altında birləşdiriləcəyi yeni bir sorğu alacağıq. Düymə ilə dublikatları silmək qalır Satırları silin - Dublikatları silin (Sətrləri Sil - Dublikatları Sil):
- Tamamlanmış sorğunun adı seçimlər panelinin sağ tərəfində dəyişdirilə bilər, ona sağlam bir ad verilə bilər (bu, əslində nəticə cədvəlinin adı olacaq) və hər şey əmrlə vərəqə yüklənə bilər. bağlayın və yükləyin (Bağla və Yüklə):
Gələcəkdə, orijinal siyahılara hər hansı bir dəyişiklik və ya əlavə ilə, nəticələr cədvəlini yeniləmək üçün sadəcə sağ klikləmək kifayət edəcəkdir.
- Power Query istifadə edərək müxtəlif fayllardan çoxlu cədvəlləri necə toplamaq olar
- Siyahıdan unikal elementlərin çıxarılması
- Uyğunluqlar və fərqlər üçün iki siyahını bir-biri ilə necə müqayisə etmək olar