Formula generică
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
rezumat
Pentru a clasifica textul folosind cuvinte cheie cu o potrivire „conține”, puteți utiliza funcția CĂUTARE, cu ajutorul INDEX și MATCH. În exemplul prezentat, formula din C5 este:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
unde cuvintele cheie este intervalul numit E5: E14, iar categoriile este intervalul numit F5: F14.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
La bază, aceasta este o funcție INDEX și MATCH.
In interiorul functiei MECIUL, vom folosi funcția de căutare pentru celulele din coloana B , căutați fiecare cuvânt cheie enumerate în intervalul numite cuvinte cheie (E5: E14):
SEARCH(keywords,B5)
Deoarece căutăm mai multe articole (în cuvintele cheie din intervalul denumit ), vom obține mai multe rezultate ca aceasta:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Valoarea! eroare apare când SEARCH nu poate găsi textul. Când SEARCH găsește o potrivire, returnează un număr care corespunde poziției textului din interiorul celulei.
Pentru a schimba aceste rezultate într-un format mai utilizabil, folosim funcția ISNUMBER, care convertește toate valorile în TRUE / FALSE astfel:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Această matrice intră în funcția MATCH ca lookup_array, cu lookup_value setată ca TRUE. MATCH returnează poziția primului TRUE pe care îl găsește în matrice (7 în acest caz) care este furnizat funcției INDEX ca număr_rând:
=INDEX(categories,7)
INDEX returnează cel de-al 7-lea element din categorii , „Auto”, ca rezultat final.
Cu XLOOKUP
Cu funcția XLOOKUP, această formulă poate fi simplificată oarecum. XLOOKUP poate utiliza aceeași logică utilizată în funcția MATCH de mai sus, astfel încât formula echivalentă este:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP localizează primul TRUE în matrice și returnează valoarea corespunzătoare din categorii .
Prevenirea potrivirilor false
O problemă cu această abordare este că puteți obține potriviri false de la șiruri care apar în cuvinte mai lungi. De exemplu, dacă încercați să potriviți „dr”, puteți găsi și „Andrea”, „bea”, „uscat” etc., deoarece „dr” apare în aceste cuvinte. Acest lucru se întâmplă deoarece SEARCH face automat o potrivire „conține”.
Pentru un hack rapid, puteți adăuga spațiu în jurul cuvintelor de căutare (adică „dr” sau „dr”) pentru a evita prinderea „dr” într-un alt cuvânt. Dar acest lucru va eșua dacă „dr” apare prima sau ultima într-o celulă sau apare cu punctuație etc.
Dacă aveți nevoie de o soluție mai precisă, o opțiune este să normalizați mai întâi textul într-o coloană de ajutor, având grijă să adăugați și un spațiu principal și final. Apoi, puteți căuta cuvinte întregi înconjurate de spații.
