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

Опубликовано: 01.09.2018

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

в Excel сумма ПРОПИСЬЮ, вес прописью, деньги прописью! Как это сделать и как настроить Excel

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

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

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

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

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

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

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

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

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

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

2. Откройте любую книгу Excel, можно новую.

3. Сочетанием клавиш Alt+F11 (в этом сочетании используется левая клавиша Alt) откройте редактор Visual Basic for Applications.

4. Дважды кликните на строке «VBA Project (PERSONAL.XLSB)» или один раз на крестике слева для открытия вложенного списка. Если эта позиция уже развернута переходите к пункту 5.

5. Дважды кликните на строке «Microsoft Excel Objects» или один раз на крестике слева для открытия списка объектов. Если эта позиция уже развернута переходите к пункту 6.

6. Кликнув правой кнопкой на строке «ЭтаКнига», откройте контекстное меню и выберите в нем пункт «Import File...».

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 с модулем, в котором будет записан ваш макрос. Открыв модуль, выделите мышью код вашего макроса, удалите его и вставьте код функции «СуммаПрописью», скопировав его выше.