Excelで入力フォームや台帳等を作るとき、プルダウン（データの入力規則）を使うことはよくありますよね。「データの入力規則」を使えば、意図しない値を入力されることを防げるので、とても便利です。
そんな便利な「データの入力規則」（プルダウン）ですが、通常の使い方だと、事前に決められた値を選択肢にすることしかできず、場合によって選択肢が変わる（可変のプルダウン）ということができません。
この記事では、一歩発展させて、ユーザーが入力した値に連動してプルダウンの内容が変更される、可変のプルダウンを作成する方法を解説します。
まずはサンプルでイメージを確認しよう
今回の成果物は、求人情報を入力するフォームにしてみました。
「大分類」に設定した値に連動して、「小分類」のプルダウン（入力規則）が変更されているのがわかりますでしょうか。
使用する機能・関数
今回は、以下の関数等を組み合わせて作成しました。
使ったことの無い関数があっても大丈夫です。順を追って説明していきます。
STEP1：選択肢のマスタ表を作る
今回は「営業」「事務」「医療」を職種の大分類とし、それぞれに４つの小項目を用意しました。
STEP2：大分類のプルダウン（入力規則）を作る
まずは「データの入力規則」基礎編として、大分類をプルダウン化しましょう。
これで、大分類を入力するC4セルが職種を選択するプルダウンになりました。
STEP3：小分類の入力規則を設定する
続いて、小分類の入力規則（プルダウン）を作っていきます。
STEP3-1：OFFSET関数を使ってプルダウンの選択肢を指定する
まずは第１段階として、OFFSET関数を使って、「営業」の小分類をプルダウンにする入力規則を考えます。
入力規則に設定する「元の値」の数式は以下のとおりになります。
<関数の構文> =OFFSET(基準, 行数, 列数, [高さ], [幅])
<使用例＞ =OFFSET($F$2,1,1,4,1)
なぜこのような式になるのか解説します。まずはOFFSET関数について簡単に説明します。
Offset関数は、①基準となるセルから、②指定した行数移動して、③指定した列数移動した地点のセル、もしくは、そのセルを基準として④高さ、⑤幅を定めたセル範囲を取得する関数です。
文字だけではわかりにくいと思いますので、下の画像を見てください。
先ほど提示した数式は、①F2セルを基準として、②下に１行移動、③右に１列移動、その位置から④高さ4行分、⑤幅１列分のセル範囲を指定するという意味になります。
これで「営業」の小分類である G3:G6 が指定できました。
STEP3-2：MATCH関数を使って選択範囲が自動で変わるようにする
ここまでの手順では、小分類のプルダウンの選択肢は、「営業」の小分類になっています。OFFSET関数の「列数」を固定値の１にしているからですね。
ここからは、「列数」の部分を「１」固定ではなく、MATCH関数を使って可変にすることで、大分類の入力値に連動して、小分類の選択肢が切り替わるようにしていきます。
<関数の構文> =MATCH(検査値, 検査範囲, [照合の型])
<使用例> =MATCH($C4,$G$2:$I$2,0)
MATCH関数は、①検査値に指定した値が、②検査範囲の左（または上）から数えて何番目にあるかを調べる関数 と思っていただいてOKです。
（[照合の型]についてはややこしいので説明を省略します。が、基本的には 0 を設定すると思います）
検査値が「営業」であれば、結果は 1 となりますし、検査値が「事務」であれば、結果は 2 となります。
これを利用して、先ほどのOFFSET関数の式を修正してみましょう。
<修正前> =OFFSET($F$2,1,1,4,1)
<修正後> =OFFSET($F$2,1,MATCH($C4,$G$2:$I$2,0),4,1)
この数式を D4セル の入力規則に設定すれば、「大分類」の入力値に連動して「小分類」のプルダウンの選択肢が変動するようにできます。
参考
いかがでしたか？
少し長くなりましたが、Excelの応用テクニックとして有用な技だと思いますので、是非理解してもらえればと思います。
最後に、参考にしたWEBサイトを紹介します。
コメント