Excelで動的範囲名（ダイナミックレンジ）を活用して柔軟なドロップダウンリストを作成する方法

Excelのスプレッドシートでは、データ入力を簡単にしたり、入力内容を統一したりするために、セルにドロップダウンリスト（プルダウンメニュー）を設定することがよくあります。このドロップダウンは、「データの入力規則」機能を使って許可する入力値のリストを指定することで作成できます。

基本的なドロップダウンリストの作り方

シンプルなドロップダウンリストを設定するには、まずデータを入力するセルを選択し、「データ」タブの「データの入力規則」をクリックします。続いて「入力値の種類」で「リスト」を選択し、「元の値」欄にリスト項目をカンマ区切りで入力します。

このような基本的なドロップダウンでは、許可する項目のリストが入力規則そのものの中に直接記述されているため、リストを変更するには入力規則を開いて編集し直す必要があります。しかし、Excelに不慣れなユーザーにとっては操作が難しかったり、選択肢が多い場合は編集が面倒だったりします。

名前付き範囲を使う方法とその課題

もうひとつの方法として、リストをスプレッドシート内の名前付き範囲として定義し、データの入力規則の「元の値」欄にその範囲名を等号（=）付きで指定するやり方があります。

この方法ならリスト項目の編集は格段に楽になりますが、項目の追加や削除には問題が伴います。名前付き範囲（この例では「FruitChoices」）は固定されたセル範囲（$H$3:$H$10など）を参照しているため、H11以降のセルに新しい項目を追加しても、そのセルが範囲外であるためドロップダウンには表示されません。

また逆に、たとえば「梨」と「イチゴ」の項目を削除すると、それらはドロップダウンから消えますが、範囲自体は空になったH9・H10のセルも含んだままなので、ドロップダウンには2つの「空白の選択肢」が表示されてしまいます。

こうした理由から、通常の名前付き範囲をドロップダウンのリストソースとして使う場合、項目を追加・削除するたびに、名前付き範囲そのものを編集してセル数を調整しなければなりません。

解決策：動的範囲名（ダイナミックレンジ）を使う

この問題を解決するのが、動的範囲名（ダイナミックレンジ）です。動的範囲名とは、項目の追加や削除に応じて、データブロックのサイズに自動的に拡大・縮小する範囲名のことです。これを実現するには、固定のセルアドレスではなく数式を使って名前付き範囲を定義します。

静的な範囲と動的な範囲の違い

通常の（静的な）範囲名は、特定のセル範囲を直接参照します（例：$H$3:$H$10）。

一方、動的な範囲は次のような数式で定義されます（以下は動的範囲名を使用する別のブックからの例です）。

始める前に、記事で使用しているExcelサンプルファイルをダウンロードしておきましょう（並べ替え用マクロは無効化されています）。

数式の全体像

果物（Fruits）の選択肢は、見出し（FRUITS）の直下にあるセルブロックに入力されています。この見出しにも「FruitsHeading」という名前が付けてあります。

果物の選択肢に対して動的範囲を定義するために使われる数式全体は、次のとおりです。

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeadingは、リストの最初の項目の一つ上の行にある見出しセルを指します。数式内の「20」（2箇所で使用）は、リストの最大サイズ（最大行数）であり、必要に応じて変更可能です。

この例では実際の項目は8つだけですが、その下にはさらに項目を追加できる空のセルがあります。「20」は実際の項目数ではなく、項目を入力できるブロック全体を指しています。

数式の分解：仕組みを理解する

それでは、この数式をパーツごとに分解して、どのように機能するのかを見ていきましょう。

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

最も内側にあるのが OFFSET(FruitsHeading,1,0,20,1) です。これは、FruitsHeadingセルの下にある、選択肢を入力できる20個のセルブロックを参照します。OFFSET関数は「FruitsHeadingセルを起点として、1行下へ、0列横へ移動し、そこから縦20行×横1列の範囲を選択する」という意味になります。つまり、果物の選択肢が入力される20行分のブロックが得られます。

ISBLANK関数：空白かどうかの判定

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(前述のOFFSET),0,0),0)-1,20),1)

読みやすくするため、先ほどのOFFSET関数を「前述のOFFSET」と表記しています。ISBLANK関数は、OFFSET関数が定義する20行分のセル範囲に対して作用します。

ISBLANKは、参照範囲内の各セルが空白かどうかに応じて、TRUE／FALSEからなる20個の値のセットを作成します。この例では、最初の8つのセルには項目が入っているため最初の8つの値はFALSEとなり、残りの12個はTRUEになります。

INDEX関数：配列として取り出す

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(前述の結果,0,0),0)-1,20),1)

ここでも「前述の結果」は、先に説明したISBLANK関数とOFFSET関数の組み合わせを指します。INDEX関数は、ISBLANK関数が生成した20個のTRUE／FALSE値を含む配列を返します。

INDEXは通常、データブロックの中から特定の行・列を指定して値（または値の範囲）を取り出すために使われますが、行と列の引数をゼロに設定すると（この例がまさにそうですが）、データブロック全体を含む配列を返すようになります。

MATCH関数：最初の空白セルの位置を見つける

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,前述の配列,0)-1,20),1)

MATCH関数は、INDEX関数が返した配列の中から、最初のTRUE値の位置を返します。リストの最初の8項目は空白ではないため、配列の最初の8つの値はFALSEになり、9番目の値がTRUEになります（範囲の9行目が空だからです）。

したがってMATCH関数は「9」を返します。しかし、ここで本当に知りたいのはリスト内の項目数なので、数式ではMATCHの結果から1を引いています（これにより最後の項目の位置が得られます）。最終的に MATCH(TRUE,前述の配列,0)-1 は「8」を返します。

IFERROR関数：リストが満杯の場合への備え

=OFFSET(FruitsHeading,1,0,IFERROR(前述の結果,20),1)

IFERROR関数は、最初に指定した値がエラーになった場合に、代わりの値を返します。この関数が含まれているのは、セルブロック全体（20行すべて）が項目で埋まった場合、MATCH関数がエラーを返すためです。

というのも、MATCH関数にはISBLANK関数の結果の配列から最初のTRUE値を探すよう指示していますが、空白のセルがひとつもなければ、配列はすべてFALSEで埋め尽くされます。MATCH関数は検索対象の値（TRUE）が見つからないとエラーを返すのです。

そこで、リストが満杯の場合（MATCHがエラーを返す場合）、IFERROR関数は代わりに「20」を返します（リストには必ず20項目あるはずだからです）。

最後のOFFSET：目的の範囲を返す

最後に、OFFSET(FruitsHeading,1,0,前述の結果,1) が、私たちが求めている範囲を返します。FruitsHeadingセルを起点に1行下へ移動し、リスト内の項目数と同じ行数（幅は1列）の範囲を選択します。つまり、数式全体としては、実際の項目だけを含む範囲（最初の空白セルまで）が返されるのです。

この数式でドロップダウンのソースとなる範囲を定義すれば、リストを自由に編集できます（残りの項目が先頭セルから連続していれば、項目の追加・削除は自由）。そしてドロップダウンは常に現在のリスト内容を反映するようになります。

この記事で使用したサンプルファイル（Dynamic Lists）は本サイトからダウンロードできます。ただし、WordPressの仕様上、マクロを含むExcelブックが扱えないため、マクロは動作しません。

別の方法：リストブロック自体に名前を付ける

リストブロックの行数を数式に直接指定する代わりに、リストブロック自体に範囲名を割り当てて、それを修正版の数式で使うこともできます。サンプルファイルでは、2つ目のリスト（Names）でこの方法を採用しています。ここでは、「NAMES」見出しの下にあるリストブロック全体（サンプルファイルでは40行）に「NameBlock」という範囲名を付けています。NamesListを定義する別の数式は次のとおりです。

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

この数式では、NamesBlockが OFFSET(FruitsHeading,1,0,20,1) を置き換え、ROWS(NamesBlock)が先ほどの数式内の「20」（行数）を置き換えています。

まとめ

不慣れなユーザーでも簡単に編集できるドロップダウンリストを実現したいなら、ぜひ動的範囲名を活用してみてください。なお、この記事ではドロップダウンリストを中心に解説しましたが、動的範囲名は、サイズが変わりうる範囲やリストを参照したいあらゆる場面で使用できます。ぜひお試しください！