Il problema
A volte può servire controllare se una serie di celle contengano almeno una parola tra quelle indicate in una lista.
Per prima cosa, assegnamo un nome alle celle che contengono le parole da ricercare, ovvero al range G5:G6. Chiameremo questo range “parole”.
Ricordo che per definire un nome simbolico per un range di celle, basta selezionare le celle interessate (nel nostro caso G5:G6), fare clic con il tasto destro e scegliere dal menu contestuale Definisci Nome.
Vediamo adesso la colonna “Risultato” alla cella C5, contiene la formula:
[highlight background=”#DDFF99″ color=”#000000″]=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA(parole;B5)))>0[/highlight]
Esaminiamo la formula che risolve il problema
Analizzeremo la formula passo passo, partendo dall’interno.
- [highlight background=”#DDFF99″ color=”#000000″]RICERCA(parole;B5)[/highlight]. Questa funzione ricerca se nella cella B5 sia presente una o più parole tra quelle del range “parole”, ovvero [G5:G6]. Se la parola ricercata è presente, la funzione restituisce la posizione (numero intero) del primo carattere della parola all’interno del testo. Se la parola non è presente, la funzione restituisce #VALORE!. Tradotto nel nostro caso, la funzione ricerca nella cella B5 prima la parola “casa” e poi la parola “ufficio”. La prima parola non viene trovata, la seconda viene trovata e il suo primo carattere si trova in posizione 3. Dunque la funzione RICERCA restituirà il vettore (a due valori poichè le parole ricercate sono due in questo caso): {#VALORE!;3}.
- [highlight background=”#DDFF99″ color=”#000000″]VAL.NUMERO(RICERCA(parole;B5))[/highlight]. La funzione VAL.NUMERO controlla se il suo argomento è un valore numerico e risponde con VERO o FALSO. Nel nostro caso la funzione andrà a verificare se i valori del vettore {#VALORE!;3} sono numerici: la risposta ovviamente sarà che il primo valore non è numerico mentre il secondo lo è. La sua risposta sarà quindi un vettore {FALSO;VERO}.
- [highlight background=”#DDFF99″ color=”#000000″]–VAL.NUMERO(RICERCA(parole,B5))[/highlight]. Il doppio segno meno prima della funzione VAL.NUMERO serve a convertire il risultato (vedi precedente passo) in 0,1 invece di FALSO,VERO. Il risultato di questo passaggio sarà dunque il vettore {0;1;}.
- [highlight background=”#DDFF99″ color=”#000000″]MATR.SOMMA.PRODOTTO(–VAL.NUMERO(RICERCA(parole;B5)))[/highlight]. La funzione MATR.SOMMA.PRODOTTO viene usata per eseguire prima la somma dei valori presenti in una serie di vettori (matrice) e poi il prodotto di queste somme. Nel nostro caso essa opera su un unico vettore, {0;1} come visto al passo precedente, e ne restituirà la somma. La funzione MATR.SOMMA.PRODOTTO può essere sostituita, ma solo in Office 365, dalla funzione SOMMA. Nel nostro caso dunque, sommando i valori del vettore {0;1}, il risultato sarà 1.
- [highlight background=”#DDFF99″ color=”#000000″]MATR.SOMMA.PRODOTTO(–VAL.NUMERO(RICERCA(parole;B5)))>0[/highlight]. Ultimo passaggio sarà quello di verificare se il risultato della somma eseguita al passo precedente sia maggiore di zero. Infatti, per come abbiamo concatenato le varie funzioni, il risultato del passo 4 sarà maggiore di zero solo se almeno una parola del range “parole” è stata trovata nel testo. Poichè al passo 4 abbiamo ottenuto 1, che è maggiore di zero, la funzione restituirà VERO. Infatti la parola “ufficio” è stata trovata nel testo.
Estendendo la formula alle restanti celle [C6:C10], andremo a effettuare la ricerca nei testi contenuti nelle celle [B6:B10].
Nota aggiuntiva: ricerca di 2 o più parole presenti simultaneamente nel testo
Nel nostro esempio, la funzione vista restituisce il valore VERO se una delle parole “casa” o “ufficio” è presente all’interno delle varie celle. Se invece volessimo verificare se entrambe le parole siano presenti nel testo, basterebbe modificare la formula in questo modo:
[highlight background=”#DDFF99″ color=”#000000″]=MATR.SOMMA.PRODOTTO(--VAL.NUMERO(RICERCA(parole;B5)))>1[/highlight]
Infatti, se entrambe le parole sono presenti nel testo, il risulato della somma – come visto al punto 4, sarà 2. Quindi nella formula modificata come sopra, chiederemo alla funzione di restituire il valore VERO solo se la somma è maggiore di 1, intercettando così solo il caso in cui entrambe le parole sono presenti nel testo.
