Excel で入力規則を利用したドロップダウンリストを使用していると、まれに複数のリストをまとめて１つのドロップダウンリストとして表示させたい場合があります。例えば、商品マスタは本店で固定のものを用意して入力させたいけど、支店ごとにカスタムした独自の商品マスタも使いたい、といった場合です。
こういった場合は、２つのドロップダウンリストを連動させて、２つのリストに「本店」と「支店」という名前を設定し、INDIRECＴで「本店」や「支店」をリスト名に指定することで、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列のデータ項目の羅列を作ればよいです。
最初に結論から言うと、①リスト１の末端を調べる。②リスト１の末端からリスト２の要素を表示させる。の２つさえ実現できれば、リストの結合は可能です。まずは、リスト１の末端を調べるために、リスト１の総個数を調べましょう。
次に、行数がわかるように行番号を数える列を追加しましょう。
リスト１の末端以降かを判定するには、行数とデータ数を比較し、行数がデータ個数を超えていることで判定します。
次に、行数がリスト１の個数を超えていた場合、リスト２の何番目の値を表示すればよいかを計算します。リスト１のデータがゼロ個ならリスト２の表示は行数を完全に一致するわけですからROW()となりますが、リスト１の個数分だけオフセットをかける必要がありますから、G列（リスト１のデータ個数）を引きます。
これで、リスト１の範囲を超えているかどうかと、リスト２の何行目のデータを表示するか、という２つのポイントがクリアできました。この状態でリスト１のデータ行を増やしたり減らしたりすることで、この数式がどのように作用するのかを知ることが出来ます。
次に、これらの列を利用して、リスト1の範囲を超えていたら（H列がTRUEなら）リスト２の指定行（I行）のデータを表示する、という数式を入力してみます。
一部データに0が表示されていますが、INDIRECTの特徴として参照先のセルが空文字だと0が表示されます。参照先が空文字ではないときだけINDIRECTするように IF(INDIRECT(x)=””,””,INDIRECT(x))というような数式で囲めば、下記のようにきれいにリストが出ます。
この後、F列をドロップダウンリストのデータ元として設定すれば、常にリスト１とリスト２を合わせたものをドロップダウンリストから選択させることが可能です。また、ドロップダウンリスト末尾の空白セルが気になる場合は、OFFSETとCOUNTIF(“?*”)でリストの範囲を適切に設定することで空白を除去することが可能です。
前任者不在のエクセルにお困りなら
弊社ＦＩＴＳは仙台でＷｅｂシステム、業務システムの受託開発のほか、エクセルを利用した業務改善や、基幹システム構築による脱エクセルなども支援しています。また、前任者が退職してしまったExcelのマクロやVBAについての相談対応や、解析業務も行っております。これらのことや、システム開発、導入にお困りでしたら無料のお見積りや相談をご利用ください。
株式会社ＦＩＴＳお問い合わせページ
https://www.fits-inc.jp/