前回まではグラフの作り方についてまとめて紹介してきました。
グラフに関する記事も充実してきましたので、しばらくはExcelでデータを上手に活用するためのコツについて紹介していきます。
今回はExcelで入力規則を設定する方法を紹介していきます。
この記事を読むことで、
- 入力ミスをなくすことができる
- 入力の効率が向上する
といった効果を期待できます。
また、入力規則については、今回と次回の2回にわたって解説していきます。
今回は、入力規則を使ったことが無い方に向けた内容になっています。
入力規則で出来ること
「入力規則って何？」という方も多いと思います。
入力規則は、セルに入力できる項目を制限できる機能です。
設定することで、選択肢となるリスト（プルダウンリスト）を表示してくれます。
Excelでは、ドロップダウンリストと表現します。
入力規則の設定メニューです。設定タブでは、データ形式の設定を行います。
また、メッセージ表示の設定も行えます。
対象セルのIMEも自動で設定できます。半角や全角の指定をしておくと便利です。
入力規則では、以下の形式を設定することができます。
| 形式 | 詳細 |
|---|---|
| 数値 | 整数、小数点など |
| 時間 | 日付、自国 |
| 文字 | 文字数の制限 |
| リスト | 複数のデータ項目 |
このうちで最も使われる頻度が高く、効果が最も高いのがリストの設定です。
プルダウンリストの表示も、リスト設定でしか行えません。
今回はリストを設定する方法に限定します。
入力規則の設定方法と解除の方法
プルダウンリストを用いた入力規則を行うには、元データの準備が必要になります。
データ入力用の表と商品マスタ表を準備しました。
B列の商品CDにJ列の商品CDをリストに設定していきます。
元データの作成が済んだら、「データ」タブの「データの入力規則」をクリックします。
「データの入力規則」の設定ダイアログが表示されます。
「入力値の種類」で「リスト」を選択します。
「元の値」に条件を入力すると、プルダウンリストに表示するデータを設定できます。
入力規則を解除する方法は、「すべてクリア」をクリックします。
また、コピペする時には、入力規則のみを指定することもできます。
他のセルにプルダウンリストを活用したい時にはとても便利です。
リストを追加する方法
元の値を設定する方法については、5つの方法を紹介します
直接リストを入力
最も簡単な方法は、「元の値」にリストとなるデータを直接入力する方法です。
カンマ( , )でデータを区切ることが出来ます。
リストの内容を変更したい場合は、「データの入力規則」メニューから修正する必要があります。
セルを参照する
「元の値」にセル範囲を選択することで設定できます。
ここで、J2:H8 を範囲に選択します。
リストの内容を変更したい場合は、直接入力した場合と同じように、「データの入力規則」メニューから修正する必要があります。
名前の定義を活用する
名前の定義を活用すると、再設定を時にダイアログを再表示する必要がありません。
まずは、リストにしたいセル範囲に名前を付けます。ここでは「CDリスト」と名付けました。
ダイアログの元の値に「=CDリスト」と入力すれば、対象の範囲がリストとして選択されます。
メリットは、再び入力規則のダイアログを開いて再設定する必要がないということです。
直接入力や、セル範囲指定の場合は、設定された全てのセルに変更を適用しなければなりません。名前の定義の場合は名前の管理で修正するだけで全てのセルに変更が反映されます。
デメリットは、多用しすぎると名前の管理が難しくなるということです。 また、リストを更新したい場合は、名前の管理で範囲を修正しないといけないこともデメリットになります。
名前の定義に関しては、過去に記事を掲載しています。
関数を使う
元の値に関数を設定することも出来ます。
OFFSET関数とCOUNTA関数を組み合わせて使います。
リストを追加しても、関数で範囲が自動追加されますので、メンテナンスフリーです。
マスタ表にデータを追加します。
このように、入力規則にも自動でデータが追加されていることが分かります。
テーブル機能を使う
元のマスタ表がテーブル設定されていれば、テーブルの名前でリストを設定することができます。
テーブルを活用する場合は、初期設定の名前ではなく、自分で識別できる名前にしておきましょう。
「テーブルデザイン」タブを選択し、テーブル名を編集します。
入力規則の設定でテーブルを活用する場合は、INDIRECT関数とテーブル内の列名を指定する必要があります。
慣れるまでは、使いにくいかもしれませんが、2020年時点では最もメンテナンスフリーな設定方法ではないかと思います。
Excel2003以前ならば、テーブル機能はないのですが、Excel2007以降であればテーブル機能を使えますので、テーブル機能を活用すべきです。
リストの追加を簡単に行う方法
セル範囲からシンプルに入力規則を設定した後に、リストを簡単に追加する必要があります。
それは、マスタ表の元データについて、一部をドラッグで動かせば設定できます。
メニューを開く必要はありませんが、操作ミスが発生すると、リストが崩れることがありますので、注意が必要です。
動画を添付したツイートを貼り付けておきますので、よかったら参考にしてください。
入力規則をセル範囲で設定した場合、
— カピオ@システム担当の父親 (@sys_dad) August 13, 2020
リストを簡単に追加できる方法です。
設定画面開かなくてもいいので楽ですが、間違いやすいので要注意です。 pic.twitter.com/w24jWI9V1J
まとめ
入力規則は、入力を省略できるプルダウンリストを活用することができるので、Excelでデータを入力・集計する方にとっては、身に付けておいた方がよい機能です。
VLOOKUPなどの検索関数と併用すると、入力の効率が更に上がります。
次回は、2段階で入力規則を設定する方法について紹介します。
本日も最後までありがとうございました。