Genel formül
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Özet
Bir hücrenin birçok dizeden birini içerip içermediğini test etmek için, ARAMA, ISAYI ve TOPLA işlevlerine dayalı bir formül kullanabilirsiniz. Aşağıya kopyalanan C5'teki formül:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Burada şeyler adlandırılmış aralık E5: E9'dur.
Açıklama
B5: B11'deki her bir hücreyi, adlandırılmış aralık şeylerinde (E5: E9) dizelerden herhangi birini içerip içermediğini görmek için test etmek istiyoruz . C5'te kullandığımız formül kopyalandı:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Bu formül, bir hücreyi tek bir alt dize için kontrol eden bir formüle (burada açıklanmıştır) dayanmaktadır. Hücre alt dizeyi içeriyorsa, formül DOĞRU değerini verir. Değilse, formül YANLIŞ değerini döndürür:
ISNUMBER(SEARCH(things,B5))
Ancak, bu durumda, SEARCH'e bir dizi listesi veriyoruz. Nesnelerde 5 dize olduğundan , SEARCH aşağıdaki gibi bir dizi içinde 5 sonuç verir:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
SEARCH bir dize bulduğunda, o dizenin konumunu döndürür. ARAMA bir dize bulamazsa, # DEĞER! hata. B5'te ilk kelime olarak "sarı" göründüğü için 1 görüyoruz. Diğer dizeler bulunamadığından, diğer 4 öğe hatalardır.
Bu dizi doğrudan ISNUMBER işlevine döndürülür. ISNUMBER daha sonra bir TRUE / FALSE değerleri dizisi döndürür:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Dizide bir tane bile DOĞRU varsa, bir hücrenin aradığımız dizelerden en az birini içerdiğini biliyoruz. DOĞRU olup olmadığını kontrol etmenin en kolay yolu, tüm değerleri birbirine eklemektir. Bunu SUMPRODUCT ile yapabiliriz, ancak önce DOĞRU / YANLIŞ değerlerini şu şekilde çift negatif (-) 1 ve 0'lara zorlamamız gerekir:
--ISNUMBER(SEARCH(things,B5))
Bu, yalnızca 1'ler ve 0'ları içeren yeni bir dizi verir:
(1;0;0;0;0)
doğrudan SUMPRODUCT'e gönderilir:
=SUMPRODUCT((1;0;0;0;0))
SUMPRODUCT, işlenecek tek bir diziyle, dizideki öğeleri ekler ve bir sonuç döndürür. Sıfır olmayan herhangi bir sonuç bir "isabet" olduğumuz anlamına gelir, bu nedenle DOĞRU veya YANLIŞ sonucunu zorlamak için> 0 ekleriz:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Sabit kodlu bir liste ile
Dizge listesi için aranacak bir aralık kullanmak gerekli değildir. Ayrıca bir dizi sabiti de kullanabilirsiniz. Örneğin, "kırmızı", "mavi" ve "yeşil" olup olmadığını kontrol etmek için aşağıdaki gibi bir formül kullanabilirsiniz:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Yanlış eşleşmeleri önleme
Bu yaklaşımla ilgili bir sorun, daha uzun kelimelerin içinde görünen alt dizelerden yanlış eşleşmeler alabilmenizdir. Örneğin, "dr" ile eşleşmeye çalışırsanız, bu kelimelerin içinde "dr" geçtiği için "Andrea", "içki", "kuru" vb. De bulabilirsiniz. Bunun nedeni, SEARCH'in otomatik olarak "içerir" eşleşmesi yapmasıdır.
Hızlı bir hack için, başka bir kelimede "dr" yi yakalamaktan kaçınmak için arama kelimelerinin (yani "dr" veya "dr") etrafına boşluk ekleyebilirsiniz. Ancak bir hücrede "dr" ilk veya son olarak görünürse veya noktalama işaretiyle görünürse bu başarısız olur.
Daha doğru bir çözüme ihtiyacınız varsa, bir seçenek, önce metni bir yardımcı sütunda normalleştirerek, baştaki ve sondaki boşlukları da eklemeye dikkat etmektir. Ardından ortaya çıkan metinde bu sayfadaki formülü kullanırsınız.
