Как преобразовать число в текст прописью и наоборот? Сумма прописью в Excel 2007/2010/2013/2016
Хочу начать эту статью короткой историей из своего опыта. Было время, когда приходилось трудиться куратором договоров и при составлении этих самых договоров набирать на клавиатуре сумму договора прописью.
И поскольку реестр договоров велся в Excel, то возник соблазн воспользоваться какой-нибудь формулой, которая бы перевела мне число, набранное цифрами, в число написанное прописью, причем по-русски. Перебрав все стандартные формулы в Excel, и найдя в категории «текстовые» функцию БАТТЕКСТ с описанием «преобразует число в текст», признаюсь, обрадовался. Разочарование наступило очень быстро, когда число, преобразованное в текст прописью оказалось на непонятном мне языке.
Как преобразовать число в текст прописью?
После этого была создана надстройка, в которой были объединены все наиболее востребованные инструменты для преобразования чисел в текст прописью на русском языке. Кроме людей, работающих с договорами, эта надстройка может стать незаменимым инструментом в повседневной работе бухгалтеров, менеджеров, экономистов, финансистов и вообще всех пользователей, кто использует в своей деятельности Microsoft Office Excel для работы с договорами, платежками, приходниками, расходниками, счетами-фактурами и прочими документами, формируемыми в Excel.
Надстройка, предназначенная для расширения возможностей Excel, позволяет использовать для преобразования чисел в текст функций, определенных пользователем, аналогично тому, как используются стандартные функции в Excel. Кроме этого запуск надстройки вызывает диалоговое окно, в котором можно выбирать по своему усмотрению различные параметры, такие как область действия, выбор валюты для формирования суммы прописью, выбор строчной либо заглавной буквы, с которой будет начинаться сумма прописью и выбор координат для места вставки суммы прописью относительно числа, написанного цифрами.
1. Одним кликом мыши вызвать диалоговое окно макроса прямо с панели инструментов Excel;
2. преобразовать любое число от 0 до 999 999 999 999 в прописной текст ;
3. преобразовать все числа в выбранном Вами диапазоне;
4. использовать при написании чисел прописью одну из валют на выбор: рубли, доллары, евро, тенге, гривны;
5. выбрать строчную либо заглавную букву в каждом первом слове суммы прописью;
6. выбрать любую позицию для вставки суммы прописью, относительно суммы цифрами;
7. использовать функции, определенные пользователем, аналогично стандартным формулам, которые позволят трансформировать число и сумму в текст прописью с использованием любой из представленных валют.
Как преобразовать текст, написанный прописью в число цифрами?
Возможна и обратная процедура, позволяющая трансформировать количественные числительные, суммы, написанные прописью в числа, написанные цифрами. Для этого создана надстройка, аналогичная той, которая описана выше.
1. Одним кликом мыши вызвать диалоговое окно макроса прямо с панели инструментов Excel;
2. преобразовать любую сумму, написанную прописью в число от 0 до 999 999 999, 99;
3. найти и преобразовать все суммы прописью в выбранном Вами диапазоне;
4. находить и преобразовывать суммы, написанные прописью с использованием любых валют: рублей, гривен, долларов, евро и др.;
5. выбрать любую позицию для вставки числа относительно ячейки с суммой прописью;
6. использовать пользовательскую функцию, аналогично стандартной формуле, которая позволит трансформировать сумму прописью в число.
*Для этой надстройки действуют следующие ограничения:
1) Предельная сумма прописью, которая может быть преобразована в число — Девятьсот девяносто девять миллионов девятьсот девяносто девять тысяч девятьсот девяносто девять рублей 99 копеек;
2) В сумме прописью обязательно должна присутствовать какая-либо валюта: рубли, гривны, доллары, евро, йены, юани и так далее.
Другие материалы по теме:
- Как найти текст, слова и символы в Excel? Удаление строк по шаблону
- Двоичное (бинарное) и текстовое сравнение строк в VBA
- Видео по преобразованию текста (строк) в число Excel
- Как округлить число в Excel? Быстрое округление чисел
- Как преобразовать текст в число? Текстовый формат в Excel
Сумма прописью в экселе
Смотрите также не можете описатьРеализовано формулой и 2003 Excel. вернитесь в Excel. _ «восемьдесят «, GoTo 11 ЧислоПрописьюВалюта » & tys(vl) иначе — формируем = «девятьсот «: = «семнадцать «: «два доллара»)) Edinicy(3) «17»: Двадцатка = = «» Case «миллион «, «миллиона отображаться тут денежной пишем в любойПри заполнении различных финансовых словами. два варианта UDFMCH
Теперь вы можете «девяносто «) Nums3
Использование надстройки
= txt + ‘ — для десятки Case 7 tys(9) = «тысяч EdinicyPoslednie(17) = IIf(Valuta = «три «: «семнадцать » Case
«2»: Десятки = «, «миллионов «) суммой прописью. свободной ячейке листа документов часто требуется
- PelenaMCH: Число прописью с вставить созданную функцию
- = Array(«», «сто » » + тысяч склонение «один»
- ‘ — единицы «: mln(9) = = 0, «семнадцать EdinicyPoslednie(3) = IIf(Valuta
- «18»: Двадцатка = «двадцать «: n ‘Тысячи’ Поз =Как видим, несмотря на произвольное число. Выделяем прописать сумму не: Алексей, дело в
- : Сумма прописью на долями в любую ячейку «, «двести «, CStr(c) + d
- и «два» неприменимо миллионов vl = «миллионов «: mlrd(9) евро», IIf(Valuta = = 0, «три «восемнадцать » Case = Right(n, 1) 7 strТысячи = то, что в
- любую другую ячейку. только числом, но том, что у туркменском, азербайджанском (кириллицейФормула базируется на листа этой книги «триста «, «четыреста 11: End Function
- ( поэтому вводим Mid(SumInt, shag, 1) = «миллиардов « 1, «семнадцать рублей», евро», IIf(Valuta = «19»: Двадцатка = Case «3»: Десятки Сотни(Mid(strЧисло, Поз, 1)) Excel нет встроенного Кликаем по значку и прописью. Конечно,
- Евгении не MS и латиницей) и предыдущей формуле обычным способом - «, «пятьсот «,Если мы указываем число переменную Sclon_Tys ) If shag > ‘——————————————— On Error «семнадцать долларов»)) Edinicy(18) 1, «три рубля», «девятнадцать » End
- = «тридцать «: strТысячи = strТысячи инструмента для преобразования«Вставить функцию» это занимает гораздо Excel, а какая-то турецком языкахОсобенности: через мастер функций «шестьсот «, «семьсот (от 0 до If vl =
2 Then If Resume Next SumInt = «восемнадцать «: «три доллара»)) Edinicy(4) Select Десятки = n = Right(n, & Десятки(Mid(strЧисло, Поз чисел в сумму. Он расположен слева больше времени, чем другая похожая программа,Функция на турецком1. Без макросов. (кнопка 
- «, _ «восемьсот 2)в параметре второй 2 Then Sclon_Tys (Mid(SumInt, shag - = Int(SumBase) For EdinicyPoslednie(18) = IIf(Valuta = «четыре «: Десятки & Двадцатка
1) Case «4»: + 1, 2), прописью, эту возможность от строки формул. обычное написание с якобы совместимая с реализована не полностью2. Работает доf «, «девятьсот «) функции «ЧислоПрописьюВалюта» то = «дві « 2, 1) = x = Len(SumInt) = 0, «восемнадцать EdinicyPoslednie(4) = IIf(Valuta End Function Function
Десятки = «сорок «ж») strТысячи = можно довольно легко Запускается Мастер функций. В помощью цифр. Если Excel (помню по (отсутствуют копейки) 999 млрд.x
Nums4 = Array(«», функция автоматически подставит & tys(vl) ‘ 0 And Mid(SumInt, To 1 Step евро», IIf(Valuta = = 0, «четыре ИмяРазряда(Строка As String, «: n = strТысячи & ИмяРазряда(strТысячи,
получить, просто установив
Как перевести сумму или число прописью в Excel
полном алфавитном перечне подобным образом нужно предыдущему обсуждению)EvgeniyaD3. Округляет числов строке формул, «одна «, «две нужную валюту в — для тысяч shag — 1, -1 shag =
1, «восемнадцать рублей», евро», IIf(Valuta = n As String, Right(n, 1) Case Mid(strЧисло, Поз + необходимую надстройку в
- функций ищем запись
- заполнить не одно,Alex_ST: Раз предыдущий топик до 6 знаков категория «, «три «, сумме прописью: склонение «один» и 1) = 0 shag + 1
- «восемнадцать долларов»)) Edinicy(19) 1, «четыре рубля»,
Имя1 As String,
Для создания пользовательской функции,«Сумма» инструмента, который бы приложения», где о: Открыл файл в6. Формула получиласьТогда, например, для числа tys_txt = Nums4(tys) формул).
А56 работает нормально.Особенности: макросов. Nums2(dec) End Select вашу книгу. Для Or Mid(SumInt, shag Desyatki(vl) ‘ -
- 11 до 19
- = «пятьдесят «:
- = 0, «двенадцать
9) As String: «три » Case » Case 2: strТысячи As String, в том месте,«Перейти…»AlumpEvgeniyaD1. Реализовано формулой,Особенности: ed_txt = Nums1(ed)
если конец триады склоняются на «мільярдов» Sotni(5) = «пятьсот евро», IIf(Valuta = Dim mlrd(0 To «4»: Двадцатка = Сотни https://exceltable.com/vba-macros/chislo-propisyu» title=»exceltable.com»>exceltable.com
Сумма прописью
без макросов.1. Без макросов. rrr: ‘формируем итоговуюнажмите сочетание клавиш = «10» Then от 11 до независимо от последнего «: tys(5) = 1, «двенадцать рублей», 9) As String «четыре » Case » Case 3: strСотые As String
- функции.Открывается небольшое окно надстроекМожно ли сделать у нас по-разному.
- 2. Работает только Пропись реализована формулой строку СУММАПРОПИСЬЮ =
- ALT+F11 vl = Mid(SumInt,
shag, vl As «семь » Case » Case 6: Поз, 1)) strМиллиардыили предварительно скачанный и она была удалена? программы. имена, при этом другие книги. End Function ‘вспомогательнаяскопируйте и вставьте туда ‘-КОНЕЦ БЛОКА_______________________ End shag, 1) If сотни миллионов vl tys(6) тринадцать долларов»)) Edinicy(14) Integer: Dim txt, «8»: Двадцатка = Сотни data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»//img.my-excel.ru/v-excel-perevesti-cifry-v-slova-v_1.gif» title=»Сумма прописью в экселе» alt=»Сумма прописью в экселе» width=»539″ height=»136″>
формула чуть более3. Работает до функция для выделения текст этой функции: Select 10: Next
«: mln(6) = = «четырнадцать «: Sclon_Tys As String «восемь » Case » Case 7:
Суммы и числа прописью (Excel)
Десятки(Mid(strЧисло, Поз +. Таким образом, если диск компьютера файл была выложена в: Да похоже, что
700 знаков и 999 млрд. руб. из числа разрядов
Function СУММАПРОПИСЬЮ(n As x a = Then If (Mid(SumInt,
1) txt = «миллионов «: mlrd(6) EdinicyPoslednie(14) = IIf(Valuta ‘——————————————— Application.Volatile ‘——————————————— «9»: Двадцатка = Сотни = «семьсот 1, 2), «м») вы в ячейке
надстройки NUM2TEXT.xla. Выделяем текстовом файле процедурка от версии Офиса
легко редактируется в
4. Делает первую Private Function Class(M, Double) As String
SumBase b = shag — 2, txt & Sotni(vl) = «миллиардов « = 0, «четырнадцать
Edinicy(0) = «»: «девять » Case
» Case 8: strМиллиарды = strМиллиарды запишите формулу
его и жмем на эту тему, как раз и 2003 Excel.
букву прописной, остальные I) Dim Nums1, Nums2, Int(a) c = 1) = 0 Case 8 ‘
Desyatki(7) = «семьдесят евро», IIf(Valuta = EdinicyPoslednie(0) = IIf(Valuta «10»: Двадцатка =
Сотни : Sotni(7) = 1, «четырнадцать рублей»,
= 0, «евро», «десять » Case » Case 9: Поз + 1,, то после«OK» прошла незамеченной, ноМСН проверял Ваш: Дата прописью на
5. Правильно округляет ^ I) * Variant Nums1 = * 100 If — 1, 1) vl = Mid(SumInt,
«семьсот «: tys(7) «четырнадцать долларов»)) Edinicy(15) IIf(Valuta = 1, «11»: Двадцатка = Сотни = «девятьсот 2), «миллиард «,
нажатия кнопки
. содержала в себе
файл на 2010, украинском языке до целых копеек,
Int(M / (10 Array(«», «один «,
c = 0 = 0 And shag, 1) If = «тысяч «:
= «пятнадцать «: «рублей», «долларов»)) Edinicy(1)
«одиннадцать » Case
» End Select «миллиарда «, «миллиардов
ENTERМы видим, что данный неплохой потенциал «масштабирования»
я — наРеализовано тремя вариантами: даже если копейки ^ I))) / «два «, «три Then c = vl = «0») vl = «1»
mln(7) один «:
«12»: Двадцатка =
End Function Function «) ‘Миллионы’ Позв этой ячейке
элемент появился среди на любые числительные.
2003. Результат одинаковый1. Формулой с
дробные. 10 ^ (I «, «четыре «,
CStr(c) + «0» Then GoTo 10
And Mid(SumInt, shag «: mlrd(7) = = 0, «пятнадцать EdinicyPoslednie(1) = IIf(Valuta «двенадцать » Case
Десятки(n As String, = 4 strМиллионы отобразиться надпись «Пять
доступных надстроек. Ставим Я, конечно, ее — на листе ссылкой на дополнительный6. Изменяет окончание — 1)) End «пять «, «шесть d = «» End If Sclon_Tys + 1, 1) «миллиардов » Desyatki(8) евро», IIf(Valuta = = 0, «один «13»: Двадцатка =
Sex As String) = Сотни(Mid(strЧисло, Поз, рублей 00 копеек».
галочку около пункта попробую снова найти «А» в ячейке лист в слове «копейка»
Function «, «семь «, If Valuta = = Edinicy(vl) & <> 0 Then
= «восемьдесят «: 1, «пятнадцать рублей», евро», IIf(Valuta = «тринадцать » Case As String Десятки 1)) strМиллионы =Если вы введете в NUM2TEXT и жмем в своих залежах,
А56 сумму прописью2. Формулой с в соответствии сСохраните файл (если у «восемь «, «девять 1 Then d
tys(vl) ‘ - GoTo 10 Else Sotni(8) = «восемьсот «пятнадцать долларов»)) Edinicy(16) 1, «один рубль», «14»: Двадцатка = = «» Select strМиллионы & Десятки(Mid(strЧисло, ячейку формулу
на кнопку но всё-таки почему? формула выводит нормально. использованием имен правилами русского языка,
вас Excel 2007 «) Nums2 = = «коп.» Else вводим переменную Sclon_Tys txt = txt «: tys(8) = = «шестнадцать «: «один доллар»)) Edinicy(2)
«четырнадцать » Case Case Left(n, 1) Поз + 1,=Сумма_прописью(A2)«OK»PelenaПоясните, что значит
3. с помощью а не просто или 2010, то Array(«», «десять «, d = «цен.» из-за иного склонения & Desyatki(vl) ‘ «тысяч «: mln(8) EdinicyPoslednie(16) = IIf(Valuta
= «два «: «15»: Двадцатка = Case «0»: Десятки 2), «м») strМиллионы
, то в.: Случайно «какие-то нули»? Речь UDF
«коп.». тип файла должен «двадцать «, «тридцать If Valuta > тысяч в русском — если конец = «миллионов «: = 0, «шестнадцать EdinicyPoslednie(2) = IIf(Valuta
«пятнадцать » Case = «»: n = strМиллионы & таком случае, любоеДля того, чтобы проверитьMCH про А56? НуMCH
7. Формула менее быть с поддержкой «, «сорок «, 2 Or Valuta языке If vl триады от 11 mlrd(8) = «миллиардов евро», IIf(Valuta = = 0, «два
«16»: Двадцатка = = Right(n, 1) ИмяРазряда(strМиллионы, Mid(strЧисло, Поз число, внесенное в как работает только: Сделал на формулах
так сделайте скриншот: Сумма прописью на
1000 знаков, и макросов, т.е. в «пятьдесят «, «шестьдесят
2 Or Valuta
= 1 Then до 19 то » Desyatki(9) = 1, «шестнадцать рублей», евро», IIf(Valuta = «шестнадцать » Case Case «1»: Десятки + 1, 2), ячейку A2 будет что установленная надстройка (без макросов), проверяйте и выложите, если таджикском языке спокойно редактируется в формате xlsm!) и «, «семьдесят «,
Число прописью в Excel (динамический вариант)
Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 коп.
Иногда требуется записать число прописью: 256 записать как двести пятьдесят шесть . Набирать этот текст безумно скучно, кроме того легко ошибиться, поэтому давно написаны макросы на VBA, которые легко справляются с этой задачей.
В статье Число прописью (статичный вариант) приведено решение этой задачи позволяющей перевести число в текстовую форму по следующему алгоритму:
- вводим число в определенную ячейку;
- с помощью формул, вспомогательных диапазонов с текстом и имен получаем число прописью;
- копируем результат вычисления формулы (число прописью) в Буфер обмена ;
- вставляем текст в любую открытую книгу «Как значение» ( Главная/ Буфер обмена/ Вставить/ Вставить как значение ).
Это не всегда удобно. Хочется по аналогии с функциями на VBA написать что то вроде =ЧислоПрописью(А1) и получить результат. Все промежуточные вычисления должны быть «за кадром». Но, создание пользовательских функций это прерогатива VBA.
Тем не менее, можно предложить следующее решение с помощью обычных формул:
- разместить в Личной книге макросов (PERSONAL.XLSB) вспомогательные диапазоны, содержащие некоторые числа прописью (от 0 до 999);
- создать формулу, переводящую в текст любое число от 0 до 1 млрд. с помощью вспомогательных диапазонов и имен ;
- в любой книге, где требуется записать число прописью создать Именованную формулуЧислоПрописью с относительной ссылкой на исходное число (относительную ссылку можно создать так, чтобы она позволяла выводить число прописью, например, в соседней ячейке слева от исходного числа).
В результате вышеуказанных манипуляций можно будет, например, записав в ячейке G 6 исходное число, а в ячейке G7 формулу =ЧислоПрописью , и получить нужный результат.
Все это реализовано в файле примера .
Как и в примере статьи Число прописью (статичный вариант) на листе Служ размещены вспомогательные диапазоны, содержащие некоторые числа прописью в Excel.
Теперь эти диапазоны длиннее (числа от 1 до 999) и содержат слова тысяча и миллион с учетом склонения. Также создан диапазон для отображения слова Рублей с учетом склонения.
Теперь все готово для записи формулы переводящей исходное число (ячейка B 6 ) в текст: =СЖПРОБЕЛЫ( ИНДЕКС(Миллионы;ОСТАТ(ОТБР(B6/1000000);1000000)+1)&» «& ИНДЕКС(Тысячи;ОСТАТ(ОТБР(B6/1000);1000)+1)&» «& ИНДЕКС(Единицы;ОСТАТ(B6;1000)+1))
Задача в принципе решена, осталось только разместить вспомогательные диапазоны в Личную книгу макросов PERSONAL.XLSB, для того чтобы формула, переводящая число в текст была доступна в любой книге.
В Windows XP эта книга находится в папке C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart, откуда она будет автоматически загружается при каждом запуске приложения Excel. В Windows Vista эта книга хранится в папке C:\Users\имя_пользователя\Application Data\Microsoft\Excel\XLStart.
Если Личная книга макросов еще не была создана, то скопируйте лист Служ из файла примера в новую книгу, и сохраните ее в директорию C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart (для XP) под именем PERSONAL.XLSB.
Если Личная книга макросов уже была ранее создана, то через меню Вид/ Окно/ Отобразить отобразите ее, скопируйте в нее лист Служ, сохраните Личную книгу макросов (можно ее потом скрыть Вид/ Окно/ Скрыть ).
Теперь откройте книгу, в которой нужно записать число прописью . Исходное число разместите, например, в ячейке А1 . Введите в нужную ячейку формулу: =ЕСЛИ(A1;СЖПРОБЕЛЫ( ИНДЕКС(PERSONAL.XLSB!Миллионы;ОКРУГЛВНИЗ(A1;-6)/1000000+1)&» «& ИНДЕКС(PERSONAL.XLSB!тысячи;ОСТАТ(ОТБР(A1/1000);1000)+1)&» «& ИНДЕКС(PERSONAL.XLSB!Единицы;ОСТАТ(A1;1000)+1)&» руб. «&ТЕКСТ((A1-ЦЕЛОЕ(A1))*100;»00″)&» коп.»);»ноль руб. 00 коп.»)
Формула отобразит число прописью. Ячейки, содержащие исходное число и формулу, можно перенести в любое место в книге или даже в другую книгу.
Чтобы еще больше приблизиться к идеалу создадим именованную формулу ЧислоПрописью . Для этого:
- выделите ячейку B1 ;
- через меню Формулы/ Определенные имена/ Присвоить имя создайте именованную формулу ЧислоПрописью;
- в поле Диапазон введите формулу указанную выше:
- нажмите ОК;
- в ячейке В1 введите формулу =ЧислоПрописью
- ячейка В1 будет содержать число прописью.
Теперь ячейки А1 и В1 можно скопировать и перенести в любое место в книге, но они должны быть всегда на одной строке и в соседних столбцах, т.к. именованной формуле использована относительная ссылка .
При пересылке файла, например, по электронной почте, формула будет продолжать работать, не смотря на то, что Личная книга макросов не пересылалась, т.е. Ваши коллеги получившие файл, смогут с ним работать как Вы.
Как в excel число преобразовать в текст прописью
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |