Általános képlet
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Összegzés
Egy cella teszteléséhez, hogy kiderül-e, hogy a sok karaktersorozat egyike van-e, használhat egy képletet, amely a SEARCH, ISNUMBER és SUMPRODUCT függvényeken alapul. A C5 képlete, lemásolva:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
ahol a dolgok a megnevezett tartomány E5: E9.
Magyarázat
Minden B5: B11 cellát tesztelni akarunk, hogy lássuk, tartalmaz-e a megnevezett tartománybeli dolgokban (E5: E9) szereplő húrokat . A C5-ben használt képlet, amelyet lemásoltunk, a következő:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ez a képlet egy olyan képleten alapul (itt elmagyarázva), amely ellenőrzi, hogy egy cellában egyetlen alstring van-e. Ha a cella tartalmazza az alszöveget, a képlet IGAZ értéket ad vissza. Ha nem, akkor a képlet HAMIS:
ISNUMBER(SEARCH(things,B5))
Ebben az esetben azonban a SEARCH számára megadunk egy karakterlánc-listát. Mivel a dolgokban 5 karakterlánc van , a SEARCH 5 eredményt ad vissza egy ilyen tömbben:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Amikor a SEARCH talál egy karakterláncot, visszaadja a karakterlánc helyét. Ha a SEARCH nem talál karakterláncot, akkor #VALUE! hiba. Mivel a B5 első szavaként a "sárga" jelenik meg, egy 1-et látunk. Mivel a többi karakterlánc nem található, a másik 4 elem hiba.
Ezt a tömböt közvetlenül az ISNUMBER függvény adja vissza. Az ISNUMBER ezután visszaadja az IGAZ / HAMIS értékek tömbjét:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Ha csak egy IGAZ van a tömbben, akkor tudjuk, hogy egy cella legalább a keresett karakterláncokat tartalmazza. A TRUE ellenőrzésének legegyszerűbb módja az összes érték összeadása. Megtehetjük ezt a SUMPRODUCT segítségével, de először az IGAZ / HAMIS értékeket 1 és 0 értékekre kell kényszerítenünk kettős negatív (-) értékkel:
--ISNUMBER(SEARCH(things,B5))
Ez egy új tömböt eredményez, amely csak 1-et és 0-t tartalmaz:
(1;0;0;0;0)
közvetlenül a SUMPRODUCT-hez szállítva:
=SUMPRODUCT((1;0;0;0;0))
Csak egy tömb feldolgozásával a SUMPRODUCT hozzáadja a tömb elemeit, és eredményt ad vissza. Bármely nem nulla eredmény azt jelenti, hogy van "találatunk", ezért> 0 értéket adunk hozzá az IGAZ vagy HAMIS végeredmény kikényszerítéséhez:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Keményen kódolt listával
A keresendő karakterláncok listájához nem szükséges tartományt használni. Használhat egy tömbállandót is. Például a "piros", "kék" és "zöld" ellenőrzéséhez használjon egy következő képletet:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
A hamis egyezések megelőzése
Ennek a megközelítésnek az egyik problémája, hogy hamis egyezéseket kaphat a hosszabb szavak belsejében megjelenő alsorokból. 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 "tartalmaz" egyezést hajt végre.
A gyors feltörés érdekében helyet 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" jelenik meg először vagy utoljára egy cellában, vagy írásjelekkel jelenik meg.
Ha pontosabb 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 a kapott szövegen használja az ezen az oldalon található képletet.
