Преобразование чисел, дат и строк в настраиваемый текстовый формат из кода VBA Excel с помощью функции Format. Синтаксис, параметры, символы, примеры.
Определение функции Format
Format – это функция, которая преобразует число, дату или строку в текст, отформатированный в соответствии с именованным выражением формата или инструкциями, составленными из специальных символов.
Синтаксис и параметры
Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
- Expression – любое допустимое выражение (переменная), возвращающее числовое значение или строку (обязательный параметр).
- FormatExpression – выражение формата, именованное или содержащее инструкции из специальных символов (необязательный параметр).
- FirstDayOfWeek – константа, задающая первый день недели (необязательный параметр).
- FirstWeekOfYear – константа, задающая первую неделю года (необязательный параметр).
Именованные выражения форматов
Именные форматы даты и времени
| Имя формата | Описание |
|---|---|
| General Date | Стандартное отображение даты и времени в соответствии с параметрами системы. |
| Long Date | Длинный формат даты. |
| Medium Date | Средний формат даты. |
| Short Date | Краткий формат даты. |
| Long Time | Длинный формат времени. |
| Medium Time | Средний формат времени. |
| Short Time | Краткий формат времени. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:
|
1 2 3 4 5 6 7 8 9 |
Sub FormatDateTime() MsgBox "General Date: " & Format(Now, "General Date") & vbNewLine _ & vbNewLine & "Long Date: " & Format(Now, "Long Date") & vbNewLine _ & vbNewLine & "Medium Date: " & Format(Now, "Medium Date") & vbNewLine _ & vbNewLine & "Short Date: " & Format(Now, "Short Date") & vbNewLine _ & vbNewLine & "Long Time: " & Format(Now, "Long Time") & vbNewLine _ & vbNewLine & "Medium Time: " & Format(Now, "Medium Time") & vbNewLine _ & vbNewLine & "Short Time: " & Format(Now, "Short Time") End Sub |
Скорее всего, результат будет таким:
Именованные форматы чисел
| Имя формата | Описание |
|---|---|
| General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
| Currency | Денежный формат. |
| Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
| Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
| Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
| Scientific | Отображение числа в экспоненциальном виде. |
| Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
| True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
| On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub FormatNumber() Dim n As Double n = 2641387.7381962 'n = 0 MsgBox "Форматируемое число = " & n & vbNewLine _ & vbNewLine & "General Number: " & Format(n, "General Number") & vbNewLine _ & vbNewLine & "Currency: " & Format(n, "Currency") & vbNewLine _ & vbNewLine & "Fixed: " & Format(n, "Fixed") & vbNewLine _ & vbNewLine & "Standard: " & Format(n, "Standard") & vbNewLine _ & vbNewLine & "Percent: " & Format(n, "Percent") & vbNewLine _ & vbNewLine & "Scientific: " & Format(n, "Scientific") & vbNewLine _ & vbNewLine & "Yes/No: " & Format(n, "Yes/No") & vbNewLine _ & vbNewLine & "True/False: " & Format(n, "True/False") & vbNewLine _ & vbNewLine & "On/Off: " & Format(n, "On/Off") End Sub |
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).
Специальные символы для выражений форматов
Символы для форматов даты и времени
| Символ | Описание |
|---|---|
| Точка (.) | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа. |
| Пробел | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью. |
| Двоеточие (:) | Разделитель компонентов времени (часы, минуты, секунды). |
| d | День в виде числа без нуля в начале (1–31). |
| dd | День в виде числа с нулем в начале (01–31). |
| m | Месяц в виде числа без нуля в начале (1–12). Если (m) следует после (h) или (hh), отображаются минуты (0–59). |
| mm | Месяц в виде числа с нулем в начале (01–12). Если (mm) следует после (h) или (hh), отображаются минуты (00–59). |
| mmm | Месяц прописью в сокращенном виде (янв–дек). |
| mmmm | Полное название месяца (январь–декабрь). |
| y | День года в виде числа (1–366). |
| yy | Год в виде 2-значного числа (00–99). |
| yyyy | Год в виде 4-значного числа (1900–9999). |
| h | Часы в виде числа без нуля в начале (0–23). |
| hh | Часы в виде числа с нулем в начале (00–23). |
| n (m) | Минуты в виде числа без нуля в начале (0–59). |
| nn (mm) | Минуты в виде числа с нулем в начале (00–59). |
| s | Секунды в виде числа без нуля в начале (0–59). |
| ss | Секунды в виде числа с нулем в начале (00–59). |
В этой таблице перечислены далеко не все символы для выражений форматов даты и времени. Вы можете ознакомиться со всеми символами, в том числе и для форматирования чисел, на сайте разработчика.
Примеры отображения даты с помощью разных по количеству наборов символа d:
|
1 2 3 4 5 6 7 8 |
Sub DataIsD() MsgBox "d: " & Format(Now, "d") & vbNewLine _ & vbNewLine & "dd: " & Format(Now, "dd") & vbNewLine _ & vbNewLine & "ddd: " & Format(Now, "ddd") & vbNewLine _ & vbNewLine & "dddd: " & Format(Now, "dddd") & vbNewLine _ & vbNewLine & "ddddd: " & Format(Now, "ddddd") & vbNewLine _ & vbNewLine & "dddddd: " & Format(Now, "dddddd") End Sub |
Символы для числовых форматов
| Символ | Описание |
|---|---|
| Точка (.) | Десятичный разделитель. |
| Запятая (,) | Разделитель групп разрядов. В отображаемых числах заполняется пробелом. |
| (0) | Заполнитель, который отображает цифру или ноль. Используется, когда нужны ведущие нули или нули в конце числа. |
| (#) | Заполнитель, который отображает цифру или ничего не отображает. Используется, когда не нужны ведущие нули или нули в конце числа. |
| (%) | Заполнитель процента. Выражение умножается на 100, а знак процента (%) вставляется на той позиции, где он указан в строке формата. |
| (E- E+ e- e+) | Экспоненциальный формат. |
Примеры использования символов в выражениях числовых форматов VBA Excel:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub FormatNumber2() Dim n As Double n = 2641387.7381962 'n = 0.2397842 MsgBox "Форматируемое число = " & n & vbNewLine _ & vbNewLine & "0.##: " & Format(n, "0.##") & vbNewLine _ & vbNewLine & "000.###: " & Format(n, "000.###") & vbNewLine _ & vbNewLine & "#,###.###: " & Format(n, "#,###.###") & vbNewLine _ & vbNewLine & "0 %: " & Format(n, "0 %") & vbNewLine _ & vbNewLine & "0.### E-: " & Format(n, "0.### E-") & vbNewLine _ & vbNewLine & "0.### E+: " & Format(n, "0.### E+") End Sub |
Символы для текстовых форматов
| Символ | Описание |
|---|---|
| At-символ (@) | Заполнитель для символов, отображающий знак или пробел. |
| Амперсанд (&) | Заполнитель для символов, отображающий знак или ничего (пустая строка). |
| Меньше (<) | Принудительный перевод всех буквенных символов в нижний регистр. |
| Больше (>) | Принудительный перевод всех буквенных символов в верхний регистр. |
Примеры использования символов в выражениях строковых форматов VBA Excel:
|
1 2 3 4 5 6 |
Sub FormatString() MsgBox "Номер телефона: " & Format("1234567890", "+7 (@@@) @@@-@@-@@") & vbNewLine _ & vbNewLine & "Серия и номер паспорта: " & Format("1234567890", "&& && &&&&") & vbNewLine _ & vbNewLine & "Нижний регистр: " & Format("Нижний регистр", "<") & vbNewLine _ & vbNewLine & "Верхний регистр: " & Format("Верхний регистр", ">") End Sub |
Форматы для различных значений одного выражения
Различные форматы для разных числовых значений
В выражении формата для чисел предусмотрено от одного до четырех разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от значения, возвращенного параметром Expression функции Format.
| Количество разделов | Результат форматирования |
|---|---|
| Один раздел | Выражение формата применяется ко всем значениям. |
| Два раздела | Первый раздел применяется к положительным значениям и нулям, второй – к отрицательным значениям. |
| Три раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям. |
| Четыре раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям, четвертый – к значениям Null. |
Пример использования четырех разделов в выражении формата числовых значений:
|
1 2 3 4 5 6 7 8 9 10 |
Sub FormatDifferentValues() MsgBox "Число 1234,5678: " & _ Format(1234.5678, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Число -25: " & _ Format(-25, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Число 0: " & _ Format(0, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") _ & vbNewLine & vbNewLine & "Null: " & _ Format(Null, "#,##0.00 руб.;Отрицательное число;Ноль рублей;Значение Null") End Sub |
Различные форматы для разных строковых значений
В выражении формата для строк предусмотрено до двух разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от текста, возвращенного параметром Expression функции Format.
| Количество разделов | Результат форматирования |
|---|---|
| Один раздел | Выражение формата применяется ко всем строковым данным. |
| Два раздела | Первый раздел применяется к строковым данным, второй – к значениям Null и пустым строкам («»). |
Пример использования двух разделов в выражении формата строк:
|
1 2 3 4 5 6 7 8 9 10 |
Sub FormatString2() MsgBox "Строка «Белка»: " & _ Format("Белка", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Пустая строка: " & _ Format("", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Строка «Null»: " & _ Format("Null", "@;Пустая строка или Null") _ & vbNewLine & vbNewLine & "Значение Null: " & _ Format(Null, "@;Пустая строка или Null") End Sub |
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.
Исправить в табл. «Символы для форматов даты и времени» для символов m и mm количество месяцев (1-31) и (01-31)
Спасибо, Владимир! Исправил.
Евгений, здравствуйте!
Никак не получается решить данную задачку, может поможете.
В столбце «A» из списка выбираются значки валют — ₽, $, €, £. В столбце «B» прописаны числовые значения (стоимость). Необходимо сделать функцию, которая бы при выборе одного из значка валют, соответствующим образом меняла формат числа («#,##0.00 $») из соседней ячейки (столбец «B»).
Например:
A B
$ 100 $
₽ 1,000 ₽
€ 10 €
Заранее спасибо!
Здравствуйте, Николай!
Выделите столбец B и задайте три правила условного форматирования в зависимости от содержимого ячейки A1 (по относительному адресу, без значков $).
Правило 1: Формула —
=A1="$", Формат —Финансовый $ французский (Канада)Правило 2: Формула —
=A1="₽", Формат —Финансовый ₽Правило 3: Формула —
=A1="€", Формат —€ Евро