【Excel】入力規則、条件付き書式を使ってデータの内容・形式を整える

---

Excelはクラウド上で共有し、複数人でデータを入力・統合することもできます。一方で、各人が好きなように入力してしまっては、データの形式がバラバラで、見づらく、フィルターなども機能しない表になってしまいます。

たとえば、以下のような営業成績表ですが、各項目の表記がバラバラで見づらくなっています。空白セルもあり、入力忘れか、該当なしかどうか判断も難しいです。

このようなことが起こらないようにあらかじめ入力規則、条件付き書式を使ってデータの内容・形式を定めておくことをおすすめします。

データの入力内容をリストから選択できるようにする

たとえば、以下の表のB列の「部署」では、部署名の表記がバラバラになってしまっています。これでは、部署名でフィルターをかけても正しく絞り込まれないため、表記を統一しなければいけません。

表記を統一するには、データの入力内容をあらかじめリストとして定義して、そこから選択できるようにします。

まず、営業成績表とは別に「リスト」というシートを作成し、そこに部署名の一覧を用意します。

部署名を入力する範囲を選択し、「データ」⇒「データの入力規則」⇒「データの入力規則」をクリックします。

「設定」の「入力値の種類」で「リスト」を選択し、「元の値」として先ほど作成した部署名の一覧を選択します。

「OK」をクリックすると以下のように「部署」の列のセルにプルダウンリストが表示され、ここから選択することができるようになりました。ちなみにこのプルダウンリストは「Alt + ↓」キーで選択することもできます。

このプルダウンリストに従って入力すれば、以下のようにデータの形式が整いました。

このプルダウンリスト以外の内容のデータを入力しようとすると、以下のようにエラーメッセージが表示されます。

名簿から氏名のリストを作成し、社員IDを自動入力する

次に氏名についても、部署名と同じようにリストを作成し、入力内容を制限したいと思います。ここではあらかじめ作成されている名簿を活用してリストを作成したいと思います。また、社員IDについては覚えていない人もいるため、名簿から自動的に入力されるようにします。

まず、名簿というシートを作成し、社員IDと氏名の書かれたリストを用意します。

氏名を入力するセルを選択し、「データ」⇒「データの入力規則」⇒「データの入力規則」をクリックします。

「設定」の「入力値の種類」で「リスト」を選択し、「元の値」として先ほど用意した氏名の一覧を選択します。

そうすると以下のように氏名をプルダウンリストから選択できるようになりました。

次に社員IDを氏名から自動入力されるように、XLOOKUP関数を使用します。セルC5に以下の数式を入力します。ここでVLOOKUP関数ではなく、XLOOKUP関数を使用しているのは、検索範囲である氏名が左端列にないからです。

=XLOOKUP(D5,名簿!$B$2:$B$19,名簿!$A$2:$A$19)

数式	役割
XLOOKUP(D5	セルD5の値を検索する。今回は「氏名」の記載されたセル。
名簿!$B$2:$B$19	検索範囲はセルB2~B19。今回は「名簿」の「氏名」の記載された列。
名簿!$A$2:$A$19	表示するデータはセルA2~A19の内、検索値に合致するデータのセル。今回は「名簿」の「社員ID」を表示させる。

そうすると以下のように氏名に対応する社員IDが表示されます。

その他の社員の氏名もプルダウンリストに従って入力することで、社員IDが自動的に入力されました。

この「社員ID」ですが、数式で自動入力するため、記入は不要である旨を明示したいです。そのため、「社員ID」の列を選択し、「データの入力規則」⇒「入力時メッセージ」で以下のように記載します。

そうすると以下のように「社員ID」のセルを選択すると以下のようにメッセージが表示されます。

整数のみを入力できるようにする

次に、「入社年次」「売上」「件数」については整数の数値データのみで入力できるように設定したいと思います。

整数でデータを入力する範囲を選択し、「データ」⇒「データの入力規則」⇒「データの入力規則」をクリックします。

「入力値の種類」に「整数」、「データ」に「次の値以上」、「最小値」に「0」を入力します。これで、0以上の整数のみ入力可能となります。

たとえば、文字列を含んだ値をセルに入力すると以下のようにエラーメッセージが表示されます。

整数を入力すれば、正しい単位で値が表示されるようにセルの書式設定を定めます。たとえば、入社年次は西暦で表示したいので、「0″年”」と書式を定めます。

その他、売上や件数についても以下のように書式設定を行います。

項目	書式設定
入社年次（～年）	0″年”
売上（～万円）	#,##0″万円”
件数（～件）	#,##0″件”

また、データの入力方法について捕捉したい場合は、「入力時メッセージ」で定めることができます。ここでは、売上の数値は万円未満は入力しないようにさせるため、以下のようにメッセージを表示させます。

「売上」のセルを選択すると、以下のようにメッセージが表示されます。

空白セルを強調する

次に、各社員が担当する商品について表すセルについて形式を整えたいと思います。以下の表では、担当を表す記号が様々用いられており、空白セルも存在しています。これを「〇、×」形式で入力させ、空白セルは強調するようにしたいと思います。

まず、入力内容を「〇」か「×」に形式をそろえたいと思います。入力するセルを選択し、「データ」⇒「データの入力規則」⇒「データの入力規則」をクリックします。

「入力値の種類」に「リスト」を選択し、「元の値」に「〇,×」と入力します。リストは他のセルから参照することもできますし、以下のように「,（コンマ）」で区切り、直接入力することもできます。

そうすると以下のように、プルダウンリストで「〇」か「×」かを選択することができます。

次に入力されていないセルを目立たせて入力漏れがないようにします。

空白セルを強調させたい範囲を選択し、「条件付き書式」⇒「新しいルール」をクリックします。

「指定の値を含むセルだけを書式設定」を選択し、「空白」を選択します。書式は任意のものを設定します。今回は空白セルは薄い赤色の塗りつぶしで表示されるようにします。

「OK」をクリックすれば、以下のように空白セルが強調され、入力漏れを視覚的に表すことができます。

もし、文字で空欄があることを示したい場合は、COUNTBLANK関数を使用します。COUNTBLANK関数は空白セルの数を数える関数で、空白セルが1つ以上ある場合は「空欄があります」というメッセージを赤字で表示します。

たとえば、セルM8に以下のように入力します。

=IF(COUNTBLANK(H8:L8)>=1,”空欄があります”,””)

空欄がある行については、以下のように「空欄があります」というメッセージが表示されます。

セルM8を他の行にもコピーします。他の行には空白のセルがないため何も表示されません。

このように入力規則、条件付き書式を活用することで、複数人で編集する表でも形式を整え、見やすく、加工しやすいデータにすることができます。

---