「表示形式」をVBAコード的に最速理解する

vbacellssyoshikieyecatch

エクセルVBA セルの表示形式の最速理解のカギは、NumberFormatLocalプロパティが全てです。
数値・日付・時刻・分数・指数・文字列など全て解説します。

こんにちは　じゅんぱ店長　(＠junpa33)　です。

エクセルVBAの操作の中での基本中の基本「セルの表示形式」についてです。

エクセルVBAでプログラムを作成する中でも、この表示形式の設定を間違えばプログラムが動かないことすらあります。

少々言い過ぎたかもしれませんが、一番重要な設定ですので、しっかり理解しておきましょう。

セルの書式設定にある「セルの表示形式」はコレのこと

エクセルシートから行うセルの表示形式の設定作業ならマウスクリックで ”チョイ-チョイ” と簡単作業なのですが、

同じことをVBAコードで行うためには少々の知識を覚えることが必要です。

目次へ戻る

セルの表示形式は、「NumberFormatLocalプロパティ」

セルの表示形式は、全て「NumberFormatLocalプロパティ」でコントロールされています。

セルの表示形式の設定を覚えることは、「NumberFormatLocalプロパティ」を覚える事と同じ意味です。

NumberFormatLocalプロパティの構文

構文

Rangeオブジェクト.NumberFormatLocal = 表示形式

  • 　Rangeオブジェクトの代表的なものは「Range」や「Cells」になります。
  • 　表示形式は、VBAですでに定義されている書式や書式記号です。文字列の形式で指定します。

表示形式の分類は、全てで12種類あります。（代表的な表示形式）

  • 標準　　　　　（ “G/標準” ）
  • 数値　　　　　（ “0_” ）
  • 通貨　　　　　（ “￥#,##0;-￥#,##0” ）
  • 会計　　　　　（ “_ * #,##0_ ;_ * -#,##0_ ;_ * “-“_ ;_ @_ ” ）
  • 日付　　　　　（ “yyyy/m/d” ）
  • 時刻　　　　　（ “[$-F400]h:mm:ss AM/PM” ）
  • パーセンテージ（ “0%” ）
  • 分数　　　　　（ “# ?/?” ）
  • 指数　　　　　（ “0.E+00” ）
  • 文字列　　　　（ “@” ）
  • その他　　　　（ “[<=999]000;[<=9999]000-00;000-0000” ）郵便番号の場合
  • ユーザー定義　（ “[$-411]ge.m.d” ）和暦表示の場合

となります。

それでは、12種類の分類されぞれの設定方法を見ていきます。

標準

「標準」はデフォルト状態では全てこの形式になっています。

この「標準」を指定することはほぼ無いです。あるとすれば、「デフォルトに戻す」ときになります。

＜記述見本＞

  • Range(“A1”).NumberFormatLocal = “G/標準”
  • Cells(1, 1).NumberFoematLocal = “G/標準”

数値

数値は「標準」設定のままでも数値として入力表示されます。

桁数に関して実用レベルで「数値」の設定をしないといけなくなる時があります。

  • 数値の桁数が11桁以下の時

「標準」設定のままで表示され数式計算も可能です。

  • 数値の桁数が12桁以上15桁以下の時

「数値」設定をしなければ正常に表示されません。数式計算はされます。

「文字列」設定でも正常に表示されます、が、もはや数値とは見なされません。数式計算は出来ません。

  • 数値の桁数が16桁以上の時

「文字列」設定でのみ平常に表示されます。数値とは見なされません。数式計算も出来ません。

バーコードをリーダーなどで読み取ることが多々ありますが、現在多くが13桁バーコードになっています。

セルの表示形式設定をなにも触らず「標準」にしていると、100%間違いなく、バーコードリーダーで読み取った数値が正常表示されません。

その表示セルは「数値」の設定に変更してください。

＜記述見本＞

  • 3桁ごとの区切りカンマを付ける　1,234

Range(“A1”).NumberFormatLocal = “#,##0”

  • 小数点以下を表示ピリオドを付ける　以下1位、以下2位　123.4　12.34

Range(“A1”).NumberFormatLocal = “0.0”

Range(“A1”).NumberFormatLocal = “0.00”

  • マイナスの赤字表記とカンマ3桁区切り　-1,234

Range(“A1”).NumberFormatLocal = “#,##0;[赤]-#,##0”

  • マイナスの赤字△表記とカンマ3桁区切り　△1,234

Range(“A1”).NumberFormatLocal = “#,##0;[赤]△#,##0”

通貨

「標準」の設定であっても、「￥」マークを付けて数値をセルに入力すれば通貨表示になります。

この「通貨」設定をすることで数値のみで通貨表示させることが出来ます。

＜記述見本＞

  • 日本円マイナスは赤字表記　￥1,234

Range(“A1”).NumberFormatLocal = “￥#,##0;[赤]-￥#,##0”

  • ドル表記マイナスは赤字△表記　＄1,234

Range(“A1”).NumberFormatLocal = “$#,##0;[赤]△$#,##0”

会計

「会計」の設定については、「通貨」設定とよく似ていますが、セル表示で通貨記号の表示位置が違ったり、右端に半角スペース空いたりと表示に差があります。

表示の差異会計の表示設定通貨の表示設定
通貨記号の位置通貨記号は左詰め通貨記号は数値の前
数値の位置右詰め通貨記号を含めて右詰め
スペースの配置数値の右に半角スペースつかない
空欄セルの処理通貨記号とゼロ表示のハイフン「￥0」のゼロ円表示
3桁区切りと赤字表示3桁区切りは自動、赤字表示なし3桁区切り自動、赤字表示可能

＜記述見本＞

  • 日本円で小数点以下を設定しない　¥　 -123,456

Range(“A1”).NumberFormatLocal = “_ ￥* #,##0_ ;_ ￥* -#,##0_ ;_ ￥* “”-“”_ ;_ @_ “

日付

「日付」設定は、表示バリエーションも非常に多いです。「今回はこれにする、次はあれにする」とかの使い方は無いにしても、何通りかの普段使いの表示方法は知っておきましょう。

西暦年月日年・月・日・曜日説明表示例
西暦年yy西暦年2桁22
yyyy西暦年4桁2022
西暦月m月7
mm月2桁統一07
mmm英語略式Jul
mmmm英語July
西暦日d日9
dd日2桁統一09
西暦曜日ddd英語略式Sat
dddd英語Saturday
和暦年月日暦・年・月・日・曜日説明表示例
元号・和暦g元号ローマ字表記R
gg元号略式令
ggg元号令和
e和暦4
ee和暦2桁統一04
月・日は西暦に同じ西暦参照
日本語曜日aaa日本語略式土
aaaa日本語土曜日

＜記述見本＞

Range(“A!”) = “2022/07/09”

  • 2022年7月9日(土)　を表示させる

Range(“A1”).NumberFormatLocal = “yyyy””年””m””月””d””日””(aaa)”

  • 令和4年7月9日(土曜日)を表示させる

Range(“A1”).NumberFormatLocal = “ggge””年””m””月””d””日””(aaaa)”

  • July 09 2022 を表示させる

Range(“A1”).NumberFormatLocal = “mmmm dd yyyy”

時刻

「時刻」設定は恐らく普段使いは、24時間表記か12時間表記の時分秒2桁統一表示が殆どでしょう。

また、「日付」表示との組み合わせで使うことも多いと思います。

時分秒、時間表示時・分・秒・午前午後説明表示例
時h時（24時間表記）8
hh時2桁統一（24時間表記）08
分m分5
mm分2桁統一05
秒s秒3
ss秒2桁統一03
12時間表示AM/PM12時間表記AMまたはPM

＜記述見本＞

Range(“A1”) = Now

  • 現在時刻を24時間表示で表示する　08:05:03

Range(“A1”).NumberFormatLocal = “hh:mm:ss”

  • 現在時刻を12時間表示で表示する　08:05:03 AM

Range(“A1”).NumberFormatLocal = “hh:mm:ss AM/PM”

  • 現在の日付と時刻をまとめて表示する　2022/07/09-08:05:03 AM

Range(“A1”).NumberFormatLocal = “yyyy/mm/dd””-“” hh:mm:ss AM/PM”

「日付」「時刻」設定の便利な利用法

エクセルファイルを頻繁に更新することがある場合

ファイル名を変更しなければ、常に上書き保存となります。

ファイル内のデータが追加や変更されて、常に最新のものだけがファイルとして存在することになります。

もし、追加変更などの更新の内容が間違っていたら？・・・もう更新前に戻すことは出来ません。

そんな時は、

Now関数と「日付」「時刻」の表示の設定を利用して、エクセルファイルの保存名の後に日時コードを付け加えておきます。

例えば「.NumberFormatLocal = “yymmdd””_”” hhmmss”」

これを付け加えることで、1秒単位で上書きされても別ファイル名として保存されることになります。

秒単位が極端な場合は「ss」を削除すれば、分単位となります。

更新の過去履歴も分かり非常に便利です。（保存ファイル数が多くなりますので、要ら無くなり次第削除を忘れずに！！）

パーセンテージ

「パーセンテージ」設定で「％」表示になります。

＜記述見本＞

Range(“A1”) = 0.355

  • 整数値で％表示する　36％

Range(“A1”).NumberFormatLocal = “0%”

  • 小数点以下2桁まで％表示する　35.50%

Range(“A1”).NumberFormatLocal = “0.00%”

分数

「分数」設定は、少し注意が必要です。

「標準」設定の状態で分数（のつもり）で入力すると日付として表示されます。その後で「分数」設定しても、もはや正常に表示されることはありません。

セルに分数を入力するコツとしては、先にその入力セルの表示形式を「分数」に設定変更しておく必要があります。

＜記述見本＞

  • Range(“A1”).NumberFormatLocal = “# ??/??”

「3/13」と「10/3」をセルに入力すると・・・

「分数」設定後に分数をセル入力すると　正常表示　3/13　仮分数の場合は帯分数に変換表示　3 1/3　になる

  • Range(“A18”).NumberFormatLocal = “##/##”

「3/13」と「10/3」をセルに入力すると・・・

「分数」設定後に分数をセル入力すると　正常表示　3/13　仮分数の場合は仮分数でそのまま表示　10/3　になる

指数

「指数」設定で指数表示となります。

例えば　指数表示は、8.E+00となり意味は　８ = 8 x 100　ということになります。

＜記述見本＞

Range(“A1”) = 2022

  • 2.022E+03　と表示される　2.022 x 103　という意味です。

Range(“A1”).NumberFormatLocal = “0.000E+00”

文字列

「文字列」設定で文字列を入力表示することが出来ますが、「標準」の設定のままでも文字列の表示は可能です。

文字列の設定を行うと入力文字が入力文字のまま表示されるというのが特徴です。計算はされません。

＜記述見本＞

Range(“A1”).NumberFormatLocal = “@”

Range(“A1”) = “=123+321”

  • 計算式を計算せずにそのまま表示します。　=123+321

その他

郵便番号、電話番号（東京）、正負記号、漢数字、大字、全角、全角桁区切りの表示設定があります。

その中でここでは、郵便番号を取り上げます。

＜記述見本＞

Range(“A1”) = 1001111

  • 郵便番号として　100-1111　と表示させる

Range(“A1”).NumberFormatLocal = “[<=999]000;[<=9999]000-00;000-0000”

ユーザー定義

「ユーザー定義」設定を使ってオリジナルに表示させることが出来ます。

ここでは、税込価格を明示する表示設定を行います。

＜記述見本＞

Range(“A1”) = 12345

  • 税込価格を明示した表示にする　¥1,235(税込)

Range(“A1”).NumberFormatLocal = “#,##0″”(税込)”””

目次へ戻る

セルの表示形式の最速理解のまとめ

セルの表示形式はエクセルVBAでも非常に基本的な部分です。

基本的にセルの表示形式は内容を理解するというようなものでなく、内容を覚えるという質のものになります。

セルの表示形式を最速理解するとは、普段使いが多い「数値」「通貨」「日付」「時刻」「文字列」などの設定をサクッと使い慣れてしまうということでしょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

VBAcellareaeyecatch
セルの範囲選択と設定は14のツールで対応する

次の記事

「配置」を最速理解する エクセルVBA
vbacellsplacementeyecatch