Formatering av tall i Excel VBA
Tall kommer i alle slags formater i Excel -regneark. Du er kanskje allerede kjent med popup-vinduet i Excel for bruk av forskjellige numeriske formater:
Formatering av tall gjør tallene lettere å lese og forstå. Excel -standarden for tall som er lagt inn i celler er ‘Generelt’ format, noe som betyr at nummeret vises nøyaktig slik du skrev det inn.
For eksempel, hvis du skriver inn et rundt tall, f.eks. 4238, vil det bli vist som 4238 uten desimaltegn eller tusenvis skilletegn. Et desimalnummer som 9325.89 vil bli vist med desimalpunktet og desimalene. Dette betyr at den ikke vil stille seg opp i kolonnen med de runde tallene, og vil se ekstremt rotete ut.
Uten å vise tusenvis skilletegn er det også vanskelig å se hvor stort et tall faktisk er uten å telle de enkelte sifrene. Er det millioner eller titalls millioner?
Sett fra en bruker som ser nedover en kolonne med tall, gjør dette det ganske vanskelig å lese og sammenligne.
I VBA har du tilgang til nøyaktig samme formatformat som du har på forsiden av Excel. Dette gjelder ikke bare en angitt verdi i en celle i et regneark, men også ting som meldingsbokser, UserForm -kontroller, diagrammer og grafer og Excel -statuslinjen nederst i venstre hjørne av regnearket.
Format -funksjonen er en ekstremt nyttig funksjon i VBA når det gjelder presentasjon, men den er også veldig kompleks når det gjelder fleksibiliteten som tilbys i hvordan tall vises.
Hvordan bruke formatfunksjonen i VBA
Hvis du viser en meldingsboks, kan Format -funksjonen brukes direkte:
| 1 | MsgBox -format (1234567.89, "#, ## 0.00") |
Dette vil vise et stort antall ved hjelp av kommaer for å skille tusenvis og vise to desimaler. Resultatet blir 1 234 567,89. Nullpunktene i stedet for hashen sørger for at desimaler vises som 00 i hele tall, og at det er en ledende null for et tall som er mindre enn 1
Hashtag -symbolet (#) representerer en sifferplassholder som viser et siffer hvis det er tilgjengelig i den posisjonen, eller ellers ingenting.
Du kan også bruke formatfunksjonen til å adressere en individuell celle, eller en rekke celler for å endre formatet:
| 1 | Ark ("Ark1"). Område ("A1: A10"). NumberFormat = "#, ## 0.00" |
Denne koden angir celleområdet (A1 til A10) til et tilpasset format som skiller tusenvis med kommaer og viser 2 desimaler.
Hvis du sjekker formatet på cellene på Excel -grensesnittet, finner du at et nytt tilpasset format er opprettet.
Du kan også formatere tall på Excel -statuslinjen nederst til venstre i Excel -vinduet:
| 1 | Application.StatusBar = Format (1234567.89, "#, ## 0.00") |
Du fjerner dette fra statuslinjen ved å bruke:
| 1 | Application.StatusBar = "" |
Opprette en formatstreng
Dette eksemplet vil legge til teksten "Totalt salg" etter hvert nummer, i tillegg til at det inneholder en tusenvis skilletegn
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00" "Totalt salg" "" |
Slik vil tallene dine se ut:
Vær oppmerksom på at celle A6 har en "SUM" -formel, og dette vil inkludere teksten "Totalt salg" uten å kreve formatering. Hvis formateringen brukes, som i koden ovenfor, vil den ikke sette en ekstra forekomst av "Total Sales" i celle A6
Selv om cellene nå viser alfanumeriske tegn, er tallene fremdeles i numerisk form. "SUM" -formelen fungerer fortsatt fordi den bruker den numeriske verdien i bakgrunnen, ikke hvordan tallet er formatert.
Komma i formatstrengen gir tusenvis skilletegn. Vær oppmerksom på at du bare trenger å sette dette i strengen én gang. Hvis tallet kommer opp i millioner eller milliarder, vil det fortsatt dele tallene i grupper på 3
Nullen i formatstrengen (0) er en sifferplassholder. Det viser et siffer hvis det er der, eller et null. Plasseringen er veldig viktig for å sikre jevnhet med formateringen
I formatstrengen vil hashtegnene (#) ingenting vise hvis det ikke er noe siffer. Men hvis det er et tall som .8 (alle desimaler), vil vi at det skal vises som 0.80 slik at det stemmer overens med de andre tallene.
Ved å bruke en enkelt null til venstre for desimaltegnet og to nuller til høyre for desimaltegnet i formatstrengen, vil dette gi det nødvendige resultatet (0,80).
Hvis det bare var ett null til høyre for desimaltegnet, ville resultatet være ‘0,8’ og alt ville bli vist til en desimal.
Bruke en formatstreng for justering
Det kan være lurt å se alle desimaltallene i et område på linje med desimalpunktene, slik at alle desimalpunktene ligger rett under hverandre, men mange desimaler er det på hvert tall.
Du kan bruke et spørsmålstegn (?) I formatstrengen for å gjøre dette. '?' Indikerer at et tall vises hvis det er tilgjengelig, eller et mellomrom
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Dette viser tallene dine som følger:
Alle desimaltegnene står nå på linje under hverandre. Celle A5 har tre desimaler, og dette vil kaste justeringen ut normalt, men bruk av "?" - tegnet justerer alt perfekt.
Bruke bokstavelige tegn i formatstrengen
Du kan legge til et hvilket som helst bokstav i formatstrengen din ved å gå foran den med en skråstrek (\).
Anta at du vil vise en bestemt valutaindikator for tallene dine, som ikke er basert på din lokalitet. Problemet er at hvis du bruker en valutaindikator, refererer Excel automatisk til din lokale og endrer den til den som passer for landestandarden som er angitt på Windows Kontrollpanel. Dette kan ha implikasjoner hvis Excel -applikasjonen din distribueres i andre land, og du vil sikre at valutaindikatoren alltid er den samme uansett hvilken lokalitet som er.
Det kan også være lurt å angi at tallene er i millioner i følgende eksempel:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
Dette gir følgende resultater på regnearket:
Når du bruker en skråstrek for å vise bokstavelige tegn, trenger du ikke å bruke en skråstrek for hvert enkelt tegn i en streng. Du kan bruke:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Dette vil vise "mill" etter hvert tall i det formaterte området.
Du kan bruke de fleste tegn som bokstav, men ikke reserverte tegn som 0, #,?
Bruk av kommaer i en formatstreng
Vi har allerede sett at kommaer kan brukes til å lage tusenvis skilletegn for store tall, men de kan også brukes på en annen måte.
Ved å bruke dem på slutten av den numeriske delen av formatstrengen, fungerer de som skalere av tusenvis. Med andre ord vil de dele hvert tall med 1000 hver gang det er komma.
I eksempeldataene viser vi det med en indikator på at det er i millioner. Ved å sette inn ett komma i formatstrengen, kan vi vise disse tallene delt på 1000.
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Dette vil vise tallene delt på 1000 selv om det opprinnelige tallet fortsatt vil være i bakgrunnen i cellen.
Hvis du setter to kommaer i formatstrengen, blir tallene delt med en million
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
Dette blir resultatet med bare ett komma (divider med 1000):
Opprette betinget formatering i formatstrengen
Du kan konfigurere betinget formatering på forsiden av Excel, men du kan også gjøre det i VBA -koden, noe som betyr at du kan manipulere formatstrengen programmatisk for å gjøre endringer.
Du kan bruke opptil fire seksjoner i formatstrengen. Hver seksjon er avgrenset med et semikolon (;). De fire seksjonene tilsvarer positiv, negativ, null og tekst
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0.00; [Red]-#, ## 0.00; [Green]#, ## 0.00; [Blue]" |
I dette eksemplet bruker vi samme hash, komma og null tegn for å gi tusen skilletegn og to desimaler, men vi har nå forskjellige seksjoner for hver type verdi.
Den første delen er for positive tall og er ikke forskjellig fra det vi allerede har sett når det gjelder format.
Den andre delen for negative tall introduserer en farge (rød) som holdes innenfor et par firkantede parenteser. Formatet er det samme som for positive tall bortsett fra at det er lagt til et minus (-) foran.
Den tredje delen for null tall bruker en farge (grønn) innenfor firkantede parenteser med den numeriske strengen den samme som for positive tall.
Den siste delen er for tekstverdier, og alt dette trenger er en farge (blå) igjen innenfor hakeparenteser
Dette er resultatet av å bruke denne formatstrengen:
Du kan gå videre med betingelser i formatstrengen. Anta at du ønsket å vise hvert positivt tall over 10 000 som grønt, og hvert annet tall som rødt kunne du bruke denne formatstrengen:
| 1 | Område ("A1: A7"). NumberFormat = "[> = 10000] [Grønn]#, ## 0.00; [<10000] [Rød]#, ## 0.00" |
Denne formatstrengen inkluderer betingelser for> = 10000 satt i firkantede parenteser, slik at grønt bare vil bli brukt der tallet er større enn eller lik 10000
Dette er resultatet:
Bruke brøker i formatering av strenger
Brøker brukes ikke ofte i regneark, siden de vanligvis tilsvarer desimaler som alle er kjent med.
Noen ganger tjener de imidlertid et formål. Dette eksemplet viser dollar og øre:
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0" "dollar og" "00/100" "cent" "" |
Dette er resultatet som vil bli produsert:
Husk at til tross for at tallene vises som tekst, er de fortsatt der i bakgrunnen som tall, og alle Excel -formlene kan fortsatt brukes på dem.
Dato- og klokkeslettformater
Datoer er faktisk tall, og du kan bruke formater på dem på samme måte som for tall. Hvis du formaterer en dato som et tall, vil du se et stort tall til venstre for desimaltegnet og et antall desimaler. Tallet til venstre for desimaltegnet viser antall dager som starter fra 01. januar 1900, og desimalene viser tiden basert på 24 timer
| 1 | MsgBox-format (nå (), "dd-mmm-åååå") |
Dette vil formatere gjeldende dato til å vise '08-juli-2020 '. Ved å bruke ‘mmm’ for måneden vises de tre første tegnene i månedsnavnet. Hvis du vil ha hele månedsnavnet, bruker du ‘mmmm’
Du kan inkludere tider i formatstrengen din:
| 1 | MsgBox-format (nå (), "dd-mmm-åååå hh: mm AM/PM") |
Dette vil vise '08-juli-2020 13:25 '
'Hh: mm' representerer timer og minutter, og AM/PM bruker en 12-timers klokke i motsetning til en 24-timers klokke.
Du kan inkludere teksttegn i formatstrengen:
| 1 | MsgBox Format (Now (), "dd-mmm-åååå hh: mm AM/PM" "i dag" "" ") |
Dette vil vise '08 -Jul-2020 13:25 i dag '
Du kan også bruke bokstavene med en skråstrek foran på samme måte som for numeriske formatstrenger.
Forhåndsdefinerte formater
Excel har en rekke innebygde formater for både tall og datoer som du kan bruke i koden. Disse gjenspeiler hovedsakelig det som er tilgjengelig på nummerformateringsfronten, selv om noen av dem går utover det som normalt er tilgjengelig i popup-vinduet. Du har heller ikke fleksibiliteten i forhold til antall desimaler, eller om tusenvis skilletegn brukes.
Generelt nummer
Dette formatet viser tallet nøyaktig slik det er
| 1 | MsgBox -format (1234567.89, "Generelt nummer") |
Resultatet blir 1234567.89
Valuta
| 1 | MsgBox -format (1234567.894, "valuta") |
Dette formatet vil legge til et valutasymbol foran tallet f.eks. $, £ avhengig av lokalitet, men det vil også formatere tallet til 2 desimaler og skille tusenvis med kommaer.
Resultatet blir 1 234 567,89 dollar
Fikset
| 1 | MsgBox -format (1234567.894, "løst") |
Dette formatet viser minst ett siffer til venstre, men bare to sifre til høyre for desimaltegnet.
Resultatet blir 1234567.89
Standard
| 1 | MsgBox -format (1234567.894, "Standard") |
Dette viser tallet med tusen skilletegn, men bare til to desimaler.
Resultatet blir 1 234 567,89
Prosent
| 1 | MsgBox -format (1234567.894, "prosent") |
Tallet multipliseres med 100 og et prosent symbol (%) legges til på slutten av tallet. Formatet vises til 2 desimaler
Resultatet blir 123456789,40%
Vitenskapelig
| 1 | MsgBox -format (1234567.894, "vitenskapelig") |
Dette konverterer tallet til eksponentielt format
Resultatet blir 1.23E+06
Ja Nei
| 1 | MsgBox -format (1234567.894, "Ja/Nei") |
Dette viser "Nei" hvis tallet er null, ellers vises "Ja"
Resultatet blir "Ja"
Sant/usant
| 1 | MsgBox -format (1234567.894, "Sant/usant") |
Dette viser "False" hvis tallet er null, ellers viser det "True"
Resultatet blir "sant"
På av
| 1 | MsgBox -format (1234567.894, "På/Av") |
Dette viser "Av" hvis tallet er null, ellers vises "På"
Resultatet blir "På"
Generell dato
| 1 | MsgBox -format (nå (), "Generell dato") |
Dette vil vise datoen som dato og klokkeslett ved bruk av AM/PM -notasjon. Hvordan datoen vises, avhenger av innstillingene i Windows Kontrollpanel (Klokke og region | Region). Det kan vises som "mm/dd/åååå" eller "dd/mm/åååå"
Resultatet blir ‘7/7/2020 15:48:25’
Lang date
| 1 | MsgBox -format (nå (), "Long Date") |
Dette vil vise en lang dato som definert i Windows Kontrollpanel (Klokke og region | Region). Vær oppmerksom på at det ikke inkluderer tiden.
Resultatet blir 'tirsdag 7. juli 2022'
Middels dato
| 1 | MsgBox -format (nå (), "middels dato") |
Dette viser en dato som er definert i innstillingene for korte datoer som definert av språk i Windows Kontrollpanel.
Resultatet blir '07 -Jul-20 '
Kort dato
| 1 | MsgBox Format (Now (), "Short Date") |
Viser en kort dato som definert i Windows Kontrollpanel (Klokke og region | Region). Hvordan datoen vises, avhenger av lokaliteten din. Det kan vises som "mm/dd/åååå" eller "dd/mm/åååå"
Resultatet blir ‘7/7/2020’
Lang tid
| 1 | MsgBox Format (Now (), "Long Time") |
Viser lang tid som definert i Windows Kontrollpanel (Klokke og region | Region).
Resultatet blir ‘16:11:39 PM’
Middels tid
| 1 | MsgBox Format (Now (), "Medium Time") |
Viser en middels tid som definert av din lokalitet i kontrollpanelet i Windows. Dette er vanligvis angitt som 12-timers format ved hjelp av timer, minutter og sekunder og AM/PM-formatet.
Resultatet blir '04: 15 PM '
Kort tid
| 1 | MsgBox -format (nå (), "kort tid") |
Viser en middels tid som definert i Windows Kontrollpanel (Klokke og region | Region). Dette er vanligvis angitt som 24-timers format med timer og minutter
Resultatet blir '16: 18 '
Farer ved bruk av Excels forhåndsdefinerte formater i datoer og tider
Bruken av de forhåndsdefinerte formatene for datoer og klokkeslett i Excel VBA er veldig avhengig av innstillingene i Windows Kontrollpanel og også hva landemerket er satt til
Brukere kan enkelt endre disse innstillingene, og dette vil påvirke hvordan datoene og klokkeslettene dine vises i Excel
For eksempel, hvis du utvikler et Excel-program som bruker forhåndsdefinerte formater i VBA-koden, kan disse endres fullstendig hvis en bruker er i et annet land eller bruker et annet sted enn deg. Det kan hende du oppdager at kolonnebredder ikke passer til datodefinisjonen, eller på et brukerskjema er Active X -kontrollen, for eksempel en kombinasjonsboks (rullegardinmenyen), for smal til at datoene og klokkeslettene kan vises riktig.
Du må vurdere hvor publikum er geografisk når du utvikler Excel -applikasjonen din
Brukerdefinerte formater for tall
Det er en rekke forskjellige parametere du kan bruke når du definerer formatstrengen:
| Karakter | Beskrivelse |
| Null streng | Ingen formatering |
| 0 | Sifferplassholder. Viser et siffer eller et null. Hvis det er et siffer for den posisjonen, viser det sifferet ellers viser det 0. Hvis det er færre sifre enn nuller, får du innledende eller etterfølgende nuller. Hvis det er flere sifre etter desimaltegnet enn det er nuller, avrundes tallet til antall desimaler som vises av nullene. Hvis det er flere sifre før desimaltegnet enn nuller, vises disse normalt. |
| # | Sifferplassholder. Dette viser et siffer eller ingenting. Den fungerer på samme måte som nullplassholderen ovenfor, bortsett fra at innledende og etterfølgende nuller ikke vises. For eksempel vil 0,75 bli vist ved hjelp av null plassholdere, men dette ville være .75 med # plassholdere. |
| . Desimal tegn. | Bare én tillatt per formatstreng. Dette tegnet avhenger av innstillingene i Windows Kontrollpanel. |
| % | Prosentandel av plassholder. Multipliserer tallet med 100 og plasserer % tegn der det vises i formatstrengen |
| , (komma) | Tusen separator. Dette brukes hvis 0 eller # plassholdere brukes og formatstrengen inneholder et komma. Ett komma til venstre for desimaltegnet indikerer runde til nærmeste tusen. F.eks. ## 0, To tilstøtende kommaer til venstre for tusenutskilleren indikerer avrunding til nærmeste million. F.eks. ## 0 ,, |
| E- E+ | Vitenskapelig format. Dette viser tallet eksponentielt. |
| : (kolon) | Tidsseparator - brukes når du formaterer en tid for å dele timer, minutter og sekunder. |
| / | Datoseparator - dette brukes når du angir et format for en dato |
| - + £ $ ( ) | Viser en bokstavelig karakter.For å vise et annet tegn enn det som er oppført her, gå foran det med en skråstrek (\) |
Brukerdefinerte formater for datoer og tider
Disse tegnene kan alle brukes i formateringsstrengen når du formaterer datoer og klokkeslett:
| Karakter | Betydning |
| c | Viser datoen som ddddd og klokkeslettet som ttttt |
| d | Vis dagen som et tall uten ledende null |
| dd | Vis dagen som et tall med ledende null |
| ddd | Vis dagen som en forkortelse (søn - lør) |
| dddd | Vis hele navnet på dagen (søndag - lørdag) |
| ddddd | Vis et dato serienummer som en fullstendig dato i henhold til Short Date i de internasjonale innstillingene i kontrollpanelet i Windows |
| dddddd | Viser et dato serienummer som en fullstendig dato i henhold til Long Date i de internasjonale innstillingene i Windows Kontrollpanel. |
| w | Viser ukedagen som et tall (1 = søndag) |
| ww | Viser årets uke som et tall (1-53) |
| m | Viser måneden som et tall uten ledende null |
| mm | Viser måneden som et tall med ledende nuller |
| mmm | Viser måned som en forkortelse (jan-des) |
| mmmm | Viser hele månedens navn (januar - desember) |
| q | Viser årets kvartal som et tall (1-4) |
| y | Viser årets dag som et tall (1-366) |
| åå | Viser året som et tosifret tall |
| åååå | Viser året som firesifret tall |
| h | Viser timen som et tall uten ledende null |
| hh | Viser timen som et tall med ledende null |
| n | Viser minuttet som et tall uten ledende null |
| nn | Viser minuttet som et tall med ledende null |
| s | Viser det andre som et tall uten ledende null |
| ss | Viser det andre som et tall med ledende null |
| ttttt | Vis et tidsserienummer som en fullstendig tid. |
| MORGEN KVELD | Bruk en 12-timers klokke og vis AM eller PM for å indikere før eller etter middag. |
| morgen kveld | Bruk en 12-timers klokke og bruk am eller pm for å indikere før eller etter middag |
| A/P | Bruk en 12-timers klokke og bruk A eller P for å indikere før eller etter middagstid |
| a/s | Bruk en 12-timers klokke og bruk a eller p for å indikere før eller etter middagstid |
