Formuła ogólna
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Podsumowanie
Aby przetłumaczyć litery w ciągu na liczby, możesz użyć formuły tablicowej opartej na funkcjach TEXTJOIN i WYSZUKAJ.PIONOWO ze zdefiniowaną tabelą translacji, aby zapewnić niezbędne wyszukiwania. W przedstawionym przykładzie formuła w C5 to:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
gdzie „xtable” to nazwany zakres E5: F10.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje operację tablicową do wygenerowania tablicy liter z tekstu wejściowego, tłumaczy każdą literę z osobna na liczbę, a następnie ponownie łączy wszystkie liczby razem i zwraca wynik jako ciąg.
Aby przeanalizować ciąg wejściowy w tablicę lub litery, używamy funkcji MID, ROW, LEN i INDIRECT w następujący sposób:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN zwraca długość tekstu wejściowego, który jest łączony do „1:” i przekazywany jako tekst POŚREDNI. INDIRECT oblicza tekst jako odwołanie do wiersza, a funkcja WIERSZ zwraca tablicę liczb do MID:
MID(B5,(1;2;3),1)
Następnie MID wyodrębnia jeden znak dla każdej pozycji początkowej i mamy:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Zasadniczo prosimy WYSZUKAJ.PIONOWO o jednoczesne znalezienie dopasowania dla „a”, „b” i „c”. Z niejasnych powodów musimy „wyłuskać” tę tablicę w określony sposób, używając zarówno funkcji T, jak i funkcji JEŻELI. Po uruchomieniu funkcji WYSZUKAJ.PIONOWO mamy:
=TEXTJOIN("",1,(9;4;6))
a TEXTJOIN zwraca ciąg „946”.
Wpisz liczbę
Aby wyprowadzić liczbę jako wynik końcowy (zamiast ciągu), dodaj zero. Operacja matematyczna przekształci łańcuch w liczbę.
Sumuj liczby
Aby zsumować liczby zamiast wymieniać je, możesz zamienić TEXTJOIN na SUM w następujący sposób:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Uwaga: funkcja TEXTJOIN została wprowadzona za pośrednictwem programu subskrypcji Office 365 w 2018 roku.
