Формат по образцу в Excel
Как пользоваться форматированием по образцу
Думаю большинство знает такую команду как Формат по образцу она находится на вкладке Главная. Однако не все знают обо всех возможностях данной команды.
Данная команда копирует все форматирование из исходного диапазона, в том числе форматы, границы, условное форматирование, объединение ячеек.
Как правило формат по образцу используется следующим образом:
Приемы применения форматирования по образцу
Создаем горячую клавишу на вставку форматирования
Можно конечно вынести кнопку Формат по образцу на панель быстрого доступа, но это не избавит нас от необходимости пользоваться мышкой при выборе диапазона к которому необходимо применить форматирование.
Гораздо удобнее скопировать формат например сочетанием клавиш Ctrl + C, выбрать нужный диапазон и вставить в него скопированное форматирование например, сочетанием клавиш Ctrl + Shift + V.
Чтобы так сделать, нам понадобится написать небольшой макрос. Вот его код:
Скопируйте код выше и вставьте его себе файл или в персональную книгу макросов.
После этого останется добавить горячую клавишу на выполнение данной команды. Для этого:
Как пользоваться горячей клавишей
Теперь, когда мы создали горячую клавишу посмотрим как ей пользоваться.
Теперь нет необходимости заново копировать формат, можете выбирать следующий диапазон и вставлять форматирование в него.
VBA Excel. Функция Format (синтаксис, параметры, примеры)
Преобразование чисел, дат и строк в настраиваемый текстовый формат из кода VBA Excel с помощью функции Format. Синтаксис, параметры, символы, примеры.
Определение функции Format
Синтаксис и параметры
Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
Именованные выражения форматов
Именные форматы даты и времени
| Имя формата | Описание |
|---|---|
| General Date | Стандартное отображение даты и времени в соответствии с параметрами системы. |
| Long Date | Длинный формат даты. |
| Medium Date | Средний формат даты. |
| Short Date | Краткий формат даты. |
| Long Time | Длинный формат времени. |
| Medium Time | Средний формат времени. |
| Short Time | Краткий формат времени. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:
Скорее всего, результат будет таким:
Именованные форматы чисел
| Имя формата | Описание |
|---|---|
| General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
| Currency | Денежный формат. |
| Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
| Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
| Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
| Scientific | Отображение числа в экспоненциальном виде. |
| Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
| True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
| On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате 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:
Символы для числовых форматов
| Символ | Описание |
|---|---|
| Точка (.) | Десятичный разделитель. |
| Запятая (,) | Разделитель групп разрядов. В отображаемых числах заполняется пробелом. |
| (0) | Заполнитель, который отображает цифру или ноль. Используется, когда нужны ведущие нули или нули в конце числа. |
| (#) | Заполнитель, который отображает цифру или ничего не отображает. Используется, когда не нужны ведущие нули или нули в конце числа. |
| (%) | Заполнитель процента. Выражение умножается на 100, а знак процента (%) вставляется на той позиции, где он указан в строке формата. |
| (E- E+ e- e+) | Экспоненциальный формат. |
Примеры использования символов в выражениях числовых форматов VBA Excel:
Символы для текстовых форматов
| Символ | Описание |
|---|---|
| At-символ (@) | Заполнитель для символов, отображающий знак или пробел. |
| Амперсанд (&) | Заполнитель для символов, отображающий знак или ничего (пустая строка). |
| Меньше ( ) | Принудительный перевод всех буквенных символов в верхний регистр. |
Примеры использования символов в выражениях строковых форматов VBA Excel:
Форматы для различных значений одного выражения
Различные форматы для разных числовых значений
В выражении формата для чисел предусмотрено от одного до четырех разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от значения, возвращенного параметром Expression функции Format.
| Количество разделов | Результат форматирования |
|---|---|
| Один раздел | Выражение формата применяется ко всем значениям. |
| Два раздела | Первый раздел применяется к положительным значениям и нулям, второй – к отрицательным значениям. |
| Три раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям. |
| Четыре раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям, четвертый – к значениям Null. |
Пример использования четырех разделов в выражении формата числовых значений:
Функция Format
Возвращает Variant (String), содержащий выражение expression, отформатированное в соответствии с инструкциями, включенными в выражение формата.
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Синтаксис
Format(Expression, [ Format ], [ FirstDayOfWeek ], [ FirstWeekOfYear ])
Синтаксис функции Format состоит из следующих частей:
| Часть | Описание |
|---|---|
| Expression | Обязательный атрибут. Любое допустимое выражение. |
| Format | Необязательный атрибут. Допустимое именованное или определяемое пользователем выражение формата. |
| FirstDayOfWeek | Необязательный атрибут. Константа, задающая первый день недели. |
| FirstWeekOfYear | Необязательный атрибут. Константа, задающая первую неделю года. |
Параметры
Аргумент firstdayofweek имеет следующие параметры.
| Константа | Значение | Описание |
|---|---|---|
| vbUseSystem | 0 | Использовать настройку API многоязыковой поддержки. |
| vbSunday | 1 | Воскресенье (по умолчанию) |
| vbMonday | 2 | Понедельник |
| vbTuesday | 3 | Вторник |
| vbWednesday | 4 | Среда |
| vbThursday | 5 | Четверг |
| vbFriday | 6 | Пятница |
| vbSaturday | 7 | Суббота |
Аргумент firstweekofyear имеет следующие параметры.
| Константа | Значение | Описание |
|---|---|---|
| vbUseSystem | 0 | Использовать настройку API многоязыковой поддержки. |
| vbFirstJan1 | 1 | Начать с недели, содержащей 1 января (по умолчанию). |
| vbFirstFourDays | 2 | Начать с первой недели, в которой имеется по крайней мере четыре дня в данном году. |
| vbFirstFullWeek | 3 | Начать с первой полной недели года. |
Примечания
| Чтобы отформатировать | Сделайте следующее |
|---|---|
| Числа | Используйте предопределенные именованные числовые форматы или создайте числовые форматы, определяемые пользователем. |
| Даты и время | Используйте предопределенные именованные форматы даты и времени или создайте числовые форматы, определяемые пользователем. |
| Даты и время, представленные числами | Используйте именованные форматы даты и времени или создайте числовые форматы. |
| Строки | Создайте свои собственные определяемые пользователем строковые форматы. |
Функция Format усекает format до 257 символов.
Если вы попытаетесь форматировать числа без задания атрибута format, функция Format предоставит функциональность, аналогичную функции Str, хотя и с поддержкой международных форматов. Однако положительные числа, отформатированные как строки с использованием функции Format, не будут включать начальный пробел, зарезервированный для знака или значения; в случае же преобразования с помощью функции Str начальный пробел сохранится.
При форматировании нелокализованных числовых строк, следует использовать определяемый пользователем числовой формат, чтобы обеспечить получение желаемых результатов.
Если используется григорианский календарь, значение символов выражения format не изменяется. Если используется календарь Хиджра, все символы формата даты (например, dddd, mmmm, yyyy) имеют то же значение, но применяются к календарю Хиджра. Символы формата остаются английскими; символы, отображаемые в текстовом виде (например, AM и PM), отображают строку (на английском или арабском языке), связанную с этим символом. Диапазон некоторых символов при использовании календаря Хиджра изменяется.
Символы даты
| Символ | Диапазон |
|---|---|
| d | 1–31 (день месяца без нуля в начале) |
| dd | 01–31 (день месяца с нулем в начале) |
| w | 1–7 (день недели, начиная с воскресенья = 1) |
| ww | 1–53 (неделя года без нуля в начале; неделя 1 начинается с 1 января) |
| m | 1–12 (месяц года без нуля в начале, начиная с января = 1) |
| mm | 01–12 (месяц года с нулем в начале, начиная с января = 01) |
| mmm | Отображает сокращенное название месяца (в названии месяцев Хиджра нет сокращений) |
| mmmm | Отображает полное название месяца |
| y | 1–366 (день года) |
| yy | 00–99 (последние две цифры года) |
| yyyy | 100–9999 (год из трех или четырех цифр) |
Символы времени
| Символ | Диапазон |
|---|---|
| h | 0–23 (1–12 с добавлением «AM» или «PM») (час суток без нуля в начале) |
| hh | 00–23 (01–12 с добавлением «AM» или «PM») (час суток с нулем в начале) |
| n | 0–59 (минута часа без нуля в начале) |
| nn | 00–59 (минута часа с нулем в начале) |
| m | 0–59 (минута часа без нуля в начале). Только если находится после h или hh |
| mm | 00–59 (минута часа с нулем в начале). Только если находится после h или hh |
| s | 0–59 (секунда минуты без нуля в начале) |
| ss | 00–59 (секунда минуты с нулем в начале) |
Пример
В этом примере показываются различные варианты использования функции Format для форматирования значений с использованием как именованных форматов, так и форматов, определяемых пользователем. Отображение разделителей даты (/), времени (:) и литерала AM/PM зависит от языковых настроек системы, в среде которой выполняется код. При отображении времени и дат в среде разработки используются краткие форматы даты и времени, определяемые настройками языка для кода. При их отображении в процессе выполнения кода используются краткие форматы даты и времени языка системы, которые могут отличаться от настроек языка для кода. В этом примере подразумевается использование языка «Английский (США)». Элементы MyTime и MyDate отображаются в среде разработки с использованием текущих кратких форматов даты и времени языка системы.
Различные форматы для различных числовых значений
В определяемом пользователем expression формата для чисел может иметься от одного до четырех разделов, отделяемых друг от друга точками с запятой. Если аргумент формата содержит один из именованных числовых форматов, допускается использование только одного раздела.
| Если используется | Результат |
|---|---|
| Только один раздел | Выражение формата применяется ко всем значениям. |
| Два раздела | Первый раздел применяется к положительным значениям и нулям, второй — к отрицательным значениям. |
| Три раздела | Первый раздел применяется к положительным значениям, второй — к отрицательным значениям, третий — к нулям. |
| Четыре раздела | Первый раздел применяется к положительным значениям, второй — к отрицательным значениям, третий — к нулям, четвертый — к значениям Null. |
Если записать знаки точки с запятой без кода между ними, то отсутствующий раздел будет печататься с использованием формата положительного значения. Например, следующий формат отображает положительные и отрицательные значения с использованием формата первого раздела и отображает «Zero», если значением является нуль.
Различные форматы для различных строковых значений
В expression формата для строк может иметься один раздел или два раздела, отделяемых друг от друга точками с запятой (;).
| Если используется | Результат |
|---|---|
| Только один раздел | Формат применяется ко всем строковым данным. |
| Два раздела | Первый раздел применяется к строковым данным, второй — к значениям Null и пустым строкам («»). |
Именованные форматы даты и времени
В следующей таблице указываются предопределенные имена форматов даты и времени.
| Имя формата | Описание |
|---|---|
| General Date | Отображение даты и/или времени, например 4/3/93 05:34 PM. Если дробная часть отсутствует, отображается только дата, например 4/3/93. Если отсутствует целая часть, отображается только время, например 05:34 PM. Отображение даты определяется параметрами системы. |
| Long Date | Отображение даты в соответствии с длинным форматом даты, используемым в системе. |
| Medium Date | Отображение даты с использованием среднего формата даты, соответствующего языковой версии ведущего приложения. |
| Short Date | Отображение даты в соответствии с кратким форматом даты, используемым в системе. |
| Long Time | Отображение времени в соответствии с длинным форматом даты, используемым в системе; включает часы, минуты и секунды. |
| Medium Time | Отображение времени в 12-часовом формате с использованием часов, минут и указателя AM/PM. |
| Short Time | Отображение времени в 24-часовом формате, например 17:45. |
Именованные числовые форматы
В следующей таблице указываются предопределенные имена числовых форматов.
| Имя формата | Описание |
|---|---|
| General Number | Отображение числа без знака разделителя групп разрядов. |
| Currency | Отображение числа с использованием разделителя групп разрядов, если это необходимо; отображаются две цифры справа от разделителя целой и дробной части. Вывод основывается на языковых настройках системы. |
| Fixed | Отображение по крайней мере одной цифры слева и двух цифр справа от разделителя целой и дробной части. |
| Standard | Отображение числа с использованием разделителя групп разрядов; отображаются по крайней мере одна цифра слева и две цифры справа от разделителя целой и дробной части. |
| Percent | Отображение числа, умноженного на 100 со знаком процента (%), добавляемого справа; всегда отображаются две цифры справа от разделителя целой и дробной части. |
| Scientific | Используется стандартное экспоненциальное представление. |
| Yes/No | Отображается «Нет», если число равняется 0; в противном случае отображается «Да». |
| True/False | Отображается False, если число равняется 0; в противном случае отображается True. |
| On/Off | Отображается «Вкл», если число равняется 0; в противном случае отображается «Выкл». |
Определяемые пользователем строковые форматы
Вы можете использовать любой из следующих знаков для создания expression формата для строк.
| Знак | Описание |
|---|---|
| @ | Заполнитель для символов. Отображает знак или пробел. Если в строке имеется знак на позиции, в которой в строке форматирования располагается at-символ (@), отображается этот знак; в противном случае на этой позиции отображается пробел. Заполнители заполняются справа налево, если только в строке форматирования не будет представлен восклицательный знак (!). |
| & | Заполнитель для символов. Отображает знак или ничего не отображает. Если в строке имеется знак на позиции, в которой располагается амперсанд (&), отображается этот знак; в противном случае не отображается ничего. Заполнители заполняются справа налево, если только в строке форматирования не будет представлен восклицательный знак (!). |
| Принудительное отображение верхнего регистра. Отображение всех знаков в формате верхнего регистра. | |
| ! | Принудительное заполнение заполнителей в порядке слева направо. По умолчанию заполнители заполняются справа налево. |
Определяемые пользователем форматы даты и времени
В следующей таблице указываются знаки, которые могут использоваться при создании определяемых пользователем форматов даты и времени.
Определяемые пользователем числовые форматы
В следующей таблице указываются знаки, которые могут использоваться при создании определяемых пользователем форматов чисел.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.