Generička formula
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Sažetak
Za prevođenje slova u nizu u brojeve možete upotrijebiti formulu niza koja se temelji na funkcijama TEXTJOIN i VLOOKUP, s definiranom tablicom prijevoda za pružanje potrebnih pretraživanja. U prikazanom primjeru formula u C5 je:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
gdje je "xtable" imenovani raspon E5: F10.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
U osnovi, ova formula koristi operaciju niza za generiranje niza slova iz ulaznog teksta, prevodi svako slovo pojedinačno u broj, zatim ponovno spaja sve brojeve i vraća izlaz kao niz.
Za raščlanjivanje ulaznog niza u niz ili slova koristimo funkcije MID, ROW, LEN i INDIRECT poput ove:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN vraća duljinu ulaznog teksta, koji je povezan s "1:" i predat je INDIRECT kao tekst. INDIRECT tekst procjenjuje kao referencu na red, a funkcija ROW vraća niz brojeva u MID:
MID(B5,(1;2;3),1)
Tada MID izdvaja po jedan znak za svaku početnu poziciju i imamo:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
U osnovi tražimo da VLOOKUP istodobno pronađe podudaranje za "a", "b" i "c". Iz nejasnih razloga, ovaj niz moramo "dereferencirati" na određeni način koristeći i funkcije T i IF. Nakon pokretanja VLOOKUP-a imamo:
=TEXTJOIN("",1,(9;4;6))
a TEXTJOIN vraća niz "946".
Iznesite broj
Da biste broj prikazali kao konačni rezultat (umjesto niza), dodajte nulu. Matematička će operacija prisiliti niz u broj.
Brojevi zbrojeva
Da biste zbrojili brojeve, umjesto da ih navedete, TEXTJOIN možete zamijeniti sa SUM ovako:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Napomena: funkcija TEXTJOIN uvedena je putem pretplatničkog programa Office 365 2018. godine.
