Форматирование чисел в Excel VBA
Числа в таблицах Excel представлены во всех форматах. Возможно, вы уже знакомы со всплывающим окном в Excel для использования различных числовых форматов:
Форматирование чисел упрощает чтение и понимание чисел. По умолчанию в Excel для чисел, вводимых в ячейки, используется «Общий» формат, что означает, что число отображается точно так, как вы его ввели.
Например, если вы введете круглое число, например 4238, он будет отображаться как 4238 без десятичной точки и разделителей тысяч. Десятичное число, такое как 9325,89, будет отображаться с десятичной точкой и десятичными знаками. Это означает, что он не выровняется в столбце с круглыми числами и будет выглядеть очень неаккуратно.
Кроме того, не показывая разделителей тысяч, трудно увидеть, насколько большое число на самом деле, без подсчета отдельных цифр. Это миллионы или десятки миллионов?
С точки зрения пользователя, просматривающего столбец чисел, это затрудняет чтение и сравнение.
В VBA у вас есть доступ к тому же диапазону форматов, что и в интерфейсе Excel. Это касается не только введенного значения в ячейку на листе, но и таких вещей, как окна сообщений, элементы управления UserForm, диаграммы и графики, а также строку состояния Excel в нижнем левом углу листа.
Функция форматирования - чрезвычайно полезная функция в VBA с точки зрения представления, но она также очень сложна с точки зрения гибкости, предлагаемой в способе отображения чисел.
Как использовать функцию форматирования в VBA
Если вы показываете окно сообщения, то функцию форматирования можно использовать напрямую:
| 1 | Формат MsgBox (1234567,89, "#, ## 0,00") |
Это отобразит большое число с использованием запятых для разделения тысяч и отображения 2 десятичных знаков. Результат будет 1 234 567,89. Нули вместо хэша гарантируют, что десятичные дроби будут отображаться как 00 в целых числах, и что есть начальный ноль для числа, которое меньше 1
Символ хэштега (#) представляет собой заполнитель цифры, который отображает цифру, если она доступна в этой позиции, или ничего.
Вы также можете использовать функцию формата для адресации отдельной ячейки или диапазона ячеек, чтобы изменить формат:
| 1 | Листы ("Лист1"). Диапазон ("A1: A10"). NumberFormat = "#, ## 0.00" |
Этот код установит диапазон ячеек (от A1 до A10) в настраиваемый формат, который разделяет тысячи запятыми и показывает 2 десятичных знака.
Если вы проверите формат ячеек в интерфейсе Excel, вы обнаружите, что был создан новый настраиваемый формат.
Вы также можете форматировать числа в строке состояния Excel в нижнем левом углу окна Excel:
| 1 | Application.StatusBar = Format (1234567,89, "#, ## 0,00") |
Вы очищаете это в строке состояния, используя:
| 1 | Application.StatusBar = "" |
Создание строки формата
В этом примере после каждого числа будет добавлен текст «Общий объем продаж», а также будет включен разделитель тысяч.
| 1 | Таблицы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "#, ## 0.00" "Общий объем продаж" "" |
Вот как будут выглядеть ваши числа:
Обратите внимание, что в ячейке A6 есть формула «СУММ», и она будет включать текст «Общий объем продаж» без необходимости форматирования. Если применяется форматирование, как в приведенном выше коде, он не помещает дополнительный экземпляр «Всего продаж» в ячейку A6.
Хотя в ячейках теперь отображаются буквенно-цифровые символы, числа по-прежнему представлены в числовой форме. Формула «СУММ» по-прежнему работает, потому что в ней используется числовое значение в фоновом режиме, а не способ форматирования числа.
Запятая в строке формата обеспечивает разделитель тысяч. Обратите внимание, что вам нужно ввести это в строку только один раз. Если число достигает миллионов или миллиардов, оно все равно разделит цифры на группы по 3
Ноль в строке формата (0) - это цифра-заполнитель. Он отображает цифру, если она есть, или ноль. Его расположение очень важно для обеспечения единообразия с форматированием.
В строке формата символы решетки (#) ничего не отображают, если нет цифры. Однако, если есть число вроде 0,8 (все десятичные дроби), мы хотим, чтобы оно отображалось как 0,80, чтобы оно совпадало с другими числами.
Использование одного нуля слева от десятичной точки и двух нулей справа от десятичной точки в строке формата дает требуемый результат (0,80).
Если бы справа от десятичной точки был только один ноль, то результатом было бы «0,8», и все отображалось бы с точностью до одного десятичного знака.
Использование строки формата для выравнивания
Мы можем захотеть, чтобы все десятичные числа в диапазоне были выровнены по их десятичным точкам, чтобы все десятичные знаки находились непосредственно друг под другом, независимо от количества десятичных знаков в каждом числе.
Для этого вы можете использовать вопросительный знак (?) В строке формата. Знак "?" Указывает на то, что отображается номер, если он доступен, или пробел.
| 1 | Листы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Это отобразит ваши числа следующим образом:
Все десятичные точки теперь выстраиваются друг под другом. В ячейке A5 есть три десятичных разряда, и это нормально выровняло бы выравнивание, но использование символа «?» Выравнивает все идеально.
Использование буквенных символов в строке формата
Вы можете добавить любой буквальный символ в строку формата, поставив перед ним обратную косую черту (\).
Предположим, вы хотите отобразить конкретный индикатор валюты для ваших чисел, который не зависит от вашего региона. Проблема в том, что если вы используете индикатор валюты, Excel автоматически ссылается на ваш локальный язык и меняет его на тот, который соответствует языку, установленному на панели управления Windows. Это может иметь последствия, если ваше приложение Excel распространяется в других странах, и вы хотите убедиться, что независимо от языкового стандарта индикатор валюты всегда один и тот же.
Вы также можете указать, что числа указаны в миллионах в следующем примере:
| 1 | Листы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ m" |
Это даст следующие результаты на вашем листе:
При использовании обратной косой черты для отображения буквальных символов нет необходимости использовать обратную косую черту для каждого отдельного символа в строке. Вы можете использовать:
| 1 | Листы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ mill" |
После каждого числа в пределах отформатированного диапазона будет отображаться «мельница».
В качестве литералов можно использовать большинство символов, но не зарезервированные символы, такие как 0, # ,?
Использование запятых в строке формата
Мы уже видели, что запятые можно использовать для создания разделителей тысяч для больших чисел, но их можно использовать и по-другому.
Используя их в конце числовой части строки формата, они действуют как масштабаторы тысяч. Другими словами, они будут делить каждое число на 1000 каждый раз, когда появляется запятая.
В данных примера мы показываем это с индикатором в миллионах. Вставив одну запятую в строку формата, мы можем показать эти числа, разделенные на 1000.
| 1 | Листы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "\ $ #, ## 0.00, \ m" |
Это покажет числа, разделенные на 1000, хотя исходное число все еще будет в ячейке на заднем плане.
Если в строке формата поставить две запятые, то числа будут разделены на миллион
| 1 | Листы ("Лист1"). Диапазон ("A1: A6"). NumberFormat = "\ $ #, ## 0,00 ,, \ m" |
Это будет результат с использованием только одной запятой (разделите на 1000):
Создание условного форматирования в строке формата
Вы можете настроить условное форматирование во внешнем интерфейсе Excel, но вы также можете сделать это в своем коде VBA, что означает, что вы можете программно управлять строкой формата для внесения изменений.
Вы можете использовать до четырех разделов в строке формата. Каждый раздел отделяется точкой с запятой (;). Четыре раздела соответствуют положительным, отрицательным, нулевым и текстовым
| 1 | Диапазон ("A1: A7"). NumberFormat = "#, ## 0,00; [Красный] - #, ## 0,00; [Зеленый] #, ## 0,00; [Синий]" |
В этом примере мы используем один и тот же хэш, запятую и ноль для разделения тысяч и двух десятичных знаков, но теперь у нас есть разные разделы для каждого типа значения.
Первый раздел предназначен для положительных чисел и не отличается по формату от того, что мы уже видели ранее.
Во втором разделе для отрицательных чисел вводится цвет (красный), заключенный в пару квадратных скобок. Формат такой же, как и для положительных чисел, за исключением того, что перед ним был добавлен знак минус (-).
В третьем разделе для нулевых чисел используется цвет (зеленый) в квадратных скобках с числовой строкой, такой же, как для положительных чисел.
Последний раздел предназначен для текстовых значений, и все, что для этого нужно, это снова цвет (синий) в квадратных скобках.
Это результат применения этой строки формата:
Вы можете пойти дальше с условиями в строке формата. Предположим, вы хотите, чтобы каждое положительное число выше 10 000 отображалось зеленым, а все остальные числа - красным, вы можете использовать эту строку формата:
| 1 | Диапазон ("A1: A7"). NumberFormat = "[> = 10000] [Зеленый] #, ## 0,00; [<10000] [Красный] #, ## 0,00" |
Эта строка формата включает условия для> = 10000, указанные в квадратных скобках, поэтому зеленый цвет будет использоваться только там, где число больше или равно 10000.
Вот результат:
Использование дробей в строках форматирования
Дроби не часто используются в электронных таблицах, поскольку они обычно приравниваются к десятичным дробям, с которыми все знакомы.
Однако иногда они служат определенной цели. В этом примере будут отображаться доллары и центы:
| 1 | Диапазон ("A1: A7"). NumberFormat = "#, ## 0" "долларов и" "00/100" "центов" "" |
Вот результат, который будет получен:
Помните, что, несмотря на то, что числа отображаются в виде текста, они по-прежнему присутствуют в фоновом режиме в виде чисел, и все формулы Excel по-прежнему можно использовать с ними.
Форматы даты и времени
Даты на самом деле являются числами, и вы можете использовать их форматы так же, как и числа. Если вы отформатируете дату как числовое число, вы увидите большое число слева от десятичной точки и количество десятичных знаков. Число слева от десятичной точки показывает количество дней, начиная с 01 января 1900 года, а десятичные разряды показывают время, основанное на 24 часах.
| 1 | Формат MsgBox (Сейчас (), «дд-ммм-гггг») |
Это отформатирует текущую дату так, чтобы показать '08 -Июль-2020 '. При использовании «ммм» для месяца отображаются первые три символа названия месяца. Если вам нужно полное название месяца, используйте «мммм».
Вы можете включить время в строку формата:
| 1 | Формат MsgBox (Сейчас (), «дд-ммм-гггг чч: мм AM / PM») |
Будет отображаться '08 -Июль-2020 13:25 '.
«Чч: мм» представляет часы и минуты, а AM / PM использует 12-часовой формат времени, а не 24-часовой формат.
Вы можете включить текстовые символы в строку формата:
| 1 | Формат MsgBox (Сейчас (), "дд-ммм-гггг чч: мм AM / PM" "сегодня" "") |
Будет отображаться "08-июл-2020 13:25 сегодня".
Вы также можете использовать буквальные символы, используя обратную косую черту впереди, так же, как для строк числового формата.
Предопределенные форматы
В Excel есть несколько встроенных форматов чисел и дат, которые вы можете использовать в своем коде. В основном они отражают то, что доступно во внешнем интерфейсе форматирования чисел, хотя некоторые из них выходят за рамки того, что обычно доступно во всплывающем окне. Кроме того, у вас нет гибкости в отношении количества десятичных знаков или использования разделителей тысяч.
Общий номер
В этом формате число будет отображаться точно так, как оно есть.
| 1 | Формат MsgBox (1234567,89, «Общий номер») |
Результат будет 1234567,89
Валюта
| 1 | Формат MsgBox (1234567,894, «Валюта») |
В этом формате перед числом будет добавлен символ валюты, например $, £ в зависимости от вашего языкового стандарта, но он также отформатирует число до двух десятичных знаков и разделит тысячи запятыми.
Результат будет 1234567,89 $.
Фиксированный
| 1 | Формат MsgBox (1234567.894, «Фиксированный») |
Этот формат отображает по крайней мере одну цифру слева, но только две цифры справа от десятичной точки.
Результат будет 1234567,89
Стандарт
| 1 | Формат MsgBox (1234567.894, «Стандартный») |
Это отображает число с разделителями тысяч, но только с двумя десятичными знаками.
Результат будет 1234567,89
Процентов
| 1 | Формат MsgBox (1234567,894, «Процент») |
Число умножается на 100, и в конце числа добавляется символ процента (%). Формат отображается с точностью до 2 знаков после запятой.
Результат будет 123456789,40%
Научный
| 1 | Формат MsgBox (1234567.894, «Научный») |
Это преобразует число в экспоненциальный формат.
Результат будет 1.23E + 06
Да нет
| 1 | Формат MsgBox (1234567.894, «Да / Нет») |
Отображается «Нет», если число равно нулю, в противном случае отображается «Да».
Результатом будет «Да».
Верно / неверно
| 1 | Формат MsgBox (1234567,894, «Истина / Ложь») |
Отображается «False», если число равно нулю, в противном случае отображается «True».
Результатом будет «True».
Вкл выкл
| 1 | Формат MsgBox (1234567.894, «Вкл. / Выкл.») |
Отображается «Выкл.», Если число равно нулю, в противном случае отображается «Вкл.».
Результат будет «Вкл».
Общая дата
| 1 | Формат MsgBox (Сейчас (), «Общая дата») |
Дата будет отображаться как дата и время в формате AM / PM. Отображение даты зависит от ваших настроек в Панели управления Windows (Часы и регион | Регион). Может отображаться как «мм / дд / гггг» или «дд / мм / гггг».
Результатом будет «07.07.2020 15:48:25».
Длинное свидание
| 1 | Формат MsgBox (Сейчас (), «Длинная дата») |
Это будет отображать длинную дату, как определено на панели управления Windows (Часы и регион | Регион). Обратите внимание, что это не включает время.
Результатом будет «Вторник, 7 июля 2022 г.».
Средняя дата
| 1 | Формат MsgBox (Сейчас (), «Средняя дата») |
При этом отображается дата, определенная в параметрах краткой даты, как определено языковым стандартом в Панели управления Windows.
Результат будет '07 -Июль-20 '.
Короткое свидание
| 1 | Формат MsgBox (Сейчас (), «Краткая дата») |
Отображает короткую дату, как определено на панели управления Windows (Часы и регион | Регион). Отображение даты зависит от вашего региона. Может отображаться как «мм / дд / гггг» или «дд / мм / гггг».
Результатом будет «07.07.2020».
Много времени
| 1 | Формат MsgBox (Сейчас (), «Долгое время») |
Отображает долгое время, как определено в Панели управления Windows (Часы и регион | Регион).
Результатом будет «16:11:39 PM».
Среднее время
| 1 | Формат MsgBox (Сейчас (), «Среднее время») |
Отображает среднее время, определенное вашим языком в Панели управления Windows. Обычно это 12-часовой формат с использованием часов, минут и секунд, а также формат AM / PM.
Результат будет "04:15".
Короткое время
| 1 | Формат MsgBox (Сейчас (), «Кратковременное») |
Отображает среднее время, определенное в Панели управления Windows (Часы и регион | Регион). Обычно это 24-часовой формат с часами и минутами.
Результат будет '16: 18 '.
Опасности использования предварительно определенных форматов Excel в датах и времени
Использование предопределенных форматов даты и времени в Excel VBA очень зависит от настроек в Панели управления Windows, а также от того, какой языковой стандарт установлен на
Пользователи могут легко изменить эти параметры, и это повлияет на отображение даты и времени в Excel.
Например, если вы разрабатываете приложение Excel, которое использует предопределенные форматы в вашем коде VBA, они могут полностью измениться, если пользователь находится в другой стране или использует другой языковой стандарт. Вы можете обнаружить, что ширина столбцов не соответствует определению даты или что в пользовательской форме элемент управления Active X, такой как элемент управления в виде поля со списком (раскрывающийся список), слишком узок для правильного отображения даты и времени.
При разработке приложения Excel необходимо учитывать географическое положение аудитории.
Определяемые пользователем форматы чисел
Существует ряд различных параметров, которые вы можете использовать при определении строки формата:
| Характер | Описание |
| Нулевая строка | Без форматирования |
| 0 | Заполнитель цифры. Отображает цифру или ноль. Если для этой позиции есть цифра, она отображает цифру, в противном случае отображается 0. Если цифр меньше, чем нулей, вы получите начальные или конечные нули. Если после десятичной точки больше цифр, чем нулей, то число округляется до количества десятичных разрядов, отображаемых нулями. Если перед десятичной точкой больше цифр, чем нулей, они будут отображаться нормально. |
| # | Заполнитель цифры. Это отображает цифру или ничего. Он работает так же, как и заполнитель нуля выше, за исключением того, что начальные и конечные нули не отображаются. Например, 0,75 будет отображаться с нулевыми заполнителями, но это будет 0,75 с использованием # заполнителей. |
| . Десятичная точка. | Только один разрешен на строку формата. Этот символ зависит от настроек в Панели управления Windows. |
| % | Заполнитель процента. Умножает число на 100 и помещает символ% там, где он появляется в строке формата. |
| , (запятая) | Разделитель тысяч. Это используется, если используются заполнители 0 или #, а строка формата содержит запятую. Одна запятая слева от десятичной точки означает округление до ближайшей тысячи. Например. ## 0, Две соседние запятые слева от разделителя тысяч означают округление до ближайшего миллиона. Например. ## 0 ,, |
| E- E + | Научный формат. Это отображает число экспоненциально. |
| : (двоеточие) | Разделитель времени - используется при форматировании времени для разделения часов, минут и секунд. |
| / | Разделитель даты - используется при указании формата даты. |
| - + £ $ ( ) | Отображает буквальный символ.Чтобы отобразить символ, отличный от указанного здесь, поставьте перед ним обратную косую черту (\) |
Пользовательские форматы даты и времени
Все эти символы могут использоваться в строке формата при форматировании даты и времени:
| Характер | Имея в виду |
| c | Отображает дату как ддддд и время как тттт |
| d | Отображение дня в виде числа без нуля в начале |
| дд | Отображение дня в виде числа с нулем в начале |
| ддд | Отображение дня в виде аббревиатуры (вс - сб) |
| дддд | Отображение полного названия дня (воскресенье - суббота) |
| ддддд | Отображение серийного номера даты как полной даты в соответствии с краткой датой в международных настройках панели управления Windows |
| дддддд | Отображает серийный номер даты как полную дату в соответствии с длинной датой в международных настройках панели управления Windows. |
| ш | Отображает день недели в виде числа (1 = воскресенье) |
| ww | Отображает неделю года в виде числа (1-53) |
| м | Отображает месяц в виде числа без нуля в начале |
| мм | Отображает месяц в виде числа с ведущими нулями |
| М-м-м | Отображает месяц в виде сокращения (январь-декабрь) |
| мммм | Отображает полное название месяца (январь - декабрь) |
| q | Отображает квартал года в виде числа (1-4) |
| у | Отображает день года в виде числа (1-366) |
| гг | Отображает год в виде двузначного числа |
| гггг | Отображает год в виде четырехзначного числа |
| час | Отображает час в виде числа без нуля в начале |
| чч | Отображает час в виде числа с нулем в начале |
| п | Отображает минуты в виде числа без нуля в начале |
| nn | Отображает минуты в виде числа с нулем в начале |
| s | Отображает секунду как число без нуля в начале |
| SS | Отображает секунду в виде числа с нулем в начале |
| тттт | Отображение серийного номера времени как полного времени. |
| ДО ПОЛУДНЯ ПОСЛЕ ПОЛУДНЯ | Используйте 12-часовые часы и отображайте AM или PM, чтобы указать до или после полудня. |
| до полудня после полудня | Используйте 12-часовые часы и используйте am или pm, чтобы указать до или после полудня. |
| A / P | Используйте 12-часовые часы и используйте A или P, чтобы указать до или после полудня. |
| а / п | Используйте 12-часовые часы и используйте a или p, чтобы указать до или после полудня. |
