#Excel – Come testare se una cella contiene tutti i 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, MATR.SOMMA.PRODOTTO e CONTA.VALORI.

Testare se una cella contiene tutti i valori di un elenco

 

Contesto

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

Potreste costruire una formula che utilizzi diverse funzioni SE nidificate per controllare la presenza di ogni elemento, ma questa soluzione non è comoda se avete tante cose da cercare. Ogni volta che aggiungete una parola da cercare è necessario aggiungere un altro SE nidificato e sistemare le parentesi.

Soluzione

La soluzione è quella di creare una formula che conti tutte le corrispondenze in una sola volta. Una volta che abbiamo ottenuto questo, dobbiamo semplicemente confrontare questo conteggio con il numero di articoli che stiamo cercando. Se corrispondono, sappiamo che la cella contiene tutti gli elementi.

Nell’esempio mostrato, la formula che usiamo in cella C3 è:

=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA(lista;B3)))=CONTA.VALORI(lista)

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 “lista”, E3:E5) restituirà un elenco di valori VERO/FALSO. Il risultato del test sulla cella B3 è in realtà una matrice che assomiglia a questa:

{VERO.VERO.VERO}

Dove ogni VERO rappresenta un elemento trovato, e ogni FALSO rappresenta un elemento non trovato.

LEGGI:  #Excel - Come trovare il valore più grande con criteri [Quick Tip]

Possiamo poi forzare i valori VERO/FALSO trasformandoli in 1 e 0 con un doppio negativo (, chiamato anche doppio unario):

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

che produce una matrice così:

{1.1.1}

Poi, abbiamo dato in pasto questa matrice a MATR.SOMMA.PRODOTTO, che ci darà la somma totale. Se questa somma è uguale al numero di elementi dell’intervallo denominato “lista”, sappiamo che abbiamo trovato tutte le cose e che possiamo restituire VERO. Riusciamo a fare questo confrontando direttamente i due numeri. Otteniamo il conteggio di tutte le celle non vuote in “lista” con CONTA.VALORI:

CONTA.VALORI(lista)

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 le cose giallo, verde e cane, è possibile utilizzare {“giallo”. “verde”. “cane”} in questo modo:

=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA({"giallo". "verde". "cane"};B3)))=CONTA.VALORI(lista)

 

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