一般にプログラミングの世界などで、足りない桁数分「0」を追加して長さを合わせることを「ゼロ パディング」と言います。
Excel では 0001 が 1 になる、といった「ゼロがなくなる」ことがよく話題になります。これをゼロ サプレスといいますが、あまりこちらの言葉は聞きませんね。
今回は Excel でのゼロ パディングの3つのポイントをご紹介します。
そのため、セルの書式を「文字列」に設定することが「第1のポイント」です。
この場合は数値を入力しても「見た目を変えてあげる」と考えます。表示形式で「円」や「人」といった単位を加えても数値として扱うことができますが、これと同じ考え方です。「ユーザー定義」の「種類(T)」で「000000」と設定します。すると、この表示形式が設定されたセルに数値を入力すると、指定した6桁に足りない分を 0 で埋めてくれます。この表示形式を使うのが「第2のポイント」です。
文字列設定と違い、このセルに入力されているデータは「数値」となります。見た目は 0001 などですが、四則演算可能です。
上述の表示形式は計算可能ですから、連番や何らかの演算で数値の計算が可能で、見た目はゼロ パディングすることが可能ですが、文字列 “E” などと組み合わせると、表示形式で設定されているゼロは消えてしまいます。
これを避けるためには TEXT関数を使ってゼロ パディングされた表示形式を使って数値を文字列にします。これが「第3のポイント」です。
=TEXT(セル、”00000”)
ちなみに、TEXT関数や表示形式で使った ”00000” の 0 は数字のゼロではなく、書式記号としての 0 です。
https://support.microsoft.com/ja-jp/kb/883199
なので “99999” としたところで 9 がパディングされることはありません。
また、小数点以下のゼロ パディングもあります。123.000 のようなものです。書式設定の [数値] で [小数点以下の桁数] を指定しますが、書式記号で書けば、ユーザー定義書式で小数点以下3桁は #.000 もしくは .000 となります。
ちなみに、#,##0 と #,### では同じ三桁カンマでも、0 の場合の表示が変わります。前者は 0 を表示、後者は何も表示しない、となります。
ゼロ パディングはできるのですが、結果的に、このゼロ パディングされた「文字列」の “0001” をワークシートのセルに入力した時点で、0001 –> 1 になります。
よって、事前にセルの書式設定をしておくか、VBA からセルの書式設定が必要になります。
セルを文字列の書式に設定してゼロ パディングするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “@”
Range(“A1”).Value = Format(buf,”00000”)
数値のまま、表示形式でゼロ パディングのユーザー定義書式にするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “00000”
Range(“A1”).Value = buf
になります。
パディングをする関数は Text.PadStart です。
Text.PadStart
https://msdn.microsoft.com/en-us/library/mt260573.aspx
そのため、数値を文字列に変更します。Number.ToText を使って、数値を文字列にして、6桁、”0” から始まる、とします。
似たような関数として Text.PadEnd もあります。これは、足りない桁の後ろを指定した文字で埋めます。
Text.PadEnd
https://msdn.microsoft.com/en-us/library/mt260477.aspx
[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。
Excel では 0001 が 1 になる、といった「ゼロがなくなる」ことがよく話題になります。これをゼロ サプレスといいますが、あまりこちらの言葉は聞きませんね。
今回は Excel でのゼロ パディングの3つのポイントをご紹介します。
文字列設定で反映
0001 を 1 ではなく 0001 としてセルで扱うとなると、いくつかの方法があり、そのひとつは、入力されたデータを文字列として扱う方法です。そのため、セルの書式を「文字列」に設定することが「第1のポイント」です。
- 設定したいセル・範囲を選択する
- Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「文字列」を選択して [OK]
表示形式で設定
ですが、「ゼロ パディング」という意味は、桁数が決まっていて、足りない分だけ 0 を入れて長さを合わせることです。ですから、例えば 6 桁でゼロ パディングしてほしい場合は、1 と入力したら 000001 であり、342 と入力したら 000342 になってほしいこともあるわけです。この場合は数値を入力しても「見た目を変えてあげる」と考えます。表示形式で「円」や「人」といった単位を加えても数値として扱うことができますが、これと同じ考え方です。「ユーザー定義」の「種類(T)」で「000000」と設定します。すると、この表示形式が設定されたセルに数値を入力すると、指定した6桁に足りない分を 0 で埋めてくれます。この表示形式を使うのが「第2のポイント」です。
- 設定したいセル・範囲を選択する
- Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「ユーザー定義」を選択する
- 「種類」でゼロ パディングしたい桁数の 0 を入れる
文字列設定と違い、このセルに入力されているデータは「数値」となります。見た目は 0001 などですが、四則演算可能です。
TEXT関数でパディング
数値を数値として扱うのであれば、わざわざゼロ パディングする必要はないでしょう。たとえば社員番号のような E00001 のようなデータを作りたい場合、E と ゼロ パディングした数値をつなげるパターンになりますよね。上述の表示形式は計算可能ですから、連番や何らかの演算で数値の計算が可能で、見た目はゼロ パディングすることが可能ですが、文字列 “E” などと組み合わせると、表示形式で設定されているゼロは消えてしまいます。
これを避けるためには TEXT関数を使ってゼロ パディングされた表示形式を使って数値を文字列にします。これが「第3のポイント」です。
=TEXT(セル、”00000”)
ちなみに、TEXT関数や表示形式で使った ”00000” の 0 は数字のゼロではなく、書式記号としての 0 です。
https://support.microsoft.com/ja-jp/kb/883199
なので “99999” としたところで 9 がパディングされることはありません。
また、小数点以下のゼロ パディングもあります。123.000 のようなものです。書式設定の [数値] で [小数点以下の桁数] を指定しますが、書式記号で書けば、ユーザー定義書式で小数点以下3桁は #.000 もしくは .000 となります。
ちなみに、#,##0 と #,### では同じ三桁カンマでも、0 の場合の表示が変わります。前者は 0 を表示、後者は何も表示しない、となります。
Format 関数を使ったゼロ パディング
VBA では TEXT関数同様に Format 関数を使ってゼロ パディングをすることができます。ゼロ パディングされた文字列をMsgBoxを使って表示可能です。
ゼロ パディングはできるのですが、結果的に、このゼロ パディングされた「文字列」の “0001” をワークシートのセルに入力した時点で、0001 –> 1 になります。
よって、事前にセルの書式設定をしておくか、VBA からセルの書式設定が必要になります。
セルを文字列の書式に設定してゼロ パディングするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “@”
Range(“A1”).Value = Format(buf,”00000”)
数値のまま、表示形式でゼロ パディングのユーザー定義書式にするには
Dim buf as Long
buf = 1
Range(“A1”).NumberFormatLocal = “00000”
Range(“A1”).Value = buf
になります。
Power Query でゼロ パディング
パディングをする関数は Text.PadStart です。
Text.PadStart
https://msdn.microsoft.com/en-us/library/mt260573.aspx
そのため、数値を文字列に変更します。Number.ToText を使って、数値を文字列にして、6桁、”0” から始まる、とします。
似たような関数として Text.PadEnd もあります。これは、足りない桁の後ろを指定した文字で埋めます。
Text.PadEnd
https://msdn.microsoft.com/en-us/library/mt260477.aspx
[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。
