1. Home » 
2. Excel講座の総目次 » 
3. Excel基本講座の目次 » 
4. 連動するドロップダウンリストを作成する(入力規則)

連動するドロップダウンリストを作成する(入力規則)：Excel基本講座

作成:2025/6/3

連動するドロップダウンリストを作成する　　　　　　　Topへ

• ここで使用するデータは、シート「納入表」に入力されている「食材」「品名」を使って連動するリストを作成します。
  テーブルに変換して、テーブル名「食材_tbl」と設定しました。
  

重複しない食材をリストを作成します

1. シート名「入力」にUNIQUE関数で重複しないリストを作成します。
   重複しないリストを　=SORT(UNIQUE(食材_tbl[食材]))　で取り出します。
   なお、SORT関数で昇順に並べ替えています。
   
2. 入力規則の設定を行います。
   セル範囲C3:C9を選択して、[データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
3. データの入力規則で入力値の種類で「リスト」を選択します。
   元の値に　スピル範囲演算子(#)を使って　=$G$3#　とします。←ここがポイントです。
   数式を元の値で指定できないが#を付けることで計算結果の配列のデータ全体を指定することができます。
   
4. 重複しないドロップダウンリストが作成できました。
   

重複しない食材と品名のリストを作成します

1. シート「納入表」の食材_tbl　の食材と品名の列のユニークな(重複しない)組み合わせを取り出します。
   =UNIQUE(食材_tbl[[食材]:[品名]])　とします。
   この時、取り出したデータを食材と品名のそれぞれ昇順に並べ替えた方が見やすくなると考えました。,
   SORTBY(食材_tbl[[食材]:[品名]],食材_tbl[食材],1,食材_tbl[品名],1)　となります。
   組み合わせると、
   =UNIQUE(
   SORTBY(食材_tbl[[食材]:[品名]],
   食材_tbl[食材],1,食材_tbl[品名],1))
   としました。
   

食材から関連する品名を取り出す

1. C3セルに「肉」と入力されたら、「牛肉、豚肉」
   C4セルに「野菜」と入力されたら、「ダイコン、タマネギ、ニンジン」
   C5セルに「くだもの」と入力されたら、「みかん、りんご」
   を選択するような仕組みを考えます。
   これを入力規則でリストにするのですが、Filter関数が入力規則では設定できないようです。
   となったら、従来よく使われていたOffset関数の出番になります。
   =OFFSET(入力!$J$2,
   MATCH(入力!$C3,入力!$I$3:$I$20,0),0,
   COUNTIF(入力!$I$3:$I$20,入力!$C3))
   としました。
   • 食材と品名のリストの入力!$J$2を取り出すリストの基準の位置としました。=OFFSET(入力!$J$2,
     Match関数で$C3セルの入力された食材の位置を探します。MATCH(入力!$C3,入力!$I$3:$I$20,0)
     この数式は下方向でも機能するように 列番号を固定し　$C3(複合参照)　とします。
     なお、$I$20はこの程度のデータ数まで増えたもOKといった感じ指定しています。
     取り出すデータ数はCOUNTIF(入力!$I$3:$I$20,入力!$C3)で計算します。
   
2. 「食材_tbl」に新たな食材が入力されました。
   
3. 入力された食材、「さかな」がドロップダウンリストに表示されました。
   
4. 新たな品名「マグロ」もドロップダウンリストに表示されました。
   

スポンサードリンク

PageViewCounter

Since2006/2/27