Excelスプレッドシートには、データ入力を簡素化および/または標準化するためのセルドロップダウンが含まれていることがよくあります。これらのドロップダウンは、データ検証機能を使用して作成され、許可されるエントリのリストを指定します。
簡単なドロップダウンリストを設定するには、データを入力するセルを選択し、[データ検証(Data Validation)] （ [データ(Data)]タブ）をクリックし、[データ検証]を選択し、[(Data Validation)リスト(List)] （[許可(Allow):)]を選択して、リスト項目を入力します（コンマで区切ってください）。 ）[ソース(Source)：]フィールドに入力します（図1を参照）。
このタイプの基本的なドロップダウンでは、許可されるエントリのリストがデータ検証自体の中で指定されます。したがって、リストに変更を加えるには、ユーザーはデータ検証を開いて編集する必要があります。ただし、これは、経験の浅いユーザーや選択肢のリストが長い場合には難しい場合があります。
もう1つのオプションは、スプレッドシート内の名前付き範囲に(named range within the spreadsheet)リストを配置し、データ検証の[ソース(Source)：]フィールドでその範囲名（等号で始まる）を指定することです（図2(Figure 2)を参照）。
この2番目の方法を使用すると、リスト内の選択肢を簡単に編集できますが、項目の追加または削除には問題が生じる可能性があります。名前付き範囲（この例ではFruitChoices）は固定範囲のセル（図のように$ H $ 3：$ H $ 10）を参照しているため、 H11以下のセルにさらに選択肢を追加すると、ドロップダウンに表示されません。 （これらのセルはFruitChoicesの範囲の一部ではないため）。
同様に、たとえば、梨(Pears)とイチゴ(Strawberries)のエントリが消去された場合、それらはドロップダウンに表示されなくなりますが、ドロップダウンは空のセルH9とを含むFruitChoicesの範囲全体を参照するため、代わりに2つの「空の」選択肢が含まれます。H10。
これらの理由から、ドロップダウンのリストソースとして通常の名前付き範囲を使用する場合、エントリがリストに追加または削除される場合は、名前付き範囲自体を編集して、セルを増減する必要があります。
この問題の解決策は、ドロップダウンの選択肢のソースとしてダイナミックレンジ名を使用することです。(dynamic)ダイナミックレンジ名は、エントリが追加または削除されたときにデータのブロックのサイズに正確に一致するように自動的に拡張（または縮小）する名前です。これを行うには、セルアドレスの固定範囲ではなく数式を使用して、名前付き範囲を定義します。(formula)
Excelで(Excel)ダイナミックレンジ(Dynamic Range)を設定する方法
通常の（静的）範囲名は、指定されたセル範囲を参照します（この例では、$ H $ 3：$ H $ 10、以下を参照）。
ただし、ダイナミックレンジは、式を使用して定義されます（ダイナミックレンジ名を使用する別のスプレッドシートから取得した以下を参照）。
開始する前に、 Excelサンプルファイルをダウンロードしてください （ソートマクロは無効になっています）。
この式を詳しく調べてみましょう。Fruitsの選択肢は、見出し（ FRUITS(FRUITS) ）のすぐ下のセルのブロックにあります。その見出しにも名前が割り当てられます：FruitsHeading：
Fruitsの選択肢のダイナミックレンジを定義するために使用される式全体は次のとおりです。
=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列の領域を選択します。これで、 Fruits(Fruits)の選択肢が入力される20行のブロックが得られます。
式の次の部分はISBLANK関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
ここでは、OFFSET関数（上記で説明）が「上記」に置き換えられています（読みやすくするため）。ただし、ISBLANK関数は、 (ISBLANK)OFFSET関数が定義する20行のセル範囲で動作しています。
次に、 ISBLANK(ISBLANK)は20個のTRUE値とFALSE値のセットを作成し、 (FALSE)OFFSET関数によって参照される20行の範囲内の個々のセルのそれぞれが空白（空）であるかどうかを示します。この例では、最初の8つのセルは空ではなく、最後の12の値はTRUEになるため、セットの最初の8つの値は(TRUE)FALSEになります。
式の次の部分は、INDEX関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
繰り返しになりますが、「上記」とは、上記のISBLANKおよびOFFSET関数を指します。INDEX関数は、 ISBLANK関数によって作成された20個の(ISBLANK)TRUE / FALSE値を含む配列を返します。
INDEXは通常、特定の行と列（そのブロック内）を指定することにより、データのブロックから特定の値（または値の範囲）を選択するために使用されます。ただし、行と列の入力をゼロに設定すると（ここで行われるように）、INDEXはデータのブロック全体を含む配列を返します。
式の次の部分はMATCH関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH関数は、 (MATCH)INDEX関数によって返される配列内の最初のTRUE値の位置を返します。リストの最初の8つのエントリは空白ではないため、配列の最初の8つの値はFALSEになり、9番目の値はTRUEになります（範囲の9番目の行が空であるため）。
したがって、MATCH関数は9の値を返します。ただし、この場合、リストに含まれるエントリの数を知りたいので、数式はMATCH値（最後のエントリの位置を示します）から1を減算します。したがって、最終的に、MATCH（TRUE、上記、0）-1は8の値を返します。
式の次の部分はIFERROR関数です。
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
指定された最初の値でエラーが発生した場合、IFERROR関数は代替値を返します。(IFERROR)セルのブロック全体（20行すべて）がエントリで埋められている場合、MATCH関数はエラーを返すため、この関数が含まれています。
これは、MATCH関数に（ (MATCH)ISBLANK関数からの値の配列内の）最初のTRUE値を探すように指示しているためですが、セルのNONEが空の場合、配列全体が(NONE)FALSE値で埋められます。MATCHは、検索している配列でターゲット値（TRUE ）を見つけることができない場合、エラーを返します。
したがって、リスト全体がいっぱいの場合（したがって、MATCHはエラーを返します）、IFERROR関数は代わりに値20を返します（リストには20のエントリが必要であることがわかっています）。
最後に、OFFSET（FruitsHeading、1,0、上記、1）(OFFSET(FruitsHeading,1,0,the above,1))は、実際に探している範囲を返します。FruitsHeadingセルから開始し、1行下に0列を超えてから、行数が多い領域を選択します。リストにはエントリがあります（1列幅）。したがって、数式全体が一緒になって、実際のエントリのみを含む範囲（最初の空のセルまで）が返されます。
この数式を使用してドロップダウンのソースとなる範囲を定義すると、リストを自由に編集でき（残りのエントリが一番上のセルから始まり、連続している限り、エントリを追加または削除できます）、ドロップダウンには常に現在の値が反映されます。リスト（図6(Figure 6)を参照）。
ここで使用されているサンプルファイル（動的リスト）が含まれており、このWebサイトからダウンロードできます。ただし、WordPressはマクロを含む(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（行数）を置き換えます。
したがって、簡単に編集できるドロップダウンリスト（経験の浅い他のユーザーも含む）の場合は、ダイナミックレンジ名を使用してみてください。また、この記事はドロップダウンリストに焦点を当てていますが、ダイナミックレンジ名は、サイズが異なる可能性のある範囲またはリストを参照する必要がある場所であればどこでも使用できることに注意してください。楽しみ！
Use Dynamic Range Names in Excel for Flexible Dropdowns
Excel sрreadsheets oftеn include cell dropdowns to simplify and/or standardize data entry. These dropdowns are created using the data validation feature to specify a list of allowable entriеs.
To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).
In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation. This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.
Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).
This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic. Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).
Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.
For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.
A solution to this problem is to use a dynamic range name as the source for the dropdown choices. A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed. To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.
How to Setup a Dynamic Range in Excel
A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):
But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):
Before we get started, make sure you download our Excel example file (sort macros have been disabled).
Let’s examine this formula in detail. The choices for Fruits are in a block of cells directly below a heading (FRUITS). That heading is also assigned a name: FruitsHeading:
The entire formula used to define the dynamic range for the Fruits choices is:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading refers to the heading that is one row above the first entry in the list. The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).
Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added. The number 20 refers to the entire block where entries can be made, not to the actual number of entries.
Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1). This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered. This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide. So that gives us the 20-row block where the Fruits choices are entered.
The next piece of the formula is the ISBLANK function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read). But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.
ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not. In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.
The next piece of the formula is the INDEX function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Again, “the above” refers to the ISBLANK and OFFSET functions described above. The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.
INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block). But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.
The next piece of the formula is the MATCH function:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function. Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9th row in the range is empty).
So the MATCH function will return the value of 9. In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry). So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.
The next piece of the formula is the IFERROR function:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
The IFERROR function returns an alternate value, if the first value specified results in an error. This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.
This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values. If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.
So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).
Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide). So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).
Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).
The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.
As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula. In the example file, a second list (Names) uses this method. Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock. The alternate formula for defining the NamesList is then:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.
So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names! And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!
