Formátování čísel v aplikaci Excel VBA
Čísla přicházejí ve všech typech formátů v listech aplikace Excel. Možná už znáte vyskakovací okno v Excelu pro používání různých číselných formátů:
Formátování čísel usnadňuje čtení a porozumění číslům. Výchozí hodnota aplikace Excel pro čísla zadaná do buněk je „Obecný“ formát, což znamená, že se číslo zobrazuje přesně tak, jak jste jej zadali.
Pokud například zadáte kulaté číslo, např. 4238, zobrazí se jako 4238 bez desetinné čárky nebo oddělovačů tisíců. Zobrazí se desetinné číslo, například 9325,89, s desetinnou čárkou a desetinnými místy. To znamená, že se nebude řadit do sloupce s kulatými čísly a bude vypadat extrémně chaoticky.
Bez zobrazení oddělovačů tisíců je také těžké spočítat, jak velké číslo ve skutečnosti je, bez počítání jednotlivých číslic. Jsou to miliony nebo desítky milionů?
Z pohledu uživatele, který se dívá dolů na sloupec čísel, je to docela obtížné číst a porovnávat.
Ve VBA máte přístup k přesně stejné řadě formátů, jaké máte na frontendu Excelu. To platí nejen pro zadanou hodnotu v buňce na listu, ale také pro věci jako pole se zprávami, ovládací prvky UserForm, grafy a grafy a stavový řádek aplikace Excel v dolním levém rohu listu.
Funkce Format je ve VBA velmi užitečnou funkcí při prezentaci, ale je také velmi složitá z hlediska flexibility nabízené při zobrazování čísel.
Jak používat funkci formátování ve VBA
Pokud zobrazujete okno se zprávou, lze funkci Formát použít přímo:
| 1 | Formát MsgBox (1234567.89, "#, ## 0,00") |
Tím se zobrazí velké číslo oddělené tisíci čárkami a dvě desetinná místa. Výsledkem bude 1 234 567,89. Nuly místo hashe zajišťují, že desetinná místa budou zobrazena jako 00 v celých číslech a že u čísla, které je menší než 1, je počáteční nula
Symbol hashtag (#) představuje zástupný znak číslice, který zobrazuje číslici, pokud je na dané pozici k dispozici, nebo jinak nic.
Můžete také použít funkci formátování k adresování jednotlivé buňky nebo rozsah buněk pro změnu formátu:
| 1 | Listy ("List1"). Rozsah ("A1: A10"). NumberFormat = "#, ## 0,00" |
Tento kód nastaví rozsah buněk (A1 až A10) na vlastní formát, který odděluje tisíce čárkami a zobrazuje 2 desetinná místa.
Pokud zkontrolujete formát buněk na frontendu Excelu, zjistíte, že byl vytvořen nový vlastní formát.
Můžete také formátovat čísla na stavovém řádku aplikace Excel v dolním levém rohu okna aplikace Excel:
| 1 | Application.StatusBar = Formát (1234567.89, "#, ## 0,00") |
Toto odstraníte ze stavového řádku pomocí:
| 1 | Application.StatusBar = "" |
Vytvoření formátovacího řetězce
V tomto příkladu bude za každé číslo přidán text „Celkové tržby“ a také oddělovač tisíců
| 1 | Listy („List1“). Rozsah („A1: A6“). NumberFormat = "#, ## 0,00" "Celkový prodej" "" |
Takto budou vypadat vaše čísla:
Buňka A6 má vzorec „SUM“ a bude obsahovat text „Celkové tržby“ bez nutnosti formátování. Pokud je použito formátování, jako ve výše uvedeném kódu, nevloží do buňky A6 další instanci „celkového prodeje“
Ačkoli buňky nyní zobrazují alfanumerické znaky, čísla jsou stále přítomna v číselné formě. Vzorec „SUM“ stále funguje, protože používá číselnou hodnotu na pozadí, nikoli způsob formátování čísla.
Čárka v řetězci formátu poskytuje oddělovač tisíců. Všimněte si toho, že to stačí vložit do řetězce pouze jednou. Pokud číslo dosáhne milionů nebo miliard, bude stále rozdělovat číslice do skupin po 3
Nula v řetězci formátu (0) je zástupný znak číslice. Zobrazuje číslici, pokud je tam, nebo nulu. Jeho umístění je velmi důležité pro zajištění jednotnosti s formátováním
V řetězci formátu znaky hash (#) nezobrazí nic, pokud neexistuje žádná číslice. Pokud však existuje číslo jako .8 (všechna desetinná místa), chceme, aby se zobrazovalo jako 0,80, aby bylo v souladu s ostatními čísly.
Použitím jediné nuly nalevo od desetinné čárky a dvou nul napravo od desetinné čárky ve formátovacím řetězci to poskytne požadovaný výsledek (0,80).
Pokud by napravo od desetinné čárky byla pouze jedna nula, pak by byl výsledek ‘0,8’ a vše by se zobrazilo na jedno desetinné místo.
Použití formátovacího řetězce pro zarovnání
Možná budeme chtít vidět všechna desetinná čísla v rozsahu zarovnaná na jejich desetinná místa, takže všechna desetinná místa jsou přímo pod sebou, ale na každém čísle je mnoho desetinných míst.
K tomu můžete použít otazník (?) V řetězci formátu. ‘?‘ Označuje, že je zobrazeno číslo, je -li k dispozici, nebo mezera
| 1 | Listy ("List1"). Rozsah ("A1: A6"). NumberFormat = "#, ## 0,00 ??" |
Vaše čísla se zobrazí následovně:
Všechny desetinné čárky se nyní seřadí pod sebou. Buňka A5 má tři desetinná místa, a tím by se zarovnání vyhodilo normálně, ale použití znaku „?“ Vše dokonale zarovná.
Použití doslovných znaků v řetězci formátu
Do formátovacího řetězce můžete přidat jakýkoli doslovný znak tak, že jej zadáte zpětným lomítkem (\).
Předpokládejme, že chcete pro svá čísla zobrazit konkrétní měnový indikátor, který není založen na vašem národním prostředí. Problém je v tom, že pokud použijete ukazatel měny, Excel automaticky odkazuje na vaši místní měnu a změní ji na tu, která je vhodná pro národní prostředí, které je nastaveno na ovládacím panelu Windows. To může mít důsledky, pokud je vaše aplikace Excel distribuována do jiných zemí a chcete zajistit, aby bez ohledu na národní prostředí byl indikátor měny vždy stejný.
V následujícím příkladu můžete také uvést, že čísla jsou v milionech:
| 1 | Listy ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 \ m" |
To na vašem listu vytvoří následující výsledky:
Při použití zpětného lomítka k zobrazení doslovných znaků není nutné používat zpětné lomítko pro každý jednotlivý znak v řetězci. Můžeš použít:
| 1 | Listy ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 \ mill" |
Po každém čísle ve formátovaném rozsahu se zobrazí „mlýn“.
Většinu znaků můžete použít jako literály, nikoli však vyhrazené znaky, například 0, #,?
Použití čárky ve formátovacím řetězci
Už jsme viděli, že čárkami lze vytvářet oddělovače tisíců pro velká čísla, ale lze je použít i jiným způsobem.
Jejich použitím na konci číselné části formátovacího řetězce fungují jako škálovače tisíců. Jinými slovy, rozdělí každé číslo o 1 000 pokaždé, když je čárka.
V ukázkových datech je zobrazujeme s indikátorem, že jde o miliony. Vložením jedné čárky do formátovacího řetězce můžeme tato čísla zobrazit děleno 1 000.
| 1 | Listy ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00, \ m" |
Zobrazí se čísla vydělená 1 000, i když původní číslo bude v buňce stále na pozadí.
Pokud do řetězce formátu vložíte dvě čárky, budou čísla dělena milionem
| 1 | Listy ("List1"). Rozsah ("A1: A6"). NumberFormat = "\ $#, ## 0,00 ,, \ m" |
To bude výsledek s použitím pouze jedné čárky (děleno 1 000):
Vytváření podmíněného formátování v řetězci formátu
Můžete nastavit podmíněné formátování na frontendu Excelu, ale můžete to také udělat v kódu VBA, což znamená, že s řetězcem formátu můžete manipulovat programově a provádět změny.
V řetězci formátu můžete použít až čtyři oddíly. Každá sekce je oddělena středníkem (;). Čtyři sekce odpovídají pozitivním, negativním, nulovým a textovým
| 1 | Rozsah ("A1: A7"). NumberFormat = "#, ## 0,00; [červená]-#, ## 0,00; [zelená]#, ## 0,00; [modrá]“ |
V tomto příkladu používáme stejné znaky hash, čárky a nuly k poskytnutí tisíců oddělovačů a dvou desetinných míst, ale nyní máme pro každý typ hodnoty jiné oddíly.
První část je pro kladná čísla a nijak se neliší od toho, co jsme již dříve viděli z hlediska formátu.
Druhá část pro záporná čísla zavádí barvu (červenou), která je držena v páru hranatých závorek. Formát je stejný jako pro kladná čísla s tím rozdílem, že byl vpředu přidán znak mínus (-).
Třetí část pro nulová čísla používá barvu (zelenou) v hranatých závorkách s číselným řetězcem stejnou jako pro kladná čísla.
Poslední část je pro textové hodnoty a vše, co k tomu potřebuje, je opět barva (modrá) v hranatých závorkách
Toto je výsledek použití tohoto formátovacího řetězce:
Můžete jít dále s podmínkami v rámci formátovacího řetězce. Předpokládejme, že byste chtěli zobrazit každé kladné číslo nad 10 000 jako zelené a každé další číslo jako červené, můžete použít tento formátovací řetězec:
| 1 | Rozsah ("A1: A7"). NumberFormat = "[> = 10 000] [zelený]#, ## 0,00; [<10 000] [červený]#, ## 0,00" |
Tento formátovací řetězec obsahuje podmínky pro> = 10 000 v hranatých závorkách, takže zelená bude použita pouze tam, kde je číslo větší nebo rovné 10 000
Toto je výsledek:
Použití zlomků při formátování řetězců
V tabulkách se zlomky často nepoužívají, protože se obvykle rovnají desetinám, které každý zná.
Někdy však splní svůj účel. Tento příklad zobrazí dolary a centy:
| 1 | Rozsah ("A1: A7"). NumberFormat = "#, ## 0" "dolarů a" "00/100" "centů" "" |
Toto je výsledek, který bude vytvořen:
Pamatujte, že i když jsou čísla zobrazena jako text, stále jsou tam na pozadí jako čísla a lze na nich stále používat všechny vzorce Excelu.
Formáty data a času
Data jsou ve skutečnosti čísla a můžete na nich používat formáty stejným způsobem jako pro čísla. Pokud formátujete datum jako číselné číslo, uvidíte nalevo od desetinné čárky velké číslo a několik desetinných míst. Číslo nalevo od desetinné čárky udává počet dní počínaje 1. lednem-1900 a desetinná místa udávají čas na základě 24 hodin
| 1 | Formát MsgBox (nyní (), „dd-mmm-rrrr“) |
Tím se zformátuje aktuální datum na „08-červenec 2020“. Použitím „mmm“ pro měsíc zobrazíte první tři znaky názvu měsíce. Pokud chcete celý název měsíce, použijte „mmmm“
Do řetězce formátu můžete zahrnout časy:
| 1 | Formát MsgBox (nyní (), „dd-mmm-rrrr hh: mm dop./Odp.“) |
Zobrazí se „08-červenec 2020 13:25“
„Hh: mm“ představuje hodiny a minuty a AM/PM používá 12hodinový formát hodin, na rozdíl od 24hodinového.
Do řetězce formátu můžete začlenit textové znaky:
| 1 | Formát MsgBox (nyní (), "dd-mmm-rrrr hh: mm dop./Hod." "Dnes" "") |
Zobrazí se „08-červenec 2020 13:25 dnes“
Můžete také použít doslovné znaky pomocí zpětného lomítka vpředu stejným způsobem jako pro řetězce číselného formátu.
Předdefinované formáty
Excel má řadu předdefinovaných formátů pro čísla i data, která můžete použít v kódu. Ty odrážejí hlavně to, co je k dispozici na frontendu pro formátování čísel, i když některé z nich překračují rámec toho, co je běžně dostupné ve vyskakovacím okně. Také nemáte flexibilitu ohledně počtu desetinných míst ani toho, zda jsou použity oddělovače tisíců.
Obecné číslo
Tento formát zobrazí číslo přesně tak, jak je
| 1 | Formát MsgBox (1234567.89, „obecné číslo“) |
Výsledkem bude 1234567,89
Měna
| 1 | Formát MsgBox (1234567.894, „měna“) |
Tento formát přidá před číslo např. Symbol měny, např. $, £ v závislosti na vašem národním prostředí, ale také zformátuje číslo na 2 desetinná místa a oddělí tisíce čárkami.
Výsledkem bude 1 234 567,89 $
Pevný
| 1 | Formát MsgBox (1234567.894, „opraveno“) |
Tento formát zobrazuje alespoň jednu číslici nalevo, ale pouze dvě číslice napravo od desetinné čárky.
Výsledkem bude 1234567,89
Standard
| 1 | Formát MsgBox (1234567.894, „standardní“) |
Zobrazí se číslo s oddělovači tisíců, ale pouze na dvě desetinná místa.
Výsledkem bude 1 234 567,89
Procento
| 1 | Formát MsgBox (1234567.894, „procento“) |
Číslo se vynásobí 100 a na konci čísla se přidá symbol procenta (%). Formát se zobrazuje na 2 desetinná místa
Výsledkem bude 123456789,40%
Vědecký
| 1 | Formát MsgBox (1234567.894, „vědecký“) |
Tím se číslo převede na exponenciální formát
Výsledkem bude 1,23E+06
Ano ne
| 1 | Formát MsgBox (1234567.894, „Ano/Ne“) |
Pokud je číslo nulové, zobrazí se „Ne“, v opačném případě „Ano“
Výsledkem bude „Ano“
Pravda/nepravda
| 1 | Formát MsgBox (1234567.894, „True/False“) |
Pokud je číslo nula, zobrazí se „False“, v opačném případě se zobrazí „True“
Výsledek bude „pravdivý“
Zapnuto vypnuto
| 1 | Formát MsgBox (1234567.894, „Zapnuto/Vypnuto“) |
Pokud je číslo nulové, zobrazí se „Vypnuto“, v opačném případě „Zapnuto“
Výsledkem bude „Zapnuto“
Obecné datum
| 1 | Formát MsgBox (nyní (), „obecné datum“) |
Datum se zobrazí jako datum a čas pomocí zápisu AM/PM. Způsob zobrazení data závisí na vašem nastavení v Ovládacích panelech Windows (Hodiny a oblast | Region). Může být zobrazen jako „mm/dd/rrrr“ nebo „dd/mm/rrrr“
Výsledkem bude „7. 7. 2020 15:48:25“
Dlouhé rande
| 1 | Formát MsgBox (nyní (), „dlouhé datum“) |
Zobrazí se dlouhé datum, jak je definováno v ovládacím panelu systému Windows (hodiny a oblast | oblast). Všimněte si, že nezahrnuje čas.
Výsledkem bude „úterý 7. července 2022“
Střední datum
| 1 | Formát MsgBox (nyní (), „střední datum“) |
Zobrazí se datum definované v nastavení krátkého data podle národního prostředí v Ovládacích panelech Windows.
Výsledkem bude „07. července-20“
Krátké datum
| 1 | Formát MsgBox (nyní (), „krátké datum“) |
Zobrazí krátké datum, jak je definováno v ovládacím panelu systému Windows (hodiny a oblast | oblast). Způsob zobrazení data závisí na vašem místním nastavení. Může být zobrazen jako „mm/dd/rrrr“ nebo „dd/mm/rrrr“
Výsledkem bude „7.7.2020“
Dlouho
| 1 | MsgBox Format (Now (), "Long Time") |
Zobrazuje dlouhou dobu, jak je definována v Ovládacích panelech Windows (Hodiny a oblast | Region).
Výsledek bude „16:11:39 PM“
Střední čas
| 1 | Formát MsgBox (nyní (), „střední čas“) |
Zobrazí střední čas definovaný vaším národním prostředím v Ovládacích panelech Windows. To je obvykle nastaveno jako 12hodinový formát využívající hodiny, minuty a sekundy a formát AM/PM.
Výsledek bude „16:15“
Krátká doba
| 1 | Formát MsgBox (nyní (), „krátký čas“) |
Zobrazuje střední čas podle definice v Ovládacích panelech Windows (Hodiny a oblast | Region). Obvykle je nastaven jako 24hodinový formát s hodinami a minutami
Výsledek bude „16:18“
Nebezpečí používání předdefinovaných formátů aplikace Excel v datech a časech
Použití předdefinovaných formátů pro data a časy v aplikaci Excel VBA velmi závisí na nastavení v Ovládacích panelech systému Windows a také na tom, kde je národní prostředí nastaveno.
Uživatelé mohou tato nastavení snadno změnit, což bude mít vliv na způsob zobrazení vašich dat a časů v aplikaci Excel
Pokud například vyvíjíte aplikaci Excel, která používá předdefinované formáty v kódu VBA, tyto se mohou zcela změnit, pokud je uživatel v jiné zemi nebo používá jiné národní prostředí. Můžete zjistit, že šířky sloupců neodpovídají definici data, nebo v uživatelském formuláři je ovládací prvek Active X, například ovládací prvek pole se seznamem (rozevírací seznam), příliš úzký na to, aby se data a časy zobrazily správně.
Při vývoji aplikace Excel musíte zvážit, kde je publikum geograficky
Uživatelsky definované formáty pro čísla
Při definování formátovacího řetězce můžete použít řadu různých parametrů:
| Charakter | Popis |
| Nulový řetězec | Žádné formátování |
| 0 | Zástupný znak číslice. Zobrazí číslici nebo nulu. Pokud pro danou pozici existuje číslice, zobrazí se číslice, jinak se zobrazí 0. Pokud je číslic méně než nul, dostanete počáteční nebo koncové nuly. Pokud je za desetinnou čárkou více číslic, než kolik je nul, číslo se zaokrouhlí na počet desetinných míst zobrazených nulami. Pokud je před desetinnou čárkou více číslic než nuly, zobrazí se normálně. |
| # | Zástupný znak číslice. Zobrazí se číslice nebo nic. Funguje stejně jako nulový zástupný symbol výše, kromě toho, že se nezobrazují úvodní a koncové nuly. Například 0,75 by bylo zobrazeno pomocí nulových zástupných symbolů, ale toto by bylo 0,75 pomocí # zástupných symbolů. |
| . Desetinná čárka. | Na řetězec formátu je povolen pouze jeden. Tento znak závisí na nastavení v Ovládacích panelech Windows. |
| % | Zástupný symbol procento. Vynásobí číslo 100 a umístí % znaku tam, kde se objeví ve formátovacím řetězci |
| , (čárka) | Oddělovač tisíců. To se používá, pokud jsou použity 0 nebo # zástupné symboly a formátovací řetězec obsahuje čárku. Jedna čárka nalevo od desetinné čárky označuje zaokrouhlení na celé tisíce. Např. ## 0, Dvě sousední čárky nalevo od oddělovače tisíců označují zaokrouhlení na nejbližší milion. Např. ## 0 ,, |
| E- E+ | Vědecký formát. Toto číslo exponenciálně zobrazuje. |
| : (dvojtečka) | Oddělovač času - používá se při formátování času na dělení hodin, minut a sekund. |
| / | Oddělovač data - používá se při zadávání formátu data |
| - + £ $ ( ) | Zobrazí doslovný znak.Chcete -li zobrazit jiný než zde uvedený znak, vložte před něj zpětné lomítko (\) |
Uživatelsky definované formáty pro data a časy
Při formátování dat a časů lze ve formátovacím řetězci použít všechny tyto znaky:
| Charakter | Význam |
| C | Datum zobrazuje jako ddddd a čas jako ttttt |
| d | Zobrazit den jako číslo bez úvodní nuly |
| dd | Zobrazit den jako číslo s počáteční nulou |
| ddd | Zobrazit den jako zkratku (Ne - So) |
| dddd | Zobrazit celý název dne (neděle - sobota) |
| ddddd | Zobrazte sériové číslo data jako úplné datum podle Krátkého data v Mezinárodním nastavení ovládacího panelu systému Windows |
| dddddd | Zobrazí sériové číslo data jako úplné datum podle Dlouhého data v mezinárodním nastavení ovládacího panelu Windows. |
| w | Zobrazuje den v týdnu jako číslo (1 = neděle) |
| ww | Zobrazuje týden v roce jako číslo (1-53) |
| m | Zobrazí měsíc jako číslo bez úvodní nuly |
| mm | Zobrazí měsíc jako číslo s úvodními nulami |
| mmm | Zobrazuje měsíc jako zkratku (leden-prosinec) |
| mmmm | Zobrazuje úplný název měsíce (leden - prosinec) |
| q | Zobrazuje čtvrtletí roku jako číslo (1-4) |
| y | Zobrazuje den v roce jako číslo (1-366) |
| yy | Rok se zobrazuje jako dvouciferné číslo |
| rrrr | Rok zobrazuje jako čtyřmístné číslo |
| h | Zobrazí hodinu jako číslo bez úvodní nuly |
| hh | Zobrazí hodinu jako číslo s počáteční nulou |
| n | Zobrazí minutu jako číslo bez úvodní nuly |
| nn | Zobrazí minutu jako číslo s počáteční nulou |
| s | Zobrazí dvojku jako číslo bez úvodní nuly |
| ss | Zobrazí druhou jako číslo s počáteční nulou |
| ttttt | Zobrazit časové sériové číslo jako kompletní čas. |
| DOPOLEDNE ODPOLEDNE | Použijte 12hodinové hodiny a zobrazte dopolední nebo odpolední hodiny před nebo po poledni. |
| dopoledne odpoledne | Použijte 12hodinové hodiny a použijte dopolední nebo odpolední hodiny k označení před nebo po poledni |
| A/P | Použijte 12hodinové hodiny a pomocí A nebo P označte před polednem nebo po poledni |
| a/p | Použijte 12hodinové hodiny a pomocí a nebo p označte před polednem nebo po poledni |
