Generisk formel
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Sammendrag
For å oversette bokstaver i en streng til tall, kan du bruke en matriseformel basert på TEXTJOIN- og VLOOKUP-funksjonene, med en definert oversettelsestabell for å gi de nødvendige oppslagene. I eksemplet vist er formelen i C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
hvor "xtable" er det navngitte området E5: F10.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
Forklaring
I kjernen bruker denne formelen en matriseoperasjon for å generere en rekke bokstaver fra inngangsteksten, oversetter hver bokstav individuelt til et tall, og kobler deretter alle tallene sammen igjen og returnerer utdataene som en streng.
For å analysere inngangsstrengen i en matrise eller bokstaver, bruker vi MID, ROW, LEN og INDIRECT funksjoner slik:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returnerer lengden på inngangsteksten, som er sammenkoblet til "1:" og overlevert til INDIREKTE som tekst. INDIRECT evaluerer teksten som en radreferanse, og ROW-funksjonen returnerer en rekke tall til MID:
MID(B5,(1;2;3),1)
MID trekker deretter ut ett tegn for hver startposisjon, og vi har:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
I det vesentlige ber vi VLOOKUP om å finne en kamp for "a", "b" og "c" samtidig. Av uklare grunner trenger vi å "dereere" denne matrisen på en bestemt måte ved å bruke både T- og IF-funksjonene. Etter at VLOOKUP har kjørt, har vi:
=TEXTJOIN("",1,(9;4;6))
og TEXTJOIN returnerer strengen "946".
Skriv ut et tall
Hvis du vil sende et tall som det endelige resultatet (i stedet for en streng), legger du til null. Matematikkoperasjonen vil tvinge strengen til et tall.
Sum tall
For å summere tallene i stedet for å liste dem, kan du erstatte TEXTJOIN med SUM slik:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Merk: TEXTJOIN-funksjonen ble introdusert via Office 365-abonnementsprogrammet i 2018.
