Formula generica
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Sommario
Per classificare il testo utilizzando parole chiave con una corrispondenza "contiene", è possibile utilizzare la funzione RICERCA, con l'aiuto di INDICE e CONFRONTA. Nell'esempio mostrato, la formula in C5 è:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
dove le parole chiave è l'intervallo denominato E5: E14 e le categorie è l'intervallo denominato F5: F14.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Al centro, questa è una funzione INDICE e CONFRONTA.
All'interno della funzione CONFRONTA, utilizziamo la funzione RICERCA per cercare nelle celle nella colonna B ogni parola chiave elencata nelle parole chiave dell'intervallo denominato (E5: E14):
SEARCH(keywords,B5)
Poiché stiamo cercando più elementi (nelle parole chiave dell'intervallo denominato ), otterremo più risultati come questo:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Il valore! l'errore si verifica quando la RICERCA non riesce a trovare il testo. Quando la RICERCA trova una corrispondenza, restituisce un numero che corrisponde alla posizione del testo all'interno della cella.
Per modificare questi risultati in un formato più utilizzabile, utilizziamo la funzione ISNUMBER, che converte tutti i valori in TRUE / FALSE in questo modo:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Questo array va nella funzione MATCH come lookup_array, con lookup_value impostato come TRUE. CONFRONTA restituisce la posizione del primo VERO che trova nell'array (7 in questo caso) fornito alla funzione INDICE come row_num:
=INDEX(categories,7)
INDICE restituisce il settimo elemento nelle categorie , "Auto", come risultato finale.
Con XLOOKUP
Con la funzione XLOOKUP, questa formula può essere semplificata in qualche modo. XLOOKUP può utilizzare la stessa logica utilizzata all'interno della funzione CONFRONTA sopra, quindi la formula equivalente è:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP individua il primo TRUE nella matrice e restituisce il valore corrispondente dalle categorie .
Prevenire le false corrispondenze
Un problema con questo approccio è che potresti ottenere false corrispondenze da sottostringhe che appaiono all'interno di parole più lunghe. Ad esempio, se provi ad abbinare "dr" potresti trovare anche "Andrea", "drink", "dry", ecc. Poiché "dr" appare all'interno di queste parole. Ciò accade perché la RICERCA esegue automaticamente una corrispondenza "contiene".
Per un trucco veloce, puoi aggiungere uno spazio intorno alle parole di ricerca (es. "Dr" o "dr") per evitare di cogliere "dr" in un'altra parola. Ma questo fallirà se "dr" appare per primo o per ultimo in una cella, o appare con punteggiatura, ecc.
Se hai bisogno di una soluzione più accurata, un'opzione è normalizzare il testo prima in una colonna helper, avendo cura di aggiungere anche uno spazio iniziale e uno finale. Quindi puoi cercare parole intere circondate da spazi.
