Formula generică
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
rezumat
Pentru a evidenția celulele care conțin una dintre multele șiruri de text, puteți utiliza o formulă bazată pe funcțiile ISNUMBER și SEARCH, împreună cu funcția SUMPRODUCT. În exemplul prezentat, formatarea condițională aplicată B4: B11 se bazează pe această formulă:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Explicaţie
Lucrând din interior spre exterior, această parte a formulei caută în fiecare celulă din B4: B11 toate valorile din intervalul numit „lucruri”:
--ISNUMBER(SEARCH(things,B4)
Funcția SEARCH returnează poziția valorii dacă a fost găsită și eroarea #VALUE dacă nu a fost găsită. Pentru B4, rezultatele revin într-o matrice ca aceasta:
(8;#VALUE!;#VALUE!)
Funcția ISNUMBER schimbă toate rezultatele în TRUE sau FALSE:
(TRUE;FALSE;FALSE)
Dublu negativ în fața lui ISNUMBER forțează TRUE / FALSE la 1/0:
(1;0;0)
Funcția SUMPRODUCT adaugă apoi rezultatele, care sunt testate împotriva zero:
=SUMPRODUCT((1;0;0))>0
Orice rezultat diferit de zero înseamnă că a fost găsită cel puțin o valoare, astfel încât formula returnează TRUE, declanșând regula.
Ignorați lucrurile goale
Pentru a ignora celulele goale din intervalul numit „lucruri”, puteți încerca o formulă modificată ca aceasta:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Acest lucru funcționează atâta timp cât valorile textului pe care le testați nu conțin șirul „FALS”. Dacă da, puteți extinde funcția IF pentru a include o valoare, dacă este falsă, știind că nu apare în text (adică „zzzz”, „####” etc.)
Opțiune sensibilă la majuscule
SEARCH nu este sensibil la majuscule și minuscule. Pentru a verifica și cazul, înlocuiți SEARCH cu FIND astfel:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Prevenirea potrivirilor false
O problemă cu această abordare este că puteți vedea potriviri false cauzate de ș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 efectuează automat o potrivire „conține”.
Pentru o remediere parțială, 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 lângă punctuație. Acest lucru poate fi abordat parțial adăugând spațiu și în jurul textului original. Pentru a adăuga spațiu la începutul și la sfârșitul ambelor în același timp, puteți încerca o formulă de genul acesta:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Cu toate acestea, acest lucru nu va rezolva problemele cauzate de punctuație.
Dacă aveți nevoie de o soluție mai completă, 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.
