ExcelのOFFSET関数で動的なドロップダウンリストを作成する3つの方法

大量のデータを扱う際、Excelは最も便利なツールです。通常のドロップダウンリストは誰でも作成できますが、作業を効率化するには、リストの内容が自動的に更新される動的なドロップダウンリストが必要になることがよくあります。これを実現するのがOFFSET関数です。本記事では、OFFSET関数を使ってExcelに動的なドロップダウンリストを作成する方法を詳しく解説します。

以下が、本記事で使用するデータセットです。スポーツの競技（Event）と優勝者リスト（List of Winners）が含まれています。動的なドロップダウンリストを作成し、各競技に対応する優勝者を選択できるようにしていきます。

OFFSET関数で動的ドロップダウンリストを作成する3つの方法

方法1：OFFSET関数とCOUNTA関数を組み合わせて作成する

まずは、OFFSET関数とCOUNTA関数を組み合わせて動的なドロップダウンリストを作成する方法を紹介します。ここでは範囲C4:C11にドロップダウンリストを作成し、優勝者リストから優勝者を選択できるようにします。

手順：

➤ 範囲C4:C11を選択します。次に、データタブ >> データツール >> データの入力規則 >> データの入力規則をクリックします。

➤ データの入力規則ダイアログボックスが表示されます。入力値の種類のドロップダウンからリストを選択してください。

➤ 元の値ボックスに、次の数式を入力します。

=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)

数式の解説

➥ COUNTA($E$4:$E$100) ➜ 範囲E4:E100内の空白以外のセル数を返します。

結果 ➜ {4}

➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ 指定した基準セルから行・列のオフセットに基づいた範囲を返します。

➥ OFFSET($E$4,0,0,4,1)

結果 ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}

解説： 基準セルはE4です。行の移動量が0、列の移動量が0、高さが4セルなので、最終的にE4:E7の値が取得されます。

➤ OKをクリックします。

これで、範囲C4:C11の各セルにドロップダウンボックスが作成されました。

ドロップダウンボックスの選択肢が優勝者リストと一致していることがわかります。それでは、このドロップダウンリストが本当に動的かどうか確認してみましょう。仮に、射撃（Shooting）競技の優勝者がJamesだとします。Jamesはまだ優勝者リストに存在しないため、名前を追加してどうなるか見てみましょう。

優勝者リストにJamesの名前を追加すると同時に、Excelが自動的にドロップダウンの選択肢を更新しました。つまり、このドロップダウンリストは動的だということです。

➤ 次に、残りの優勝者を選択していきます。

注意： COUNTA関数で指定した範囲はE4:E100です。そのため、範囲E4:E100内のセルに追加や変更を行う限り、Excelはドロップダウンの選択肢を自動更新してくれます。

方法2：OFFSET関数とCOUNTIF関数を組み合わせて作成する

OFFSET関数とCOUNTIF関数を組み合わせることでも、動的なドロップダウンリストを作成できます。

手順：

➤ 方法1と同様にデータの入力規則ダイアログボックスを開き、元の値ボックスに次の数式を入力します。

=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))

数式の解説

➥ COUNTIF($E$4:$E$100,"<>") ➜ 範囲E4:E100内の空白以外のセル数を返します。

結果 ➜ {4}

➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ 指定した基準セルから行・列のオフセットに基づいた範囲を返します。

➥ OFFSET($E$4,0,0,4,1)

結果 ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}

解説： 基準セルはE4です。行・列の移動量がともに0、高さが4セルなので、E4:E7の値が取得されます。

➤ OKをクリックします。

➤ 範囲C4:C11の各セルにドロップダウンボックスが作成されます。

このドロップダウンリストも動的かどうか確認してみましょう。先ほどと同様に、射撃競技の優勝者がJamesであると仮定します。優勝者リストに名前を追加してみます。

Jamesの名前を追加すると同時に、Excelが自動的にドロップダウンの選択肢を更新しました。こちらの方法でも動的なドロップダウンリストが実現できます。

➤ 残りの優勝者を選択しましょう。

注意： COUNTIF関数で指定した範囲はE4:E100です。この範囲内でセルの追加や更新を行えば、Excelはドロップダウンの選択肢を自動的に反映してくれます。

方法3：複数の関数を組み合わせて連動型（ネスト）ドロップダウンリストを作成する

このセクションでは、さらに高度な連動型（ネスト）ドロップダウンリストの作成方法を解説します。OFFSET、COUNTA、MATCHの3つの関数を組み合わせて使用します。

以下がこの方法で使用するデータセットで、特定の商品に関する情報が含まれています。セルF3とF4に2つのドロップダウンリストを作成し、F3で選択した項目に応じて、F4の選択肢が自動的に切り替わる仕組みを作ります。順番に見ていきましょう。

ステップ1：F3にドロップダウンリストを作成する

➤ 方法1と同様にデータの入力規則ダイアログボックスを開き、元の値ボックスにテーブルの見出し（セルB3:D3）へのセル参照を指定します。

これで、セルF3にドロップダウンリストが作成されます。

ステップ2：F4に動的なドロップダウンリストを作成する

次に、セルF4にもう1つのドロップダウンリストを作成します。F4の選択肢は、F3のドロップダウンリストで何を選んだかによって変化します。

➤ データの入力規則ダイアログボックスを開き、元の値ボックスに次の数式を入力します。

=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)

数式の解説

➥ MATCH($F$3,$B$3:$D$3,0) ➜ 範囲B3:D3内におけるセルF3の値の相対的な位置を返します。

結果：{1}

➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ 指定した基準セルから行・列のオフセットに基づいた範囲を返します。高さが10に設定されているため、基準セルから始まる10個のセル値の配列が出力されます。

結果：{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}

➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ 選択された範囲内の空白以外のセル数を返します。

➥ COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}

結果：{4}

➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1) ➔ 基準セルからの行・列オフセットに基づく範囲を返します。

➥ OFFSET($B$3,1,1-1,COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}),1)

➥ OFFSET($B$3,1,0,4,1)

結果：{"Sam";"Curran";"Yank";"Rochester"}

解説： 基準セルはB3です。行の移動量が1、列の移動量が0、高さが4セルなので、B4:B7の値が取得されます。

➤ OKをクリックします。

これでセルF4に動的なドロップダウンリストが作成されました。F3での選択内容に応じて、選択肢が自動的に変わります。例えば、F3のドロップダウンリストでNameを選択すると、F4のドロップダウンリストにはName列に含まれる名前が表示されます。

同様に、F3でProductを選択すると、F4のドロップダウンリストにはProduct列の商品が表示されます。

さらに、Name、Product、Brandのいずれかの列にデータを追加・更新すると、F4のドロップダウンリストも自動的に更新されます。例として、Name列に新しい名前Rockを追加すると、ドロップダウンリストにも自動的に反映されました。

練習用ワークブック

ご覧のとおり、OFFSET関数を使った動的なドロップダウンリストの作成は少し難しく感じられるかもしれません。繰り返し練習することをおすすめします。練習用のシートを用意しましたので、ぜひ活用してください。

まとめ

本記事では、OFFSET関数を使ってExcelに動的なドロップダウンリストを作成する3つの方法を解説しました。COUNTA関数やCOUNTIF関数との組み合わせ、さらにMATCH関数を使った連動型リストまで、目的に応じて使い分けることで、Excelでのデータ管理が格段に効率化されます。皆様のお役に立てば幸いです。ご意見やご質問がありましたら、ぜひコメント欄でお知らせください。

関連記事

• Excelでテーブルから動的なリストを作成する3つの簡単な方法
• 条件に基づいてExcelに動的なリストを作成する方法（単一条件・複数条件対応）