Formula generica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Sommario
Se vuoi testare una cella per vedere se contiene tutti gli elementi in un elenco, puoi farlo con una formula che utilizza la funzione RICERCA, con l'aiuto delle funzioni NUMERO, SUMPRODOTTO e CONTA.VALORI.
Contesto
Supponiamo che tu abbia un elenco di stringhe di testo nell'intervallo B5: B8 e desideri scoprire se queste celle contengono tutte le parole in un altro intervallo, E5: E7.
Potresti creare una formula che utilizzi istruzioni IF annidate per controllare ogni elemento, ma non si ridimensionerà bene se hai molte cose da cercare. Ogni volta che aggiungi una parola da cercare, dovrai aggiungere un altro IF nidificato e regolare le parentesi.
Soluzione
La soluzione è creare una formula che conti tutte le corrispondenze contemporaneamente. Una volta ottenuto, confrontiamo semplicemente il conteggio con il conteggio degli elementi che stiamo cercando. Se corrispondono, sappiamo che una cella contiene tutti gli elementi.
Nell'esempio mostrato, la formula che stiamo usando è:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Spiegazione
La chiave è questo snippet:
ISNUMBER(SEARCH(things,B5)
Questo si basa su un'altra formula (spiegata in dettaglio qui) che controlla semplicemente una cella per una singola sottostringa. Se la cella contiene la sottostringa, la formula restituisce VERO. In caso contrario, la formula restituisce FALSE.
Tuttavia, se forniamo alla stessa formula un elenco di cose (in questo caso, stiamo usando un intervallo denominato "cose", E5: E7) ci restituirà un elenco di valori VERO / FALSO, uno per ogni elemento in . Il risultato è un array simile a questo:
(VERO; VERO; VERO)
Dove ogni TRUE rappresenta un elemento trovato e ogni FALSE rappresenta un elemento non trovato.
Possiamo forzare i valori VERO / FALSO a 1 e 0 con un doppio negativo (-, chiamato anche doppio unario):
--ISNUMBER(SEARCH(things,B5))
che produce un array come questo:
(1; 1; 1)
Successivamente, elaboriamo questo array con SUMPRODUCT, che ci darà una somma totale. Se questa somma è uguale al numero di elementi nell'intervallo denominato "cose", sappiamo di aver trovato tutte le cose e possiamo restituire VERO. Il modo in cui lo facciamo è confrontare direttamente i due numeri. Otteniamo un conteggio delle celle non vuote in "cose" utilizzando COUNTA:
=COUNTA(things)
Con un elenco hardcoded
Non è necessario utilizzare un intervallo per l'elenco delle cose. Se stai cercando solo un numero limitato di cose, puoi utilizzare un elenco in formato array, chiamato costante di array. Ad esempio, se stai solo cercando i colori rosso, blu e verde, puoi utilizzare ("rosso", "blu", "verde") in questo modo:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
