セルの値に基づくドロップダウンリストでExcelフィルターを作成する方法

ドロップダウンリストフィルターとは、重複のない一意の項目名のリストのことです。ドロップダウンリストから任意の項目を選択すると、その選択に関連するデータだけが表示されます。この記事では、Excelでセルの値に基づいたドロップダウンリストフィルターを作成する方法を、ステップバイステップでわかりやすく解説します。

以下のリンクから練習用のExcelファイルをダウンロードして、実際に操作しながら学ぶことができます。

セルの値に基づくドロップダウンリストでExcelフィルターを作成する手順

ステップ1: ドロップダウンリスト用の一意のリストを作成する

ドロップダウンリストフィルターを作成するには、まず重複のない一意のリストを作成する必要があります。その後、このリストを基に残りの作業を進めていきます。

それでは、一意の項目リストを作成しましょう。

重複のない一意のリストを作成する手順は以下のとおりです。

❶ まず、データテーブルから項目をコピーします。ここでは例として、データテーブルのカテゴリ(Category)列から項目を抽出します。

❷ 一意のリストを作成したいデータ範囲を選択し、[データ] > [重複の削除]に移動します。

❸ [重複の削除]ダイアログボックスが表示されます。設定内容をご自身の希望どおりに確認し、問題なければ[OK]をクリックします。

これで一意の項目リストが完成しました。次に、ドロップダウンリストフィルターを追加します。

❹ 任意のセルを選択し、[データ] > [データの入力規則] > [データの入力規則]に移動します。

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

❺ [設定]タブで、[入力値の種類]ボックスから[リスト]を選択します。

❻ [元の値]ボックスに、一意のリストを作成したセル範囲を指定します。

❼ [OK]をクリックします。

最後に、下の画像のようにExcelにドロップダウンリストフィルターが作成されます。

関連記事: Excelで一意の値を使ったドロップダウンリストを作成する方法(4つの方法)

ステップ2: ドロップダウンリストフィルターを機能させる

ドロップダウンリストフィルターを追加できました。次に、作成したフィルターを使って、既存のデータテーブルからデータを抽出していきます。

そのために、メインのデータテーブルに3つの補助列(ヘルパー列)を追加する必要があります。ここでは、それぞれ行番号(Row SL)、一致(Matched)、並び替え(Ordered)という名前を付けます。

1つ目のヘルパー列: 行番号(Row SL)

この列には、データテーブルの各行の連番を格納します。手順は以下のとおりです。

❶ セルF5に次の数式を入力します。

=ROWS($E$5:E5)

ROWS関数の引数には配列を指定します。各要素の意味は以下のとおりです。

• $E$5は行番号列の最初のセルです。F4キーを押すことでドル記号($)を追加し、セル参照を固定できます。
• E5も行番号列の最初のセルです。

この数式は、セル$E$5からE5までの距離(行数の差)を計算しています。フィルハンドルをセルF5からF12までドラッグすると、$E$5は固定されたままですが、E5は徐々に変化していきます。2つのセル参照の間隔が広がっていくため、結果としてデータテーブルの行番号が自動的に取得できます。

💡 ポイント: 行番号は手動で入力しても問題ありません。

❷ Enterキーを押して数式を実行します。

❸ フィルハンドルをセルF5からF12までドラッグします。

2つ目のヘルパー列: 一致(Matched)

この列では、セルK4のドロップダウンリストフィルターで選択した項目と一致する行の番号のみを返します。

手順は以下のとおりです。

❶ セルG5に次の数式を入力します。

=IF(B5=$K$4,F5,"")

上記の数式の各要素について説明します。

• B5は、ドロップダウンリストフィルターで選択した項目と照合する最初の項目のセルアドレスです。
• $K$4は、ドロップダウンリストフィルターがあるセルのアドレスです。
• F5は、B5と$K$4が一致した場合に返す値のセルアドレスです。
• ""(空文字列)は、B5と$K$4が一致しない場合に空白を返すために使用します。

❷ Enterキーを押します。

❸ フィルハンドルをセルG5からG12までドラッグします。

3つ目のヘルパー列: 並び替え(Ordered)

2つ目のヘルパー列「一致」では、行番号が連続せず、飛び飛びに表示されることがあります。行番号を順番に隣接したセルへ整列させて表示させるために、「並び替え」列が必要になります。

手順は以下のとおりです。

❶ セルH5に次の数式を入力します。

=IFERROR(SMALL($G$5:$G$12,F5),"")

• $G$5:$G$12は、SMALL関数が最小値を探すセル範囲です。
• F5は、SMALL関数が小さい順に数値を取り出すのに役立ちます。F5には1が含まれており、下方向にコピーされるたびに値が1ずつ増加するため、n番目に小さい値を順番に取得できます。
• ""は、SMALL関数が検索対象の値を見つけられずエラーが発生した場合に、IFERROR関数によってセルを空白のままにするために使用します。

❷ Enterキーを押して数式を実行します。

❸ 最後に、フィルハンドルをセルH5からH12までドラッグします。

これでヘルパー列の準備がすべて完了しました。

関連記事: Excelでフィルター付きドロップダウンリストを作成する方法(7つの方法)

ステップ3: ドロップダウンリストフィルターの実行

それでは、ドロップダウンリストフィルターを実際に動かしてみましょう。

❶ データテーブルを別の場所にコピーします。次に、[クリア] > [内容のクリア]コマンドを使用して、コピー先の内容をすべて消去します。コピーしたテーブルの全セルを選択してDeleteキーを押すことでも同じ操作ができます。

❷ コピーしたデータテーブルの最初のセルに、次の数式を入力します。

=IFERROR(INDEX($B$5:$E$12,$G5,COLUMNS($M$5:M5)),"")

• $B$5:$E$12は、元のデータテーブルのセル範囲です。
• $G5は、2つ目のヘルパー列の最初のセルです。
• $M$5:M5は、コピーしたデータテーブルの最初の列のセル範囲です。
• ""は、ドロップダウンリストフィルターで選択した項目に該当するデータがない場合に、IFERROR関数によってすべてのセルを空白にするために使用します。

❸ Enterキーを押して数式を実行します。

❹ コピーしたデータテーブル全体にフィルハンドルをドラッグし、上記の数式をテーブル内のすべてのセルに適用します。

関連記事: Excelのドロップダウンリストが動作しないときの対処法(8つの問題と解決策)

まとめ

今回は、Excelでセルの値に基づいたドロップダウンリストフィルターを作成する手順を段階的に解説しました。「重複の削除」「データの入力規則」に加え、ROWS・IF・SMALL・INDEXなどの関数を組み合わせることで、選択した項目に応じたデータを自動的に抽出できます。記事に添付されている練習用ワークブックをダウンロードして、ぜひ実際に試してみてください。ご質問がある場合は、下のコメント欄でお気軽にお尋ねください。できるだけ早く回答いたします。また、当サイトExceldemyにもぜひお越しください。

関連記事

• Excelで複数選択可能なドロップダウンリストを作成する方法
• Excel VBAで複数の依存型ドロップダウンリストを作成する(3つの方法)
• Excelのドロップダウンリストから複数選択する方法(3つの方法)
• Excelのドロップダウンリストを自動更新する方法(3つの方法)
• Excelで複数選択リストボックスを作成する方法