Формула Excel: преобразование буквы в число
Иногда вы хотите преобразовать a в 1, b в 2, c в 3 и так далее в листе Excel. Однако преобразовывать их одну за другой - пустая трата времени. В этом руководстве я представлю несколько формул Excel для решения этой проблемы.
Преобразование одной буквы в число в каждой ячейке
Чтобы преобразовать одну букву в число в каждой ячейке Excel, вы можете использовать формулу ниже.
Общая формула:
| COLUMN(INDIRECT(cell_reference&1)) |
аргументы
| Cell_reference: the cell that contains the letter you want to convert to number. |
Как работает эта формула
Возьмите пример, чтобы объяснить формулу. Здесь вы конвертируете букву в ячейке C3 в число. Пожалуйста, используйте эту формулу.
| =COLUMN(INDIRECT(C3&1)) |
Нажмите Enter .
объяснение
INDIRECT функция: эта функция используется для преобразования текстовой строки в действительную ссылку. Здесь INDIRECT (C3 & 1) вы можете увидеть это так:
НЕПРЯМОЙ (C3 & 1)
= КОСВЕННО (s & 1)
= КОСВЕННО (s1)
COLUMN функция: COLUMN функция возвращает номер столбца данной ссылки. Теперь формула КОЛОНКА (s1) который возвращает 19.
Примечания:
1. Эта формула возвращает номер столбца, что означает, что если в ячейке более одной буквы, она вернется, как показано ниже.
2. Формула не чувствительна к регистру.
Преобразование нескольких букв в строку чисел в каждой ячейке
Если вы хотите преобразовать буквы в каждой ячейке в числа, как показано на скриншоте ниже, вам понадобится сложная формула массива.
Общая формула:
| TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(cell_refer,ROW(INDIRECT("1:"&LEN(cell_refer))),1))),Rtable,2,0)) |
аргументы
| Cell_refer: the cell that you want to convert the letters to numbers. |
| Rtable:a table with two columns, one lists all letters, one lists the relative numbers. |
Как работает эта формула
Предположим, вы хотите преобразовать буквы в диапазоне B3: B8 в числа, сделайте следующее:
Во-первых, создайте таблицу, в которой перечислены все буквы и их относительные номера.
Затем выберите диапазон таблиц и перейдите к Поле имени (рядом с панелью формул), чтобы присвоить ему имя диапазона, здесь имя диапазона - Rtable. см. снимок экрана:
Теперь используйте эту формулу:
| =TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)) |
Нажмите Enter key, извлекаются все первые буквы каждого слова в ячейке A1.
объяснение
СТРОКА (КОСВЕННАЯ ("1:" & ДЛИН (B3))): СТРОКА функция возвращает номер строки, есть формула массива, затем возвращает {1; 2; 3}. СРЕДНЯЯ (B3, СТРОКА (КОСВЕННАЯ ("1:" & ДЛИН (B3))), 1): Функция MID возвращает символ в определенной позиции данной строки. Эту формулу можно увидеть ниже
=MID(B3,{1;2;3},1)
= MID («acd»; {1; 2; 3}; 1)
= {"а"; "в"; "г"}
VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0): Функция ВПР используется для поиска совпадающих чисел «a», «c», «d» во втором столбце диапазона Rtable. Затем он возвращается {1; 3; 4}.
TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): Функция ОБЪЕДИНЕНИЕ это новая функция в Excel 2019 и 365, она используется для объединения всех текстов с разделителем. Вот оно возвращается 134.
Примечания:
В этой формуле регистр не учитывается.
Примечание:
Предположим, что a = 1, b = 2, c = 3, d = 4, вы можете использовать следующую формулу:
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |
Нажмите Enter .
Эта формула чувствительна к регистру.
Файл примера
Нажмите, чтобы загрузить образец файла
Относительные формулы
- Удалить первые N символов из ячейки
Здесь вводится формула для удаления символов из левой части текстовой строки в Excel. - Удалить справа от текста
Здесь вводится формула для удаления символов с правой стороны текстовой строки в Excel. - Извлечь последнее слово из текстовой строки в Excel
В этом руководстве представлена формула для извлечения последнего слова из заданной текстовой строки. - Извлечь первое слово из текстовой строки в Excel
В этом руководстве представлена формула для извлечения первого слова из заданной текстовой строки.
Относительные функции
- КОСВЕННАЯ функция
Преобразуйте текстовую строку в действительную ссылку. - КОЛОНКА функция
Возвращает номер столбца, в котором появляется формула, или номер столбца данной ссылки. - СТРОКА функция
Вернуть номер строки ссылки. - MID
Вернуть определенные символы из середины текстовой строки.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
