Genel formül
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Özet
Birçok metin dizesinden birini içeren hücreleri vurgulamak için, SUMPRODUCT işleviyle birlikte ISNUMBER ve SEARCH işlevlerini temel alan bir formül kullanabilirsiniz. Gösterilen örnekte, B4: B11'e uygulanan koşullu biçimlendirme şu formüle dayanmaktadır:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Açıklama
İçten dışa doğru çalışarak, formülün bu bölümü B4: B11'deki her bir hücreyi "şeyler" adlı aralıktaki tüm değerler için arar:
--ISNUMBER(SEARCH(things,B4)
ARAMA işlevi, bulunursa değerin konumunu ve bulunamazsa # DEĞER hatasını verir. B4 için sonuçlar şöyle bir dizi halinde geri gelir:
(8;#VALUE!;#VALUE!)
ISNUMBER işlevi, tüm sonuçları DOĞRU veya YANLIŞ olarak değiştirir:
(TRUE;FALSE;FALSE)
ISNUMBER önündeki çift negatif DOĞRU / YANLIŞ'ı 1 / 0'a zorlar:
(1;0;0)
SUMPRODUCT işlevi, sıfıra karşı test edilen sonuçları toplar:
=SUMPRODUCT((1;0;0))>0
Sıfır olmayan herhangi bir sonuç, en az bir değerin bulunduğu anlamına gelir, bu nedenle formül DOĞRU döndürerek kuralı tetikler.
Boş şeyleri görmezden gelin
"Şeyler" adlı aralıktaki boş hücreleri yok saymak için, aşağıdaki gibi değiştirilmiş bir formülü deneyebilirsiniz:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Bu, test ettiğiniz metin değerleri "FALSE" dizesini içermediği sürece çalışır. Eğer öyleyse, metinde yanlış olduğu biliniyorsa, IF işlevini bir değer içerecek şekilde genişletebilirsiniz (ör. "Zzzz", "####" vb.)
Büyük / küçük harfe duyarlı seçenek
Arama büyük-küçük harfe duyarlı değil. Vakayı da kontrol etmek için, ARAMA yerine FIND yazın:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Yanlış eşleşmeleri önleme
Bu yaklaşımla ilgili bir sorun, daha uzun kelimelerin içinde görünen alt dizelerin neden olduğu yanlış eşleşmeler görebilmenizdir. Ö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 gerçekleştirmesidir.
Kısmi bir düzeltme için, başka bir sözcükte "dr" yi yakalamaktan kaçınmak için arama sözcüklerinin (yani "dr" veya "dr") çevresine boşluk ekleyebilirsiniz. Ancak hücrede "dr" ilk veya son olarak görünürse veya noktalama işaretinin yanında görünürse bu başarısız olur. Bu, orijinal metnin etrafına da boşluk eklenerek kısmen giderilebilir. Her ikisinin de başlangıcına ve sonuna aynı anda boşluk eklemek için, aşağıdaki gibi bir formül deneyebilirsiniz:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Ancak bu, noktalama işaretlerinden kaynaklanan sorunları çözmez.
Daha eksiksiz 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ı eklemeye dikkat etmektir. Ardından boşluklarla çevrili tam kelimeleri arayabilirsiniz.
