Formula Excel: Verificare se una cella contiene uno tra diversi valori escludendo altri valori

Supponiamo che siano presenti due elenchi di valori e si voglia verificare se la cella B3 contiene uno dei valori dell’intervallo E3:E5, ma allo stesso tempo non ne contenga alcuno dell’intervallo F3:F4, come illustrato nello screenshot seguente. Questa guida fornisce una formula per gestire rapidamente questa operazione in Excel e ne spiega gli argomenti.

Formula generica:

Argomenti

Valore restituito:

La formula restituisce 1 o 0. Quando la cella contiene uno dei valori da includere e non contiene alcuno dei valori da escludere, restituisce 1; in caso contrario, restituisce 0. In questa formula, 1 e 0 vengono trattati come valori logici VERO e FALSO.

Funzionamento della formula

Supponendo di voler verificare se la cella B3 contiene uno dei valori presenti nell'intervallo E3:E5, escludendo contemporaneamente i valori dell'intervallo F3:F4, utilizzare la seguente formula

Premere Invio per ottenere il risultato della verifica.

Spiegazione

Parte 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) verifica se la cella contiene valori in E3:E5

Funzione TROVA: la funzione TROVA restituisce la posizione del primo carattere di una stringa di testo all'interno di un’altra. Se trova una corrispondenza, restituisce la posizione relativa; in caso contrario, genera l’errore #VALORE!. Ad esempio, la formula SEARCH($E$3:$E$5,B3) cerca ciascun valore dell’intervallo E3:E5 nella cella B3 e restituisce la posizione di ogni stringa trovata all’interno di B3. Il risultato è un array del tipo: {1;7;12}.

Funzione VAL.NUMERO: restituisce VERO quando una cella contiene un numero. Pertanto, ISNUMBER(SEARCH($E$3:$E$5,B3)) restituirà un array del tipo {VERO;VERO;VERO}, poiché la funzione TROVA ha individuato tre corrispondenze numeriche.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) converte il valore VERO in 1 e il valore FALSO in 0, trasformando così il risultato dell'array in {1;1;1}.

Funzione MATR.SOMMA.PRODOTTO: moltiplica intervalli o array e ne restituisce la somma dei prodotti. La formula SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) restituisce 1+1+1=3.

Infine, confrontando la formula a sinistra SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) con 0, non appena il risultato della formula a sinistra è maggiore di 0, il risultato sarà VERO; altrimenti, restituirà FALSO. In questo caso, restituisce VERO.

Parte 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) verifica se la cella non contiene valori in F3:F4

La formula TROVA($F$3:$F$4;B3) cerca ciascun valore dell'intervallo $F$3:$F$4 nella cella B3 e restituisce la posizione di ogni stringa di testo trovata. Il risultato sarà un array del tipo: {#VALORE!;#VALORE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) restituirà un risultato sotto forma di array {FALSO;FALSO}, poiché la funzione TROVA non ha trovato alcuna corrispondenza.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) converte il valore VERO in 1 e il valore FALSO in 0, trasformando così il risultato dell'array in {0;0}.

Funzione MATR.SOMMA.PRODOTTO: moltiplica intervalli o array e ne restituisce la somma dei prodotti. La formula SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) restituisce 0+0=0.

Infine, confrontando la formula a sinistra SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) con 0, non appena il risultato della formula a sinistra è uguale a 0, viene restituito VERO; in caso contrario, viene restituito FALSO. In questo caso, il risultato è VERO.

Parte 3: Moltiplicazione delle due formule

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

In questa formula, 1 e 0 sono interpretati come i valori logici VERO e FALSO.

File di esempio

Fare clic per scaricare il file di esempio

Formule correlate

• Verificare se una cella contiene un testo specifico
  Per verificare se una cella contiene alcuni testi nell'intervallo A ma non contiene i testi nell'intervallo B, è possibile utilizzare una formula matriciale che combina le funzioni CONTA, TROVA e E in Excel
• Verificare se una cella contiene uno tra diversi elementi
  Questa guida presenta una formula per verificare se una cella contiene uno qualsiasi tra diversi valori in Excel, spiegandone gli argomenti e il funzionamento.
• Verificare se una cella contiene uno degli elementi
  Supponiamo di avere in Excel un elenco di valori nella colonna E e di voler verificare se le celle della colonna B contengono almeno uno dei valori presenti in colonna E, restituendo VERO o FALSO.
• Verificare se una cella contiene un numero
  A volte potrebbe essere necessario verificare se una cella contiene valori numerici. Questa guida ti mostra una formula che restituisce VERO se la cella contiene un numero e FALSO in caso contrario.