Genel formül
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Özet
Listedeki tüm öğeleri içerip içermediğini görmek için bir hücreyi test etmek istiyorsanız, bunu ISNUMBER, SUMPRODUCT ve COUNTA işlevlerinin yardımıyla ARA işlevini kullanan bir formülle yapabilirsiniz.
Bağlam
Diyelim ki B5: B8 aralığında bir metin dizesi listeniz var ve bu hücrelerin başka bir aralıktaki (E5: E7) tüm kelimeleri içerip içermediğini öğrenmek istiyorsunuz.
Her bir öğeyi kontrol etmek için iç içe geçmiş EĞER ifadeleri kullanan bir formül oluşturabilirsiniz, ancak arayacak çok şeyiniz varsa bu iyi ölçeklenmeyecektir. Aramak için her kelime eklediğinizde, başka bir iç içe EĞER eklemeniz ve parantezleri ayarlamanız gerekir.
Çözüm
Çözüm, tüm eşleşmeleri tek seferde sayan bir formül oluşturmaktır. Bunu elde ettikten sonra, bu sayıyı aradığımız öğelerin sayısıyla karşılaştırırız. Eşleşirlerse, bir hücrenin tüm öğeleri içerdiğini biliyoruz.
Gösterilen örnekte kullandığımız formül şudur:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Açıklama
Anahtar şu pasajdır:
ISNUMBER(SEARCH(things,B5)
Bu, bir hücreyi tek bir alt dize için kontrol eden başka bir formüle (burada ayrıntılı olarak açıklanmıştır) dayanmaktadır. Hücre alt dizeyi içeriyorsa, formül DOĞRU değerini verir. Aksi takdirde formül YANLIŞ değerini döndürür.
Bununla birlikte, aynı formüle nesnelerin bir listesini verirsek (bu durumda, "şeyler" olarak adlandırılan adlandırılmış bir aralık kullanıyoruz, E5: E7), bize içindeki her öğe için bir DOĞRU / YANLIŞ değerleri listesi verecektir. . Sonuç şuna benzer bir dizidir:
(DOĞRU; DOĞRU; DOĞRU)
Her DOĞRU, bulunan bir öğeyi temsil ederken ve her YANLIŞ, bulunmayan bir öğeyi temsil eder.
DOĞRU / YANLIŞ değerlerini çift negatif (-, aynı zamanda çift tekli olarak da adlandırılır) 1 ve 0'lara zorlayabiliriz:
--ISNUMBER(SEARCH(things,B5))
bunun gibi bir dizi verir:
(1; 1; 1)
Daha sonra, bu diziyi SUMPRODUCT ile işleriz, bu da bize toplam bir toplam verir. Bu toplam, "şeyler" adlı aralıktaki öğelerin sayısına eşitse, her şeyi bulduğumuzu biliyoruz ve DOĞRU döndürebiliriz. Bunu yapmamızın yolu, iki sayıyı doğrudan karşılaştırmaktır. COUNTA kullanarak "şeyler" içinde boş olmayan hücre sayısı elde ederiz:
=COUNTA(things)
Sabit kodlu bir liste ile
Öğe listeniz için bir aralık kullanmanız gerekmez. Yalnızca az sayıda şey arıyorsanız, dizi sabiti adı verilen dizi biçiminde bir liste kullanabilirsiniz. Örneğin, yalnızca kırmızı, mavi ve yeşil renklerini arıyorsanız, ("kırmızı", "mavi", "yeşil") şu şekilde kullanabilirsiniz:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
