Формула Excel: Перетворити букву на цифру
Іноді на аркуші Excel потрібно перетворити a на 1, b на 2, c на 3 тощо. Однак перетворювати їх по черзі - марна трата часу. У цьому підручнику я представляю деякі формули Excel для вирішення цієї проблеми.
Перетворення однієї літери на число в кожному комірці
Щоб перетворити одну букву в число в кожній комірці Excel, ви можете використовувати формулу нижче.
Загальна формула:
| COLUMN(INDIRECT(cell_reference&1)) |
Аргументи
| Cell_reference: the cell that contains the letter you want to convert to number. |
Як працює ця формула
Візьміть приклад, щоб пояснити формулу. Тут ви перетворите букву в комірці С3 у число. Будь ласка, використовуйте цю формулу.
| =COLUMN(INDIRECT(C3&1)) |
прес
Що натомість? Створіть віртуальну версію себе у
ключ
Пояснення
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)) |
прес
Що натомість? Створіть віртуальну версію себе у
клавіша, усі перші літери кожного слова в комірці А1 витягуються.
Пояснення
РЯД (НЕПРАВИЛЬНИЙ ("1:" І ОБ'ЄМ (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}.
TEXTJOIN("",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, ви можете використовувати формулу, як показано нижче:
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |
прес
Що натомість? Створіть віртуальну версію себе у
ключ
Ця формула враховує регістр.
Зразок файлу
Клацніть, щоб завантажити зразок файлу
Відносні формули
- Видалити перші N символів з комірки
Тут представлена формула для видалення символів з лівого боку текстового рядка в Excel. - Видалити праворуч від тексту
Тут представлена формула для видалення символів з правої сторони текстового рядка в Excel. - Витяг останнього слова з текстового рядка в Excel
Цей підручник містить формулу для вилучення останнього слова із текстового рядка, що дає. - Витяг першого слова з текстового рядка в Excel
Цей підручник містить формулу для вилучення першого слова із текстового рядка, що дає.
Відносні функції
- Непряма функція
Перетворити текстовий рядок на дійсне посилання. - Функція COLUMN
Поверніть номер стовпця, у якому з’являється формула, або номер стовпця даного посилання. - Функція ROW
Повернути номер рядка посилання. - MID
Поверніть конкретні символи з середини текстового рядка.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
