Formula generica
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Sommario
Per tradurre le lettere in una stringa in numeri, è possibile utilizzare una formula di matrice basata sulle funzioni TEXTJOIN e VLOOKUP, con una tabella di traduzione definita per fornire le ricerche necessarie. Nell'esempio mostrato, la formula in C5 è:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
dove "xtable" è l'intervallo denominato E5: F10.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Fondamentalmente, questa formula utilizza un'operazione di matrice per generare una matrice di lettere dal testo di input, traduce ogni lettera individualmente in un numero, quindi unisce nuovamente tutti i numeri e restituisce l'output come stringa.
Per analizzare la stringa di input in un array o in lettere, utilizziamo le funzioni MID, ROW, LEN e INDIRECT come questa:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN restituisce la lunghezza del testo di input, che viene concatenato a "1:" e passato a INDIRETTO come testo. INDIRETTO valuta il testo come un riferimento di riga e la funzione RIGA restituisce un array di numeri a MID:
MID(B5,(1;2;3),1)
MID quindi estrae un carattere per ogni posizione iniziale e abbiamo:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
In sostanza, stiamo chiedendo CERCA.VERT per trovare una corrispondenza per "a", "b" e "c" allo stesso tempo. Per ragioni oscure, abbiamo bisogno di "dereferenziare" questo array in un modo particolare usando entrambe le funzioni T e IF. Dopo aver eseguito CERCA.VERT, abbiamo:
=TEXTJOIN("",1,(9;4;6))
e TEXTJOIN restituisce la stringa "946".
Emetti un numero
Per produrre un numero come risultato finale (invece di una stringa), aggiungi zero. L'operazione matematica costringerà la stringa in un numero.
Somma i numeri
Per sommare i numeri insieme invece di elencarli, puoi sostituire TEXTJOIN con SUM in questo modo:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Nota: la funzione TEXTJOIN è stata introdotta tramite il programma di abbonamento a Office 365 nel 2018.
