柔軟なドロップダウンのためにExcelでダイナミックレンジ名を使用する

Excelスプレッドシートには、データ入力を簡素化および/または標準化するためのセルドロップダウンが含まれていることがよくあります。これらのドロップダウンは、データ検証機能を使用して作成され、許可されるエントリのリストを指定します。

簡単なドロップダウンリストを設定するには、データを入力するセルを選択し、データ検証をクリックします。 （データ タブ）、[データ検証]を選択し、[リスト]を選択します （[許可:)の下]をクリックし、ソースにリストアイテム（コンマで区切って）を入力します ：フィールド（図1を参照）。

このタイプの基本的なドロップダウンでは、許可されるエントリのリストがデータ検証自体の中で指定されます。したがって、リストに変更を加えるには、ユーザーはデータ検証を開いて編集する必要があります。ただし、これは、経験の浅いユーザーや選択肢のリストが長い場合には難しい場合があります。

もう1つのオプションは、スプレッドシート内の名前付き範囲にリストを配置し、その範囲名（等号で始まる）をソースで指定することです。 ：データ検証のフィールド（図2を参照）。

この2番目の方法を使用すると、リスト内の選択肢を簡単に編集できますが、項目の追加または削除には問題が生じる可能性があります。名前付き範囲（この例ではFruitChoices）は固定範囲のセル（図のように$ H $ 3：$ H $ 10）を参照しているため、H11以下のセルにさらに選択肢を追加すると、ドロップダウンに表示されません。 （これらのセルはFruitChoicesの範囲の一部ではないため）

同様に、たとえば、PearsとStrawberriesのエントリが消去された場合、それらはドロップダウンに表示されなくなりますが、ドロップダウンは空のセルH9とを含むFruitChoicesの範囲全体を参照するため、代わりに2つの「空の」選択肢が含まれます。 H10。

これらの理由から、ドロップダウンのリストソースとして通常の名前付き範囲を使用する場合、エントリがリストに追加またはリストから削除される場合は、名前付き範囲自体を編集して、セルを増減する必要があります。

この問題の解決策は、動的を使用することです。 ドロップダウン選択のソースとしての範囲名。ダイナミックレンジ名は、エントリが追加または削除されたときにデータのブロックのサイズに正確に一致するように自動的に拡張（または縮小）する名前です。これを行うには、式を使用します 、セルアドレスの固定範囲ではなく、名前付き範囲を定義します。

Excelでダイナミックレンジを設定する方法

通常の（静的）範囲名は、指定されたセル範囲を参照します（この例では、$ H $ 3：$ H $ 10、以下を参照）：

ただし、ダイナミックレンジは数式を使用して定義されます（ダイナミックレンジ名を使用する別のスプレッドシートから取得した以下を参照）：

開始する前に、Excelサンプルファイルをダウンロードしてください（並べ替えマクロが無効になっています）。

この式を詳しく調べてみましょう。果物の選択肢は、見出しのすぐ下のセルのブロックにあります（果物 ）。その見出しには、 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の選択肢が入力される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値のセットを作成し、OFFSET関数によって参照される20行の範囲内の個々のセルのそれぞれが空白（空）であるかどうかを示します。この例では、最初の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はデータのブロック全体を含む配列を返します。

数式の次の部分はMATCH関数です：

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

マッチ 関数は、INDEX関数によって返される配列内の最初のTRUE値の位置を返します。リストの最初の8つのエントリは空白ではないため、配列の最初の8つの値はFALSEになり、9番目の値はTRUEになります（9 ^th 以降） 範囲内の行は空です。

したがって、MATCH関数は 9の値を返します 。ただし、この場合、リストに含まれるエントリの数を知りたいので、数式はMATCH値（最後のエントリの位置を示します）から1を減算します。したがって、最終的に、MATCH（TRUE、上記、0）-1は 8の値を返します。 。

式の次の部分はIFERROR関数です：

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

指定された最初の値でエラーが発生した場合、IFERROR関数は代替値を返します。セルのブロック全体（20行すべて）がエントリでいっぱいになると、MATCH関数がエラーを返すため、この関数が含まれています。

これは、MATCH関数に（ISBLANK関数の値の配列で）最初のTRUE値を探すように指示しているためですが、空のセルが1つもない場合、配列全体が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（行数）を置き換えます。

したがって、簡単に編集できるドロップダウンリスト（経験の浅い他のユーザーを含む）の場合は、ダイナミックレンジ名を使用してみてください。また、この記事はドロップダウンリストに焦点を当てていますが、ダイナミックレンジ名は、サイズが異なる可能性のある範囲またはリストを参照する必要がある場所であればどこでも使用できることに注意してください。お楽しみください！