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


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

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

  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. привет всем, помогите написать на англ. суму в гривне

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

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

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

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

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

      Удалить
    4. извините, но как их сокращать?

      Удалить
    5. у меня наоборот ошибка в том, что их слишком мало..

      Удалить
    6. Если Вы не покажете, какая у Вас формула, сложно понять, что не так, может символ потерялся или скобка, из-за этого может "полететь" вся формула.

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

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

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

    ОтветитьУдалить
  17. Я формулу вручную вводила.

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

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

    ОтветитьУдалить
  20. Супер! Работет!!! Огромное спасибо!

    ОтветитьУдалить
  21. а куда формулу вводить надо ?

    ОтветитьУдалить
    Ответы
    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)

      Удалить
    2. Добрый вечер. Отличная статья.Спасибо. Только при замене на российские рубли получается российскИХ рублЕЙ при любых значениях, т е 1 российскИХ рублЕЙ. 2 российскИХ рублЕЙ и т п. Формулу просматривала. Не пойму что не так. Файл скачивала ваш. Остальное всё работает. В чем может быть причина, скажите пжлст?

      Удалить
    3. Перепроверила пример выше с российскими рублями, всё работает правильно. При замене фрагмента формулы проверьте, правильно ли заменили адрес ячейки, откуда берется сумма (т.е. в конкретном примере выше проверьте, что указано вместо адреса A1, там должен быть адрес Вашей ячейки с цифровой суммой).

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

    ОтветитьУдалить
  26. Счастлива, что все таки наткнулась на Вашу статью. Я уже скачивала, но только формулы и были мученья, если требовалось использовать формулу в др.файле. У того автора не разъяснялось про подготовительные действия. СПАСИБО ОГРОМНОЕ!!! Счастья, здоровья Вам и вашим близким, успехов в работе и хорошего дохода.

    ОтветитьУдалить
  27. Спасибо, очень пригодилось :) узнала новое, а в частности что в Excel можно делать справочники :) Спасибо, большое!!!

    ОтветитьУдалить
    Ответы
    1. Пожалуйста. Да, справочники бывают иногда полезны.

      Удалить
  28. Спасибо большое. Хочу применить для прописи количества тонн. Вопрос такой, где нужно поправить, чтобы цифры после запятой показывало 3 шт. Т.е. в рублях выглядело бы так : "Шесть рублей 705 копеек"

    ОтветитьУдалить
    Ответы
    1. 1) в диспетчере имен у имени n0 замените диапазон на: ="000000000000"&ПСТР(1/2;2;1)&"000"

      2) в формуле замените фрагмент

      ПРАВСИМВ(ТЕКСТ(A1;n0);2)&" копе"

      на

      ПРАВСИМВ(ТЕКСТ(A1;n0);3)&" копе"

      Удалить
    2. Пробовал менять. Цифр после запятой по-прежнему 2

      Удалить
    3. А точно не забыли изменить формат ячейки на показ поличестав цифр после запятой в размере 3?

      Удалить
    4. Здравствуйте! выполнил все согласно рекомендации. но возникла проблема в том, что выводит значения только двух регистров после запятой, так как исходя из формулы &ЕСЛИ(-ПРАВСИМВ(ТЕКСТ(E11;n0);3);ПОДСТАВИТЬ(ИНДЕКС(n1x;ПСТР(ТЕКСТ(E11;n0);14;1)+1;ПСТР(ТЕКСТ(E11;n0);15;1)+1);"z";" ");"ноль ")&"килограм"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(E11;n0);3)-11;1);9);100);{0;"м":1;"ма":4;"м"};2) прописываются только два регистра. Подскажите как я могу добавить значения третьего регистра. В диспетчере имен значения изменены на 3

      Удалить
  29. Вы гения!!! :) Спасибо большущее! Доступно, понятно, наглядно. Вы сэкономили мне кучу времени, а главное нервов!

    ОтветитьУдалить
  30. Подскажите, пожалуйста, как 2 формулу преобразовать в доллары США и евро. Как будет выглядеть формула?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Как вариант, замените в формуле текст (для долларов):

      "рубл"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(B6;n0);11;2)-11;100);9);10);{0;"ь ":1;"я ":4;"ей "};2)&ПРАВСИМВ(ТЕКСТ(B6;n0);2)&" копе"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(B6;n0);2)-11;100);9);10);{0;"йка":1;"йки":4;"ек"};2)

      на текст:

      "доллар"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(B5;n0);11;2)-11;100);9);10);{0;" ":1;"а ":4;"ов "};2)&ПРАВСИМВ(ТЕКСТ(B5;n0);2)&" цент"&ВПР(ОСТАТ(МАКС(ОСТАТ(ПРАВСИМВ(ТЕКСТ(B5;n0);2)-11;100);9);10);{0;"":1;"а":4;"ов"};2)

      Для евро аналогично (измените фразу "доллар" на "евро")

      Удалить
  31. Если для ячейки, в которой прописана Ваша формула, установить параметр "переносить по словам, то начинает отображать, например вот такое: 11104,00 {"Десять ";"Одиннадцать ";"Двенадцать ";"Тринадцать ";"Четырнадцать ";"Пятнадцать ";"Шестнадцать ";"Семнадцать ";"Восемнадцать ";"Девятнадцать "}Тысяч сто четыре рубля 00 копеек

    ОтветитьУдалить
    Ответы
    1. Проверила на файле-примере, приложенном к статье, такого эффекта не заметила. Может ошибка при копировании формулы?

      Удалить
  32. Добрый день, не зависимо от того какие копейки я указываю в тексте прописывается "00 копеек". Подскажите, пожалуйста, как это исправить?

    ОтветитьУдалить
    Ответы
    1. Такой эффект в файле-примере или формула скопирована в другой файл? Если первое, то может не совпадают региональные настройки (хотя формула должна работать независимо о того, точка или запятая используется как разделитель). Если второе, проверьте формулу или скопируйте сюда для проверки. Или приложите свой файл.

      Удалить
    2. Добрый день. Все получилось, я все удалила, а потом еще раз скопировала формулу и справочники к ней, и теперь копейки отображаются правильно. Спасибо большое)))

      Удалить
  33. Здравствуйте! при протягивании формулы вниз по столбцу ячейки не меняются. Как это можно исправить?

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

      Удалить
  34. Здравствуйте! Нужно прописать словами сумму литров и миллилитров. Пример: 20,34 Двадцать литров триста сорок миллилитров.
    С литрами и миллилитрами справилась, а вот как заменить "тридцать четыре" на "триста сорок" ?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Т.е. Вам нужно, чтобы прописью было не только до запятой, но и после запятой? Или нужно чтобы было "Двадцать литров 340 миллилитров"?

      Удалить
  35. Спасибо огромное! Дай Вам Бог здоровья!!!!

    ОтветитьУдалить
  36. Здравствуйте! Скажите, а как сделать, чтобы в одной ячейке отображались значения по формуле № 3, а в другой отображались или рубли без копеек, или рубли с копейками. С уважением, Дмитрий

    ОтветитьУдалить
    Ответы
    1. Если правильно поняла, то отсекаете во 2й формуле всё, что до "рубл".

      Удалить
  37. Большое спасибо! Все получилось! Супер! Скопировал из Вашего ответа про замену на доллары, убрал копейки и теперь рубли меняются))

    ОтветитьУдалить
  38. добрый день! выдает следующее сообщение при заполнении диапазона n_3:

    There's a problem with this formula.

    Not trying to type a formula?
    When the first character is an equal (=) or minus (-) sign, excel thinks it's a formula:

    you type: =1+1, cell shows: 2

    To get around this, type an apostrophe (') first:

    you type: '=1+1, cell shows: = 1+1

    Подскажите, пожалуйста, что с этим можно сделать.

    ОтветитьУдалить
    Ответы
    1. Может вводите диапазон с ошибкой? Проверьте, что всё также, как написано выше. Может версия Excel устаревшая?

      Удалить
  39. Диапазон ввожу также как у вас, дает сохранить только если ставлю апостроф вначале формулы перед =, но при этом же смысл формулы уже будет другой? Версия Excel 2016

    ОтветитьУдалить
    Ответы
    1. Скачайте готовый файл примера, проверьте, что там формула работает. Если да, то попытайтесь повторить копированием в своем файле.

      Удалить
  40. На Excel 2003 первая формула не работает (видимо слишком длинная)

    ОтветитьУдалить
  41. Лишний раз убедился, что 90% знаний ученика зависят от учителя и его умения объяснять! Огромное спасибо за Ваш труд, за подробные объяснения, за информацию в картинках про диапазоны и справочники и за то, что вот уже почти три года продолжаете терпеливо объяснять непонимающим и помогать нуждающимся оптимизировать данные решения под их конкретные задачи!

    ОтветитьУдалить
  42. Все работает, но: от 11 до 19 прописью пишет на единицу меньше, например 15 (Четырнадцать) рублей 00 копеек. От 0 до 9 - правильно, от 20 до 100 тоже. С тысячами и миллионами также - 15000 (Четырнадцать тысяч) рублей 00 копеек.

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

      Удалить
  43. Разобрался. В Диспетчере имен n_2 скопировано было с ошибкой. Все работает! Спасибо!

    ОтветитьУдалить
  44. Спасибо! Разобралась. Все работает.

    ОтветитьУдалить
  45. Как же я Вам благодарна, дорогие создатели такой чудной формулы!!!!!!!!!!!!
    Все работает!!!! Спасибо Вам!!!!

    ОтветитьУдалить
  46. Ничего чудного/волшебного в этой формуле нет, это как микроскопом гвозди забивать, не понимаю в чем зло использования макросов и/или надстроек?

    ОтветитьУдалить
  47. Дмитрий Картридж10 августа 2019 г. в 21:06

    Очень благодарен за формулу)
    Но есть ошибка!
    Имя диапазона: n_2
    Диапазон: ={"десятьz";"одиннадцатьz";"двенадцатьz";"тринадцатьz";"четырнадцатьz";"пятнадцатьz";"шестнадцатьz";"семнадцатьz";"восемнадцатьz";"девятнадцатьz"}
    На да!
    ={"";"десятьz";"одиннадцатьz";"двенадцатьz";"тринадцатьz";"четырнадцатьz";"пятнадцатьz";"шестнадцатьz";"семнадцатьz";"восемнадцатьz";"девятнадцатьz"}
    и всё заработало)

    ОтветитьУдалить
    Ответы
    1. Странно. А какого рода была ошибка? Если я меняю диапазон так, как Вы указали, перестает правильно считать десятки (например, вместо одиннадцати тысяч, пишет десять).

      Удалить
  48. Здравствуйте, используя эту формулу в книге Excel формата .xlsx, появляется ошибка "#ИМЯ?". ... как это исправить?

    ОтветитьУдалить
  49. Спасибо Вам большое!!! Дай Вам бог крепкого здоровья, счастья и финансового благополучия!!!

    ОтветитьУдалить
  50. Добрый день!
    А кто нибуть может расшифровать диапазон n0x?
    Какой смысл использовать функцию если? Ведь это диапазоны

    ОтветитьУдалить
  51. Урряяяя!!! Это просто праздник какой то! Как я удачно наткнулась на эту страницу!!! Спасибо огромное, все работает!!!

    ОтветитьУдалить
  52. Спасибо тебе, добрый человек!
    Давно искал такую формулу!
    Это гениально!

    ОтветитьУдалить
  53. Добрый день,
    А могли бы Вы подсказать, как прописать/подогнать формулу, чтобы данные отображались в формате 0 рублей 00 копеек?..

    ОтветитьУдалить
  54. Как сделать чтоб копейки считались с другой ячейки?

    ОтветитьУдалить
  55. А сами справочники внутрь формулы можно зашить?

    ОтветитьУдалить
    Ответы
    1. Попытаться можно, но формула будет "километровая".

      Удалить
  56. Спасибо!Это решение воистину прекрасно! За 15 минут настройки сэкономлены недели жизни десятков людей!!!

    ОтветитьУдалить
  57. Огромное спасибо за подспорье! Как удобно и точно все! Вы молодец!

    ОтветитьУдалить
  58. Подскажите, как убрать копейки в первой формуле?

    ОтветитьУдалить
  59. Методом тыка, все получилось. Большое спасибо )))

    ОтветитьУдалить
  60. Здравствуйте. Искал формулу, чтобы можно было переводить в счетах числа в буквы. На нескольких сайтах смотрел, нашел только макросы, надстройки и еще что-то. С ними ничего не получилось. Здесь все просто и понятно, минут за 5-10 все получилось. Спасибо большое автору, сайту и всем кто к этому причастен))

    ОтветитьУдалить
  61. Еще в копилку решений такой вариант, для тех кому покороче больше нравится:

    ="("&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПРОПНАЧ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(
    ИНДЕКС(t_999;ПСТР(H17+ZZ;3;2)+1;ПСТР(H17+ZZ;2;1)+1)&
    ЕСЛИ(--ПСТР(H17+ZZ;2;3)>0;ВПР(ПСТР(H17+ZZ;4;1)+1;dB;2);"")&
    ИНДЕКС(t_999;ПСТР(H17+ZZ;6;2)+1;ПСТР(H17+ZZ;5;1)+1)&
    ЕСЛИ(--ПСТР(H17+ZZ;5;3)>0;ВПР(ПСТР(H17+ZZ;7;1)+1;dM;2);"")&
    ПОДСТАВИТЬ(ПОДСТАВИТЬ(
    ИНДЕКС(t_999;ПСТР(H17+ZZ;9;2)+1;ПСТР(H17+ZZ;8;1)+1);
    "один ";"одна ");"два ";"две ")&
    ЕСЛИ(--ПСТР(H17+ZZ;8;3)>0;ВПР(ПСТР(H17+ZZ;10;1)+1;dT;2);"")&
    ИНДЕКС(t_999;ПСТР(H17+ZZ;12;2)+1;ПСТР(H17+ZZ;11;1)+1)&
    ЕСЛИ(H17<1;"Ноль ";""));" ";"w"));"w";" "))&
    ") руб. "&ПРАВСИМВ(ТЕКСТ(H17;"#,00");2)&" коп."

    Естественно это без макроса и без надстроек.
    Но основное запрятано в имена

    ОтветитьУдалить
  62. Отлично сработал! раньше я при таких случаях использовал макрокоманду

    ОтветитьУдалить
  63. Добрый день! Во первых, огромное спасибо! Во-вторых, как сделать, чтобы первая буква была не прописная?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте.
      > как сделать, чтобы первая буква была не прописная?
      Попробуйте убрать из формулы функцию ПРОПНАЧ.

      Удалить
  64. Здравствуйте. Нужна формула которая будет писать число прописью, например 123,4567 - Сто двадцать три целых четыре тысячи пятьсот шестьдесят семь десятитысячных. Я нашел формулу в интернете. Она считает, но округляет до 123,46, т.е. выдает значение - Сто двадцать три целых сорок шесть сотых, хотя число стоит 123,4567. Как сделать, чтобы сумма прописью писалась правильно?

    ОтветитьУдалить
  65. =ПОДСТАВИТЬ(ПРОПНАЧ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(C32;n0);1;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(C32;n0);2;1)+1;ПСТР(ТЕКСТ(C32;n0);3;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(C32;n0);1;3);"миллиард"&ВПР(ПСТР(ТЕКСТ(C32;n0);3;1)*И(ПСТР(ТЕКСТ(C32;n0);2;1)-1);мил;2);"")&ИНДЕКС(n_4;ПСТР(ТЕКСТ(C32;n0);4;1)+1)&ИНДЕКС(n0x;ПСТР(ТЕКСТ(C32;n0);5;1)+1;ПСТР(ТЕКСТ(C32;n0);6;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(C32;n0);4;3);"миллион"&ВПР(ПСТР(ТЕКСТ(C32;n0);6;1)*И(ПСТР(ТЕКСТ(C32;n0);5;1)-1);мил;2);"")&ИНДЕКС(n_4;ПСТР(ТЕКСТ(C32;n0);7;1)+1)&ИНДЕКС(n1x;ПСТР(ТЕКСТ(C32;n0);8;1)+1;ПСТР(ТЕКСТ(C32;n0);9;1)+1)&ЕСЛИ(-ПСТР(ТЕКСТ(C32;n0);7;3);ВПР(ПСТР(ТЕКСТ(C32;n0);9;1)*И(ПСТР(ТЕКСТ(C32;n0);8;1)-1);тыс;2);"")&ИНДЕКС(n_4;ПСТР(ТЕКСТ(C32;n0);10;1)+1)&ИНДЕКС(ЕСЛИ(-ПСТР(ТЕКСТ(C32;n0);14;6);n1x;n0x);ПСТР(ТЕКСТ(C32;n0);11;1)+1;ПСТР(ТЕКСТ(C32;n0);12;1)+1));"z";" ")&ЕСЛИ(ОТБР(ТЕКСТ(C32;n0));;"Ноль ")&ЕСЛИ(-ПСТР(ТЕКСТ(C32;n0);14;6);ЕСЛИ(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(C32;n0);11;2)-11;100);9);10);"целых ";"целая ")&ПОДСТАВИТЬ(ИНДЕКС(n_4;ПСТР(ТЕКСТ(C32;n0);14;6)/10^5+1)&ИНДЕКС(n1x;ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)/10^4;10)+1;ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)/1000;10)+1)&ЕСЛИ(ЦЕЛОЕ(ПСТР(ТЕКСТ(C32;n0);14;6)/1000);ВПР(ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)/1000;10)*(ОСТАТ(ЦЕЛОЕ(ПСТР(ТЕКСТ(C32;n0);14;6)/10^4);10)<>1);тыс;2);"")&ИНДЕКС(n_4;ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)/100;10)+1)&ИНДЕКС(n1x;ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)/10;10)+1;ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6);10)+1);"z";" ")&ИНДЕКС(доля;ДЛСТР(ПСТР(ТЕКСТ(C32;n0);14;6));(ОСТАТ(МАКС(ОСТАТ(ПСТР(ТЕКСТ(C32;n0);14;6)-11;100);9);10)>0)+1);)

    ОтветитьУдалить
  66. В примере, формула считает правильно. Переношу ее к себе в файл - она начинает округлять. Подскажите что можно сделать.

    ОтветитьУдалить
    Ответы
    1. Попробуйте в диспетчере имен у имени n0 увеличить количество нулей в конце, например: ="000000000000"&ПСТР(1/2;2;1)&"00000"

      Удалить
    2. Спасибо БОЛЬШОЕ! Все получилось!

      Удалить
  67. Спасибо БОЛЬШОЕ! Все получилось! Очень Вам благодарен.

    ОтветитьУдалить
  68. Товарищи, помогите пожалуйста в таком вопросе.
    Немного адаптировал формулу под иены без копеек. Но всплыла проблемка. Пример:

    32 537 532 (Тридцать два миллиона пятьсот тридцать семь тысяч пятьсот тридцать два) иены

    два иены (((

    Пробовал менять =ЕСЛИ(n_3=1;n_2;n_3&n_1) и =ЕСЛИ(n_3=1;n_2;n_3&n_5), тогда две миллиона пишет.

    Как исправить, пожалуйста помогите.

    ОтветитьУдалить
  69. отличная формула, спасибо! Хочу её модернизировать для расчете НДС 20% в формате "сумма ***,** руб. (*** рублей ** копейки), в том числе НДС 20% в сумме ***,** руб. (*** рублей ** копейки)", подскажите пожалуйста что добавить в формулу?

    ОтветитьУдалить
  70. Доброго времени суток! Очень классная формула!!! Вот бы ее под вес адаптировать. Или может автор формулы смогла бы объяснить какое имя за что отвечает, чтобы переделать формулу для веса в тн с прописью килограммов и граммов. Заранее благодарю!

    ОтветитьУдалить
  71. Ребята у меня получилось переделать на вес, если кому интересно скину формулу.

    ОтветитьУдалить
    Ответы
    1. Здравствуйте, рада, что получилось. Думаю, всем будет интересно посмотреть результат :)

      Удалить
  72. Здравствуйте!Ищу формулу, которая выводит сумму прописью на английском языке. Помогите, пожалуйста

    ОтветитьУдалить
  73. Как по формуле 3 убрать заглавную букву?

    ОтветитьУдалить
  74. Спасибо огромное добрый человек! Всё доступно и понятно :)

    ОтветитьУдалить
  75. Научите как заменить в формуле ячейку А1 на нужную, чтобы не каждую в формуле менять.

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