ドロップダウンリストを2段階選択で絞り込むには（データの入力規則）

Excelでは入力規則と関数を使って、リストの中から1つ選択すると、次のリストは前のリストに該当する内容の中からしか選択できないようにすることができます。

Excel2016で解説しますが、他のバージョンでも同様の操作で作成することができます。

たとえば、下のように部署は［第1営業部］［第2営業部］［第3営業部］の中から選択できて、［第1営業部］を選択すると、［第1営業部］のメンバーしか選択できないようにすることができます。

入力ミスを減らすことができて便利ですし、入力するユーザー側も選択肢は少ない方が楽ですね。

2段階選択と呼ばれることもあります。

参考ドロップダウンリストから値を選択して入力できるようにする方法は、以下の記事でも解説しています。リストにテーブルを使った場合と、そうでない場合の違いを知っていただければと思います。

入力規則に使用するリストの作成

それほど難しくはないので、ぜひ挑戦してみてください。

まず、準備として入力規則に使用するリストの一覧を作成します。

セル［D1］からセル［F5］までがリストとなります。

セル範囲に名前付け

そして、このリストでそれぞれのセル範囲に名前を付けます。

セル［D1］からセル［F1］を選択して、［数式］タブの［名前の定義］をクリックします。

［部署］とします。

そして、同じようにセル［D2］からセル［D5］までを［第1営業部］、セル［E2］からセル［E5］までを［第2営業部］、セル［F2］からセル［F5］までを［第3営業部］という名前を付けます。

範囲に付ける名前は勝手に付けてはいけません。

必ず1行目の項目名と同じにします。

選択範囲から作成

D列、E列、F列の範囲に名前を付ける場合は、［数式］タブの［選択範囲から作成］をクリックすると効率的です。

名前を自動生成できます。

例えば、セル［D1］からセル［D5］までを選択します。

そして、［数式］タブの［定義された名前］グループにある［選択範囲から作成］をクリックします。

ショートカットキーは、［Ctrl］+［Shift］+［F3］です。

［選択範囲から名前を作成］ダイアログボックスが表示されます。

［上端行］のチェックボックスがオンになっていることを確認して［OK］ボタンをクリックします。

これで、［第1営業部］という名前のセル範囲が設定されます。

E列もF列も同じ操作でセル範囲に名前を付けます。

参考ここでのサンプルのように各列の行数が揃っている場合は、表全体を範囲選択して［選択範囲から作成］をクリックすると、まとめて名前を付けることができます。

名前の管理を確認

［数式］タブの［名前の管理］をクリックして、［名前の管理］ダイアログボックスが下のようになっていればOKです。

［名前の管理］ダイアログボックスは、［Ctrl］+［F3］でも表示することができます。

確認だけですので、［名前の管理］ダイアログボックスは閉じます。

参考範囲に名前を付ける方法は、名前ボックスを利用する方法もあります。

入力規則の設定

では、入力規則を設定します。

セル［A2］からセル［A7］までを範囲選択して、［データ］タブの［データの入力規則］をクリックします。

［データの入力規則］ダイアログボックスが表示されます。

［入力値の種類］のプルダウンメニューから［リスト］を選択します。

［元の値］には、［=部署］と入力します。

［OK］ボタンを押して閉じます。

これで、セル［A2］からセル［A7］までは、入力リストから部署名の選択ができるようになります。

参考［データの入力規則］ダイアログボックスの［入力値の種類］で［リスト］を選択すると、2つの設定項目が表示されます。

• 空白を無視する
• ドロップダウンリストから選択する

この2つの設定内容については、以下の記事で解説しています。

リストの元の値に［INDIRECT関数］を設定

今度は、セル［B2］からセル［B7］までを範囲選択して、上記と同様に［データ］タブの［データの入力規則］をクリックして、［データの入力規則］ダイアログボックスを表示させます。

そして、［設定］タブの［入力値の種類］のプルダウンメニューから［リスト］を選択します。

［元の値］には、［=INDIRECT($A2)］と入力します。

元の値には、名前を付けた範囲を指定するわけです。

そのためにINDIRECT関数を使います。

INDIRECT関数は、指定されている文字列への参照を返す関数です。

INDIRECT 関数 - Microsoft サポート（Microsoft）

［OK］ボタンをクリックして閉じます。

下のようなメッセージウィンドウが表示されたら、［はい］をクリックします。

部署選択で［第1営業部］を選択した場合は、［第1営業部］のメンバーのみが表示されるようになります。

入力規則のリストが増減する場合

もし、入力規則のリスト範囲が変動する場合は、リストをテーブルに変換すると自動的に増減できます。

セル［D1］からセル［D5］までを範囲選択して、［挿入］タブの［テーブル］グループにある［テーブル］をクリックします。

［テーブルの作成］ダイアログボックスが表示されます。

［先頭行をテーブルの見出しとして使用する］のチェックボックスをオンにして、［OK］ボタンをクリックします。

以下のようにテーブルが設定されます。テーブルを設定すると、自動的にスタイルが設定されます。

スタイルが設定されていても、何も問題はありません。

同じようにセル［E1］からセル［E5］まで、セル［F1］からセル［F5］までもテーブルの設定をします。

参考テーブルについては、以下の記事で解説しています。

スタイルをクリアする場合

もし、スタイルをクリアしたい場合は、［デザイン］タブの［テーブルスタイル］グループの［その他］ボタンをクリックして、

一覧から［なし］、または［クリア］をクリックしてください。

既定のスタイルを変更する場合も、この一覧から選択します。

テーブルのフィルターボタンも不要であれば、［デザイン］タブの［テーブルスタイルのオプション］グループにある［フィルターボタン］のチェックボックスをオフにします。

以下のようになります。

スタイルを設定していない場合と見た目は変わらないようになります。

ここでは、サンプルとして以下のようにスタイルを変更しておきます。

作成したテーブルの確認

［数式］タブの［定義された名前］グループにある［名前の管理］をクリックすると、以下のようになっていることを確認します。

テーブルが3つ作成されていて、それぞれのテーブルの範囲は、先に設定した［第1営業部］、［第2営業部］、［第3営業部］と同じになっています。

上記のままでもいいのですが、煩雑にならないようにするには、既存の［第1営業部］、［第2営業部］、［第3営業部］を削除して、［テーブル1］、［テーブル2］、［テーブル3］を［第1営業部］、［第2営業部］、［第3営業部］に変更します。

名前を変更したいテーブルを選択して、［名前の管理］ダイアログボックスの［編集］ボタンをクリックします。

［名前の編集］ダイアログボックスが表示されるので、ここでテーブルの名前を変更することができます。

結果、以下のようになります。

リストに氏名を追加して確認

D列の［第1営業部］に［緒方］を追加してみます。

ドロップダウンリストには、自動的に追加した名前が表示されます。テーブルは、自動的に拡張されるので便利ですね。

参考もし、テーブルの範囲が拡張されていないようであれば、［オートコレクトのオプション］の設定を確認してください。

テーブル範囲の自動拡張の設定

リストから氏名を削除する場合

［第2営業部］の［林田］を削除したい場合は、E列のテーブルを選択します。ここでは、セル［E1］からセル［E5］ですね。

右下隅の黒いボタンにマウスを合わせると、マウスポインタが斜めに変わりますので、その時に上へ向かってセル［E4］までドラッグします。

テーブルの範囲が小さくなり、プルダウンリストには、［林田］の名前は表示されなくなります。

削除しておいた方が分かりやすいのであれば、削除してください。

ただ［Delete］キーで名前を削除しただけでは、テーブルの範囲は変わりませんので、ドロップダウンリストには空白が表示されます。

空白を表示したくなければ、テーブルの範囲を設定してください。

参考データの入力規則の参照リストにテーブルを使用する方法については、以下の記事でも解説しています。

関数を使う設定方法は以下の記事を参照してください。

範囲が可変する名前定義

入力規則を使った絞り込みリストについては、以下の記事でも解説しています。

Excelの入力規則で絞込みリストを作成

以下は、Accessでの絞り込みリストの作成方法です。

無効データをチェックするには

いくら入力規則を設定しても、コピーして貼り付けられた値やオートフィルで入力された値は受け入れられてしまいます。

こういう時、無効な値をチェックできる機能があります。

［無効データのマーク］という機能です。

絞り込みの設定していても、無効なデータを入力されてしまうことはあります。

たとえば、A列で部署を選択して、B列で氏名を選択した後、再度　A列で部署を選択することができてしまいます。

だから、下のようになってしまうことがあります。

無効な値をチェックする方法です。

［データ］タブの［データの入力規則］の▼ボタンをクリックして、［無効データのマーク］をクリックします。

注意範囲を選択する必要はありません。

すると、入力規則に合わないデータには赤い丸が付きます。

部署と氏名が合わないセルに赤い丸が付くということです。

修正後、再度　［無効データのマーク］をクリックすると、赤い丸は付きません。

入力規則マークのクリア

また、無効データのマークをクリアするには、再度［データの入力規則］の▼ボタンをクリックして、［入力規則マークのクリア］をクリックします。

［無効データのマーク］については、以下の記事でも解説しています。

参考名前の削除は、［名前の管理］ダイアログボックスで行いますが、テーブルは範囲に変更しないと［名前の管理］ダイアログボックスから消すことはできません。

関連入力規則を使ってIMEの入力モードも設定できます。

入力規則（ドロップダウンリスト）と条件付き書式を組み合わせる方法も解説しています。

入力規則のリストにUNIQUE関数のスピル範囲演算子を使って範囲を指定すると、データを追加しても重複しない値が自動的にドロップダウンリストに追加されます。