Számok formázása Excel VBA -ban
A számok mindenféle formátumban megjelennek az Excel munkalapokon. Lehet, hogy már ismeri az Excel előugró ablakát a különböző numerikus formátumok használatához:
A számok formázása megkönnyíti a számok olvasását és megértését. A cellákba bevitt számok Excel alapértelmezett beállítása az „Általános” formátum, ami azt jelenti, hogy a szám pontosan úgy jelenik meg, ahogy beírta.
Ha például kerek számot ír be, pl. 4238, 4238 néven jelenik meg tizedesvessző vagy ezer elválasztó nélkül. Egy tizedes szám, például 9325,89 jelenik meg a tizedesvesszővel és a tizedesjelekkel. Ez azt jelenti, hogy nem fog sorba állni az oszlopban a kerek számokkal, és rendkívül rendetlenül fog kinézni.
Ezenkívül az ezer elválasztó megjelenítése nélkül nehéz látni, hogy mekkora szám valójában az egyes számjegyek számítása nélkül. Millióban vagy tízmillióban van?
Abban a szemszögből, hogy egy felhasználó egy számoszlopot néz le, ez meglehetősen megnehezíti az olvasást és az összehasonlítást.
A VBA -ban pontosan ugyanaz a formátumtartomány érhető el, mint az Excel kezelőfelületén. Ez nemcsak a munkalap egyik cellájában megadott értékre vonatkozik, hanem az üzenetmezőkre, a UserForm vezérlőkre, diagramokra és grafikonokra, valamint a munkalap bal alsó sarkában található Excel állapotsorra is.
A Formátum funkció rendkívül hasznos funkció a VBA -ban megjelenítés szempontjából, de nagyon összetett a számok megjelenítésének rugalmasságában is.
A Formátum funkció használata a VBA -ban
Ha üzenetmezőt jelenít meg, akkor a Formátum funkció közvetlenül használható:
| 1 | MsgBox formátum (1234567.89, "#, ## 0.00") |
Ez nagy számot jelenít meg vesszővel az ezrek elválasztásához és 2 tizedesjegy megjelenítéséhez. Az eredmény 1 234 567,89 lesz. A hash helyett a nullák biztosítják, hogy a tizedesjegyek egész számként 00 -ként jelenjenek meg, és hogy az 1 -nél kisebb számoknál az első nulla legyen
A hashtag szimbólum (#) egy számjegy -helyőrzőt jelent, amely megjelenít egy számjegyet, ha az adott helyen elérhető, vagy semmi.
Használhatja a formázási funkciót az egyes cellák megszólításához, vagy a cellatartományt a formátum megváltoztatásához:
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A10"). NumberFormat = "#, ## 0.00" |
Ez a kód a cellák tartományát (A1 -től A10 -ig) egyéni formátumba állítja, amely vesszővel elválasztja az ezreket, és 2 tizedesjegyet mutat.
Ha ellenőrzi a cellák formátumát az Excel kezelőfelületén, azt fogja látni, hogy új egyéni formátum jött létre.
A számokat az Excel állapotsorában, az Excel ablak bal alsó sarkában is formázhatja:
| 1 | Application.StatusBar = Formátum (1234567.89, "#, ## 0.00") |
Ezt az állapotsorból a következőképpen törölheti:
| 1 | Application.StatusBar = "" |
Formátum karakterlánc létrehozása
Ez a példa minden szám után hozzáadja a „Teljes értékesítés” szöveget, valamint több ezer elválasztót tartalmaz
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "#, ## 0.00" "Teljes értékesítés" "" |
Így fognak kinézni a számaid:
Vegye figyelembe, hogy az A6 cella „SUM” képlettel rendelkezik, és ez tartalmazza a „Teljes értékesítés” szöveget formázás nélkül. Ha a formázást alkalmazza, mint a fenti kódban, akkor nem helyez el egy extra példányt a „Teljes értékesítésből” az A6 cellába
Bár a cellák most alfa -numerikus karaktereket jelenítenek meg, a számok továbbra is numerikus formában vannak jelen. A „SUM” képlet továbbra is működik, mert a háttérben a számértéket használja, nem pedig a szám formázását.
A vessző a formázási karakterláncban adja meg az ezres elválasztót. Ne feledje, hogy ezt csak egyszer kell beírnia a karakterláncba. Ha a szám milliókba vagy milliárdokba ütközik, akkor is 3 tagú csoportokra osztja a számokat
A nulla a formátum karakterláncában (0) egy számjegy helyőrző. Egy számjegyet jelenít meg, ha van, vagy nullát. Pozicionálása nagyon fontos a formázás egységességének biztosítása érdekében
A formázási karakterláncban a hash karakterek (#) semmit nem jelenítenek meg, ha nincs számjegy. Ha azonban van olyan szám, mint .8 (minden tizedesjegy), akkor azt szeretnénk, ha 0,80 -ként jelenne meg, hogy egybeesjen a többi számmal.
Ha egyetlen nullát használ a tizedesponttól balra, és két nullát a tizedesponttól jobbra a formázási karakterláncban, ez megadja a kívánt eredményt (0,80).
Ha csak egy nulla lenne a tizedesponttól jobbra, akkor az eredmény „0,8” lenne, és minden egy tizedesjegyig jelenne meg.
Formázási karakterlánc használata az igazításhoz
Lehet, hogy a tartomány összes tizedes számát a tizedesjegyeikhez igazítva szeretnénk látni, hogy az összes tizedespont közvetlenül egymás alatt legyen, bármennyi tizedesjegy van minden számnál.
Ehhez használhat kérdőjelet (?) A formázási karakterláncban. A „?” Azt jelzi, hogy szám jelenik meg, ha rendelkezésre áll, vagy szóköz
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Ez a következőképpen jeleníti meg a számokat:
Az összes tizedespont most egymás alatt sorakozik. Az A5 cella három tizedesjegyű, és ez rendesen kidobná az igazítást, de a „?” Karakter használata mindent tökéletesen igazít.
Szó szerinti karakterek használata a karakterláncban
Bármilyen szó szerinti karaktert hozzáadhat a formázási karakterlánchoz, ha azt egy fordított perjel (\) előzi meg.
Tegyük fel, hogy egy bizonyos pénznemindikátort szeretne megjeleníteni a számaihoz, amely nem a területi beállításon alapul. A probléma az, hogy ha valutajelzőt használ, az Excel automatikusan hivatkozik a helyi értékre, és a Windows Vezérlőpultján beállított területi beállításnak megfelelőre módosítja. Ennek következményei lehetnek, ha az Excel -alkalmazást más országokban terjesztik, és biztosítani kívánja, hogy a területi beállításoktól függetlenül a pénznemmutató mindig ugyanaz legyen.
Azt is jelezheti, hogy a számok millióban vannak megadva a következő példában:
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
Ez a következő eredményeket hozza létre a munkalapon:
Ha fordított perjelet használ a szó szerinti karakterek megjelenítéséhez, akkor nem kell fordított perjelet használni a karakterlánc minden egyes karakteréhez. Te tudod használni:
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Ez a formázott tartományon belüli összes szám után „malmot” jelenít meg.
A legtöbb karaktert literálként használhatja, de nem fenntartott karaktereket, például 0, #,?
Vesszők használata formátumban
Láthattuk már, hogy vesszőkkel több ezer elválasztót lehet létrehozni nagy számok számára, de más módon is használhatók.
Ha a formázási karakterlánc numerikus részének végén használja őket, akkor ezres skálázóként működnek. Más szavakkal, minden egyes számot ezerrel osztanak el minden alkalommal, amikor vessző van.
A példaadatokban azt mutatószámmal mutatjuk, hogy millió. Ha egy vesszőt szúr be a formázási karakterláncba, akkor ezeket a számokat ezerrel osztva tudjuk megjeleníteni.
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Ez a számokat 1000 -gyel osztva jeleníti meg, bár az eredeti szám továbbra is a háttérben lesz a cellában.
Ha két vesszőt tesz a formázási karakterláncba, akkor a számok millióval lesznek elosztva
| 1 | Táblázatok ("Sheet1"). Tartomány ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
Ez lesz az eredmény csak egy vessző használatával (osztva 1000 -el):
Feltételes formázás létrehozása a Format karakterláncon belül
Feltételes formázást az Excel kezelőfelületén is beállíthat, de ezt a VBA -kódon belül is elvégezheti, ami azt jelenti, hogy a formázási karakterláncot programozással módosíthatja.
A formázási karakterláncban legfeljebb négy szakaszt használhat. Minden szakasz pontosvesszővel van határolva (;). A négy szakasz pozitív, negatív, nulla és szövegnek felel meg
| 1 | Tartomány ("A1: A7"). NumberFormat = "#, ## 0.00; [Piros]-#, ## 0.00; [Zöld]#, ## 0.00; [Kék]" |
Ebben a példában ugyanazokat a kivonatokat, vesszőket és nulla karaktereket használjuk ezer elválasztó és két tizedes pont megadásához, de most minden szakaszhoz különböző szakaszok tartoznak.
Az első rész a pozitív számokra vonatkozik, és formailag nem különbözik attól, amit korábban láthattunk.
A negatív számok második része egy színt (piros) mutat be, amelyet egy szögletes zárójelben tartanak. A formátum megegyezik a pozitív számokéval, azzal a különbséggel, hogy egy mínusz (-) jel került elöl.
A nulla számok harmadik része egy színt (zöld) használ a szögletes zárójelben, a numerikus karakterlánccal megegyező módon, mint a pozitív számoknál.
Az utolsó szakasz a szöveges értékekre vonatkozik, és ehhez csak egy színre (kék) van szükség szögletes zárójelben
Ez a formátum karakterlánc alkalmazásának eredménye:
A formázási karakterláncon belüli feltételekkel tovább léphet. Tegyük fel, hogy minden 10.000 feletti pozitív számot zöldként akart megjeleníteni, és minden más számot pirossal, ezt a formátumú karakterláncot használhatja:
| 1 | Tartomány ("A1: A7"). NumberFormat = "[> = 10000] [Zöld]#, ## 0.00; [<10000] [Piros]#, ## 0.00" |
Ez a formázási karakterlánc szögletes zárójelben tartalmazza a> = 10000 feltételeket, így a zöld csak akkor használható, ha a szám 10000 -nál nagyobb vagy egyenlő
Ez az eredmény:
Töredékek használata a karakterláncokban
A töredékeket nem gyakran használják a táblázatokban, mivel általában egyenértékűek a tizedesekkel, amelyeket mindenki ismer.
Néha azonban célt szolgálnak. Ez a példa dollárt és centet jelenít meg:
| 1 | Tartomány ("A1: A7"). NumberFormat = "#, ## 0" "dollár és" "00/100" "cent" "" |
Ezt az eredményt fogják produkálni:
Ne feledje, hogy annak ellenére, hogy a számok szövegként jelennek meg, számokként továbbra is ott vannak a háttérben, és az összes Excel képlet továbbra is használható rajtuk.
Dátum és idő formátumok
A dátumok valójában számok, és a formátumokat ugyanúgy használhatja, mint a számokat. Ha a dátumot számszerű számként formázza, akkor a tizedesponttól balra egy nagy szám és egy tizedesjegy látható. A tizedesvesszőtől balra lévő szám az 1900. január 1-jétől kezdődő napok számát, a tizedesjegy pedig a 24 órás időt mutatja
| 1 | MsgBox formátum (most (), "dd-mmm-yyyy") |
Ez formázza az aktuális dátumot a '08-július-2020 'megjelenítésére. A hónap „mmm” használata a hónap nevének első három karakterét jeleníti meg. Ha a teljes hónap nevét szeretné megadni, használja az „mmmm” szót
A formázási karakterláncban megadhatja az időpontokat:
| 1 | MsgBox formátum (Most (), "dd-mmm-yyyy hh: mm AM/PM") |
Ekkor a '08-2020. Július 01:25 PM 'jelenik meg
A „hh: mm” órákat és perceket jelent, a AM/PM 12 órás órát használ a 24 órás órával szemben.
Szöveges karaktereket illeszthet be a formázási karakterláncba:
| 1 | MsgBox formátum (Most (), "dd-mmm-yyyy hh: mm AM/PM" "ma" "") |
Ezen jelenik meg a '08-2020. Július 01:25 PM ma '
Szó szerinti karaktereket is használhat fordított perjelekkel elöl ugyanúgy, mint a numerikus formátumú karakterláncok esetében.
Előre meghatározott formátumok
Az Excel számos beépített formátumot tartalmaz számokhoz és dátumokhoz egyaránt, amelyeket a kódban használhat. Ezek elsősorban azt tükrözik, ami a számformázási kezelőfelületen elérhető, bár néhányuk túlmutat azon, ami a felugró ablakban általában rendelkezésre áll. Ezenkívül nem rendelkezik a rugalmassággal a tizedesjegyek számával, illetve azzal kapcsolatban, hogy ezer elválasztót használ -e.
Általános szám
Ez a formátum pontosan úgy jeleníti meg a számot, ahogy van
| 1 | MsgBox formátum (1234567.89, "Általános szám") |
Az eredmény 1234567,89 lesz
Valuta
| 1 | MsgBox formátum (1234567.894, "Valuta") |
Ez a formátum egy pénznem szimbólumot ad a szám elé, pl. $, £ a területtől függően, de a számot 2 tizedesjegyre is formázza, és az ezreket vesszővel választja el.
Az eredmény 1 234 567,89 dollár lesz
Rögzített
| 1 | MsgBox formátum (1234567.894, "Fix") |
Ez a formátum legalább egy számjegyet jelenít meg a bal oldalon, de csak két számjegyet a tizedesponttól jobbra.
Az eredmény 1234567,89 lesz
Alapértelmezett
| 1 | MsgBox formátum (1234567.894, "Standard") |
Ez megjeleníti a számot az ezer elválasztóval együtt, de csak két tizedesjegyig.
Az eredmény 1 234 567,89 lesz
Százalék
| 1 | MsgBox formátum (1234567.894, "Százalék") |
A számot megszorozzuk 100 -zal, és a szám végén százalékos szimbólumot (%) adunk. A formátum 2 tizedesjegyig jelenik meg
Az eredmény 123456789,40% lesz
Tudományos
| 1 | MsgBox formátum (1234567.894, "Tudományos") |
Ez átalakítja a számot exponenciális formátumba
Az eredmény 1,23E+06 lesz
Igen nem
| 1 | MsgBox formátum (1234567.894, "Igen/Nem") |
Ha a szám nulla, akkor a „Nem” jelenik meg, ellenkező esetben az „Igen”
Az eredmény „Igen” lesz
Igaz hamis
| 1 | MsgBox formátum (1234567.894, "Igaz/hamis") |
Ez a „Hamis” értéket jeleníti meg, ha a szám nulla, ellenkező esetben az „Igaz”
Az eredmény "igaz" lesz
Be ki
| 1 | MsgBox formátum (1234567.894, "Be/Ki") |
Ha „nulla”, akkor „Ki” jelenik meg, ellenkező esetben „Be”
Az eredmény „Be” lesz
Általános dátum
| 1 | MsgBox formátum (most (), "Általános dátum") |
Ez a dátumot dátumként és időként jeleníti meg AM/PM jelöléssel. A dátum megjelenítésének módja a Windows Vezérlőpult beállításaitól függ (Óra és régió | Régió). Megjelenhet „mm/dd/yyyy” vagy „dd/mm/yyyy” formátumban
Az eredmény „2020.07.07. 15:48:25” lesz
Hosszú randi
| 1 | MsgBox formátum (most (), "Hosszú dátum") |
Ez egy hosszú dátumot jelenít meg a Windows Vezérlőpultjának megfelelően (Óra és régió | Régió). Vegye figyelembe, hogy nem tartalmazza az időt.
Az eredmény „2022. július 7., kedd” lesz
Közepes dátum
| 1 | MsgBox formátum (most (), "Közepes dátum") |
Ez a Windows Vezérlőpult területi beállításai szerinti rövid dátumbeállításokban meghatározott dátumot jeleníti meg.
Az eredmény '07-július-20 'lesz
Rövid dátum
| 1 | MsgBox formátum (most (), "Rövid dátum") |
Rövid dátumot jelenít meg a Windows Vezérlőpultjának megfelelően (Óra és régió | Régió). A dátum megjelenítésének módja a területtől függ. Megjelenhet „mm/dd/yyyy” vagy „dd/mm/yyyy” formátumban
Az eredmény: 2020.07.07.
Hosszú idő
| 1 | MsgBox formátum (most (), "Long Time") |
Hosszú ideig jeleníti meg a Windows Vezérlőpultjának megfelelően (Óra és régió | Régió).
Az eredmény "4:11:39 PM" lesz
Közepes idő
| 1 | MsgBox formátum (most (), "Közepes idő") |
Közepes időt jelenít meg a Windows Vezérlőpult területi beállításai szerint. Ez általában 12 órás formátumként van beállítva órák, percek és másodpercek, valamint AM/PM formátum használatával.
Az eredmény '04: 15 PM 'lesz
Rövid idő
| 1 | MsgBox formátum (most (), "Rövid idő") |
Közepes időt jelenít meg a Windows Vezérlőpultjának megfelelően (Óra és régió | Régió). Ezt általában 24 órás formátumként állítják be órákkal és percekkel
Az eredmény ’16: 18 ’lesz
Az Excel előre meghatározott formátumának használatának veszélyei dátumokban és időkben
Az Excel VBA dátumokra és időpontokra előre meghatározott formátumok használata nagymértékben függ a Windows Vezérlőpult beállításaitól, és attól is, hogy milyen területi beállításra van beállítva
A felhasználók könnyen módosíthatják ezeket a beállításokat, és ez hatással lesz arra, hogyan jelennek meg a dátumok és idők az Excelben
Például, ha olyan Excel-alkalmazást fejleszt, amely előre meghatározott formátumokat használ a VBA-kódon belül, ezek teljesen megváltozhatnak, ha a felhasználó egy másik országban tartózkodik, vagy más területi beállítást használ. Előfordulhat, hogy az oszlopszélességek nem felelnek meg a dátummeghatározásnak, vagy egy felhasználói űrlapon az Active X vezérlő, például a kombinált doboz (legördülő) vezérlő túl keskeny ahhoz, hogy a dátumok és idők megfelelően megjelenjenek.
Az Excel alkalmazás fejlesztésekor figyelembe kell vennie a közönség földrajzi helyét
Felhasználó által meghatározott formátumok a számokhoz
A formázási karakterlánc meghatározásakor számos különböző paramétert használhat:
| karakter | Leírás |
| Null karakterlánc | Nincs formázás |
| 0 | Digitális helyőrző. Egy számjegyet vagy nullát jelenít meg. Ha van egy számjegy az adott pozícióhoz, akkor a számjegyet jeleníti meg, különben 0 -t mutat. Ha kevesebb számjegy van, mint nulla, akkor az elején vagy a végén lévő nullákat kapja. Ha a tizedesvessző után több számjegy van, mint nulla, akkor a számot a nullákkal jelzett tizedesjegyek számáig kerekítik. Ha több számjegy van a tizedespont előtt, mint nulla, ezek normálisan jelennek meg. |
| # | Digitális helyőrző. Ez megjelenít egy számjegyet vagy semmit. Ugyanúgy működik, mint a fenti nulla helyőrző, azzal a különbséggel, hogy az első és a záró nulla nem jelenik meg. Például 0,75 jelenik meg nulla helyőrző használatával, de ez 0,75 lenne # helyőrző használatával. |
| . Tizedesvessző. | Formátumonként csak egy megengedett. Ez a karakter a Windows Vezérlőpult beállításaitól függ. |
| % | Százalékos helyőrző. Megszorozza a számot 100 -zal, és % karaktert helyez el a formátumban |
| , (vessző) | Ezer elválasztó. Ezt akkor használja, ha 0 vagy # helyőrzőt használ, és a formázási karakterlánc vesszőt tartalmaz. A tizedesponttól balra egy vessző jelzi a kerek ezres pontossággal. Például. ## 0, Az ezer elválasztótól balra lévő két szomszédos vessző a millióra való kerekítést jelzi. Például. ## 0 ,, |
| E-E+ | Tudományos formátum. Ez exponenciálisan jeleníti meg a számot. |
| : (kettőspont) | Időelválasztó - az órák, percek és másodpercek felosztására használt idő formázásakor használatos. |
| / | Dátumelválasztó - ezt a dátum formátumának megadásakor használják |
| - + £ $ ( ) | Szó szerinti karaktert jelenít meg.Az itt felsoroltaktól eltérő karakterek megjelenítéséhez előtte fordított perjel (\) |
Felhasználó által meghatározott formátumok dátumokhoz és időpontokhoz
Ezek a karakterek mind használhatók a formázott karakterláncokban a dátumok és idők formázásakor:
| karakter | Jelentése |
| c | A dátumot ddddd -ként, az időt ttttt -ként jeleníti meg |
| d | A napot számként jelenítse meg nulla előtt |
| dd | Jelenítse meg a napot számként, első nullával |
| ddd | A nap megjelenítése rövidítésként (vasárnap - szo) |
| dddd | A nap teljes nevének megjelenítése (vasárnap - szombat) |
| ddddd | A dátum sorszámát teljes dátumként jelenítse meg a Windows Vezérlőpult Nemzetközi beállításai rövid dátuma szerint |
| dddddd | A dátum sorszámát teljes dátumként jeleníti meg a Windows Vezérlőpult Nemzetközi beállításai Hosszú dátum szerint. |
| w | A hét napját számként jeleníti meg (1 = vasárnap) |
| ww | Az év hetét számként jeleníti meg (1-53) |
| m | A hónapot számként jeleníti meg nulla előtt |
| mm | A hónapot számként jeleníti meg, kezdő nullákkal |
| mmm | A hónap rövidítése (január-december) |
| mmmm | Megjeleníti a hónap teljes nevét (január - december) |
| q | Az év negyedévét számként jeleníti meg (1-4) |
| y | Az év napját számként jeleníti meg (1-366) |
| yy | Az évet kétjegyű számként jeleníti meg |
| yyyy | Az évet négyjegyű számként jeleníti meg |
| h | Az órát számként jeleníti meg nulla előtt |
| hh | Az órát számként jeleníti meg, előtti nullával |
| n | A percet számként jeleníti meg nulla előtt |
| nn | A percet számként jeleníti meg, előtti nullával |
| s | A másodikat számként jeleníti meg nulla előtt |
| ss | A másodikat számként jeleníti meg, első nullával |
| ttttt | Az idő sorszámának megjelenítése teljes időként. |
| DÉLELŐTT DÉLUTÁN | Használjon 12 órás órát, és a délelőtti vagy délutáni megjelenítéssel jelezze dél előtt vagy után. |
| délelőtt délután | Használjon 12 órás órát, és használja az am vagy a pm gombot a dél előtti vagy a délutáni jelzéshez |
| A/P | Használjon 12 órás órát, és az A vagy a P gombbal jelezze dél előtt vagy után |
| a/o | Használjon 12 órás órát, és a vagy a p gombbal jelezze dél előtt vagy után |
