Numbrite vormindamine Excel VBA -s
Exceli töölehtedel on numbreid igasugustes vormingutes. Teil võib olla juba tuttav Exceli hüpikaken erinevate numbrivormingute kasutamiseks:
Numbrite vormindamine muudab numbrid hõlpsamini loetavaks ja arusaadavaks. Lahtritesse sisestatud numbrite Exceli vaikeseade on vorming „Üldine”, mis tähendab, et number kuvatakse täpselt nii, nagu selle sisestasite.
Näiteks kui sisestate ümmarguse numbri nt. 4238, kuvatakse see numbrina 4238 ilma kümnendkoha või tuhandete eraldajateta. Komaarv, näiteks 9325,89, kuvatakse koos kümnendkoha ja kümnendkohaga. See tähendab, et see ei ühti ümmarguste numbritega veerus ja näeb välja väga räpane.
Samuti on tuhandeid eraldajaid näitamata raske üksikute numbrite loendamiseta näha, kui suur number tegelikult on. Kas miljonites või kümnetes miljonites?
Kasutaja seisukohast, kes vaatab numbrite veergu alla, muudab see lugemise ja võrdlemise üsna keeruliseks.
VBA -s on teil juurdepääs täpselt samale vorminguvalikule, mis teil on Exceli esiküljel. See kehtib mitte ainult töölehe lahtrisse sisestatud väärtuse, vaid ka selliste asjade kohta nagu sõnumikastid, UserFormi juhtelemendid, diagrammid ja graafikud ning Exceli olekuriba töölehe vasakus alanurgas.
Funktsioon Vorming on esitlemisel VBA -s äärmiselt kasulik funktsioon, kuid see on ka numbrite kuvamise paindlikkuse poolest väga keeruline.
Kuidas kasutada vormindamisfunktsiooni VBA -s
Kui kuvate sõnumikast, saab funktsiooni Vorming kasutada otse:
| 1 | MsgBoxi vorming (1234567.89, "#, ## 0.00") |
See kuvab tuhandete eraldamiseks komade abil suure arvu ja kahe kümnendkoha täpsusega. Tulemuseks on 1 234 567,89. Nullid räsimärgi asemel tagavad, et kümnendkohad kuvatakse täisarvudena 00 ja et number on väiksem kui 1
Räsimärk (#) tähistab numbrikohatäit, mis kuvab numbri, kui see on selles asendis saadaval, või mitte midagi.
Vormindusfunktsiooni saate kasutada ka üksiku lahtri või vormingute muutmiseks lahtrivahemiku jaoks.
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A10"). NumberFormat = "#, ## 0.00" |
See kood määrab lahtrite vahemiku (A1 kuni A10) kohandatud vormingusse, mis eraldab tuhanded komaga ja näitab 2 kohta pärast koma.
Kui kontrollite Exceli kasutajaliidese lahtrite vormingut, leiate, et uus kohandatud vorming on loodud.
Numbreid saate vormindada ka Exceli olekuribal Exceli akna vasakus alanurgas:
| 1 | Application.StatusBar = Vorming (1234567.89, "#, ## 0.00") |
Selle kustutate olekuribalt, kasutades järgmist.
| 1 | Application.StatusBar = "" |
Vormingstringi loomine
See näide lisab iga numbri järele teksti „Kogumüük” ja sisaldab tuhandeid eraldajaid
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "#, ## 0.00" "Müük kokku" "" |
Teie numbrid näevad välja sellised:
Pange tähele, et lahtril A6 on valem „SUM” ja see sisaldab teksti „Müük kokku” ilma vormindamist nõudmata. Kui rakendatakse vormingut, nagu ülaltoodud koodis, ei lisa see lahtrisse A6 täiendavat eksemplari „Kogumüük”
Kuigi lahtrid kuvavad nüüd tähtnumbrilisi tähemärke, on numbrid endiselt numbrilises vormis. Valem „SUM” töötab endiselt, kuna see kasutab taustal arvväärtust, mitte numbri vormindamist.
Koma vormingustringis annab tuhandete eraldaja. Pange tähele, et peate selle stringi sisestama ainult üks kord. Kui number ulatub miljonitesse või miljarditesse, eraldab see numbrid endiselt 3 -liikmelisteks rühmadeks
Vormingstringi (0) null on kohaline kohatäide. See kuvab numbri, kui see on olemas, või nulli. Selle positsioneerimine on vormindamise ühtluse tagamiseks väga oluline
Vormindustringis ei näita räsimärgid (#) midagi, kui numbrit pole. Kui aga on selline arv nagu .8 (kõik kümnendkohad), tahame, et see kuvataks 0,80, nii et see oleks kooskõlas teiste numbritega.
Kasutades vormingustringis ühte nulli komakohast vasakul ja kahte nulli koma paremal, annab see vajaliku tulemuse (0,80).
Kui komakohast paremal oleks ainult üks null, oleks tulemus ‘0,8’ ja kõik kuvataks ühe kümnendkoha täpsusega.
Vormindusstringi kasutamine joondamiseks
Võib -olla tahame, et kõik kümnendarvud oleksid vahemikus joondatud nende komakohtadega, nii et kõik kümnendkohad oleksid otse üksteise all, kuigi palju kümnendkohti on igal numbril.
Selleks saate oma vormingustringis kasutada küsimärki (?). "?" Näitab, et number kuvatakse, kui see on saadaval, või tühik
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
See kuvab teie numbrid järgmiselt.
Kõik komakohad on nüüd üksteise all. Lahtril A5 on kolm kohta pärast koma ja see viskaks joondamise tavaliselt välja, kuid tähe "?" Kasutamine joondab kõik ideaalselt.
Kirjanduslike märkide kasutamine vormingustringis
Saate oma vormingustringi lisada mis tahes sõnasõnalise märgi, kirjutades selle ette kaldkriipsuga (\).
Oletame, et soovite oma numbrite jaoks näidata teatud valuutaindikaatorit, mis ei põhine teie lokaadil. Probleem on selles, et kui kasutate valuutaindikaatorit, viitab Excel automaatselt teie kohalikule ja muudab selle Windowsi juhtpaneelil määratud lokaadile vastavaks. Sellel võib olla mõju, kui teie Exceli rakendust levitatakse teistes riikides ja soovite tagada, et olenemata asukohast oleks valuutanäitaja alati sama.
Samuti võite järgmises näites märkida, et numbrid on miljonites:
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ m" |
See annab teie töölehele järgmised tulemused:
Kasutades kaldkriipsu sõnasõnaliste märkide kuvamiseks, ei pea stringi iga üksiku märgi puhul tagasilööki kasutama. Sa võid kasutada:
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
See kuvab vormindatud vahemikus iga numbri järel „mill”.
Enamikku märke saate kasutada literaalidena, kuid mitte reserveeritud märke, näiteks 0, #,?
Koma kasutamine vormingustringis
Oleme juba näinud, et komadega saab luua suure hulga jaoks tuhandeid eraldajaid, kuid neid saab kasutada ka muul viisil.
Kasutades neid vormingustringi numbrilise osa lõpus, toimivad nad tuhandete skaleerijatena. Teisisõnu jagavad nad iga numbri 1000 -ga iga kord, kui on koma.
Näidisandmetes näitame seda indikaatoriga, et see on miljonites. Sisestades vormingustringi ühe koma, saame need numbrid jagada 1000 -ga.
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
See näitab numbreid jagatuna 1000 -ga, kuigi algne number jääb lahtrisse siiski taustale.
Kui sisestate vormingustringi kaks koma, jagatakse numbrid miljoniga
| 1 | Arvutustabelid ("leht 1"). Vahemik ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
See on tulemus, kasutades ainult ühte koma (jagage 1000 -ga):
Tingimusliku vorminduse loomine vormingustringis
Tingimusvormingu saate seadistada Exceli esiküljel, kuid saate seda teha ka oma VBA -koodi piires, mis tähendab, et saate muudatuste tegemiseks vormindustringi programmiliselt manipuleerida.
Vormindustringis saate kasutada kuni nelja jaotist. Iga sektsioon on piiratud semikooloniga (;). Neli osa vastavad positiivsele, negatiivsele, nullile ja tekstile
| 1 | Vahemik ("A1: A7"). NumberFormat = "#, ## 0.00; [Punane]-#, ## 0.00; [Roheline]#, ## 0,00; [Sinine]" |
Selles näites kasutame tuhandeid eraldajaid ja kahte kümnendkoha täpsustamiseks samu räsi-, koma- ja nullmärke, kuid nüüd on meil iga väärtuse tüübi jaoks erinevad jaotised.
Esimene jaotis on positiivsete numbrite jaoks ja ei erine formaadi poolest sellest, mida oleme juba varem näinud.
Negatiivsete numbrite teine osa tutvustab värvi (punast), mida hoitakse nurksulgudes. Formaat on sama mis positiivsete numbrite puhul, välja arvatud see, et ees on lisatud miinusmärk (-).
Nullnumbrite kolmas jaotis kasutab nurksulgudes olevat värvi (roheline), mille numbriline string on sama mis positiivsete numbrite puhul.
Viimane osa on tekstiväärtuste jaoks ja selleks on vaja ainult nurksulgudes olevat värvi (sinist)
See on selle vormingustringi rakendamise tulemus:
Vormingstringi tingimustega saate kaugemale minna. Oletame, et soovite näidata positiivset arvu üle 10 000 rohelisena ja iga teist numbrit punasena, kui kasutate seda vormingustringi:
| 1 | Vahemik ("A1: A7"). NumberFormat = "[> = 10000] [Roheline]#, ## 0.00; [<10000] [Punane]#, ## 0.00" |
See vormingustring sisaldab nurksulgudes seatud tingimusi> = 10000, nii et rohelist kasutatakse ainult siis, kui arv on suurem või võrdne 10000
See on tulemus:
Murdude kasutamine stringide vormindamisel
Murdeid arvutustabelites sageli ei kasutata, kuna need võrduvad tavaliselt kümnendkohtadega, mis on kõigile tuttavad.
Kuid mõnikord täidavad need eesmärki. See näide kuvab dollareid ja sente:
| 1 | Vahemik ("A1: A7"). NumberFormat = "#, ## 0" "dollarit ja" "00/100" "senti" "" |
See on tulemus, mis saadakse:
Pidage meeles, et vaatamata sellele, et numbrid kuvatakse tekstina, on need endiselt numbrina taustal ja kõiki Exceli valemeid saab nende peal kasutada.
Kuupäeva ja kellaaja vormingud
Kuupäevad on tegelikult numbrid ja nende vorminguid saate kasutada samamoodi nagu numbrite puhul. Kui vormindate kuupäeva numbrilise numbrina, näete komakohast vasakul suurt arvu ja kümnendkohti. Koma järel vasakul olev number näitab päevade arvu, mis algavad 1. jaanuarist 1900, ja kümnendkohad näitavad kellaaega 24 tunni alusel
| 1 | MsgBoxi vorming (nüüd (), "pp-kk-aaaa") |
See vormindab praeguse kuupäeva näitamiseks '08-juuli-2020 '. Kui kasutate kuu jooksul „mmm”, kuvatakse kuu nime esimesed kolm märki. Kui soovite kuu täielikku nime, kasutage "mmmm"
Vormingustringi saate lisada aegu:
| 1 | MsgBoxi vorming (praegu (), "pp-kk-aaaa hh: mm AM/PM") |
See kuvab '08-juuli 2020 01:25 PM '
„Hh: mm” tähistab tunde ja minuteid ning AM/PM kasutab 24-tunnise kella asemel 12-tunnist kella.
Saate oma vormingustringi lisada tekstimärke:
| 1 | MsgBoxi vorming (praegu (), "pp-kk-aaaa hh: mm AM/PM" "täna" "") |
See kuvab täna '08-juuli 2020 01:25 PM '
Võite kasutada ka sõnasõnalisi märke, kasutades tagasilööki ees samamoodi nagu numbrivormingu stringide puhul.
Eelmääratud vormingud
Excelis on mitu sisseehitatud vormingut nii numbrite kui ka kuupäevade jaoks, mida saate oma koodis kasutada. Need peegeldavad peamiselt numbrivormingu kasutajaliideses saadaolevat, kuigi mõned neist lähevad kaugemale sellest, mis hüpikaknas tavaliselt saadaval on. Samuti pole teil paindlikkust kümnendkohtade arvu ega tuhandete eraldajate osas.
Üldnumber
See vorming kuvab numbri täpselt sellisena, nagu see on
| 1 | MsgBoxi vorming (1234567.89, "Üldnumber") |
Tulemuseks on 1234567,89
Valuuta
| 1 | MsgBoxi vorming (1234567.894, "Valuuta") |
See vorming lisab numbri ette valuutasümboli, nt. $, £ sõltuvalt teie asukohast, kuid see vormindab ka numbri kahe kümnendkoha täpsusega ja eraldab tuhanded komaga.
Tulemuseks on 1 234 567,89 dollarit
Fikseeritud
| 1 | MsgBoxi vorming (1234567.894, "Fikseeritud") |
See vorming kuvab vähemalt ühe numbri vasakul, kuid ainult kaks numbrit koma paremal.
Tulemuseks on 1234567,89
Standard
| 1 | MsgBoxi vorming (1234567.894, "Standard") |
See kuvab arvu tuhande eraldajaga, kuid ainult kahe kümnendkoha täpsusega.
Tulemuseks on 1 234 567,89
Protsent
| 1 | MsgBoxi vorming (1234567.894, "Protsent") |
Arv korrutatakse 100 -ga ja numbri lõppu lisatakse protsendimärk (%). Vorming kuvatakse kahe kümnendkoha täpsusega
Tulemuseks on 123456789,40%
Teaduslik
| 1 | MsgBoxi vorming (1234567.894, "Teaduslik") |
See teisendab numbri eksponentsiaalseks vorminguks
Tulemuseks on 1.23E+06
Jah ei
| 1 | MsgBoxi vorming (1234567.894, "Jah/ei") |
Kui arv on null, kuvatakse see „Ei”, vastasel juhul kuvatakse „Jah”
Tulemuseks on "Jah"
Õige Vale
| 1 | MsgBoxi vorming (1234567.894, "Õige/vale") |
Kui arv on null, kuvatakse see „vale”, vastasel juhul kuvatakse „tõene”
Tulemuseks on "tõsi"
Sisse välja
| 1 | MsgBoxi vorming (1234567.894, "Sisse/välja") |
Kui number on null, kuvatakse see olekus „Väljas”, vastasel juhul kuvatakse „Sees”
Tulemuseks on "Sees"
Üldine kuupäev
| 1 | MsgBoxi vorming (nüüd (), "Üldkuupäev") |
See kuvab kuupäeva kuupäeva ja kellaajana, kasutades märget AM/PM. Kuupäeva kuvamise viis sõltub teie Windowsi juhtpaneeli seadetest (Kell ja piirkond | Piirkond). Seda võidakse kuvada kujul „kk/pp/aaaa“ või „pp/kk/aaaa“
Tulemuseks on „7.7.2020 15:48:25”
Pikk kuupäev
| 1 | MsgBoxi vorming (nüüd (), "pikk kuupäev") |
See kuvab pika kuupäeva, nagu on määratletud Windowsi juhtpaneelil (Kell ja piirkond | Piirkond). Pange tähele, et see ei sisalda aega.
Tulemuseks on teisipäev, 7. juuli 2022
Keskmine kuupäev
| 1 | MsgBoxi vorming (nüüd (), "Keskmine kuupäev") |
See kuvab kuupäeva, mis on määratletud Windowsi juhtpaneeli lokaadi määratletud lühikeste kuupäevaseadete järgi.
Tulemuseks on '07-juuli-20 '
Lühike kuupäev
| 1 | MsgBoxi vorming (nüüd (), "Lühike kuupäev") |
Kuvab lühikese kuupäeva, nagu on määratletud Windowsi juhtpaneelil (Kell ja piirkond | Piirkond). Kuupäeva kuvamise viis sõltub teie asukohast. Seda võidakse kuvada kujul „kk/pp/aaaa“ või „pp/kk/aaaa“
Tulemuseks on "7/7/2020"
Kaua aega
| 1 | MsgBoxi vorming (nüüd (), "Pikk aeg") |
Kuvab pika aja, nagu on määratletud Windowsi juhtpaneelil (Kell ja piirkond | Piirkond).
Tulemuseks on "4:11:39 PM"
Keskmine aeg
| 1 | MsgBoxi vorming (nüüd (), "Keskmine aeg") |
Kuvab Windowsi juhtpaneelil lokaadi määratletud keskmise aja. Tavaliselt on see seatud 12-tunniseks vorminguks, kasutades tunde, minuteid ja sekundeid ning AM/PM vormingut.
Tulemuseks on '04: 15 PM '
Lühike aeg
| 1 | MsgBoxi vorming (nüüd (), "Lühike aeg") |
Kuvab keskmise aja, nagu on määratletud Windowsi juhtpaneelil (Kell ja piirkond | Piirkond). Tavaliselt on see seatud 24-tunniseks vorminguks koos tundide ja minutitega
Tulemuseks on 16:18
Exceli etteantud vormingute kasutamise ohud kuupäevadel ja kellaaegadel
Kuupäevade ja kellaaegade eelnevalt määratletud vormingute kasutamine Excel VBA-s sõltub suuresti Windowsi juhtpaneeli seadetest ja ka lokaadi seadistusest
Kasutajad saavad neid seadeid hõlpsasti muuta ja see mõjutab teie kuupäevade ja kellaaegade kuvamist Excelis
Näiteks kui töötate välja Exceli rakenduse, mis kasutab teie VBA-koodis etteantud vorminguid, võivad need täielikult muutuda, kui kasutaja asub teises riigis või kasutab teist lokaati. Võib juhtuda, et veerulaiused ei sobi kuupäevamääratlusega või kasutajavormil on Active X -juhtelement, näiteks liitkast (rippmenüü), liiga kitsas, et kuupäevi ja kellaaegu õigesti kuvada.
Exceli rakenduse väljatöötamisel peate arvestama sellega, kus publik geograafiliselt asub
Kasutajate määratud vormingud numbritele
Vormindustringi määratlemisel saate kasutada mitmeid erinevaid parameetreid.
| Iseloom | Kirjeldus |
| Null string | Vormindamine puudub |
| 0 | Arvukohatäide. Kuvab numbri või nulli. Kui selle positsiooni jaoks on olemas number, kuvab see numbri, vastasel juhul on see 0. Kui numbreid on vähem kui nulle, saate esimesed või tagumised nullid. Kui pärast komakohta on rohkem numbreid kui nulle, ümardatakse see arv nullidega näidatud kümnendkohtade arvuni. Kui koma ees on rohkem numbreid kui nulle, kuvatakse need tavaliselt. |
| # | Arvukohatäide. See kuvab numbri või mitte midagi. See toimib samamoodi nagu ülaltoodud nullkohatäide, välja arvatud see, et esi- ja lõppnulle ei kuvata. Näiteks kuvatakse 0,75, kasutades null kohatäiteid, kuid see on 0,75, kasutades # kohatäidet. |
| . Koma. | Vormingu stringi kohta on lubatud ainult üks. See märk sõltub Windowsi juhtpaneeli seadetest. |
| % | Kohatäide protsentides. Korrutab arvu 100 -ga ja paigutab % märgi sinna, kus see vormingustringis ilmub |
| , (koma) | Tuhande eraldaja. Seda kasutatakse juhul, kui kasutatakse 0 või # kohatäidet ja vormingustring sisaldab koma. Üks koma komakohast vasakul tähistab ümardamist tuhande täpsusega. Nt. ## 0, Kaks kõrvuti asetsevat koma tuhandest eraldajast vasakul tähistavad ümardamist lähima miljonini. Nt. ## 0 ,, |
| E- E+ | Teaduslik formaat. See kuvab arvu eksponentsiaalselt. |
| : (koolon) | Aja eraldaja - kasutatakse aja vormindamisel tundide, minutite ja sekundite jagamiseks. |
| / | Kuupäeva eraldaja - seda kasutatakse kuupäeva vormingu määramisel |
| - + £ $ ( ) | Kuvab sõnasõnalise iseloomu.Muude kui siin loetletud tähemärkide kuvamiseks pange see ette kaldkriipsuga (\) |
Kuupäevade ja kellaaegade kasutaja määratud vormingud
Neid märke saab kuupäevade ja kellaaegade vormindamisel kasutada vormindamisel:
| Iseloom | Tähendus |
| c | Kuvab kuupäeva kujul ddddd ja kellaaja ttttt |
| d | Näidake päeva numbrina ilma nullita |
| dd | Näidake päeva numbrina, mille ees on null |
| ddd | Kuva päev lühendina (pühapäev - la) |
| dddd | Kuva päeva täielik nimi (pühapäev - laupäev) |
| ddddd | Kuvage kuupäeva seerianumber täieliku kuupäevana vastavalt Windowsi juhtpaneeli rahvusvaheliste sätete lühikesele kuupäevale |
| dddddd | Kuvab kuupäeva seerianumbri täieliku kuupäevana vastavalt Windowsi juhtpaneeli rahvusvaheliste sätete pikale kuupäevale. |
| w | Näitab nädalapäeva numbrina (1 = pühapäev) |
| ww | Kuvab aastanädala numbrina (1–53) |
| m | Kuvab kuu numbrina ilma nullini |
| mm | Kuvab kuu eesolevate nullidega numbrina |
| mmm | Kuvab kuu lühendina (jaanuar-detsember) |
| mmmm | Kuvab kuu täisnime (jaanuar - detsember) |
| q | Kuvab aasta kvartali numbrina (1–4) |
| y | Kuvab aastapäeva numbrina (1-366) |
| yy | Kuvab aasta kahekohalise numbrina |
| aaaa | Kuvab aasta neljakohalise numbrina |
| h | Kuvab tunni arvuna ilma nullini |
| hh | Näitab tunde eesoleva nulliga numbrina |
| n | Kuvab minuti arvuna ilma nullini |
| nn | Kuvab minuti eesoleva nulliga numbrina |
| s | Kuvab teise arvuna ilma nullini |
| ss | Näitab teist numbrit eesmise nulliga |
| ttttt | Kuva aja seerianumber täisajana. |
| AM/PM | Kasutage 12-tunnist kella ja kuvage enne või pärast lõunat näidik AM või PM. |
| am/pm | Kasutage 12-tunnist kella ja kasutage nuppu am või pm, et näidata enne või pärast keskpäeva |
| A/P | Kasutage 12-tunnist kella ja kasutage A või P, et näidata enne või pärast keskpäeva |
| a/lk | Kasutage 12-tunnist kella ja kasutage enne või pärast keskpäeva märkimiseks a või p |
