エクセルVBA Format関数をテッパン関数にする!実務で頻度の高い書式と使用例

VBAFormateyecatch

エクセルVBA Format関数をテッパン関数にする。
普段業務のコードの記述でも結構、Format関数を使える部分があります。
まずはそこからFormat関数の理解を深めましょう。

 

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

 

今回は、文字列操作のVBA関数「Format関数」の使い方についてです。

 

Format関数をテッパン関数にする

 

Format関数は、エクセルVBAの書式変換のメインとなる関数と言えます。

Format関数は、「指定された書式に変換した値を返す」という機能の関数です。

「数値と通貨型」「日付と時刻」「(日付時刻の)シリアル値」「文字列」のデータについて処理することが出来ます。

 

エクセルVBAの使い手になるためには、是非とも自分のテッパン関数にしないといけない部類のものになります。

ただ、利用範囲の広いFormat関数だからこそ、やたら意図なしに使いすぎると「訳が分からなくなる(コードの設計意図が見え難くなる)」こともあります。

なので、Format関数を自分のテッパン関数にするには、

「このコード設計の時にはFormat関数一択」という使い方をしていくのがいいでしょう。

 

多用途で利用できそうなFormat関数であるからこそ、逆に

自分が、実務で使うVBAコード組み立てでのFormat関数は、「この処理ではいつもFormat関数」と決めておくべきです。

悩める君

どうしてでしょうか? Format関数を使えば、覚える関数が少なくなっていいでしょ。

表示形式の設定は、一押し全てFormat関数というものでもありません。

じゅんぱ店長

例えば、

Format関数を使って年月日の表示形式を統一指定して、特定のセルに表示させたいとします。

そのセルに入力させるデータをその都度Format関数で処理させることになります。でも、むしろ、

Rangeオブジェクトのプロパティで、そのセルに「値の表示形式を規定」する方が簡単で明確ではないでしょうか。

プログラムメンテを考えても後々楽だと思います。

 

また別の例えで、

Format関数では、数値を表示する桁数を指定することが出来ます。(末尾を四捨五入計算する。)

数字の計算処理プログラムで、自分もよく起こりがちですが、

VBAコードを組み立てているうちに、「この変数の数値はどういう処理をしたんだっけ?」とかいう ” ド忘れ ” が起こったりします。

確かにこういった場合は、Format関数で処理させているよりも、数値の専用関数の方が明らかに、コードを読み返した時に「あ!そうだった」とすぐに記憶が戻ったりします。

 

Format関数でデータを指定の表示形式に変換する書式

 

Format関数は、データを指定された書式に変換しその結果を返します。(バリアント型)

Format関数は多用途な関数ですが、構文は比較的シンプルなものです。

Format関数の構文

Format ( データ [ ,  書式 [ ,  開始曜日 [ ,  開始週 ] ] ] )

項目 説明
データ 必須

「日付と時刻」「(日付時刻の)シリアル値」「数値と通貨型」「文字列」 変換の対象となる式、文字列

書式 省略可

定義済み書式、ユーザー定義の書式の文字列指定

開始曜日 省略可(省略時の既定値は日曜日)

週の始まりを示す定数

定数か値を指定します。

定数 説明
vbUseSystem 0 NLS  API設定を使用します。
vbSunday 1 日曜日(既定)
vbMonday 2 月曜日
vbTuesday 3 火曜日
vbWednesday 4 水曜日
vbThursday 5 木曜日
vbFriday 6 金曜日
vbSaturday 7 土曜日
開始週 省略可(省略時の既定値は1月1日のある週を第1週とする)

年度の第1週をどこから数えるかを示す定数

定数か値を指定します。

定数 説明
vbUseSystem 0 NLS  API設定を使用します。
vbFirstJan1 1 1月1日が含まれる週から開始します(既定)。
vbFirstFourDays 2 4日以上が含まれる最初の週が年の第1週目になります。
vbFirstFullWeek 3 1週間全体が含まれる最初の週がその年の第1週目になります。

 

適用データ形式は、「日付と時刻」「(日付時刻の)シリアル値」「数値と通貨型」「文字列」になります。

順番に説明していきます。

 

日付と時刻データについて

 

Format関数の中で一番表示バリエーションがあるデータ型になります。

普段使いしない表示パターンもありますので、必要なものだけピックアップして覚えていきましょう。

主な書式指定文字は

書式指定文字 内容
g 年号のアルファベット頭文字を返します。(M、T、S、H、R)
gg 年号の先頭の1文字を漢字で返します。(明、大、昭、平、令)
ggg 年号を返します。(明治、大正、昭和、平成、令和)
e 年号に基づく和暦の年を返します。
ee 年号に基づく和暦の年を返します。(2桁表示)
yy 西暦の年を下2桁の数値で返します。
yyyy 西暦の年を4桁の数値で返します。
m 月を表す数値を返します。
mm 月を表す数値を返します。(2桁表示)
mmm 月の名前を英語の省略形て返します。(Jan~Dec)
mmmm 月の名前を英語で返します。(January~December)
oooo 月の名前を日本語で返します。(1月~12月)
d 日付を返します。
dd 日付を返します。(2桁表示)
ddd 曜日を英語の省略形で返します。(Sun~Sat)
dddd 曜日を英語で返します。(Sunday~Saturday)
aaa 曜日を日本語の省略形で返します。(日~土)
aaaa 曜日を日本語で返します。(日曜日~土曜日)
w 曜日を表す数値を返します。(日曜日は1、~土曜日は7)
y 指定した日付が1年のうちで何日目かを返します。(1~366)
q 指定した日付が1年のうちで何四半期に当たるかを返します。(1~4)
ww 指定した日付が1年のうちで何週目にあたるかを返します。(1~54)
/ 日付の区切り記号を挿入する位置を指定します。
h 時間を返します。
hh 時間を返します。(2桁表示)
n 分を返します。
nn 分を返します。(2桁表示)
s 秒を返します。
ss 秒を返します。(2桁表示)
AM/PM 時刻が正午以前の場合はAM、正午~午後11時59分の間はPMを返します。
am/pm 時刻が正午以前の場合はam、正午~午後11時59分の間はpmを返します。
: 時刻の区切り記号を挿入する位置を指定します。

 

リストに戻る

 

(日付時刻の)シリアル値データについて

 

エクセルの日付時刻はシリアル値によって管理されています。

データとしてシリアル値を指定された書式に変換します。

定義済み書式は

General Date データでシリアル値の整数部分は日付を、小数部分は時刻を返します。
Long Date Windows10「設定」の「時刻と言語」で「地域」の地域設定データで
日付(長い形式)の書式で日時を返します。
Medium Date 簡略形式で表した日付を返します。
Short Date Windows10「設定」の「時刻と言語」で「地域」の地域設定データで
日付(短い形式)の書式で日時を返します。
Long Time データでシリアル値の小数部分について、時間、分、秒で返します。
Medium Time データでシリアル値の小数部分について、時間と分を12時間制の形式で時刻を返します。
Short Time  データでシリアル値の小数部分について、時間と分を24時間制の形式で時刻を返します。

 

リストに戻る

 

数値と通貨型について

 

PCの設定で設定した地域の通貨記号と、3桁区切を表示させるには「Currency」を指定します。

頻度的には、書式指定文字の利用頻度も多いかと思います。

定義済み数値書式は

General Number 指定された数値をそのまま返します。
Currency 適切な場合は、桁区切り記号を付けて数を返します。小数点記号の右 2 桁を返します。 出力はシステム ロケール設定に基づきます。
Fixed 少なくとも整数部 1 桁、小数部 2 桁を返します。
Standard 区切り記号を付けて、少なくとも整数部 1 桁、小数部 2 桁を返します。
Percent 数値を 100 倍して、右側にパーセント記号 (%) を付けて返します。小数部は常に 2 桁です。
Scientific 標準の指数表記を返します。
Yes/No 数値が 0 の場合は Noを返します。それ以外の場合はYes を返します。
True/False 数値が 0 の場合はFalseを返します。それ以外の場合はTrueを返します。
On/Off 数値が 0 の場合はOffを返します。それ以外の場合はOnを返します。

 

数値表示書式指定文字

0 1つの「0」が数値の一桁を表す。データの桁数が小さい場合は0で桁を埋めて返します。
# 1つの「#」が数値の一桁を表す。データの桁数が小さい場合は桁を埋めずに返します。
. 「0」「#」と組み合わせて小数点を挿入する位置を指定します。
% データを100倍してパーセント表示を返します。
, 「0」「#」と組み合わせて、3桁区切り記号の挿入位置を指定します。
¥ 「¥」記号に続く1文字をそのまま表示する。

 

リストに戻る

 

文字列について

 

文字列の操作についてはこのような書式指定文字があります。

文字列表示書式指定文字

@ 1つの「@」が1つの文字またはスペースを表します。「@」を指定した位置に文字がない場合は半角スペースで埋めて返します。
& 1つの「&」が1つの文字を表します。「&」を指定した位置に文字がない場合は詰めて返します。
< データのうち、アルファベットの大文字をすべて小文字に変換します。半角全角を問いません。
> データのうち、アルファベットの小文字をすべて大文字に変換します。半角全角を問いません。
! 文字を左から右の順に埋めていきます。

 

リストに戻る

 

Format関数 実務で頻度の高い書式と使用例

 

Format関数を使うおすすめの場面を独断と偏見で、これからいくつか紹介していきたいと思います。

 

西暦表示の日付を和暦に変更する方法

 

POINT

書式指定文字を利用します。

年号は「ggg」

和暦の年は「e」(漢字の「年」は表示しません)

日本語の月の名前は「oooo」(漢字の「月」は表示します)

日付2桁表示「dd」(漢字の「日」は表示しません)

日本語の曜日は「aaaa」(記号の「( )」は表示しません)

コード

Sub Format関数テスト1()

Dim MyVal As Variant
    MyVal = Worksheets("Sheet1").Range("B3")
    Worksheets("Sheet1").Range("C3") = Format(MyVal, "ggge年oooodd日(aaaa)")

End Sub

vbaformat001

 

リストに戻る

 

有効期限の終了日を求める方法

 

POINT

日時時間の計算はエクセルがシリアル値で計算します。

つまり、単に日数をプラスするだけでOKです。

コード

Sub Format関数テスト2()

Dim MyVal, Mylim As Variant
    MyVal = Worksheets("Sheet1").Range("B3")
    Mylim = Worksheets("Sheet1").Range("C3")
    Worksheets("Sheet1").Range("D3") = Format(MyVal + Mylim, "ggge年oooodd日(aaaa)")

End Sub

vbaformat002

 

リストに戻る

 

「年月日-時刻」の区切り記号なしの表示方法

 

POINT

データ入力したエクセルテンプレートを別名保存する場合に、

「ファイル名+日時」とするときにも利用できます。

コード

Sub Format関数テスト3()

Dim MyVal As Variant
    Worksheets("Sheet1").Range("B3") = Now()
    MyVal = Worksheets("Sheet1").Range("B3")
    Worksheets("Sheet1").Range("D3") = Format(MyVal, "yymmdd-hhnn")

End Sub

vbaformat003

 

リストに戻る

 

ID番号付加で桁数を統一する方法

 

POINT

Format関数で数字の桁数を揃えることが出来ます。

書式指定文字「0」を使用します。

 

表示させる注意点としては、エクセルの仕様として

セル表示において、数値と判断される文字列で先頭に「0」がある場合は、デフォルトとでは自動的に「0」を削除してしまう ことです。

 

したがって、ここでは、セルの表示を

  • 強制的に文字列型にする
  • セルの表示を右寄せにする

コードを付け加える必要があります。

MEMO

With~End Withの使い方については、この記事を参考にしてください。

With~End Withの使い方。VBAコードを簡潔に記述する

コード

Sub Format関数テスト4()

Dim MyVal As Variant
Dim i As Long

    Worksheets("Sheet1").Range("B3") = 1
    Worksheets("Sheet1").Range("B4") = 23
    Worksheets("Sheet1").Range("B5") = 155
    Worksheets("Sheet1").Range("B6") = 1797
    Worksheets("Sheet1").Range("B7") = 25791

    For i = 3 To 7
        MyVal = Worksheets("Sheet1").Range("B" & i)
        
        With Worksheets("Sheet1").Range("D" & i)
            .NumberFormatLocal = "@"
            .HorizontalAlignment = xlRight
            .Value = Format(CStr(MyVal), "00000")
        End With
        
    Next i

End Sub

vbaformat004

 

リストに戻る

 

数値データを単位記号を付けて表示する方法

 

POINT

「¥」記号に続く1文字をそのまま表示することが出来るので、数値の単位などを付加した書式を指定することが出来ます。

コード

Sub Format関数テスト5()

Dim MyVal As Variant

    Worksheets("Sheet1").Range("B3") = 13
    Worksheets("Sheet1").Range("C3") = 11

    MyVal = Worksheets("Sheet1").Range("B3") * Worksheets("Sheet1").Range("C3")
    Worksheets("Sheet1").Range("D" & 3) = Format(MyVal, "0.00¥c¥㎡")

End Sub

vbaformat005

 

リストに戻る

 

Format関数をテッパン関数に まとめ

 

Format関数を利用するに当たっては、そのたくさんの機能を覚える必要はありません。

自分の業務の中で、必要となる部分を知っておけばまずは十分だと言えます。

この記事の最初の方で、変換できる書式をたくさん説明しましたが、

読んでいるだけで面倒な気分になったのではないでしょうか。

 

「自分の業務では、このコード設計の時はFormat関数を使う。」

という様な使い方が、Format関数をテッパン関数にする一番の近道になるのではと思います。

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

「VBA最速理解」の記事一覧を開く

 

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

 

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