Generische Formel
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Zusammenfassung
Um Buchstaben in einer Zeichenfolge in Zahlen zu übersetzen, können Sie eine Array-Formel verwenden, die auf den Funktionen TEXTJOIN und VLOOKUP basiert und eine definierte Übersetzungstabelle enthält, um die erforderlichen Suchvorgänge bereitzustellen. Im gezeigten Beispiel lautet die Formel in C5:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
Dabei ist "xtable" der benannte Bereich E5: F10.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Im Kern verwendet diese Formel eine Array-Operation, um ein Array von Buchstaben aus dem Eingabetext zu generieren, übersetzt jeden Buchstaben einzeln in eine Zahl, verbindet dann alle Zahlen wieder und gibt die Ausgabe als Zeichenfolge zurück.
Um die Eingabezeichenfolge in ein Array oder Buchstaben zu analysieren, verwenden wir die Funktionen MID, ROW, LEN und INDIRECT wie folgt:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN gibt die Länge des Eingabetextes zurück, der mit "1:" verkettet und als Text an INDIRECT übergeben wird. INDIRECT wertet den Text als Zeilenreferenz aus und die ROW-Funktion gibt ein Array von Zahlen an MID zurück:
MID(B5,(1;2;3),1)
MID extrahiert dann an jeder Startposition ein Zeichen für und wir haben:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Im Wesentlichen bitten wir VLOOKUP, gleichzeitig eine Übereinstimmung für "a", "b" und "c" zu finden. Aus unklaren Gründen müssen wir dieses Array auf besondere Weise unter Verwendung der T- und IF-Funktionen "dereferenzieren". Nachdem VLOOKUP ausgeführt wurde, haben wir:
=TEXTJOIN("",1,(9;4;6))
und TEXTJOIN gibt die Zeichenfolge "946" zurück.
Geben Sie eine Nummer aus
Fügen Sie Null hinzu, um eine Zahl als Endergebnis (anstelle einer Zeichenfolge) auszugeben. Die mathematische Operation zwingt die Zeichenfolge zu einer Zahl.
Summenzahlen
Um die Zahlen zusammenzufassen, anstatt sie aufzulisten, können Sie TEXTJOIN wie folgt durch SUM ersetzen:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Hinweis: Die TEXTJOIN-Funktion wurde 2018 über das Office 365-Abonnementprogramm eingeführt.
