Yinelenenler olmadan iki listeyi birleştirme

Klasik bir durum: Birleştirilmesi gereken iki listeniz var. Ayrıca, ilk listelerde hem benzersiz öğeler hem de eşleşen öğeler olabilir (hem listeler arasında hem de içeride), ancak çıktıda kopyaları olmayan bir liste almanız gerekir (tekrarlar):

Geleneksel olarak, böyle yaygın bir sorunu çözmenin birkaç yoluna bakalım - ilkel “alnından” daha karmaşık ama zarif.

Yöntem 1: Yinelenenleri Kaldır

Sorunu en basit şekilde çözebilirsiniz – her iki listenin öğelerini manuel olarak tek bir listeye kopyalayın ve ardından aracı elde edilen kümeye uygulayın. Yinelemeleri Kaldır sekmeden Veri (Veri — Yinelenenleri Kaldır):

Tabii ki, kaynak listelerindeki veriler sıklıkla değişirse bu yöntem çalışmayacaktır - her değişiklikten sonra tüm prosedürü tekrarlamanız gerekecektir. 

Yöntem 1a. Pivot tablo

Bu yöntem aslında bir öncekinin mantıklı bir devamıdır. Listeler çok büyük değilse ve içindeki maksimum öğe sayısı önceden biliniyorsa (örneğin, 10'dan fazla değil), iki tabloyu doğrudan bağlantılarla bir araya getirebilir, sağdakilerle bir sütun ekleyebilir ve ortaya çıkan tabloya göre bir özet tablo oluşturun:

Bildiğiniz gibi, pivot tablo tekrarları yok sayar, bu nedenle çıktıda yinelemeler olmadan birleşik bir liste elde ederiz. 1'li yardımcı sütun, yalnızca Excel'in en az iki sütun içeren özet tablolar oluşturabilmesi nedeniyle gereklidir.

Orijinal listeler değiştirildiğinde, yeni veriler doğrudan bağlantılar yoluyla birleştirilmiş tabloya gidecek, ancak pivot tablonun manuel olarak güncellenmesi gerekecek (sağ tıklama - Güncelle ve Kaydet). Anında yeniden hesaplamaya ihtiyacınız yoksa, diğer seçenekleri kullanmak daha iyidir.

Yöntem 2: Dizi Formülü

Formüllerle sorunu çözebilirsiniz. Bu durumda, sonuçların yeniden hesaplanması ve güncellenmesi, orijinal listelerdeki değişikliklerden hemen sonra otomatik olarak ve anında gerçekleşir. Kolaylık ve kısalık olması için listelerimize isim verelim. Liste 1 и Liste 2kullanma İsim Yöneticisi çıkıntı formül (Formüller — Ad Yöneticisi — Oluştur):

Adlandırmadan sonra ihtiyacımız olan formül şöyle görünecektir:

İlk bakışta ürkütücü görünüyor, ama aslında her şey o kadar korkutucu değil. Alt+Enter tuş kombinasyonunu kullanarak bu formülü birkaç satırda genişletmeme izin verin ve burada yaptığımız gibi boşluklarla girintileyin:

Buradaki mantık şudur:

• INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) formülü ilk listeden tüm benzersiz öğeleri seçer.Bunlar biter bitmez #YOK hatası vermeye başlar:

  

• INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) formülü, ikinci listeden benzersiz öğeleri aynı şekilde çıkarır.
• Birbirine yuvalanmış iki EĞERHATA işlevi, önce liste-1'den benzersiz olanların çıktısını, ardından liste-2'den birbiri ardına çıktıyı uygular.

Bunun bir dizi formülü olduğuna dikkat edin, yani yazdıktan sonra sıradan olmayan bir hücreye girilmelidir. Keşfet, ancak bir klavye kısayoluyla Ctrl+vardiya+Keşfet ve sonra bir kenar boşluğu ile alt hücrelere kopyalayın (sürükleyin).

Excel'in İngilizce sürümünde bu formül şöyle görünür:

=EĞERHATA(EĞERHATA(INDEX(Liste1, KAÇINCI(0, EĞERSAY($E$1:E1, Liste1), 0)), INDEX(Liste2, KAÇINCI(0, EĞERSAY($E$1:E1, Liste2), 0)) ), “”) 

Bu yaklaşımın dezavantajı, kaynak tablolarda çok sayıda (birkaç yüz veya daha fazla) öğe varsa, dizi formüllerinin dosyayla çalışmayı belirgin şekilde yavaşlatmasıdır. 

Yöntem 3. Güç Sorgusu

Kaynak listeleriniz, örneğin birkaç yüz veya binlerce gibi çok sayıda öğeye sahipse, yavaş dizi formülü yerine, temelde farklı bir yaklaşım olan Power Query eklenti araçlarını kullanmak daha iyidir. Bu eklenti, varsayılan olarak Excel 2016'da yerleşiktir. Excel 2010 veya 2013'e sahipseniz, ayrı olarak indirip kurabilirsiniz (ücretsiz).

Eylemlerin algoritması aşağıdaki gibidir:

1. Yüklü eklentinin ayrı bir sekmesini açın Güç Sorgu (Excel 2010-2013'e sahipseniz) veya sadece sekmeye gidin Veri (Excel 2016'nız varsa).
2. İlk listeyi seçin ve düğmesine basın Tablodan/Aralıktan (Aralık/Tablodan). Listemizden bir "akıllı masa" oluşturmanız istendiğinde, aynı fikirdeyiz:

   

3. Yüklenen verileri ve sorgu adını görebileceğiniz sorgu düzenleyici penceresi açılır. Tablo 1 (isterseniz kendiniz değiştirebilirsiniz).
4. Tablo başlığına çift tıklayın (kelime Liste 1) ve başka bir adla yeniden adlandırın (örneğin İnsanlar). Tam olarak ne isimlendirileceği önemli değil, ancak icat edilen isim hatırlanmalıdır, çünkü. ikinci tablo içe aktarılırken daha sonra tekrar kullanılması gerekecektir. Gelecekte iki tabloyu birleştirmek, yalnızca sütun başlıkları eşleşirse işe yarar.
5. Sol üst köşedeki açılır listeyi genişletin kapat ve indir Ve seç Kapatın ve yükleyin… (Kapat&Şuraya Yükle…):

   

6. Bir sonraki iletişim kutusunda (biraz farklı görünebilir – paniğe kapılmayın), öğesini seçin. Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun):

   

7. İkinci liste için tüm prosedürü (2-6 arasındaki noktalar) tekrarlıyoruz. Bir sütun başlığını yeniden adlandırırken, önceki sorgudakiyle aynı adı (Kişiler) kullanmak önemlidir.
8. Sekmedeki Excel penceresinde Veri veya sekmesinde Güç Sorgu Klinik Veri Al – İstekleri Birleştir – Ekle (Veri Al — Sorguları Birleştir — Ekle):

   

9. Görüntülenen iletişim kutusunda, açılır listelerden isteklerimizi seçin:

   

10. Sonuç olarak, iki listenin alt alta bağlanacağı yeni bir sorgu elde edeceğiz. Düğmeyle kopyaları kaldırmak için kalır Satırları Sil – Yinelenenleri Kaldır (Satırları Sil - Yinelenenleri Sil):

    

11. Tamamlanan sorgu, seçenekler panelinin sağ tarafında yeniden adlandırılabilir, ona mantıklı bir ad verilir (aslında bu sonuç tablosunun adı olacaktır) ve her şey komutla sayfaya yüklenebilir. kapat ve indir (Kapat&Yükle):

    

Gelecekte, orijinal listelerde herhangi bir değişiklik veya ekleme yapıldığında, sonuç tablosunu güncellemek için sağ tıklamanız yeterli olacaktır.

• Power Query kullanarak farklı dosyalardan birden çok tablo nasıl toplanır
• Bir Listeden Benzersiz Öğeleri Çıkarma
• Eşleşmeler ve farklılıklar için iki liste birbiriyle nasıl karşılaştırılır?