Általános képlet
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Összegzés
A sok szöveges karakterlánc egyikét tartalmazó cellák kiemeléséhez használhatja az ISNUMBER és a SEARCH függvényeken alapuló képletet a SUMPRODUCT függvénnyel együtt. A bemutatott példában a B4: B11 feltételes formázása ezen a képleten alapul:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Magyarázat
Belülről kifelé haladva a képlet ezen része a B4: B11 minden cellájában megkeresi a "dolgok" nevű tartomány összes értékét:
--ISNUMBER(SEARCH(things,B4)
A SEARCH függvény visszaadja az érték pozícióját, ha megtalálta, és a #VALUE hibát, ha nem található. A B4 esetében az eredmények egy ilyen tömbben térnek vissza:
(8;#VALUE!;#VALUE!)
Az ISNUMBER függvény minden eredményt igazra vagy hamisra változtat:
(TRUE;FALSE;FALSE)
Az ISNUMBER előtti kettős negatív az IGAZ / HAMIS 1/0-ra kényszeríti:
(1;0;0)
Ezután a SUMPRODUCT függvény összeadja az eredményeket, amelyet nullával tesztelnek:
=SUMPRODUCT((1;0;0))>0
Bármely nem nulla eredmény azt jelenti, hogy legalább egy értéket találtak, így a képlet IGAZ értéket ad vissza, kiváltva a szabályt.
Hagyd figyelmen kívül az üres dolgokat
A "dolgok" nevű tartomány üres celláinak figyelmen kívül hagyásához kipróbálhat egy ilyen módosított képletet:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Ez addig működik, amíg a tesztelt szövegértékek nem tartalmazzák a "FALSE" karakterláncot. Ha igen, akkor kiterjesztheti az IF függvényt egy értékre, ha hamis, hogy a szövegben nem fordul elő (pl. "Zzzz", "####" stb.)
Kis- és nagybetű érzékeny lehetőség
A KERESÉS nem különbözteti meg a kis- és nagybetűket. A kis- és nagybetűk ellenőrzéséhez cserélje ki a SEARCH szót a FIND-re:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
A hamis egyezések megelőzése
Ennek a megközelítésnek az egyik problémája, hogy hamis egyezéseket láthat a hosszabb szavak belsejében megjelenő alsorok miatt. Például, ha megpróbálja megfelelni a "dr" szónak, akkor előfordulhat, hogy megtalálja az "Andrea", "ital", "száraz" stb. Elemeket is, mivel ezekben a szavakban a "dr" szerepel. Ez azért történik, mert a SEARCH automatikusan végrehajtja a "tartalmaz" egyezést.
Részleges javításhoz szóközt adhat a keresőszavak körül (pl. "Dr" vagy "dr"), hogy elkerülje a "dr" más szavak elkapását. De ez nem fog sikerülni, ha a "dr" egy vagy egy cellában jelenik meg először, vagy az írásjelek mellett jelenik meg. Ez részben megoldható az eredeti szöveg körül is szóköz hozzáadásával. Ha mindkettő elejéhez és végéhez egyszerre teret kíván adni, próbálkozzon egy ilyen képlettel:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Ez azonban nem oldja meg az írásjelek okozta problémákat.
Ha teljesebb megoldásra van szüksége, akkor az egyik lehetőség az, hogy először normalizálja a szöveget egy segítő oszlopban, ügyelve arra is, hogy egy vezető és záró helyet is hozzáadjon. Ezután egész szavakat kereshet szóközökkel körülvéve.
