Excelスプレッドシートには、多くの場合、データ入力を簡素化および/または標準化するためのセルドロップダウンが含まれています。これらのドロップダウンは、データ検証機能を使用して作成され、許可されるエントリのリストを指定します。
単純なドロップダウンリストを設定するには、データを入力するセルを選択し、[データ検証（[データ]タブ）、[データ検証]を選択し、[リスト]（[許可：]の下）を選択して、[ソース：フィールド（図1を参照）。
このタイプの基本的なドロップダウンでは、許可されるエントリのリストが指定されます。データ検証自体の中。したがって、リストを変更するには、ユーザーはデータ検証を開いて編集する必要があります。ただし、これは経験の浅いユーザーや、選択リストが長い場合には難しいかもしれません。
別のオプションは、リストをスプレッドシート内の名前付き範囲 に配置してから指定することです。データ検証の[ソース：]フィールドの範囲名（等号で始まります）。
In_content_1 all：[300x250] / dfp：[640x360]->
この2番目の方法では、リスト内の選択肢を簡単に編集できますが、アイテムの追加または削除には問題が生じる可能性があります。名前付き範囲（この例ではFruitChoices）は固定範囲のセル（図のように$ H $ 3：$ H $ 10）を参照しているため、セルH11以下にさらに選択肢が追加されると、ドロップダウンに表示されません（これらのセルはFruitChoices範囲の一部ではないため）。
たとえば、梨とイチゴのエントリが消去されると、ドロップダウンには表示されなくなりますが、ドロップダウンには2つが含まれます。ドロップダウンは空のセルH9およびH10を含むFruitChoices範囲全体を参照するため、「空」の選択肢。
これらの理由により、通常の名前付き範囲をドロップダウンのリストソースとして使用する場合、名前付き範囲エントリをリストに追加またはリストから削除する場合は、それ自体を編集してセルの数を増やしたり減らしたりする必要があります。
この問題の解決策は、動的ドロップダウン選択のソースとしての範囲名。ダイナミックレンジ名は、エントリが追加または削除されるときにデータブロックのサイズに正確に一致するように自動的に拡張（または縮小）する名前です。これを行うには、セルアドレスの固定範囲ではなく、式を使用して、名前付き範囲を定義します。
動的な設定方法Excelの範囲
通常の（静的な）範囲名は、指定されたセル範囲（この例では$ H $ 3：$ H $ 10、以下を参照）を指します：
ただし、ダイナミックレンジは式を使用して定義されます（ダイナミックレンジ名を使用する別のスプレッドシートから取得した以下を参照）：
始める前に、必ずExcelサンプルファイル をダウンロードしてください（ソートマクロは無効になっています）。
この式を詳しく調べてみましょう。フルーツの選択は、見出しのすぐ下にあるセルのブロックにあります（フルーツ）。その見出しには名前も割り当てられます：FruitsHeading：
のダイナミックレンジを定義するために使用される式全体果物の選択肢は次のとおりです。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(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(MATCH(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(the above),0,0),0)-1,20),1)
ここでは、（上記で説明した）OFFSET関数が（読みやすくするために）上記に置き換えられました。ただし、ISBLANK関数は、OFFSET関数が定義する20行のセル範囲で動作します。
ISBLANKは、20個のTRUEおよびFALSE値のセットを作成し、20- OFFSET関数によって参照される行範囲は、空白（空）かそうではありません。この例では、最初の8個のセルが空ではなく、最後の12個の値がTRUEになるため、セットの最初の8個の値はFALSEになります。
次の式はINDEX関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
ここでも、「上記」とは、上記のISBLANKおよびOFFSET機能を指します。 INDEX関数は、ISBLANK関数によって作成された20個のTRUE / FALSE値を含む配列を返します。
INDEXは通常、特定の値（または値の範囲）を選択するために使用されます特定の行と列（そのブロック内）を指定することによるデータのブロック。ただし、（ここで行われているように）行と列の入力をゼロに設定すると、INDEXはデータブロック全体を含む配列を返します。
次の式はMATCH関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH関数は、INDEX関数によって返される配列内の最初のTRUE値の位置を返します。リストの最初の8つのエントリは空白ではないため、配列の最初の8つの値はFALSEになり、9番目の値はTRUEになります（範囲の9 th行が空であるため）。
したがって、MATCH関数は9の値を返します。ただし、この場合、リストにあるエントリの数を本当に知りたいので、式はMATCH値から1を減算します（最後のエントリの位置を示します）。最終的に、MATCH（TRUE、the above、0）-1は8の値を返します。
次の式はIFERROR関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
最初に指定された値がエラーになった場合、IFERROR関数は代替値を返します。セルのブロック全体（20行すべて）がエントリでいっぱいになった場合、MATCH関数はエラーを返すため、この関数が含まれています。
これは、MATCH関数に検索するように指示しているためです。最初のTRUE値（ISBLANK関数からの値の配列内）が、NONEのセルが空の場合、配列全体がFALSE値で埋められます。 MATCHは、検索中の配列でターゲット値（TRUE）を見つけることができない場合、エラーを返します。
したがって、リスト全体がいっぱいの場合（したがって、MATCHはエラーを返します）、IFERROR関数は代わりに20の値を返します（リストに20のエントリが必要であることを認識しています）。
最後に、OFFSET（FruitsHeading、1,0、the above、1）は実際に探している範囲：Fruit FruitsHeadingセルから始めて、1行下、0列を超えてから、リスト内にエントリがある（ただし、1列幅の）行数が多い領域を選択します。したがって、式全体では、実際のエントリのみを含む範囲（最初の空のセルまで）が返されます。
この式を使用して、ドロップダウンのソースである範囲を定義すると、自由に編集できますリスト（残りのエントリが最上部のセルから始まり連続している限り、エントリの追加または削除）とドロップダウンには常に現在のリストが反映されます（図6を参照）。
ここで使用されているサンプルファイル（動的リスト） は含まれており、このWebサイトからダウンロードできます。ただし、WordPressはマクロを含むExcelブックを好まないため、マクロは機能しません。
リストブロック内の行数を指定する代わりに、リストブロックにそのブロックを割り当てることができます。変更された式で使用できる独自の範囲名。サンプルファイルでは、2番目のリスト（名前）がこのメソッドを使用しています。ここでは、リストブロック全体（「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（行数）を置き換えます。
したがって、簡単に編集できるドロップダウンリスト（経験の浅い他のユーザーも含む）の場合、ダイナミックレンジ名を使用してみてください！また、この記事ではドロップダウンリストに焦点を当てていますが、ダイナミックレンジ名は、サイズが異なる可能性のある範囲またはリストを参照する必要がある場所で使用できます。お楽しみください！