Genel formül
=FILTER(list1,COUNTIF(list2,list1))
Özet
İki listedeki eşleşen değerleri çıkarmak üzere verileri filtrelemek için FİLTRE işlevini ve EĞERSAY veya EĞERSAY işlevini kullanabilirsiniz. Gösterilen örnekte, F5'teki formül şöyledir:
=FILTER(list1,COUNTIF(list2,list1))
burada liste1 (B5: B16) ve liste2 (D5: D14) adlandırılmış aralıklardır. FILTER tarafından döndürülen sonuç yalnızca değerleri içerir Liste1 görünür list2 .
Not: FİLTRE, Excel 365'teki yeni bir dinamik dizi işlevidir.
Açıklama
Bu formül, EĞERSAY işleviyle oluşturulan mantıksal bir teste dayalı olarak verileri almak için FİLTRE işlevine dayanır:
=FILTER(list1,COUNTIF(list2,list1))
içten dışa doğru çalışarak, COUNTIF işlevi gerçek filtreyi oluşturmak için kullanılır:
COUNTIF(list2,list1)
List2'yi aralık bağımsız değişkeni olarak ve list1'i ölçüt bağımsız değişkeni olarak kullandığımıza dikkat edin . Başka bir deyişle, EĞERSAY'dan liste1'de liste2'de görünen tüm değerleri saymasını istiyoruz . EĞERSAY'a ölçütler için birden çok değer verdiğimizden, birden çok sonuç içeren bir dizi elde ederiz:
(1;1;0;1;0;1;0;0;1;0;1;1)
Dizinin list1'deki her değer için bir tane olmak üzere 12 sayı içerdiğini unutmayın . Sıfır değeri bir değer göstermektedir Liste1 bulunmayan list2 . Başka bir pozitif sayı olarak bir değeri belirtir Liste1 bulunan list2 . Bu dizi, include bağımsız değişkeni olarak doğrudan FILTER işlevine döndürülür:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filtre işlevi, diziyi filtre olarak kullanır. Herhangi bir değer Liste1 herhangi bir değer, pozitif bir sayı varlığını sürdürürken ile ilişkili ise sıfır ile ilişkili çıkarılır.
Sonuç, F5: F11 aralığına yayılan 7 eşleşen değerden oluşan bir dizidir. Veriler değişirse, FILTER yeniden hesaplar ve yeni verilere dayalı olarak eşleşen değerlerin yeni bir listesini döndürür.
Eşleşmeyen değerler
Olmayan eşleme değerlerini çıkarmak için Liste1 (yani değerler Liste1 görünmüyor list2 böyle formüle DEĞİL fonksiyonunu ekleyebilirsiniz):
=FILTER(list1,NOT(COUNTIF(list2,list1)))
DEĞİL işlevi, EĞERSAY'ın sonucunu etkili bir şekilde tersine çevirir - sıfır olmayan herhangi bir sayı YANLIŞ olur ve herhangi bir sıfır değeri DOĞRU olur. Sonuç olarak bir değerler listesi Liste1 mevcut değildir list2 .
INDEX ile
FİLTRE işlevi olmadan eşleşen değerleri çıkarmak için bir formül oluşturmak mümkündür, ancak formül daha karmaşıktır. Seçeneklerden biri, INDEX işlevini aşağıdaki gibi bir formülde kullanmaktır:
G5'teki formül aşağıya kopyalanır:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
Bu formülün özü , dizi bağımsız değişkeni olarak list1'i alan INDEX işlevidir . Kalan formülün çoğu, eşleşen değerler için kullanılacak satır numarasını hesaplar. Bu ifade, ilgili satır numaralarının bir listesini oluşturur:
ROW(list1)-ROW(INDEX(list1,1,1))+1
list1'deki satırları temsil eden 12 sayı dizisi döndürür :
(1;2;3;4;5;6;7;8;9;10;11;12)
Bunlar, EĞER işlevi ve yukarıdaki FİLTRE'de kullanılan mantıkla, EĞERSAY işlevine göre filtrelenir:
COUNTIF(list2,list1) // find matching values
Ortaya çıkan dizi şuna benzer:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Bu dizi, formül sütundan aşağı doğru kopyalanırken eşleşen bir sonraki satır numarasını almak için kullanılan KÜÇÜK işlevine doğrudan gönderilir. SMALL için k değeri (nth düşünün) genişleyen bir aralıkla hesaplanır:
ROWS($G$5:G5) // incrementing value for k
EĞERHATA işlevi, formül kopyalandığında ve eşleşen değerlerin bitmesi durumunda oluşan hataları yakalamak için kullanılır. Bu fikrin başka bir örneği için bu formüle bakın.
