Excel VBAでの数値の書式設定

Excel VBAで数字の書式設定をする

Excelのワークシートでは、数字にはさまざまな表示形式があります。Excelのポップアップウィンドウでさまざまな数値形式を利用できることは、すでにご存じだと思います。

数値の書式設定は、数値を読みやすく、理解しやすくするものです。セルに入力された数値のExcelのデフォルトは「標準」形式であり、入力された数値がそのまま表示されます。

例えば、4238のような整数を入力した場合、小数点や桁区切りのない4238として表示されます。9325.89のような小数点のある数値は、小数点と小数が表示されます。つまり、整数と桁の位置がばらばらで、非常に見栄えが悪くなります。

また、3桁ごとの区切り文字が表示されないと、個々の桁をいちいち数えないと実際にどれくらいの大きさの数字なのかが分かりにくくなります。 数百万なのか、数千万なのか。

実際に数字の列を順にチェックするユーザーからすると、かなり見づらく、比較しづらい状態です。

VBAでも、Excelの表を手で操作するのとまったく同じ種類の書式を利用することができます。これは、ワークシートのセルに入力された値だけでなく、メッセージボックス、UserFormコントロール、チャートやグラフ、ワークシートの左下にあるExcelのステータスバーなどにも当てはまります。 VBAの中でも書式関数は、見た目を整えるという意味では非常に便利な関数である一方、数値の表示方法の柔軟性という面では非常に複雑な関数でもあります。

VBAでFormat関数を使うには

メッセージボックスを表示するのであれば、Format関数を直接使用することができます。

MsgBox Format(1234567.89, "#,##0.00")

このコードは、大きな数字をカンマで区切って表示し、小数点以下2桁を表示するものです。 結果は1,234,567.89となります。 「#」の代わりに0を使うことで、小数は整数で00と表示され、1より小さい数には先頭の0が付くことを保証しています。

ハッシュタグ記号（#）は、その位置に数字がある場合はその数字を表示し、ない場合は何も表示しないプレースホルダーを表します。

また、書式関数を使用して、個々のセル、またはセルの範囲を指定して、書式を変更することができます。

Sheets("Sheet1").Range("A1:A10").NumberFormat = "#,##0.00"

このコードは、セル範囲（A1～A10）を、千の位をカンマで区切り、小数点以下2桁を表示するカスタム書式に設定します。

Excelのメニューからセルの書式を確認すると、新しいカスタム書式が作成されていることが分かります。

また、Excelウィンドウの左下にあるExcelステータスバーでも数値の書式設定を行うことができます。

Application.StatusBar = Format(1234567.89, "#,##0.00")

以下のコードでステータスバーの表示をクリアします。

Application.StatusBar = ""

書式指定文字列を作成する

この例では、各数値の後に「Total Sales」というテキストを追加し、さらに千区切り文字を追加しています。

Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00"" Total Sales"""

このように数字が表示されます。

セルA6には「SUM」式があり、ここにはあらためて書式設定をしなくても「Total Sales」テキストを含むことに注意してください。 上記のコードのように書式が適用されていれば、セルA6の内容そのものに「Total Sales」の文字が追加されることはありません。

セルの表示は英数字となりましたが、セルの値としては、数値のまま存在しています。SUM式は、数値がどのように表示されるかには関係なく、バックグラウンドで数値の処理をします。

書式指定文字列のカンマは、千の区切り文字になります。これを書式指定文字列で指定するのは一箇所だけでよいことに注意してください。数百万から数十億の数値であっても、3桁毎にカンマが挿入されて表示されます。

書式指定文字列のゼロ（0）は、1つの桁を表すプレースホルダーです。数字があればそれを表示し、なければゼロを表示する。この位置は、数値の桁を揃えるために非常に重要です。

書式指定文字列の中で、ハッシュ文字（#）は、その桁の数字がなければ何も表示されません。 しかし、.8（すべて小数）のような数字がある場合、他の数字と並ぶように0.80と表示させたいはずです。書式指定文字列の小数点の左に0を1つ、小数点の右に0を2つ使用することで、必要な結果（0.80）を得ることができます。 もし、小数点の右側にゼロが1つしかなければ、結果は「0.8」となり、すべて小数点以下1桁で表示されます。

書式指定文字列を使って整列させる

ある範囲の小数点以下の数字がすべて、小数点以下の桁数が何桁であっても真下に来るように整列して表示したい場合があります。 書式指定文字列の中にクエスチョンマーク（？）を使ってこれを行うことができます。 クエスチョンマークは、数字があればそれを表示し、なければスペースを表示します。

Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00??"

こうすると、以下のように数字が表示されます。

これで小数点以下がすべて一列に並びました。 セルA5には小数点以下が3つあるので、通常なら整列が崩れてしまいますが、「?」により全ての桁が揃って表示されています。

書式指定文字列でリテラル文字を使用する

書式指定文字列の前にバックスラッシュ(\、多くの日本語フォントでは円記号￥として表示されます。)を付けると、任意のリテラル文字を追加できます。

ロケールに基づかない特定の通貨表示を数値に表示させたいとします。 問題は、通貨インジケータを使用すると、Excelは自動的にあなたのロケールを参照し、Windowsコントロールパネルで設定されているロケールに適したものに変更することです。 これは、Excelアプリケーションを他の国で配布する場合、ロケールが何であれ、通貨表示が常に同じであることを確認したい場合に影響します。

また、次の例のように、数値が百万単位であることを示したい場合があります。

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \m"

これにより、ワークシート上で次のような結果が得られます。

バックスラッシュを使用してリテラル文字を表示する場合、文字列内の個々の文字にバックスラッシュを使用する必要はありません。

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill".

これは、書式指定された範囲内のすべての数値の後に’mill’を表示します。 ほとんどの文字をリテラルとして使用できますが、0、#、?などの予約文字は使用できません。

書式指定文字列でのカンマの使用

カンマは大きな数値の千単位の区切りに使えることはすでに見たとおりですが、別の使い方もあります。 書式指定文字列の数値部分の最後に使用することで、千単位のスケーラーとしても機能します。つまり、コンマがあるたびに、各数値を1,000で割ってくれるのです。 先述の例のデータでは、百万単位であることを示すインジケータを付けて表示していました。書式指定文字列にカンマを1つ挿入することで、それらの数字を1,000で割って表示させることができます。

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00,\m"

これで1,000で割った数字が表示されますが、セルには元の数字がそのまま残っています。 書式指定文字列にカンマを2つ入れると、数値は100万で割られることになります。

Sheets("Sheet1").Range("A1:A6").NumberFormat = " \$#,##0.00,,\m".

次の図は、カンマを1つだけ使った結果になります（1,000で割る）。

書式指定文字列の中に条件付き書式を作成する

条件付き書式はExcelの表の操作で設定することもできますが、VBAコードの中で行うこともできます。つまり、プログラム上で書式指定文字列を操作して変更を加えることができるのです。 書式指定文字列には、最大4つのセクションを使用することができます。 各セクションは、セミコロン（;）で区切られ、正、負、ゼロ、そしてテキストにそれぞれ対応します。

Range("A1:A7").NumberFormat = "#,##0.00;[Red]-#,##0.00;[Green] #,##0.00;[Blue]"

この例では、同じハッシュ、カンマ、ゼロを使用して、千単位の区切り文字と2つの小数点を指定していますが、今度は値の種類ごとに異なるセクションを用意しています。

最初のセクションは正の数に対応しており、書式的にはすでに見てきたものと変わりません。

2番目のセクションは負の数に対応しており、角括弧で囲んで色（赤）を指定しています。マイナス（-）の記号が前に追加されている以外は、正の数と同じ形式です。

3番目のセクションはゼロ用で、角括弧で色（緑）を指定し、書式指定文字列は正の数と同じです。

最後のセクションはテキスト用で、これも角括弧の中に色（青）を指定しているだけです。

上記の書式指定文字列を適用した結果がこれです。

さらに、書式指定文字列の中に条件を設定することもできます。 例えば、10,000以上の正の数を緑、それ以外を赤で表示したい場合は、次のような書式指定文字列を使用します。

Range("A1:A7").NumberFormat = "[>=10000][Green]#,##0.00;[<10000][Red]#,##0.00"

この書式指定文字列は、>=10000の条件を角括弧で囲んでおり、数値が10000以上の場合のみ緑が使用されるようになっています。 これがその結果です。

書式設定文字列で分数を使用する

分数は通常、誰もが知っている小数に相当するため、表計算ソフトではあまり使われません。 しかし、時には必要なこともあります。この例では、ドルとセントを表示します。

Range("A1:A7").NumberFormat = "#,##0 "" dollars and "" 00/100 "" cents """

このような結果が出力されます。

数字がテキストとして表示されていても、Excelの内部処理では数字として存在し、Excelのすべての数式が使えることを忘れないでください。

日付と時刻の書式

日付は数字なので、数字と同じように書式を使うことができます。 日付を数値としてフォーマットすると、小数点の左に大きな数字が表示され、小数点以下の桁数が表示されます。小数点の左側の数字は01-Jan-1900から数えた日数を示し、小数点以下の数字は24時間を基準にした時刻を示します。

MsgBox Format(Now(), "dd-mmm-yyyy")

これは、現在の日付が「08-Jul-2020」のように表示されるように書式設定します。月に ‘mmm’ を使用すると、月名の最初の3文字が表示されます。 完全な月名が必要な場合は、’mmmm’ を使用します。

書式指定文字列に時刻を含めることもできます。

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM")

これは、「08-Jul-2020 01:25 PM」のように表示されます。 hh:mm」は時間と分を表し、AM/PMは24時間制ではなく12時間制を使用します。 書式指定文字列にテキスト文字を取り入れることができます。

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM"" today""")

これは、「08-Jul-2020 01:25 PM today」のように表示されます。 数値の書式指定文字列と同じように、前にバックスラッシュを付けてリテラル文字を使うこともできます。

定義済みの書式

Excelには、数値と日付の両方について、コードで使用できる多くの組み込み書式があります。 これらは主に数値書式のExcelの表の操作で利用可能なものを反映して用意されていますが、中には通常のポップアップウィンドウでは表示されないものも含まれてます。 代わりに、小数点以下の桁数、桁区切りの有無などの自由度はありません。

General Number

この書式は、数字をそのまま表示します。

MsgBox Format(1234567.89, "General Number")

結果は1234567.89となります。

Currency

MsgBox Format(1234567.894, "Currency")

この書式では、数字の前に通貨記号を付けますが、ロケールによっては $ や £や￥となります。 USロケールでの結果は、$1,234,567.89となります。（日本語ロケールでは、￥1,234,568となります。）

Fixed

MsgBox Format(1234567.894, "Fixed")

この書式では、少なくとも左側に1桁、小数点の右側に2桁だけ表示されます。 結果は1234567.89となります。

Standard

MsgBox Format(1234567.894, "Standard")

これは、小数点以下2桁までしか表示されませんが、千の区切り文字で数値を表示します。 結果は1,234,567.89となります。

Percent

MsgBox Format(1234567.894, "Percent")

数値を100倍して、数値の末尾にパーセント記号（%）を付加します。 フォーマットは小数点以下2桁まで表示されます。 結果は 123456789.40% となります。

Scientific

MsgBox Format(1234567.894, "Scientific")

これは、数値を指数表示に変換します。 結果は 1.23E+06 となります。

Yes/No

MsgBox Format(1234567.894, "Yes/No")

これは数値がゼロの場合は Yes、それ以外の場合は No と表示します。 結果は Yes になります。

True/False

MsgBox Format(1234567.894, "True/False")

数値が0の場合は False、それ以外の場合は True を表示します。 結果は True になります。

On/Off

MsgBox Format(1234567.894, "On/Off")

数値が0の場合は Off、それ以外の場合は Onと表示されます。 結果は ‘オン’ になります

General Date

MsgBox Format(Now(), "General Date")

これにより、日付がAM/PM表記で日付と時刻が表示されます。 日付の表示方法は、Windowsのコントロールパネル（時刻と言語  > 地域）の設定に依存します。ロケールによってはmm/dd/yyyy’ または ‘dd/mm/yyyy’ のように表示されるかもしれません。 日本語ロケールでは、「2020/7/7 15:48:25」のように表示されます。

Long Date

MsgBox Format(Now(), "Long Date")

これは、Windowsのコントロールパネル（時刻と言語  > 地域）で定義された長い形式で日付を表示します。 時刻は含まれないことに注意してください。 標準的な日本語ロケールでは、「2020年7月7日」となります。

Medium Date

MsgBox Format(Now(), "Medium Date")

これは、アプリケーションの言語設定における簡略形式で日付を表示します。 標準的な日本語ロケールでは「20-07-07」となります。

Short Date

MsgBox Format(Now(), "Short Date")

Windowsのコントロールパネル（時刻と言語  > 地域）で定義された短い形式で日付を表示します。日付の表示方法は、ロケールに依存します。mm/dd/yyyy’ または ‘dd/mm/yyyy’ として表示されるかもしれません。 標準的な日本語ロケールでは「2020/7/7」のようになります。

Long Time

MsgBox Format(Now(), "Long Time")

Windowsのコントロールパネル（時刻と言語  > 地域）で定義された長い形式で時刻を表示します。 結果は「04:11:39」のようになります。

Medium Time

MsgBox Format(Now(), "Medium Time")

Windowsのコントロールパネルでロケールによって定義された中間の時刻を表示します。これは通常、時間、分を使った12時間制とAM/PM形式で設定されています。 結果は、「04:15 午後」となります。

Short Time

MsgBox Format(Now(), "Short Time")

Windowsのコントロールパネル（時刻と言語  > 地域）で定義されている短い形式で時刻を表示します。これは、通常、時間と分を含む24時間制として設定されます。 結果は「16:18」となります。

Excelの日付と時刻の定義済み書式を使用する危険性

Excel VBAで日付と時刻の定義済みフォーマットを使用するかどうかは、Windowsコントロールパネルの設定と、ロケールの設定に大きく依存します。 ユーザーはこれらの設定を簡単に変更することができ、Excelでの日付と時刻の表示方法に影響を与えます。 例えば、VBAコードであらかじめ定義されたフォーマットを使用するExcelアプリケーションを開発する場合、ユーザーが別の国にいたり、自分と異なるロケールを使用していたりすると、これらのフォーマットが完全に変更されることがあります。 また、列の幅が日付の定義に合わない、あるいはユーザーフォームのコンボボックス（ドロップダウン）コントロールなどのActive Xコントロールの幅が狭すぎて日付や時刻が正しく表示されない、などの問題が発生する可能性があります。 Excelアプリケーションを開発する際には、視聴者が地理的にどこにいるのかを考慮する必要があります。

数値のユーザー定義書式

書式指定文字列を定義する際に使用できるパラメータは、いくつかあります。

文字	説明
Null文字列	書式なし
0	桁のプレースホルダー。数字またはゼロを表示します。その位置に数字があれば、その数字を表示し、なければゼロを表示します。0の数より桁数が少ない場合は、先頭または末尾にゼロが表示されます。小数点以下の桁数が0の数より多い場合は、0の数で示される小数点以下の桁数に丸められます。整数部の桁数が0の数より多い場合、これらは通常通り表示されます。
#	桁のプレースホルダー。これは上記のゼロプレースホルダーと同じ働きをしますが、先頭と末尾のゼロは表示されません。例えば、ゼロプレースホルダーを使うと0.75と表示されますが、#プレースホルダーを使うと.75と表示されます。
.（小数点）	1つの書式指定文字列につき1つだけ許可されます。この文字は、Windowsのコントロールパネルの設定に依存します。（ロケールによっては、ピリオドではなくカンマが使われます。）
%	パーセンテージのプレースホルダーです。数値を100倍し、%文字を書式指定文字列のどこに置くかを指定します。
,（コンマ）	千単位の区切り文字。これは0または#プレースホルダーが使用され、書式指定文字列にカンマが含まれる場合に使用されます。「##0, 」のように、整数部の右側にカンマを1つ置くと、千未満の数字が丸められます。「##0,,」のように整数部の右に2つのカンマを置くと、百万単位に丸めることを意味します。
E- E+	指数表記で数値を表示します。
:（コロン）	時刻の区切り文字。時、分、秒を分割して書式設定する際に使用します。
/	日付の区切り文字 – 日付の書式を指定するときに使用します。
– + £ $ ( )	リテラル文字を表示します。ここに記載されていない文字を表示する場合は、文字の前にバックスラッシュ(\、多くの日本語フォントでは円記号￥)を付けます。

日付と時刻のユーザー定義書式

これらの文字はすべて、日付や時刻の書式設定に使用することができます。

文字	意味
c	日付を dddddd、時刻を ttttt と表示します。
d	日付が1桁の場合、先頭のゼロを付けずに表示します。
dd	日付が1桁の場合、先頭のゼロを付けて表示します。
ddd	曜日を英語の略語で表示します。（Sun～Sat） 日本語1文字で表示したい場合は aaa を使います。（日〜土）
dddd	曜日を英語のフルネームで表示します。（Sunday～Saturday） 日本語3文字で表示したい場合は aaaa を使います。（日曜日〜土曜日）
ddddd	Windowsコントロールパネルの国際設定の、短い形式の日付を表示します。
dddddd	Windowsコントロールパネルの国際設定の、長い形式の日付を表示します。
w	曜日を数字で表示します。（1=日曜日、7=土曜日）
ww	その年の何週目を数字で表示します。（1～53）
m	月が1桁の場合、先頭のゼロを付けずに表示します。
mm	月が1桁の場合、先頭のゼロを付けて表示します。
mmm	月を英語の略語で表示します。（Jan～Dec）
mmmm	月の英語の正式名称を表示します。（January～December）
q	四半期を数字で表示します。（1～4）
y	その年の何日目かを数字で表示します。（1～366）
yy	西暦の下2桁の数字で表示します。
yyyy	西暦を4桁の数字で表示します。
h	時間が一桁の場合、先頭のゼロを付けずに表示します。
hh	時間が一桁の場合、先頭のゼロを付けて表示します。
n	分が一桁の場合、先頭のゼロを付けずに表示します。
nn	分が一桁の場合、先頭のゼロを付けて表示します。
s	秒が一桁の場合、先頭のゼロを付けずに表示します。
ss	秒が一桁の場合、先頭のゼロを付けて表示します。
ttttt	時刻を長い形式で表示します。
AM/PM	12時間制で、午前と午後をそれぞれAMとPMで表示します。
am/pm	12時間制で、午前と午後をそれぞれamとpmで表示します。
A/P	12時間制で、午前と午後をそれぞれAとPで表示します。
a/p	12時間制で、午前と午後をそれぞれaとpで表示します。