Obecný vzorec
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
souhrn
Chcete-li přeložit písmena v řetězci na čísla, můžete použít maticový vzorec založený na funkcích TEXTJOIN a VLOOKUP s definovanou překladovou tabulkou, která zajistí potřebné vyhledávání. V zobrazeném příkladu je vzorec v C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
kde „xtable“ je pojmenovaný rozsah E5: F10.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
V podstatě tento vzorec používá operaci pole ke generování pole písmen ze vstupního textu, každé písmeno jednotlivě přeloží na číslo, poté znovu spojí všechna čísla dohromady a vrátí výstup jako řetězec.
K analýze vstupního řetězce na pole nebo písmena používáme funkce MID, ROW, LEN a INDIRECT, jako je tato:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN vrací délku vstupního textu, který je zřetězen na "1:" a předán INDIRECT jako text. INDIRECT vyhodnotí text jako odkaz na řádek a funkce ROW vrátí pole čísel do MID:
MID(B5,(1;2;3),1)
MID poté extrahuje jeden znak na každé výchozí pozici a máme:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
V zásadě žádáme VLOOKUP, aby našel shodu pro „a“, „b“ a „c“ současně. Z obskurních důvodů musíme toto pole „dereferovat“ konkrétním způsobem pomocí funkcí T i IF. Po spuštění VLOOKUP máme:
=TEXTJOIN("",1,(9;4;6))
a TEXTJOIN vrací řetězec „946“.
Výstup čísla
Chcete-li vypsat číslo jako konečný výsledek (místo řetězce), přidejte nulu. Matematická operace donutí řetězec k číslu.
Součet čísel
Chcete-li součet čísel místo jejich vypsání, můžete nahradit TEXTJOIN SUM takto:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Poznámka: Funkce TEXTJOIN byla zavedena prostřednictvím předplatného programu Office 365 v roce 2018.
