曜日と表示形式とTEXT関数とFormat関数

実践ワークシート協会主催の Excel VBA セミナーでは、講師の田中先生が毎回必ず聞く質問がいくつかありますが、そのうちの一つが「曜日を表す表示形式」に関連する内容です。

Excel VBA セミナー ベーシック
http://officetanaka.net/seminar/seminar1.htm

なぜ VBA セミナーで表示形式？と思われるかもしれませんが、VBA プログラミングにおいて、日付＝シリアル値から曜日を知るためには、曜日を返す専用の関数はなく、表示形式を利用して曜日を判定しなくてはならないからです。その関数が　Format 関数です。

Format関数 （Excel VBA の関数）
http://officetanaka.net/excel/vba/function/Format.htm

この Format 関数はワークシート関数の TEXT 関数とほぼ同じ動きをします。

TEXT関数 （Excel ワークシート関数）
http://officetanaka.net/excel/function/function/text.htm

そして、このFormat関数やTEXT関数を使う上で知っておかなければならない知識が「書式記号」です。

曜日の鉄板ネタとして、書式記号の a を使って、aaa や aaaa で日本語での曜日をシリアル値から表示できます。ddd や dddd を使えば英語での曜日をシリアル値から調べることができます。

曜日を表示する
http://officetanaka.net/excel/function/tips/tips38.htm

曜日の表示で WEEKDAY関数と CHOOSE関数を使った数式を見ることがありますが、TEXT関数が一番簡単だと思います。ただし、aaa といった書式記号を覚えていなくてはなりません。

この書式記号、書式設定のユーザー定義書式でも使います。
セルを選択して Ctrl+1 でセルの書式設定ダイアログボックスが表示され、表示形式で書式記号を使って、セルに入力された表示方法を制御します。

セル内に入力されたデータがシリアル値になっていれば、このように曜日を調べることが可能です。シリアル値はExcelが内部で持っている万年カレンダーのようなもので、曜日のみならず、うるう年も管理しているので、うるう年の計算をしてチェックするような必要はありません。

Office TANAKA サイトも上記のURLページ以外にも多くの表示形式の記事があります。それだけ、表示形式や書式記号は深い、と言えるでしょう。

さて、冒頭に田中先生が表示形式についてかならず質問する、と書きましたが、この表示形式・書式記号の aaa を知っている人が年々「減って」きています。Excel 鉄板ネタの一つなのですが、本当にこれを知っている人が減っているのです。

あらためて、きっちりと伝えるべき技術は定番として伝え続けなければならないんだ、と感じる1件です。

ちなみに Power Query で日付から曜日を判定するには、WEEKDAY関数と似たような動きをする Date.DayOfWeek がヘルプの数式カテゴリから見つかりますが、ワークシート関数同様に TEXT 関数に相当するメソッドを使ったほうがよさそうです。Date.ToText で "ddd" で書式設定して日本語の曜日を表示させることが可能です。

Date.ToText のヘルプ
(Power Query が機械翻訳で電源のクエリになっているのはご愛嬌）

なお、この曜日については SharePoint のリストでも同様の動きと扱いをすることができます。これもチェックですね。

ExcelユーザーのためのSharePointリスト「集計値」列

Read More

Excel でゼロ パディングしたい – 0で埋める

一般にプログラミングの世界などで、足りない桁数分「0」を追加して長さを合わせることを「ゼロ パディング」と言います。
Excel では 0001 が 1 になる、といった「ゼロがなくなる」ことがよく話題になります。これをゼロ サプレスといいますが、あまりこちらの言葉は聞きませんね。
今回は Excel でのゼロ パディングの3つのポイントをご紹介します。

文字列設定で反映

0001 を 1 ではなく 0001 としてセルで扱うとなると、いくつかの方法があり、そのひとつは、入力されたデータを文字列として扱う方法です。
そのため、セルの書式を「文字列」に設定することが「第1のポイント」です。

• 設定したいセル・範囲を選択する
• Ctrl+1 で「セルの書式設定」ダイアログの「表示形式」で「文字列」を選択して [OK]

これで、 0001 と入力しても、1 になることはありません。シングルコーテーションを 0001 の前に入力する方法も紹介されることが多いのですが、セル書式設定本来の機能からすると、入力するセルの位置・範囲が決まっているのであれば、セルの書式設定による文字列の設定が基本です。（シングルコーテーションによる文字列入力については Office TANAKA のこちらの記事を参照ください。)

表示形式で設定

ですが、「ゼロ パディング」という意味は、桁数が決まっていて、足りない分だけ 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セミナーを受けた後は、これさえあれば何もいらない。

Read More