Formatering af tal i Excel VBA
Tal findes i alle slags formater i Excel -regneark. Du kender muligvis allerede pop-up-vinduet i Excel til brug af forskellige numeriske formater:
Formatering af tal gør tallene lettere at læse og forstå. Excel -standarden for tal, der er indtastet i celler, er ‘Generelt’ format, hvilket betyder, at nummeret vises nøjagtigt, som du skrev det ind.
Hvis du f.eks. Indtaster et rundt tal f.eks. 4238, vil det blive vist som 4238 uden decimaltegn eller tusinder af skilletegn. Et decimaltal, f.eks. 9325.89, vises med decimaltegnet og decimalerne. Det betyder, at det ikke kommer i kø i kolonnen med de runde tal, og vil se ekstremt rodet ud.
Uden at vise tusinderne er det også svært at se, hvor stort et tal faktisk er uden at tælle de enkelte cifre. Er det i millioner eller titusinder af millioner?
Set fra en bruger, der kigger ned i en kolonne med tal, gør det det ret svært at læse og sammenligne.
I VBA har du adgang til nøjagtig det samme udvalg af formater, som du har på forsiden af Excel. Dette gælder ikke kun en indtastet værdi i en celle i et regneark, men også ting som meddelelsesbokse, UserForm -kontroller, diagrammer og grafer og Excel -statuslinjen i nederste venstre hjørne af regnearket.
Formateringsfunktionen er en ekstremt nyttig funktion i VBA i præsentationsbetingelser, men den er også meget kompleks med hensyn til fleksibiliteten i, hvordan tal vises.
Sådan bruges formateringsfunktionen i VBA
Hvis du viser en meddelelsesboks, kan funktionen Format bruges direkte:
| 1 | MsgBox -format (1234567.89, "#, ## 0.00") |
Dette vil vise et stort antal ved hjælp af kommaer til at adskille tusinderne og vise 2 decimaler. Resultatet bliver 1.234.567,89. Nuller i stedet for hash sikrer, at decimaler vises som 00 i hele tal, og at der er et førende nul for et tal, der er mindre end 1
Hashtag -symbolet (#) repræsenterer en ciffer -pladsholder, der viser et ciffer, hvis det er tilgængeligt i den position, eller ellers ingenting.
Du kan også bruge formatfunktionen til at adressere en individuel celle eller en række celler til at ændre formatet:
| 1 | Ark ("Ark1"). Område ("A1: A10"). NumberFormat = "#, ## 0.00" |
Denne kode indstiller celleområdet (A1 til A10) til et tilpasset format, der adskiller tusinderne med kommaer og viser 2 decimaler.
Hvis du tjekker formatet på cellerne på Excel -frontenden, vil du opdage, at et nyt brugerdefineret format er blevet oprettet.
Du kan også formatere tal på Excel -statuslinjen i nederste venstre hjørne af Excel -vinduet:
| 1 | Application.StatusBar = Format (1234567.89, "#, ## 0.00") |
Du sletter dette fra statuslinjen ved at bruge:
| 1 | Application.StatusBar = "" |
Oprettelse af en formatstreng
I dette eksempel tilføjes teksten 'Total salg' efter hvert nummer, samt en tusinder -separator
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00" "Samlet salg" "" |
Sådan ser dine tal ud:
Bemærk, at celle A6 har en 'SUM' formel, og dette inkluderer teksten 'Total salg' uden at kræve formatering. Hvis formateringen anvendes, som i ovenstående kode, vil den ikke sætte en ekstra forekomst af 'Total Sales' i celle A6
Selvom cellerne nu viser alfanumeriske tegn, er tallene stadig til stede i numerisk form. 'SUM' -formlen fungerer stadig, fordi den bruger den numeriske værdi i baggrunden, ikke hvordan tallet formateres.
Kommaet i formatstrengen giver adskillelsen mellem tusinder. Bemærk, at du kun behøver at sætte dette i strengen én gang. Hvis tallet løber op i millioner eller milliarder, vil det stadig opdele cifrene i grupper på 3
Nul i formatstrengen (0) er en cifferpladsholder. Det viser et ciffer, hvis det er der, eller et nul. Dens placering er meget vigtig for at sikre ensartethed med formateringen
I formatstrengen viser hashtegnene (#) intet, hvis der ikke er et ciffer. Men hvis der er et tal som .8 (alle decimaler), vil vi have det vist som 0.80, så det stemmer overens med de andre tal.
Ved at bruge et enkelt nul til venstre for decimalpunktet og to nuller til højre for decimalpunktet i formatstrengen, giver dette det krævede resultat (0,80).
Hvis der kun var et nul til højre for decimalpunktet, ville resultatet være '0,8', og alt ville blive vist til en decimal.
Brug af en formatstreng til justering
Vi vil måske se alle decimaltal i et område justeret efter deres decimaler, så alle decimalerne ligger direkte under hinanden, hvor mange decimaler der er på hvert tal.
Du kan bruge et spørgsmålstegn (?) I din formatstreng til at gøre dette. '?' Angiver, at et nummer vises, hvis det er tilgængeligt, eller et mellemrum
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Dette viser dine tal som følger:
Alle decimaler står nu på linje under hinanden. Celle A5 har tre decimaler, og dette ville smide justeringen normalt ud, men ved hjælp af "?" -Tegnet justeres alt perfekt.
Brug af bogstavelige tegn inden for formatstrengen
Du kan tilføje et hvilket som helst bogstav til din formatstreng ved at gå forud for det med en skråstreg (\).
Antag, at du vil vise en bestemt valutaindikator for dine tal, som ikke er baseret på din lokalitet. Problemet er, at hvis du bruger en valutaindikator, henviser Excel automatisk til din lokale og ændrer den til den, der er passende for den landestandard, der er angivet på Windows Kontrolpanel. Dette kan have konsekvenser, hvis din Excel -applikation distribueres i andre lande, og du vil sikre, at uanset landestandard er valutaindikatoren altid den samme.
Du vil muligvis også angive, at tallene er i millioner i følgende eksempel:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
Dette vil give følgende resultater på dit regneark:
Når du bruger en skråstreg til at vise bogstavelige tegn, behøver du ikke at bruge en skråstreg for hvert enkelt tegn i en streng. Du kan bruge:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Dette viser 'mill' efter hvert tal inden for det formaterede område.
Du kan bruge de fleste tegn som bogstaver, men ikke reserverede tegn som f.eks. 0, #,?
Brug af kommaer i en formatstreng
Vi har allerede set, at kommaer kan bruges til at oprette tusindvis af separatorer til store tal, men de kan også bruges på en anden måde.
Ved at bruge dem i slutningen af den numeriske del af formatstrengen fungerer de som skalere af tusinder. Med andre ord vil de dividere hvert tal med 1.000 hver gang der er et komma.
I eksempeldataene viser vi det med en indikator på, at det er i millioner. Ved at indsætte et komma i formatstrengen, kan vi vise disse tal divideret med 1.000.
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Dette viser tallene divideret med 1.000, selvom det originale nummer stadig vil være i baggrunden i cellen.
Hvis du sætter to kommaer i formatstrengen, bliver tallene divideret med en million
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
Dette bliver resultatet ved kun at bruge et komma (divider med 1.000):
Oprettelse af betinget formatering inden for formatstrengen
Du kan konfigurere betinget formatering på forsiden af Excel, men du kan også gøre det inden for din VBA -kode, hvilket betyder, at du kan manipulere formatstrengen programmatisk for at foretage ændringer.
Du kan bruge op til fire sektioner i din formatstreng. Hvert afsnit er afgrænset med et semikolon (;). De fire afsnit svarer til positiv, negativ, nul og tekst
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0.00; [Red]-#, ## 0.00; [Green]#, ## 0.00; [Blue]" |
I dette eksempel bruger vi den samme hash, komma og nul tegn til at give tusind separatorer og to decimaler, men vi har nu forskellige sektioner for hver værditype.
Det første afsnit er for positive tal og er ikke anderledes end det, vi tidligere har set med hensyn til format.
Det andet afsnit for negative tal introducerer en farve (rød), der holdes inden for et par firkantede parenteser. Formatet er det samme som for positive tal, bortset fra at der er tilføjet et minus (-) foran.
Den tredje sektion for nul tal bruger en farve (grøn) inden for parenteser med den numeriske streng den samme som for positive tal.
Det sidste afsnit er for tekstværdier, og alt hvad dette har brug for er en farve (blå) igen inden for firkantede parenteser
Dette er resultatet af at anvende denne formatstreng:
Du kan gå videre med betingelser inden for formatstrengen. Antag, at du ville vise hvert positivt tal over 10.000 som grønt, og hvert andet tal som rødt kunne du bruge denne formatstreng:
| 1 | Område ("A1: A7"). NumberFormat = "[> = 10000] [Grøn]#, ## 0.00; [<10000] [Rød]#, ## 0.00" |
Denne formatstreng indeholder betingelser for> = 10000 sat i firkantede parenteser, så grønt kun vil blive brugt, hvor tallet er større end eller lig med 10000
Dette er resultatet:
Brug af brøker i formatering af strenge
Brøker bruges ikke ofte i regneark, da de normalt svarer til decimaler, som alle kender.
Nogle gange tjener de imidlertid et formål. Dette eksempel viser dollars og øre:
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0" "dollars og" "00/100" "cent" "" |
Dette er resultatet, der vil blive produceret:
Husk, at på trods af at tallene vises som tekst, er de stadig der i baggrunden som tal, og alle Excel -formlerne kan stadig bruges på dem.
Dato- og klokkeslætformater
Datoer er faktisk tal, og du kan bruge formater på dem på samme måde som for tal. Hvis du formaterer en dato som et numerisk tal, vil du se et stort tal til venstre for decimalpunktet og et antal decimaler. Tallet til venstre for decimaltegnet viser antallet af dage, der starter den 01. januar 1900, og decimalerne viser tiden baseret på 24 timer
| 1 | MsgBox-format (Nu (), "dd-mmm-åååå") |
Dette vil formatere den aktuelle dato til at vise '08-juli-2020 '. Brug af ‘mmm’ for måneden viser de tre første tegn i månedsnavnet. Hvis du vil have hele månedsnavnet, bruger du ‘mmmm’
Du kan inkludere tider i din formatstreng:
| 1 | MsgBox Format (Nu (), "dd-mmm-åååå hh: mm AM/PM") |
Dette viser '08-juli-2020 13:25 '
'Hh: mm' repræsenterer timer og minutter, og AM/PM bruger et 12-timers ur i modsætning til et 24-timers ur.
Du kan indarbejde teksttegn i din formatstreng:
| 1 | MsgBox Format (Nu (), "dd-mmm-åååå hh: mm AM/PM" "i dag" "")) |
Dette viser '08-juli-2020 13:25 i dag '
Du kan også bruge bogstavelige tegn ved hjælp af en skråstreg foran på samme måde som for numeriske formatstrenge.
Foruddefinerede formater
Excel har en række indbyggede formater til både tal og datoer, som du kan bruge i din kode. Disse afspejler hovedsageligt, hvad der er tilgængeligt på nummerformateringsfronten, selvom nogle af dem går ud over det, der normalt er tilgængeligt i pop-up-vinduet. Du har heller ikke fleksibiliteten i forhold til antallet af decimaler, eller om der bruges tusindvis af skilletegn.
Generelt nummer
Dette format viser nummeret nøjagtigt, som det er
| 1 | MsgBox -format (1234567.89, "Generelt nummer") |
Resultatet bliver 1234567.89
betalingsmiddel
| 1 | MsgBox -format (1234567.894, "Valuta") |
Dette format tilføjer et valutasymbol foran tallet f.eks. $, £ afhængigt af din lokalitet, men det formaterer også tallet til 2 decimaler og adskiller tusinderne med kommaer.
Resultatet bliver $ 1.234.567,89
Rettet
| 1 | MsgBox -format (1234567.894, "Fixed") |
Dette format viser mindst et ciffer til venstre, men kun to cifre til højre for decimaltegnet.
Resultatet bliver 1234567.89
Standard
| 1 | MsgBox -format (1234567.894, "Standard") |
Dette viser tallet med de tusind skillelinjer, men kun til to decimaler.
Resultatet bliver 1.234.567,89
Procent
| 1 | MsgBox -format (1234567.894, "procent") |
Tallet ganges med 100, og et procent symbol (%) tilføjes i slutningen af tallet. Formatet vises til 2 decimaler
Resultatet bliver 123456789,40%
Videnskabelig
| 1 | MsgBox -format (1234567.894, "Videnskabelig") |
Dette konverterer tallet til eksponentielt format
Resultatet bliver 1,23E+06
Ja Nej
| 1 | MsgBox -format (1234567.894, "Ja/Nej") |
Dette viser 'Nej', hvis tallet er nul, ellers vises 'Ja'
Resultatet bliver 'Ja'
Sandt falsk
| 1 | MsgBox -format (1234567.894, "Sandt/falsk") |
Dette viser 'Falsk', hvis tallet er nul, ellers viser det 'sandt'
Resultatet bliver 'sandt'
Tænd sluk
| 1 | MsgBox -format (1234567.894, "Til/Fra") |
Dette viser 'Fra', hvis tallet er nul, ellers vises 'Til'
Resultatet bliver 'Til'
Generel dato
| 1 | MsgBox -format (Nu (), "Generel dato") |
Dette viser datoen som dato og klokkeslæt ved hjælp af AM/PM -notation. Hvordan datoen vises, afhænger af dine indstillinger i Windows Kontrolpanel (Ur og region | Region). Det kan vises som 'mm/dd/åååå' eller 'dd/mm/åååå'
Resultatet bliver ‘7/7/2020 15:48:25’
Lang dato
| 1 | MsgBox -format (Nu (), "Long Date") |
Dette viser en lang dato som defineret i Windows Kontrolpanel (Ur og region | Region). Bemærk, at det ikke inkluderer tiden.
Resultatet bliver 'tirsdag den 7. juli 2022'
Medium dato
| 1 | MsgBox -format (Nu (), "Medium dato") |
Dette viser en dato som defineret i indstillingerne for korte datoer som defineret af lokalitet i Windows Kontrolpanel.
Resultatet bliver '07 -Jul-20 '
Kort dato
| 1 | MsgBox -format (Nu (), "Short Date") |
Viser en kort dato som defineret i Windows Kontrolpanel (Ur og region | Region). Hvordan datoen vises afhænger af din lokalitet. Det kan vises som 'mm/dd/åååå' eller 'dd/mm/åååå'
Resultatet bliver '7/7/2020'
Lang tid
| 1 | MsgBox -format (nu (), "lang tid") |
Viser lang tid som defineret i Windows Kontrolpanel (Ur og region | Region).
Resultatet bliver '16:11:39'
Mellemtid
| 1 | MsgBox -format (Nu (), "Mellemtid") |
Viser en mellemlang tid som defineret af din lokalitet i Windows Kontrolpanel. Dette indstilles normalt som 12-timers format ved hjælp af timer, minutter og sekunder og AM/PM-formatet.
Resultatet bliver '04: 15 PM '
Kort tid
| 1 | MsgBox -format (Nu (), "Kort tid") |
Viser en mellemlang tid som defineret i Windows Kontrolpanel (Ur og region | Region). Dette er normalt indstillet som 24-timers format med timer og minutter
Resultatet bliver '16: 18 '
Farer ved brug af Excels foruddefinerede formater i datoer og tider
Brugen af de foruddefinerede formater til datoer og tidspunkter i Excel VBA er meget afhængig af indstillingerne i Windows Kontrolpanel og også, hvad landestandarden er indstillet til
Brugere kan nemt ændre disse indstillinger, og dette vil have en effekt på, hvordan dine datoer og tidspunkter vises i Excel
Hvis du f.eks. Udvikler et Excel-program, der bruger foruddefinerede formater i din VBA-kode, kan disse ændre sig fuldstændigt, hvis en bruger er i et andet land eller bruger en anden lokalitet end dig. Du kan opleve, at kolonnebredder ikke passer til datodefinitionen, eller på en brugerformular er Active X -kontrollen, f.eks. En kombinationsboks (drop down) -kontrol, for smal til, at datoer og tidspunkter kan vises korrekt.
Du skal overveje, hvor publikum er geografisk, når du udvikler din Excel -applikation
Brugerdefinerede formater for tal
Der er en række forskellige parametre, du kan bruge, når du definerer din formatstreng:
| Karakter | Beskrivelse |
| Null streng | Ingen formatering |
| 0 | Cifret pladsholder. Viser et ciffer eller et nul. Hvis der er et ciffer for den position, viser det cifret, ellers viser det 0. Hvis der er færre cifre end nuller, får du førende eller efterfølgende nuller. Hvis der er flere cifre efter decimaltegnet, end der er nuller, afrundes tallet til antallet af decimaler, der vises af nullerne. Hvis der er flere cifre før decimalpunktet end nuller, vises disse normalt. |
| # | Cifret pladsholder. Dette viser et ciffer eller ingenting. Det fungerer på samme måde som nul -pladsholderen ovenfor, bortset fra at indledende og efterfølgende nuller ikke vises. For eksempel vil 0,75 blive vist ved hjælp af nul pladsholdere, men dette ville være .75 ved hjælp af # pladsholdere. |
| . Decimaltegnet. | Kun én tilladt pr. Formatstreng. Dette tegn afhænger af indstillingerne i Windows Kontrolpanel. |
| % | Procentdel pladsholder. Multiplicerer tallet med 100 og placerer % tegn, hvor det vises i formatstrengen |
| , (komma) | Tusind separator. Dette bruges, hvis der bruges 0 eller # pladsholdere, og formatstrengen indeholder et komma. Et komma til venstre for decimaltegnet angiver runde til nærmeste tusinde. F.eks. ## 0, To tilstødende kommaer til venstre for tusindseparatoren angiver afrunding til den nærmeste million. F.eks. ## 0 ,, |
| E- E+ | Videnskabeligt format. Dette viser tallet eksponentielt. |
| : (kolon) | Tidsudskiller - bruges ved formatering af en tid til at dele timer, minutter og sekunder. |
| / | Datoseparator - dette bruges, når du angiver et format for en dato |
| - + £ $ ( ) | Viser en bogstavelig karakter.For at få vist et andet tegn end angivet her skal du gå forud for det med en omvendt skråstreg (\) |
Brugerdefinerede formater til datoer og tider
Disse tegn kan alle bruges i din formatstreng, når du formaterer datoer og tidspunkter:
| Karakter | Betyder |
| c | Viser datoen som ddddd og klokkeslættet som ttttt |
| d | Vis dagen som et tal uden nul |
| dd | Vis dagen som et tal med førende nul |
| ddd | Vis dagen som en forkortelse (søn - lør) |
| dddd | Vis dagens fulde navn (søndag - lørdag) |
| ddddd | Vis et dato serienummer som en komplet dato i henhold til Short Date i de internationale indstillinger i Windows Kontrolpanel |
| dddddd | Viser et dato serienummer som en komplet dato i henhold til Long Date i de internationale indstillinger i Windows Kontrolpanel. |
| w | Viser ugedag som et tal (1 = søndag) |
| ww | Viser årets uge som et tal (1-53) |
| m | Viser måneden som et tal uden nul |
| mm | Viser måneden som et tal med førende nuller |
| mmm | Viser måned som en forkortelse (jan-dec) |
| mmmm | Viser månedens fulde navn (januar - december) |
| q | Viser årets kvartal som et tal (1-4) |
| y | Viser årets dag som et tal (1-366) |
| åå | Viser året som et tocifret tal |
| åååå | Viser året som firecifret tal |
| h | Viser timen som et tal uden førende nul |
| hh | Viser timen som et tal med førende nul |
| n | Viser minuttet som et tal uden førende nul |
| nn | Viser minuttet som et tal med førende nul |
| s | Viser det andet som et tal uden førende nul |
| ss | Viser det andet som et tal med førende nul |
| ttttt | Vis et tidsserienummer som en fuldstændig tid. |
| AM/PM | Brug et 12-timers ur og vis AM eller PM for at angive før eller efter middag. |
| am/pm | Brug et 12-timers ur, og brug am eller pm til at angive før eller efter middag |
| A/P | Brug et 12-timers ur, og brug A eller P til at angive før eller efter middag |
| a/s | Brug et 12-timers ur, og brug a eller p til at angive før eller efter middag |
