Oblikovanje brojeva u Excelu VBA
Brojevi dolaze u svim vrstama formata na radnim listovima programa Excel. Možda ste već upoznati s skočnim prozorom u Excelu za korištenje različitih numeričkih formata:
Oblikovanje brojeva olakšava čitanje i razumijevanje brojeva. Zadana vrijednost Excel za brojeve unesene u ćelije je format "Općenito", što znači da se broj prikazuje točno onako kako ste ga unijeli.
Na primjer, ako unesete okrugli broj, npr. 4238, prikazat će se kao 4238 bez decimalne točke ili razdjelnika tisuća. Decimalni broj, kao što je 9325,89, bit će prikazan s decimalnom točkom i decimalnim brojevima. To znači da se neće poredati u stupac s okruglim brojevima i da će izgledati iznimno neuredno.
Također, bez prikazivanja razdjelnika tisuća, teško je vidjeti koliko je zapravo velik broj bez brojanja pojedinačnih znamenki. Radi li se o milijunima ili desecima milijuna?
Sa stajališta korisnika koji gleda prema dolje stupac brojeva, to otežava čitanje i usporedbu.
U VBA -u imate pristup potpuno istom rasponu formata koji imate na prednjoj strani programa Excel. To se ne odnosi samo na unijetu vrijednost u ćeliju na radnom listu, već i na stvari poput okvira s porukama, kontrola UserForm -a, grafikona i grafikona te statusne trake programa Excel u donjem lijevom kutu radnog lista.
Funkcija Format iznimno je korisna funkcija u VBA -i u smislu prezentacije, ali je također vrlo složena u smislu fleksibilnosti koja se nudi u načinu prikaza brojeva.
Kako koristiti funkciju oblikovanja u VBA
Ako prikazujete okvir s porukom, tada se funkcija Oblikovanje može koristiti izravno:
| 1 | MsgBox format (1234567,89, "#, ## 0,00") |
Ovo će prikazati veliki broj pomoću zareza za odvajanje tisuća i za prikaz 2 decimalna mjesta. Rezultat će biti 1.234.567,89. Nule umjesto raspršivanja osiguravaju da se decimale prikazuju kao 00 u cijelim brojevima, te da postoji početna nula za broj koji je manji od 1
Simbol hashtaga (#) predstavlja rezervirano mjesto za znamenku koje prikazuje znamenku ako je dostupna na tom mjestu ili ništa.
Također možete koristiti funkciju oblikovanja za adresiranje pojedinačne ćelije ili niz ćelija za promjenu formata:
| 1 | Listovi ("List1"). Raspon ("A1: A10"). NumberFormat = "#, ## 0.00" |
Ovaj će kôd postaviti raspon ćelija (A1 do A10) u prilagođeni format koji razdvaja tisuće zarezima i prikazuje 2 decimalna mjesta.
Ako provjerite format ćelija na prednjem dijelu programa Excel, vidjet ćete da je stvoren novi prilagođeni format.
Brojeve možete oblikovati i na statusnoj traci programa Excel u donjem lijevom kutu prozora programa Excel:
| 1 | Application.StatusBar = Format (1234567.89, "#, ## 0.00") |
To brišete sa statusne trake pomoću:
| 1 | Application.StatusBar = "" |
Stvaranje niza formata
Ovaj će primjer dodati tekst "Ukupna prodaja" nakon svakog broja, kao i razdjelnik tisuća
| 1 | Listovi ("List1"). Raspon ("A1: A6"). NumberFormat = "#, ## 0.00" "Ukupna prodaja" "" |
Ovako će izgledati vaši brojevi:
Imajte na umu da ćelija A6 ima formulu "SUM", a to će uključivati tekst "Ukupne prodaje" bez potrebe za oblikovanjem. Ako se primijeni oblikovanje, kao u gornjem kodu, neće staviti dodatnu instancu "ukupne prodaje" u ćeliju A6
Iako ćelije sada prikazuju alfanumeričke znakove, brojevi su i dalje prisutni u numeričkom obliku. Formula "SUM" i dalje funkcionira jer koristi numeričku vrijednost u pozadini, a ne način na koji je broj oblikovan.
Zarez u nizu formata nudi separator tisuća. Imajte na umu da ovo trebate staviti samo jednom u niz. Ako se broj kreće u milijunima ili milijardama, i dalje će odvojiti znamenke u skupine od 3
Nula u nizu formata (0) je čuvar mjesta. Prikazuje znamenku ako postoji ili nulu. Njegovo je pozicioniranje vrlo važno kako bi se osigurala ujednačenost s oblikovanjem
U nizu formata, hash znakovi (#) neće prikazati ništa ako nema znamenke. Međutim, ako postoji broj poput .8 (sve decimale), želimo da se prikaže kao 0.80 kako bi se poravnao s ostalim brojevima.
Korištenjem jedne nule lijevo od decimalne točke i dvije nule desno od decimalne točke u nizu formata, to će dati traženi rezultat (0,80).
Da je samo jedna nula desno od decimalne točke, rezultat bi bio '0.8' i sve bi se prikazalo na jedno decimalno mjesto.
Korištenje niza formata za poravnanje
Možda bismo htjeli vidjeti sve decimalne brojeve u rasponu poravnane na njihovim decimalnim zarezima, tako da su sve decimalne točke izravno jedna ispod druge, bez obzira na to što na svakom broju ima mnogo decimalnih mjesta.
Za to možete koristiti znak pitanja (?) Unutar niza formata. Znak '?' Označava da se prikazuje broj ako je dostupan ili razmak
| 1 | Listovi ("List1"). Raspon ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Ovo će prikazati vaše brojeve na sljedeći način:
Sve decimalne točke sada se nižu jedna ispod druge. Ćelija A5 ima tri decimalna mjesta i to bi normalno izbacilo poravnanje, ali pomoću znaka '?' Sve se savršeno poravnava.
Korištenje doslovnih znakova unutar niza formata
Možete unijeti bilo koji doslovni znak u niz formata prethodeći mu obrnutu kosu crtu (\).
Pretpostavimo da želite prikazati određeni pokazatelj valute za svoje brojeve koji se ne temelji na vašem lokalitetu. Problem je u tome što ako koristite pokazatelj valute, Excel se automatski poziva na vaš lokal i mijenja ga u onaj koji odgovara lokalnom jeziku koji je postavljen na upravljačkoj ploči sustava Windows. To bi moglo imati posljedice ako se vaša Excel aplikacija distribuira u drugim zemljama i želite osigurati da je, bez obzira na lokalizaciju, pokazatelj valute uvijek isti.
U sljedećem primjeru možda ćete htjeti naznačiti da su brojevi u milijunima:
| 1 | Tablice ("List1"). Raspon ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
To će na vašem radnom listu proizvesti sljedeće rezultate:
Prilikom korištenja obrnute kose crte za prikaz doslovnih znakova, ne morate koristiti obrnutu kosu crtu za svaki pojedinačni znak unutar niza. Možeš koristiti:
| 1 | Listovi ("List1"). Raspon ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Ovo će prikazati "mlin" nakon svakog broja unutar formatiranog raspona.
Većinu znakova možete koristiti kao literale, ali ne i rezervirane znakove poput 0, #,?
Korištenje zareza u nizu formata
Već smo vidjeli da se zarezi mogu koristiti za stvaranje tisuća separatora za velike brojeve, ali se mogu koristiti i na drugi način.
Koristeći ih na kraju numeričkog dijela niza formata, djeluju kao skalatori tisuća. Drugim riječima, svaki će broj podijeliti s 1.000 svaki put kad se pojavi zarez.
U primjerima podataka to prikazujemo s pokazateljem da je u milijunima. Umetanjem jednog zareza u niz formata možemo prikazati te brojeve podijeljene s 1.000.
| 1 | Listovi ("List1"). Raspon ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Ovo će pokazati brojeve podijeljene s 1.000 iako će izvorni broj i dalje biti u pozadini u ćeliji.
Ako u niz formata stavite dvije zareze, brojevi će se podijeliti s milijun
| 1 | Tablice ("List1"). Raspon ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
To će biti rezultat pomoću samo jednog zareza (podijelite s 1.000):
Stvaranje uvjetnog oblikovanja unutar niza formata
Možete postaviti uvjetno oblikovanje na prednjem dijelu programa Excel, ali to možete učiniti i unutar svog VBA koda, što znači da programskim nizom možete manipulirati nizom formata kako biste unijeli promjene.
Možete koristiti najviše četiri odjeljka u nizu formata. Svaki je odjeljak omeđen točkom -zarezom (;). Četiri odjeljka odgovaraju pozitivnom, negativnom, nula i tekstu
| 1 | Raspon ("A1: A7"). NumberFormat = "#, ## 0.00; [Crveno]-#, ## 0.00; [Zeleno]#, ## 0.00; [Plavo]" |
U ovom primjeru koristimo iste znakove raspršivanja, zareza i nule kako bismo osigurali tisuću separatora i dvije decimalne točke, ali sada imamo različite odjeljke za svaku vrstu vrijednosti.
Prvi dio je za pozitivne brojeve i ne razlikuje se od onoga što smo već vidjeli u smislu formata.
Drugi odjeljak za negativne brojeve uvodi boju (crvenu) koja se drži unutar para uglatih zagrada. Format je isti kao za pozitivne brojeve, osim što je ispred dodan znak minus (-).
Treći odjeljak za nulte brojeve koristi boju (zelenu) unutar uglatih zagrada s numeričkim nizom istim kao za pozitivne brojeve.
Posljednji odjeljak odnosi se na tekstualne vrijednosti, a sve što je potrebno je boja (Plava) opet unutar uglatih zagrada
Ovo je rezultat primjene niza ovog formata:
Možete ići dalje s uvjetima unutar niza formata. Pretpostavimo da ste htjeli prikazati svaki pozitivan broj iznad 10.000 kao zelen, a svaki drugi broj kao crven mogli biste koristiti ovaj niz formata:
| 1 | Raspon ("A1: A7"). NumberFormat = "[> = 10000] [Zeleno]#, ## 0,00; [<10000] [Crveno]#, ## 0,00" |
Ovaj niz formata uključuje uvjete za> = 10000 postavljene u uglatim zagradama, tako da će se zelena koristiti samo ako je broj veći ili jednak 10000
Ovo je rezultat:
Korištenje razlomaka u oblikovanju nizova
Razlomci se ne koriste često u proračunskim tablicama jer su obično jednaki decimalnim mjestima koja su svima poznata.
Međutim, ponekad služe svrsi. U ovom primjeru prikazat će se dolari i centi:
| 1 | Raspon ("A1: A7"). NumberFormat = "#, ## 0" "dolara i" "00/100" "centi" "" |
Ovo je rezultat koji će se proizvesti:
Upamtite da unatoč tome što su brojevi prikazani kao tekst, oni su i dalje u pozadini kao brojevi i na njima se mogu koristiti sve formule programa Excel.
Formati datuma i vremena
Datumi su zapravo brojevi i na njima možete koristiti formate na isti način kao i za brojeve. Ako datum oblikujete kao brojčani broj, lijevo od decimalne točke vidjet ćete veliki broj i broj decimalnih mjesta. Broj lijevo od decimalne točke prikazuje broj dana koji počinju od 01. siječnja 1900., a decimalna mjesta pokazuju vrijeme na temelju 24 sata
| 1 | MsgBox format (sada (), "dd-mmm-ggggg") |
Time će se trenutni datum oblikovati tako da prikazuje '08 -srpnja 2020. ' Korištenje 'mmm' za mjesec prikazuje prva tri znaka naziva mjeseca. Ako želite cijeli naziv mjeseca, upotrijebite "mmmm"
U niz formata možete uključiti vremena:
| 1 | MsgBox format (sada (), "dd-mmm-ggggg hh: mm AM/PM") |
Ovo će prikazivati '08 -srpnja 2020. 13:25 '
'Hh: mm' predstavlja sate i minute, a AM/PM koristi 12-satni sat za razliku od 24-satnog sata.
Možete unijeti tekstualne znakove u niz formata:
| 1 | MsgBox format (sada (), "dd-mmm-ggggg hh: mm AM/PM" "danas" "") |
Ovo će prikazati '08 -srpnja 2020. 13:25 danas '
Također možete koristiti doslovne znakove koristeći obrnutu kosu crtu ispred na isti način kao i za nizove numeričkog formata.
Unaprijed definirani formati
Excel ima brojne ugrađene formate za brojeve i datume koje možete koristiti u svom kodu. Oni uglavnom odražavaju ono što je dostupno na prednjem dijelu oblikovanja brojeva, iako neki od njih nadilaze ono što je uobičajeno dostupno u skočnom prozoru. Također, nemate fleksibilnost u pogledu broja decimalnih mjesta niti u tome koriste li se tisuće separatora.
Opći broj
Ovaj format prikazuje broj točno onakav kakav jest
| 1 | MsgBox format (1234567,89, "Opći broj") |
Rezultat će biti 1234567,89
Valuta
| 1 | MsgBox format (1234567.894, "Valuta") |
Ovaj format dodaje simbol valute ispred broja, npr. $, £ ovisno o vašem jeziku, ali će također oblikovati broj na 2 decimalna mjesta i odvojit će tisuće zarezima.
Rezultat će biti 1.234.567,89 USD
Popravljeno
| 1 | MsgBox format (1234567.894, "Fiksno") |
Ovaj format prikazuje najmanje jednu znamenku lijevo, ali samo dvije znamenke desno od decimalne točke.
Rezultat će biti 1234567,89
Standard
| 1 | MsgBox format (1234567.894, "Standard") |
Ovdje se prikazuje broj s tisuću razdjelnika, ali samo na dvije decimale.
Rezultat će biti 1.234.567,89
Postotak
| 1 | MsgBox format (1234567.894, "Postotak") |
Broj se množi sa 100 i dodaje se simbol postotka (%) na kraju broja. Format se prikazuje na 2 decimalna mjesta
Rezultat će biti 123456789,40%
Znanstveni
| 1 | MsgBox format (1234567.894, "Znanstveno") |
Time se broj pretvara u eksponencijalni format
Rezultat će biti 1.23E+06
Da ne
| 1 | MsgBox format (1234567.894, "Da/Ne") |
Ovo prikazuje "Ne" ako je broj nula, u suprotnom prikazuje "Da"
Rezultat će biti "Da"
Točno netočno
| 1 | MsgBox format (1234567.894, "Tačno/Netačno") |
Ovo prikazuje "False" ako je broj nula, u suprotnom prikazuje "True"
Rezultat će biti "Istina"
Uključeno, Isključeno
| 1 | MsgBox format (1234567.894, "Uključeno/Isključeno") |
Ovo prikazuje "Isključeno" ako je broj nula, inače prikazuje "Uključeno"
Rezultat će biti "Uključeno"
Opći datum
| 1 | MsgBox format (sada (), "opći datum") |
Ovo će prikazati datum kao datum i vrijeme pomoću AM/PM zapisa. Način prikaza datuma ovisi o vašim postavkama na upravljačkoj ploči sustava Windows (Sat i regija | regija). Može se prikazati kao "mm/dd/gggg" ili "dd/mm/gggg"
Rezultat će biti ‘7.7.2020 15:48:25 PM’
Dugi spoj
| 1 | MsgBox format (sada (), "dugi datum") |
Ovo će prikazati dugačak datum kako je definirano na Windows upravljačkoj ploči (Sat i regija | regija). Imajte na umu da ne uključuje vrijeme.
Rezultat će biti 'utorak, 7. srpnja 2022.'
Srednji datum
| 1 | MsgBox format (sada (), "srednji datum") |
Ovo prikazuje datum kako je definirano u postavkama kratkog datuma kako je definirano lokalnim jezikom na upravljačkoj ploči sustava Windows.
Rezultat će biti '07 -Jul-20 '
Kratak spoj
| 1 | MsgBox format (sada (), "kratki datum") |
Prikazuje kratki datum kako je definirano na Windows upravljačkoj ploči (Sat i regija | regija). Način prikaza datuma ovisi o vašem području. Može se prikazati kao "mm/dd/gggg" ili "dd/mm/gggg"
Rezultat će biti "7.7.2020."
Dugo vrijeme
| 1 | MsgBox format (sada (), "dugo vremena") |
Prikazuje dugo vrijeme kako je definirano na Windows upravljačkoj ploči (Sat i regija | regija).
Rezultat će biti "4:11:39 PM"
Srednje vrijeme
| 1 | MsgBox format (sada (), "Srednje vrijeme") |
Prikazuje srednje vrijeme prema definiciji vašeg jezika na upravljačkoj ploči sustava Windows. Obično se postavlja kao 12-satni format koristeći sate, minute i sekunde te format AM/PM.
Rezultat će biti '04: 15 popodne '
Kratko vrijeme
| 1 | MsgBox format (sada (), "kratko vrijeme") |
Prikazuje srednje vrijeme kako je definirano na Windows upravljačkoj ploči (Sat i regija | regija). Obično se postavlja kao 24-satni format sa satima i minutama
Rezultat će biti '16: 18 '
Opasnosti korištenja Excel-ovih unaprijed definiranih formata u datumima i vremenima
Korištenje unaprijed definiranih formata za datume i vrijeme u programu Excel VBA uvelike ovisi o postavkama na upravljačkoj ploči sustava Windows i o tome na koje je jezično mjesto postavljeno
Korisnici mogu lako promijeniti te postavke, a to će utjecati na prikaz vaših datuma i vremena u Excelu
Na primjer, ako razvijete Excel aplikaciju koja koristi unaprijed definirane formate unutar vašeg VBA koda, oni se mogu potpuno promijeniti ako je korisnik u drugoj zemlji ili vam koristi drugo mjesto. Možda ćete otkriti da širine stupaca ne odgovaraju definiciji datuma ili je na korisničkom obrascu kontrola Active X, poput kontrole u kombiniranom okviru (padajuća), preuska da bi se datumi i vremena mogli ispravno prikazati.
Morate uzeti u obzir gdje se zemljopisno nalazi publika kada razvijate svoju Excel aplikaciju
Korisnički definirani formati za brojeve
Postoji niz različitih parametara koje možete koristiti pri definiranju niza formata:
| Lik | Opis |
| Nulti niz | Nema oblikovanja |
| 0 | Rezervoar znamenki. Prikazuje znamenku ili nulu. Ako za tu poziciju postoji znamenka, tada se prikazuje znamenka, u protivnom prikazuje 0. Ako ima manje znamenki od nula, dobit ćete vodeće ili zadnje nule. Ako iza decimalnog zareza ima više znamenki nego nula, tada se broj zaokružuje na broj decimalnih mjesta prikazanih nulama. Ako prije decimalne točke ima više znamenki nego nula, one će se normalno prikazati. |
| # | Rezervoar znamenki. Ovo prikazuje znamenku ili ništa. Radi isto kao gore rezervirano mjesto nulte točke, samo što se početne i krajnje nule ne prikazuju. Na primjer, 0,75 bi se prikazalo s nula rezerviranih mjesta, ali to bi bilo 0,75 pomoću # rezerviranih mjesta. |
| . Decimalna točka. | Dopušten je samo jedan niz za format. Ovaj znak ovisi o postavkama na upravljačkoj ploči sustava Windows. |
| % | Postotak rezerviranih mjesta. Množi broj sa 100 i postavlja znak % na mjesto gdje se pojavljuje u nizu formata |
| , (zarez) | Tisuću separatora. To se koristi ako se koriste 0 ili # rezervirana mjesta i format niza sadrži zarez. Jedan zarez lijevo od decimalne točke zaokružuje na najbližu tisuću. Npr. ## 0, Dvije susjedne zareze lijevo od razdjelnika tisuća označavaju zaokruživanje na najbliži milijun. Npr. ## 0 ,, |
| E- E+ | Znanstveni format. Ovo prikazuje broj eksponencijalno. |
| : (dvotočka) | Razdjelnik vremena - koristi se pri oblikovanju vremena za podjelu sati, minuta i sekundi. |
| / | Odvajač datuma - to se koristi kada se navodi format za datum |
| - + £ $ ( ) | Prikazuje doslovni znak.Za prikaz znaka koji nije naveden ovdje, prethodi mu kosa crta (\) |
Korisnički definirani formati datuma i vremena
Svi ti znakovi mogu se koristiti za formatiranje niza prilikom oblikovanja datuma i vremena:
| Lik | Značenje |
| c | Prikazuje datum kao ddddd, a vrijeme kao ttttt |
| d | Prikažite dan kao broj bez vodeće nule |
| dd | Prikažite dan kao broj s početnom nulom |
| ddd | Prikažite dan kao skraćenicu (ned - sub) |
| dddd | Prikažite puno ime dana (nedjelja - subota) |
| ddddd | Prikažite serijski broj datuma kao potpuni datum prema Kratki datum u međunarodnim postavkama upravljačke ploče sustava Windows |
| dddddd | Prikazuje serijski broj datuma kao potpuni datum prema Long Date u međunarodnim postavkama upravljačke ploče sustava Windows. |
| w | Prikazuje dan u tjednu kao broj (1 = nedjelja) |
| ww | Prikazuje tjedan u godini kao broj (1-53) |
| m | Prikazuje mjesec kao broj bez vodeće nule |
| mm | Prikazuje mjesec kao broj s vodećim nulama |
| mmm | Mjesec se prikazuje kao kratica (siječanj-prosinac) |
| mmmm | Prikazuje puni naziv mjeseca (siječanj - prosinac) |
| q | Prikazuje tromjesečje godine kao broj (1-4) |
| y | Prikazuje dan u godini kao broj (1-366) |
| yy | Prikazuje godinu kao dvoznamenkasti broj |
| gggg | Prikazuje godinu kao četveroznamenkasti broj |
| h | Prikazuje sat kao broj bez vodeće nule |
| hh | Prikazuje sat kao broj s početnom nulom |
| n | Prikazuje minute kao broj bez vodeće nule |
| nn | Prikazuje minute kao broj s početnom nulom |
| s | Prikazuje drugi kao broj bez vodeće nule |
| ss | Prikazuje drugi kao broj s početnom nulom |
| ttttt | Prikažite serijski broj vremena kao kompletno vrijeme. |
| PRIJEPODNE POSLJEPODNE | Koristite 12-satni sat i pokažite AM ili PM za označavanje prije ili poslije podne. |
| prijepodne posljepodne | Koristite 12-satni sat i upotrijebite am ili pm za označavanje prije ili poslije podne |
| A/P | Koristite 12-satni sat i upotrijebite A ili P za označavanje prije ili poslije podne |
| a/str | Koristite 12-satni sat i koristite a ili p za označavanje prije ili poslije podne |
