Перейти к содержимому

Сумма прописью в Excel

Преобразование в Excel денежной суммы из числовой формы в сумму прописью. Блок ячеек с формулами. Пользовательская функция «СуммаПрописью». Личная книга макросов.

  1. Зачем нужна сумма прописью
  2. Блок ячеек с формулами
  3. Пользовательская функция «СуммаПрописью»
  4. Еще один вариант добавления функции «СуммаПрописью» в книгу макросов или локальную книгу
  5. Если нет персональной книги макросов

Зачем нужна сумма прописью

Сумма прописью используется во многих бухгалтерских документах, и если учет ведется в специализированных программах, то проблем с этим не возникает. Но если какие-то документы, требующие указания денежных сумм прописью, приходится заполнять самостоятельно в Excel, тогда появляется непреодолимое желание как-то все это автоматизировать. Вроде бы и несложно написать сумму прописью или число прописью вручную, но когда приходится это делать часто, начинает утомлять и невозможно избежать ошибок. К сожалению, в Excel нет встроенной функции, преобразующей числовое денежное выражение в сумму прописью, но не стоит расстраиваться, так как на этот счет в сети можно найти множество поделок народных умельцев, а здесь я представляю свои. Формат преобразования: 0,00 = Ноль рублей 00 копеек.

Блок ячеек с формулами

Это первый вариант, который я начал использовать, когда столкнулся с необходимостью перевода числовых выражений в сумму прописью. Преимущество этого способа заключается в его простоте, а также файлы с блоками формул можно передавать на другой компьютер и они будут прекрасно работать. Сейчас я блоки ячеек с формулами в Excel почти не использую, они остались только в некоторых отдельных местах и в файлах, которые размещаю для скачивания, чтобы они открывались без сообщения о вредоносном коде и необходимости разрешения на выполнение макросов. Пример такого файла - Заявление в ФСС на возмещение расходов.

Перейдите по ссылке и скачайте заявление, в котором отобразите скрытые столбцы O и P и тогда увидите блок ячеек для преобразования числового денежного выражения в сумму прописью в Excel. Можете скопировать его и вставить в свои файлы. В ячейку справа от ячейки с текстом «вход» вставляется ссылка на числовое значение, а в ту ячейку на вашем листе, где должен отобразиться результат преобразования, вставляется ссылка на ячейку справа от ячейки с текстом «выход». Данный блок формул преобразует числовые выражения до 7 целочисленных разрядов включительно.

Пользовательская функция «СуммаПрописью»

В настоящее время я использую для преобразования числового выражения в сумму прописью пользовательскую функцию, написанную на VBA Excel и которую можно вставить в ячейку листа с помощью мастера функций или вручную, также как и любую другую встроенную функцию Excel. Далее все по порядку.

1. Скачиваем архив и извлекаем из него файл «Module.bas» с кодом функции.

Для того, чтобы функция «СуммаПрописью» была доступна во всех книгах Excel, ее необходимо поместить в персональную (личную) книгу макросов PERSONAL.XLSB. Для этого необходимо:

2. Открыть любую книгу Excel, можно новую.
3. Сочетанием клавиш левая клавиша Alt+F11 открыть редактор Visual Basic for Applications.
Окно редактора Visual Basic for Applications 1
4. Дважды кликнуть на строке «VBA Project (PERSONAL.XLSB)» или один раз на крестике слева для открытия вложенного списка. Если эта позиция уже развернута переходим к пункту 5.Окно редактора Visual Basic for Applications 2
5. Дважды кликнуть на строке «Microsoft Excel Objects» или один раз на крестике слева для открытия списка объектов. Если эта позиция уже развернута переходим к пункту 6.Окно редактора Visual Basic for Applications 3

6. Кликнув правой кнопкой на строке «ЭтаКнига», открыть контекстное меню и выбрать в нем «Import File...».
Окно редактора Visual Basic for Applications 4

7. В открывшемся диалоге выбора файлов найти скачанный ранее «Module.bas» и выбрать его. Модуль с функцией будет автоматически загружен.

Теперь можно в ячейки на листах Excel вставлять формулу: «=PERSONAL.XLSB!СуммаПрописью(АдресЯчейки)» или, что намного удобнее, выбирать функцию с помощью мастера. Откройте мастер функций, выберите категорию «Определенные пользователем» и в списке доступных функций появится «PERSONAL.XLSB!СуммаПрописью», которую можно выбрать, а на втором шаге вставить адрес ячейки с числовым значением.

Пример работы функции СуммаПрописью

Формула будет доступна во всех книгах Excel, но только на компьютере, на котором находится персональная книга макросов PERSONAL.XLSB с загруженным модулем.

Если книгу планируется передавать на другие машины, тогда в шаге 6 надо правой кнопкой кликнуть на строке «ЭтаКнига», относящейся не к «VBA Project (PERSONAL.XLSB)», а относящейся к «VBA Project (ВашаКнига)» с именем вашей книги, тогда модуль с функцией загрузится непосредственно в вашу рабочую книгу Excel и будет доступен для этой книги на любом компьютере. В этом случае формула будет иметь вид: «=СуммаПрописью(АдресЯчейки)» и ее записывать придется вручную, так как в мастере функций ее не будет.

Еще один вариант добавления функции «СуммаПрописью» в книгу макросов или локальную книгу

Создайте новый программный модуль или откройте уже имеющийся в персональной книге макросов PERSONAL.XLSB или в вашей локальной книге Excel и вставьте непосредственно в него код функции «СуммаПрописью».

Скопируйте код и вставьте в программный модуль:

Public Function СуммаПрописью(x As Double) As String
If x > 999999999999.99 Then
СуммаПрописью = "Аргумент больше 999 999 999 999.99!"
ElseIf x < 0 Then
СуммаПрописью = "Аргумент отрицательный!"
Else
x = FormatNumber(x, 2)
Dim b As Byte, b1 As Byte, b2 As Byte, kop As String
b = (x - Fix(x)) * 100
b2 = b \ 10
b1 = b Mod 10
If b2 <> 1 And b1 = 1 Then
kop = " копейка"
ElseIf b2 <> 1 And b1 > 1 And b1 < 5 Then
kop = " копейки"
Else
kop = " копеек"
End If
kop = b2 & b1 & kop
Dim y(1 To 4) As Integer, i1 As Byte
For i1 = 1 To 4
x = Fix(x) / 1000
y(i1) = (x - Fix(x)) * 1000
Next
Dim Text(1 To 4) As String, i2 As Byte, y1 As Byte, y2 As Byte, _
y3 As Byte, Text0 As String, Text1 As String, Text2 As String, Text3 As String, _
Text4 As String
For i2 = 1 To 4
y1 = y(i2) Mod 10
y2 = (y(i2) - y1) / 10 Mod 10
y3 = y(i2) \ 100
Text1 = Choose(y3 + 1, "", "сто ", "двести ", "триста ", "четыреста ", _
"пятьсот ", "шестьсот ", "семьсот ", "восемьсот ", "девятьсот ")
Text2 = Choose(y2 + 1, "", "", "двадцать ", "тридцать ", "сорок ", _
"пятьдесят ", "шестьдесят ", "семьдесят ", "восемьдесят ", "девяносто ")
If y2 = 1 Then
Text3 = Choose(y1 + 1, "десять ", "одиннадцать ", "двенадцать ", _
"тринадцать ", "четырнадцать ", "пятнадцать ", "шестнадцать ", _
"семнадцать ", "восемнадцать ", "девятнадцать ")
ElseIf y2 <> 1 And i2 = 2 Then
Text3 = Choose(y1 + 1, "", "одна ", "две ", "три ", "четыре ", "пять ", _
"шесть ", "семь ", "восемь ", "девять ")
Else
Text3 = Choose(y1 + 1, "", "один ", "два ", "три ", "четыре ", "пять ", _
"шесть ", "семь ", "восемь ", "девять ")
End If
If y2 <> 1 And y1 = 1 Then
Text4 = Choose(i2, "рубль ", "тысяча ", "миллион ", "миллиард ")
ElseIf y2 <> 1 And y1 > 1 And y1 < 5 Then
Text4 = Choose(i2, "рубля ", "тысячи ", "миллиона ", "миллиарда ")
ElseIf y1 = 0 And y2 = 0 And y3 = 0 Then
Text4 = Choose(i2, "рублей ", "", "", "")
Else
Text4 = Choose(i2, "рублей ", "тысяч ", "миллионов ", "миллиардов ")
End If
Text(i2) = Text1 & Text2 & Text3 & Text4
Next
If y(1) + y(2) + y(3) + y(4) = 0 Then
Text0 = "ноль рублей " & kop
Else
Text0 = Text(4) & Text(3) & Text(2) & Text(1) & kop
End If
СуммаПрописью = Replace(Text0, Left(Text0, 1), UCase(Left(Text0, 1)), 1, 1)
End If
End Function

Если нет персональной книги макросов

Может получиться так, что в вашем Excel вообще не будет персональной книги макросов PERSONAL.XLSB. В таком случае, открыв любую книгу, необходимо записать любой макрос, выбрав перед записью «Сохранить в: Личная книга макросов».

Окно Запись макроса

Запишите какой-нибудь простенький макрос, например, скопируйте одну ячейку, вставьте содержимое буфера в другую ячейку и остановите запись макроса. В результате будет создана книга PERSONAL.XLSB с модулем, в котором будет записан ваш макрос. Открыв модуль, выделите мышью код вашего макроса, удалите его и вставьте код функции «СуммаПрописью», скопировав его выше.

Добавить комментарий