リンクユニット

Windows 10 & Office 2016 Excel 2016

データの入力規則を使った絞り込みリストの作成

投稿日:2017年1月9日 更新日:

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

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

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

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

入力規則の絞り込み

参考ドロップダウンリストから値を選択して入力できるようにする方法は、以下の記事で解説しています。

入力規則のリストが可変(増減)するならテーブルに変換して自動修正

Excelの入力規則でドロップダウンリストから選択できるように設定することは多いですね。 でも、そのリストが増減する場合、その度に入力規則の ...

続きを見る

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


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

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

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

リスト作成

セル範囲に名前付け

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

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

[部署]とします。

名前付け

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

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

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

名前の付け方ルール

選択範囲から作成

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

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

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

選択範囲から作成

選択したセルから名前を自動的に生成します。

通常は、最上部の行または左端の列を選択します。

[数式]タブの[定義された名前]グループにある[選択範囲から作成]

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

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

[選択範囲から名前を作成]ダイアログボックス

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

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

名前の管理を確認

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

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

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

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

範囲に名前を付ける方法を参照してください。

名前の管理

入力規則の設定

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

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

データの入力規則

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

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

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

[OK]ボタンを押して閉じます。

データの入力規則ダイアログボックス

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

リストから選択

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

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

そして、[入力値の種類]のプルダウンメニューから[リスト]を選択します。

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

=INDIRECT($A2)

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

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

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

INDIRECT 関数 - Office サポート(Microsoft)

[OK]ボタンをクリックして閉じます。

データの入力規則ダイアログボックスの元の値に関数

下のようなメッセージが表示されたら、[はい]をクリックします。

メッセージウィンドウ

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

リストから絞り込み選択

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

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

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

[挿入]タブの[テーブル]

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

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

[テーブルの作成]ダイアログボックス

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

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

テーブルの作成

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

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

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

[デザイン]タブの[テーブルスタイル]グループの[その他]ボタン

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

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

テーブルのスタイルの一覧

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

[テーブルスタイルのオプション]グループにある[フィルターボタン]

以下のようになります。

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

スタイルをクリアしたテーブル

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

3つのテーブルを設定

作成したテーブルの確認

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

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

[数式]タブの[名前の管理]

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

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

[名前の管理]ダイアログボックス

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

[名前の編集]ダイアログボックス

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

[名前の管理]ダイアログボックス

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

D列の[第1営業部]に「緒方」を追加してみます。

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

テーブルに名前を追加

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

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

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

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

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

テーブルのサイズ変更

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

テーブルのサイズを縮小した後のドロップダウンリスト

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

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

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

不要な名前を削除

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

入力規則のリストが可変(増減)するならテーブルに変換して自動修正

Excelの入力規則でドロップダウンリストから選択できるように設定することは多いですね。 でも、そのリストが増減する場合、その度に入力規則の ...

続きを見る

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

範囲が可変する名前定義

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

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

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

コンボボックスの絞り込み

入力フォームなどで、よく絞り込みは使いますね。2つのコンボボックスが連結して動作します。 たとえば、営業部を選択すると、営業部の人の名前だけ ...

続きを見る

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

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

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

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

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

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

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

ミス入力があるワークシート

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

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

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

無効データのマーク

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

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

無効データのマークで点いた赤い丸

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

入力規則マークのクリア

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

入力規則マークのクリア

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

入力規則のエラーメッセージと無効データのマーク

Excelの[データ]タブの[データの入力規則]のメニューの中に[無効データのマーク]というのがあります。 これは入力規則に設定した以外の値 ...

続きを見る

関連入力規則関連の記事です。

データの入力規則のコピーと貼り付け

Excelの入力規則は、便利ですね。 ですが、この入力規則はコピー・貼り付けができるということは案外知られていないようです。 コピーした後に ...

続きを見る

重複データの入力を[データの入力規則]で禁止するには

Excelで1つの列に重複した値を入力できないように規制する方法を紹介します。 Excel2013で解説しますが、他のバージョンも同じ方法で ...

続きを見る

行と列が交差するセルの値を求める

Excelで下のようなマトリックス表があって、このマトリックス表の行と列が交差する値を、関数を使って自動的に求める方法です。 早見表などでも ...

続きを見る

データの入力規則を使ってIMEの入力モードを自動切り替え

Excelでは、[入力規則]を使って入力時の日本語入力をオンにしたり、オフにしたりすることができます。 この設定を行っておくと、入力時にIM ...

続きを見る

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

ドロップダウンリストから選択して自動的に背景色を付ける

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにす ...

続きを見る

Profile

執筆者/はま

もっと理解したいあなたへ

おすすめ記事と広告

-Windows 10 & Office 2016 Excel 2016

Copyright © 2001-2020 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2020 AllRights Reserved Powered by AFFINGER4.