Excelで先頭をゼロ埋めした数字項目に入力規則を適用する方法[VBAマクロもアリ]

　前回に引き続き、今回も Excel のちょっとしたアレについて（何）。

　最初にお断りしておきますが、今回の内容は、単に私の勘違いを解きほぐした経緯に過ぎないかも知れません。

　ですが、もしかしたら私と同じような勘違いをされる方も、万人にひとりくらいはいらっしゃるかも知れません。

　であれば、いちおう記事として残しておく意義もあろうというものです。

......あるといいなぁ。

　さてさて、シリーズの第２回目にして、早くも最終回である今回の内容は、「先頭をゼロ埋めした数字項目に対して、最小値と最大値を整数で指定してデータの入力規則を適用する方法」という、別にそんなの普通にできるんじゃねーの、と思ってしまいがちな、前回よりもさらにどうでもいい操作上の事柄です。

　題して『春二番！第２回、これ Excel でどうやってやるのかな大会！ドキッ！マクロもあるよ！』です。

　ちなみに、春二番って俗称らしいですよ？

※動作確認は Excel 2007, 2013 で行っていますが、それ以外のほとんどのバージョンでも普通に動作すると思います。そんな大したことしてませんからね。

読み込み中です。少々お待ち下さい

やりたいことを、画像でみてみよう

　はい、そんな訳でね。

　お前は一体、何をほざいておるのか、ということを、画像を混じえて見てみましょう。

　まずですね、下図の B 列のように、先頭（左端）がゼロ埋めされたコードがあったとします。この例で言えば、商品コードということになるでしょうか。

　このような場合、先頭のゼロが欠けてしまわないように、データベース上では NUMBER（数値）型ではなく CHAR（文字）型などで項目を定義して、ゼロ埋めされた値をそのまま格納するケースが、割りと良くあります。

　数値だと、先頭のゼロが消えちゃいますからね。

「いやいや、容量もったいないやろ。桁ぁ小さいからえぇやて？ アホか、桁小さかっても性能考えて、数値で持って必要だったら表示の時にゼロ埋めしぃや」という向きもあるかと思います。

　私も基本的には同感なのですが、スクラッチ（より今っぽく、オンプレミスと言うべきでしょうか）のシステムだと、既にこういう持ち方をしている場合が、実際に山ほどあるので仕方がありません。連携先のシステムまで含めて、全てのデータ定義を更新するのは現実的ではありませんからね。

　そして、Excel でも同様のノリで、「このコードは先頭をゼロ埋めしてんのかー。まぁ、データベース上でも文字列で定義されてる項目だし、こっちでも数値じゃなくて文字列にしとけばいいっしょ」みたい思い込み、セルの書式設定で表示形式を「文字列」に設定して、コード列の先頭行から順番に「001」「002」「003」と入力したとします。

　続いて、コード列に数字以外の文字（アルファベットや記号、日本語など）が入力されないように、リボンの「データ」から「データの入力規則...」を設定するとします。

　このサンプルでは、コードは 3 桁ということにしましょう。なので、まず入力値の種類で「整数」を選択し、「次の値の間」で最小値を「0」、最大値を「999」と入力します。

　このように設定すれば、数字以外が入力された時は、まんまとエラーが表示される筈ですね。

　それでは早速、データ列の値を一旦クリアして、意気揚々と入力してみましょ......おや？

　数字しか入力していないのに、なんかエラーになっちゃいました。

　それだったらってんで、先頭のゼロを省いて「1」だけ入力してみます。

ダ メ で し た。

　あれあれ、アルファベットや記号どころか、数字すら入力できなくなってしまいましたね。

　これでは何も入力できないので、困ってしまいます。

何が悪いのか

　ちょっと考えれば分かることですが、そもそもセルの表示形式に「文字列」を設定しているのが良くない訳でして。

「整数」で入力制限をしているところに、中身が「1」であれなんであれ「文字列」が入力されてしまっては、Excel としては「入力した値は正しくありません」と応じるしかありません。

　まぁ、要するに、入力規則で整数が指定されている訳ですから、揃えてセルの表示形式も数値にしましょうということですね。

　また、表示形式が「文字列」のセルに数字を入力すると、いちいち「数値が文字列として保存されています」というエラーがデフォルトではついてしまうので、見栄えも悪いことですし。

マクロを使わない場合

　さて、それでは、どう変更すれば良いのかを見ていきましょう。

　まずは、VBA マクロを使わない場合の操作です。

　先頭のゼロ埋めに関しては、表示形式をユーザー定義で「000（3 桁の場合）」とすれば良いだけですから、簡単ですね。

1. 適用したい範囲を選択して、右クリック等から「セルの書式設定」を選択し、表示形式のユーザー定義で「000」を設定します。
「000」を設定

2. 説明が重複しますが、コード列に数字以外の文字を入力できないように、リボンの「データ」から「データの入力規則...」を設定します。
データの入力規則を設定

3. 入力値の種類で「整数」を選択し、「次の値の間」で最小値に「0」、最大値に「999」を入力して「OK」ボタンを押下します。
データの入力規則を設定

　以上の操作で、入力可能な値を制限しつつ、先頭がゼロ埋めされた「001」「002」「003」のようなコードを入力できるようになりました（どころか、「1」「2」「3」と入力するだけで、自動的に先頭がゼロ埋めされるので、「文字列」を指定した場合よりも楽ちんです）。

　試しに、アルファベットやマイナスの数値を入力すると、入力規則に引っかかって、ちゃんとエラーになることが確認できます。

マクロを使う場合

　さて、VBA マクロを使う場合も、いちおう見てみましょう。

　例えば、アクティブシートの B 列「全体」に一括して、上で見てきたような表示形式＆入力規則を設定する場合は、こんな感じになります。

1.   ' B 列全体の表示形式に「000」を設定して、整数の 0 〜 999 以内で入力制限
2.   With ActiveSheet.Range("B1").EntireColumn
3.     .NumberFormatLocal = "000"
4.     With .Validation
5.       .Delete
6.       .Add Type:=xlValidateWholeNumber, _
7.            AlertStyle:=xlValidAlertStop, _
8.            Operator:=xlBetween, _
9.            Formula1:="0", _
10.            Formula2:="999"
11.       '以下のプロパティは、必要に応じて設定する
12.       .IgnoreBlank = True ' 空白を無視する
13.       .ShowInput = True ' 入力時メッセージの表示／非表示
14.       .InputTitle = "コード"
15.       .InputMessage = "000 ～ 999 の間で入力してください"
16.       .ShowError = True ' エラーメッセージの表示／非表示
17.       .ErrorTitle = "エラー"
18.       .ErrorMessage = "000 ～ 999 の間で入力してください"
19.       .IMEMode = xlIMEModeDisable ' 数値なので IME 使用不可
20.     End With
21.   End With
22.   
23.   ' ヘッダー行のみ表示形式を「標準」に戻したい場合は、以下を追加
24.   With ActiveSheet.Range("A1").EntireRow
25.     .Validation.Delete ' ヘッダー行の入力規則を削除
26.     .NumberFormatLocal = "G/標準" ' ヘッダー行の表示形式を「標準」に設定
27.   End With

　上記はあくまでサンプルですから、列全体ではなく任意の範囲を選択して適用することも、もちろん可能です。

おわりに

　やぁ、できたできた。

　ていうか、最初からやっとけって話ですね。

　いえ、あの、一瞬「ん？」ってなっただけで、すぐ分かりましたよ、モチロンです。

　いや、ほんとホント。

　とはいえ、Excel って、びっくりするような変態的テクニックを使っている例を良く見かけるので、これが最も適したやり方だとは言いませんが。

　ただ、余談になりますが、あんまり Excel に特化した学習をするのも、正直どうなんだろうなぁ、と個人的には思わないでもありません。

「毎日＆長時間、特定のアプリケーションを使いまくる」という特殊な環境でも無い限り、ひとつのアプリ操作をそこまで極めても、コストが効果に見合わないというか。

　Excel でしか通用しない変態的テクニックや VBA マクロって、とにかくツブしが効かないので、そこに学習コストをかけるくらいなら、どれでもいいからプログラム言語の習得に充てた方が、知らないアプリでもなんとなく何やってんだか分かるようになるので、遥かにコストパフォーマンスが高いんじゃないかなぁ。

　所詮は、いちアプリケーションですしね。

　資格をエサにボロい商売をしている連中を喜ばせるのもアホらしいですし、必要になった時に調べるくらいのお気楽な心構えで充分ではないかと。