Печать суммы прописью в Excel без использования макросов
Ранее в статье на нашем сайте была описана печать суммы прописью в Excel с использованием макроса. Эта возможность полезна для бухгалтеров, финансистов, менеджеров, то есть для всех, активно использующих электронные таблицы при работе с финансовыми документами. Однако в некоторых случаях использование макроса нежелательно, а автоматизировать расчет суммы прописью все равно нужно. Для таких случаев описан еще один способ получения нужного результата. Вы можете скачать таблицу Excel с примером вывода суммы прописью этим способом, щелкнув мышью на этой ссылке.
Если вы по каким-либо причинам не хотите включать выполнение макросов, или у вас не получилось подключить программу, а также, если у вас нет возможности получить программу из интернета, можно использовать другой способ получения суммы прописью, с помощью ввода расчетных формул в лист Excel. Однако использование макросов удобнее и легче, так что по возможности лучше работать именно с ними.
При втором способе вычисления суммы прописью вы должны ввести на отдельном листе рабочей книги Excel формулы для расчета. При этом исходные данные будут браться из ячейки A1 этого листа, а результат помещен в ячейку A2. Далее этот лист нужно скопировать в любую таблицу, и поместить в ячейку A1 ссылку на сумму, которую вы хотите записать прописью. После этого в ячейку, в которой должна располагаться сумма прописью, поместите ссылку на ячейку A2 добавленного листа. Основной недостаток такого способа - вы можете вставить только одно значение суммы прописью. Если вам понадобится вторая сумма, записанная буквами, нужно будет вставить в рабочую книгу еще один лист с вычислениями. Однако у такого способа есть и достоинства. Вы можете произвольно модифицировать алгоритм вычислений, например, изменив название денежной единицы.
Создайте новую таблицу Excel и оставьте в ней только один лист. Как вы помните, в ячейке A1 будут помещаться исходные данные, а в ячейку A2 помещен результат. Вначале введем в таблицу все нужные для получения суммы прописью слова. Нужно заполнить строки с третьей по девятую, введя слова в первые десять столбцов.
Начнем заполнение с третьей строки. В ячейку B3 введите один, в C3 введите два, в D3 - три, и так далее. В последний столбец, в ячейку J3, нужно ввести девять. В следующую строку нужно ввести названия чисел более десять. В ячейку A4 введите десять, в ячейку B4 введите одиннадцать, в C4 введите двенадцать и так далее, до девятнадцати. В пятой строке вводятся названия десятков, и начинается ввод с третьего столбца. В ячейку C5 введите двадцать, в ячейку D5 введите тридцать и так далее. Сотни вводятся в шестую строку. В ячейку B6 введите сто, в C6 введите двести и так далее. В следующую строку нужно ввести названия тысяч. В ячейку B7 введите одна тысяча, в C7 введите две тысячи, в D7 введите три тысячи и так далее до слов девять тысяч в ячейку J7. Если вы не планируете писать прописью суммы более 999 тысяч, следующую строку заполнять не надо. В противном случае введите в ячейку B8 слово один миллион, в C8 - два миллиона и так далее. Следующая строка предназначена для склонения названия денежных единиц. Если вы не хотите писать «рублей», «рубля» и «рубль», а ограничитесь словом «руб.», можете ее не заполнять. Если же вы хотите использовать полное слово, в ячейку A9 введите рублей, ячейку B9 введите рубль. В ячейки C9, D9, и E9 введите рубля, а в остальные ячейки F9, G9, H9, I9, J9 введите рублей. Вы ввели все нужные слова, и пора добавить расчетные формулы.
В ячейку A11 введите число 10, а в ячейку A12 введите формулу =A11*10. В ячейке A12 появится результат - 100. С помощью автозаполнения скопируйте формулу из A12 в ячейки до девятнадцатой строки. Для этого щелкните мышью на ячейке A12, чтобы сделать ее активной. Подведите указатель мыши к маркеру заполнения, расположенном в правом нижнем углу рамки выделения ячейки. Нажмите и не отпускайте левую кнопку мыши, после чего начните перемещать указатель. При этом область выделения ячеек будет увеличиваться. После выделения ячеек до A19 включительно, отпустите кнопку мыши. Все выделенные ячейки будет заполнены нужной формулой. При этом каждая следующая ячейка будет ссылаться на предыдущую, умножая ее значение на 10.
Далее нужно выделить отдельные цифры из исходного числа. Введите в ячейку B11 формулу =ЦЕЛОЕ($A$1/A11*10)-(ЦЕЛОЕ($A$1/A11))*10. В этой ячейку будет отображаться крайняя правая цифра числа. Например, если ввести в ячейку A1 число 326, вы увидите в ячейке В11 цифру 6. Поясним, как это получилось. Формула $A$1/A11*10 делит число на 10 и умножает тоже на 10, то есть в результате остается число 326. Функция ЦЕЛОЕ также не меняет это число. В правой части формула $A$1/A11 делит число на 10, то есть получается 32,6. Функция ЦЕЛОЕ оставляет целую часть, то есть 32. Умножаем на 10, и получаем 320. Наконец, вычитая 320 из 326, мы получаем 6, что и требовалось. В формуле ссылка на ячейку A1 использует абсолютную адресацию - $A$1, чтобы при копировании формулы эта ссылка не менялась.
После того, как вы ввели формулу в ячейку B11, нужно ее размножить в расположенные ниже ячейки. С помощью автозаполнения, описанного выше, размножьте формулу из ячейки B11 во все ячейки до B19 включительно. В каждой ячейке появится своя цифра. Например, если вы ввели в ячейку A1 число 326, в ячейке B12 появится цифра 2, а в B13 появится цифра 3. Логика вычислений такая же, как и при определении единиц, но в каждом случае выполняется деление на свое число - 100, 1000 и так далее.
В результате выполненных действий у нас есть все цифры, входящие в исходное число. Теперь настала пора описать самую ответственную часть расчетов - преобразование цифры в нужное слово. Для каждого разряда числа нужно ввести свою формулу, и начнем мы с единиц. В ячейку C11 введите формулу =ИНДЕКС(A3:J3;B11+1). Функция ИНДЕКС извлекает значение нужной ячейки из нескольких. Диапазон просматриваемых ячеек задается первым аргументом - A3:J3. напоминаем, что в эти ячейки ранее были введены названия цифр - один, два и так далее. Второй аргумент указывает на конкретную ячейку. Введя B11+1, вы получите слово из ячейки B3, если в ячейке B11 расположена единица, и слово из ячейки I3, если в B11 выводится 8. Попробуйте ввести в ячейку A1 разные числа, и вы увидите, как в C11 появляются названия последней цифры числа.
Вы можете заметить, что числа от 11 до 19 преобразуются неверно. Чтобы исправить этот недостаток, следует усложнить формулу. Если вы введете в ячейку C11 формулу =ИНДЕКС(A4:J4;B11+1), правильно будут вычисляться суммы от 11 до 19, но остальные суммы будут неверно вычисляться. Обратите внимание, что новая формула отличается от первой только тем, что указана четвертая, а не третья строка таблицы. Именно в четвертую строку вводились слова одиннадцать, двенадцать и так далее. Чтобы выводить разные слова, нужно использовать условную функцию ЕСЛИ. Исправьте формулу в ячейке C11, чтобы получилось такое выражение:
=ЕСЛИ(B12=1;ИНДЕКС(A4:J4;B11+1);ИНДЕКС(A3:J3;B11+1))
Если в ячейке B12 единица, то есть число от 10 до 19, будет использоваться формула ИНДЕКС(A4:J4;B11+1), а во всех остальных случаях используется формула ИНДЕКС(A3:J3;B11+1). Теперь последняя цифра переводится в текст почти правильно. Единственным неудобством является то, что если эта цифра равна нулю, ноль и появляется в ячейке. Чтобы исправить эту ошибку, введите в ячейку A3 один пробел. Теперь вместо нуля в итоговую ячейку будет вставляться пробел. Аналогично добавьте пробелы в ячейки A5, A6 и B5, чтобы в дальнейшем не допускать таких ошибок.
Десятки и сотни вычисляются примерно так же. Введите в ячейку C12 формулу =ИНДЕКС(A5:J5;B12+1). Берется значение десятков и находится нужная ячейка в пятой строке. С помощью автозаполнения скопируйте формулу из ячейки C12 в C13. В последней ячейке появится формула =ИНДЕКС(A6:J6;B13+1), описывающая получение сотен.
Для вычисления тысяч нужно выполнить немного более сложные действия. Вначале введите в ячейку C14 формулу =ЕСЛИ(B15=1;ИНДЕКС(A4:J4;B14+1) & A7;ИНДЕКС(A7:J7;B14+1))
Эта формула очень похожа на используемую для единиц, только к ней добавляется слово «тысяча». Оператор & сцепляет строки, чтобы получилось число типа одиннадцати тысяч. Однако в ячейку A7 мы не ввели слово «тысяч». Ведь если исходное число типа 50000 или 112234, то слово «тысяч» нужно выводить, а в числе 5000000 или 565 слово «тысяч» отсутствует. Добавим в ячейку A7 условие. Введите в нее формулу
=ЕСЛИ(И((A1>1000);ИЛИ((B14<>0);(B15<>0);(B16<>0)));" тысяч";" ")
Теперь, если число меньше тысячи, в ячейке A7 располагается пробел. Пробел же располагается, если в большом числе нет разрядов тысяч, а только миллионы. В остальных случаях в ячейке A7 располагается слово «тысячи». Обратите внимание, что перед первой буквой слова добавлен пробел, чтобы получилось «одиннадцать тысяч», а не «одиннадцатьтысяч».
Десятки и сотни тысяч вычисляются так же, как и просто десятки и сотни. Введите в ячейку C15 формулу =ИНДЕКС(A5:J5;B15+1), и скопируйте ее в ячейку C16 с помощью автозаполнения, чтобы в ней получилась формула =ИНДЕКС(A6:J6;B16+1). Кстати, сразу аналогично введите в ячейку C18 формулу =ИНДЕКС(A5:J5;B18+1), а в ячейку C19 формулу =ИНДЕКС(A6:J6;B19+1). Теперь осталось добавить только единицы миллионов.
Вначале введем условие в ячейку A8. Введите в эту ячейку формулу =ЕСЛИ(A1>1000000;" миллионов";" "), чтобы не добавлять слово «миллионов» для маленьких чисел. Далее, как и для тысяч, введите в ячейку С17 формулу
=ЕСЛИ(B18=1;ИНДЕКС(A4:J4;B17+1)&A8;ИНДЕКС(A8:J8;B17+1))
Все точно так же, как и для тысяч, но используется не седьмая, а восьмая строка таблицы. Кстати, при необходимости вы можете аналогично добавить перевод миллиардов в текст. Отдельные части суммы прописью готовы. Попробуйте вводить разные числа в ячейку A1 и проверьте правильность вычислений. Если что-то считается неверно, проверьте правильность ввода всех формул.
В ячейку C20 введите формулу для склонения названия денежной единицы =ЕСЛИ(B12=1;A9;ИНДЕКС(A9:J9;B11+1)). Если число оканчивается на 11, 12, и тому подобное, то используется слово «рублей», в противном случае берется слово из девятой строки таблицы. Если вы не хотите писать полностью это слово, можете вместо формулы ввести в ячейку руб..
Далее в ячейку C21 введите формулу для вычисления копеек:
=ТЕКСТ((ОКРУГЛ(A1;2)-ЦЕЛОЕ(A1))*100;"00")
Вычисляются копейки, после чего преобразуются в текст, причем числа менее десяти выводятся с первым нулем, например «04». При желании можно склонять и копейки, как и рубли, но мы в примере добавим слово «коп.», как это чаще всего принято.
Все части готовы, и осталось только сцепить их. В ячейку A23 введите:
=СЖПРОБЕЛЫ(C19&" "&C18&" "&C17&" "&C16&" "&C15&" "&C14&" "&C13&" "&C12&" "&C11&" "&C20&" "&C21&" коп.")
Функция СЖПРОБЕЛЫ уберет лишние пробелы, а длинная формула сцепит все части, добавив между них пробелы. Полученный результат имеет два недостатка - сумма начинается со строчной буквы и при описании числа менее одного рубля, не пишется «Ноль рублей». Чтобы исправить это, введите в ячейку A2 формулу
=ЕСЛИ(ЦЕЛОЕ(A1)=0;"Ноль " &A23;ЗАМЕНИТЬ(A23;1;1;ПРОПИСН(ЛЕВСИМВ(A23;1))))
Все формулы введены, и вы можете проверить правильность вывода суммы прописью. Вначале лучше изменить формат ячейки A1 на финансовый, щелкнув на ней мышью и нажав кнопку с рисунком денег на панели инструментов. После увеличьте ширину первого столбца, и приступайте с проверке. Введите разные числа, от 0 до 999999999, в ячейку A1, и в ячейке A2 появится результат. Для ввода копеек, их следует отделять запятой, например 24,73.
Если все вычисляется правильно, можете скрыть строки с вспомогательными формулами. Для этого выделите строки с третьей по двадцать третью и выберите команду меню Формат - Строка - Скрыть. На листе останутся только исходная сумма и сумма прописью. Сохраните электронную таблицу, чтобы использовать ее в дальнейшем.
Чтобы вставить в любую таблицу вывод числа прописью, добавьте еще один лист, и скопируйте на него первые 24 строки из созданной только что таблицы. Назовите этот лист Пропись. Далее нужно добавить нужные ссылки. Например, в таблице на листе Лист1 в ячейке D12 выводится сумма, которую нужно вывести в виде текста в ячейке D13. Перейдите на лист Пропись и в ячейку A1 введите ссылку =Лист1!D12. После этого перейдите на лист Лист1 и ячейку D13 введите ссылку =Пропись!A2. В последней ячейке сразу появится сумма прописью.
