Excelスプレッドシートには、データ入力を簡素化または標準化するためにセルドロップダウンが含まれていることがよくあります。 これらのドロップダウンは、データ検証機能を使用して作成可能なエントリのリストを指定することによって作成されます。
単純なドロップダウンリストを設定するには、データを入力するセルを選択し、[ データの検証 ]（[ データ ]タブ）をクリックして[ データの検証 ]を選択し、[許可]の下の[ リスト ]を選択します。 Source ：フィールドに）を入力します（図1を参照）。
このタイプの基本的なドロップダウンでは、許容エントリのリストはデータ検証自体の中で指定されます。 したがって、リストを変更するには、ユーザーはデータ検証を開いて編集する必要があります。 しかし、これは経験の浅いユーザー、または選択肢のリストが長い場合には困難な場合があります。
別のオプションとして、スプレッドシート内の名前付き範囲にリストを配置してから、データ検証の[ Source ：]フィールドにその範囲名（等号で始まります）を指定します（図2を参照）。
この2番目の方法では、リスト内の選択項目の編集が簡単になりますが、項目を追加または削除すると問題が生じる可能性があります。 名前付き範囲（この例ではFruitChoices）は固定範囲のセル（図のように$ H $ 3：$ H $ 10）を参照しているので、H11以下のセルに追加の選択肢があるとドロップダウンに表示されません。 （それらのセルはFruitChoicesの範囲の一部ではないので）。
同様に、たとえば、PearsとStrawberriesのエントリが消去された場合、それらはドロップダウンに表示されなくなりますが、ドロップダウンはまだFruitChoicesの範囲全体（空のセルH9とH9を含む）を参照するため、ドロップダウンには2つの「空」の選択肢が含まれます。 H10
これらの理由から、ドロップダウンのリストソースとして通常の名前付き範囲を使用する場合、エントリがリストに追加またはリストから削除された場合は、名前付き範囲自体を編集してセルを増減する必要があります。
この問題を解決するには、ドロップダウン選択のソースとしてダイナミックレンジ名を使用します。 ダイナミックレンジ名は、エントリが追加または削除されるときにデータブロックのサイズと完全に一致するように自動的に拡大（または縮小）される名前です。 これを行うには、固定範囲のセルアドレスではなく式を使用して名前付き範囲を定義します。
Excelでダイナミックレンジを設定する方法
通常の（静的な）範囲名は、指定された範囲のセル（この例では$ H $ 3：$ H $ 10、下記参照）を表します。
しかし、ダイナミックレンジは式を使って定義されます（下記参照、ダイナミックレンジ名を使った別のスプレッドシートから引用）。
始める前に、必ずExcelのサンプルファイルをダウンロードしてください（ソートマクロは無効になっています）。
この公式を詳しく調べてみましょう。 フルーツの選択は、見出しのすぐ下にあるセルのブロックにあります（ フルーツ ）。 その見出しにも名前が割り当てられています： FruitsHeading ：
フルーツの選択のダイナミックレンジを定義するために使用される式全体は、次のとおりです。
= OFFSET（FruitsHeading、1, 0、IFERROR（一致（TRUE、INDEX（ISBLANK（OFFSET（FruitsHeading、1, 0, 20, 1））、0, 0）、0）-1, 20）、1）
FruitsHeadingは、リストの最初のエントリの1行上の見出しです。 数値20（計算式で2回使用される）は、リストの最大サイズ（行数）です（これは必要に応じて調整できます）。
この例では、リストには8つのエントリしかありませんが、追加のエントリを追加できる場合は、これらの下に空のセルもあります。 番号20は、実際のエントリ数ではなく、エントリを作成できるブロック全体を表します。
それでは、式がどのように機能するかを理解するために、式を細かく分割して（各部分を色分けして）みましょう。
= OFFSET（FruitsHeading、1, 0、IFERROR（一致（TRUE、INDEX（ISBLANK（ OFFSET（FruitsHeading、1, 0, 20, 1） ）、0, 0）、0）-1, 20）、1）
最も内側のピースはOFFSETです（FruitsHeading、1, 0, 20, 1） 。 これは、選択を入力できる20個のセル（FruitsHeadingセルの下）のブロックを参照しています。 このOFFSET関数は基本的に次のように言っています。FruitsHeadingセルから始めて、1行0列以上移動してから、長さ20行、幅1列の領域を選択します。 それでそれは私達にフルーツの選択が入力される20列のブロックを与える。
式の次の部分はISBLANK関数です。
= OFFSET（FruitsHeading、1, 0、IFERROR（MATCH（TRUE、INDEX（ ISBLANK（上記の） 、0, 0）、0）-1, 20）、1））
ここでは、（上で説明した）OFFSET関数は（上記を読みやすくするために）「上記」に置き換えられました。 しかし、ISBLANK関数は、OFFSET関数が定義している20行のセル範囲で動作しています。
次に、ISBLANKは、20のTRUE値とFALSE値のセットを作成し、OFFSET関数で参照される20行の範囲内の個々のセルがそれぞれ空白（空）かどうかを示します。 この例では、最初の8個のセルは空ではなく、最後の12個の値はTRUEになるため、セット内の最初の8個の値はFALSEになります。
次の式はINDEX関数です。
= OFFSET（FruitsHeading、1, 0、IFERROR（MATCH（TRUE、 INDEX（上記、0, 0） 、0）-1, 20）、1）
繰り返しますが、「上記」とは、上記のISBLANKおよびOFFSET機能を指します。 INDEX関数は、ISBLANK関数によって作成された20個のTRUE / FALSE値を含む配列を返します。
INDEXは通常、（そのブロック内の）特定の行と列を指定することによって、データのブロックから特定の値（または値の範囲）を選択するために使用されます。 しかし、（ここで行っているように）行と列の入力をゼロに設定すると、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値を探すように指示しているためですが、セルのNONEが空の場合は、配列全体がFALSE値で埋められます。 MATCHが検索中の配列でターゲット値（TRUE）が見つからない場合は、エラーが返されます。
そのため、リスト全体がいっぱいになると（したがって、MATCHがエラーを返す）、代わりにIFERROR関数は20の値を返します（リストには20個のエントリがなければならないことがわかります）。
最後に、 OFFSET（FruitsHeading、1, 0、上記、1）は実際に探している範囲を返します。FruitsHeadingセルから始めて1行0列以上下に移動します。リスト（および1列幅）にエントリがあります。 そのため、式全体で、実際のエントリのみを含む範囲が返されます（最初の空のセルまで）。
この式を使用してドロップダウンのソースとなる範囲を定義すると、リストを自由に編集でき（残りのエントリが最上位セルから始まり隣接している限り、エントリを追加または削除できます）、ドロップダウンに常に現在の値が反映されます。リスト（図6を参照）。
ここで使用されているサンプルファイル（動的リスト）は含まれており、このWebサイトからダウンロードできます。 しかし、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（行数）を置き換えます。
そのため、（経験の浅い他のユーザも含めて）簡単に編集できるドロップダウンリストには、ダイナミックレンジ名を使用してみてください。 また、この記事ではドロップダウンリストを中心に説明しましたが、動的範囲名は、サイズの異なる可能性がある範囲またはリストを参照する必要がある場合はどこでも使用できます。 楽しい！
