Рубли и копейки в excel - IT Новости из мира ПК
Semenalidery.com

IT Новости из мира ПК
339 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Рубли и копейки в 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. В ячейке с функцией отобразился результат в виде суммы прописью, что и требовалось.Причем, если мы поменяем число в исходной ячейке, на которую ссылается функция, значение в финальной ячейке также изменится.
Читать еще:  Как заполнить нумерацию в таблице excel

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Функцию можно также записать и вручную без вызова мастера функций. Она имеет синтаксис Сумма_прописью(сумма) или Сумма_прописью(координаты_ячейки). Таким образом, если вы в ячейке запишите формулу =Сумма_прописью(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 можно скопировать и перенести в любое место в книге, но они должны быть всегда на одной строке и в соседних столбцах, т.к. именованной формуле использована относительная ссылка .

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

    Ссылка на основную публикацию
    Adblock
    detector