Сумма прописью
В обоих случаях нам нужно понимать с каким масштабом цифр мы работаем. До ста, до тысячи, до миллиона, до миллиарда и так далее.
Без макросов
В демонстрационных целях я сделаю формулу, которая преобразует в текст числа меньше миллиона. В принципе, с таким же подходом можно ее адаптировать и на большие числа, главное, вписаться в ограничение длины формулы Excel: 16 384 байт.
Приготовьтесь, формула будет большая.
= IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=1;»сто «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=2;»двести «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=3;»триста «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=4;»четыреста «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=5;»пятьсот «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=6;»шестьсот «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=7;»семьсот «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=8;»восемьсот «;IF(QUOTIENT(nm;100000)-QUOTIENT(nm;1000000)*10=9;»девятьсот «;)))))))))&IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=2;»двадцать «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=3;»тридцать «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=4;»сорок «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=5;»пятьдесят «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=6;»шестьдесят «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=7;»семьдесят «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=8;»восемьдесят «;IF(QUOTIENT(nm;10000)-QUOTIENT(nm;100000)*10=9;»девяносто «;))))))))&
IF(AND(QUOTIENT(nm;1000)-QUOTIENT(nm;100000)*100 9); IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=0;»десять»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=1;»одиннадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=2;»двенадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=3;»тринадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=4;»четырнадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=5;»пятнадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=6;»шестнадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=7;»семнадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=8;»восемнадцать»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=9;»девятнадцать»;))))))))));
)&IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=0;»»;IF(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=1;» тысяча «;IF(OR(QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=2;QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=3;QUOTIENT(nm;1000)-QUOTIENT(nm;10000)*10=4);» тысячи «; » тысяч «)))
&IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=1;»сто «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=2;»двести «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=3;»триста «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=4;»четыреста «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=5;»пятьсот «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=6;»шестьсот «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=7;»семьсот «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=8;»восемьсот «;IF(QUOTIENT(nm;100)-QUOTIENT(nm;1000)*10=9;»девятьсот «;)))))))))&IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=2;»двадцать «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=3;»тридцать «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=4;»сорок «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=5;»пятьдесят «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=6;»шестьдесят «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=7;»семьдесят «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=8;»восемьдесят «;IF(QUOTIENT(nm;10)-QUOTIENT(nm;100)*10=9;»девяносто «;))))))))&
IF(AND(nm-QUOTIENT(nm;100)*100 9); IF(nm-QUOTIENT(nm;10)*10=0;»десять»;IF(nm-QUOTIENT(nm;10)*10=1;»одиннадцать»;IF(nm-QUOTIENT(nm;10)*10=2;»двенадцать»;IF(nm-QUOTIENT(nm;10)*10=3;»тринадцать»;IF(nm-QUOTIENT(nm;10)*10=4;»четырнадцать»;IF(nm-QUOTIENT(nm;10)*10=5;»пятнадцать»;IF(nm-QUOTIENT(nm;10)*10=6;»шестнадцать»;IF(nm-QUOTIENT(nm;10)*10=7;»семнадцать»;IF(nm-QUOTIENT(nm;10)*10=8;»восемнадцать»;IF(nm-QUOTIENT(nm;10)*10=9;»девятнадцать»;))))))))));
Чтобы расширить формулу для чисел до миллиарда, нужно скопировать первую часть формулы (подчеркнутую), добавить в каждой формуле QUOTIENT по три нуля, заменить «тысяч, тысяча, тысячи» на миллионы, соответсвенно, и вставить обратно в формулу перед частью про тысячи.
Нужно заметить, что даже в случае с числами до миллиона, Excel уже начинает подтупливать при редактировании формулы.
С макросами
Не будем изобретать велосипед, есть множество фрагментов кода, который преобразует число в сумму прописью (и добавляет еще рубли или гривные, или что еще). Воспользуемся одним из них. Откроем редактор Visual Basic for Applications, нажав Alt+F11 и импортируем модуль с файлом (все файлы будут доступны в конце статьи).
Число прописью в Excel (динамический вариант)
Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 коп.
В статье Число прописью (статичный вариант) приведено решение этой задачи позволяющей перевести число в текстовую форму по следующему алгоритму:
Это не всегда удобно. Хочется по аналогии с функциями на VBA написать что то вроде =ЧислоПрописью(А1) и получить результат. Все промежуточные вычисления должны быть «за кадром». Но, создание пользовательских функций это прерогатива VBA.
Тем не менее, можно предложить следующее решение с помощью обычных формул:
Как и в примере статьи Число прописью (статичный вариант) на листе Служ размещены вспомогательные диапазоны, содержащие некоторые числа прописью в Excel.
Теперь эти диапазоны длиннее (числа от 1 до 999) и содержат слова тысяча и миллион с учетом склонения. Также создан диапазон для отображения слова Рублей с учетом склонения.
Теперь все готово для записи формулы переводящей исходное число (ячейка B 6 ) в текст: =СЖПРОБЕЛЫ( ИНДЕКС(Миллионы;ОСТАТ(ОТБР(B6/1000000);1000000)+1)&» «& ИНДЕКС(Тысячи;ОСТАТ(ОТБР(B6/1000);1000)+1)&» «& ИНДЕКС(Единицы;ОСТАТ(B6;1000)+1))
Задача в принципе решена, осталось только разместить вспомогательные диапазоны в Личную книгу макросов PERSONAL.XLSB, для того чтобы формула, переводящая число в текст была доступна в любой книге.
В Windows XP эта книга находится в папке C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart, откуда она будет автоматически загружается при каждом запуске приложения Excel. В Windows Vista эта книга хранится в папке C:\Users\имя_пользователя\Application Data\Microsoft\Excel\XLStart.
Если Личная книга макросов еще не была создана, то скопируйте лист Служ из файла примера в новую книгу, и сохраните ее в директорию C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart (для XP) под именем PERSONAL.XLSB.
Если Личная книга макросов уже была ранее создана, то через меню Вид/ Окно/ Отобразить отобразите ее, скопируйте в нее лист Служ, сохраните Личную книгу макросов (можно ее потом скрыть Вид/ Окно/ Скрыть ).
Формула отобразит число прописью. Ячейки, содержащие исходное число и формулу, можно перенести в любое место в книге или даже в другую книгу.
При пересылке файла, например, по электронной почте, формула будет продолжать работать, не смотря на то, что Личная книга макросов не пересылалась, т.е. Ваши коллеги получившие файл, смогут с ним работать как Вы.
Преобразование текста в число в ячейке Excel
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
Как преобразовать текст в число в Excel
Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.
Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.
Последний способ подходит в том случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.
Есть набор значений, сохраненных в текстовом формате:
Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:
Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.
Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».
Цифры переместились вправо.
Следовательно, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.