Число прописью в EXCEL (статичный вариант)

history

Превратим число 256 в строку "двести пятьдесят шесть". Число прописью запишем без использования VBA .


Набирать текст "двести пятьдесят шесть" безумно скучно, кроме того легко ошибиться, поэтому давно написаны макросы на VBA, которые легко справляются с этой задачей.

Чтобы перевести число в текст, макросы писать не обязательно, можно эту задачу решить с помощью обычных формул.

Для решения задачи нам потребуются 4 текстовых Именованных диапазона содержащих некоторые числа прописью:

  • Диапазон Единицы , содержащий числа прописью от 1 до 20: один, два, три, … двадцать ;
  • Диапазон Десятки , содержащий числа 10, 20, …, 90 прописью: десять, двадцать, тридцать, … девяносто ;
  • Диапазон Сотни , содержащий числа 100, 200, …, 900 прописью: сто, двести, триста, … девятьсот.
  • Диапазон ЕдиницыТ, содержащий числапрописью от 1 до 20 со специальным склонением для тысяч (чтобы правильно написать дв е тысячи, а не дв а тысячи )

Диапазоны разместим на листе Служ . Также на листе Служ разместим вспомогательные формулы (см. файл примера ). Исходное число (от 0 до 1 млрд.) и его запись прописью разместим на листе Прописью.

В качестве исходного числа возьмем 123 456 789. Алгоритм решения задачи следующий.

  • Разобьем исходное число на 3 части, выделив миллионы (123), тысячи (456) и единицы с десятками и сотнями (789), подробнее смотри статью Извлекаем из числа единицы, десятки, сотни, тысячи и миллионы ;
  • Запишем выделенные числа прописью с помощью однотипных формул;
  • Полученные текстовые значения соберем вместе, добавляя слова тысяч и миллионов в правильном склонении (пять миллион ов , но два миллион а ).

Единицы, десятки и сотни Из исходного числа (ссылка на него находится в ячейке Н1 листа Служ ) с помощью формулы =ОСТАТ(H1;1000) выделим число единиц (т.е. из исходного 123 456 789 получим 789), разместим его в ячейку G 4 и запишем его прописью с помощью формулы: =ИНДЕКС(Сотни;ОТБР(G4/100)+1)&" "& ЕСЛИ(G4-ОТБР(G4/100)*100<21; ИНДЕКС(Единицы;G4-ОТБР(G4/100)*100+1); ИНДЕКС(Десятки;ОТБР((G4-ОТБР(G4/100)*100)/10))&""& ИНДЕКС(Единицы;ОСТАТ(G4;10)+1))

Формула состоит из 3-х частей (для единиц, десятков и сотен). Например, часть формулы ИНДЕКС(Сотни;ОТБР(G4/100)+1) возвращает прописью число сотен ( семь ): ОТБР(G4/100) из 789 получает 7, функция ИНДЕКС() из диапазона Сотни выбирает текстовое значение соответствующее числу 7, т.е. «семь». Часть формулы ИНДЕКС(Единицы;ОСТАТ(G4;10)+1)) возвращает прописью число единиц ( девять ). Часть формулы ЕСЛИ(G4-ОТБР(G4/100)*100<21;ИНДЕКС(Единицы;G4-ОТБР(G4/100)*100+1);ИНДЕКС(Десятки;ОТБР((G4-ОТБР(G4/100)*100)/10)) возвращает прописью число десятков ( восемь ). Результат вычисления формулы ( семьсот восемьдесят девять ) запишем в ячейку Н4.

Тысячи Затем, из исходного числа с помощью формулы =ОСТАТ(ОТБР(H1/1000);1000) выделим число тысяч (т.е. из исходного 123 456 789 получим 456). Результат вычисления формулы ( четыреста пятьдесят шесть ) запишем в ячейку Н3.

Миллионы Наконец, из исходного числа с помощью формулы =ОКРУГЛВНИЗ(H1;-6)/1000000 выделим число миллионов (т.е. из исходного 123 456 789 получим 123). Результат вычисления формулы ( сто двадцать три ) запишем в ячейку Н2.

Полученные значения тысяч и миллионов запишем прописью с помощью формул аналогичных формуле для единиц.

Теперь разберемся со склонениями слов тысяча (одна тысяч а , две тысяч и , пять тысяч) и миллион (один миллион, два миллион а , пять миллион ов ). Для тысяч это можно сделать с помощью формулы: =ЕСЛИ(G3>0; ЕСЛИ(И(G3-ОТБР(G3/100)*100>10;G3-ОТБР(G3/100)*100<15);"тысяч"; ЕСЛИ(--ПРАВСИМВ(G3;1)=0;"тысяч"; ЕСЛИ(--ПРАВСИМВ(G3;1)=1;"тысяча"; ЕСЛИ(--ПРАВСИМВ(G3;1)<5;"тысячи";"тысяч"))));"")

Результат вычисления формул запишем в ячейки I2 (для миллионов) и I3 (для тысяч).

Финальную сборку числа прописью произведем на листе Прописью с помощью формулы: =ЕСЛИ(A2>0;СЖПРОБЕЛЫ(служ!H2&" "&служ!I2&" "&служ!H3&" "&служ!I3&" "&служ!H4);"Ноль")

Алгоритм использования полученного решения следующий :

  • ввести число в ячейку А2 ,
  • получить число прописью в А3 ,
  • скопировать в Буфер обмена ( CTRL+C ),
  • вставить в любую открытую книгу «Как значение» ( Главная/ Буфер обмена/ Вставить/ Вставить как значение ).

В статье Число прописью (динамический вариант) показано как создать формулу, позволяющую переводить число в текст практически с таким же удобством, как и в случае использования макроса.


Комментарии

Только для авторизованных пользователей

Аноним, 5 апреля 2016 г.
Спасибо
Аноним, 11 июня 2017 г.
а если нужно записать число 45,3 ? что делать?))
Михаил, 12 июня 2017 г.
Если это рубли, то обычно пишут Сорок пять рублей 30 коп. Чтобы выделить 45 из 45,3 используйте функцию ЦЕЛОЕ(). Честно говоря ни разу не встречался с необходимостью написать Три десятых. Подскажите, где это требуется. В принципе, сделать прописью десятые или сотые не сложно.
Аноним, 17 июня 2020 г.
[id295933596|Михаил], подскажите, как именно сделать "Три десятых"
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved