Как правило, в финансовых документах довольно часто указывают сумму не только в виде числа, но и прописью. Вручную это делать сложно и долго. Гораздо проще автоматически преобразовать все значения при помощи функций. В данной статье мы рассмотрим, как в Excel сделать сумму прописью.
Для начала нужно открыть Эксель и написать какое-нибудь число.
Для того чтобы его перевести в текстовый вид, нужно скачать дополнительный макрос, написанный на vba. Данная функция по умолчанию в редакторе отсутствует.
Num2Text
Прежде чем приступить к дальнейшим действиям, вам необходимо скачать данную надстройку. Официального сайта в этом случае нет. Таких модулей в интернете довольно много.
Будьте внимательны. Скачивайте только те файлы, у которых расширение xla, а не exe, поскольку во втором случае там могут быть вирусы.
После этого нужно сделать следующее:
- Зайдите в пункт меню «Файл».
- В появившемся окне перейдите в раздел «Параметры».
- Затем нажимаем на пункт «Надстройки».
- Кликните на кнопку «Перейти» (предварительно убедитесь, что рядом выбран пункт «Надстройки Excel»).
- После этого у вас появится окно, в котором можно вставить дополнительные файлы, чтобы редактор «узнал» новые функции. Нажмите на кнопку «Обзор».
- После этого укажите заранее скаченный файл и кликните «Открыть».
- Затем нужно поставить галочку около нового пункта и нажать на «ОК».
На этом этап подготовки завершен. Теперь Excel сможет делать перевод цифр в текстовый вид.
Создание простой формулы
Для того чтобы увидеть возможности новых функций, нужно сделать следующее:
- Перейдите на любую соседнюю ячейку. Кликните на иконку «Fx».
Абсолютные ссылки в Эксель
- В появившемся окне в разделе категории выберите пункт «Полный алфавитный перечень».
- Найдите там функцию «Сумма_прописью». Выберите её и нажмите на «OK».
- После этого появится окно, в котором нужно указать аргументы функции. Следует указать ячейку, значение которой должно писаться прописью. Для этого достаточно кликнуть на неё.
- Сразу после этого адрес ячейки подставится автоматически. Для продолжения кликните «OK».
- В результате этого вы увидите вашу сумму прописью. Вплоть до копеек. Благодаря этому можно переводить даже дробные числа.
- Попробуйте изменить ваше значение на любое другое. Сумма прописью изменится мгновенно.
Сумма ячеек
В финансовых отчетах необходимо считать сумму различных ячеек или столбцов. В этом случае формула немного усложняется. Рассмотрим, как это сделать.
- Проставьте цифры в 4 ячейки подряд.
- Кликните на ячейку, в которой вы работали до этого. Скопируйте формулу при помощи сочетания клавиш Ctrl+C.
- После этого перейдите в другую ячейку, чтобы создать новую формулу. Активируйте строку ввода уравнений.
- Нажмите на клавиатуре на кнопки Ctrl+V.
- Удаляем аргумент функции, то есть «C4» в нашем случае. Вместо этого в скобке пишем обычную формулу суммы, которая будет считать диапазон наших ячеек.
=Сумма_прописью(СУММ(A4:D4))
- В результате этого должно получиться следующее.
- Нажмите на клавиатуре на кнопку Enter. В итоге вы увидите, что сумма указанного диапазона значений отобразилась прописью.
У данного модуля есть большой минус – вы сможете работать только с российкими рублями. То есть, не получится сделать сумму прописью для узбекских сумов, украинских гривен, казахских тенге, американских долларов, белорусских рублей, английских фунтов и так далее. Язык поменять не выйдет. Для таких случаев существует альтернативный метод.
Sumprop
Установка данного модуля происходит точно так же, как в описанном выше способе, только на этот раз выбираем другой файл.
Как убрать режим совместимости в Эксель
- Ставим галочку возле новой надстройки и нажимаем на кнопку «OK».
После этого в вашем редакторе появится еще несколько новых функций. Рассмотрим их всех.
Работа с формулами
Для этого нужно сделать следующие действия.
- Первым делом создадим наглядную таблицу. В одном столбце у нас будут цифры, во втором – результат. Для демонстрации возможностей будем использовать дробные числа.
- Переходим первую пустую ячейку и нажимаем на кнопку «Fx».
В появившемся окне выберите категорию «Определенные пользователем». Новые функции будут именно там.
Будем вставлять поочередно. Именно в той хронологии, как они идут в списке.
СуммаПрописью
- Выберите соответствующий пункт и нажмите на кнопку «OK».
Справка нам подсказывает, что эта функция для рублей.
- В появившемся окне нужно вставить аргумент. Для этого достаточно кликнуть на ячейку с числом.
- Благодаря этому адрес подставится автоматически. Для продолжения нажмите на кнопку «OK».
- В результате этого вы увидите следующее.
СуммаПрописьюГривны
- Выделяем следующую строчку и кликаем на иконку вставки функции.
- На этот раз выбираем вторую функцию. Для вставки нажимаем на кнопку «OK».
- Затем кликаем на следующее число.
- После автоматической подстановки нажмите на кнопку «OK».
- Результат будет следующим.
СуммаПрописьюДоллары
- Переходим на третью строчку и кликаем на иконку «Fx».
- Выбираем «долларовую» функцию и нажимаем на «OK».
- Указываем соответствующий аргумент.
- Благодаря этому целое число отображается в долларах, а дробное – в центах.
СуммаПрописьюЕвро
- Переходим на четвертую строчку и вызываем окно вставки уравнения.
- Указываем функцию для «Евро».
- Кликаем на соответствующий аргумент.
- Результат будет следующим.
Обратите внимание на то, что количество центов (копеек) выводится всегда. Даже если их 0.
ЧислоПрописью
Преимущество данного модуля в том, что вы можете осуществить преобразование без дополнительных денежных приставок.
- Перейдите на последнюю строчку и нажмите на иконку «Fx».
Сводные таблицы в Эксель
- Выберите последнюю функцию.
- В качестве аргумента укажите последнее оставшееся число.
- Для вставки нажмите на кнопку «OK».
В результате проделанных действий, мы увидим следующую таблицу.
Обратите внимание на то, что во всех ячейках сумма прописью указывается с большой буквы и только число – с маленькой.
Платные модули
Описанные выше надстройки были бесплатными. В интернете можно найти и более мощные инструменты, но за определенную плату.
- На сайте PlanetaExcel продается надстройка PLEX.
- Нам показывают, что в ней находится огромная библиотека формул.
- На момент публикации статьи стоимость надстройки оценивается в 795 рублей.
- Разумеется, они предоставляют возможность бесплатного тестирования, но количество запусков ограничено.
Заключение
Прочитав данную инструкцию, вы должны были понять, как настроить редактор Excel, чтобы можно был выводить сумму прописью. Если у вас что-то не получается, возможны следующие варианты:
- вы скачали не тот файл;
- вы не добавили эту надстройку в Эксель;
- вы выделяете не те ячейки;
- используете не те функции;
- допускаете опечатку при подсчете суммы различных значений.
Видеоинструкция
Если у вас остались какие-нибудь вопросы или вы так и не смогли найти причину своих проблем, рекомендуется посмотреть видеоролик, в котором приводятся дополнительные комментарии к описанным выше инструкциям.
Источник: https://os-helper.ru/excel/summa-propisyu.html
Сумма прописью в Excel
Во многих документах мы можем видеть строку, в которой требуется указать сумму не в числовом виде, а прописью. Это защищает отчетность от подделки, ведь цифру легко переправить. А вот длинную запись незаметно исправить не так-то просто. Сумма прописью в Excel доступна только в виде надстроек или с использованием формул. Встроенного инструмента не имеется.
Как в Excel сделать сумму прописью
Скачать файл надстройки
Чтобы получить эту возможность, вы можете скачать надстройку «Сумма прописью» в Excel бесплатно на нашем сайте.
Разместите его в любом удобном каталоге и подключите. Для этого следует пройти следующие этапы:
- Запустите Excel и нажмите «Файл».
- Щелкните на параметры.
- Далее переходите в надстройки.
И кликайте по кнопке «Перейти».
- Чтобы подгрузить скачанный файл жмите «Обзор».
- И ищите его в папке, куда сохраняли. Вам предложат скопировать файл в папку надстроек, рекомендуем согласиться. Так Excel всегда будет видеть эту надстройку и вы легко найдете функцию для суммы прописью.
- Нужная библиотека Excel подключена. Галочка означает, что ее можно использовать. Жмите ОК.
- Нужные действия реализованы в виде функции СУММА_ПРОПИСЬЮ. Чтобы применить ее, введите в ячейку A1 число и установите указатель рядом, на B1. Нажмите значок Fx около строки формул.
- И в полном алфавитном перечне найдите функцию СУММА_ПРОПИСЬЮ. Рекомендуем начать печатать название. В таком случае Excel сам перемотает ближе к нужному разделу.
- Жмите ОК и в окне мастера укажите адрес A1. Можно просто мышкой кликнуть по ячейке.
- После нажатия ОК вы увидите сумму прописью.
Эта функция «сумма_прописью» работает в Excel 2007, 2010, 2016
Данная надстройка работает только для рублей. Если вам нужна другая валюта, скачайте еще файл sumprop.xla. Действуя по аналогии подключите надстройку для получения суммы прописью в Excel.
Для проверки введем в ячейке A3 произвольное число. Пусть это будет десятичная дробь. Также вызовем окно вставки функций и найдем новые функции для вывода суммы прописью. Как видите, нам стали доступны:
- Сумма Прописью
- Сумма Прописью Доллары
- Сумма Прописью Евро
- Сумма Прописью Евро
Переведем число в евро. Выбираем соответствующую функцию и кликаем по ячейке с числом. После нажатия ОК получаем результат.
С помощью этих инструментов преобразование числа в текст прописью в Excel выполняется очень просто. Если по каким-то причинам вы не хотите устанавливать их в Excel, можно воспользоваться другим способом.
Формула суммы прописью в Excel — как написать число
- Для вывода числа прописью в Excel можно создать формулу. Приведем пример:
- ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(A3;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A3;n0);2;1)+1;ПСТР(ТЕКСТ(A3;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A3;n0);1;3);»миллиард»&ВПР(ПСТР(ТЕКСТ(A3;n0);3;1)*И(ПСТР(ТЕКСТ(A3;n0);2;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A3;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A3;n0);5;1)+1;ПСТР(ТЕКСТ(A3;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A3;n0);4;3);»миллион»&ВПР(ПСТР(ТЕКСТ(A3;n0);6;1)*И(ПСТР(ТЕКСТ(A3;n0);5;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A3;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(A3;n0);8;1)+1;ПСТР(ТЕКСТ(A3;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A3;n0);7;3);ВПР(ПСТР(ТЕКСТ(A3;n0);9;1)*И(ПСТР(ТЕКСТ(A3;n0);8;1)-1);тыс;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A3;n0);10;1)+1)&ИНДЕКС(ЕСЛИ(-ПСТР(ТЕКСТ(A3;n0);14;6);n1x;n0x);ПСТР(ТЕКСТ(A3;n0);11;1)+1;ПСТР(ТЕКСТ(A3;n0);12;1)+1));»z»;» «)&ЕСЛИ(ОТБР(ТЕКСТ(A3;n0));;»Ноль «)&ЕСЛИ(-ПСТР(ТЕКСТ(A3;n0);14;6);ЕСЛИ(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A3;n0);11;2)-11;100);9);10);»целых «;»целая «)&ПОДСТАВИТЬ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(A3;n0);14;6)/10^5+1)&ИНДЕКС(n1x;ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)/10^4;10)+1;ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)/1000;10)+1)&ЕСЛИ(ЦЕЛОЕ(ПСТР(ТЕКСТ(A3;n0);14;6)/1000);ВПР(ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)/1000;10)*(ОСТАТ(ЦЕЛОЕ(ПСТР(ТЕКСТ(A3;n0);14;6)/10^4);10)1);тыс;2);»»)&ИНДЕКС(n_4;ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)/100;10)+1)&ИНДЕКС(n1x;ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)/10;10)+1;ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6);10)+1);»z»;» «)&ИНДЕКС(доля;ДЛСТР(ПСТР(ТЕКСТ(A3;n0);14;6));(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A3;n0);14;6)-11;100);9);10)>0)+1);)
- Для ее использования вам необходимо указать несколько массивов Excel:
- n_1 ={«»;»одинz»;»дваz»;»триz»;»четыреz»;»пятьz»;»шестьz»;»семьz»;»восемьz»;»девятьz»}
- n_2 ={«десятьz»;»одиннадцатьz»;»двенадцатьz»;»тринадцатьz»;»четырнадцатьz»;»пятнадцатьz»;»шестнадцатьz»;»семнадцатьz»;»восемнадцатьz»;»девятнадцатьz»}
- n_3 ={«»:1:»двадцатьz»:»тридцатьz»:»сорокz»:»пятьдесятz»:»шестьдесятz»:»семьдесятz»:»восемьдесятz»:»девяностоz»}
- n_4 ={«»;»стоz»;»двестиz»;»тристаz»;»четырестаz»;»пятьсотz»;»шестьсотz»;»семьсотz»;»восемьсотz»;»девятьсотz»}
- n_5 ={«»;»однаz»;»двеz»;»триz»;»четыреz»;»пятьz»;»шестьz»;»семьz»;»восемьz»;»девятьz»}
- n0 =»000000000000″&ПСТР(1/2;2;1)&»0#####»
- n0x =ЕСЛИ(n_3=1;n_2;n_3&n_1)
- n1x =ЕСЛИ(n_3=1;n_2;n_3&n_5)
- доля ={«десятая»;»десятых»:»сотая»;»сотых»:»тысячная»;»тысячных»:»десятитысячная»;»десятитысячных»:»стотысячная»;»стотысячных»:»миллионная «;»миллионных»}
- мил ={0;»овz»:1;»z»:2;»аz»:5;»овz»}
- тыс ={0;»тысячz»:1;»тысячаz»:2;»тысячиz»:5;»тысячz»}
Просто скопируйте эту таблицу на лист 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 вставьте новый модуль. В него добавьте следующий код.
Function SUMMPROPIS(n As Double) As String
Dim Chis1, Chis2, Chis3, Chis4, Chis5 As Variant
Chis1 = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Chis2 = Array(«», «десять «, «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят», «девяносто «)
Chis3 = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «)
Chis4 = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Chis5 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «)
If n 0 Then thous_txt = Chis4(thous) & «тысяч »
Case 1
thous_txt = Chis4(thous) & «тысяча »
Case 2, 3, 4
thous_txt = Chis4(thous) & «тысячи »
Case 5 To 9
thous_txt = Chis4(thous) & «тысяч »
End Select
If desthous = 0 And thous = 0 And hundthous 0 Then hundthous_txt = hundthous_txt & » тысяч »
eee:
hund_txt = Chis3(hund)
Select Case des
Case 1
cifr_txt = Chis5(cifr)
GoTo rrr
Case 2 To 9
des_txt = Chis2(des)
End Select
cifr_txt = Chis1(cifr)
rrr:
SUMMPROPIS = desmil_txt & mil_txt & hundthous_txt & desthous_txt & thous_txt & hund_txt & des_txt & cifr_txt
End Function
Private Function Retclass(M, I)
Retclass = Int(Int(M — (10 ^ I) * Int(M / (10 ^ I))) / 10 ^ (I — 1))
End Function
Теперь эту функцию можно вызвать обычным путем и получить сумму прописью. Она находится в разделе функций Excel «Определенные пользователем». В качестве аргумента укажите адрес с числом.
Перевод в сумму прописью осуществляется только для целых чисел. Дробное число требует дополнительной формулы: =SUMMPROPIS(A7)&» руб. «&ТЕКСТ((A7-ЦЕЛОЕ(A7))*100;»00″)&» коп.»
Вместо ячейки A7 подставляйте свой адрес. Копейки в таком исполнении будут выводиться в виде числа. Мы рассмотрели все способы представления суммы прописью в Excel. Выбирайте для себя самый удобный и пользуйтесь с удовольствием!
Источник: https://WindowsTips.ru/summa-propisyu-v-excel
Сумма прописью в Excel 2007, 2010, 2016 – где бесплатно скачать надстройку для преобразования числа в текст, формула в Эксель
При внесении информации в разные экономические документы иногда по регламенту требуется вписать сумму прописью. Если речь идет о единичных случаях, то проблем не возникнет. Но при заполнении большого числа документов на это уходит больше времени, чем при заполнении цифрами. Выясним, как установить инструмент, позволяющий автоматически вписать сумму прописью в Excel.
Зачем это нужно
Число, указанное прописью, применяют во многих бухгалтерских документов. Если фирма использует специальные программы, например 1С для формирования отчетности, то такой потребности не возникнет.
Но когда нужно ввести сумму прописью при заполнении бумаг в обычных программах, могут возникнуть проблемы.
Это требует большого количества времени, поэтому встает вопрос об автоматизации процесса.
Кроме специалистов, которым на работе нужно указать буквенное значение числа, обычным пользователям тоже может потребоваться такой ввод для своих личных целей.
Использование надстройки
В Экселе отсутствует стандартный инструмент, позволяющий добавить сумму прописью, для этого применяют специальные надстройки.
Одна из популярных надстроек — NUM2TEXT, которую можно скачать бесплатно. Она меняет цифровые значения на буквенные с помощью Мастера функций.
Предварительно необходимо загрузить и сохранить на жестком диске компьютера файл NUM2TEXT.xla.
Преобразовать числа в текст прописью в Excel 2007, 2010, 2016, можно следующим образом:
- Запустить Эксель и зайти в раздел «файл».
- Кликнуть на строку «параметры».
- Откроется окошко, в котором необходимо перейти в пункт «надстройки».
- Внизу в строке «управление» поставить значение «надстройки Эксель» и щелкнуть на элемент «перейти».
- Появится маленькое окно, в котором требуется нажать на кнопку «обзор».
- В появившемся окошке найти предварительно скачанный файл, выделить его и кликнуть на ОК.
- Инструмент появился в доступных надстройках. Поставить галочку в квадратике рядом с ним и снова щелкнуть мышкой на ОК.
- Теперь нужно проверить функционирование надстройки. Для этого в любой ячейке ввести произвольное число.
- Выделить любой другой элемент и кликнуть на ярлычок «вставить функцию», находящийся с левой стороны от строчки формулы.
- Открыть мастер функций, в предложенном списке найти «сумма прописью». Выделить ее и кликнуть на ОК.
- В окошке «аргументы функции» в строке «сумма» вписать нужное числовое значение. Оно сразу отобразится в указанной области в рублях и копейках прописью.
- В строчке можно указать адрес любой ячейки. Возможны 2 варианта – ручным способом через запись координат или простым нажатием на нее в тот момент, когда указатель мышки стоит в поле «сумма». Щелкнуть на ОК.
- Далее цифра, введенная в ячейку, отобразится в стоимостном выражении прописью в той клетке, где стоит формула.
Способы сортировки данных в Microsoft Excel
Использование формулы
Опцию записи значения прописью можно запустить вручную, не прибегая к помощи мастера функций. Но для преобразования числа в текст сначала нужно составить формулу. Примеры различных формул под разные требования можно найти на специализированном форуме.
- Синтаксис обычно такой: Сумма_прописью (сумма) или Сумма_прописью (координаты_ячейки).
- Если пользователь напечатает в ячейке формулу =Сумма_прописью (74) и нажмет на клавишу ENTER, то в ней автоматически появится текст «семьдесят четыре рубля 00 копеек» (с копейками или без зависит от самой формулы).
- Если пользователь напечатает в ячейке =Сумма прописью (А3), то цифра, стоящая в клетке А3, отобразится в запрашиваемом формате.
Скрипт VBA
Далее представлена подготовленная функция на VBA для пользователей, которая переведет цифровой показатель от 0 до 9 999 999 в его текстовое выражение, т.е. в сумму прописью. Сначала ее потребуется занести в книгу. Алгоритм действий:
- нажимают одновременно на ALT+F11, откроется редактор Visual Basic;
- добавляют новый пустой модуль посредством меню Insert – Module;
- копируют и добавляют туда скрипт:
Function СУММАПРОПИСЬЮ(n As Double) As String
Dim Nums1, Nums2, Nums3, Nums4 As Variant
Nums1 = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Nums2 = Array(«», «десять «, «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят «, «девяносто «)
Nums3 = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «)
Nums4 = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Nums5 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «)
If n 0 Then tys_txt = Nums4(tys) & «тысяч »
Case 1
tys_txt = Nums4(tys) & «тысяча »
Case 2, 3, 4
tys_txt = Nums4(tys) & «тысячи »
Case 5 To 9
tys_txt = Nums4(tys) & «тысяч »
End Select
If dectys = 0 And tys = 0 And sottys 0 Then sottys_txt = sottys_txt & » тысяч »
eee:
sot_txt = Nums3(sot)
'проверяем десятки
Select Case dec
Case 1
ed_txt = Nums5(ed)
GoTo rrr
Case 2 To 9
dec_txt = Nums2(dec)
End Select
ed_txt = Nums1(ed)
rrr:
'формируем итоговую строку
СУММАПРОПИСЬЮ = decmil_txt & mil_txt & sottys_txt & dectys_txt & tys_txt & sot_txt & dec_txt & ed_txt
End Function
'вспомогательная функция для выделения из числа разрядов
Private Function Class(M, I)
Class = Int(Int(M — (10 ^ I) * Int(M / (10 ^ I))) / 10 ^ (I — 1))
End Function
Сохраняют файл и возвращаются в Excel. Затем вставляют функцию в любую клетку листа книги привычным способом — через мастер функций или просто набирают ее в ячейке вручную, а в качестве аргумента указывают ячейку с суммой.
Способы вставки таблицы из Microsoft Excel в Word
Источник: http://composs.ru/kak-v-excel-sdelat-summu-propisyu/
Как создается сумма прописью в Excel?
Добрый день уважаемый читатель!
Статья посвящена такому интересному вопросу: «Как создается сумма прописью в Excel?». Эта возможность позволяет нам всю массу информации и расчетов, которые сведены в один результат, оформить как финансовой документ, которые нельзя трактовать двузначно.
Недаром же любые банковские, юридические и финансовые документы оформляются и цифрами и буквами. Окромя всего, такие суммы не позволяют ошибаться, где же должна стоять запятая и просто отлично выглядят в сравнении с рядом цифр.
Подведя итог, скажу, что любой пользователь Excel, который работает с цифрами и временем, а также печатает документы с их использованием, очень нуждается в инструменте создающим сумму прописью.
Решить эту проблему можно несколькими способами:
- С помощью формул;
- С помощью пользовательской функции;
- С помощью VBA.
Создание сумм прописью с помощью формул
Это вполне осуществимый вариант, но очень громоздкий. Формула, которая может самостоятельно преобразовывать числа в буковки и правильно орфографически написать сумму прописью получится очень большой.
В случае, когда вы захотите упростить формулу, вам нужно будет отдельный блок формул, который буде и заниматься всеми преобразованиями.
Единственное преимущество от использования формулы, это когда вы разберетесь с ней, то самостоятельно сможете ее изменять: разрядность, валюту, склонение и т.п.
Ради общего интереса я предоставлю вам пример файла с работающей формулой, которую я нашёл на excelworld.ru, поскольку самостоятельно формульным решением задачи я не занимался, но считаю нужным предоставить весь наработанный или собранный материал. Скачать пример можно по этой ссылке.
С помощью пользовательской функции
Как мой взгляд, это самое оптимальное и простое, относительно конечно, решение поставленного вопроса. Вы просто включаете функцию и пользуетесь ее с удовольствием.
Для этого вам необходимо выполнить некоторые действия. Сначала скачиваете файл надстройки здесь. После, переносите его в папку AddIns, которую можно найти по пути C:Documents and SettingsUserApplication DataMicrosoftAddIns, только вы вместо «User» подставляете имя вашего пользователя Windows.
Еще может возникнуть проблема в отображении папки «Application Data», она может быть скрыта, тогда вам в настройках «Свойства папки» нужно активировать пункт «Показать скрытые файлы и папки». 
Теперь собственно и приступим к включении этой надстройки. В Excel выбираем «Файл» — «Параметры» — «Надстройки». 
В диалоговом окне «Настройки» ставим галочку напротив включаемой надстройки «Сумма Прописью» и нажимаем «ОК». 
Следующим шагом, ставим курсор на нужную ячейку и вызываем «Мастер функций». В разделе «Категория» выбираем пункт «Определенные пользователем» и теперь весть список встроенных функций по написанию сумм прописью нам доступен. Кстати как видите, доступен даже выбор функций в разрезе валют. Сразу замечу, что надстроек бывает много, есть и более расширенные, но эта меня полностью устраивает, вот я ее и описываю. 
В результате мы получаем возможность получить следующие данные: 
С помощью VBA
Вот еще один способ на любителя. Конечно создать сумму прописью в Excel с помощью макроса не самый распространённый способ, но им достаточно часто пользовались и пользуются до сих пор. Большой сложности он не представляет, вам нужно:
- Комбинацией горячих клавиш ALT+F11 вызвать редактор Visual Basic;
- С помощью меню «Insert» — «Module», создать пустой новый модуль;
- Скопировать предоставленный код макроса в тело модуля.
Получается готовая пользовательская функция VBA, которая умеет превращать любую сумму от 0 до 9 999 999 в ее текстовое воплощение, создается сумма прописью.
Код нужного макроса выглядит так:
Function СУММАПРОПИСЬЮ(n As Double) As String
Dim Nums1, Nums2, Nums3, Nums4 As Variant
Nums1 = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Nums2 = Array(«», «десять «, «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, _
«восемьдесят «, «девяносто «)
Nums3 = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, _
«восемьсот «, «девятьсот «)
Nums4 = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Nums5 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «,
«пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «)
If n <= 0 Then
СУММАПРОПИСЬЮ = «ноль»
Exit Function
End If
'разделяем число на разряды, используя вспомогательную функцию Class
ed = Class(n, 1)
dec = Class(n, 2)
sot = Class(n, 3)
tys = Class(n, 4)
dectys = Class(n, 5)
sottys = Class(n, 6)
mil = Class(n, 7)
decmil = Class(n, 8)
'проверяем миллионы
Select Case decmil
Case 1
mil_txt = Nums5(mil) & «миллионов »
GoTo www
Case 2 To 9
decmil_txt = Nums2(decmil)
End Select
Select Case mil
Case 1
mil_txt = Nums1(mil) & «миллион »
Case 2, 3, 4
mil_txt = Nums1(mil) & «миллиона »
Case 5 To 20
mil_txt = Nums1(mil) & «миллионов »
End Select
www:
sottys_txt = Nums3(sottys)
'проверяем тысячи
Select Case dectys
Case 1
tys_txt = Nums5(tys) & «тысяч »
GoTo eee
Case 2 To 9
dectys_txt = Nums2(dectys)
End Select
Select Case tys
Case 0
If dectys > 0 Then tys_txt = Nums4(tys) & «тысяч »
Case 1
tys_txt = Nums4(tys) & «тысяча »
Case 2, 3, 4
tys_txt = Nums4(tys) & «тысячи »
Case 5 To 9
tys_txt = Nums4(tys) & «тысяч »
End Select
If dectys = 0 And tys = 0 And sottys 0 Then sottys_txt = sottys_txt & » тысяч »
eee:
sot_txt = Nums3(sot)
'проверяем десятки
Select Case dec
Case 1
ed_txt = Nums5(ed)
GoTo rrr
Case 2 To 9
dec_txt = Nums2(dec)
End Select
ed_txt = Nums1(ed)
rrr:
'формируем итоговую строку
СУММАПРОПИСЬЮ = decmil_txt & mil_txt & sottys_txt & dectys_txt & tys_txt & sot_txt & dec_txt & ed_txt
End Function
'вспомогательная функция для выделения из числа разрядов
Private Function Class(M, I)
Class = Int(Int(M — (10 ^ I) * Int(M / (10 ^ I))) / 10 ^ (I — 1))
End Function
|
|
Сохраняете внесенные изменения и теперь можно пользоваться новой функцией, как и прочими, она отображается в «Мастере функций» в категории «Определенные пользователем» или же просто вручную ввести ее в ячейке указав какую сумму необходимо сделать прописной.
Но как видите, макрос пишет прописью суммы, только целые числа, а вот для получения еще и копеек, эту конструкцию нужно доработать использованием следующей функции:
=СУММАПРОПИСЬЮ(A3)&» руб. «&ТЕКСТ((A3-ЦЕЛОЕ(A3))*100;»00″)&» коп.»
Приставку «Module2» в моей формуле можете опустить, так как таким образом, программа разделяет 2 похожие формулы, одна от установленных надстроек, а вторая от макроса.
А на этом у меня всё! Я очень надеюсь, что всё о том, как ваши цифры сделать прописными вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Что такое ограбление банка по сравнению с основанием банка?Бертольт Брехт
Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!
Источник: http://topexcel.ru/kak-sozdaetsya-summa-propisyu-v-excel/
Excel. Сумма прописью без макросов и надстроек
Дано: Excel c числами и суммами в рублях в ячейках.
Задача: написать данные числа прописью без использования макросов и каких-либо надстроек программы Excel, т.е. написать число прописью при помощи стандартных формул.
Пример файла excel с таким решением можно скачать здесь.
См. также Сумма прописью онлайн (если просто нужно получить результат).
Что означает прописью?
Данный файл производит следующие манипуляции с числами:
1. Формула 1 преобразовывает число в сумму прописью следующего формата:
«0 (пропись) рублей 00 копеек»
2. Формула 2 преобразовывает число в сумму прописью следующего формата:
«Пропись рублей 00 копеек»
3. Формула 3 просто переводит число в текст: число = пропись
Вот формулы, которые пишут сумму прописью:
1) Формула 1:
=ПОДСТАВИТЬ(ТЕКСТ(ОТБР(A1;0);»# ##0_ «) & «(» & ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);2;1)+1;ПСТР(ТЕКСТ(A1;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);1;3);»миллиард»&ВПР(ПСТР(ТЕКСТ(A1;n0);3;1)*И(ПСТР(ТЕКСТ(A1;n0);2;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);5;1)+1;ПСТР(ТЕКСТ(A1;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);4;3);»миллион»&ВПР(ПСТР(ТЕКСТ(A1;n0);6;1)*И(ПСТР(ТЕКСТ(A1;n0);5;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(A1;n0);8;1)+1;ПСТР(ТЕКСТ(A1;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);7;3);ВПР(ПСТР(ТЕКСТ(A1;n0);9;1)*И(ПСТР(ТЕКСТ(A1;n0);8;1)-1);тыс;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);10;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);11;1)+1;ПСТР(ТЕКСТ(A1;n0);12;1)+1));»z»;» «)&ЕСЛИ(ОТБР(ТЕКСТ(A1;n0));»»;»Ноль «)&») рубл»&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A1;n0);11;2)-11;100);9);10);{0;»ь «:1;»я «:4;»ей «};2)&ПРАВСИМВ(ТЕКСТ(A1;n0);2)&» копе»&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(A1;n0);2)-11;100);9);10);{0;»йка»:1;»йки»:4;»ек»};2);» )»;»)»)
2) Формула 2
=ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);2;1)+1;ПСТР(ТЕКСТ(A1;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);1;3);»миллиард»&ВПР(ПСТР(ТЕКСТ(A1;n0);3;1)*И(ПСТР(ТЕКСТ(A1;n0);2;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);5;1)+1;ПСТР(ТЕКСТ(A1;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);4;3);»миллион»&ВПР(ПСТР(ТЕКСТ(A1;n0);6;1)*И(ПСТР(ТЕКСТ(A1;n0);5;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(A1;n0);8;1)+1;ПСТР(ТЕКСТ(A1;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);7;3);ВПР(ПСТР(ТЕКСТ(A1;n0);9;1)*И(ПСТР(ТЕКСТ(A1;n0);8;1)-1);тыс;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);10;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);11;1)+1;ПСТР(ТЕКСТ(A1;n0);12;1)+1));»z»;» «)&ЕСЛИ(ОТБР(ТЕКСТ(A1;n0));»»;»Ноль «)&»рубл»&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A1;n0);11;2)-11;100);9);10);{0;»ь «:1;»я «:4;»ей «};2)&ПРАВСИМВ(ТЕКСТ(A1;n0);2)&» копе»&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(A1;n0);2)-11;100);9);10);{0;»йка»:1;»йки»:4;»ек»};2)
3) Формула 3
=ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);2;1)+1;ПСТР(ТЕКСТ(A1;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);1;3);»миллиард»&ВПР(ПСТР(ТЕКСТ(A1;n0);3;1)*И(ПСТР(ТЕКСТ(A1;n0);2;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);5;1)+1;ПСТР(ТЕКСТ(A1;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);4;3);»миллион»&ВПР(ПСТР(ТЕКСТ(A1;n0);6;1)*И(ПСТР(ТЕКСТ(A1;n0);5;1)-1);мил;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(A1;n0);8;1)+1;ПСТР(ТЕКСТ(A1;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(A1;n0);7;3);ВПР(ПСТР(ТЕКСТ(A1;n0);9;1)*И(ПСТР(ТЕКСТ(A1;n0);8;1)-1);тыс;2);»»)&ИНДЕКС(n_4;ПСТР(ТЕКСТ(A1;n0);10;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(A1;n0);11;1)+1;ПСТР(ТЕКСТ(A1;n0);12;1)+1));»z»;» «)&ЕСЛИ(ОТБР(ТЕКСТ(A1;n0));»»;»Ноль «)
При необходимости формулу можно подогнать под нужный формат.
Как использовать данную формулу?
Вы можете взять файл примера и внести в него свои данные, чтобы получить результат.
Если же у Вас уже есть готовый файл с заполненными данными и Вам необходимо добавить возможность написания суммы прописью, то чтобы формула заработала, необходимо сделать некоторые подготовительные действия, а именно добавить справочники, которые использует формула. В данном примере справочники заданы как именованные диапазоны (это решение хорошо тем, что пользователь эти диапазоны не видит на листах и соответственно не сможет лишний раз что-то испортить). Итак, нам необходимо сделать следующие справочники:
Имя диапазона: n_1
Диапазон: ={«»;»одинz»;»дваz»;»триz»;»четыреz»;»пятьz»;»шестьz»;»семьz»;»восемьz»;»девятьz»}
Имя диапазона: n_2
Диапазон: ={«десятьz»;»одиннадцатьz»;»двенадцатьz»;»тринадцатьz»;»четырнадцатьz»;»пятнадцатьz»;»шестнадцатьz»;»семнадцатьz»;»восемнадцатьz»;»девятнадцатьz»}
Имя диапазона: n_3
Диапазон: ={«»:1:»двадцатьz»:»тридцатьz»:»сорокz»:»пятьдесятz»:»шестьдесятz»:»семьдесятz»:»восемьдесятz»:»девяностоz»}
Имя диапазона: n_4
Диапазон: ={«»;»стоz»;»двестиz»;»тристаz»;»четырестаz»;»пятьсотz»;»шестьсотz»;»семьсотz»;»восемьсотz»;»девятьсотz»}
Имя диапазона: n_5
Диапазон: ={«»;»однаz»;»двеz»;»триz»;»четыреz»;»пятьz»;»шестьz»;»семьz»;»восемьz»;»девятьz»}
Имя диапазона: n0
Диапазон: =»000000000000″&ПСТР(1/2;2;1)&»00″
Имя диапазона: n0x
Диапазон: =ЕСЛИ(n_3=1;n_2;n_3&n_1)
Имя диапазона: n1x
Диапазон: =ЕСЛИ(n_3=1;n_2;n_3&n_5)
Имя диапазона: мил
Диапазон: ={0;»овz»:1;»z»:2;»аz»:5;»овz»}
Имя диапазона: тыс
Диапазон: ={0;»тысячz»:1;»тысячаz»:2;»тысячиz»:5;»тысячz»}
Чтобы создать справочник таких диапазонов, переходим на вкладку «Формулы» — «Диспетчер имен«:
В открывшемся окне нажимаем кнопку «Создать» и в форме вводим данные первого диапазона из таблицы:
Далее таким же образом вводим все остальные диапазоны из таблицы:
Всё, теперь можно использовать указанные выше формулы: вставьте формулу в ячейку, где необходимо отобразить число прописью, а в самой формуле (можно через любой текстовый редактор) замените адрес «A1» на адрес той ячейки, где располагается исходное число.
Ella S.
Основано на материалах форума excelworld.ru/forum/3-9902
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.
Источник: https://www.e-du.ru/2016/05/excel-summa-propisyu.html
Загрузка...
