Všeobecný vzorec
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Zhrnutie
Ak chcete preložiť písmená v reťazci na čísla, môžete použiť maticový vzorec založený na funkciách TEXTJOIN a VLOOKUP s definovanou prekladovou tabuľkou na zabezpečenie potrebných vyhľadávaní. V zobrazenom príklade je vzorec v C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
kde „xtable“ je pomenovaný rozsah E5: F10.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Tento vzorec v jadre používa operáciu poľa na vygenerovanie poľa písmen zo vstupného textu, každé písmeno jednotlivo preloží na číslo, potom znova spojí všetky čísla dohromady a vráti výstup ako reťazec.
Na analýzu vstupného reťazca do poľa alebo písmen používame nasledujúce funkcie MID, ROW, LEN a INDIRECT:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
Funkcia LEN vráti dĺžku vstupného textu, ktorý je zreťazený na „1:“ a odovzdaný INDIRECT ako text. INDIRECT vyhodnotí text ako odkaz na riadok a funkcia ROW vráti pole čísel do MID:
MID(B5,(1;2;3),1)
MID potom extrahuje jeden znak pre každú začiatočnú pozíciu a máme:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
V podstate žiadame VLOOKUP, aby našiel zhodu pre „a“, „b“ a „c“ súčasne. Z nejasných dôvodov musíme toto pole „dereferovať“ konkrétnym spôsobom pomocou funkcií T aj IF. Po spustení VLOOKUP máme:
=TEXTJOIN("",1,(9;4;6))
a TEXTJOIN vráti reťazec „946“.
Zadajte číslo
Ak chcete vygenerovať číslo ako konečný výsledok (namiesto reťazca), pridajte nulu. Matematická operácia prinúti reťazec do čísla.
Súčet čísel
Ak chcete tieto čísla namiesto súčtu sčítať, môžete TEXTJOIN nahradiť SUM takto:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Poznámka: Funkcia TEXTJOIN bola zavedená prostredníctvom predplatného programu Office 365 v roku 2018.
