Excelで３階層で絞り込み表示するプルダウンを作成

2024年5月28日 10:12

やりたいこと

エクセルのプルダウン入力を大分類＞小分類＞商品名の３層で絞り込み表示して入力できるようにしたい。
大分類の選択で小分類を絞り込み表示して、大分類と小分類の選択で、商品名を絞り込み表示するプルダウンを実装します。
※Excel2021とMicrosoft365バージョンで動作確認しています。
※Excel2019以前のバージョンではFILTER関数が使えないので動作しません。

デモ画面

デモ画面は、大分類、小分類、商品名のある商品マスタ（カフェメニュー）を準備して、入力フォームの大分類の選択で小分類を絞り込み表示し、大分類と小分類の値が一致した商品名を絞り込み表示するという機能を実現しています。

設定方法の説明

ステップ１（テーブル化）

商品マスタ（大分類、小分類、商品名、価格）のデータを準備します。
商品マスタの入力範囲を選択した状態でメニューから「ホーム＞スタイル＞テーブルとして書式設定」を選択して、任意の書式で商品マスタのデータ領域を「テーブル化」します。
選択範囲の1行目が「見出し行」になる様に範囲を指定して下さい。

ステップ２（列に名前の設定）

テーブル化した商品マスタの先頭行を用いて各列に名前を付けます。
商品マスタ範囲の任意の位置を選択した状態で、「数式＞名前の管理＞選択範囲から作成」と操作して「選択範囲から名前を作成」のダイアログから「上端行」を選択してOKボタンをクリックします。
これで商品マスタの各列に見出し行の名前が定義されました。
名前定義で、各列のセル範囲を名前で参照することができます。
(例）大分類＝セル範囲（A13:A99）

ステップ３（フィルター領域の設定）

テーブル化した商品マスタから、大分類、小分類に一致するデータを抜き出すフィルター領域を作成します。
大分類の1行目（Ａ７セル）に「=UNIQUE(大分類)」と入力します
小分類の1行目（Ｂ７セル）に「=UNIQUE(FILTER(小分類,大分類=A3,""))」と入力します。
商品名の1行目（Ｃ７セル）に「=UNIQUE(FILTER(品名,小分類=B3,""))」と入力します。

UNIQUE（セル範囲）関数は、セル範囲から重複しない値を抽出します。FILTER（セル範囲, 条件, 一致しない場合の値）関数は、セル範囲から、条件に一致する値を抜き出します。
=UNIQUE(FILTER(品名,小分類=B3,""))という関数は、FILTER関数で品名のセル範囲から、小分類のセル範囲の値がＢ３セルの値と一致する値だけを抜き出して、UNIQUE関数で重複を取り除いたリスト値にします。
※Excel2019以前のバージョンではFILTER関数が使えないので動作しません。