Excelで入力規則のリスト項目を動的に増やすには？

　Excelの「入力規則」でドロップダウンリストを利用したことがある人も多いでしょう。しかし、 ドロップダウンリストの項目の個数を変更する場合、入力規則のセル範囲を修正する必要があります。

　そこで今回は、リストの項目の個数を変更しても、修正不要な方法について紹介したいと思います。

入力規則でドロップダウンリストの実装

　まず最初にドロップダウンリストを作成します。
①名前付きセルを表示する項目の範囲に指定しておきます。（例：入力項目リスト）
②ドロップダウンリストにするセルを指定し、データ/データの入力規則を選択します。
③[入力値の種類]を”リスト”に変更、「ドロップダウン リストから選択する」が選択されているかを確認します。
④[元の値]の右側のアイコンをクリックし、名前付きセルの範囲を指定し、OKボタンを押します。

名前付きセルの範囲指定方法を変更

　ここまでで入力規則でドロップダウンリストを実装することができました。しかしリストの項目を増減させる場合には再度名前付きセルの範囲指定をする必要があります。これを改善するために、名前付きセルの範囲指定の次ように変更していきます。

①数式/名前の管理から今回使用した名前付きセルを選択し、編集ボタンをクリックします。
②参照範囲に次の数式を入力して保存します。

=OFFSET(入力規則!$D$2, 1, 0,
　　　　　　　MAX(1, COUNTA(入力規則!$D:$D)-1), 1)

　この後にリストの項目を増やしてみると、ドロップダウンリストにも追加した項目が反映されていることが確認できます。

次の段落でこの数式について解説していきたいと思います。

解説

　ここでは参照範囲に指定した数式について解説していきたいと思います。

1．OFFSET関数：第1引数～第3引数

最初にOFFSET関数の引数について確認したいと思います。

引数：OFFSET( 基準セル, 行数, 列数, 行範囲, 列範囲)

　ExcelのOFFSET関数は基準セルから行数と列数だけ移動した位置にあるセルから、行範囲と列範囲で指定した範囲を返します。
上で利用した式をもとに考えてみましょう。

第１引数　基準セル： 入力規則!$D$2 （項目名）
第２引数　行数：１（一行下にずらす）
第３引数　列数：0（ずらさない）

　今回は項目名を基準に考えています。リストの項目には項目名は含めないので行数に1を指定して、参照するセルを一つ下のセルに変更しています。列はずらす必要がないので0を指定しています。

これで名前付きセルで指定する最初のセルが”D3″に定まりました。

2．OFFSET関数：第4引数、第5引数

　次に、名前付きセルで指定するセル範囲について考えていきます。

その前にOFFSET関数内で使用されているMAX関数とCOUNTA関数について確認したいと思います。

引数：MAX(数値 1, [数値 2], ・・・)

　ExcelのMAX関数は引数で指定した値の中で大きい数値を返します。

引数：COUNTA(範囲1, [範囲２] ・・・)

　ExcelのCOUNTA関数は引数で指定した範囲にある、空白でないセルの個数を返します。

これらの関数を踏まえて確認していきます。

第４引数　行範囲： MAX(1, COUNTA(入力規則!$D:$D)-1)
第５引数　列範囲：1

　第４引数で指定した MAX(1, COUNTA(入力規則!$D:$D)-1) を分解していきます。
COUNTA(入力規則!$D:$D )でD列で空白でないセルの個数を返しています。そこから項目名の分を引く必要があるので-1をする必要があります。COUNTA関数で求めた値と1をMAX関数で比較して大きい値を返しています。
列は１列だけなので1を指定しています。

これで選択される範囲が、（項目の個数行 or 1行, 1列）に定まりました。

　これらをまとめると、「項目名の下のセルを基準とし、入力されている項目の個数分だけ広げた範囲」が名前付きセルの範囲として設定されます。よって項目の個数を変更しても、自動的に範囲が更新されるのでその都度修正する必要がなくなります。

まとめ

　今回は、ドロップダウンリストの項目数を変更しても修正がいらない方法について紹介しました。今回使用したExcel関数はよく利用するものが多いので、ぜひ使いこなせるようになってください！