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

41 комментарий :

  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. Отличное решение! Спасибо!

    ОтветитьУдалить
  20. Анонимный30 июня 2017 г., 17:57

    Супер! Работет!!! Огромное спасибо!

    ОтветитьУдалить
  21. Анонимный31 июля 2017 г., 16:12

    а куда формулу вводить надо ?

    ОтветитьУдалить
    Ответы
    1. В ту ячейку, где Вы хотите видеть результат (т.е. сумму прописью).

      Удалить
  22. Работает только на суммах до 1000(((( Что я не так сделала? На сумму от 1000 не реагирует, остается предыдущая сумма либо ноль((

    ОтветитьУдалить
    Ответы
    1. Возможно формула задана не полностью (что-то потерялось при копировании). Скачайте пример файла в начале статьи, посмотрите как работает там и скопируйте формулу оттуда.

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

    ОтветитьУдалить
  24. Добрый день. Подскажите как исправить валюту. Мне надо чтобы прописывалось "российский рубль". спасибо)

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Как вариант, замените в формуле текст:
      рубл"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A1;n0);11;2)-11;100);9);10);{0;"ь ":1;"я ":4;"ей "};2)
      на текст:
      российск"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(A1;n0);11;2)-11;100);9);10);{0;"ий рубль ":1;"их рубля ":4;"их рублей "};2)

      Удалить
  25. Здравствуйте. Есть небольшая задачка- необходимо записать число с давольно высокой арифметической точностью прописью. Числа в основном меньше 1, но есть и тысячи. Конкретное колличество знаков сказать не могу, но примерно до 1 миллионной. Пропись должна иметь вид Ноль целых сто девяносто шесть миллионных грамма или семь тысяч сриста пятьдесят шесть тысяч целых .... миллионных грамма. Ткните носом как это сделать)

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