VBA

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

このページでわかること

NumberFormatLocalプロパティの基本的な使い方がわかります。

覚えること
  1. 2021/08/05と2021/8/5は違うものと認識される場合がある→NumberFormatLocalで調整
  2. 実務でよく使うのは日付と数値の調整
  3. 表示形式をあわせないとAutoFilterで引っかからないこともある。

こんにちは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などです。
テーブルの範囲もOKです。

表示形式は大きく分けて

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

があります。

表示させたい形式一覧

書式指定文字説明設定エクセルへの入力表示
G/標準標準の表示形式。入力された値により設定は変わる“G/標準”2021/02/0144228
入力値を文字列としてそのまま表示します。“@”123123(文字列として認識されます)
「”」で囲まれた文字列を表示する。@の後ろに文字列を入れると表示が「入力」+「文字列」で表示される“@地区”関東関東地区
__に続く文字の幅だけ間隔を空ける。“@_ 地区”関東関東 地区
#その桁数が0のとき表示されない”####.##”20.120.1
0その桁数が0のとき表示される”0000.00”20.10020.10
?小数点以下で桁がない場合、スペースが空き、小数点の位置を揃えることができる。“00.00?”20.1うしろにスペースができる。
. (ピリオド)数値に小数点を表示します。”0000.00”20.10020.10
,(カンマ)数値に桁区切り記号を表示する。“#,##”5000000050,000,000
yy年を2桁の数値で表示。“yy”2021/02/0221
yyyy年を4桁の数値で表示。“yyyy”2021/02/022021
g元号のアルファベット1文字が表示。M(明治),T(大正),S(昭和),H(平成),R(令和)が表示。“g”2021/02/02R
gg元号の1文字が表示されます。明,大,昭,平“gg”2021/02/02
ggg元号が表示できます。明治,大正,昭和,平成“ggg”2021/02/02令和
e元号の年が表示される“e”2021/02/023
m月数を表示します。1~12が表示“m”2021/02/022
mm月数が2桁で表示。1~9は01~09と0が付く“mm”2021/02/0202
mmmJan~Dec“mmm”2021/02/02Feb
mmmmJanuary~December“mmmm”2021/02/02February
mmmmmJ~D頭文字が表示されます。“mmmmm”2021/02/02F
d日数を表示される。“d”2021/02/022
dd01~31が表示できます。“dd”2021/02/0202
dddSun~Sat“ddd”2021/02/02Sat
ddddSunday~Saturday“dddd”2021/02/02Saturday
aaa日~土“aaa”2021/02/02
aaaa日曜日~土曜日“aaaa”2021/02/02土曜日
h時を表示。0~23が表示。“h”03:07:083
hh00~23が表示できます。“hh”03:07:0803
m分を表示します。0~59が表示できます。“hm”03:07:0837
mm00~59が表示できます。“hhmm”03:07:080307
s秒を表示します。0~59が表示できます。“s”03:07:088
ss00~59が表示できます。“ss”03:07:0808

実務でよく使う使用例

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

特に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

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

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