Форматиране на числа в Excel VBA
Номерата идват във всички видове формати в работните листове на Excel. Може би вече сте запознати с изскачащия прозорец в Excel за използване на различни числови формати:
Форматирането на числа прави числата по -лесни за четене и разбиране. Excel по подразбиране за числата, въведени в клетките, е „Общ“ формат, което означава, че номерът се показва точно както сте го въвели.
Например, ако въведете кръгло число напр. 4238, той ще бъде показан като 4238 без десетична запетая или разделители на хиляди. Десетично число като 9325.89 ще бъде показано с десетичната запетая и десетичните знаци. Това означава, че той няма да се подреди в колоната с кръглите числа и ще изглежда изключително разхвърлян.
Също така, без да се показват разделителите на хиляди, е трудно да се види колко голямо е всъщност едно число, без да се броят отделните цифри. В милиони ли е или в десетки милиони?
От гледна точка на потребител, гледащ надолу колона с числа, това прави доста трудно четенето и сравняването.
Във VBA имате достъп до точно същия диапазон от формати, който имате в предния край на Excel. Това се отнася не само за въведена стойност в клетка на работен лист, но и за неща като полета за съобщения, контроли на UserForm, диаграми и графики и лентата за състоянието на Excel в долния ляв ъгъл на работния лист.
Функцията Format е изключително полезна функция във VBA от гледна точка на представянето, но също така е много сложна по отношение на гъвкавостта, предлагана при начина на изобразяване на числата.
Как да използвате функцията за форматиране във VBA
Ако показвате поле за съобщение, тогава функцията Форматиране може да се използва директно:
1 | MsgBox формат (1234567.89, "#, ## 0.00") |
Това ще покаже голям брой, използвайки запетаи за разделяне на хилядите и за показване на 2 десетични знака. Резултатът ще бъде 1 234 567,89. Нулите вместо хеша гарантират, че десетичните знаци ще бъдат показани като 00 в цели числа и че има начална нула за число, което е по -малко от 1
Символът на хаштаг (#) представлява заместител на цифра, който показва цифра, ако е налична в тази позиция, или нищо.
Можете също да използвате функцията за форматиране за адресиране на отделна клетка или диапазон от клетки, за да промените формата:
1 | Листове ("Sheet1"). Диапазон ("A1: A10"). NumberFormat = "#, ## 0.00" |
Този код ще зададе диапазона от клетки (A1 до A10) на персонализиран формат, който разделя хилядите със запетаи и показва 2 десетични знака.
Ако проверите формата на клетките в предния край на Excel, ще откриете, че е създаден нов персонализиран формат.
Можете също да форматирате числа в лентата за състоянието на Excel в долния ляв ъгъл на прозореца на Excel:
1 | Application.StatusBar = Формат (1234567.89, "#, ## 0.00") |
Изчиствате това от лентата на състоянието, като използвате:
1 | Application.StatusBar = "" |
Създаване на формат низ
Този пример ще добави текста „Общи продажби“ след всяко число, както и ще включва разделител на хиляди
1 | Листове ("Sheet1"). Диапазон ("A1: A6"). NumberFormat = "#, ## 0.00" "Общо продажби" "" |
Ето как ще изглеждат вашите номера:
Обърнете внимание, че клетка A6 има формула „SUM“ и тя ще включва текста „Общи продажби“, без да се изисква форматиране. Ако форматирането е приложено, както в горния код, то няма да постави допълнителен екземпляр от „Общи продажби“ в клетка A6
Въпреки че сега клетките показват буквено -цифрови знаци, числата все още присъстват в цифрова форма. Формулата „SUM“ все още работи, защото използва цифровата стойност във фонов режим, а не начина на форматиране на номера.
Запетаята във форматирания низ предоставя разделител на хиляди. Обърнете внимание, че трябва само да поставите това в низа веднъж. Ако числото достига милиони или милиарди, то все пак ще раздели цифрите на групи от 3
Нулата във форматирания низ (0) е цифров заместител. Той показва цифра, ако е там, или нула. Позиционирането му е много важно, за да се осигури еднаквост с форматирането
В низа за форматиране хеш символите (#) няма да показват нищо, ако няма цифра. Ако обаче има число като .8 (всички десетични знаци), искаме той да се показва като 0.80, така че да се подравнява с другите числа.
Използвайки една нула вляво от десетичната запетая и две нули вдясно от десетичната запетая във форматиращия низ, това ще даде необходимия резултат (0,80).
Ако имаше само една нула вдясно от десетичната запетая, тогава резултатът щеше да бъде „0.8“ и всичко щеше да се покаже на един десетичен знак.
Използване на низ за форматиране за подравняване
Може да искаме да видим всички десетични числа в диапазон, подравнен по техните десетични точки, така че всички десетични точки да са директно една под друга, колкото и да има много десетични знаци на всяко число.
Можете да използвате въпросителен знак (?) Във вашия форматиран низ, за да направите това. „?“ Показва, че се показва число, ако е налично, или интервал
1 | Листове ("Sheet1"). Диапазон ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Това ще покаже вашите номера, както следва:
Всички десетични точки сега се подреждат една под друга. Клетка A5 има три знака след десетичната запетая и това би изхвърлило подравняването нормално, но използването на знака „?“ Подравнява всичко перфектно.
Използване на буквални знаци в низа за формат
Можете да добавите буквален символ във вашия форматиран низ, като го предшествате с обратна наклонена черта (\).
Да предположим, че искате да покажете определен валутен индикатор за вашите номера, който не се основава на вашия локал. Проблемът е, че ако използвате индикатор за валута, Excel автоматично се позовава на вашия локален и го променя на този, подходящ за локала, който е зададен на контролния панел на Windows. Това може да има последици, ако приложението ви Excel се разпространява в други държави и искате да сте сигурни, че независимо от локала, индикаторът за валута винаги е един и същ.
Можете също така да посочите, че числата са милиони в следния пример:
1 | Листове ("Sheet1"). Диапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
Това ще доведе до следните резултати на вашия работен лист:
Когато използвате обратна наклонена черта за показване на буквални символи, не е необходимо да използвате обратна наклонена черта за всеки отделен знак в низ. Можеш да използваш:
1 | Листове ("Sheet1"). Диапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Това ще показва „мелница“ след всяко число в рамките на форматирания диапазон.
Можете да използвате повечето символи като литерали, но не и запазени знаци като 0, #,?
Използване на запетаи във форматиран низ
Вече видяхме, че запетаите могат да се използват за създаване на хиляди разделители за големи числа, но те могат да се използват и по друг начин.
Използвайки ги в края на числовата част на низа за формат, те действат като скалари на хиляди. С други думи, те ще разделят всяко число на 1000 всеки път, когато има запетая.
В примерните данни го показваме с индикатор, че е в милиони. Като вмъкнем една запетая във форматиращия низ, можем да покажем тези числа, разделени на 1000.
1 | Листове ("Sheet1"). Диапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Това ще покаже числата, разделени на 1000, въпреки че първоначалният номер ще остане на заден план в клетката.
Ако поставите две запетаи във форматиращия низ, числата ще бъдат разделени на милион
1 | Листове ("Sheet1"). Диапазон ("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 (сега (), "dd-mmm-yyyy") |
Това ще форматира текущата дата, за да покаже „08-юли-2020“. Използването на „mmm“ за месеца показва първите три знака от името на месеца. Ако искате пълното име на месеца, използвайте „mmmm“
Можете да включите времена във вашия форматиран низ:
1 | MsgBox формат (сега (), "dd-mmm-yyyy hh: mm AM/PM") |
Това ще показва „08-юли-2020 13:25“
„Hh: mm“ представлява часове и минути, а AM/PM използва 12-часов часовник, за разлика от 24-часов часовник.
Можете да включите текстови знаци във форматиращия низ:
1 | Формат на MsgBox (сега (), "dd-mmm-yyyy hh: mm AM/PM" "днес" "") |
Това ще показва „08-юли-2020 13:25 днес“
Можете също да използвате буквални знаци, като използвате обратна наклонена черта отпред по същия начин, както за низове с формат на числово.
Предварително определени формати
Excel има редица вградени формати както за числа, така и за дати, които можете да използвате във вашия код. Те отразяват главно това, което е налично в предния край за форматиране на номера, въпреки че някои от тях надхвърлят това, което обикновено е налично в изскачащия прозорец. Освен това нямате гъвкавост по отношение на броя на десетичните знаци или дали се използват хиляди разделители.
Общ номер
Този формат ще показва номера точно такъв, какъвто е
1 | MsgBox формат (1234567.89, "Общ номер") |
Резултатът ще бъде 1234567.89
Валута
1 | MsgBox формат (1234567.894, "Валута") |
Този формат ще добави символ на валута пред числото, напр. $, £ в зависимост от вашия локал, но също така ще форматира числото на 2 десетични знака и ще раздели хилядите със запетаи.
Резултатът ще бъде 1 234 567,89 долара
Фиксирано
1 | MsgBox формат (1234567.894, "Фиксиран") |
Този формат показва най -малко една цифра вляво, но само две цифри вдясно от десетичната запетая.
Резултатът ще бъде 1234567.89
Стандартен
1 | MsgBox формат (1234567.894, "Стандарт") |
Това показва числото с хилядите разделители, но само до два знака след десетичната запетая.
Резултатът ще бъде 1 234 567,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“
Резултатът ще бъде „Вярно“
Вкл./Изкл
1 | MsgBox формат (1234567.894, "Вкл./Изкл.") |
Това показва „Изключено“, ако числото е нула, в противен случай показва „Включено“
Резултатът ще бъде „Включен“
Обща дата
1 | Формат на MsgBox (сега (), "Обща дата") |
Това ще покаже датата като дата и час, използвайки нотация AM/PM. Как се показва датата зависи от вашите настройки в контролния панел на Windows (Часовник и регион | Регион). Може да се показва като „mm/dd/yyyy“ или „dd/mm/yyyy“
Резултатът ще бъде „7/7/2020 3:48:25 PM“
Дълга среща
1 | Формат на MsgBox (сега (), "Дълга дата") |
Това ще покаже дълга дата, както е определено в контролния панел на Windows (Часовник и регион | Регион). Обърнете внимание, че не включва часа.
Резултатът ще бъде „вторник, 7 юли 2022 г.“
Средна дата
1 | Формат на MsgBox (сега (), "средна дата") |
Това показва дата, определена в настройките за кратка дата, както е определено от локала в контролния панел на Windows.
Резултатът ще бъде '07-юли-20 '
Кратка среща
1 | Формат на MsgBox (сега (), "кратка дата") |
Показва кратка дата, както е определено в контролния панел на Windows (Часовник и регион | Регион). Как се показва датата зависи от вашия регион. Може да се показва като „mm/dd/yyyy“ или „dd/mm/yyyy“
Резултатът ще бъде „7/7/2020“
Дълго време
1 | MsgBox формат (сега (), "дълго време") |
Показва дълго време, както е определено в контролния панел на Windows (Часовник и регион | Регион).
Резултатът ще бъде „4:11:39 PM“
Средно време
1 | Формат на MsgBox (сега (), "Средно време") |
Показва средно време, както е определено от вашия локал в контролния панел на Windows. Това обикновено се задава като 12-часов формат, като се използват часове, минути и секунди и AM/PM формат.
Резултатът ще бъде '04: 15 PM '
Кратко време
1 | Формат на MsgBox (сега (), "кратко време") |
Показва средно време, както е определено в контролния панел на Windows (Часовник и регион | Регион). Това обикновено се задава като 24-часов формат с часове и минути
Резултатът ще бъде '16: 18 '
Опасности от използването на предварително зададените формати на Excel в дати и часове
Използването на предварително дефинираните формати за дати и часове в Excel VBA зависи много от настройките в контролния панел на Windows, както и от това на какво е зададен локалът
Потребителите могат лесно да променят тези настройки и това ще има ефект върху начина, по който вашите дати и часове се показват в Excel
Например, ако разработите приложение на Excel, което използва предварително дефинирани формати във вашия код на VBA, те могат да се променят напълно, ако потребител е в друга държава или използва различен локал за вас. Може да откриете, че ширините на колоните не отговарят на дефиницията на датата или в потребителска форма контролата Active X, като например комбинация (падащо меню), е твърде тясна, за да могат да се показват правилно датите и часовете.
Трябва да помислите къде е географски публиката, когато разработвате приложението си Excel
Потребителски формати за числа
Има редица различни параметри, които можете да използвате, когато дефинирате низа за форматиране:
Персонаж | Описание |
Нулев низ | Без форматиране |
0 | Заместител на цифри. Показва цифра или нула. Ако има цифра за тази позиция, тя показва цифрата, в противен случай показва 0. Ако има по -малко цифри от нули, тогава ще получите водещи или последни нули. Ако след десетичната запетая има повече цифри, отколкото нули, тогава числото се закръглява до броя на десетичните знаци, показани от нулите. Ако преди десетичната запетая има повече цифри от нулите, те ще се показват нормално. |
# | Заместител на цифри. Това показва цифра или нищо. Той работи по същия начин като нулевия заместител по -горе, с изключение на това, че началната и последната нула не се показват. Например 0.75 ще се показва с нулеви заместители, но това ще бъде .75, използвайки # заместители. |
. Десетична запетая. | Само един разрешен за низ от формат. Този знак зависи от настройките в контролния панел на Windows. |
% | Процент заместител. Умножава числото по 100 и поставя символа %, където се появява във форматиращия низ |
, (запетая) | Хиляда разделител. Това се използва, ако се използват 0 или # заместители и низът за форматиране съдържа запетая. Една запетая вляво от десетичната запетая показва кръг до най -близката хиляда. Напр. ## 0, Две съседни запетаи вляво от разделителя на хилядите показват закръгляване до най -близкия милион. Напр. ## 0 ,, |
E- E+ | Научен формат. Това показва числото експоненциално. |
: (дебело черво) | Разделител на време - използва се при форматиране на час за разделяне на часове, минути и секунди. |
/ | Разделител на дата - това се използва, когато се посочва формат за дата |
- + £ $ ( ) | Показва буквален знак.За да покажете знак, различен от изброените тук, предходете го с обратна наклонена черта (\) |
Потребителски формати за дати и часове
Всички тези знаци могат да се използват във форматиращия низ при форматиране на дати и часове:
Персонаж | Смисъл |
° С | Показва датата като ddddd и часа като ttttt |
д | Показвайте деня като число без водеща нула |
дд | Показвайте деня като число с водеща нула |
ddd | Показване на деня като съкращение (нд - сб) |
dddd | Показвайте пълното име на деня (неделя - събота) |
ddddd | Показва сериен номер на дата като пълна дата според Short Date в международните настройки на контролния панел на windows |
dddddd | Показва сериен номер на дата като пълна дата според Long Date в международните настройки на контролния панел на Windows. |
w | Показва деня от седмицата като число (1 = неделя) |
ww | Показва седмицата на годината като число (1-53) |
м | Показва месеца като число без водеща нула |
mm | Показва месеца като число с водещи нули |
ммм | Показва месеца като съкращение (януари-декември) |
Мммм | Показва пълното име на месеца (януари - декември) |
q | Показва тримесечието на годината като число (1-4) |
y | Показва деня в годината като число (1-366) |
yy | Показва годината като двуцифрено число |
yyyy | Показва годината като четирицифрено число |
з | Показва часа като число без водеща нула |
чч | Показва часа като число с водеща нула |
н | Показва минутата като число без водеща нула |
nn | Показва минутата като число с водеща нула |
с | Показва второто като число без водеща нула |
ss | Показва втория като число с водеща нула |
ttttt | Показва сериен номер на час като пълно време. |
AM/PM | Използвайте 12-часов часовник и показвайте AM или PM, за да посочите преди или след обяд. |
сутрин/следобед | Използвайте 12-часов часовник и използвайте am или pm, за да посочите преди или след обяд |
A/P | Използвайте 12-часов часовник и използвайте A или P, за да посочите преди или след обяд |
a/p | Използвайте 12-часов часовник и използвайте a или p, за да посочите преди или след обяд |