Excelで増減に対応したリストからデータの入力規則を作成する方法

Excelにはデータの入力規則という機能があります。

あらかじめリストを作っておくことで、プルダウンで入力できるデータを指定することができます。

このデータの入力規則で、プルダウンで選択できるリストをセルに一覧で作成したときに、

項目の増減に対応するにはどうすればいいのでしょうか？

この記事では、データの入力規則で指定するデータの項目が増えたり減ったりしたときにも対応する方法について書いていきます。

OFFSET関数とCOUNTA関数を併用する方法です。

結論

こちらの数式を、

=OFFSET($A$2,0,0,COUNTA(A:A)-1,1)

データの入力規則で許可: リストを選択したときの元の値: に入力します。

Aの部分は、参照したい列のローマ字に変えてください。

このデータの入力規則をプルダウンを設定したいセルに設定することで、リストの増減に対応したプルダウンを作成することができます。

参照したリストはこちらです。

ここで愛知県を増やしてみます。

そうするとプルダウンで愛知県が選択できるようになりました。

また、元のリストを北海道だけにしてみます。

そうすると、プルダウンで北海道しか選択できなくなりました。

関数について解説

データの入力規則で使用したこちらの関数について解説していきます。

=OFFSET($A$2,0,0,COUNTA(A:A)-1,1)

OFFSET関数に5つの引数が渡され、第４引数ではCOUTA関数が使われています。

OFFSET関数

OFFSET関数は以下の書式となっています。

=OFFSET(基準となるセル, 基準からずれる行数, 基準からずれる列数, 行数の範囲, 列数の範囲)

参考にしたページ: OFFSET関数の便利な使い方 | ノンプログラミングWebアプリ作成ツール – Forguncy（フォーガンシー）| グレープシティ株式会社

例えば以下の果物と価格の表で試してみましょう。

G2セルを基準として、ずらす行数は0、ずらす列数は1、行数の範囲は1、列数の範囲も1に設定しました。

結果は、りんごから1列右の100となりました。

これがOFFSET関数です。

COUNTA関数

COUNTA関数は、範囲に含まれる空白ではないセルの数を返します。

以下の書式になっています。

=COUNTA(値1, 値2, ・・・）
※値2以降は省略可

以下の例では、H2からH4までの空白ではないセルの数が3ということが分かります。

OFFSETとCOUNTAの解説を踏まえて

OFFSET関数とCOUNTA関数の合わせ技であるこちらの式について見ていきます。

=OFFSET($A$2,0,0,COUNTA(A:A)-1,1)

第一引数

第一引数には、$A$2を指定しています。

これは基準となるセルです。

リスト内のデータであるA2セルが絶対参照で指定されています。

第二引数

第二引数には0を指定しています。

これは基準となるセルから行をずらさないという意味です。

基準となるセルで必要なデータを選択しているので、

行をずらす必要がありません。

第三引数

第三引数には0を指定しています。

これは基準となるセルから列をずらさないということです。

基準となるセルで指定しているセルで必要なデータを選択しているので、

列をずらす必要はありません。

第四引数

第四引数には、行数の範囲を指定します。

ここでCOUNTA関数の登場です。

以下のようにすることで、A列の空白ではないセルの数から1を引いた数を指定できます。

COUNTA(A:A)-1

タイトル行を除くために1を引きます。

以下のリストの場合、4となります。

愛知県と福岡県を追加すると、それに応じて値も4から6に変化しました。

第五引数

第五引数には、列数の範囲を指定します。

今回は1列なので1を指定しました。