業務が捗るExcel術。データ収集は入力規則で効率的に！

クライアントからデータ収集の依頼があった時、データ収集を一人で行うと時間がかかってしまうことがあります。

そんな時はチームを作って情報を収集するのですが、収集された情報が、全角や半角が混在したり、数値を入力すべきところに文字が入力されているなど、フォーマットが揃っていなくて後から苦労するということがあります。

そんなときに使えるのがExcelの入力規則機能です。しかし、ただ入力規則機能を使っても一般的なことしかできません。今回は入力規則をカスタマイズしてあらゆるデータ入力のフォーマットを揃える方法を解説します。

ユーザー設定の入力規則

Excelの入力規則は、セルに決まったデータしか入力できなくする機能です。入力規則を設定したいセルを選択し、データタブの入力規則から設定していきます。

今回注目するポイントは、設定タブの中の入力規則の種類のユーザー設定です。ユーザー設定では数式を設定できます。

数式に設定した数式によって、あらゆる入力の法則を、Excelの関数で作った計算式として表現することができるようになるのです。

あいまいな条件

入力規則で主に使う関数はCOUNTIF関数です。COUNTIF関数の書式は次のとおりです。

=COUNTIF(対象セル,条件)

本来、COUNTIF関数の「,」の前は範囲を入力しますが、入力規則のユーザー設定で使う場合は1つのセルになります。COUNTIF関数の条件に、そのセルに入力したい値の条件を設定し、その条件に当てはまれば1という数字に、当てはまらなければ0という数字が求まります。例えばセルA1に「済」としか入力できないようにする場合は入力規則をセルA1に設定し、次の計算式を設定します。

=COUNTIF(A1,”済”)=1

このような目的でCOUNTIF関数を使う場合、条件ではあいまいさを表すことが必要で、その時によく使うのがワイルドカードです。ワイルドカードとは「なにか」「なんでも」を表します。ワイルドカードは2通りあります。また、条件が数字の場合、不等号を使うこともできます。

ワイルドカード「*」

「*」は何かの文字列を表します。長さは0文字以上で入っていなくても条件にヒットします。「東京*」という条件にすれば「東京何か」を探します。「東京都」も「東京都葛飾区」も「東京タワー」も「東京」も全部一致します。「ぶらり東京」は「東京なんとか」ではないので一致しません。つまり「東京*」は「東京で始まる」と読みかえることができます。「東京が含まれる」という条件ならば「*東京*」とすれば「なんとか東京なんとか」になります。

ワイルドカード「?」

「?」は何かの1文字を表します。必ず1文字は入っていなければ条件にヒットしません。「東京?」にすれば「東京都」や「東京人」は一致しますが「東京」や「東京の人」は一致しません。そこに1文字あるというのが条件になります。「東京都??区」では「東京都」のあとに2文字があってその後に「区」が必要なので、「東京都板橋区」「東京都新宿区」は一致で「東京都北区」「東京都町田市」は一致しません。

不等号

不等号は「>」や「<」、「<=」や「>=」を使います。一般的には不等号の左側を比較対象、右側を条件とすることが多いですが決まりではありません。「A1>=2」ではセルA1が2とイコールか2よりも大きい、つまり「セルA1が2以上」の条件になります。イコールが入るものと入らない場合で、その数値が入るかどうかを表すと思えると良いでしょう。

COUNTIF関数を使ったおススメの入力規則のユーザー設定10個

ではここから実際にユーザー設定に設定する計算式を全部で10個紹介していきます。設定するセルはセルA1とします。

何かが入力されている

=COUNTIF(A1,”*?”)=1

とにかく1文字以上が入っている条件です。クリックし入力し始めた以上、何かを入力しなければなりません。

2文字入力されている

=COUNTIF(A1,”??*”)=1

ほしい文字数の分、「?」を入力すればその文字数以上を入力するとすることができます。

メールアドレス

=COUNTIF(A1,”*@*.*”)=1

メールアドレスは、必ず「なんとか@なんとか.なんとか」になっているのですが、そのパターンを条件にしたものです。

URL

=COUNTIF(A1,”http*://*.*”)=1

URLには、http://とhttps://があるのでsが入っても入らなくてもよく、その後に.が含まれたものです。

郵便番号

=COUNTIF(A1,”???-????”)=1

3文字-4文字の組み合わせです。

電話番号

=COUNTIF(A1,“??*-?*-????”)=1

2桁以上-1桁以上-4桁という形です。

東京か神奈川で始まる

=OR(COUNTIF(A1,”東京*”)=1,COUNTIF(A1,”神奈川*”)=1)

OR関数でまたは、を表現します。「東京で始まるか、または、神奈川で始まる」を表現しています。

先頭がAと3桁の数値

=OR(COUNTIF(A1,”東京*”)=1,COUNTIF(A1,”神奈川*”)=1)

OR関数でまたは、を表現します。「東京で始まるか、または、神奈川で始まる」を表現しています。

Aに続いて3文字の数値

=AND(COUNTIF(A1,"A???")=1,NOT(ISERROR(VALUE(RIGHT(A1,3)))))

文字と数値の組み合わせは、かなり複雑になりますが実現可能です。1文字目がAで3文字分の何かという条件と、後ろの3桁が数値として成立しているかの、なおかつ条件で、ANDで結ばれます。複雑なのは後ろ3桁が数値かどうかを判定する条件で、右側3文字をRIGHT関数で取り出し、それをVALUE関数で変換すると数値ではない場合はエラーとなり、ISERROR関数を使うとTRUEになるので、そうではないものを探すのでNOTで論理を逆にするという処理をしています。

A列の合計がセルE1に入力されている値未満

=SUM(A:A)<$E$1

A列の合計がセルE1に入力されている値を超えないようにする方法です。セルA1にこのような条件を設定しても循環参照にはなりません。

条件付き書式にも応用する

条件付き書式では、条件に当てはまった場合に書式を変更することができます。もし条件に当てはまらなかったら赤い文字にするなどの警告表示をすることが可能です。その場合にも今回の計算式が役立ちます。ホームタブの条件付き書式の新しいルールをクリックします。

数式を使用して、書式設定をするセルを設定をクリックし、次の数式を満たす場合に値を書式設定に数式を入力します。入力規則と違い、「当てはまらなかったら」の条件になることが多いので、その場合はNOT関数で論理を逆にします。

まとめ

今回は、入力する際にルールを設定できる、入力規則のユーザー設定に設定する数式の解説をしました。このような入力を制限することで無駄な入力ミスすることもなくなり、入力者にとっても楽になるはずです。一か所に設定すればコピーして使うこともできます。Excelで情報をまとめるツールを作る時には覚えておきたい機能のひとつです。