Genel formül
=SUMPRODUCT(--(A1=things))>0
Özet
Bir hücreyi birkaç şeyden birine eşit olup olmadığını test etmek istiyorsanız, bunu SUMPRODUCT işlevini kullanan bir formülle yapabilirsiniz.
Bağlam
Diyelim ki B5: B11 aralığında bir metin dizesi listeniz var ve her bir hücreyi E5: E9 aralığındaki başka bir şey listesiyle test etmek istiyorsunuz. Başka bir deyişle, B5: B11'deki her hücre için bilmek istiyorsunuz: bu hücre E5: E9'daki şeylerden herhangi birine eşit mi?
İç içe geçmiş EĞER ifadelerine dayalı büyük bir formül oluşturmaya başlayabilirsiniz, ancak SUMPRODUCT tabanlı bir dizi formülü daha basit, daha temiz bir yaklaşımdır.
Çözüm
Çözüm, birden çok değeri test edecek ve DOĞRU / YANLIŞ değerlerin bir listesini döndürecek bir formül oluşturmaktır. Bunu elde ettikten sonra, bu listeyi (aslında bir dizi) SUMPRODUCT ile işleyebiliriz.
Kullandığımız formül şuna benzer:
=SUMPRODUCT(--(B5=things))>0
Açıklama
Anahtar şu pasajdır:
--(B5=things)
bu, B5'teki değeri, adlandırılmış "şeyler" aralığındaki her değerle karşılaştırır. B5'i bir diziyle karşılaştırdığımız için (yani, "şeyler", E5: E11 olarak adlandırılan aralık), sonuç aşağıdaki gibi bir DOĞRU / YANLIŞ değerler dizisi olacaktır:
(DOĞRU; YANLIŞ; YANLIŞ; YANLIŞ; YANLIŞ)
Dizide bir DOĞRU bile varsa, B5'in listedeki en az bir şeye eşit olduğunu biliyoruz, bu nedenle DOĞRU / YANLIŞ değerlerini 1 ve 0'lara zorlamak için çift negatif (-, aynı zamanda çift olarak da adlandırılır) tekli). Bu zorlamadan sonra şu var:
(1; 0; 0; 0; 0)
Şimdi sonucu, dizideki öğeleri toplayacak SUMPRODUCT ile işliyoruz. Sıfır olmayan bir sonuç alırsak, en az bir eşleşmemiz olur, bu nedenle DOĞRU veya YANLIŞ nihai sonucunu zorlamak için> 1 kullanırız.
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:
--(B5=("red","blue","green"))
Ekstra boşluklarla başa çıkmak
Test ettiğiniz hücreler fazladan boşluk içeriyorsa, düzgün eşleşmezler. Tüm fazladan boşluğu kaldırmak için formülü KIRP işlevini şu şekilde kullanacak şekilde değiştirebilirsiniz:
=SUMPRODUCT(--(TRIM(A1)=things))>0
