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
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.

32 комментария :

  1. Ответы
    1. Спасибо, но я на авторство формулы не претендую. Нашла решение на одном из форумов и подогнала немного формулу под нужный мне формат.

      Удалить
    2. А ссылку на исходные формулы можно опубликовать?

      Удалить
    3. Источник указан в конце статьи - excelworld.ru/forum/3-9902
      Там в форуме много разных вариаций этих формул, посмотрите, есть даже на разных языках.

      Удалить
    4. Спасибо, ссылку сразу не увидел.

      Первоначально данная формула была опубликована здесь: https://www.excelfin.ru/index.php/articles/formulas/140-excel-sumspell
      Есть вариации прописи опубликованные на planetaexcel.ru и sql.ru, приводить их не буду, т.к. окончательные варианты опубликованы на excelworld.ru: http://www.excelworld.ru/forum/3-9902-1

      Удалить
  2. Спасибо за формулу. Полезно очень.
    Можно еще после первого & вставить следующий текст, тогда будут отображаться и копейки в формате 12345-04
    &"-"&ЕСЛИ(ОКРУГЛ((A5-ОТБР(A5))*100;0)<10;СЦЕПИТЬ("0";ТЕКСТ(ОКРУГЛ((A5-ОТБР(A5))*100;0);"##"));ТЕКСТ(ОКРУГЛ((A5-ОТБР(A5))*100;0);"##"))

    ОтветитьУдалить
  3. однако...
    а sumprop.xla не проще?

    ОтветитьУдалить
    Ответы
    1. Надстройка хороша, когда функцией пользуетесь только Вы. А когда надо сделать файл с формулой для другого пользователя, который не хочет ставить никакие надстройки, то начинаешь вот так выкручиваться ;)

      Удалить
  4. спасибо, это очень полезно, когда без макросов!!!

    ОтветитьУдалить
  5. Подскажите формулу для "Календарных дней/дня/день", пожалуйста!

    ОтветитьУдалить
  6. Очень полезная формула! Спасибо огромное!!!

    ОтветитьУдалить
  7. В нужной ячейке после того как вставила формулу вышло №ЗНАЧ!Что не так то?

    ОтветитьУдалить
    Ответы
    1. Ячейка в файле, скаченном из этой статьи? Или Вы создали новый? Если новый, то создали ли Вы все указанные диапазоны в справочнике "Диспетчер имён"?

      Удалить
  8. Спасибо за ценную информацию! Все получилось!

    ОтветитьУдалить
  9. Спасибо большое, сильно помогло для урезанной версии Excel

    ОтветитьУдалить
  10. Спасибо =) очень пригодилось )))

    ОтветитьУдалить
  11. Помогите пож-та, необходимо, чтобы в ячейку поставили значение с буквой "H", допустим - 2058H, или какое-либо другое, как в примере ниже, тогда в соседней ячейке отоброзилось просто буква "H" и так же, если в ячейке стоит просто числовое значение, тогда в соседней ячейке отобразилось буквой "S" как в примере ниже. какой формулой воспользоваться?


    H 2058H
    S 123
    S 325
    S 212
    H 4052H
    H 142H

    ОтветитьУдалить
    Ответы
    1. Наверно, самое простое, это делать "проверку на число" =ЕСЛИ(ЕЧИСЛО(A1);"S";"H")

      Удалить
  12. Спасибо, работает :)

    ОтветитьУдалить
  13. Анонимный4 мая 2017 г., 1:17

    привет всем, помогите написать на англ. суму в гривне

    ОтветитьУдалить
    Ответы
    1. Вам нужно в формуле 1 или 2 вместо рублей указать гривну с соответствующими окончаниями.

      Удалить
  14. Анонимный4 мая 2017 г., 9:41

    Привет! Подскажите, ввела формулу, но программа ругается, что она очень большая, много аргументов. Что делать?

    ОтветитьУдалить
    Ответы
    1. Привет. Ошибка в файле, скаченном из этой статьи? Или Вы создали новый excel-файл? Если новый, то создали ли Вы предварительно все указанные диапазоны в справочнике "Диспетчер имён"?

      Удалить
    2. Анонимный5 мая 2017 г., 0:17

      Да, создала, все диапазоны. Excel ругается на большое количество аргументом в формуле. Можно ли сократить формулу, мне достаточно прописывать суммы начиная с тысяч.

      Удалить
    3. Да, можно. Уберите из формулы лишние части после тысяч.

      Удалить
  15. Анонимный5 мая 2017 г., 3:30

    У меня все получилось, формулу с ошибками ввела.Но возникла другая проблема. Сумма прописью прописала слитно, Двестивосемьрублей53копейки. Как это можно поправить?

    ОтветитьУдалить
    Ответы
    1. Вы удалили вставку пробелов в формуле. В исходной формуле они есть.

      Удалить
  16. Анонимный5 мая 2017 г., 11:00

    Подскажите, что конкретно я удалила?

    ОтветитьУдалить
  17. Анонимный5 мая 2017 г., 11:10

    Я формулу вручную вводила.

    ОтветитьУдалить
  18. Анонимный5 мая 2017 г., 11:29

    Всё, всем спасибо! У меня всё получилось!

    ОтветитьУдалить
  19. Отличное решение! Спасибо!

    ОтветитьУдалить