どうもマサヤです！
プルダウンで使用しているリストが増えた場合、追加したリストはプルダウンに反映されませんよね。
データの入力規則でリストの範囲を変更すれば問題ありませんが、
リストが更新されるたびに同じ作業をするのは、面倒でストレスが貯まります。
そこで今日はリストが増えてもプルダウンリストに自動反映させる方法を紹介します！
併せてプルダウンの基本的な作り方も紹介します。
この方法を習得すればどれだけリストが追加されても大丈夫ですよ！
基本は不要、自動反映させるリストの作り方だけ見たい！
その場合は目次の2をクリックすれば確認いただけます。
連動したプルダウンを作りたい場合はこちら！
プルダウンリストの基本的な作り方
まずは基本のプルダウンリストの作り方です。
リストが追加されないと解っているものはこれで大丈夫です！
セルを参照しないプルダウンリスト
数が少ない2つ3つのリストのために、わざわざセルを使ってリストのは見た目的に微妙ですよね。
リストの数が少ない場合はデータの入力規則に直接書き込むほうがスマートです。
動画ではカルビのランクを例に挙げてます。※お肉食べたいですね)笑
,（カンマ） で区切ることでリストを直接作れます。
◆ 手順 ◆
① タブのデータ ⇒ データの入力規則
② タブの設定 ⇒ 入力値の種類：リストを選択
③ 元の値：カルビ極上,カルビ上,カルビを入力 ⇒ OK
セルを参照するプルダウンリスト
リスト数に変動はないものの、リスト内容に変更があるのについてはセルを参照する方法を使います。
一般的なプルダウンリストの作り方です。
方法もセル範囲を選択するだけなのでお手軽ですね。
◆ 手順 ◆
① タブのデータ ⇒ データの入力規則
② タブの設定 ⇒ 入力値の種類：リストを選択
③ 元の値：セル範囲 $A$2:$A$7を選択 ⇒ OK
選択できるセル範囲は一列のみなので注意しましょう。
複数列を選択すると下図のようなエラーが発生します。
プルダウンのリストが追加されても、リストに自動反映させる方法
さて、本題のリストが追加されても大丈夫なプルダウンの作り方を紹介します。
まずは動画をどうぞ！
動画を見るとA列のリストにホルモンとバラが追加されてますね。
プルダウンを確認すると、追加したホルモンとバラはリストには表示されていません。
データの入力規則を変更すると、追加した項目が表示されるようになりました。
◆ 手順 ◆
① タブのデータ ⇒ データの入力規則
② タブの設定 ⇒ 入力値の種類：リストを選択
③ 元の値：=OFFSET(A2,0,0,COUNTA(A:A),1)を入力 ⇒ OK
セル範囲ではなく、OFFSET関数といった見慣れないものが使われてますね。
このOFFSET関数がポイントです。
まずはOFFSET関数を説明した後、実際どういった動きとなっているかを解説していきます！
OFFSET関数とは？指定したセル範囲を参照できます！
OFFSET関数は、セル範囲の参照を返してくれます。
厳密には基準セルから指定した行列を移動したセルから高さ・幅を指定した範囲を返してくれます。
◆ 定義 ◆
OFFSET(基準, 行数, 列数, [高さ], [幅])
基準：どこのセルから始めるかを指定します。
行数；基準のセルから移動したい行数を指定します。
列数：基準のセルから移動したい列数を指定します。
高さ；移動したセルから参照するセルの高（行）さを指定します。
幅 ；移動したセルから参照するセルの幅（列）を指定します。
細かい説明は別の機会にして、今回はセル範囲を帰してくれることだけを覚えましょう！
例えば下図を例にすると
開始セルはB3（緑色）から行の移動は0、列の移動も0と指定されています。
高さは3、幅は2となっています。
行列移動は0のため開始セルはB3のまま、B3を含んだ高さ3行、横2列のセル範囲が返されます。
よって、セルB3:C5（赤色）の範囲が結果として返されることになります。
自動反映させるロジックを解説！
では、改めて下図を見た上でデータ入力規則の数式を解説をしていきます！
◆ 解説 ◆
= OFFSET(A2, 0, 0, COUNTA(A:A), 1)
基準（開始）セルはA2となっていますね。
行数・列数ともに0のため、基準セルのA2から移動はありません。
高さはCOUNTA（A:A）となっています。
COUNTAは指定された範囲で値のあるセルが何個あるかを返す関数となります。
A：Aとなっているので、A列全体で値のあるセルの数を帰してくれます。
よって、↑の図では9個となります。
幅は1なのでA列のまま、よって以下のようになります。
= OFFSET(A2, 0, 0, 9, 1)
↓
= A2:A10
もう一つのポイントが高さで使われているCOUNTA関数になります。
リストが追加されるとCOUNTAにより高さが変動します。
これによりデータ入力規則で参照するセル範囲が自動で広がることにるんです！
まとめ
リストが追加されても、自動でプルダウンの範囲を広げる方法を紹介しました。
ポイントはOFFSET関数とCOUNTA関数を組み合わせることです！
これでリスト追加されるたびに、データ入力規則で範囲を変更する必要がなくなります。
要するにリスト追加されたかどうかを考えなくて済むんです！
一つ考えることが減るということは生産性向上に大きく関係しますよね。
プルダウンのリスト範囲を自動で広げたいとお悩みのアナタ！
是非、お試しください！
コメント