Excelでセルの値に基づいてドロップダウンリストを動的に変更する2つの方法

特定の値をもとにデータを抽出したい場合、ドロップダウンリストが非常に便利です。さらに、複数の連動型（依存関係のある）ドロップダウンリストを作成できれば、選択内容に応じてリストの中身が自動的に切り替わる仕組みを実現できます。本記事では、Excelでセルの値に基づいてドロップダウンリストを変更する方法を、具体的な手順とともに詳しく解説します。

セルの値に基づいてドロップダウンリストを変更する2つの方法

以下では、最も実用的な2つの方法をご紹介します。1つ目は、OFFSET関数とMATCH関数を組み合わせて、セルの値に応じてリストを動的に変更する方法です。2つ目は、Microsoft 365（Excel 365）で利用できるXLOOKUP関数を使った、よりシンプルな方法です。まず、以下のサンプルデータセットを使用して作業を進めます。

方法1：OFFSET関数とMATCH関数を組み合わせてドロップダウンリストを変更する

このデータセットには、3人の営業担当者とそれぞれが販売した商品が記録されています。ここで、特定の担当者を選ぶと、その担当者が販売した商品だけがリストに表示されるように設定していきます。以下の手順に従ってください。

ステップ1：データの入力規則リストを作成する

• 「データ」タブを開きます。
• 「データの入力規則」をクリックします。

ステップ2：リストのソースを選択する

• 「入力値の種類」で「リスト」を選択します。

• 「元の値」ボックスに、営業担当者の名前が入力された範囲E4:G4を指定します。
• Enterキーを押します。

• これで、セルB5にドロップダウンリストが表示されます。

ステップ3：OFFSET関数を適用する

• まず、OFFSET関数の基本形として次の数式を入力します。

=OFFSET($E$4)

• ここでE4は、絶対参照形式で指定した基準となる参照セルです。

• 次に「行数」引数に1を指定します。これは、基準セルE4から1行下の位置を指します。

=OFFSET($E$4,1

ステップ4：MATCH関数でOFFSET関数の列位置を指定する

• 「列数」引数には、MATCH関数を使って列の位置を動的に取得します。次のように入力します。

=OFFSET($E$4,1,MATCH($B$5

• ここでB5は、ドロップダウンリストで選択されたセルの値です。

• MATCH関数の検索範囲には、絶対参照形式で範囲E4:G4を指定します。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4

• 照合の種類には完全一致を意味する0を指定します。このMATCH関数は、選択した名前に応じて該当する列番号を返します。

MATCH($B$5,$E$4:$G$4,0)

• さらに、MATCH関数の結果から-1を引きます。これは、OFFSET関数が最初の列を0としてカウントするためです。

MATCH($B$5,$E$4:$G$4,0)-1

ステップ5：高さを指定する

• 「高さ」引数に1を指定すると、各列に含まれる値が1つであることを示します。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1

ステップ6：幅を指定する

• 「幅」引数にも1を入力します。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1,1)

• これで、B5でJacobを選択すると、Jacobの商品の最初の要素であるChocolateが表示されることが確認できます。

ステップ7：各列の要素数をカウントする

• 次に、列内の要素数を数えるために、セルC13にCOUNTA関数を使った次の数式を入力します。

=COUNTA(OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,10))

• これにより、特定の担当者（ここではJacob）の商品数がカウントされます。

ステップ8：カウント結果をOFFSET関数の高さ引数に組み込む

• 先ほどのカウント結果を高さとして反映させるため、次の数式を入力します。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)

ステップ9：数式をコピーする

• Ctrl + Cキーで数式をコピーします。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)

ステップ10：数式を貼り付ける

• コピーした数式を、データの入力規則の「元の値」欄に貼り付けます。

=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)

• 最後にEnterキーを押して変更を確定します。

• これで、別のセルの値に基づいてドロップダウンリストの内容が自動的に変わるようになります。

• 試しに、セルの値をBryanからJulianaに変更すると、Julianaが販売した商品名が表示されます。

あわせて読みたい： Excelで範囲からリストを作成する方法（3つの方法）

関連記事

• Excelで複数単語に対応した連動型ドロップダウンリストを作成する方法
• 選択内容に基づいてデータを抽出するドロップダウンフィルターの作成方法
• Excelでドロップダウンリストの選択に基づいてデータを抽出する方法
• セルの値に基づいたドロップダウンリストでExcelフィルターを作成する
• Excelのドロップダウンリストに項目を追加する方法（5つの方法）

方法2：XLOOKUP関数を使ってドロップダウンリストを変更する

Microsoft 365をお使いの場合は、XLOOKUP関数のたった1つの数式で同じことが実現できます。以下の手順に従ってください。

ステップ1：データの入力規則リストを作成する

• 「データの入力規則」から「リスト」を選択します。

ステップ2：ソース範囲を指定する

• 「元の値」ボックスに範囲E4:G4を指定します。
• その後、Enterキーを押します。

• これで、データの入力規則によるリストが作成されました。

ステップ3：XLOOKUP関数を挿入する

• 検索値としてセルB5を指定します。

=XLOOKUP(B5)

ステップ4：検索範囲を指定する

• 検索範囲として範囲E4:G4を入力します。

=XLOOKUP(B5, E4:G4)

あわせて読みたい： Excelでドロップダウンリストを編集する方法（4つの基本的なアプローチ）

ステップ5：戻り範囲を挿入する

• 戻り値となる範囲E5:G11を指定します。

• これで、選択した担当者に対応する商品が返されるようになります。

• ドロップダウンリストから任意の名前を選択すると、その担当者の商品名が表示されます。

注意点： 上の画像を見ると、範囲内の空白セルがあるためゼロ（0）が表示されています。空白セルはゼロとして扱われるためです。このゼロを非表示にするには、次の手順に従ってください。

あわせて読みたい： Excelのドロップダウンリストに空白の選択肢を追加する方法（2つの方法）

ステップ6：UNIQUE関数を適用する

• UNIQUE関数をネストした次の数式を入力します。

=UNIQUE(XLOOKUP(B5,E4:G4,E5:G11),,TRUE)

• これで、空白セルが除外された、望みどおりの結果が得られます。

あわせて読みたい： VBAでドロップダウンリストに一意の値を表示する方法（完全ガイド）

まとめ

本記事では、Excelでセルの値に基づいてドロップダウンリストを更新する2つの方法を解説しました。OFFSET関数とMATCH関数を組み合わせる方法は、どのバージョンのExcelでも使える汎用的な手法です。一方、Microsoft 365をお使いなら、XLOOKUP関数とUNIQUE関数を活用することで、より簡潔に実装できます。ぜひ実際のデータで練習し、学んだ内容を業務に活かしてください。

ご不明な点がありましたら、お気軽にお問い合わせください。コメント欄でご意見をお聞かせください。

できるだけ早くご回答いたします。

今後ともどうぞお付き合いください。学びを続けましょう。

関連記事

• Excelでフィルターのドロップダウンリストをコピーする方法（5つの方法）
• VBAでドロップダウンリストから値を選択する方法（2つの方法）
• Excelで一意の値のみのドロップダウンリストを作成する方法（4つの方法）
• Excelのドロップダウンリストが動作しないときの対処法（8つの問題と解決策）
• Excelの選択内容に応じたドロップダウンリストの作成方法
• Excelでドロップダウンリストを作成する方法（独立型・連動型）
• Excelのドロップダウンリストを自動更新する方法（3つの方法）