Formula Excel: verificare se una cella contiene tutti gli elementi di un elenco
Si supponga di avere in Excel un elenco di valori nella colonna E e di voler verificare se le celle della colonna B contengono tutti i valori presenti nella colonna E, restituendo VERO o FALSO come illustrato nell’immagine seguente. In questa guida viene fornita una formula per svolgere questa operazione.
Formula generica:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Argomenti
| Things: the list of values that you want to use to check if argument text contains. |
| Text: the cell or text string you want to check if containing argument things. |
Valore restituito:
Questa formula restituisce un valore logico: FALSO indica che la cella non contiene tutti gli elementi, mentre VERO significa che li include tutti.
Funzionamento della formula
Ad esempio, nella colonna B è presente un elenco di stringhe di testo di cui si desidera verificare se contengono tutti i valori compresi nell’intervallo E3:E5; utilizza la formula seguente.
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Premi Invio, poi trascina il quadratino di riempimento sulle celle da verificare. FALSO indica che la cella non contiene tutti i valori dell’intervallo E3:E5, mentre VERO significa che li include tutti.
Spiegazione
Funzione TROVA: restituisce la posizione del primo carattere di una stringa all’interno di un’altra. Se il testo viene trovato, la funzione restituisce la relativa posizione; in caso contrario, restituisce l’errore #VALORE!. Ad esempio, la formula SEARCH($E$3:$E$5,B4) cerca ciascun valore dell’intervallo E3:E5 nella cella B4 e restituisce le posizioni corrispondenti sotto forma di matrice: {1;7;12}
Funzione VAL.NUMERO: la funzione VAL.NUMERO verifica se un valore è un numero e restituisce VERO o FALSO. In questo caso, ISNUMBER(SEARCH($E$3:$E$5,B4)) restituisce una matrice come {true;true;true}, poiché la funzione TROVA ha individuato tre numeri.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) converte il valore VERO in 1 e FALSO in 0, trasformando così la matrice risultante in {1;1;1}.
Funzione MATR.SOMMA.PRODOTTO: moltiplica intervalli o matrici e ne restituisce la somma dei prodotti. La formula SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) restituisce 1+1+1=3.
La funzione CONTA.VALORI restituisce il numero di celle non vuote. COUNTA($E$3:$E$5) restituisce 3, quindi il risultato di SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) è uguale a 3; l’intera formula restituirà pertanto VERO o FALSO.
Osservazioni:
La formula =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) non esegue un controllo esatto. Consulta l’immagine seguente:
File di esempio
Fare clic per scaricare il file di esempio
Formule correlate
- Contare le celle uguali a
Con la funzione CONTA.SE puoi contare le celle che corrispondono a un valore specifico o che non lo contengono. - Contare le celle uguali a x o y
A volte potresti aver bisogno di contare il numero di celle che soddisfano uno dei due criteri: in questi casi, puoi usare la funzione CONTA.SE. - Contare le celle uguali a x e y
In questo articolo ti mostriamo la formula per contare le celle che soddisfano contemporaneamente due criteri. - Contare le celle diverse da
Questo articolo ti spiega come usare la funzione CONTA.SE per contare quante celle sono diverse da un valore specifico.
I Migliori Strumenti per la Produttività in Office
Kutools per Excel – Aiuta te a distinguerti dalla folla
Kutools per Excel Vanta oltre 300 funzionalità,assicurandoti che ciò di cui hai bisogno sia sempre a portata di clic...
Office Tab – Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Passa tra decine di documenti aperti in un secondo!
- Riduci centinaia di clic del mouse ogni giorno e dì addio al “mouse hand”.
- Aumenta la tua produttività del 50 % visualizzando e modificando più documenti contemporaneamente.
- Porta l’efficienza delle schede in Office (incluso Excel), proprio come in Chrome, Edge e Firefox.