Formátovanie čísel v programe Excel VBA
Čísla majú v pracovných hárkoch programu Excel všetky druhy formátov. Vyskakovacie okno v Exceli na používanie rôznych numerických formátov už možno poznáte:
Formátovanie čísel uľahčuje ich čítanie a porozumenie. Predvolená hodnota Excelu pre čísla zadané do buniek je „všeobecný“ formát, čo znamená, že sa číslo zobrazuje presne tak, ako ste ho zadali.
Ak napríklad zadáte okrúhle číslo napr. 4238, zobrazí sa ako 4238 bez desatinnej čiarky alebo oddeľovačov tisícov. Zobrazí sa desatinné číslo, napríklad 9325,89, s desatinnou čiarkou a desatinnými miestami. To znamená, že sa nezaradí do stĺpca s okrúhlymi číslami a bude pôsobiť mimoriadne chaoticky.
Bez zobrazenia oddeľovačov tisícov je tiež ťažké zistiť, aké veľké číslo v skutočnosti je, bez počítania jednotlivých číslic. Ide o milióny alebo desiatky miliónov?
Z pohľadu užívateľa, ktorý sa pozerá nadol na stĺpec čísel, je preto dosť ťažké čítať a porovnávať.
Vo VBA máte prístup k úplne rovnakému rozsahu formátov, aké máte na klientskom rozhraní Excelu. To platí nielen pre zadanú hodnotu do bunky v hárku, ale aj pre veci, ako sú polia so správami, ovládacie prvky UserForm, grafy a grafy a stavový riadok Excelu v dolnom ľavom rohu pracovného hárka.
Funkcia Format je vo VBA mimoriadne užitočná funkcia z hľadiska prezentácie, ale je tiež veľmi komplexná z hľadiska flexibility, ktorú ponúka zobrazovanie čísel.
Ako používať funkciu formátovania vo VBA
Ak sa zobrazuje okno so správou, funkciu Formát je možné použiť priamo:
| 1 | Formát MsgBox (1234567,89, "#, ## 0,00") |
Zobrazí sa veľké číslo pomocou čiarok na oddelenie tisícov a zobrazenie 2 desatinných miest. Výsledkom bude 1 234 567,89. Nuly namiesto hash zabezpečujú, že desatinné miesta budú zobrazené ako 00 v celých číslach a že pre číslo, ktoré je menšie ako 1, existuje počiatočná nula
Symbol hashtag (#) predstavuje zástupný znak číslice, ktorý zobrazuje číslicu, ak je na danej pozícii k dispozícii, alebo nič.
Na zmenu adresy konkrétnej bunky môžete použiť aj funkciu formátovania alebo formát buniek zmeniť pomocou rozsahu buniek:
| 1 | Tabuľky ("List1"). Rozsah ("A1: A10"). NumberFormat = "#, ## 0,00" |
Tento kód nastaví rozsah buniek (A1 až A10) na vlastný formát, ktorý oddeľuje tisíce čiarkami a zobrazuje 2 desatinné miesta.
Ak skontrolujete formát buniek v klientskom rozhraní programu Excel, zistíte, že bol vytvorený nový vlastný formát.
Môžete tiež formátovať čísla na stavovom riadku programu Excel v dolnom ľavom rohu okna programu Excel:
| 1 | Application.StatusBar = Formát (1234567.89, "#, ## 0,00") |
Toto vymažete zo stavového riadka pomocou:
| 1 | Application.StatusBar = "" |
Vytvorenie reťazca formátu
V tomto prípade bude za každé číslo pridaný text „Celkové tržby“ a tiež oddeľovač tisícov
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "#, ## 0,00" "Celkový predaj" "" |
Takto budú vyzerať vaše čísla:
Bunka A6 má vzorec „SUM“ a bude zahŕňať text „Celkové tržby“ bez nutnosti formátovania. Ak sa použije formátovanie, ako je uvedené vo vyššie uvedenom kóde, do bunky A6 sa nevloží ďalšia inštancia „celkového predaja“.
Aj keď bunky teraz zobrazujú alfanumerické znaky, čísla sú stále prítomné v číselnej forme. Vzorec „SUM“ stále funguje, pretože používa číselnú hodnotu na pozadí, nie spôsob formátovania čísla.
Čiarka v reťazci formátu poskytuje oddeľovač tisícov. Všimnite si toho, že to stačí vložiť do reťazca iba raz. Ak sa číslo zvýši na milióny alebo miliardy, bude stále rozdeľovať číslice do skupín po 3
Nula v reťazci formátu (0) je zástupný znak číslice. Zobrazuje číslicu, ak je tam, alebo nulu. Jeho umiestnenie je veľmi dôležité, aby sa zaistila jednotnosť s formátovaním
Ak vo formáte nie je žiadna číslica, znaky hash (#) nič nezobrazia. Ak však existuje číslo ako .8 (všetky desatinné miesta), chceme, aby sa zobrazovalo ako 0,80, aby sa zhodovalo s ostatnými číslami.
Použitím jednej nuly naľavo od desatinnej čiarky a dvoch núl napravo od desatinnej čiarky vo formátovacom reťazci to poskytne požadovaný výsledok (0,80).
Ak by napravo od desatinnej čiarky bola iba jedna nula, potom by bol výsledok „0,8“ a všetko by sa zobrazilo na jedno desatinné miesto.
Na zarovnanie použite formátovací reťazec
Možno budeme chcieť vidieť všetky desatinné čísla v rozsahu zarovnanom na ich desatinné miesta, aby boli všetky desatinné miesta priamo pod sebou, bez ohľadu na to, koľko miest desatinných miest je na každom čísle.
Na tento účel môžete vo svojom formátovacom reťazci použiť otáznik (?). „?“ Označuje, že je zobrazené číslo, ak je k dispozícii, alebo medzera
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "#, ## 0,00 ??" |
Vaše čísla sa zobrazia nasledovne:
Všetky desatinné miesta sa teraz zoradia pod seba. Bunka A5 má tri desatinné miesta, a tým by bolo zarovnanie normálne vyhodené, ale pomocou znaku „?“ Sa všetko dokonale vyrovná.
Použitie literálnych znakov v rámci formátovacieho reťazca
Do reťazca formátu môžete pridať ľubovoľný doslovný znak tak, že ho zadáte spätnou lomkou (\).
Predpokladajme, že chcete pre svoje čísla zobrazovať konkrétny ukazovateľ meny, ktorý nie je založený na vašom miestnom nastavení. Problém je v tom, že ak použijete ukazovateľ meny, Excel automaticky odkazuje na vašu miestnu hodnotu a zmení ju na hodnotu vhodnú pre miestne nastavenie nastavené na ovládacom paneli systému Windows. To môže mať dôsledky, ak je vaša aplikácia Excel distribuovaná v iných krajinách a chcete zaistiť, aby bez ohľadu na to, kde je národné prostredie, indikátor meny bol vždy rovnaký.
V nasledujúcom príklade môžete tiež uviesť, že čísla sú v miliónoch:
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 \ m" |
Na pracovnom hárku to prinesie nasledujúce výsledky:
Pri použití spätného lomítka na zobrazenie doslovných znakov nemusíte používať spätné lomítko pre každý jednotlivý znak v reťazci. Môžeš použiť:
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 \ mill" |
Po každom čísle v rámci formátovaného rozsahu sa zobrazí „frézovanie“.
Väčšinu znakov môžete použiť ako doslovné, ale nie vyhradené znaky, ako napríklad 0, #,?
Použitie čiarok vo formáte reťazca
Už sme videli, že čiarkami je možné vytvoriť oddeľovače tisícov pre veľký počet, ale dajú sa použiť aj inak.
Ich použitím na konci numerickej časti formátovacieho reťazca pôsobia ako škálovače tisícov. Inými slovami, každé číslo vydelia 1 000 vždy, keď je čiarka.
V ukážkových dátach ich ukazujeme s indikátorom, že sú v miliónoch. Vložením jednej čiarky do formátovacieho reťazca môžeme tieto čísla zobraziť delené 1 000.
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00, \ m" |
Zobrazí sa číslo vydelené 1 000, aj keď pôvodné číslo bude v bunke stále na pozadí.
Ak do reťazca formátu vložíte dve čiarky, čísla budú delené miliónom
| 1 | Tabuľky ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 ,, \ m" |
Toto bude výsledok s použitím iba jednej čiarky (delenej 1 000):
Vytvorenie podmieneného formátovania v rámci formátovacieho reťazca
Podmienené formátovanie môžete nastaviť na prednom konci programu Excel, ale môžete to urobiť aj v kóde VBA, čo znamená, že s reťazcom formátu môžete programovo manipulovať a vykonávať zmeny.
Vo svojom reťazci formátu môžete použiť až štyri sekcie. Každá časť je oddelená bodkočiarkou (;). Štyri časti zodpovedajú kladnému, zápornému, nulovému a textovému zneniu
| 1 | Rozsah ("A1: A7"). NumberFormat = "#, ## 0,00; [červená]-#, ## 0,00; [zelená]#, ## 0,00; [modrá]“ |
V tomto prípade používame rovnaké znaky hash, čiarku a nulu na poskytnutie tisíc oddeľovačov a dvoch desatinných miest, ale teraz máme pre každý typ hodnoty rôzne sekcie.
Prvá časť je pre kladné čísla a nelíši sa od toho, čo sme už predtým videli, pokiaľ ide o formát.
Druhá časť pre záporné čísla predstavuje farbu (červenú), ktorá je umiestnená v pároch hranatých zátvoriek. Formát je rovnaký ako pre kladné čísla s tým rozdielom, že vpredu bol pridaný znak mínus (-).
Tretia časť pre nulové čísla používa farbu (zelenú) v hranatých zátvorkách s číselným reťazcom rovnakú ako pre kladné čísla.
Posledná časť je pre textové hodnoty a všetko, čo potrebuje, je farba (modrá) opäť v hranatých zátvorkách
Toto je výsledok použitia tohto formátovacieho reťazca:
Môžete ísť ďalej s podmienkami v rámci formátovacieho reťazca. Predpokladajme, že chcete zobraziť každé kladné číslo nad 10 000 ako zelené a každé ďalšie číslo ako červené, môžete použiť tento formátovací reťazec:
| 1 | Rozsah ("A1: A7"). NumberFormat = "[> = 10 000] [zelený]#, ## 0,00; [<10 000] [červený]#, ## 0,00" |
Tento formátovací reťazec obsahuje podmienky pre> = 10 000 nastavené v hranatých zátvorkách, takže zelená farba sa použije iba vtedy, ak je číslo väčšie alebo rovné 10 000.
Toto je výsledok:
Použitie zlomkov pri formátovaní reťazcov
V tabuľkách sa zlomky často nepoužívajú, pretože sa zvyčajne rovnajú desatinným miestam, ktoré každý pozná.
Niekedy však slúžia účelu. Tento príklad zobrazí doláre a centy:
| 1 | Rozsah ("A1: A7"). NumberFormat = "#, ## 0" "dolárov a" "00/100" "centov" "" |
Výsledkom bude tento výsledok:
Nezabudnite, že napriek tomu, že sa čísla zobrazujú ako text, stále sú na pozadí ako čísla a dajú sa na nich stále používať všetky vzorce programu Excel.
Formáty dátumu a času
Dátumy sú v skutočnosti čísla a môžete na nich používať formáty rovnako ako pre čísla. Ak formátujete dátum ako číselné číslo, vľavo od desatinnej čiarky sa zobrazí veľké číslo a niekoľko desatinných miest. Číslo naľavo od desatinnej čiarky zobrazuje počet dní od 1. januára 1900 a desatinné miesta ukazujú čas na základe 24 hodín
| 1 | Formát MsgBox (teraz (), „dd-mmm-rrrr“) |
Toto naformátuje aktuálny dátum na „08-júl 2020“. Ak použijete pre mesiac „mmm“, zobrazia sa prvé tri znaky názvu mesiaca. Ak chcete celý názov mesiaca, použite „mmmm“
Do reťazca formátu môžete zahrnúť časy:
| 1 | Formát MsgBox (teraz (), „dd-mmm-rrrr hh: mm AM/PM“) |
Zobrazí sa „08-júl 2020 13:25“
„Hh: mm“ predstavuje hodiny a minúty a AM/PM používa 12-hodinový čas, na rozdiel od 24-hodinového.
Do formátovacieho reťazca môžete začleniť textové znaky:
| 1 | MsgBox Format (Now (), "dd-mmm-rrrr hh: mm AM/PM" "today" "") |
Zobrazí sa „08-júl 2020 dnes 13:25“
Môžete tiež použiť doslovné znaky pomocou spätného lomítka vpredu rovnakým spôsobom ako pre reťazce číselného formátu.
Preddefinované formáty
Excel má množstvo vstavaných formátov pre čísla aj dátumy, ktoré môžete použiť vo svojom kóde. Odrážajú hlavne to, čo je k dispozícii na klientskom rozhraní formátovania čísel, aj keď niektoré z nich presahujú rámec toho, čo je bežne dostupné vo vyskakovacom okne. Tiež nemáte flexibilitu v počte desatinných miest ani v tom, či sa používajú tisíce oddeľovačov.
Všeobecné číslo
Tento formát zobrazí číslo presne tak, ako je
| 1 | Formát MsgBox (1234567.89, „všeobecné číslo“) |
Výsledkom bude 1234567,89
Mena
| 1 | Formát MsgBox (1234567.894, „mena“) |
Tento formát pridá pred číslo symbol meny, napr. $, £ v závislosti od vášho miestneho nastavenia, ale tiež naformátuje číslo na 2 desatinné miesta a oddelí tisíce čiarkami.
Výsledkom bude 1 234 567,89 dolára
Opravené
| 1 | Formát MsgBox (1234567.894, „opravený“) |
Tento formát zobrazuje najmenej jednu číslicu vľavo, ale iba dve číslice napravo od desatinnej čiarky.
Výsledkom bude 1234567,89
Štandardné
| 1 | Formát MsgBox (1234567.894, „štandardný“) |
Zobrazí sa číslo s oddeľovačmi tisíc, ale iba na dve desatinné miesta.
Výsledkom bude 1 234 567,89
Percento
| 1 | Formát MsgBox (1234567.894, „percento“) |
Číslo sa vynásobí 100 a na konci čísla sa pridá symbol percenta (%). Formát sa zobrazuje na 2 desatinné miesta
Výsledok bude 123456789,40%
Vedecký
| 1 | Formát MsgBox (1234567.894, „vedecký“) |
Toto číslo skonvertuje na exponenciálny formát
Výsledkom bude 1,23E+06
Áno nie
| 1 | Formát MsgBox (1234567.894, „áno/nie“) |
Ak je číslo nulové, zobrazí sa „Nie“, v opačnom prípade „Áno“
Výsledkom bude „áno“
Pravda lož
| 1 | Formát MsgBox (1234567.894, „True/False“) |
Ak je číslo nulové, zobrazí sa „False“, v opačnom prípade „True“
Výsledok bude „pravdivý“
Zapnuté/Vypnuté
| 1 | Formát MsgBox (1234567.894, „zapnuté/vypnuté“) |
Ak je číslo nulové, zobrazí sa „Vypnuté“, v opačnom prípade sa zobrazí „Zapnuté“
Výsledkom bude „Zapnuté“
Všeobecný dátum
| 1 | Formát MsgBox (teraz (), „všeobecný dátum“) |
Dátum sa zobrazí ako dátum a čas pomocou zápisu AM/PM. Spôsob zobrazenia dátumu závisí od vašich nastavení v ovládacom paneli systému Windows (hodiny a oblasť | región). Môže sa zobrazovať ako „mm/dd/rrrr“ alebo „dd/mm/rrrr“
Výsledok bude: 7.7.2020 15:48:25
Dlhé rande
| 1 | Formát MsgBox (teraz (), „dlhý dátum“) |
Zobrazí sa dlhý dátum definovaný v ovládacom paneli systému Windows (hodiny a oblasť | región). Upozorňujeme, že nezahŕňa čas.
Výsledkom bude „utorok 7. júla 2022“
Stredný dátum
| 1 | Formát MsgBox (teraz (), „stredný dátum“) |
Zobrazí sa dátum definovaný v nastaveniach krátkeho dátumu podľa miestnych nastavení v ovládacom paneli systému Windows.
Výsledkom bude „07-júl-20“
Krátky dátum
| 1 | Formát MsgBox (teraz (), „krátky dátum“) |
Zobrazí krátky dátum definovaný v ovládacom paneli systému Windows (hodiny a oblasť | oblasť). Spôsob zobrazenia dátumu závisí od vášho miestneho nastavenia. Môže sa zobrazovať ako „mm/dd/rrrr“ alebo „dd/mm/rrrr“
Výsledkom bude „7.7.2020“
Dlho
| 1 | Formát MsgBox (teraz (), „dlho“) |
Zobrazuje dlhý čas podľa definície v ovládacom paneli systému Windows (hodiny a oblasť | oblasť).
Výsledok bude „16:11:39 PM“
Stredný čas
| 1 | Formát MsgBox (teraz (), „stredný čas“) |
Zobrazí stredný čas definovaný vašim miestnym nastavením v ovládacom paneli systému Windows. Obvykle je nastavený ako 12-hodinový formát s použitím hodín, minút a sekúnd a formátu AM/PM.
Výsledok bude „04:15 PM“
Krátka doba
| 1 | Formát MsgBox (teraz (), „krátky čas“) |
Zobrazí stredný čas, ako je definovaný v ovládacom paneli systému Windows (Hodiny a oblasť | Región). Obvykle je nastavený ako 24-hodinový formát s hodinami a minútami
Výsledok bude „16:18“
Nebezpečenstvá pri použití vopred definovaných formátov programu Excel v dátumoch a časoch
Použitie preddefinovaných formátov dátumov a časov v programe Excel VBA veľmi závisí od nastavení v ovládacom paneli systému Windows a tiež od toho, kde je miestne nastavenie nastavené.
Používatelia môžu tieto nastavenia ľahko zmeniť a bude to mať vplyv na to, ako sa v Exceli zobrazujú vaše dátumy a časy
Ak napríklad vyvíjate aplikáciu Excel, ktorá vo vašom kóde VBA používa vopred definované formáty, tieto sa môžu úplne zmeniť, ak je používateľ v inej krajine alebo používa iné národné prostredie. Môžete zistiť, že šírky stĺpcov nevyhovujú definícii dátumu, alebo v používateľskom formulári je ovládací prvok Active X, ako napríklad ovládací prvok so zoznamom (rozbaľovací zoznam), príliš úzky na to, aby sa dátumy a časy mohli správne zobrazovať.
Pri vývoji aplikácie Excel musíte zvážiť, kde sa publikum geograficky nachádza
Užívateľom definované formáty pre čísla
Pri definovaní reťazca formátu môžete použiť niekoľko rôznych parametrov:
| Charakter | Popis |
| Nulový reťazec | Žiadne formátovanie |
| 0 | Zástupný symbol číslice. Zobrazí číslicu alebo nulu. Ak pre danú pozíciu existuje číslica, zobrazí sa číslica, inak sa zobrazí 0. Ak je číslic menej ako núl, dostanete počiatočné alebo koncové nuly. Ak je za desatinnou čiarkou viac číslic ako núl, číslo sa zaokrúhli na počet desatinných miest zobrazený nulami. Ak je pred desatinnou čiarkou viac číslic ako nuly, zobrazia sa normálne. |
| # | Zástupný symbol číslice. Toto zobrazuje číslicu alebo nič. Funguje to rovnako ako nulový zástupný symbol vyššie, okrem toho, že sa nezobrazujú úvodné a koncové nuly. Napríklad 0,75 by sa zobrazilo s použitím nulových zástupných symbolov, ale toto by bolo 0,75 s použitím # zástupných znakov. |
| . Desatinná čiarka. | Na formátovací reťazec je povolený iba jeden. Tento znak závisí od nastavení v ovládacom paneli systému Windows. |
| % | Zástupný symbol percenta. Vynásobí číslo 100 a umiestni % znaku na miesto, kde sa nachádza vo formátovacom reťazci |
| , (čiarka) | Oddeľovač tisícov. Používa sa, ak sa použije 0 alebo # zástupných znakov a reťazec formátu obsahuje čiarku. Jedna čiarka vľavo od desatinnej čiarky označuje zaokrúhlenie na najbližšiu tisícku. Napr. ## 0, Dve susedné čiarky naľavo od oddeľovača tisícov označujú zaokrúhlenie na najbližší milión. Napr. ## 0 ,, |
| E- E+ | Vedecký formát. Toto číslo sa exponenciálne zobrazuje. |
| : (dvojbodka) | Oddeľovač času - používa sa na formátovanie času na hodiny, minúty a sekundy. |
| / | Oddeľovač dátumu - používa sa pri zadávaní formátu dátumu |
| - + £ $ ( ) | Zobrazuje doslovný znak.Ak chcete zobraziť iný znak, ako je tu uvedený, zadajte pred neho obrátené lomítko (\) |
Užívateľom definované formáty dátumov a časov
Tieto znaky je možné vo formátovacom reťazci použiť pri formátovaní dátumov a časov:
| Charakter | Význam |
| c | Dátum zobrazuje ako ddddd a čas ako ttttt |
| d | Zobraziť deň ako číslo bez úvodnej nuly |
| dd | Zobraziť deň ako číslo s počiatočnou nulou |
| ddd | Zobraziť deň ako skratku (Ne - So) |
| dddd | Zobraziť celý názov dňa (nedeľa - sobota) |
| ddddd | Zobrazte sériové číslo dátumu ako kompletný dátum podľa Krátkeho dátumu v medzinárodných nastaveniach ovládacieho panela systému Windows |
| dddddd | Zobrazí sériové číslo dátumu ako kompletný dátum podľa položky Long Date v medzinárodnom nastavení ovládacieho panela systému Windows. |
| w | Zobrazí deň v týždni ako číslo (1 = nedeľa) |
| ww | Zobrazuje týždeň v roku ako číslo (1-53) |
| m | Zobrazí mesiac ako číslo bez úvodnej nuly |
| mm | Zobrazí mesiac ako číslo s úvodnými nulami |
| mmm | Mesiac sa zobrazuje ako skratka (január-december) |
| mmmm | Zobrazuje úplný názov mesiaca (január - december) |
| q | Štvrťrok zobrazuje ako číslo (1-4) |
| r | Zobrazuje deň v roku ako číslo (1-366) |
| rr | Rok sa zobrazuje ako dvojciferné číslo |
| rrrr | Rok sa zobrazuje ako štvorciferné číslo |
| h | Hodinu zobrazuje ako číslo bez úvodnej nuly |
| hh | Hodinu zobrazuje ako číslo s počiatočnou nulou |
| n | Zobrazí minútu ako číslo bez úvodnej nuly |
| nn | Minútu zobrazuje ako číslo s nulou na začiatku |
| s | Druhú zobrazí ako číslo bez úvodnej nuly |
| ss | Druhú zobrazí ako číslo s počiatočnou nulou |
| ttttt | Zobraziť časové sériové číslo ako úplný čas. |
| DOPOLUDNIA POPOLUDNÍ | Na zobrazenie predpoludním alebo poludnie použite 12-hodinový režim a zobrazte ráno alebo večer. |
| dopoludnia popoludní | Použite 12-hodinový formát času a použite dopoludnie alebo popoludní na označenie hodín pred alebo poobede |
| A/P | Použite 12-hodinový formát času a pomocou A alebo P označte pred alebo poobede |
| a/p | Použite 12-hodinový formát času a pomocou a alebo p označte pred alebo poobede |
