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

Как быстро преобразовать числа валюты в слова в Excel?

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

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

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

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

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

Это не всегда практично. Я хочу написать что-то вроде =NumbersWrite(A1) и получить результат, как функция VBA. Все промежуточные расчеты должны происходить «за кулисами». Однако это язык VBA для создания пользовательских функций.

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

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

С помощью описанной выше манипуляции можно, например, записать исходное число в ячейку G6 и формулу =StoryWritten в ячейку G7, и получить желаемый результат.

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

Для листа обслуживания, как в примере статьи Число словами (статическая версия), некоторые столбцы содержат числа в Excel.

На этот раз диапазоны чисел длиннее (от 1 до 999) и содержат одну тысячу и один миллион, включая склонения. Кроме того, был создан диапазон склонения для слова «Рубли».

Теперь вы можете написать формулу, которая преобразует исходное число (ячейка B6) в текст: =SLAP( INDEX(Miliony;OSTAT(OSTAT(B6/10000);1000000)+1)&»»& INDEX(Tysiące;OSTAT(OSTAT(B6/1000);1000)+1)&»»& INDEX(Jednostki;OSTAT(B6;1000)+1))).

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

В Windows XP эта рабочая книга хранится в C:Documents and Settings, User_NameApplication_DataMicrosoftExcelXLStart, откуда она будет автоматически загружаться при каждом запуске Excel. В Windows Vista эта рабочая книга хранится в папке C:UsersiPlan DataMicrosoftExcelXLStart.

Читайте так же:
Как в Excel заменить текст соответствующими картинками?

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

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

Теперь откройте рабочую книгу, в которой вы хотите записать число словами. Поместите исходное число, например, в ячейку A1. Введите формулу в нужную ячейку: =If(A1;SPACE( INDEX(PERSONAL.XLSB!Millions;OKLVNIZ(A1;-6)/1000000+1)&»»& INDEX(PERSONAL.XLSB!Thousands;OSTAT(A1/1000);1000)+1)&»»& INDEX(PERSONAL.XLSB!Units;OSTAT(A1;1000)+1)&»». «&TEXT((A1-ONE(A1))*100; «00»)&» kop. «); «0 руб. 00 коп»).

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

Чтобы еще больше приблизиться к идеалу, мы можем создать формулу NumberWrite :

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

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

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

Как быстро преобразовать числа валюты в слова в Excel?

Объект Cells предназначен для доступа к ячейкам в стиле R1C1 к одной ячейке. Range — в стиле A1 к области (коллекции) ячеек. Удобство объекта Range в том, что можно, при использовании оператор with, обращаться к нескольким свойствам и методам. Объект Rows возвращает коллекцию строк и Columns — коллекцию столбцов объекта Range (вместо этих свойств можно использовать свойства EntireRow и EntireColumn).

Как хранить значения сразу в нескольких ячейках?

Объект Range (ExcelRange) используется для записи в несколько ячеек (диапазонов). Пример получения объекта Range для диапазона ячеек.

Дельфи

Заметьте, что при обращении к свойству Range и Cells объекта Range, адресация будет уже работать относительно области, указанной в объекте Range. Например, нижеприведенный код будет указывать не на ячейку "A1", как сразу можно подумать, а на "C2":

А вот такой код вернет ячейку с адресом "D3":

Читайте так же:
Как быстро преобразовать дату рождения в возраст в Excel?

Как определяется область выбранной ячейки и ее границы?

Чтобы получить область (области) выделенных ячеек, необходимо получить объект Range из свойства Selection объекта Excel.Application и получить доступ к свойству Range.Areas.

Delphi:

Как сохранить значения в ячейке (value, value2, text, formula)?

Начиная с версии Excel XP (10.0), свойство Value имеет параметр. Отличие Value2 от Value в том, что Value2 не поддерживает "форматирования на лету" для типов Currency, Double и Date. Свойство Text (только чтение для Range) возвращает текст в ячейке. Свойство Formula выполняет те же функции, что и Value, с поддержкой "форматирования на лету", а также позволяет записывать в ячейку формулы со ссылками в стиле A1 (в идеале английские, но что на практике, смотрите здесь). Для стиля R1C1 используется свойство FormulaR1C1.

Для записи локализованных ("русских") форматов данных и формул используются свойства с окончанием Local, например FormulaLocal.

Delphi:

Если вы попытаетесь написать макрос в Excel, вы увидите, что значения хранятся в свойстве FormulaR1C1. Вы можете сохранить его в свойстве Formula.

Внимание! При записи в свойство Formula, если это не формула, следите, чтобы текст не начинался с символов "=", "+", "-", "*", "/". Или просто к тексту прибавляйте в начало знак апострофа (код символа 39):

Delphi:

Насколько быстро работает Range по сравнению с Cells?

Запись в Range работает быстрее, но не существенно (смотрите в Demo-проекте пример "Как сделать, чтобы Excel работал быстрее?"). Это связано с тем, что в Excel TLB свойство Cells.Item[R, C] имеет тип OleVariant и, как следствие, позднее связывание. В C# между Range и Cells нет никакой разницы.

Для перевода из координат R1C1 в A1 удобно пользоваться "самодельными" функциями, например:

Delphi:

Что такое используемый диапазон? Как найти последнюю использованную ячейку? Что такое специальные клетки? Что такое CurrentRegion?

UsedRange — прямоугольная область, включающая все заполненные ячейки и незаполненные, в промежутках между заполненными ячейками, на листе. Координаты области не обязательно начинаются в ячейке A1. Также для определения координат различных ячеек можно использовать объект SpecialCells, например, с параметром xlCellTypeLastCell для нахождения последней (крайней справа снизу) используемой ячейки. CurrentRegion возвращает область вокруг ячейки, выделенную пустыми (незаполненными) ячейками. End — находит последнюю ячейку в строке или столбце перед первой попавшейся пустой ячейкой, или первую заполненную, если вызывать метод End для пустой ячейки.

Дельфы :.

Как узнать адрес ячейки?

Delphi:

Должна ли ячейка/область быть выделена для того, чтобы ввести в нее данные?

Это не обязательно. Вы можете указать адрес области ячеек в объекте Range для выбранного объекта Worksheet (и/или Workbook). Выделение или активизация только замедлят работу вашей программы. Более того, метод Select может быть вызван только на рабочем листе в активной рабочей книге. Не используйте Select и Activate без необходимости.

Читайте так же:
Как быстро удалить несколько пустых столбцов в Excel?

В чем отличие Range.Activate от Range.Select?

И метод Activate и Select делают одно и то же — выделяют (активируют) ячейку. Разница лишь в том, что метод Activate позволяет выбрать только одну ячейку на листе или сделать активной любую ячейку в области, выделенной методом Select. Метод Select позволяет выделять одну и более областей ячеек.

Как установить свойству ячейки NumberFormat "общий" формат, текстовый формат, формат даты, числа, валюты?

Чтобы NumberFormat правильно работал с английскими форматами, не забудьте подключить модуль TrDispCall.

Delphi:

Я ввожу ячейку 385.00 и получаю 385. Почему?

Потому что в ячейке установлен "общий" формат (general), который отсекает незначащие цифры. В данном примере всегда будут указываться 2 цифры после запятой:

Дельфи :

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

Лучше числа не записывать в ячейку как текст и не надеяться, что Excel всегда сможет "на лету" преобразовать текст верно. Вы никогда не можете быть уверенными, какие локальные установки формата чисел будут установлены на компьютере пользователя. Всегда перед записью переводите записываемые числа из текста в число (Float или Integer) в своей программе.

Делаю экспорт в Excel, допустим, текст "000069987", а он выводит в ячейку 69987, т.е. удаляет лидирующие нули. Как мне сделать, чтобы в ячейку выводилось 000069987?

При записи текста, содержащего одни цифры, Excel пытается его преобразовать в число. Чтобы избажать такой "помощи" со стороны Excel’я, перед записью в ячейку установите в свойство NumberFormat текстовый формат или добавьте перед текстом символ апострофа "’" (код символа 39).

Дельфийский

Как очистить область ячейки? Можно ли определить, пуста ли ячейка Excel?

Delphi:

Очистить радикально (данные, форматы, заметки и др.) ), вы можете вызвать метод Clear.

Как назвать область ячейки? Как обратиться к ячейке по имени? Существует ли такое имя на рабочем листе? Как присвоить имя области ячейки?

Delphi:

Если вы дадите области имя, которое уже существует на листе, предыдущее имя будет потеряно, т.е. имя будет перезаписано. Также можно присвоить имя области ячеек на неактивном листе. Можно определить адрес ячейки как текст (не обязательно ссылку на объект Range). Это также возможно в стиле R1C1 путем указания адреса области ячейки в параметре RefersToR1C1.

Читайте так же:
Как в Excel выделить все ячейки с комментариями?

Как объединить ячейки? Как узнать, что ячейка является частью объединенной области, и определить границы этой области?

Delphi:

Смотрите ниже, как сделать высоту строк объединенных ячеек автофиксированной.

Как сохранить данные таблицы вариантов в Excel?

Запись данных из вариантного массива (VarArray) очень хорошо расписана в статьях "По волнам интеграции… III" и "Зарисовка на тему экспорта в Excel". Для разнообразия, приведу еще раз этот вариант быстрого экспорта в Excel.

Внимание. Пишете ли вы один ряд или два, МАССИВ ДОЛЖЕН БЫТЬ ДВА! Таким образом, при создании массива вы должны указать ВЕСЬ заполняемый диапазон в адресе ячейки varData.

Delphi:

Есть ли способ считывания данных из области ячеек в массив?

Точно также, как и при экспорте, только самому создавать массив не нужно — Excel все сделает за вас. В принципе, после получения данных в массив, Excel уже не нужен, и от него можно отсоединиться.

Delphi:

Почему при выгрузке данных в Excel я не могу записать строки больше, чем 65536?

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

Как писать одновременно в ячейках нескольких листов?

Чтобы ввести данные сразу в несколько листов, можно объединить листы методом Worksheets. Выберите метод и используйте метод FillAcrossSheets

Delphi:

Как изменить фон и цвет шрифта в ячейке?

См. свойства Font и Interior объекта Range.

Как мне изменить свойства шрифта текста в ячейке (цвет, размер, имя)?

Объекты диапазона имеют свойство Characters, которое можно использовать для изменения текстовых значений ячейки.

Delphi:

Можно ли добавить текст в ячейку или строку формул, который можно редактировать с помощью F2, найдя положение курсора?

Ни в коем случае! Режим редактирования ячеек доступен в Excel. Объект приложения становится полностью недоступным через OLE.

Не могли бы вы поделиться тем, как я изменяю выравнивание/угол и отступ текста в ячейках?

Смотрите свойства HorizontalAlignment, VerticalAlignment, AddIndent и Orientation объекта Range

Delphi:

Область ячеек (Borders) имеет границы, но как их задать?

См. свойство «Границы» объекта Range.

Дельфия

Как скопировать форматы и формулы из строки в нижнюю область (Автозаполнение)?

Это самый удобный способ копирования форматов и формул для расширения области данных при использовании шаблонов. Предполагается, что между BEGIN/END подготовлены ячейки шаблона (форматирование, названная область DataRange для данных).

Delphi:

Как скопировать область ячейки со всеми форматами? Как скопировать только значения ячеек?

Читайте так же:
Как быстро создать простой список дел в Excel?

Метод Copy может не только копировать содержимое области ячеек в буфер обмена (с пустым параметром), но и позволяет скопировать определенный адрес ячейки. Чтобы вставить только выбранные параметры из вставленной ячейки, укажите необходимый XlPasteType (первый аргумент) при использовании метода PasteSpecial.

Delphi:

Каков наилучший способ копирования области так, чтобы сохранялись размеры строк и столбцов?

Из-за особенностей копирования строки и столбцы не сохраняются. Сохранение размеров столбцов и строк может быть выполнено несколькими способами:

Delphi:

Как сделать так, чтобы строки в ячейке автоматически поворачивались?

Чтобы обернуть слова в ячейке, задайте свойство WrapText объекта Range.

Дельфи :

Как вставить несколько строк/столбцов? Как удалить несколько строк/столбцов? Как я могу скрывать/показывать строки и столбцы? Как программно изменить высоту строки или ширину столбца?

Delphi:

Возможно ли настроить высоту или ширину ячеек, чтобы отобразить весь текст сразу?

Используйте метод AutoFit объекта Range для отображения всего текста в ячейке или области ячейки.

Delphi:

Как лучше всего автоматически установить высоту строк для объединенных ячеек?

AutoFit не работает при согласовании высоты объединенных ячеек. Это было решено путем разработки простого метода (адаптированного отсюда для Delphi). Для объединенных ячеек в одном ряду это работает. Выберите одну из ячеек объединенной области для текста обводки (свойство WrapText должно быть включено).

Из Дельфы :.

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

Как программно "заморозить" строки/столбцы?

Из Дельфов :.

Как добавить примечание в ячейку? Как удалить примечание? Есть ли способ изменить атрибуты шрифта примечания?

Комментарий — это своеобразный объект Shape, привязанный к определенному объекту Range.

Delphi:

Как добавить URL-адрес? Как сделать гиперссылку с изображением?

Дельфы

Как отсортировать область ячейки?

Пример сортировки всех данных на листе в первый, второй и третий столбцы.

Delphi:

Как искать значения в области ячейки или во всем листе?

Для поиска в области ячеек задайте диапазон ячеек при получении ссылки на объект Range. Если нужно искать по всему листу, то укажите UsedRange или просто одну ячейку, например "A1". Метод Find и FindNext возвращают объект Range, если значение найдено, и, если ничего не найдено, то nil (или null в C#).

Delphi:

Если у меня есть ссылка на ячейку, как я могу узнать, к какому листу она относится? Как называется книга?

Ссылку на объект электронной таблицы, содержащий данную ячейку, можно получить через свойство Parent.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector