Сумма прописью в excel без VBA и SMS

Всем привет. Мы в Magneex очень любим заниматься автоматизацией внутренних процессов, в частности я сам, придумывать новые бизнес-правила, бизнес-процессы и переносить их на нашу систему автоматизации Planfix.

Если к сути, то сейчас я занимаюсь автоматизацией процессов контроля дебиторской задолженности у клиентов, а именно над выставлением счетов и актов. В полях этих документов указывается наименование услуг, которые мы оказываем, юридические данные клиентов и прочую ерунду, которую очень легко подставить с помощью переменных в формируемый документ. Но есть очень не тривиальное поле, которое необходимо заполнить автоматически «Сумма прописью»
в самом конце этих документов. После поиска на просторах интернета было найдено множество решений на VBA, но в нашем случае это не подходит, так как генерация файла происходит на серверной стороне, а политики безопасности не дают обрабатывать VBA скрипты, и это правильно.
В общем решение было найдено великой и могущей командой ПФ, и да не извергнут на меня небеса, я хочу с вами им поделится.

И так решение:
В поле, которое нам необходимо разместить сумму прописью, вставляем следующую демоническую формулу:
=ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(цуу;ПСТР(ТЕКСТ(AF24;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(AF24;n0);2;1)+1;ПСТР(ТЕКСТ(AF24;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(AF24;n0);1;3);"миллиард"&ВПР(ПСТР(ТЕКСТ(AF24;n0);3;1)*И(ПСТР(ТЕКСТ(AF24;n0);2;1)-1);мил;2);"")&ИНДЕКС(цуу;ПСТР(ТЕКСТ(AF24;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(AF24;n0);5;1)+1;ПСТР(ТЕКСТ(AF24;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(AF24;n0);4;3);"миллион"&ВПР(ПСТР(ТЕКСТ(AF24;n0);6;1)*И(ПСТР(ТЕКСТ(AF24;n0);5;1)-1);мил;2);"")&ИНДЕКС(цуу;ПСТР(ТЕКСТ(AF24;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(AF24;n0);8;1)+1;ПСТР(ТЕКСТ(AF24;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(AF24;n0);7;3);ВПР(ПСТР(ТЕКСТ(AF24;n0);9;1)*И(ПСТР(ТЕКСТ(AF24;n0);8;1)-1);тыс;2);"")&ИНДЕКС(цуу;ПСТР(ТЕКСТ(AF24;n0);10;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(AF24;n0);11;1)+1;ПСТР(ТЕКСТ(AF24;n0);12;1)+1));"z";" ")&ЕСЛИ(ОТБР(ТЕКСТ(AF24;n0));"";"Ноль ")&"рубл"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(AF24;n0);11;2)-11;100);9);10);{0;"ь ":1;"я ":4;"ей "};2)&ПРАВСИМВ(ТЕКСТ(AF24;n0);2)&" копе"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(AF24;n0);2)-11;100);9);10);{0;"йка":1;"йки":4;"ек"};2)

В формуле есть ссылка на ячейку «AF24» ее вам необходимо заменить на ту ячейку в которой мы будем преобразовывать прописью число.

Но это еще не все, на панели управления MS Excel идем на вкладку «Формулы» ->; «Диспетчер имен»


и создаем имена с следующим содержимым:

  • Имя: n_00
    Диапазон: ="000000000000"&ПСТР(1/2;2;1)&"0#####"
  • Имя: 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_44
    Диапазон: ={"";"сто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"}
  • Имя: цуу
    Диапазон: ={"";"стоz";"двестиz";"тристаz";"четырестаz";"пятьсотz";"шестьсотz";"семьсотz";"восемьсотz";"девятьсотz"}

Если вы все сделали правильно, то должно получится, и в ячейки поля формулы вы увидите сумму написанную прописью.
Приложу сюда так же свой файл счета, где все сделано, и при необходимости вы сможете исправить сам файл.
Надеюсь статья была полезной, и вы нашли решение своей проблемы в ней.

Ссылка на файл с примером

Один комментарий к “Сумма прописью в excel без VBA и SMS”

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *