ドロップダウンリストに複数のリストを結合して設定する

 Excel で入力規則を利用したドロップダウンリストを使用していると、まれに複数のリストをまとめて1つのドロップダウンリストとして表示させたい場合があります。例えば、商品マスタは本店で固定のものを用意して入力させたいけど、支店ごとにカスタムした独自の商品マスタも使いたい、といった場合です。
 こういった場合は、2つのドロップダウンリストを連動させて、2つのリストに「本店」と「支店」という名前を設定し、INDIRECTで「本店」や「支店」をリスト名に指定することで、1つ目のリストに応じて2つ目のドロップダウンリストの中身を変化させる、といった手法が取られます。
 ただ、今回は興味があったので、1つ目のリストと2つ目のリストを合算したリストを作成し、1つのドロップダウンリスト上で選択できる、ということを目指しました。

ドロップダウンリストに複数のリストを設定したときの制約

 入力規則でリストのデータソースとして複数のドロップダウンリストを設定すると、次の画面のようなメッセージが表示されます。

 リストの元の値は、区切り文字で区切られたリストか、または単一の行または列の参照でなければなりません(The list source must be a delimited list, or a reference to single row or column)

 あるいは、リストに複数のリスト名を指定してもエラーとなります。

条件 データの入力規則には、参照演算子(OR 演算子、AND 演算子、範囲演算子などおよび配列定数は使用できません(You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria)

2つのリストを結合したリストを作成する

 2つのリストを結合したリストを作成することでこの問題を解決してみましょう。まずは、通常通りリストを2つ作成します。適当に2列のデータ項目の羅列を作ればよいです。

 最初に結論から言うと、①リスト1の末端を調べる。②リスト1の末端からリスト2の要素を表示させる。の2つさえ実現できれば、リストの結合は可能です。まずは、リスト1の末端を調べるために、リスト1の総個数を調べましょう。

 次に、行数がわかるように行番号を数える列を追加しましょう。

 リスト1の末端以降かを判定するには、行数とデータ数を比較し、行数がデータ個数を超えていることで判定します。

 次に、行数がリスト1の個数を超えていた場合、リスト2の何番目の値を表示すればよいかを計算します。リスト1のデータがゼロ個ならリスト2の表示は行数を完全に一致するわけですからROW()となりますが、リスト1の個数分だけオフセットをかける必要がありますから、G列(リスト1のデータ個数)を引きます。

 これで、リスト1の範囲を超えているかどうかと、リスト2の何行目のデータを表示するか、という2つのポイントがクリアできました。この状態でリスト1のデータ行を増やしたり減らしたりすることで、この数式がどのように作用するのかを知ることが出来ます。

 次に、これらの列を利用して、リスト1の範囲を超えていたら(H列がTRUEなら)リスト2の指定行(I行)のデータを表示する、という数式を入力してみます。

 一部データに0が表示されていますが、INDIRECTの特徴として参照先のセルが空文字だと0が表示されます。参照先が空文字ではないときだけINDIRECTするように IF(INDIRECT(x)=””,””,INDIRECT(x))というような数式で囲めば、下記のようにきれいにリストが出ます。

 この後、F列をドロップダウンリストのデータ元として設定すれば、常にリスト1とリスト2を合わせたものをドロップダウンリストから選択させることが可能です。また、ドロップダウンリスト末尾の空白セルが気になる場合は、OFFSETとCOUNTIF(“?*”)でリストの範囲を適切に設定することで空白を除去することが可能です。

エクセルにお困りなら

 弊社FITSは仙台でWebシステム、業務システムの受託開発を行っているほか、エクセルを利用した業務改善や、基幹システム構築による脱エクセルなども支援しています。もし、システム開発、導入にお困りでしたら無料のお見積りや相談をご利用ください。

株式会社FITSお問い合わせページ
https://www.fits-inc.jp/