Формула на 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)) |
Натискане Въведете ключ.
Обяснение
INDIRECT функция: тази функция се използва за преобразуване на текстов низ във валидна препратка. Тук INDIRECT(C3&1) можете да го видите като тези:
ИНДИРЕКТНО (C3&1)
=INDIRECT(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)) |
Натискане Въведете ключ, всички първи букви на всяка дума в клетка 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}.
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
Този урок предоставя формула за извличане на първата дума от дадения текстов низ.
Относителни функции
- Функция INDIRECT
Преобразувайте текстов низ във валидна препратка. - Функция COLUMN
Връща номера на колоната, чиято формула се появява, или номера на колоната на дадената препратка. - Функция ROW
Върнете номер на ред на препратка. - MID
Връща специфичните знаци от средата на текстовия низ.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
