Общая формула
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Резюме
Чтобы преобразовать буквы в строке в числа, вы можете использовать формулу массива, основанную на функциях TEXTJOIN и VLOOKUP, с определенной таблицей преобразования для обеспечения необходимого поиска. В показанном примере формула C5 имеет следующий вид:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
где «xtable» - именованный диапазон E5: F10.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
По сути, эта формула использует операцию с массивом для создания массива букв из входного текста, переводит каждую букву по отдельности в число, затем снова объединяет все числа вместе и возвращает результат в виде строки.
Чтобы преобразовать входную строку в массив или буквы, мы используем функции MID, ROW, LEN и INDIRECT, например:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN возвращает длину введенного текста, который объединяется с "1:" и передается в КОСВЕННО как текст. INDIRECT оценивает текст как ссылку на строку, а функция ROW возвращает массив чисел в MID:
MID(B5,(1;2;3),1)
Затем MID извлекает по одному символу для каждой начальной позиции, и мы получаем:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
По сути, мы просим VLOOKUP найти совпадение для «a», «b» и «c» одновременно. По непонятным причинам нам нужно «разыменовать» этот массив определенным образом, используя функции T и IF. После запуска VLOOKUP у нас есть:
=TEXTJOIN("",1,(9;4;6))
а TEXTJOIN возвращает строку «946».
Вывести число
Чтобы вывести число в качестве окончательного результата (вместо строки), добавьте ноль. Математическая операция преобразует строку в число.
Сумма чисел
Чтобы суммировать числа вместе, а не перечислять их, вы можете заменить TEXTJOIN на SUM следующим образом:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Примечание: функция TEXTJOIN была введена в рамках программы подписки на Office 365 в 2018 году.
