Genel formül
=FILTER(list1,COUNTIF(list2,list1))
Özet
İki listeyi karşılaştırmak ve ortak değerleri çıkarmak için, FİLTRE ve EĞERSAY işlevlerine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, F5'teki formül şöyledir:
=FILTER(list1,COUNTIF(list2,list1))
burada liste1 (B5: B15) ve liste2 (D5: D13) adlandırılmış aralıklardır. Sonuç, her iki listede de görünen değerler F5: F11 aralığına yayılır.
Açıklama
FILTER işlevi, bir dizi değeri ve diziyi mantıksal bir ifade veya değere göre filtreleyen bir "include" bağımsız değişkenini kabul eder.
Bu durumda dizi, B5: B15'teki tüm değerleri içeren "liste1" adlı aralık olarak sağlanır. Dahil argüman iç içe içeride FİLTRE olan EĞERSAY fonksiyonu vasıtasıyla iletilir:
=FILTER(list1,COUNTIF(list2,list1))
EĞERSAY, aralık olarak liste2 ve ölçüt olarak liste1 ile ayarlanır . EĞERSAY on bir ölçüt değeri verdiğimiz için, EĞERSAY aşağıdaki gibi bir dizi içinde on bir sonuç döndürür:
(1;1;0;1;0;1;0;1;0;1;1)
1'lerin liste1'de görünen liste2'deki öğelere karşılık geldiğine dikkat edin.
Bu dizi, "include" bağımsız değişkeni olarak doğrudan FİLTRE işlevine gönderilir:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
FİLTRE işlevi, EĞERSAY tarafından sağlanan değerleri kullanarak liste1'i filtreler. Sıfırla ilişkili değerler kaldırılır; diğer değerler korunur.
Nihai sonuç, her iki listede de bulunan ve F5: F11 aralığına yayılan bir değerler dizisidir.
Genişletilmiş mantık
Yukarıdaki formülde, filtre olarak EĞERSAY’dan elde edilen ham sonuçları kullanıyoruz. Excel sıfır olmayan herhangi bir değeri DOĞRU ve sıfırı YANLIŞ olarak değerlendirdiği için bu işe yarar. EĞERSAY, 1'den büyük bir sayı döndürürse, filtre yine de düzgün çalışacaktır.
DOĞRU ve YANLIŞ sonuçlarını açıkça zorlamak için, şu şekilde "> 0" kullanabilirsiniz:
=FILTER(list1,COUNTIF(list2,list1)>0)
Yinelenenleri kaldırın veya sıralayın
Yinelenenleri kaldırmak için formülü EŞSİZ işlevinin içine yerleştirmeniz yeterlidir:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Sonuçları sıralamak için SIRALA işlevinde iç içe yerleştirin:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
List2'de eksik liste değerleri
List1'de list2'de eksik olan değerleri çıkarmak için mantığı şu şekilde tersine çevirebilirsiniz:
=FILTER(list1,COUNTIF(list2,list1)=0)
