В тази статия ще научим как да преобразуваме числено изчислени резултати в един изходен низ и след това да ги комбинираме с различен текст в Excel.
Сценарий:
Excel третира стойности към много формати като номер, дата, час, текст, процент или валута. Те могат да се променят един в друг. Excel преобразува числа в текст, за да използва числа като текст във формули като vlookup с числа и текст. Функцията Excel TEXT ви позволява да преобразувате номера в текст. Функцията TEXT в Excel се използва за преобразуване на числа в текст. Забавната част е, че можете да форматирате този номер, за да го покажете в желания формат. Например форматирайте дата yy-mm-dd във формат dd-mm-yy. Добавете знаци за валута преди число и много други.
ТЕКСТ формула в Excel
TEXT функцията е низова функция, която преобразува всяка стойност в даден формат. Резултатът може да изглежда, че е число, но е в текстов формат.
| =ТЕКСТ(cell_ref, text_format) |
cell_ref: стойност за преобразуване чрез препратка към клетка
Text_format: Формат за преобразуване
| Формат | Изходен формат |
| д | 7 |
| ddd | Четв |
| dddd | Четвъртък |
| м | 2 |
| ммм | Февр |
| Мммм | Февруари |
| ммммм | F |
| yy | 19 |
| yyyy | 2019 |
| м/д/г | 2/7/19 |
| mm/dd/yy | 02/07/19 |
| мм/дд/гггг | 02/07/2019 |
| ddd, mmm d | Чет, 7 февруари |
| mm/dd/yyyy h: mm AM/PM | 02/07/2019 14:15 |
| mm/dd/yyyy hh: mm: ss | 07/02/2019 14:15:00 |
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме няколко примера за преобразуване на стойности на дата в текстов формат или друг необходим формат.
Конвертирайте стойността в клетка A2.
Използвайте формулата в клетка С2
| = ТЕКСТ (A2, "0") |
“0”: връща общия текстов формат.
Натиснете Enter и копирайте формулата в останалите клетки, като използвате Ctrl + D
Както можете да видите, ние имаме числа като текстов изход, защото числото на значимостта винаги варира.
Тук имаме някои числа за преобразуване в текстов формат или друг необходим формат.
Използвайте формулата:
| = ТЕКСТ (A2, "d") |
Както можете да видите стойността в изходната клетка е в текстов формат.
Можете да използвате всеки Format_text и да вършите работата си в Excel без прекъсване
Понякога трябва да добавите 0 преди определена цифра от числа като телефонен номер или пинов номер. Използвайте тази текстова формула, за да направите това …
| =ТЕКСТ( 1234, “00000”) |
Ако имате N цифри от числото, тогава в аргумент за текстов формат напишете n+1 0s.
Напишете тази текстова формула, за да добавите валута.
| =ТЕКСТ( 1234, “$0”) |
Както можете да видите, ние имаме числа като текстов изход, защото числото на значимостта винаги варира.
VBA код за конвертиране на валута във формат TEXT
Този код е разделен на четири отделни функции. Основната функция е NUM_TO_IND_RUPEE_WORD. И други три функции GetHunderds (), GetTens () и GetDigits са помощни функции, които помагат на основната функция да оформи низа.
Код
| Функция NUM_TO_IND_RUPEE_WORD (ByVal MyNumber, Незадължителни incRupees като Boolean = True)
Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores ReDim място (9) като низ Място (2) = "Хиляда": Място (3) = "Милион" Място (4) = "Милиард": Място (5) = "Трилион"
„Низово представяне на сумата. MyNumber = Trim (Str (MyNumber))
„Позиция на десетичната запетая 0 ако няма. DecimalPlace = InStr (MyNumber, ".")
„Конвертирайте Paise и задайте MyNumber в размер на рупии. Ако DecimalPlace> 0 Тогава Paise = GetTens (вляво (в средата (MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim (Вляво (MyNumber, DecimalPlace - 1)) Край Ако
myCrores = MyNumber \ 10000000 myLakhs = (MyNumber - myCrores * 10000000) \ 100000 MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000 Брой = 1
Докато myCrores "" Temp = GetHundreds (Вдясно (myCrores, 3)) If Temp "" then Crores = Temp & Place (Count) & Crores Ако Len (myCrores)> 3 Тогава myCrores = Наляво (myCrores, Len (myCrores) - 3) Иначе myCrores = "" Край Ако Брой = Брой + 1 Цикъл Брой = 1
Докато myLakhs "" Temp = GetHundreds (Вдясно (myLakhs, 3)) If Temp "" then Lakhs = Temp & Place (Count) & Lakhs Ако Len (myLakhs)> 3 Тогава myLakhs = Наляво (myLakhs, Len (myLakhs) - 3) Иначе myLakhs = "" Край Ако Брой = Брой + 1 Цикъл Брой = 1
Докато MyNumber "" Temp = GetHundreds (Вдясно (MyNumber, 3)) If Temp "" then Rupees = Temp & Place (Count) & Rupees Ако Len (MyNumber)> 3 Тогава MyNumber = Ляво (MyNumber, Len (MyNumber) - 3) Иначе MyNumber = "" Край Ако Брой = Брой + 1 Цикъл
Изберете Case Crores Дело "": Crores = "" Дело "One": Crores = "One Crore" Друг случай: Crores = Crores & "Crores" Край Изберете Изберете Case Lakhs Дело "": Lakhs = "" Случай "Един": Lakhs = "Един Lakh" Друг случай: Lakhs = Lakhs & "Lakhs" Край Изберете Изберете Case Rupees Дело "": рупии = "нула" Дело "Едно": Рупии = "Едно" Друг случай: Рупии = рупии Край Изберете Изберете Case Paise Дело "": Paise = "и Paise Zero Only" Случай "Един": Paise = "и Paise One Only" Случай в друг случай: Paise = "и Paise" & Paise & "Само" Край Изберете 'създаване на низ от думи за превод на число в думи NUM_TO_IND_RUPEE_WORD = IIf (incRupees, "Rupees", "") & Crores & _ Lakhs & Rupee & Paise Крайна функция 'Конвертира число от 100-999 в текст Функция GetHundreds (ByVal MyNumber) Dim Result As String Ако Val (MyNumber) = 0, тогава излезте от функцията MyNumber = Вдясно ("000" & MyNumber, 3) - Преобразувайте мястото на стотиците. Ако Mid (MyNumber, 1, 1) "0" Тогава Резултат = GetDigit (средна (MyNumber, 1, 1)) & "Сто" Край Ако „Преобразувайте десетките и единиците. Ако Mid (MyNumber, 2, 1) "0" Тогава Резултат = Резултат и GetTens (средна (MyNumber, 2)) Иначе Резултат = Резултат и GetDigit (средна (MyNumber, 3)) Край Ако GetHundreds = Резултат Крайна функция 'Конвертира число от 10 на 99 в текст. Функция GetTens (TensText) Dim Result As String Резултат = "" 'Нулирайте стойността на временната функция. Ако Val (вляво (TensText, 1)) = 1 Тогава „Ако стойност между 10-19… Изберете Case Val (TensText) Случай 10: Резултат = "Десет" Случай 11: Резултат = "Единадесет" Случай 12: Резултат = "Дванадесет" Случай 13: Резултат = "Тринадесет" Случай 14: Резултат = "Четиринадесет" Случай 15: Резултат = "Петнадесет" Случай 16: Резултат = "Шестнадесет" Случай 17: Резултат = "Седемнадесет" Случай 18: Резултат = "Осемнадесет" Случай 19: Резултат = "Деветнайсет" Дело иначе Край Изберете Иначе „Ако стойността е между 20-99 … Изберете Case Val (вляво (TensText, 1)) Случай 2: Резултат = "Двадесет" Случай 3: Резултат = "Тридесет" Случай 4: Резултат = "Четиридесет" Случай 5: Резултат = "Петдесет" Случай 6: Резултат = "Шестдесет" Случай 7: Резултат = "Седемдесет" Случай 8: Резултат = "Осемдесет" Случай 9: Резултат = "Деветдесет" Дело иначе Край Изберете Резултат = Резултат и GetDigit _ (Вдясно (TensText, 1)) 'Извличане на тяхното място. Край Ако GetTens = Резултат Крайна функция 'Конвертира число от 1 до 9 в текст. Функция GetDigit (цифра) Изберете Case Val (цифра) Случай 1: GetDigit = "Един" Случай 2: GetDigit = "Две" Случай 3: GetDigit = "Три" Случай 4: GetDigit = "Четири" Случай 5: GetDigit = "Пет" Случай 6: GetDigit = "Шест" Случай 7: GetDigit = "Седем" Случай 8: GetDigit = "Осем" Случай 9: GetDigit = "Девет" Друг случай: GetDigit = "" Край Изберете Крайна функция |
Горният код ще получи резултата в правилен формат. Сега направете своя собствена персонализирана функция.
Свързване на текстов низ и изчислена по формула стойност. За да направите резултата, върнат по някаква формула, по -разбираем за вашите потребители, можете да го комбинирате с текстов низ, който обяснява каква всъщност е стойността.
Например, можете да използвате следната формула, за да върнете текущата дата:
| = CONCATENATE ("Днес е", TEXT (TODAY (), "dd-mmm-yy")) |
Използване на CONCATENATE в Excel - неща, които трябва да запомните
За да сте сигурни, че вашите формули CONCATENATE винаги дават правилните резултати, запомнете следните прости правила:
Функцията Excel CONCATENATE изисква поне един "текстов" аргумент, за да работи.
Ето всички бележки за наблюдение, използващи формулата в Excel
Бележки:
- Използвайте & оператор, за да комбинирате текст. & операторът върши същата работа като функцията CONCAT
- В новите версии на Excel CONCATENATE се заменя с функцията CONCAT, която има абсолютно същия синтаксис. Функцията CONCATENATE се запазва за обратна съвместимост, обичайна практика е вместо това да се използва CONCAT, тъй като Excel не обещава, че CONCATENATE ще бъде наличен в бъдещите версии на Excel.
Надявам се, че тази статия за това как да комбинирате текст и числено изчислени резултати в един изходен низ в Microsoft Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако вашите блогове са ви харесали, споделете ги с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Excel REPLACE vs SUBSTITUTE функция: Функциите REPLACE и SUBSTITUTE са най -неразбраните функции. За да намерим и заменим даден текст, използваме функцията SUBSTITUTE. Където REPLACE се използва за замяна на няколко знака в низ.
Как да използвате функцията ISTEXT в Excel : връща логическата стойност TRUE, ако стойността на клетката е текст, използвайки функцията ISTEXT в Excel.
Как да маркирате клетки, които съдържат конкретен текст в Excel : Маркирайте клетки въз основа на формулата, за да намерите конкретната текстова стойност в клетката в Excel.
Преобразува десетичните секунди във формат на времето : Както знаем, че времето в excel се третира като числа. Часовете, минутите и секундите се третират като десетични числа. И така, когато имаме секунди като числа, как да ги преобразуваме във формат за време? Тази статия го покри.
Изчислете минутите между датите и времето в Excel : изчисляването на часовата разлика е доста лесно. Просто трябва да извадите началния час от крайния. Научете повече за тази формула, като щракнете върху връзката
Заменете текст от края на низ, започвайки от променлива позиция: За да заменим текст от края на низ, използваме функцията REPLACE. Функцията REPLACE използва позицията на текста в низ за замяна.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.