VBA

【VBA初心者】Format関数の基本と使いどころ。NumberFormatLocalとの使い分け。

記事内に商品プロモーションを含む場合があります

このページでわかること

Format関数の基本的な使い方
どういったときに使えばよいか
Format関数とNumberFormatLocalプロパティの使い分け

覚えること
  1. 構文:Format(Expression , [Format] , [Week] , [FirstWeekOfYear])
    大事なのはExpressionとFormat
  2. 日付の調整方法
    数値の調整方法
    文字列の調整方法
  3. Format関数の使いどころはMsgBoxとAutoFilter
  4. シートに反映させる場面ではNumberFormatLocalを使い、
    Msgboxやフィルタリングなどで値の調整が必要な時に使う

こんにちは、hokkyokunです。
Fomat関数についてご紹介していきます。

エクセルでは2021/08/05と2021/8/5は違うものと認識されてしまいます。
そのため、こういった日付や数値の見せ方を調整する際に、Format関数を使用します。

実務ではかなりおせわになると思いますのでやっていきましょう。

構文

構文:Format(Expression , [Format] , [FirstDayOfWeek] , [FirstWeekOfYear])

Expression:値
Format:形式を入力。例えば”yyyy/mm/dd”。「”」で挟むのを忘れずに。

引数で重要なのはExpressionとFormatです。
FirstDayOfWeekやFirstWeekOfYearは使いどころが限定しているので
最初は覚えず、慣れてきてからでいいと思います。

下記で実際の調整方法をご紹介します。

日付の調整方法

よく使う日付の調整方法についてご紹介します。
2021年2月5日を形式を変えたいとします。

yyyymmdd形式(20210205)
 Format(2021/2/5,”yyyymmdd”)→20210205

yyyy年m月d日形式(2021年2月5日)
 Format(2021/02/05,”yyyy年m月d日”)→2021年2月5日

曜日形式(金曜日)
 format(2021/02/05,”aaaa”)→金曜日

日付の表示書式まとめ

日付で使える表示方法をまとめました。

文字説明コード戻り値
d日付を返す。1~9は一桁format(“2021/05/05”,”d”)5
dd日付を返す。どの数字も二桁になる。format(“2021/05/05”,”dd”)05
ddd曜日を英語3文字で返す。format(“2021/05/05”,”ddd”)Wed
dddd曜日を英語で返す。format(“2021/05/05”,”dddd”)Wednesday
aaa曜日を漢字一文字で返す。format(“2021/05/05”,”aaa”)
aaaa曜日を漢字で返す。format(“2021/05/05”,”aaaa”)水曜日
m月を返す。1~9は一桁format(“2021/05/05”,”m”)5
mm月を返す。1~9は二桁format(“2021/05/05”,”mm”)05
mmm月を英語3文字で返す。format(“2021/08/05”,”mmm”)Aug
mmmm月を英語で返すformat(“2021/08/05”,”mmmm”)August
yy年を下二桁で返す。format(“2021/05/05”,”yy”)21
yyyy年を返す。format(“2021/05/05”,”yyyy”)2021
h時間を返す。1~9は一桁Format(“09:05:06”, “h”)9
hh時間を返す。1~9は二桁Format(“09:05:06”, “hh”)09
n分を返す。1~9は一桁Format(“09:05:06”, “n”)5
nn分を返す。1~9は二桁Format(“09:05:06”, “h”)05
s秒を返す。1~9は一桁Format(“09:05:06”, “s”)6
ss秒を返す。1~9は二桁Format(“09:05:06”, “h”)06

定型的な書式

日付を調整する際に便利な書式があるので余裕があれば覚えてもいいかもしれません

定義済みの書式説明コード戻り値
Long Date長い形式の日付Format(“2021/05/05”, “Long Date”)2021年5月5日
Short Date短い形式の日付Format(“2021年5月5日”, “Short Date”)2021/05/05
Long Time長い形式の時刻(秒数表示)Format(Now, “Long Time”)21:08:05
Short Time短い形式の時刻(秒数なし)Format(Now, “Short Time”)21:08

数値の調整方法

数値の調整方法です。
よく使うのは「#」と「0」です。

0 → 桁がなければ0をつける。
# → 桁がなければ0がつかない。

例えば「12.3」の表記を調整したい場合

Format(12.3,”000.00”)→012.30
Format(12.3,”###.##”)→12.3

また、もうひとつよく使うのが3桁ごとの区切り「,」だと思います。

例えば123456789という数値を三桁区切りで表記したい場合

Format(123456789,”#,###”)→123,456,789

桁数をそろえるときや桁数を見やすくするときに重宝します。

数値の表示書式

主な数値表示書式をまとめました。

文字説明コード戻り値
00一つにつき数値の1桁を表す。桁がない場合は0が入るFormat(123.456, “0000.0000”)0123.4560
##一つにつき数値の1桁を表す。桁がない場合は0が入らないFormat(123.456, “####.####”)123.456
,桁区切りを入れるFormat(123456, “#,###”)123,456
「,」の後に「0」や「#」を入れない桁区切りを入れた後、末尾にさらに「,」を入れる。「,」を入れた数✖1000の値を表示しないFormat(123456789, “#,###,”)
Format(123456789, “#,###,,”)
123,457

123
すぐあとの一文字をそのまま表示する。¥をつけたい場合は¥¥とする。Format(12345, “#,###\m\m”)
Format(12345, “\\#,###”)
12,345mm

\12,345
( , ) , $ , – , 、そのまま表示可能Format(123456, “($@@@-)\と、($@@@-)”)($123-)と、($456-)

定型的な表示書式

数値も定型的な書式の設定があるので、よく使うものがあればおぼえてもいいかしれないです。

定義済みの書式説明コード戻り値
General Number桁区切り無しで表示format(“1,234.567”,”General Number”)1234.567
Currency通貨記号format(“1,234.567”,”Currency”)¥1,235
Percentパーセント表示小数点第二位までformat(0.1234567,”Percent”)12.35%
Standard小数点第二位まで、桁区切りをつけて表示Format(1234.567, “Standard”)1,234.57

文字列の調整方法

個人的にはここで調整するより入力値をきちんととったほうがいいと思いますが、
Formatで調整することも可能です。

文字説明コード戻り値
1文字またはスペースを表示する。@一文字に対し、文字一つ対応。対応する文字がなければスペースFormat(“abcd”, “@@@@@”)
(前にスペースが入り) abcd
一文字を表示する。&一文字に対し、文字一つ対応。対応する文字がなければ詰めるFormat(“abcd”, “&&&&&”)abcd
!文字を詰める。左から右に詰めていく。Format(“abcd”, “!@@@@@”)abcd
すべて小文字に変換する。Format(“ABCD”, “<&”)abcd
すべて大文字に変換する。Format(“abcd”, “>&”)ABCD

Format関数の使いどころ

Format関数は是非お友達になるべき関数ですが、
どこで使うと良いでしょうか?

Format関数の使いどころはMsgBoxとAutoFilterです。

特にAutofilterの日付フィルタリングは表示が少し違うだけで、動いてくれないので、Format関数で数値を調整してからフィルタリングするとうまくいくことが多いです。

例えば、
Autofilterの検索値を入力する引数Criteriaに
Format(activecell.value,”yyyy/mm/dd”)と入れるとうまくフィルタリングされることが多いです。
(yyyy/mm/ddは任意です。ご自身のエクセルの書式を確認して調整してください。)

例えばこんな表を作ったとします。
ここから2021年8月8日に購入したもののみを検索かけるとします。

下記コードでプログラムを動かします。

Sub test()

ActiveSheet.Range("A1").AutoFilter field:=2, Criteria1:=#8/8/2021#

End Sub

すると検索が引っかからないという事態になります。

どうすれば検索に引っかかることができるでしょうか。
方法はいくつかあるのでしょうが、私はFormat関数をかませてます。

Sub test()

ActiveSheet.Range("A1").AutoFilter field:=2, Criteria1:=Format(#8/8/2021#, "yyyy/m/d")

End Sub

無事検索引っ掛けることが出来ました。
多分この方法が一番簡単です。

Format関数とNumberFormatLocalプロパティの使い分け

よく似た機能でNumberFormatLocalプロパティがあるかと思いますが、
使い分けってどうすればよいでしょうか。

Formatは上記の説明の通りフィルタリングをするときに強力な武器になります。

ただ、Format関数で変えてもエクセルに転記するとそのセルの表示形式に引っ張られるので、これで表を整えることはできません。

シートに反映させたい場面ではNumberFormatLocalを使い、Msgboxやフィルタリングなどで値の調整が必要な時はFormat関数を使うといいと思います。

NumberFormatLocalとFormat関数の違いを実際に見てみましょう。

Sub format関数()

Dim num As Double
num = Format(12.5, "000.000")
ActiveCell.Value = num
End Sub

Sub NumberFormatLocalプロパティ()

Dim num As Double
num = 12.5
ActiveCell.NumberFormatLocal = "0000.000"


End Sub

プログラムを動かしてみると、
Format関数の方はセルの書式に引っ張られるので表記は変わらず、
NumberFormatLocalは書式が変更され、表記が変わりました。

こんな違いが出ます。

ではでは。