Формула Excel: Преобразование букв в числа
Иногда вам нужно преобразовать a в 1, b в 2, c в 3 и так далее в листе Excel. Однако преобразовывать их по одной — это трата времени. В этом руководстве я представляю несколько формул Excel для решения этой проблемы.
Преобразование одной буквы в число в каждой ячейке
Чтобы преобразовать одну букву в число в каждой ячейке Excel, вы можете использовать следующую формулу.
Общая формула:
СТОЛБЕЦ(ДВССЫЛ(ссылка_на_ячейку&1)) |
Аргументы
Ссылка_на_ячейку: ячейка, содержащая букву, которую вы хотите преобразовать в число. |
Как работает эта формула
Рассмотрим пример для объяснения формулы. Здесь вы преобразуете букву в ячейке C3 в число. Пожалуйста, используйте эту формулу.
=СТОЛБЕЦ(ДВССЫЛ(C3&1)) |
Нажмите Enter клавишу.
Объяснение
Функция ДВССЫЛЭта функция используется для преобразования текстовой строки в действительную ссылку. Здесь ДВССЫЛ(C3&1) можно рассматривать так:
ДВССЫЛ(C3&1)
=ДВССЫЛ(s&1)
=ДВССЫЛ(s1)
СТОЛБЕЦ функция: функция СТОЛБЕЦ возвращает номер столбца заданной ссылки. Теперь формула СТОЛБЕЦ(s1) возвращает 19.
Примечания:
1. Эта формула возвращает номер столбца, что означает, что если в ячейке больше одной буквы, она вернет результат как показано в примере ниже.
2. Формула нечувствительна к регистру.
Преобразование нескольких букв в строку чисел в каждой ячейке
Если вы хотите преобразовать буквы в каждой ячейке в числа, как показано на скриншоте ниже, вам понадобится сложная формула массива.
Общая формула:
СОЕДИНИТЬТЕКСТ("",1,ПРОСМОТРВ(ЕСЛИ(1,ПСТР(ссылка_на_ячейку,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ссылка_на_ячейку))),1)),ТаблицаR,2,0)) |
Аргументы
Ссылка_на_ячейку: ячейка, которую вы хотите преобразовать буквы в числа. |
ТаблицаR: таблица с двумя столбцами, один из которых содержит все буквы, а другой соответствующие числа. |
Как работает эта формула
Предположим, вы хотите преобразовать буквы в диапазоне B3:B8 в числа, пожалуйста, сделайте следующее:
Во-первых, создайте таблицу, чтобы перечислить все буквы и их соответствующие числа.
Затем выберите диапазон таблицы и перейдите в Имя поля (рядом со строкой формул), чтобы дать ему имя диапазона, здесь имя диапазона - ТаблицаR. Смотрите скриншот:
Теперь используйте эту формулу:
=СОЕДИНИТЬТЕКСТ("",1,ПРОСМОТРВ(ЕСЛИ(1,ПСТР(B3,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B3))),1)),ТаблицаR,2,0)) |
Нажмите Enter клавишу, все первые буквы каждого слова в ячейке A1 извлечены.
Объяснение
СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B3)))Функция СТРОКА возвращает номер строки, это формула массива, тогда она возвращает {1;2;3}. ПСТР(B3,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B3))),1)Функция ПСТР возвращает символ в определенной позиции данной строки. Эту формулу можно рассматривать так:
=ПСТР(B3,{1;2;3},1)
=ПСТР(“acd”,{1;2;3},1)
= {"a";"c";"d"}
ПРОСМОТРВ(ЕСЛИ(1,ПСТР(B3,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B3))),1)),ТаблицаR,2,0): функция ПРОСМОТРВ используется для поиска соответствующих чисел "a", "c","d" во втором столбце диапазона ТаблицаR. Затем она возвращает {1;3;4}.
СОЕДИНИТЬТЕКСТ("",1,ПРОСМОТРВ(ЕСЛИ(1,ПСТР(B3,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B3))),1)),ТаблицаR,2,0)): функция СОЕДИНИТЬТЕКСТ является новой функцией в Excel 2019 и 365, она используется для объединения всех текстов с разделителем. Здесь она возвращает 134.
Примечания:
Эта формула нечувствительна к регистру.
Примечание:
Предположим, a=1,b=2,c=3,d=4, вы можете использовать формулу как ниже:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B3,"a",1),"b",2),"c",3),"d",4) |
Нажмите Enter клавишу.
Эта формула чувствительна к регистру.
Пример файла
Нажмите, чтобы скачать пример файла
Связанные формулы
- Удалить первые N символов из ячейки
Здесь представлена формула для удаления символов с левой стороны текстовой строки в Excel. - Удалить справа от текста
Здесь представлена формула для удаления символов с правой стороны текстовой строки в Excel. - Извлечь последнее слово из текстовой строки в Excel
Это руководство предоставляет формулу для извлечения последнего слова из заданной текстовой строки. - Извлечь первое слово из текстовой строки в Excel
Это руководство предоставляет формулу для извлечения первого слова из заданной текстовой строки.
Связанные функции
- Функция ДВССЫЛ
Преобразовать текстовую строку в действительную ссылку. - Функция СТОЛБЕЦ
Возвращает номер столбца, где находится формула или номер столбца заданной ссылки. - Функция СТРОКА
Возвращает номер строки ссылки. - ПСТР
Возвращает определенные символы из середины текстовой строки.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.