Рубли и копейки в excel

Рубли и копейки в excel

Добавление от 22.10.2009 10:51:

Akina
цитата: B1=ЛЕВСИМВ(A1;НАЙТИ(«,»; A1 & «,») -1)
C1=ПРАВСИМВ(A1 & » «;ДЛСТР(A1) — НАЙТИ(«,»;A1 & «,»)+1) А если виндовс английский и разделителем точка установлена?

Добавление от 22.10.2009 12:36:

Проблемка.
Число А1 — деньги в формате «рубли,копейки».
Если А1 например 398,80, то С1 = 8, а не 80.
Как решить?

Добавление от 22.10.2009 12:52:

awizard
Проблемка.
Число А1 — деньги в формате «рубли,копейки».
Если А1 например 398,80, то С1 = 8, а не 80.
Как решить?

msser
почему С1 = 8 вроде должно быть 0,8

решить просто домножте на 100,

в C1 пишем формулу =(A1-B1)*100

msser
цитата: Число А1 — деньги в формате «рубли,копейки».
Если А1 например 398,80, то С1 = 8, а не 80. Поставить — будет 80
Как решить? Убрать с ячейки С1 денежный формат и будет выводиться 8.

Другое дело если рубли копейки получились в результате вычисления (например а А1 забита формула =1/3), то тут большой философский вопрос сколько знаков брать. Или если используется рублевое представление «форексовских» рублей с 4 знаками после запятой.

Добавление от 13.01.2010 01:24:

Проблемка.
Число А1 — деньги в формате «рубли,копейки».
Если А1 например 398,80, то С1 = 8, а не 80.
Как решить?

C1 =ЕСЛИ(ДЛСТР(ЗНАЧЕН(ПОДСТАВИТЬ(A1;B1&»,»;»»))) «0»);ЗНАЧЕН(ПОДСТАВИТЬ(A1;B1&»,»;»»)*10);ЕСЛИ(ЦЕЛОЕ(A1)=ЗНАЧЕН(ПОДСТАВИТЬ(A1;B1&»,»;»»));0;ЕСЛИ(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;B1&»,»;»»);1)=»0″;ЗНАЧЕН(ПОДСТАВИТЬ(A1;B1&»,»;»»));ЗНАЧЕН(ПОДС ТАВИТЬ(A1;B1&»,»;»»)))))

Добавление от 13.01.2010 12:02:

чтобы отображать два числа — применяем к ячейке формат 0#. будет 02, 12. правда 0 останется 0. это грустно.

Добавление от 13.01.2010 12:10:

упс вот такой формат будет отображать 00

Добавление от 05.03.2012 21:06:

Кстати, у меня именно стоит задача чтобы разделить рубли от копеек, но чтоб можно было совершать дальнейшие математические действия, например мне надо чтоб отображалось 35,08 как 35 и 08 а 35,80 или 35,8 как 35 и 80.. самое сложное это 08 копеек, поскольку на другом листе эти данные уже не в виде просто цифр, а в виде форматированного числа в ценнике, который надо вывести на печать, в формате Х рублей, и в отдельной ячейке YY копеек. тоесть 35 рублей и 08 или 00 или 80 копеек. варианты 8 копеек не подходят. вот мучаюсь.

К сообщению приложены файлы: 1.zip, 1 file(s), 4Кb

Можно обойтись даже проще, одной формулой и вычитанием:
=INT(A1)
и
=(A1-C1)*100
и обоим столбцам поставить одинаковый пользовательский формат 00.
Т.е. заменяем формулу ТЕКСТ() на пользовательский формат.

И кстати тогда в таком варианте числа остаются числами, их можно суммировать.

К сообщению приложены файлы: 1.zip, 13 file(s), 10Кb

Указание суммы прописью в Excel

При работе с финансовыми документам часто требуется указывать сумму не только в виде числа, но и прописью. Безусловно, такое занятие способно отнять немало времени, особенно, когда речь идет не об одном документе. К тому же, при написании сумм прописью не исключены грамматические ошибки, на поиск и устранение которых также потребуются дополнительные трудозатраты. К счастью, в Эксель можно автоматизировать данный процесс и ниже мы рассмотрим, как именно это сделать.

Специальная надстройка NUM2TEXT

В программе Excel не предусмотрен какой-либо специальный инструмент, позволяющий выполнить автоматический перевод цифр в соответствующие текстовые значения. Однако в этом деле может помочь специальная надстройка NUM2TEXT, которая работает через функцию.

Алгоритм действий следующий:

1. В верхней строке окна программы кликаем по меню “Файл”.
2. В перечне слева выбираем пункт “Параметры”.
3. В открывшихся параметрах программы кликаем по разделу “Надстройки”. В нижней части окна щелкаем по кнопке “Перейти” рядом с пунктом “Управление”, значение которого должно быть установлено как “Надстройки Excel”.
4. На экране отобразится окно надстроек, в котором нажимаем кнопку “Обзор”.
5. Откроется окно обзора, где мы переходим в папку, в которой находится заранее скачанный файл надстройки “NUM2TEXT.xla”. Выбираем его и щелкаем кнопку OK.Примечание: Официального сайта, с которого можно скачать надстройку, к сожалению нет, однако, в интернете ее достаточно легко найти, воспользовавшись поисковой системой. Главное – скачивайте файл с надежных источников, обязательно с расширением “xla”. Не лишним будет после загрузки файла на компьютер проверить его с помощью антивирусной программы.
6. Система снова вернет нас в прежнее окно, где мы можем заметить, что в списке надстроек появилась и только что добавленная. Ставим флажок напротив нее (если это по какой-то причине не произошло автоматически), затем нажимаем кнопку OK.
7. Теперь нам нужно убедиться, что надстройка работает. Для этого пишем в любой незаполненной ячейке случайное число. После этого переходим в другую свободную ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
8. В окне вставки функции выбираем категорию “Полный алфавитный перечень”, находим строку “Сумма_прописью”, щелкаем по ней, затем – по кнопке OK.
9. Откроется окно с единственным аргументом функции – “Сумма”. В поле для ввода значения аргумента мы можем написать как обычное число, так и указать адрес ячейки, который печатаем вручную или выбираем в таблице кликом по нужному элементу (при этом, курсор должен находится в поле для ввода информации). После того, как значение аргумента заполнено, щелкаем OK.
10. В ячейке с функцией отобразился результат в виде суммы прописью, что и требовалось.Причем, если мы поменяем число в исходной ячейке, на которую ссылается функция, значение в финальной ячейке также изменится.

Ручной ввод формулы функции

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

В данном случае, формула выглядит следующим образом:

=Сумма_прописью(Сумма)

В качестве аргумента “Сумма”, как мы ранее уже отметили, может быть указано как конкретное число, так и ссылка на ячейку.

Например, вот как выглядит финальная формула с числом 21: =Сумма_прописью(21) .

После того, как она набрана в нужной ячейке, нам остается только нажать Enter, чтобы получить результат.

Или же мы можем вместо числа указать адрес ячейки: =Сумма_прописью(B3) .

После нажатия клавиши Enter мы также получаем результат в виде суммы прописью в ячейке с формулой.

Заключение

Несмотря на то, что в Эксель по умолчанию не предусмотрена функция, позволяющая перевести числовое значение в сумму прописью, тем не менее, выполнить данную задачу в программе можно, установив специальную надстройку. Данный процесс не займет много времени и не требует особых пользовательских навыков.

Сумма прописью в Microsoft Excel

При заполнении различных финансовых документов часто требуется прописать сумму не только числом, но и прописью. Конечно, это занимает гораздо больше времени, чем обычное написание с помощью цифр. Если подобным образом нужно заполнить не одно, а много документов, то временные потери становятся огромными. Кроме того, именно в записи суммы прописью наиболее часто встречаются грамматические ошибки. Давайте выясним, как сделать так, чтобы числа прописью вносились автоматически.

Использование надстройки

В Эксель нет встроенного инструмента, который бы помогал автоматически переводить цифры в слова. Поэтому для решения поставленной задачи используют специальные надстройки.

Одной из самых удобных является надстройка NUM2TEXT. Она позволяет поменять цифры на буквы через Мастер функций.

1. Открываем программу Excel и переходим во вкладку «Файл».

Перемещаемся в раздел «Параметры».

В активном окне параметров переходим в раздел «Надстройки».

Открывается небольшое окно надстроек Excel. Жмем на кнопку «Обзор…».

В открывшемся окне ищем предварительно скачанный и сохраненный на жесткий диск компьютера файл надстройки NUM2TEXT.xla. Выделяем его и жмем на кнопку «OK».

Мы видим, что данный элемент появился среди доступных надстроек. Ставим галочку около пункта NUM2TEXT и жмем на кнопку «OK».

Запускается Мастер функций. В полном алфавитном перечне функций ищем запись «Сумма_прописью». Её раньше не было, но она появилась тут после установки надстройки. Выделяем эту функцию. Жмем на кнопку «OK».

Отрывается окно аргументов функции Сумма_прописью. Оно содержит только одно поле «Сумма». Сюда можно записать обычное число. Оно в выделенной ячейке отобразиться в формате записанной прописью денежной суммы в рублях и копейках.

Можно в поле занести адрес любой ячейки. Это делается либо путем ручной записи координат этой ячейки, либо простым кликом по ней в то время, когда курсор находится в поле параметра «Сумма». Жмем на кнопку «OK».

После этого, любое число, которое записано в ячейку, указанную вами, будет отображаться в денежном виде прописью в том месте, где установлена формула функции.

Функцию можно также записать и вручную без вызова мастера функций. Она имеет синтаксис Сумма_прописью(сумма) или Сумма_прописью(координаты_ячейки). Таким образом, если вы в ячейке запишите формулу =Сумма_прописью(5) , то после нажатия кнопки ENTER в этой ячейке отобразиться надпись «Пять рублей 00 копеек».

Если вы введете в ячейку формулу =Сумма_прописью(A2) , то в таком случае, любое число, внесенное в ячейку A2 будет отображаться тут денежной суммой прописью.

Как видим, несмотря на то, что в Excel нет встроенного инструмента для преобразования чисел в сумму прописью, эту возможность можно довольно легко получить, просто установив необходимую надстройку в программу.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Excel как сумму вывести прописью в соседнюю ячейку?

0) Открыть книгу. Скопировать то что ниже ‘—————

1) Нажать Alt+F11

2) Insert — Module

3) Вставить скопированное

4) В ячейку A1 ввести 123456789, а в любую другую — ввести формулу =СУММА_ПРОП(A1)

‘Пользовательская функция СУММА_ПРОПИСЬЮ для применения на рабочем листе.
‘Возвращает сумму прописью в указанной валюте.
‘Работает для сумм, по модулю меньших квадриллиона. Все другие суммы функция считает нулём.
‘Для отрицательных сумм добавляется слово «Минус».
‘Первый аргумент «Ячейка» — ссылка на ячейку, содержащую сумму, которую надо отобразить прописью.
‘Второй аргумент (необязательный) «Валюта» — строка с кодом валюты. Возможные значения:
‘»USD» — доллары,
‘»EUR» — евро,
‘»RUR», любая другая строка (кроме вышеупомянутых строк) или аргумент пропущен вообще — рубли.
‘Третий аргумент (необязательный) «Копейки» — формат вывода копеек. Возможные значения:
‘1 — копейки числом,
‘2 — копейки числом с лидирующим нулём при необходимости,
‘0, любое другое число (кроме вышеупомянутых чисел) или аргумент пропущен вообще — копейки прописью.
Public Function СУММА_ПРОП(ByVal Ячейка As Range, Optional ByVal Валюта As String = «RUR», Optional ByVal Копейки As Integer = 0) As String
If Ячейка.Count <> 1 Then
СУММА_ПРОП = «Первый аргумент функции «»СУММАПРОПИСЬЮ»» должен содержать ссылку на диапазон, занимающий одну ячейку!»
Exit Function
End If
СУММА_ПРОП = SumInWords(Ячейка.Value, Валюта, Копейки)
End Function

‘Функция возвращает сумму прописью в указанной валюте.
‘Первый аргумент — число (сумма).
‘Второй аргумент — валюта. «USD» — доллары, «EUR» — евро, любая другая строка — рубли.
‘Третий аргумент — формат вывода копеек:
‘1 — копейки числом,
‘2 — копейки числом с лидирующим нулём при необходимости,
‘любое другое число — копейки прописью.
Private Function SumInWords(ByVal Arg As Double, ByVal Curr As String, ByVal Kop As Integer) As String
If Not IsNumeric(Arg) Then
SumInWords = «»
Exit Function
End If
‘Округление до двух знаков после запятой
Arg = Round(Arg, 2)

‘Будущий результат (возврат) этой функции
Dim Result As String
Result = «»

‘Для отрицательных чисел
If Arg 10 And Tens 10 And Tens 10 And Tens 10 And Tens 999999999999999# Then
ToQuadrillion = «ноль»
Exit Function
End If

‘Будущий результат (возврат) этой функции
Dim Result As String
Result = «»

‘Триллионы в переданномм числе
Dim Trillions As Long
Trillions = Fix(Arg / 1000000000000#)
If Trillions > 0 Then
Result = ToTrillion(Trillions)
‘Единицы
Dim Unitys As Integer
Unitys = Trillions — Fix(Trillions / 10) * 10
‘Десятки (два последних знака)
Dim Tens As Integer
Tens = Trillions — Fix(Trillions / 100) * 100
If Tens > 10 And Tens 0 Then
Result = Result & ToTrillion(Rest)
End If

ToQuadrillion = Trim(Result)
End Function

‘Функция возвращает число прописью.
‘Аргумент — целое положительное число, меньшее триллиона.
Private Function ToTrillion(ByVal Arg As Double) As String
Arg = Abs(Fix(Arg))
If Arg 999999999999# Then
ToTrillion = «ноль»
Exit Function
End If

‘Будущий результат (возврат) этой функции
Dim Result As String
Result = «»

‘Миллиарды в переданномм числе
Dim Milliards As Long
Milliards = Fix(Arg / 1000000000)
If Milliards > 0 Then
Result = ToMilliard(Milliards)
‘Единицы
Dim Unitys As Integer
Unitys = Milliards — Fix(Milliards / 10) * 10
‘Десятки (два последних знака)
Dim Tens As Integer
Tens = Milliards — Fix(Milliards / 100) * 100
If Tens > 10 And Tens 0 Then
Result = Result & ToMilliard(Rest)
End If

ToTrillion = Trim(Result)
End Function

Число прописью в Excel (динамический вариант)

Запишем число прописью в Excel без использования VBA . Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 коп.

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

В статье Число прописью (статичный вариант) приведено решение этой задачи позволяющей перевести число в текстовую форму по следующему алгоритму:

• вводим число в определенную ячейку;
• с помощью формул, вспомогательных диапазонов с текстом и имен получаем число прописью;
• копируем результат вычисления формулы (число прописью) в Буфер обмена ;
• вставляем текст в любую открытую книгу «Как значение» ( Главная/ Буфер обмена/ Вставить/ Вставить как значение ).

Это не всегда удобно. Хочется по аналогии с функциями на VBA написать что то вроде =ЧислоПрописью(А1) и получить результат. Все промежуточные вычисления должны быть «за кадром». Но, создание пользовательских функций это прерогатива VBA.

Тем не менее, можно предложить следующее решение с помощью обычных формул:

• разместить в Личной книге макросов (PERSONAL.XLSB) вспомогательные диапазоны, содержащие некоторые числа прописью (от 0 до 999);
• создать формулу, переводящую в текст любое число от 0 до 1 млрд. с помощью вспомогательных диапазонов и имен ;
• в любой книге, где требуется записать число прописью создать Именованную формулуЧислоПрописью с относительной ссылкой на исходное число (относительную ссылку можно создать так, чтобы она позволяла выводить число прописью, например, в соседней ячейке слева от исходного числа).

В результате вышеуказанных манипуляций можно будет, например, записав в ячейке G 6 исходное число, а в ячейке G7 формулу =ЧислоПрописью , и получить нужный результат.

Все это реализовано в файле примера .

Как и в примере статьи Число прописью (статичный вариант) на листе Служ размещены вспомогательные диапазоны, содержащие некоторые числа прописью в 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 DataMicrosoftExcelXLStart, откуда она будет автоматически загружается при каждом запуске приложения Excel. В Windows Vista эта книга хранится в папке C:Usersимя_пользователяApplication DataMicrosoftExcelXLStart.

Если Личная книга макросов еще не была создана, то скопируйте лист Служ из файла примера в новую книгу, и сохраните ее в директорию C:Documents and Settingsимя_пользователяApplication DataMicrosoftExcelXLStart (для XP) под именем PERSONAL.XLSB.

Если Личная книга макросов уже была ранее создана, то через меню Вид/ Окно/ Отобразить отобразите ее, скопируйте в нее лист Служ, сохраните Личную книгу макросов (можно ее потом скрыть Вид/ Окно/ Скрыть ).

Теперь откройте книгу, в которой нужно записать число прописью . Исходное число разместите, например, в ячейке А1 . Введите в нужную ячейку формулу: =ЕСЛИ(A1;СЖПРОБЕЛЫ( ИНДЕКС(PERSONAL.XLSB!Миллионы;ОКРУГЛВНИЗ(A1;-6)/1000000+1)&» «& ИНДЕКС(PERSONAL.XLSB!тысячи;ОСТАТ(ОТБР(A1/1000);1000)+1)&» «& ИНДЕКС(PERSONAL.XLSB!Единицы;ОСТАТ(A1;1000)+1)&» руб. «&ТЕКСТ((A1-ЦЕЛОЕ(A1))*100;»00″)&» коп.»);»ноль руб. 00 коп.»)

Формула отобразит число прописью. Ячейки, содержащие исходное число и формулу, можно перенести в любое место в книге или даже в другую книгу.

Чтобы еще больше приблизиться к идеалу создадим именованную формулу ЧислоПрописью . Для этого:

• выделите ячейку B1 ;
• через меню Формулы/ Определенные имена/ Присвоить имя создайте именованную формулу ЧислоПрописью;
• в поле Диапазон введите формулу указанную выше:
• нажмите ОК;
• в ячейке В1 введите формулу =ЧислоПрописью
• ячейка В1 будет содержать число прописью.

Теперь ячейки А1 и В1 можно скопировать и перенести в любое место в книге, но они должны быть всегда на одной строке и в соседних столбцах, т.к. именованной формуле использована относительная ссылка .

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