Excel 入力規則リストの選択肢を連動させる方法
Excel入力規則のリストにて選択肢を連動させる方法です。
Excel入力規則にはリストというものがあります。ここでいう選択肢の連動というのは、1つ目のリストを変更すると2つ目のリストの中身が変動するという意味となります。企業では自社で取り扱っている商品を、大分類、中分類、小分類のようにカテゴリー別に管理しますね。
つまり、ここでやりたいのは、大分類を変更したら、それに属する中分類だけをリストに表示したい、ってことです。
ここでは Excel入力規則のリストで選択肢を連動させる方法 を紹介します。
目次
入力規則のリストで選択肢を連動する方法
それではExcel入力規則のリストで選択肢を連動させる方法について解説します。まずはデータを準備しておきましょう。
ここでは下図のようなデータを用意しました。
これから説明する方法を使うにあたり、上位の分類で並び替えをしておく必要がありますのでご注意ください。
リストの連動にはOFFSET関数、MATCH関数、COUNTIF関数を使います。
OFFSET関数を利用する
2つ以上のリストを連動するにはOFFSET関数を使います。
OFFSET関数の仕様は下記のようになります。
OFFSET(基準セル,行数,列数,高さ,幅)
指定した基準セルから指定した行数、列数の範囲への参照を返します。
ふむ、まったくもって理解しづらい関数ですな。引数も多いし。
仮に「=OFFSET(A1,2,1,1,1)」と入力した場合、基準となるセル(A1)から下に2行、右に1列動いた位置に基準セルを移動するという意味になります。つまりセルB3へ移動して、高さと幅=1(1行×1列の範囲)の値を返してくれるってことですね。
詳しくは「Excel OFFSET関数」などで検索してみてください。
今回はこのOFFSET関数を使って、リストの開始位置と個数を計算して取得するという方法を採用します。
MATCH関数で開始位置を取得する
開始位置の取得にはMATCH関数を利用します。
MATCH関数の仕様は下記のようになります。
MATCH(検査値,検査範囲,照合の種類)
指定された照合の種類に従って検査範囲内を検索し、検査値と一致する要素の、配列内での対照的な位置を表す数値を返します。
ふむ、まったくもって何を言っているのかわからん関数ですな。
要約すると、範囲内に検索する値が何列目にあるのかを返してくれる関数になります。
検査値：検索する値
検査範囲：検索する範囲
照合の種類：検索方法を指定する
(1→検査値以下の最大値, 0→検査値に一致する値のみ, -1→検査値以上の最小値)
仮に下図のように「=MATCH(B3,1:1,0)」とすると、3列目にあるよって教えてくれるんですね。
これで開始位置の取得方法がわかりました。
COUNTIF関数で個数を取得する
最後にCOUNTIF関数で個数を取得します。
COUNTIF関数の仕様は下記のようになります。
COUNTIF(範囲,検索条件)
指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。
ふむ、これは簡単ですね。
仮に下図のように「=COUNTIF(A1:A7,C2)」とすると、3個あるよって教えてくれるんですね。
これで個数の取得方法がわかりました。
2つのリストを連動する
リストの連動に必要なOFFSET関数、MATCH関数、COUNTIF関数の使い方が理解できたところで早速やってみましょう。
こんな感じの表を用意します。1つ目のリストをA2へ設定し、2つ目のリストをB2へ連動します。
A2の1つ目のリストにはD列のCATEGORYをプルダウンに設定します。
=$D$2:$D$3
B2の2つ目のリストにはF列のCATEGORYとMODELを連動させてプルダウンに設定します。
=OFFSET($G$1,MATCH($A2,$F:$F,0)-1,0,COUNTIF($F:$F,$A2))
「元の値 はエラーと判断されます。続けますか？」のメッセージには「はい」としてください。
A列CATEGORYに「ノート」と選択してB列MODELのプルダウンを開くと、、、
おおおー、F列が「ノート」のMODELだけが出てきたー！
こちらにサンプルを置いておきますので、自己責任にてご利用ください。
3つのリストを連動する
今度は3つのリストを連動します。OFFSET関数、MATCH関数、COUNTIF関数を使うことは変わりませんが、検索する値は1つ目と2つ目の選択値を結合したものになります。
今度は各シートにデータを作ります。これによりマスタデータが増減した際にも対応できます。
Sheet1に下図のように1行目にタイトルを設け、2行目に入力規則リストの数式を入れます。
A2の数式
=CATEGORY!$A:$A
B2の数式
=OFFSET(MANUFACTURER!$B$1,MATCH($A2,MANUFACTURER!$A:$A,0)-1,0,COUNTIF(MANUFACTURER!$A:$A,$A2))
C2の数式
=OFFSET(MODEL!$C$1,MATCH($A2&$B2,MODEL!$A:$B,0)-1,0,COUNTIF(MODEL!$A:$B,$A2&B2))
A2、B2と選択してC2のプルダウンを開くと、、、
おおおー、選択肢が連動しているーー！
こちらにサンプルを置いておきますので、自己責任にてご利用ください。
まとめ
Excel入力規則のリストで選択肢を連動させる方法を紹介しました。
今回紹介した3つのリストを連動する方法は、シート別にデータを作ることでデータ増減をカバーしています。マスタデータをシステムから取得できる場合には有効ですが、手入力の場合には管理が煩わしく感じるかも。
これ以外にも名前定義を経由して構造化参照を入力規則に設定することもできます。正直、名前定義って複雑化するイメージがあって好きじゃないので説明は割愛しますね。
おつかれさまでした。