【Excel】可変・連動のプルダウン(入力規則)を作ろう

Excelで入力フォームや台帳等を作るとき、プルダウン(データの入力規則)を使うことはよくありますよね。「データの入力規則」を使えば、意図しない値を入力されることを防げるので、とても便利です。

そんな便利な「データの入力規則」(プルダウン)ですが、通常の使い方だと、事前に決められた値を選択肢にすることしかできず、場合によって選択肢が変わる(可変のプルダウン)ということができません。

この記事では、一歩発展させて、ユーザーが入力した値に連動してプルダウンの内容が変更される、可変のプルダウンを作成する方法を解説します。

まずはサンプルでイメージを確認しよう

今回の成果物は、求人情報を入力するフォームにしてみました。

「大分類」で「営業」を選ぶか「事務」を選ぶかで、「小分類」の選択肢が変わっている

「大分類」に設定した値に連動して、「小分類」のプルダウン(入力規則)が変更されているのがわかりますでしょうか。

使用する機能・関数

今回は、以下の関数等を組み合わせて作成しました。

今回使う機能・関数
  • データの入力規則
  • Offset関数
  • Match関数

使ったことの無い関数があっても大丈夫です。順を追って説明していきます。

STEP1:選択肢のマスタ表を作る

今回は「営業」「事務」「医療」を職種の大分類とし、それぞれに4つの小項目を用意しました。

3つの職種とそれぞれの小分類を表にした

STEP2:大分類のプルダウン(入力規則)を作る

まずは「データの入力規則」基礎編として、大分類をプルダウン化しましょう。

データ → データの入力規則
種類を「リスト」にし、元の値は 職種マスタの 営業~医療 の部分を指定

これで、大分類を入力するC4セルが職種を選択するプルダウンになりました。

STEP3:小分類の入力規則を設定する

続いて、小分類の入力規則(プルダウン)を作っていきます。

STEP3-1:OFFSET関数を使ってプルダウンの選択肢を指定する

まずは第1段階として、OFFSET関数を使って、「営業」の小分類をプルダウンにする入力規則を考えます。

入力規則に設定する「元の値」の数式は以下のとおりになります。

<関数の構文> =OFFSET(基準, 行数, 列数, [高さ], [幅])

<使用例> =OFFSET($F$2,1,1,4,1)

なぜこのような式になるのか解説します。まずはOFFSET関数について簡単に説明します。

Offset関数は、①基準となるセルから、②指定した行数移動して、③指定した列数移動した地点のセル、もしくは、そのセルを基準として④高さ、⑤幅を定めたセル範囲を取得する関数です。

文字だけではわかりにくいと思いますので、下の画像を見てください。

OFFSET($F$2,1,1,4,1) が指定するセル範囲

先ほど提示した数式は、①F2セルを基準として、②下に1行移動③右に1列移動、その位置から④高さ4行分⑤幅1列分のセル範囲を指定するという意味になります。

これで「営業」の小分類である G3:G6 が指定できました。

STEP3-2:MATCH関数を使って選択範囲が自動で変わるようにする

ここまでの手順では、小分類のプルダウンの選択肢は、「営業」の小分類になっています。OFFSET関数の「列数」を固定値の1にしているからですね。

ここからは、「列数」の部分を「1」固定ではなく、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サイトを紹介します。

Office TANAKA - ワークシート関数[OFFSET 関数]
OFFSET 関数
書式: OFFSET(基準, 行数, 列数 , )
MATCH 関数
書式: MATCH(検査値, 検査範囲, )

コメント

タイトルとURLをコピーしました