【Excel・エクセル】プルダウンメニューをリストと連動!自動反映するには

入力,時短

もりの

プルダウンメニューのリストって、項目が増えるたびに範囲指定し直すのが面倒じゃない?

やまの

OFFSET関数を使えば、リストと連動できるんよ。

今回のポイント
  • プルダウンメニューの自動連動には、OFFSET関数を使う
  • 「=OFFSET(リストの一番上のセル,0,0,COUNTA(リストの列)-1,1)」
  • テーブルを範囲選択でもできる

スポンサーリンク

【Excel・エクセル】プルダウンのリストを自動で更新したい

プルダウンメニュー(ドロップダウンリスト)のデータをセル範囲で指定すると楽ですが……

プルダウンメニューの例
プルダウンメニューの例

項目を追加するときは、データ範囲も変更しないといけないので、ちょっと面倒ですよね。

自動で更新されないプルダウンメニュー
リストの更新が面倒

コレを、項目を追加しただけでプルダウンメニューのデータ範囲が自動で更新できるようにしたいと思います。

やまの

OFFSET関数を使うんよ。

OFFSET関数は、セルの位置や範囲を指定できる関数です。

OFFSET関数の書き方

=OFFSET(参照,行数,列数,高さ,幅)

OFFSETこの位置から、次の範囲を指定して
参照参照このセルから
行数縦方向にこのセル分(+なら下、-なら上)
列数横方向にこのセル分(+なら右、-なら左)の位置から
高さこのセル分下
このセル分右までの範囲
もりの

OFFSET関数でデータ範囲を指定するのか、難しそう……

やまのののんびりした顔
やまの

大丈夫、さっそくやり方を見てみよう。

【Excel・エクセル】プルダウン×OFFSET関数のやり方

ステップ1

まずはプルダウンメニューを作ります。

「データタブ」→データツールのところにある「データの入力規則」をクリックします。

データの入力規則の場所
データの入力規則の場所

ステップ2

出てきたメニューから、「データの入力規則」をクリックです。

データの入力規則
データの入力規則

ステップ3

「設定」→入力値の種類から、「リスト」を選んでください。

データの入力規則ダイアログボックス
データの入力規則ダイアログボックス

ステップ4

リストのメニューに切り替わりました。

そして実は、「元の値」のところには、実は数式もかけるんです。

データの入力規則ダイアログボックスのリストの画面
元の値のところに項目を書いていく
やまのののんびりした顔
やまの

先に言うと、「=OFFSET(リストの一番上のセル,0,0,COUNTA(リストの)-1,1)」だよ。

ステップ5

ここではOFFSET関数を使うので、「=OFFSET(」と入力します。

「=OFFSET(」まで入力した画像
まずは関数名

ステップ6

リストにしたいデータ範囲の一番上のセルをクリックします。

リストの一番上をクリックした画像
リストの一番上をクリック

ステップ7

セル範囲の開始位置は動かさなくていいので、「,0,0,」と入力しましょう。

OFFSET関数の行数と列数を指定した画像
最初のセルは動かしませんよってこと

ステップ8

さて、OFFSET関数の高さですが、COUNTA関数を使って表していきます。

COUNTA関数は、セル範囲に何かしら書いてある(空白以外の)セルの個数をカウントする関数です。

COUNTA関数の書き方

=COUNTA(値1,値2,……)

COUNTAこの中に空白以外のセルは何個ですか?
値1「この中に」の部分(範囲で指定する)
値2別のセル範囲も指定できる(省略可)

COUNTA関数でリストの個数を求めて、それを高さに使えばいいんですね。

リストの個数を求めたい画像
リストの個数を求めたい

とりあえず「COUNTA(」まで入力しちゃいましょう。

「COUNTA(」まで入力した画像
関数使うなら関数名を書くのじゃ

ステップ9

リストの個数を知りたいので、リストの列を選択します。

リストの列を選択した画像
リストの列を指定

ステップ10

COUNTA関数は終わりなのでカッコを閉じます。

ただこのままだと、セルD1の職業リストもカウントされてしまうので、「-1」します。

そして「,(カンマ)」

COUNTA関数から―1した画像
項目名のセルは除外

ステップ11

最後にOFFSET関数の幅を指定します。

セル範囲は1行分でいいので「1」と入力し、カッコを閉じます。

OFFSET関数の幅を指定した画像
幅を指定してフィニッシュ!

完成!

これで、自動連動するプルダウンメニューの完成です!

職業リストに新しく追加すると……

プルダウンメニューの例
プルダウンメニューの例

連動してプルダウンメニューに自動入力されましたね!

リストが自動で追加されたプルダウンメニュー
リストが自動で追加された

そのほかの自動連動プルダウンメニューの作り方

元の値をセル範囲で指定する際、その範囲がテーブルになっていれば、リストと連動できます。

テーブルの画像
テーブルを指定する
もりの

詳しくはこちらのページをご参照ください!

【Excel・エクセル】その他プルダウンメニューの連動

プルダウンとリストを連動して自動入力する

プルダウンメニューのリストを増やす際、いちいちリストの範囲も追加するのって面倒ですよね?

OFFSET関数を使えばリストと連動して、リストに追加するだけでプルダウンメニューの選択肢が自動入力されるんです!

プルダウンメニューには4つの選択肢しかありません。

D列のリストに項目を追加すると……

プルダウンメニューの例
種も仕掛けもございません

連動してプルダウンメニューに自動入力されました!

リストが自動で追加されたプルダウンメニュー
リストが自動で追加された
やまの

詳しくは、こっちのページを見てね!

プルダウンと連動して色付けする

プルダウンで選んだ選択肢によって、セルを付けることもできます。

条件付き書式を使いますよ。

自動で色分けされるプルダウンリスト
自動で色付けしてくれる
もりの

詳しくは、こっちのページをご覧ください!

これで、項目が増えてもいちいちデータ範囲を更新せずに済みますね!
それでは、お疲れさまでした!

今回のおさらい
  • プルダウンメニューの自動連動には、OFFSET関数を使う
  • 「=OFFSET(リストの一番上のセル,0,0,COUNTA(リストの列)-1,1)」
  • テーブルを範囲選択でもできる