Genel formül
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Özet
Bir "içerir" eşleşmesine sahip anahtar kelimeleri kullanarak metni kategorilere ayırmak için, ARA işlevini, INDEX ve MATCH yardımıyla kullanabilirsiniz. Gösterilen örnekte, C5'teki formül şöyledir:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
burada anahtar kelimeler E5: E14 olarak adlandırılır ve kategoriler F5: F14 olarak adlandırılır.
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
Açıklama
Özünde, bu bir INDEX ve MATCH işlevidir.
MATCH işlevinin içinde, adlandırılmış aralık anahtar sözcüklerinde (E5: E14) listelenen her anahtar sözcük için B sütunundaki hücreleri aramak için ARA işlevini kullanırız :
SEARCH(keywords,B5)
Birden çok öğe aradığımız için (adlandırılmış aralık anahtar kelimelerinde ), aşağıdaki gibi birden çok sonuç alacağız:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Değer! Hata, SEARCH metni bulamadığında ortaya çıkar. ARA bir eşleşme bulduğunda, hücrenin içindeki metnin konumuna karşılık gelen bir sayı döndürür.
Bu sonuçları daha kullanışlı bir biçime dönüştürmek için, tüm değerleri DOĞRU / YANLIŞ'a dönüştüren ISNUMBER işlevini kullanıyoruz:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Bu dizi, aranan_değer TRUE olarak ayarlanmış şekilde arama_dizisi olarak MATCH işlevine gider. KAÇINCI, dizide bulduğu ilk DOĞRU'nun (bu durumda 7) konumunu döndürür ve bu, INDEX işlevine satır_sayısı olarak sağlanır:
=INDEX(categories,7)
INDEX , nihai sonuç olarak "Otomatik" kategorilerindeki 7. öğeyi döndürür .
XLOOKUP ile
XLOOKUP işlevi ile bu formül bir şekilde basitleştirilebilir. XLOOKUP, yukarıdaki MATCH işlevinde kullanılan mantığın aynısını kullanabilir, bu nedenle eşdeğer formül:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP, dizideki ilk TRUE değerini bulur ve kategorilerden karşılık gelen değeri döndürür .
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 hücrede "dr" ilk veya son olarak görünürse veya noktalama işaretleri vb. İle 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 boşluklarla çevrili tam kelimeleri arayabilirsiniz.
