#Excel – Come testare se una cella contiene uno dei valori di un elenco

Se volete testare una cella per vedere se contiene uno dei valori di un elenco, è possibile farlo con una formula che utilizzi la funzione RICERCA, assieme alle funzioni VAL.NUMERO e MATR.SOMMA.PRODOTTO.

Testare se una cella contiene un valore preso da un elenco

Contesto

Supponiamo che abbiate una lista di stringhe di testo nell’intervallo B3:B12, e che vogliate verificare ogni cella rispetto ad un altro elenco di cose nell’intervallo E3:E7. In altre parole, per ogni cella in B3:B12, volete sapere se questa contiene almeno una delle cose in E3:E7.

Potreste iniziare a costruire un grande formula basata su tante funzioni SE nidificate, ma non sarebbe affatto divertente, soprattutto se l’elenco delle cose che si desidera controllare è grande.

Soluzione

La soluzione è quella di creare una formula che possa testare alla ricerca di più valori e restituire un elenco di valori VERO/FALSO. Una volta ottenuto questo, siamo in grado di elaborare tale elenco (una matrice, in realtà) con MATR.SOMMA.PRODOTTO.

La formula che abbiamo usato assomiglia a questa:

=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA(elenco;B3)))>0

Come funziona questa formula

La chiave è nella porzione:

VAL.NUMERO(RICERCA(stringa;B3))

Questa formula controlla semplicemente una cella alla ricerca di una singola stringa. Se la cella contiene la stringa, la formula restituisce VERO. In caso contrario, la formula restituisce FALSO.

Tuttavia, se le diamo in pasto un elenco di cose (in questo caso, stiamo usando un intervallo denominato chiamato “elenco”, E3:E7) restituirà un elenco di valori VERO/FALSO. Il risultato del test sulla cella B3 è in realtà una matrice che assomiglia a questa:

{FALSO.FALSO.FALSO.FALSO.FALSO}

Si noti che se avessimo anche un solo VERO nella matrice, sapremmo che la cella contiene almeno una delle cose nell’elenco. Quindi, siamo in grado di forzare i valori VERO/FALSO trasformandoli in 1 e 0 con un doppio negativo (, chiamato anche doppio unario):

--VAL.NUMERO(RICERCA(elenco;B3))

che produce una matrice così:

{0.0.0.0.0}

Ora trattiamo il risultato con MATR.SOMMA.PRODOTTO, che sommerà l’intera matrice. Sappiamo che se otteniamo un risultato diverso da zero , avremmo un “hit”, in modo da utilizzare >0 per arrivare al risultato finale di VERO o FALSO.

Con una lista hard-coded

Non c’è alcun obbligo di utilizzare un intervallo per il vostro elenco di cose. Se siete alla ricerca solo di un piccolo numero di cose, è possibile utilizzare un elenco in formato matrice, che si chiama “costante di matrice”. Ad esempio, se state cercando solo i colori rosso, blu e verde, è possibile utilizzare {“rosso”. “blu”. “verde”} in questo modo:

=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA({"rosso"."blu"."verde"};B3)))>0

 

LE SCHEDE TECNICHE DELLE FUNZIONI UTILIZZATE
LEGGI:  #Excel - Come sommare se le celle contengono x oppure y

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti