Formula generica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sommario
Per testare una cella per vedere se contiene una delle tante stringhe, è possibile utilizzare una formula basata sulle funzioni SEARCH, ISNUMBER e SUMPRODUCT. La formula in C5, copiata in basso, è:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
dove le cose sono l'intervallo denominato E5: E9.
Spiegazione
Vogliamo testare ogni cella in B5: B11 per vedere se contiene una qualsiasi delle stringhe nell'intervallo denominato cose (E5: E9). La formula che stiamo usando in C5, copiata, è:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Questa formula si basa su una formula (spiegata qui) che controlla una cella per una singola sottostringa. Se la cella contiene la sottostringa, la formula restituisce VERO. In caso contrario, la formula restituisce FALSE:
ISNUMBER(SEARCH(things,B5))
Tuttavia, in questo caso, forniamo a SEARCH un elenco di stringhe. Poiché ci sono 5 stringhe nelle cose , SEARCH restituisce 5 risultati in un array come questo:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Quando SEARCH trova una stringa, restituisce la posizione di quella stringa. Se la RICERCA non trova una stringa, restituisce un #VALORE! errore. Poiché "giallo" appare come prima parola in Si5, vediamo un 1. Poiché le altre stringhe non vengono trovate, gli altri 4 elementi sono errori.
Questo array viene restituito direttamente alla funzione ISNUMBER. ISNUMBER restituisce quindi un array di valori TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Se abbiamo anche un TRUE nell'array, sappiamo che una cella contiene almeno una delle stringhe che stiamo cercando. Il modo più semplice per verificare TRUE è sommare tutti i valori insieme. Possiamo farlo con SUMPRODUCT, ma prima dobbiamo forzare i valori VERO / FALSO su 1 e 0 con un doppio negativo (-) come questo:
--ISNUMBER(SEARCH(things,B5))
Questo produce un nuovo array contenente solo 1 e 0:
(1;0;0;0;0)
consegnato direttamente a SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Con un solo array da elaborare, SUMPRODUCT aggiunge gli elementi nell'array e restituisce un risultato. Qualsiasi risultato diverso da zero significa che abbiamo un "hit", quindi aggiungiamo> 0 per forzare un risultato finale di VERO o FALSO:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Con un elenco hardcoded
Non è necessario utilizzare un intervallo per l'elenco di stringhe da cercare. È inoltre possibile utilizzare una costante di matrice. Ad esempio, per verificare la presenza di "rosso", "blu" e "verde", puoi utilizzare una formula come questa:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Prevenire le false corrispondenze
Un problema con questo approccio è che potresti ottenere false corrispondenze da sottostringhe che appaiono all'interno di parole più lunghe. Ad esempio, se provi ad abbinare "dr" potresti trovare anche "Andrea", "drink", "dry", ecc. Poiché "dr" appare all'interno di queste parole. Ciò accade perché la RICERCA esegue automaticamente una corrispondenza "contiene".
Per un trucco veloce, puoi aggiungere uno spazio intorno alle parole di ricerca (es. "Dr" o "dr") per evitare di cogliere "dr" in un'altra parola. Ma questo fallirà se "dr" appare per primo o per ultimo in una cella o appare con punteggiatura.
Se hai bisogno di una soluzione più accurata, un'opzione è normalizzare il testo prima in una colonna helper, avendo cura di aggiungere anche uno spazio iniziale e uno finale. Quindi usa la formula in questa pagina sul testo risultante.
