久しぶりにエクセルをがっつり設定してみました。

普段からエクセルを常用はしておりますが、大体は数字をまとめるための四則演算の関数やデータを自動計算してくれるvlookup関数くらいまででしたが、今回は人力でチェックする時間を省くという目的のために「入力規則」を設定して、所定のフォーマット以外入力をさせないエクセルを作りました。

入力規則でここまで出来るのか〜ということを調べながら実践したことを共有します。

リスト形式

まずは、入力規則の基本から。

このような、リスト形式、htmlだとプルダウンメニューと言ったりすると思います。

1. リストの中の要素を別のセルで作る。（後で非表示にするか、別シートなどで）
2. データ＞入力規則
3. 許可：リスト
4. 元の値：1で作成したセルを選択

でリスト形式の入力規則が作成出来ます。

文字数制限

1. データ＞入力規則
2. 許可：文字列（長さ指定）
3. データ：次の値以下
4. 最大値：50

これで50文字以下の設定が出来ます。

半角、全角の判断はなく、改行も一文字として判別され、上記のケースですと最後の7入力した段階でエラーが出ます。

URL形式

1. データ＞入力規則
2. 許可：ユーザー設定
3. 数式：=AND(LEN(セル名)=LENB(セル名),ISERROR(FIND(” “,セル名)))

タイトルにも上げた設定ですが、正確には、全角と半角スペースを許可しない設定になります。半角英数字と記号は許可されるので実用性ではこれで十分と判断しました。

ユーザー設定を選択するとエクセルが持っている各種関数をそのまま使えます。

LEN関数はそもそも、バイト数をカウントする関数でLENBは、半角を1バイト、全角を2バイトとカウントします。これを入力規則に応用することで、LEN=LENBが同じでないといけない。つまり、全角が入っているとLENBの値が大きくなってしまうので、エラーが出るという設定になります。

そこに、半角スペースを見つけたらエラーを出す「ISERROR(FIND(” “,セル名))」という記述を書き、どちらか一つで許可しないようにANDで囲います。

ORで囲んでしまうと、全角文字かつ半角スペースの両方がないとエラーが出ないので注意です。

他にもいろいろ

• 整数
• 少数点数
• 日付
• 時刻

といった形式で制限が出来つつ、ユーザー設定で関数を駆使すれば、もっといろいろことが出来そうですね。

エラーメッセージの設定

デフォルト設定ですと、間違っていることしか分からない不親切なメッセージになります。

1. データ＞入力規則
2. エラーメッセージタブ

で設定することが出来、入力規則に応じたメッセージを出して上げれば親切になります。