【データの抽出では必ず使う】NumberFormatLocalの基本使い方と使用例

こんにちはhokkyokunです。
エクセルでは2021/08/05と2021/8/5は異なるものと認識される場合があります。
そういった表示形式の調整変更に、NumberFomatLocalを使います。

NumberFomatLocalの役割は？

エクセルの上部にいろいろな機能が詰まった「タブ」があると思います。
そのなかの「ホームタブ」の真ん中あたりに表示形式を変更できる場所があります。
ここの設定をいじることができるようになります。

【超重要表現】これだけは覚えよう

・「20.1」をいろいろな表示形式に変えたいとき

　小数点第二位まで表示したい　
　Rangeオブジェクト.NumbdrfomatLocal=”00.00”
　→20.10

　小数点以下を四捨五入したい　
　Rangeオブジェクト.NumbdrfomatLocal=”####”
　→20

　数値を文字列にしたい　
　Rangeオブジェクト.NumbdrfomatLocal=”@”
　→文字列として「20.1」

・「2021/5/30」を色々な日付の標識に変えたいとき

　yyyy/mm/dd形式にしたい
　Rangeオブジェクト.NumbdrfomatLocal=”yyyy/mm/dd”
　→2021/05/30

　yyyymmdd形式にしたい
　Rangeオブジェクト.NumbdrfomatLocal=”yyyymmdd”
　→20210531

　yyyy年m月d日形式にしたい
　Rangeオブジェクト.NumbdrfomatLocal=”yyyy””年””m””月””d””日”””
　→2021年5月31日

・「5000」をドルマークを付けて、かつ三桁毎に区切りをつけたいとき。

　 Rangeオブジェクト.NumbdrfomatLocal=”＄　#,##0″
　→＄　5,000

NumberFormatLocalの構文

構文：オブジェクト.NumberformatLocal=”表示させたい形式”

オブジェクトには範囲のrange,cells、行のrows、列のcolumnsなどです。
テーブルの範囲もＯＫです。

表示形式は大きく分けて

• 数値系
• 日付系
• 文字列系

があります。

表示させたい形式一覧

書式指定文字	説明	設定	エクセルへの入力	表示
G/標準	標準の表示形式。入力された値により設定は変わる	“G/標準”	2021/02/01	44228
＠	入力値を文字列としてそのまま表示します。	“@”	123	123（文字列として認識されます）
“	「”」で囲まれた文字列を表示する。＠の後ろに文字列を入れると表示が「入力」＋「文字列」で表示される	“@地区”	関東	関東地区
＿	＿に続く文字の幅だけ間隔を空ける。	“@_　地区”	関東	関東　地区
#	その桁数が0のとき表示されない	”####.##”	20.1	20.1
0	その桁数が0のとき表示される	”0000.00”	20.1	0020.10
?	小数点以下で桁がない場合、スペースが空き、小数点の位置を揃えることができる。	“00.00?”	20.1	うしろにスペースができる。
. (ピリオド)	数値に小数点を表示します。	”0000.00”	20.1	0020.10
,(カンマ)	数値に桁区切り記号を表示する。	“#,##”	50000000	50,000,000
yy	年を2桁の数値で表示。	“yy”	2021/02/02	21
yyyy	年を4桁の数値で表示。	“yyyy”	2021/02/02	2021
g	元号のアルファベット1文字が表示。M(明治),T(大正),S(昭和),H(平成),R(令和)が表示。	“g”	2021/02/02	R
gg	元号の1文字が表示されます。明,大,昭,平	“gg”	2021/02/02	令
ggg	元号が表示できます。明治,大正,昭和,平成	“ggg”	2021/02/02	令和
e	元号の年が表示される	“e”	2021/02/02	3
m	月数を表示します。1～12が表示	“m”	2021/02/02	2
mm	月数が2桁で表示。1～9は01～09と0が付く	“mm”	2021/02/02	02
mmm	Jan～Dec	“mmm”	2021/02/02	Feb
mmmm	January～December	“mmmm”	2021/02/02	February
mmmmm	J～D頭文字が表示されます。	“mmmmm”	2021/02/02	F
d	日数を表示される。	“d”	2021/02/02	2
dd	01～31が表示できます。	“dd”	2021/02/02	02
ddd	Sun～Sat	“ddd”	2021/02/02	Sat
dddd	Sunday～Saturday	“dddd”	2021/02/02	Saturday
aaa	日～土	“aaa”	2021/02/02	土
aaaa	日曜日～土曜日	“aaaa”	2021/02/02	土曜日
h	時を表示。0～23が表示。	“h”	03:07:08	3
hh	00～23が表示できます。	“hh”	03:07:08	03
m	分を表示します。0～59が表示できます。	“hm”	03:07:08	37
mm	00～59が表示できます。	“hhmm”	03:07:08	0307
s	秒を表示します。0～59が表示できます。	“s”	03:07:08	8
ss	00～59が表示できます。	“ss”	03:07:08	08

実務でよく使う使用例

実務で使用する用途は数値と日付です。

特にAutoFilterを使用するときなどに使います！
じゃないと、うまくフィルタリングしてくれないことがあります。

日付の使用例

やりたいこと：日付の「/」を取った形で表示
目的：ブック名（ファイル名に「/」は使えない）やデータベースの調整

Sub test()
ActiveCell.NumberFormatLocal = "yyyymmdd"
End Sub

プログラム実行前

プログラム実行後。「/」を取って、yyyymmdd形式にできました。

数値0と＃の使い分け

小数点の表示の整理をしてみましょう
桁数を0もしくは＃で表示します。

0→桁があろうがなかろうが「0」を表示します。
＃→桁がなければ「0」は表示しません

1234.567の場合
0～0000→1235
00000→01235
0.0→1234.6
0.00→1234.57
0.000→1234.567
0.0000→1234.5670

1234.567の場合
#~####→1235
#####→1235
#.#→1234.6
#.##→1234.57
#.###→1234.567
#.####→1234.567

数値の使用例

さて、具体例を見ていきましょう。

やりたいこと：小数点の表示桁数を調整
目的：桁数をそろえて見栄えよくする。

Rangeオブジェクト.NumberFormatLocal=”

下記のような表があったとします。
悪くはないですが、桁数が不ぞろいで見栄えが少し悪いですね。

①小数点無し
小数点を表示せず、桁数を揃えるとこんな感じです。

Sub 小数点無し()

ActiveSheet.Range("D17:D22").NumberFormatLocal = "000"

End Sub

②小数点あり
小数点を表示させるバージョンはこんな感じです。
状況によってはありだけどちょっと見づらいかもしれないです。

Sub 小数点あり()

ActiveSheet.Range("D17:D22").NumberFormatLocal = "000.00"

End Sub

③余分なゼロを消す
小数点第一以外のゼロを消したバージョンです。
見やすいですね。でも小数点の位置がずれてて気持ち悪いです。

Sub 余分なゼロを消す()

ActiveSheet.Range("D17:D22").NumberFormatLocal = "##0.0#"

End Sub

④小数点の位置を合わせる

小数点の位置を合わせてみましょう。
かなりいい感じですね。とても見やすく見栄えもきれいです。

Sub test()

ActiveSheet.Range("D17:D22").NumberFormatLocal = "##0.0??"

End Sub

どれが適切かは状況次第ですが、

自分で自由自在に作れるという状況がいいですね。