Excelに入力してほしくない文字があったとします。例えば数字だけ入れてほしいのに文字を入れられちゃうとか。
金額だけ「300」って入れてもらえばいいのに「300円(入金済み)」とか余計な情報まで入ってしまえばそれは数値として成立しなくて足し算の対象外になるし、単純な四則計算であれば文字列を足し算しちゃだめっていうことでVALUEエラーが出ます。
そういうことを防ぐ機能はなんでしょう？
そうです。入力規則ですね。その入力規則を徹底的に使いこなして、絶対に入力してほしくない文字を絶対に入れられないようにする、というのがこの記事の狙いです。
ユーザー設定の入力規則
Excelの入力規則は、データタブの入力規則で呼び出します。
これは入力規則の設定タブの画面です。
入力値の種類からどんな風に入力させないようにするかを選びますが、文字列であれば文字数を規定したり、いくつからいくつまでの数値だけが入力できるようにするといったことができますね。また、本当の入力規則という意味とは若干離れますが、リストから選択することもできます。
これらの入力規則では、必ず大文字とか、「東京都」から始まるもの、のような特殊な条件を設定することができません。
そんな時は、入力値の種類を「ユーザー設定」にして、入力してほしくない法則を計算式にして、数式欄に入力します。
計算式？法則？
となっている方も多いと思います。
それを簡単に解説します。
ユーザー設定の数式欄に入力する計算式
おそらく、計算式を考えるのは大変なので、ちょっとだけ解説したあと、そのまま使える計算式をカタログにします。
まず、計算式の解説です。
主に使う関数はCOUNTIF関数かSEARCH関数です。
その前にワイルドカードを解説します。
ワイルドカード
なんとかの文字列を表すものがワイルドカードです。何の文字でもいいのです。
検索する時に使えるのですね。
ワイルドカードは2種類しかありません。
「*」がなにかの文字列、「?」がなにかの1文字です。
「*市*」なら「なんとか市なんとか」なので、住所かどうか検索するのに使えます。
「?学期」なら「なんとかの一文字のあと学期」になります。
「*市」なら最後に「市」が入る文字列、「市*」なら最初に「市」が入る文字列、「*市*」であれば「市」が含まれる文字列ということになります。
COUNTIF関数
セルA1が、「市」が含まれる文字列かどうか調べるには次の計算式です。
=COUNTIF(A1,”*市*”)>0
もし、セルA1に「仙台市宮城野区」と入力すれば、それは「なんとか市なんとか」なので、カウントが1になり、0より大きくなるのでTRUEになります。もし「市」が含まれていない文字列なら0になり、0よりも大きくないのでFALSEになります。
しかし、入力データが「123」の数値だった場合で次の計算式ではおかしな動作になります。
=COUNTIF(A1,”1*”)>0
「123」は「1」で始まるから、TRUEにならなければならないのですが、FALSEになります。
これはCOUNTIF関数で「”1*”」と入力設定しているのですが、「””」で囲んでいるために、文字列でなければ一致しないと判断してしまうからです。
逆に、文字列だけかどうかをこの方法で判定できます。
SEARCH関数
数値入力の「123」も文字列になる「12a」のどちらもOKにしたいのであれば、SEARCH関数を使いましょう。SEARCH関数は、文字列の中から指定した文字を探してそれが何文字目か教えてくれ、もし文字列が見つけられなければエラーになってくれます。これってFIND関数でもできますよね。でもFIND関数はワイルドカードが使えないのです。SEARCH関数とFIND関数の動作はそっくりなのですが、ワイルドカードが使えるかそうかの違いがあるのです。
セルA1のなかで、「市」が何文字目かSEARCH関数で調べるには次の計算式です。
=SEARCH(“*市*”,A1)
この計算式でエラーなら「市」が含まれていないと判断します。そこでエラーかどうか調べるISERROR関数を使います。
=ISERROR(SEARCH(“*市*”,A1))
これで文字列が見つからずにエラーとなったら、TRUEになります。
いやそうではありませんね。文字列が見つからなかったらFALSEになってほしいので逆の答えになっています。TRUEとFALSEを逆転させるためにNOT関数を使います。
=NOT(ISERROR(SEARCH(“*市*”,A1)))
この方法であれば、COUNTIF関数で一致しない数値の「123」でも一致します。
入力規則のユーザー設定カタログ
では、上記のSEARCH関数やその他の関数を使って、入力規則の設定例を紹介します。
設定をするセルはA1です。
何か入力されている（空白以外）
=NOT(ISERROR(SEARCH(“*”,A1)))
何とかという文字列です。1文字以上、何かが入っていればTRUEになります。
2文字以上入力されている
=NOT(ISERROR(SEARCH(“??*”,A1)))
文字数の分??にすればその文字数以上を指定できます。
文字列の設定でもできますが、文字列の設定では文字数の設定だけで、何かで始まる何文字の文字数というのは指定できないです。
2文字以上で何かで始まる/終わる
=NOT(ISERROR(SEARCH(“東京???*”,A1)))
東京の後、3文字以上が入力されているの場合はこの書式です。
=NOT(ISERROR(SEARCH(“???*東京”,A1)))
3文字以上の何かの最後に東京で終わる文字列はこれです。
メールアドレス
=NOT(ISERROR(SEARCH(“*@*.*”,A1)))
@の前にアカウント名、@の後ろに.を含んだドメイン名です。
URL
=NOT(ISERROR(SEARCH(“http*://*.*”,A1)))
http://とhttps://があるので*でs入っても入らなくても良しとして。その後に.が含まれたアドレスという形です。
郵便番号
=NOT(ISERROR(SEARCH(“???-????”,A1)))
3文字-4文字の組み合わせです。旧郵便番号を含める場合は下2桁はあってもなくてもよいとするので「???-??*」とします。
電話番号
=NOT(ISERROR(SEARCH(“??*-?*-????”,A1)))
日本にはいろいろな局番の形式があるので、2桁以上-1桁以上-4桁という形です。しかし、この設定で入力させるのは入力者にとっては酷ですね。
東京か神奈川で始まる
=OR(COUNTIF(A1,”東京*”)>0,COUNTIF(A1,”神奈川*”)>0)
SEARCH関数では、式が長くなるのでCOUNTIF関数を使っています。OR関数でまたは、を表現します。
間に空白が入っていない
=ISERROR(SEARCH(“* *”A1))
空白が入っていれば、エラーになるので、NOT関数を使わずにそのままISERROR関数の答えでいいです。
全角のみ
ここからはワイルドカードではない入力規則です。
=A1=JIS(A1)
JIS関数で全角に変換した結果が変換前と同じなら全角です。同じようにUPPER関数で大文字かどうかも判定できます
リストに未入力の文字
=COUNTIF(A:A,A1)<=1
A列にはじめて入力される場合、その文字のCOUNTIF関数の結果は2になるので1以下で判定します。
MATCH関数とISERROR関数でも作れますね。
数値
=ISNONTEXT(A1)
データの種類を数値にすればできますが。他のIS関数を使えばもっと面白いことができるかもしれません。
合計した結果が100未満になる
=SUM(A:A)<100
A列の合計が100を超えないようにする方法です。
日付・時間の入力規則
Excelの入力規則は、日付と時間の入力規則は設定上はできるようになっていますが、厳密な意味での日付、時刻は判定できません。
Excelは日時をシリアル値で管理して、実際には日時ではなく数値だからです。
2019年1月1日から12月31までの日付の入力設定をしたセルに、46300という数値を入力してもエラーになりません。
この判定をするにはVBAが必要で、セルの表示値で判定する自作関数をユーザー設定に設定すればできます。
条件付き書式への応用
この記事のテクニックは入力規則で有効に使える方法なのですが、条件付き書式でも使える書式です。エラーの値に色を付けたいのであれば、上記で紹介した論理は全部逆ですのでNOT関数を外す、または付けるでご利用ください。
まとめ
このような設定は、誰もが間違わないシート操作においてとても重要なものです。できればすべての入力シートの入力セルに設定したいくらいです。
でもそれは大変なので、このような設定をしたシートをあらかじめ作成しておいて、そのセルをコピーすれば設定されるといったようなものを作っておくとよいですね。その場合は条件付き書式も設定しておくとよいと思います。
しないよりしたほうがいいというものではなく、入力間違い防止のために絶対にした方がいいのです。
でも、作業に余裕があるかどうかで判断することになるのでしょうね。
コメント