Oblikovanje številk v Excelu VBA
Številke so v Excelovih delovnih listih v vseh oblikah. Morda ste že seznanjeni s pojavnim oknom v Excelu za uporabo različnih numeričnih oblik:
Oblikovanje številk olajša branje in razumevanje številk. Privzeta vrednost Excela za številke, vnesene v celice, je oblika »Splošno«, kar pomeni, da je številka prikazana točno tako, kot ste jo vnesli.
Če na primer vnesete okroglo številko, npr. 4238, bo prikazan kot 4238 brez decimalne vejice ali ločevalnikov tisoč. Z decimalno vejico in decimalnimi mesti se prikaže decimalno število, na primer 9325,89. To pomeni, da se ne bo uvrstil v stolpec z okroglimi številkami in bo videti zelo grdo.
Prav tako je brez štetja posameznih števk težko prikazati, kako veliko je število v resnici. Je v milijonih ali na desetine milijonov?
Z vidika uporabnika, ki gleda navzdol po stolpcu številk, je to precej težko brati in primerjati.
V VBA imate dostop do popolnoma istega formata, ki ga imate na sprednjem delu Excela. To ne velja samo za vneseno vrednost v celico na delovnem listu, ampak tudi za stvari, kot so polja s sporočili, kontrolniki uporabniškega obrazca, grafikoni in grafi, ter vrstica stanja Excel v spodnjem levem kotu delovnega lista.
Funkcija Oblika je v predstavitvenem smislu izjemno uporabna funkcija v VBA, vendar je tudi zelo zapletena v smislu prilagodljivosti, ki jo ponuja način prikaza števil.
Kako uporabljati funkcijo oblikovanja v VBA
Če prikazujete polje s sporočilom, lahko funkcijo Oblika uporabite neposredno:
| 1 | Format sporočila (1234567.89, "#, ## 0.00") |
To bo prikazalo veliko število z vejicami za ločevanje tisoč in za 2 decimalni mesti. Rezultat bo 1.234.567,89. Ničle namesto razpršitve zagotavljajo, da bodo decimalke prikazane kot 00 v celih številkah in da je začetna ničla za število, ki je manjše od 1
Simbol hashtag (#) predstavlja namestnik števk, ki prikaže številko, če je na voljo na tem mestu, ali pa nič.
Funkcijo oblikovanja lahko uporabite tudi za naslavljanje posamezne celice ali obseg celic za spremembo oblike:
| 1 | Listi ("List1"). Obseg ("A1: A10"). NumberFormat = "#, ## 0.00" |
Ta koda nastavi obseg celic (A1 do A10) v obliko po meri, ki ločuje tisoče z vejicami in prikazuje 2 decimalni mesti.
Če preverite obliko celic na sprednjem delu Excela, boste ugotovili, da je bila ustvarjena nova oblika po meri.
Številke lahko oblikujete tudi v vrstici stanja programa Excel v spodnjem levem kotu okna Excel:
| 1 | Application.StatusBar = Oblika (1234567.89, "#, ## 0.00") |
To počistite iz vrstice stanja z:
| 1 | Application.StatusBar = "" |
Ustvarjanje niza zapisa
Ta primer bo za vsako številko dodal besedilo „Skupna prodaja“, vključno z ločevalnikom tisoč
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "#, ## 0.00" "Skupna prodaja" "" |
Tako bodo videti vaše številke:
Upoštevajte, da ima celica A6 formulo „SUM“, ki bo vključevala besedilo „Skupne prodaje“ brez oblikovanja. Če se uporabi oblikovanje, kot je v zgornji kodi, v celico A6 ne bo vstavljen dodaten primerek „celotne prodaje“
Čeprav celice zdaj prikazujejo alfanumerične znake, so številke še vedno prisotne v številčni obliki. Formula „SUM“ še vedno deluje, ker uporablja številsko vrednost v ozadju, ne pa način oblikovanja številke.
Vejica v nizu za oblikovanje vsebuje ločilo za tisoče. Upoštevajte, da morate to samo enkrat vnesti v niz. Če število teče v milijone ali milijarde, bo še vedno ločilo števke v skupine po 3
Ničelna vrednost v nizu za obliko (0) je namestnik mesta. Prikaže številko, če obstaja, ali ničlo. Njegov položaj je zelo pomemben za zagotovitev enotnosti pri oblikovanju
V nizu za oblikovanje razpršeni znaki (#) ne prikažejo nič, če ni števke. Če pa obstaja število, kot je .8 (vse decimalke), želimo, da se prikaže kot 0.80, tako da se ujema z drugimi številkami.
Če uporabite eno samo ničlo levo od decimalne vejice in dve ničli desno od decimalne vejice v nizu za oblikovanje, bo to dalo zahtevani rezultat (0,80).
Če bi bila le ena ničla desno od decimalne vejice, bi bil rezultat "0,8" in vse bi bilo prikazano na eno decimalno mesto.
Uporaba nizov formatov za poravnavo
Morda bomo želeli videti vsa decimalna števila v razponu, poravnanem na njihovih decimalnih mestih, tako da bodo vsa decimalna mesta natančno ena pod drugo, ne glede na to, koliko je decimalnih mest na vsaki številki.
To lahko storite z vprašajem (?) V nizu oblike. '?' Pomeni, da je prikazana številka, če je na voljo, ali presledek
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Tako bodo prikazane vaše številke:
Vse decimalne vejice se zdaj vrstijo ena pod drugo. Celica A5 ima tri decimalna mesta, kar bi običajno izravnalo poravnavo, vendar uporaba znaka '?' Vse popolnoma poravna.
Uporaba dobesednih znakov v nizu oblike
V niz za obliko lahko dodate kateri koli dobesedni znak tako, da pred njim postavite poševnico (\).
Recimo, da želite prikazati določen kazalnik valute za svoje številke, ki ne temelji na vašem jeziku. Težava je v tem, da če uporabljate kazalnik valute, se Excel samodejno sklicuje na vaš lokal in ga spremeni v tistega, ki je primeren za področno nastavitev, ki je nastavljena na nadzorni plošči sistema Windows. To bi lahko imelo posledice, če se vaša aplikacija Excel distribuira v drugih državah in želite zagotoviti, da je kazalnik valute, ne glede na lokacijo, vedno enak.
V naslednjem primeru lahko tudi navedete, da so številke v milijonih:
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
To bo na vašem delovnem listu prineslo naslednje rezultate:
Pri uporabi povratne poševnice za prikaz dobesednih znakov vam ni treba uporabiti povratne poševnice za vsak posamezen znak v nizu. Lahko uporabiš:
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
To bo prikazalo "mlin" za vsako številko v oblikovanem obsegu.
Večino znakov lahko uporabite kot dobesedne, ne pa tudi rezerviranih znakov, kot so 0, #,?
Uporaba vej v nizu zapisa
Videli smo že, da se z vejicami lahko ustvari na tisoče ločil za velika števila, lahko pa jih uporabimo tudi na drug način.
Z uporabo na koncu številskega dela niza za oblikovanje delujejo kot skalarji tisoč. Z drugimi besedami, vsakič, ko je vejica, bodo vsako število delili s 1.000.
V vzorčnih podatkih ga prikazujemo z indikatorjem, da je v milijonih. Če v niz za obliko vstavimo eno vejico, lahko prikažemo te številke, deljene s 1.000.
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
To bo pokazalo številke, deljene s 1.000, čeprav bo prvotna številka še vedno v ozadju v celici.
Če v niz za obliko vstavite dve vejici, se bodo številke delile z milijonom
| 1 | Listi ("List1"). Obseg ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
To bo rezultat z uporabo samo ene vejice (deljeno s 1.000):
Ustvarjanje pogojnega oblikovanja v nizu oblike
Na sprednjem delu Excela lahko nastavite pogojno oblikovanje, lahko pa tudi v kodi VBA, kar pomeni, da lahko programsko upravljate niz za oblikovanje, da spremenite.
V nizu oblike lahko uporabite največ štiri razdelke. Vsak odsek je ločen s podpičjem (;). Štirje odseki ustrezajo pozitivnemu, negativnemu, ničelnemu in besedilnemu
| 1 | Razpon ("A1: A7"). NumberFormat = "#, ## 0.00; [Rdeča]-#, ## 0.00; [Zelena]#, ## 0.00; [Modra]" |
V tem primeru uporabljamo iste znake razpršitve, vejice in nič za podajanje tisoč ločil in dveh decimalk, vendar imamo zdaj za vsako vrsto vrednosti različne odseke.
Prvi razdelek je za pozitivna števila in se glede oblike ne razlikuje od tistega, kar smo že videli.
Drugi razdelek za negativna števila uvaja barvo (rdečo), ki je v parih oglatih oklepajev. Oblika je enaka kot pri pozitivnih številkah, le da je spredaj dodan znak minus (-).
Tretji odsek za ničelna števila uporablja barvo (zeleno) v oglatih oklepajih s številskim nizom, ki je enak kot za pozitivna števila.
Zadnji razdelek je za besedilne vrednosti, vse kar potrebuje pa je barva (modra) spet v oglatih oklepajih
To je rezultat uporabe tega niza oblike:
S pogoji v nizu za obliko lahko greste dlje. Recimo, da želite vsako pozitivno število nad 10.000 prikazati kot zeleno, vsako drugo število pa kot rdeče, lahko uporabite ta niz za oblikovanje:
| 1 | Obseg ("A1: A7"). NumberFormat = "[> = 10000] [Zelena]#, ## 0,00; [<10000] [Rdeča]#, ## 0,00" |
Ta niz formata vključuje pogoje za> = 10000, postavljene v oglatih oklepajih, tako da bo zelena uporabljena le, če je število večje ali enako 10000
To je rezultat:
Uporaba ulomkov pri oblikovanju nizov
Delci se v preglednicah ne uporabljajo pogosto, saj so običajno enaki decimalkam, ki jih vsi poznajo.
Vendar včasih služijo svojemu namenu. Ta primer prikazuje dolarje in cente:
| 1 | Razpon ("A1: A7"). NumberFormat = "#, ## 0" "dolarjev in" "00/100" "centov" "" |
To bo rezultat:
Ne pozabite, da so kljub številkam, ki so prikazane kot besedilo, še vedno v ozadju kot številke in na njih je še vedno mogoče uporabiti vse formule Excel.
Oblika datuma in časa
Datumi so dejansko številke in oblike zapisa na njih lahko uporabite na enak način kot za številke. Če datum oblikujete kot številčno številko, boste levo od decimalne vejice videli veliko število in število decimalnih mest. Številka levo od decimalne vejice prikazuje število dni od 1. januarja 1900, decimalna mesta pa čas, ki temelji na 24 urah
| 1 | Format MsgBox (zdaj (), "dd-mmm-llll") |
To bo oblikovalo trenutni datum tako, da bo prikazan '08 -julij 2020 '. Z uporabo 'mmm' za mesec se prikažejo prvi trije znaki imena meseca. Če želite polno ime meseca, uporabite »mmmm«
V niz formata lahko vključite čase:
| 1 | MsgBox Format (Now (), "dd-mmm-yyyy hh: mm AM/PM") |
Na zaslonu bo prikazano '08-julij 2020 13:25 '
„Hh: mm“ predstavlja ure in minute, AM/PM pa uporablja 12-urno uro v nasprotju s 24-urno uro.
Besedilne znake lahko vključite v niz oblike:
| 1 | MsgBox Format (Now (), "dd-mmm-yyyy hh: mm AM/PM" "today" "")) |
To bo prikazalo "08.-julij 2020 01:25 PM"
Dobesedne znake lahko uporabite tudi s poševnico nazaj na enak način kot za nize numerične oblike.
Vnaprej določene oblike
Excel ima številne vgrajene oblike številk in datumov, ki jih lahko uporabite v kodi. Ti večinoma odražajo tisto, kar je na voljo na sprednjem delu oblikovanja številk, čeprav nekateri presegajo tisto, kar je običajno na voljo v pojavnem oknu. Prav tako nimate prožnosti glede števila decimalnih mest ali uporabe tisoč ločil.
Splošna številka
Ta oblika bo prikazala številko točno takšno, kot je
| 1 | Format sporočila (1234567,89, "Splošna številka") |
Rezultat bo 1234567,89
Valuta
| 1 | Format MsgBox (1234567.894, "Valuta") |
Ta oblika bo dodala simbol valute pred številko, npr. $, £, odvisno od vašega jezika, vendar bo tudi oblikoval številko na 2 decimalni mesti in tisoče ločil z vejicami.
Rezultat bo 1.234.567,89 USD
Popravljeno
| 1 | Format MsgBox (1234567.894, "Fiksno") |
Ta oblika prikazuje vsaj eno številko levo, le dve števki pa desno od decimalne vejice.
Rezultat bo 1234567,89
Standardno
| 1 | Format sporočila (1234567.894, "standardno") |
To prikaže število s tisoč ločili, vendar le na dve decimalni mesti.
Rezultat bo 1.234.567,89
Odstotek
| 1 | Format sporočila (1234567.894, "Odstotek") |
Število se pomnoži s 100 in na koncu številke se doda simbol za odstotek (%). Oblika se prikaže na 2 decimalni mesti
Rezultat bo 123456789,40%
Znanstveno
| 1 | Format MsgBox (1234567.894, "Znanstveno") |
To pretvori število v eksponentno obliko
Rezultat bo 1,23E+06
Da/ne
| 1 | Format sporočila (1234567.894, "Da/Ne") |
Ta prikaže "Ne", če je število nič, drugače pa "Da"
Rezultat bo "Da"
Pravilno napačno
| 1 | Format MsgBox (1234567.894, "True/False") |
Ta prikaže "False", če je število nič, sicer pa "True"
Rezultat bo "res"
Prižgi ugasni
| 1 | Format sporočila (1234567.894, "Vklop/Izklop") |
Če je številka nič, se prikaže "izklopljeno", sicer pa "vklopljeno"
Rezultat bo "Vklopljen"
Splošni datum
| 1 | MsgBox Format (Now (), "General Date") |
To bo prikazalo datum kot datum in čas z zapisom AM/PM. Način prikaza datuma je odvisen od vaših nastavitev na nadzorni plošči sistema Windows (Ura in regija | Regija). Lahko se prikaže kot "mm/dd/llll" ali "dd/mm/llll"
Rezultat bo "7.7.2020 15:48:25"
Dolg zmenek
| 1 | MsgBox Format (Now (), "Long Date") |
Prikazal se bo dolg datum, kot je določeno na nadzorni plošči sistema Windows (Ura in regija | Regija). Upoštevajte, da ne vključuje časa.
Rezultat bo "torek, 7. julij 2022"
Srednji datum
| 1 | MsgBox Format (Now (), "Medium Date") |
Ta prikaže datum, kot je opredeljen v nastavitvah kratkega datuma, kot je določen z jezikovnimi nastavitvami na nadzorni plošči sistema Windows.
Rezultat bo '07-julij-20 '
Kratek zmenek
| 1 | Format sporočila (zdaj (), "kratek datum") |
Prikaže kratek datum, kot je opredeljen na nadzorni plošči sistema Windows (Ura in regija | Regija). Kako je datum prikazan, je odvisno od vašega kraja. Lahko se prikaže kot "mm/dd/llll" ali "dd/mm/llll"
Rezultat bo "7.7.2020"
Dolgo časa
| 1 | MsgBox Format (Now (), "Long Time") |
Prikaže dolgo časa, kot je opredeljeno na nadzorni plošči sistema Windows (Ura in regija | Regija).
Rezultat bo "4:11:39 PM"
Srednji čas
| 1 | MsgBox Format (Now (), "Medium Time") |
Prikaže vmesni čas, ki ga določa vaša področna nastavitev na nadzorni plošči sistema Windows. Običajno je to nastavljeno kot 12-urni format z urami, minutami in sekundami ter formatom AM/PM.
Rezultat bo '04: 15 PM '
Kratek čas
| 1 | MsgBox Format (Now (), "Short Time") |
Prikaže srednji čas, kot je določeno na nadzorni plošči sistema Windows (Ura in regija | Regija). Običajno je to nastavljeno kot 24-urni format z urami in minutami
Rezultat bo '16: 18 '
Nevarnosti uporabe Excelovih vnaprej določenih oblik v datumih in časih
Uporaba vnaprej določenih oblik za datume in ure v Excelu VBA je zelo odvisna od nastavitev na nadzorni plošči sistema Windows in tudi od tega, na katero področno nastavitev je nastavljena
Uporabniki lahko preprosto spremenijo te nastavitve, kar bo vplivalo na prikaz datumov in ur v Excelu
Če na primer razvijete Excelovo aplikacijo, ki uporablja vnaprej določene oblike v kodi VBA, se lahko te popolnoma spremenijo, če je uporabnik v drugi državi ali uporablja drugačno področno nastavitev za vas. Morda boste ugotovili, da se širine stolpcev ne ujemajo z definicijo datuma ali pa je na uporabniškem obrazcu kontrolnik Active X, kot je nadzorni gumb (spustni meni), preozek, da bi bili datumi in časi pravilno prikazani.
Pri razvoju aplikacije Excel morate upoštevati, kje je občinstvo geografsko
Uporabniško določene oblike za številke
Pri določanju niza zapisa lahko uporabite več različnih parametrov:
| Znak | Opis |
| Ničelni niz | Brez oblikovanja |
| 0 | Namestnik številk. Prikaže številko ali ničlo. Če za to mesto obstaja številka, potem prikaže števko, sicer pa 0. Če je števk manj kot nič, boste dobili začetne ali zadnje ničle. Če je za decimalno vejico več števk, kot je nič, se število zaokroži na število decimalnih mest, prikazanih z ničlami. Če je pred decimalno vejico več števk kot nič, bodo te običajno prikazane. |
| # | Namestnik številk. To prikaže številko ali nič. Deluje enako kot zgornji nadomestni znak nič, le da začetne in zadnje ničle niso prikazane. Na primer 0,75 bi bilo prikazano z ničelnimi ogradami, to pa bi bilo 0,75 z # ogradami. |
| . Decimalna vejica. | Dovoljen je samo en niz formata. Ta znak je odvisen od nastavitev na nadzorni plošči sistema Windows. |
| % | Odstotni namestnik. Pomnoži številko s 100 in postavi znak %, kjer se pojavi v nizu za oblikovanje |
| , (vejica) | Tisoč ločil. To se uporabi, če se uporabljajo 0 ali # nadomestna mesta in niz za obliko vsebuje vejico. Ena vejica levo od decimalne vejice označuje zaokroževanje na najbližji tisoč. Npr. ## 0, Dve sosednji vejici levo od ločevalnika tisoč označujeta zaokroževanje na najbližji milijon. Npr. ## 0 ,, |
| E- E+ | Znanstveni format. To prikazuje število eksponentno. |
| : (debelo črevo) | Ločilo časa - uporablja se pri oblikovanju časa za delitev ur, minut in sekund. |
| / | Ločilo datuma - to se uporablja pri določanju oblike datuma |
| - + £ $ ( ) | Prikaže dobesedni znak.Če želite prikazati znak, ki ni naveden tukaj, pred njim poševnico (\) |
Uporabniško določene oblike za datume in ure
Pri oblikovanju datumov in ur lahko vse te znake uporabite pri oblikovanju niza:
| Znak | Pomen |
| c | Prikaže datum kot ddddd in čas kot ttttt |
| d | Prikažite dan kot številko brez začetne ničle |
| dd | Prikažite dan kot številko z začetno ničlo |
| ddd | Prikažite dan kot okrajšavo (ned - sobota) |
| dddd | Prikažite polno ime dneva (nedelja - sobota) |
| ddddd | Prikažite serijsko številko datuma kot popoln datum v skladu s kratkim datumom v mednarodnih nastavitvah nadzorne plošče sistema Windows |
| dddddd | Prikaže serijsko številko datuma kot popoln datum glede na Long Date v mednarodnih nastavitvah nadzorne plošče Windows. |
| w | Prikaže dan v tednu kot številko (1 = nedelja) |
| ww | Prikaže teden v letu kot številko (1-53) |
| m | Prikaže mesec kot število brez začetne ničle |
| mm | Prikaže mesec kot številko z vodilnimi ničlami |
| mmm | Prikazuje mesec kot okrajšavo (januar-december) |
| mmmm | Prikaže polno ime meseca (januar - december) |
| q | Prikazuje četrtletje leta kot številko (1-4) |
| y | Prikaže dan v letu kot številko (1-366) |
| yy | Prikaže leto kot dvomestno število |
| llll | Leto prikaže kot štirimestno število |
| h | Prikaže uro kot številko brez začetne ničle |
| hh | Prikaže uro kot številko z začetno ničlo |
| n | Prikaže minuto kot številko brez začetne ničle |
| nn | Prikaže minuto kot številko z začetno ničlo |
| s | Prikaže drugo kot število brez začetne ničle |
| ss | Prikaže drugo kot številko z začetno ničlo |
| ttttt | Prikažite časovno zaporedno številko kot celoten čas. |
| DOPOLDNE POPOLDNE | Uporabite 12-urno uro in prikažite AM ali PM za označevanje pred ali po poldnevu. |
| dopoldne Popoldne | Uporabite 12-urno uro in uporabite am ali pm za označevanje pred ali po poldnevu |
| A/P | Uporabite 12-urno uro in uporabite A ali P za označevanje pred ali po poldnevu |
| a/p | Uporabite 12-urno uro in uporabite a ali p za označevanje pred ali po poldnevu |
