Formula generica
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Sommario
Per testare una cella per una delle tante stringhe, escludendone altre, è possibile utilizzare una formula basata sulle funzioni SEARCH, ISNUMBER e SUMPRODUCT. Nell'esempio mostrato la formula in C5 è:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
dove "include" è l'intervallo denominato E5: E9 e "exclude" è l'intervallo denominato G5: G6.
Spiegazione
Fondamentalmente, questa formula utilizza la funzione RICERCA per cercare più stringhe all'interno di una cella. All'interno del SUMPRODUCT a sinistra, SEARCH cerca tutte le stringhe nell'intervallo denominato "include".
Nel SUMPRODUCT a destra, SEARCH cerca tutte le stringhe nell'intervallo denominato "escludi".
In entrambe le parti della formula, RICERCA restituisce posizioni numeriche quando vengono trovate stringhe e errori in caso contrario. La funzione ISNUMBER converte i numeri in TRUE e gli errori in FALSE e il doppio negativo converte i valori TRUE FALSE in 1 e 0.
Il risultato a questo punto è simile a questo:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Poi:
=(1>0)*(0=0) =TRUE*TRUE =1
Nota: questa formula restituisce 1 o zero, che vengono gestiti come VERO e FALSO nelle formule, nella formattazione condizionale o nella convalida dei dati.
