Формула Excel: преобразование букв в числа

Иногда на листе Excel нужно преобразовать a в 1, b в 2, c в 3 и так далее. Однако делать это вручную — пустая трата времени. В этом руководстве вы найдёте несколько эффективных формул Excel для быстрого решения этой задачи.

Преобразование одной буквы в число в каждой ячейке

Чтобы преобразовать одну букву в число в каждой ячейке Excel, воспользуйтесь приведённой ниже формулой.

Универсальная формула:

Аргументы

Как работает эта формула

Рассмотрим пример, чтобы наглядно продемонстрировать работу формулы. Допустим, вы хотите преобразовать букву из ячейки C3 в число — для этого используйте данную формулу.

Нажмите Enter.

Пояснение

INDIRECTФункция : эта функция преобразует текстовую строку в корректную ссылку. Например, INDIRECT(C3&1) можно представить следующим образом:
 INDIRECT(C3&1)
=INDIRECT(s&,1)
=INDIRECT(s1)

COLUMNФункция : функция COLUMN возвращает номер столбца указанной ссылки. Теперь формула выглядит так: COLUMN(s1), и она возвращает 19.

Примечания:

1. Эта формула возвращает номер столбца, поэтому если в ячейке содержится более одной буквы, результат будет таким, как показано в приведённом ниже примере.

2. Формула нечувствительна к регистру.

Преобразование нескольких букв в строку чисел в каждой ячейке

Если вы хотите преобразовать буквы в каждой ячейке в числа, как показано на рисунке ниже, вам потребуется сложная формула массива.

Универсальная формула:

Аргументы

Как работает эта формула

Предположим, вы хотите преобразовать буквы в диапазоне B3:B8 в числа. Выполните следующие действия:

Во-первых, создайте таблицу, в которой каждая буква будет сопоставлена соответствующему ей числу.

Затем выделите диапазон таблицы и перейдите в Поле имени (рядом со строкой формул), чтобы присвоить ему имя диапазона. В данном случае это имя — Rtable. См. рисунок:

Теперь используйте эту формулу:

Нажмите Enter, и из ячейки A1 будут извлечены первые буквы каждого слова.

Пояснение

ROW(INDIRECT("1:"&LEN(B3))): функция СТРОКА (ROW) возвращает номер строки; поскольку используется формула массива, она возвращает {1;2;3}. MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1): функция ПСТР (MID) возвращает символ в указанной позиции заданной строки. Эту формулу можно представить следующим образом:
=MID(B3,{1,2,3},1)
=MID(“acd”,{1,2,3},1)
= {«a»;«c»;«d»}

VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0): функция ВПР (VLOOKUP) выполняет поиск соответствующих значений для «a», «c» и «d» во втором столбце диапазона Rtable и возвращает {1;3;4}.

ТЕКСТОВОЙСОЮЗ(«»,1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): функция ТЕКСТОВОЙСОЮЗ (TEXTJOIN) — это новая функция в Excel 2019 и 365, которая объединяет все текстовые значения с указанным разделителем. В данном случае она возвращает 134.

Примечания:

Эта формула не учитывает регистр.

Примечание:

Предположим, что a = 1, b = 2, c = 3 и d = 4. Вы можете воспользоваться следующей формулой:

Нажмите Enter.

Эта формула учитывает регистр.

Пример файла

Нажмите, чтобы скачать пример файла

Связанные формулы

• Удаление первых N символов из ячейки
  Здесь приведена формула для удаления заданного количества символов с начала текстовой строки в Excel.
• Удаление символов с правой стороны текста
  Здесь приведена формула для удаления определённых символов с правой стороны текстовой строки в Excel.
• Извлечение последнего слова из текстовой строки в Excel
  В этом руководстве вы найдёте формулу, которая поможет извлечь последнее слово из любой заданной текстовой строки.
• Извлечение первого слова из текстовой строки в Excel
  В этом руководстве вы найдёте формулу для извлечения первого слова из любой текстовой строки.

Связанные функции

• Функция ДВССЫЛ (INDIRECT)
  Преобразует текстовую строку в действительную ссылку.
• Функция СТОЛБЕЦ (COLUMN)
  Возвращает номер столбца, в котором находится формула, или номер столбца указанной ссылки.
• Функция СТРОКА (ROW)
  Возвращает номер строки для указанной ссылки.
• Функция ПСТР (MID)
  Возвращает заданное количество символов из середины текстовой строки.