エクセルのOFFSET関数でプルダウンを3段階連動させる方法

プルダウンは、セルにあらかじめ選択肢を設定しておいて決まった項目だけを入力できるようにするとても便利な機能です。

プルダウンは関数を使うことで他のプルダウンと連動させることできるため、1つ目のプルダウンで選んだ選択肢に対応して2つ目のプルダウンの選択肢を変更するといったようなことが可能です。

この記事では、エクセルのOFFSET関数でプルダウンを3段階連動させる方法をご紹介します。

OFFSET関数について

OFFSET（オフセット）関数とは検索/行列関数の1つで、指定したセルもしくはセル範囲から指定した行数/列数分移動した先にあるセルの値、またはセル範囲を返す関数です。

数式は「=OFFSET(参照,行数,列数,[高さ],[幅])」です。

指定したセルを取り出すことができるため、プルダウンの選択肢リストをカテゴリ分けしておけば開始位置と何個分のセルかを指定して条件に合う選択肢を抽出することができます。

プルダウンの連動とは？

例えば、上の画像のようにF列の大分類で「東日本か西日本」のプルダウンを作っておいて、G列の中分類で「都道府県」で絞り、さらにH列の小分類で「中分類で絞った都道府県にある支店名」で選択肢を絞る、といったようなプルダウンの連動が可能になります。

選択肢リストから抽出する開始位置はどこか、何個分のセルを取り出すかという指定は「MATCH関数」と「COUNTIF関数」で行います。

OFFSET関数とMATCH関数とCOUNTIF関数を使った3つのプルダウンを連動させる方法は下記「エクセルのOFFSET関数でプルダウンを3段階連動させる方法」セクションでご紹介しています。

関数を以下に記載しますのでコピーしてお使いください。中分類/小分類の部分にはシート名をご入力ください。

=OFFSET(中分類$B$2,MATCH($F3,中分類!$A:$A,0)-2,0,COUNTIF(中分類!$A:$A,$F3))

=OFFSET(小分類!$B$2,MATCH($F3&$G3,小分類!$A:$A,0)-2,0,COUNTIF(小分類!$A:$A,$F3&$G3))

それぞれの数式についての解説は「数式について」でご紹介しています。

エクセルのOFFSET関数でプルダウンを3段階連動させる方法

OFFSET関数、MATCH関数、COUNTIF関数を組み合わせることでプルダウンを3段階で連動させることが出来ます。今回はブロック（大分類）、都道府県（中分類）、支店（小分類）の順に選択肢を絞っていくプルダウンの作成方法をご紹介します。

まずは大分類の選択肢リストのシートを作成します。新規でシート（例：ブロック）を作成し、大分類の選択肢（例：東日本と西日本）を入力します。

次に、中分類の選択肢リストを作成します。中分類は2つ目のプルダウンになります。新規でシート（例：都道府県）を作成し中分類の選択肢を入力します。大分類で東日本と西日本のどちらを選択したかによって、中分類から対応する選択肢を表示するように設定するため上の画像のように大分類をA列、対応する中分類をB列に入力しましょう。

次に、小分類の選択肢リストを作成します。小分類は3つ目のプルダウンになります。新規でシート（例：支店）を作成し小分類の選択肢を入力します。大分類+中分類の項目に対応する選択肢を表示するように設定するため上の画像のように大分類+中分類の項目をA列、対応する小分類をB列に入力しましょう。

次に、表のシートに戻りプルダウンを作成します。まずは大分類のプルダウンを作成するので、セル（例：F3セルからF8セル）、データタブ、「データの入力規則」の順に選択します。

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリストを選択し、↑ボタンを押します。

セル参照のダイアログボックスが表示されます。大分類のシートを表示して選択肢を入力しておいたセル番地（例：A2セルとA3セル）を選択し、OKボタンを押します。

「データの入力規則」ダイアログボックスに戻ります。OKボタンを押します。

これで大分類のプルダウンが作成できました。この後の中分類/小分類の設定を分かりやすくするために、セル右側の三角ボタンを押して任意の選択肢（例：東日本）を選択しておきましょう。

次に中分類のプルダウンを作成します。セル（例：G3セルからG8セル）、データタブ、「データの入力規則」の順に選択します。

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリスト、「元の値」に上記「プルダウンの連動とは？」セクションに記載した関数を参考に=OFFSET(都道府県!$B$2,MATCH($F3,都道府県!$A:$A,0)-2,0,COUNTIF(都道府県!$A:$A,$F3))と入力し、OKボタンを押します。

これで中分類のプルダウンが作成できました。セル右側の三角ボタンを押して、大分類で設定した東日本と西日本に対応した選択肢に絞られていれば問題なく数式を設定できています。任意の選択肢（例：東京都）を選択しておきましょう。

次に小分類のプルダウンを作成します。セル（例：H3セルからH8セル）、データタブ、「データの入力規則」の順に選択します。

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリスト、「元の値」に上記「プルダウンの連動とは？」セクションに記載した関数を参考に=OFFSET(支店!$B$2,MATCH($F3&$G3,支店!$A:$A,0)-2,0,COUNTIF(支店!$A:$A,$F3&$G3))と入力し、OKボタンを押します。

これで小分類のプルダウンが作成できました。セル右側の三角ボタンを押して、中分類の都道府県名に対応した支店名が表示されていれば問題なく数式を設定できています。任意の選択肢（例：渋谷支店）を選択しておきましょう。

他の選択肢を確認して、ブロックと都道府県で絞った支店名のみが表示されているか見てみましょう。上の画像では西日本の愛知県で絞った支店名として「名古屋支店」「豊田支店」が表示されています。これで、OFFSET関数を使って3つのプルダウンを連動することができました。

数式について

中分類の数式

上記は中分類の数式についての解説です。

OFFSET関数の引数は「=OFFSET(参照,行数,列数,[高さ],[幅])」で、上の画像の①が参照、③が行数、④が列数、⑤が高さになっています。

①は中分類のリストの最初のセル（例：B2セル）を指定し、MATCH関数でリストの開始位置（東日本と西日本の選択肢をどこで分けるか）を検索します。これがOFFSET関数の行数の引数になります。

MATCH関数の引数は、MATCH(検査値,検査範囲,[照合の種類])です。

②が検査値の引数になり、大分類のプルダウン（例：プルダウンシートのF3セル）を指定します。

すると、③で都道府県シートの基準セルから数えて2つ下のセル（例：B4）が返ってくるため「-2」を数式に追加して①で指定したB2セルが開始位置になるようにします。

これはMATCH関数の場合は基準となるセルを含めてカウントしますが、OFFSET関数は基準となるセルを含めずにカウントするため「-2」が必要になります。

また、MATCH関数だけで数式を完結させると「MATCH($F3,都道府県!$A:$A,0)」では結果がA2セルを表す「2」になりますが、OFFSET関数の行数の引数としては「2」だと「B2セルを0として2行下のセル」として「B4」が返ってくるため「-2」をしてB2を指定することが必要になります。

④は列数の引数ですが、列は移動しないため0のままです。

最後にCOUNTIF関数で大分類に対応する選択肢がそれぞれいくつずつあるかをカウントします。今回はそれぞれ4つずつになります。

小分類の数式

上記は小分類の数式についての解説です。

OFFSET関数の引数は「=OFFSET(参照,行数,列数,[高さ],[幅])」で、上の画像の①が参照、③が行数、④が列数、⑤が高さになっています。

①は小分類のリストの最初のセル（例：B2セル）を指定し、MATCH関数でリストの開始位置（どこで支店名を分けるか）を検索します。これがOFFSET関数の行数の引数になります。

MATCH関数の引数は、MATCH(検査値,検査範囲,[照合の種類])です。

②が検査値の引数になりますが、大分類と中分類のプルダウン（例：プルダウンシートのF3セルとG3セル）の項目を使って検索を行うため&で結合して指定します。

すると、③で支店シートの基準セルから数えて2つ下のセル（例：B4）が返ってくるため「-2」を数式に追加して①で指定したB2セルが開始位置になるようにします。

「-2」を追加する理由は上記「中分類の数式」セクションでご紹介していますのでご確認ください。

④は列数の引数ですが、列は移動しないため0のままです。

最後にCOUNTIF関数で大分類と中分類に対応する小分類の選択肢がそれぞれいくつずつあるかをカウントします。今回は各都道府県につき2つずつになります。