Число прописью в Excel (динамический вариант)
history 3 апреля 2013 г.
Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 коп.
В статье Число прописью (статичный вариант) приведено решение этой задачи позволяющей перевести число в текстовую форму по следующему алгоритму:
Это не всегда удобно. Хочется по аналогии с функциями на VBA написать что то вроде =ЧислоПрописью(А1) и получить результат. Все промежуточные вычисления должны быть «за кадром». Но, создание пользовательских функций это прерогатива VBA.
Тем не менее, можно предложить следующее решение с помощью обычных формул:
Как и в примере статьи Число прописью (статичный вариант) на листе Служ размещены вспомогательные диапазоны, содержащие некоторые числа прописью в 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.
Если Личная книга макросов уже была ранее создана, то через меню Вид/ Окно/ Отобразить отобразите ее, скопируйте в нее лист Служ, сохраните Личную книгу макросов (можно ее потом скрыть Вид/ Окно/ Скрыть ).
Формула отобразит число прописью. Ячейки, содержащие исходное число и формулу, можно перенести в любое место в книге или даже в другую книгу.
При пересылке файла, например, по электронной почте, формула будет продолжать работать, не смотря на то, что Личная книга макросов не пересылалась, т.е. Ваши коллеги получившие файл, смогут с ним работать как Вы.
Число прописью в Excel: инструкция, как написать сумму
Сумма прописью в Excel
Как отключить мини-приложения в 11
Как вернуть старое контекстное меню в 11
Не запускается PC Health Check на 11
Не могу обновиться до 11
Проверка совместимости с 11
Во многих документах мы можем видеть строку, в которой требуется указать сумму не в числовом виде, а прописью. Это защищает отчетность от подделки, ведь цифру легко переправить. А вот длинную запись незаметно исправить не так-то просто. Сумма прописью в Excel доступна только в виде надстроек или с использованием формул. Встроенного инструмента не имеется.
Как в Excel сделать сумму прописью
Чтобы получить эту возможность, вы можете скачать надстройку «Сумма прописью» в Excel бесплатно на нашем сайте.
Разместите его в любом удобном каталоге и подключите. Для этого следует пройти следующие этапы:
Эта функция «сумма_прописью» работает в Excel 2007, 2010, 2016
Данная надстройка работает только для рублей. Если вам нужна другая валюта, скачайте еще файл sumprop.xla. Действуя по аналогии подключите надстройку для получения суммы прописью в Excel.
Для проверки введем в ячейке A3 произвольное число. Пусть это будет десятичная дробь. Также вызовем окно вставки функций и найдем новые функции для вывода суммы прописью.
Как видите, нам стали доступны:
Переведем число в евро. Выбираем соответствующую функцию и кликаем по ячейке с числом.
После нажатия ОК получаем результат.
С помощью этих инструментов преобразование числа в текст прописью в Excel выполняется очень просто. Если по каким-то причинам вы не хотите устанавливать их в Excel, можно воспользоваться другим способом.
Для вывода числа прописью в Excel можно создать формулу. Приведем пример:
Для ее использования вам необходимо указать несколько массивов Excel:
Просто скопируйте эту таблицу на лист Excel, а формулу вставляйте туда, где необходима сумма прописью. Вышеприведенная конструкция предназначена для ячейки A3. Не забудьте поменять адрес.
В англоязычном Excel формула суммы прописью будет выглядеть следующим образом:
SUBSTITUTE(PROPER(INDEX(n_4,MID(TEXT(A1,n0),1,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),2,1)+1,MID(TEXT(A1,n0),3,1)+1)&IF(-MID(TEXT(A1,n0),1,3),»миллиард»&VLOOKUP(MID(TEXT(A1,n0),3,1)*AND(MID(TEXT(A1,n0),2,1)-1),мил,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),4,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),5,1)+1,MID(TEXT(A1,n0),6,1)+1)&IF(-MID(TEXT(A1,n0),4,3),»миллион»&VLOOKUP(MID(TEXT(A1,n0),6,1)*AND(MID(TEXT(A1,n0),5,1)-1),мил,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),7,1)+1)&INDEX(n1x,MID(TEXT(A1,n0),8,1)+1,MID(TEXT(A1,n0),9,1)+1)&IF(-MID(TEXT(A1,n0),7,3),VLOOKUP(MID(TEXT(A1,n0),9,1)*AND(MID(TEXT(A1,n0),8,1)-1),тыс,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),10,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),11,1)+1,MID(TEXT(A1,n0),12,1)+1)),»z»,» «)&IF(TRUNC(TEXT(A1,n0)),»»,»Ноль «)&»рубл»&VLOOKUP(MOD(MAX(MOD(MID(TEXT(A1,n0),11,2)-11,100),9),10),<0,"ь ";1,"я ";4,"ей ">,2)&RIGHT(TEXT(A1,n0),2)&» копе»&VLOOKUP(MOD(MAX(MOD(RIGHT(TEXT(A1,n0),2)-11,100),9),10),<0,"йка";1,"йки";4,"ек">,2)
Как видим, это не самый удобный способ преобразовать число в текст прописью в Excel.
Преобразование с помощью макросов Excel
Можно написать собственную функцию, которая произведет конвертацию суммы прописью. Нажмите сочетание ALT+F11 и в открывшемся разработчике Excel VBA вставьте новый модуль.
В него добавьте следующий код.
Теперь эту функцию можно вызвать обычным путем и получить сумму прописью. Она находится в разделе функций Excel «Определенные пользователем».
В качестве аргумента укажите адрес с числом.
Перевод в сумму прописью осуществляется только для целых чисел.
Дробное число требует дополнительной формулы:
Вместо ячейки A7 подставляйте свой адрес. Копейки в таком исполнении будут выводиться в виде числа.
Мы рассмотрели все способы представления суммы прописью в Excel. Выбирайте для себя самый удобный и пользуйтесь с удовольствием!
Сумма прописью в Excel: как написать число текстом
Здравствуйте.
Нередко в различных документах Excel (особенно, связанных с бухгалтерией, подсчетом денег и т.д. ?) требуется прописать полученную сумму «Итого» прописью (текстом). Например, рядом с числом «5100,45» — написать «пять тысяч сто рублей 45 копеек».
Встроенной простой функции для решения это небольшой задачки в Excel нет (к сожалению). Разумеется, чтобы каждый раз не работать «руками», решая эту задачу — лучше это всё автоматизировать!
Собственно, об этом и будет эта небольшая заметка.
? ШАГ 1
Загрузить этот спец. файл можно отсюда: ссылка на Яндекс-диск (на вирусы проверено, всё чисто ✔). Совместимо с Excel 2019, 2016, 2013 (с др. не проверял).
Примечание по надстройке.
Она позволит написать сумму прописью на русском языке:
? ШАГ 2
Далее в разделе «Надстройки» нажимаем по кнопке «Перейти» (в нижней части окна).
Затем с помощью кнопки «Обзор» указываем загруженную надстройку (см. ШАГ 1 ☝), ставим галочку напротив «Сумма прописью» и нажимаем OK. См. пример ниже. ?
? ШАГ 3
Если всё прошло корректно — то в Excel должно было появиться 4-ре новых функции. Чтобы проверить так ли это — попробуйте начать вводить функцию «=сум» (на русском!). ?
Теперь в Excel появилось 4 новых функции
? ШАГ 4
Как видите из примера ниже — всё работает! «Пятьдесят четыре тысячи сто семьдесят семь рублей 84 копейки» (прямо как во всех бухгалтерских документах) ?
Как создается сумма прописью в Excel?
Добрый день уважаемый читатель!
Статья посвящена такому интересному вопросу: «Как создается сумма прописью в Excel?». Эта возможность позволяет нам всю массу информации и расчетов, которые сведены в один результат, оформить как финансовой документ, которые нельзя трактовать двузначно. Недаром же любые банковские, юридические и финансовые документы оформляются и цифрами и буквами. Окромя всего, такие суммы не позволяют ошибаться, где же должна стоять запятая и просто отлично выглядят в сравнении с рядом цифр. Подведя итог, скажу, что любой пользователь Excel, который работает с цифрами и временем, а также печатает документы с их использованием, очень нуждается в инструменте создающим сумму прописью.
Решить эту проблему можно несколькими способами:
Создание сумм прописью с помощью формул
Это вполне осуществимый вариант, но очень громоздкий. Формула, которая может самостоятельно преобразовывать числа в буковки и правильно орфографически написать сумму прописью получится очень большой. В случае, когда вы захотите упростить формулу, вам нужно будет отдельный блок формул, который буде и заниматься всеми преобразованиями. Единственное преимущество от использования формулы, это когда вы разберетесь с ней, то самостоятельно сможете ее изменять: разрядность, валюту, склонение и т.п.
Ради общего интереса я предоставлю вам пример файла с работающей формулой, которую я нашёл на excelworld.ru, поскольку самостоятельно формульным решением задачи я не занимался, но считаю нужным предоставить весь наработанный или собранный материал. Скачать пример можно по этой ссылке.
С помощью пользовательской функции
Как мой взгляд, это самое оптимальное и простое, относительно конечно, решение поставленного вопроса. Вы просто включаете функцию и пользуетесь ее с удовольствием.
Для этого вам необходимо выполнить некоторые действия. Сначала скачиваете файл надстройки здесь. После, переносите его в папку AddIns, которую можно найти по пути C:\Documents and Settings\User\Application Data\Microsoft\AddIns, только вы вместо «User» подставляете имя вашего пользователя Windows. Еще может возникнуть проблема в отображении папки «Application Data», она может быть скрыта, тогда вам в настройках «Свойства папки» нужно активировать пункт «Показать скрытые файлы и папки». 
Теперь собственно и приступим к включении этой надстройки. В Excel выбираем «Файл» — «Параметры» — «Надстройки». 
В диалоговом окне «Настройки» ставим галочку напротив включаемой надстройки «Сумма Прописью» и нажимаем «ОК». 
Следующим шагом, ставим курсор на нужную ячейку и вызываем «Мастер функций». В разделе «Категория» выбираем пункт «Определенные пользователем» и теперь весть список встроенных функций по написанию сумм прописью нам доступен. Кстати как видите, доступен даже выбор функций в разрезе валют. Сразу замечу, что надстроек бывает много, есть и более расширенные, но эта меня полностью устраивает, вот я ее и описываю. 
В результате мы получаем возможность получить следующие данные: 
С помощью VBA
Вот еще один способ на любителя. Конечно создать сумму прописью в Excel с помощью макроса не самый распространённый способ, но им достаточно часто пользовались и пользуются до сих пор. Большой сложности он не представляет, вам нужно:
Получается готовая пользовательская функция VBA, которая умеет превращать любую сумму от 0 до 9 999 999 в ее текстовое воплощение, создается сумма прописью.
Как перевести сумму или число прописью в Excel
Часто нужно перевести число в текст в Excel так, чтобы оно отображалось прописью (словами) на русском или других языках. Так как по умолчанию нет готовой функции, создадим свою пользовательскую функцию с помощью макросов.
Пример использования пользовательской функции для преобразования числа суммы в текстовые слова, которую можно скачать в конце статьи:
strЧисло = Format(Int(Число), «000000000000» )
‘Единицы’
Поз = 10
strЕдиницы = Сотни(Mid(strЧисло, Поз, 1))
strЕдиницы = strЕдиницы & Десятки(Mid(strЧисло, Поз + 1, 2), «м» )
If strМиллиарды & strМиллионы & strТысячи & strЕдиницы = «» Then strЕдиницы = «ноль »
‘strЕдиницы = strЕдиницы & ИмяРазряда(» «, Mid(strЧисло, Поз + 1, 2), «рубль «, «рубля «, «рублей «)
‘Сотые’
‘strСотые = strКопейки & » » & ИмяРазряда(strКопейки, Right(strКопейки, 2), ‘»копейка», «копейки», «копеек»)
Число_Error:
MsgBox Err.Description
End Function
Function Сотни(n As String ) As String
Сотни = «»
Select Case n
Case 0: Сотни = «»
Case 1: Сотни = «сто »
Case 2: Сотни = «двести »
Case 3: Сотни = «триста »
Case 4: Сотни = «четыреста »
Case 5: Сотни = «пятьсот »
Case 6: Сотни = «шестьсот »
Case 7: Сотни = «семьсот »
Case 8: Сотни = «восемьсот »
Case 9: Сотни = «девятьсот »
End Select
End Function
Десятки = Десятки & Двадцатка
End Function
Можно написать алгоритм макро программы по-другому и еще сделать так, чтобы она дописывала валюту суммы прописью. Для этого создайте Module2 и введите в него следующий код:
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String
FuncName = «ЧислоПрописьюВалюта»
FuncDesc = «Функция преобразовывает число суммы текстовыми словами»
Category = 1 ‘Text category
ArgDesc(1) = «Исходная сумма»
ArgDesc(2) = «(необязательный) Тип отображаемой валюты 0-Евро, 1-Рубли, 2-Доллары.»
ArgDesc(3) = «(необязательный) Нужны ли копейки: 0-нет, 1-отображать копейи стандартно, 2-отображать только дробную часть (без слов).»
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub
Также не забудьте добавить в рабочую книгу код вызова макроса регистрации DescribeFunction, чтобы отображать описание атрибутов для пользовательской функции:
Кроме того благодаря данному макросу DescribeFunction функция будет доступна в группе: «ФОРМУЛЫ»-«Библиотека функций»-«Финансовые»-«ЧислоПрописьюВалюта»
Если мы указываем число (от 0 до 2)в параметре второй функции «ЧислоПрописьюВалюта» то функция автоматически подставит нужную валюту в сумме прописью:
Как видите, этот VBA-код макроса преобразует числа в слова. После вставки данного кода в модуль редактора макросов, у нас работает новая функция, которую можно вызвать из мастера (кнопка fx возле строки формул).
Украинская версия функции ЧислоСловоВалюта для перевода сумм в гривны находиться в следующем файле:
Теперь вы можете быстро перевести сумму в слова прописью. Чтобы воспользоваться готовым решением рекомендуем скачать пример числа прописью в Excel. Данный файл содержит уже готовую пользовательскую функцию и VBA-код макроса, который доступен в модуле из редактора.