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

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

更新:2025/6/28;作成:2025/6/3

TrimRange関数を使って連動するドロップダウンリストを作成する　　　　　　　Topへ

• TrimRange関数がExcel for Microsoft365で使用できるようになりました。(2025/6/20に気付きました)
  連動するドロップダウンリストも容易に設定できるようになりました。

1. ここではドロップダウンリストに表示するデータをE2:G6にリストにしています。
   B4:C4セルにドロップダウンリストを設定します。
   

   	B	C	D	E	F	G
   2				野菜	肉	果物
   3	種類	品名		大根	豚肉	りんご
   4	肉	鶏肉		人参	牛肉	みかん
   5				レタス	鶏肉	バナナ
   6					羊肉

2. ドロップダウンリストを設定するB4セルを選択して、[データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
3. 入力値の種類で「リスト」を選択します。
   元の値に　リスト参照で　=E2:.J2　と入力します。
   
4. 連動するドロップダウンリストを設定するC4セルを選択して、[データ]タブのデータツール グループにある[データの入力規則]を実行します。
   
5. 入力値の種類で「リスト」を選択します。
   元の値に　リスト参照で　=TRIMRANGE(XLOOKUP(B4,2:2,3:10))　と入力します。
   • TrinRange関数の使い方は　TRIMRANGE関数とトリム参照を使って空白の行または列を除外する　をご覧ください。
   
   • ほかにもいくつか数式を考えることができます。
     =TRIMRANGE(INDEX(E3:H10,,XMATCH(B4,E2:H2)))
     =TRIMRANGE(CHOOSECOLS(E3:H10,XMATCH(B4,E2:H2)))
6. B4セルのドロップダウンリストから「果物」を選択しました。
   
7. C4セルのドロップダウンリストに追加したパーナップル、イチゴが表示されました。
   
8. H列にデータを追加しました。
   B4セルのドロップダウンリストに「魚」が追加されました。
   
9. C4セルのドロップダウンリストに追加したかつお、サバ、たこが表示されました。
   

Offset関数を使って連動するドロップダウンリストを作成する　　　　　　　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