Format関数の基本的な使い方
どういったときに使えばよいか
Format関数とNumberFormatLocalプロパティの使い分け
-
構文：Format(Expression , [Format] , [Week] , [FirstWeekOfYear])
大事なのはExpressionとFormat -
日付の調整方法
数値の調整方法
文字列の調整方法 - Format関数の使いどころはMsgBoxとAutoFilter
-
シートに反映させる場面では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
桁数をそろえるときや桁数を見やすくするときに重宝します。
数値の表示書式
主な数値表示書式をまとめました。
| 文字 | 説明 | コード | 戻り値 |
| 0 | 0一つにつき数値の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は書式が変更され、表記が変わりました。
ではでは。
