Formula generică
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
rezumat
Pentru a traduce literele dintr-un șir în cifre, puteți utiliza o formulă matrice bazată pe funcțiile TEXTJOIN și VLOOKUP, cu un tabel de traducere definit pentru a furniza căutările necesare. În exemplul prezentat, formula din C5 este:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
unde „xtable” este intervalul numit E5: F10.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
La bază, această formulă folosește o operațiune matrice pentru a genera o matrice de litere din textul de intrare, traduce fiecare literă individual într-un număr, apoi îmbină din nou toate numerele și returnează ieșirea ca șir.
Pentru a analiza șirul de intrare într-o matrice sau litere, folosim funcții MID, ROW, LEN și INDIRECT astfel:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returnează lungimea textului introdus, care este concatenat la „1:” și predat lui INDIRECT ca text. INDIRECT evaluează textul ca referință de rând, iar funcția ROW returnează o serie de numere la MID:
MID(B5,(1;2;3),1)
MID extrage apoi un caracter pentru fiecare poziție de pornire și avem:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
În esență, cerem VLOOKUP să găsească o potrivire pentru „a”, „b” și „c” în același timp. Din motive obscure, trebuie să „dereferențiem” această matrice într-un mod particular folosind atât funcțiile T, cât și funcțiile IF. După executarea VLOOKUP, avem:
=TEXTJOIN("",1,(9;4;6))
iar TEXTJOIN returnează șirul „946”.
Afișați un număr
Pentru a afișa un număr ca rezultat final (în loc de un șir), adăugați zero. Operația matematică va constrânge șirul într-un număr.
Suma numerelor
Pentru a însuma numerele în loc să le enumerați, puteți înlocui TEXTJOIN cu SUM astfel:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Notă: funcția TEXTJOIN a fost introdusă prin intermediul programului de abonament Office 365 în 2018.
