Загальна формула
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Резюме
Для перекладу букв у рядку в цифри можна використовувати формулу масиву, засновану на функціях TEXTJOIN та VLOOKUP, із визначеною таблицею перекладів для забезпечення необхідних пошуків. У наведеному прикладі формула в С5 має вигляд:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
де "xtable" - названий діапазон E5: F10.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
По суті, ця формула використовує операцію масиву, щоб генерувати масив літер з вхідного тексту, переводить кожну букву окремо в число, потім знову об'єднує всі цифри і повертає результат у вигляді рядка.
Для синтаксичного аналізу вхідного рядка на масив або літери ми використовуємо функції MID, ROW, LEN та INDIRECT, такі як:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN повертає довжину вхідного тексту, який об'єднується в "1:" і передається в INDIRECT як текст. 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 році.
