Formatera nummer i Excel VBA
Siffror finns i alla möjliga format i Excel -kalkylblad. Du kanske redan känner till popup-fönstret i Excel för att använda olika numeriska format:
Formatering av siffror gör siffrorna lättare att läsa och förstå. Excel -standard för siffror som anges i celler är "Allmänt" -format, vilket innebär att numret visas exakt som du skrev in det.
Om du till exempel anger ett runt nummer t.ex. 4238 kommer det att visas som 4238 utan decimalpunkt eller tusentals separatorer. Ett decimaltal som 9325.89 visas med decimalpunkten och decimalerna. Det betyder att det inte kommer att ligga i kolumnen med de runda siffrorna och kommer att se extremt rörigt ut.
Utan att visa tusentalsavgränsarna är det också svårt att se hur stort ett antal faktiskt är utan att räkna de enskilda siffrorna. Är det i miljoner eller tiotals miljoner?
Från en användares synvinkel som tittar ner i en kolumn med siffror gör det det ganska svårt att läsa och jämföra.
I VBA har du tillgång till exakt samma format som du har på framsidan av Excel. Detta gäller inte bara ett inmatat värde i en cell i ett kalkylblad, utan även saker som meddelandefält, UserForm -kontroller, diagram och grafer och Excel -statusfältet i nedre vänstra hörnet av kalkylbladet.
Formatfunktionen är en extremt användbar funktion i VBA i presentations termer, men det är också mycket komplext när det gäller flexibiliteten som erbjuds i hur siffror visas.
Hur man använder formatfunktionen i VBA
Om du visar en meddelanderuta kan du använda Format -funktionen direkt:
| 1 | MsgBox -format (1234567.89, "#, ## 0.00") |
Detta kommer att visa ett stort antal med kommatecken för att skilja tusentals och för att visa 2 decimaler. Resultatet blir 1 234 567,89. Nollorna i stället för hash ser till att decimaler visas som 00 i heltal och att det finns en inledande nolla för ett tal som är mindre än 1
Hashtagg -symbolen (#) representerar en siffra platshållare som visar en siffra om den är tillgänglig i den positionen, eller annars ingenting.
Du kan också använda formatfunktionen för att adressera en enskild cell eller ett cellintervall för att ändra formatet:
| 1 | Ark ("Ark1"). Område ("A1: A10"). NumberFormat = "#, ## 0.00" |
Denna kod kommer att ställa in cellintervallet (A1 till A10) till ett anpassat format som skiljer tusentals med kommatecken och visar 2 decimaler.
Om du kontrollerar cellernas format på Excel -gränssnittet hittar du att ett nytt anpassat format har skapats.
Du kan också formatera siffror i Excel -statusfältet längst ned till vänster i Excel -fönstret:
| 1 | Application.StatusBar = Format (1234567.89, "#, ## 0.00") |
Du rensar detta från statusfältet genom att använda:
| 1 | Application.StatusBar = "" |
Skapa en formatsträng
I det här exemplet kommer texten "Total försäljning" att läggas till efter varje nummer, samt en tusentalsavgränsare
| 1 | Kalkylark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00" "Total försäljning" "" |
Så här kommer dina siffror att se ut:
Observera att cell A6 har en "SUMM" -formel, och detta kommer att innehålla texten "Total försäljning" utan att kräva formatering. Om formateringen tillämpas, som i ovanstående kod, kommer den inte att lägga till en extra instans av "Total Sales" i cell A6
Även om cellerna nu visar alfanumeriska tecken, finns siffrorna fortfarande i numerisk form. Formeln ‘SUMMA’ fungerar fortfarande eftersom den använder det numeriska värdet i bakgrunden, inte hur talet är formaterat.
Kommat i formatsträngen ger tusentalsavgränsaren. Observera att du bara behöver lägga detta i strängen en gång. Om antalet går upp i miljoner eller miljarder kommer det fortfarande att dela upp siffrorna i grupper om 3
Nollan i formatsträngen (0) är en siffra platshållare. Den visar en siffra om den är där, eller en nolla. Dess placering är mycket viktig för att säkerställa enhetlighet med formateringen
I formatsträngen visar hashtecknen (#) ingenting om det inte finns någon siffra. Men om det finns ett tal som .8 (alla decimaler), vill vi att det ska visas som 0.80 så att det ligger i linje med de andra talen.
Genom att använda en enda nolla till vänster om decimalpunkten och två nollor till höger om decimalpunkten i formatsträngen, ger detta det erforderliga resultatet (0,80).
Om det bara fanns en nolla till höger om decimalpunkten, skulle resultatet vara "0,8" och allt skulle visas till en decimal.
Använda en formatsträng för justering
Vi kanske vill se alla decimalnummer i ett intervall i linje med deras decimaler, så att alla decimaler ligger direkt under varandra, hur många decimaler som helst finns på varje tal.
Du kan använda ett frågetecken (?) I din formatsträng för att göra detta. "?" Indikerar att ett nummer visas om det är tillgängligt eller ett mellanslag
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Detta visar dina nummer enligt följande:
Alla decimaler står nu under varandra. Cell A5 har tre decimaler och detta skulle slänga ut justeringen normalt, men att använda "?" - tecknet anpassar allt perfekt.
Använda bokstavstecken inom formatsträngen
Du kan lägga till vilket bokstav som helst i din formatsträng genom att föregå det med ett snedstreck (\).
Antag att du vill visa en särskild valutaindikator för dina nummer som inte är baserad på ditt språk. Problemet är att om du använder en valutaindikator hänvisar Excel automatiskt till din lokal och ändrar den till den som är lämplig för den plats som är inställd på Windows kontrollpanel. Detta kan få konsekvenser om din Excel -applikation distribueras i andra länder och du vill se till att valutaindikatorn alltid är densamma oavsett vilken lokal det är.
Du kanske också vill ange att siffrorna är i miljoner i följande exempel:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
Detta ger följande resultat på ditt kalkylblad:
När du använder ett snedstreck för att visa bokstavstecken behöver du inte använda ett snedstreck för varje enskilt tecken i en sträng. Du kan använda:
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Detta kommer att visa "kvarn" efter varje nummer inom det formaterade intervallet.
Du kan använda de flesta tecken som bokstav, men inte reserverade tecken som 0, #,?
Användning av kommatecken i en formatsträng
Vi har redan sett att kommatecken kan användas för att skapa tusentals separatorer för stora antal, men de kan också användas på ett annat sätt.
Genom att använda dem i slutet av den numeriska delen av formatsträngen fungerar de som tusentals skalare. Med andra ord kommer de att dela varje tal med 1000 varje gång det finns ett komma.
I exempeldata visar vi det med en indikator på att det är i miljoner. Genom att infoga ett kommatecken i formatsträngen kan vi visa dessa tal dividerade med 1 000.
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Detta visar siffrorna dividerade med 1000 även om det ursprungliga numret fortfarande kommer att finnas i bakgrunden i cellen.
Om du sätter två kommatecken i formatsträngen delas siffrorna med en miljon
| 1 | Ark ("Ark1"). Område ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
Detta blir resultatet med bara ett komma (dividera med 1000):
Skapa villkorlig formatering inom formatsträngen
Du kan ställa in villkorlig formatering på framsidan av Excel, men du kan också göra det i din VBA -kod, vilket innebär att du kan manipulera formatsträngen programmatiskt för att göra ändringar.
Du kan använda upp till fyra sektioner i din formatsträng. Varje sektion avgränsas av ett semikolon (;). De fyra sektionerna motsvarar positivt, negativt, noll och text
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0.00; [Red]-#, ## 0.00; [Green]#, ## 0.00; [Blue]" |
I det här exemplet använder vi samma hash, komma och noll tecken för att ge tusen separatorer och två decimaler, men vi har nu olika sektioner för varje typ av värde.
Det första avsnittet är för positiva siffror och skiljer sig inte från vad vi redan sett tidigare när det gäller format.
Det andra avsnittet för negativa tal introducerar en färg (röd) som hålls inom ett par hakparenteser. Formatet är detsamma som för positiva tal förutom att ett minus (-) har lagts till framför.
Det tredje avsnittet för nolltal använder en färg (grön) inom hakparenteser med den numeriska strängen densamma som för positiva tal.
Det sista avsnittet är för textvärden, och allt som behövs är en färg (blå) igen inom hakparenteser
Detta är resultatet av att använda denna formatsträng:
Du kan gå längre med villkor inom formatsträngen. Antag att du ville visa varje positivt tal över 10 000 som grönt, och vartannat nummer som rött kan du använda den här formatsträngen:
| 1 | Område ("A1: A7"). NumberFormat = "[> = 10000] [Grön]#, ## 0.00; [<10000] [Röd]#, ## 0.00" |
Denna formatsträng innehåller villkor för> = 10000 i hakparenteser så att grönt bara kommer att användas där talet är större än eller lika med 10000
Detta är resultatet:
Använda bråk i formateringssträngar
Fraktioner används inte ofta i kalkylblad, eftersom de normalt motsvarar decimaler som alla känner till.
Men ibland tjänar de ett syfte. I det här exemplet visas dollar och cent:
| 1 | Område ("A1: A7"). NumberFormat = "#, ## 0" "dollar och" "00/100" "cent" "" |
Detta är resultatet som kommer att produceras:
Kom ihåg att trots att siffrorna visas som text, finns de fortfarande där i bakgrunden som siffror och alla Excel -formler kan fortfarande användas på dem.
Datum- och tidsformat
Datum är faktiskt siffror och du kan använda format på dem på samma sätt som för siffror. Om du formaterar ett datum som ett numeriskt tal kommer du att se ett stort tal till vänster om decimalpunkten och ett antal decimaler. Siffran till vänster om decimalpunkten visar antalet dagar som börjar med 01-jan-1900, och decimalerna visar tiden baserat på 24 timmar
| 1 | MsgBox-format (nu (), "dd-mmm-åååå") |
Detta formaterar det aktuella datumet så att det visar '08-juli-2020 '. Om du använder ”mmm” för månaden visas de tre första tecknen i månadens namn. Om du vill ha hela månadens namn använder du "mmmm"
Du kan inkludera tider i din formatsträng:
| 1 | MsgBox-format (nu (), "dd-mmm-åååå hh: mm AM/PM") |
Detta kommer att visa '08-juli-2020 13:25 '
"Hh: mm" representerar timmar och minuter och AM/PM använder en 12-timmars klocka i motsats till en 24-timmars klocka.
Du kan införliva texttecken i din formatsträng:
| 1 | MsgBox-format (nu (), "dd-mmm-åååå hh: mm AM/PM" "idag" "")) |
Detta kommer att visa '08 -Jul-2020 13:25 idag '
Du kan också använda bokstavstecken med ett snedstreck framför på samma sätt som för numeriska formatsträngar.
Fördefinierade format
Excel har ett antal inbyggda format för både siffror och datum som du kan använda i din kod. Dessa återspeglar huvudsakligen vad som är tillgängligt på frontformatet för nummerformatering, även om några av dem går utöver vad som normalt är tillgängligt i popup-fönstret. Du har inte heller flexibiliteten över antalet decimaler, eller om tusentals separatorer används.
Allmänt nummer
Detta format visar numret exakt som det är
| 1 | MsgBox -format (1234567.89, "Allmänt nummer") |
Resultatet blir 1234567,89
Valuta
| 1 | MsgBox -format (1234567.894, "Valuta") |
Detta format kommer att lägga till en valutasymbol framför siffran t.ex. $, £ beroende på din lokal, men det kommer också att formatera talet till 2 decimaler och kommer att skilja tusentals med kommatecken.
Resultatet blir 1 234 567,89 dollar
Fast
| 1 | MsgBox -format (1234567.894, "Fixat") |
Detta format visar minst en siffra till vänster men bara två siffror till höger om decimalpunkten.
Resultatet blir 1234567,89
Standard
| 1 | MsgBox -format (1234567.894, "Standard") |
Detta visar antalet med tusenavgränsare, men bara till två decimaler.
Resultatet blir 1 234 567,89
Procent
| 1 | MsgBox -format (1234567.894, "Procent") |
Antalet multipliceras med 100 och en procentsymbol (%) läggs till i slutet av numret. Formatet visas till 2 decimaler
Resultatet blir 123456789,40%
Vetenskaplig
| 1 | MsgBox -format (1234567.894, "vetenskapligt") |
Detta omvandlar talet till exponentiellt format
Resultatet blir 1,23E+06
Ja Nej
| 1 | MsgBox -format (1234567.894, "Ja/Nej") |
Detta visar "Nej" om talet är noll, annars visas "Ja"
Resultatet blir "Ja"
Sant falskt
| 1 | MsgBox -format (1234567.894, "True/False") |
Detta visar "Falskt" om talet är noll, annars visas "Sant"
Resultatet blir "sant"
På av
| 1 | MsgBox -format (1234567.894, "På/Av") |
Detta visar "Av" om talet är noll, annars visas "På"
Resultatet blir "På"
Allmänt datum
| 1 | MsgBox -format (nu (), "Allmänt datum") |
Detta visar datum som datum och tid med AM/PM -notering. Hur datumet visas beror på dina inställningar i Windows kontrollpanel (Klocka och region | Region). Det kan visas som "mm/dd/åååå" eller "dd/mm/åååå"
Resultatet blir '7/7/2020 15:48:25'
Långt datum
| 1 | MsgBox -format (nu (), "Long Date") |
Detta kommer att visa ett långt datum enligt definitionen i Windows kontrollpanel (Klocka och region | Region). Observera att den inte inkluderar tiden.
Resultatet blir "tisdagen den 7 juli 2022"
Medium Datum
| 1 | MsgBox -format (nu (), "Medium Date") |
Detta visar ett datum som definieras i kortdatuminställningarna som definieras av språk i Windows kontrollpanel.
Resultatet blir '07 -Jul-20 '
Kort datum
| 1 | MsgBox -format (nu (), "kort datum") |
Visar ett kort datum enligt definitionen i Windows kontrollpanel (Klocka och region | Region). Hur datumet visas beror på din ort. Det kan visas som "mm/dd/åååå" eller "dd/mm/åååå"
Resultatet blir 7/7/2020
Länge sedan
| 1 | MsgBox -format (nu (), "Lång tid") |
Visar en lång tid enligt definitionen i Windows kontrollpanel (Klocka och region | Region).
Resultatet blir "16:11:39 PM"
Medeltid
| 1 | MsgBox -format (nu (), "Medium Time") |
Visar en medeltid som definieras av din lokal i Windows kontrollpanel. Detta är vanligtvis inställt som 12-timmarsformat med timmar, minuter och sekunder och AM/PM-format.
Resultatet blir '04: 15 PM '
Kort tid
| 1 | MsgBox -format (nu (), "kort tid") |
Visar en medeltid enligt definitionen i Windows kontrollpanel (Klocka och region | Region). Detta är vanligtvis inställt som 24-timmarsformat med timmar och minuter
Resultatet blir '16: 18 '
Faror med att använda Excels fördefinierade format i datum och tider
Användningen av de fördefinierade formaten för datum och tider i Excel VBA är mycket beroende av inställningarna i Windows kontrollpanel och även vad platsen är inställd på
Användare kan enkelt ändra dessa inställningar, och detta kommer att påverka hur dina datum och tider visas i Excel
Om du till exempel utvecklar ett Excel-program som använder fördefinierade format i din VBA-kod kan dessa ändras helt om en användare är i ett annat land eller använder en annan plats än dig. Du kanske märker att kolumnbredder inte passar datumdefinitionen, eller på en användarform är Active X -kontrollen, t.ex. en kombinationsruta (rullgardinsmeny), för smal för att datum och tider ska kunna visas korrekt.
Du måste överväga var publiken är geografiskt när du utvecklar din Excel -applikation
Användardefinierade format för nummer
Det finns ett antal olika parametrar som du kan använda när du definierar din formatsträng:
| Karaktär | Beskrivning |
| Nullsträng | Ingen formatering |
| 0 | Siffra platshållare. Visar en siffra eller en nolla. Om det finns en siffra för den positionen visar den siffran annars visar den 0. Om det finns färre siffror än nollor får du inledande eller bakre nollor. Om det finns fler siffror efter decimalpunkten än det finns nollor, avrundas siffran till antalet decimaler som visas av nollorna. Om det finns fler siffror före decimalpunkten än nollor visas dessa normalt. |
| # | Siffra platshållare. Detta visar en siffra eller ingenting. Det fungerar på samma sätt som nollplatshållaren ovan, förutom att inledande och bakre nollor inte visas. Till exempel skulle 0,75 visas med noll platshållare, men detta skulle vara .75 med # platshållare. |
| . Decimalpunkt. | Endast en tillåten per formatsträng. Detta tecken beror på inställningarna i Windows kontrollpanel. |
| % | Procentuell platshållare. Multiplicerar antalet med 100 och placerar % tecken där det visas i formatsträngen |
| , (kommatecken) | Tusentals separator. Detta används om 0 eller # platshållare används och formatsträngen innehåller ett komma. Ett komma till vänster om decimalen anger runda till närmaste tusen. T.ex. ## 0, Två intilliggande kommatecken till vänster om tusenseparatorn indikerar avrundning till närmaste miljon. T.ex. ## 0 ,, |
| E- E+ | Vetenskapligt format. Detta visar antalet exponentiellt. |
| : (kolon) | Tidsavgränsare - används vid formatering av en tid för att dela timmar, minuter och sekunder. |
| / | Datoseparator - detta används när du anger ett format för ett datum |
| - + £ $ ( ) | Visar en bokstavlig karaktär.För att visa ett annat tecken än det som anges här, föregå det med ett snedstreck (\) |
Användardefinierade format för datum och tider
Dessa tecken kan alla användas i formateringssträngen när du formaterar datum och tider:
| Karaktär | Menande |
| c | Visar datumet som ddddd och tiden som ttttt |
| d | Visa dagen som ett tal utan nollpunkt |
| dd | Visa dagen som ett tal med inledande noll |
| ddd | Visa dagen som en förkortning (sön - lör) |
| dddd | Visa dagens fullständiga namn (söndag - lördag) |
| ddddd | Visa ett datum -serienummer som ett fullständigt datum enligt Short Date i de internationella inställningarna på Windows kontrollpanel |
| dddddd | Visar ett datum serienummer som ett fullständigt datum enligt Long Date i de internationella inställningarna på Windows kontrollpanel. |
| w | Visar veckodagen som ett tal (1 = söndag) |
| ww | Visar årets vecka som ett tal (1-53) |
| m | Visar månaden som ett tal utan inledande nolla |
| mm | Visar månaden som ett tal med ledande nollor |
| mmm | Visar månad som en förkortning (jan-dec) |
| Mmmm | Visar månadens fullständiga namn (januari - december) |
| q | Visar årets kvartal som ett tal (1-4) |
| y | Visar årets dag som ett tal (1-366) |
| åå | Visar året som ett tvåsiffrigt tal |
| åååå | Visar året som fyrsiffrigt tal |
| h | Visar timmen som ett tal utan inledande nolla |
| hh | Visar timmen som ett tal med inledande nolla |
| n | Visar minuten som ett tal utan inledande nolla |
| nn | Visar minuten som ett tal med inledande nolla |
| s | Visar den andra som ett tal utan inledande nolla |
| ss | Visar den andra som ett tal med inledande nolla |
| ttttt | Visa ett tidsserienummer som en fullständig tid. |
| AM PM | Använd en 12-timmars klocka och visa AM eller PM för att indikera före eller efter middagstid. |
| am PM | Använd en 12-timmars klocka och använd am eller pm för att indikera före eller efter middagstid |
| A/P | Använd en 12-timmars klocka och använd A eller P för att indikera före eller efter middagstid |
| a/s | Använd en 12-timmars klocka och använd a eller p för att indikera före eller efter middagstid |
