Excel で入力規則を利用したドロップダウンリストを使用していると、まれに複数のリストをまとめて1つのドロップダウンリストとして表示させたい場合があります。例えば、商品マスタは本店で固定のものを用意して入力させたいけど、支店ごとにカスタムした独自の商品マスタも使いたい、といった場合です。
こういった場合は、2つのドロップダウンリストを連動させて、2つのリストに「本店」と「支店」という名前を設定し、INDIRECTで「本店」や「支店」をリスト名に指定することで、1つ目のリストに応じて2つ目のドロップダウンリストの中身を変化させる、といった手法が取られます。
ただ、今回は興味があったので、1つ目のリストと2つ目のリストを合算したリストを作成し、1つのドロップダウンリスト上で選択できる、ということを目指しました。
ドロップダウンリストに複数のリストを設定したときの制約
入力規則でリストのデータソースとして複数のドロップダウンリストを設定すると、次の画面のようなメッセージが表示されます。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/入力規則に二列を選択すると単一の行または列の参照にしろとのエラー-1024x507.png)
リストの元の値は、区切り文字で区切られたリストか、または単一の行または列の参照でなければなりません(The list source must be a delimited list, or a reference to single row or column)
あるいは、リストに複数のリスト名を指定してもエラーとなります。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/ドロップダウンリストに複数のリストを設定した場合-1024x564.png)
条件 データの入力規則には、参照演算子(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列のデータ項目の羅列を作ればよいです。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/通常のリストを2つ作成する.png)
最初に結論から言うと、①リスト1の末端を調べる。②リスト1の末端からリスト2の要素を表示させる。の2つさえ実現できれば、リストの結合は可能です。まずは、リスト1の末端を調べるために、リスト1の総個数を調べましょう。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト1の個数を数える.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト1の個数数える(完了).png)
次に、行数がわかるように行番号を数える列を追加しましょう。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/データが何行目かを表すNoの列を追加する.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/データが何行目かを表すNoの列を追加する(完了).png)
リスト1の末端以降かを判定するには、行数とデータ数を比較し、行数がデータ個数を超えていることで判定します。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/行数Noがリスト1の個数を超えているかどうか.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/行数Noがリスト1の個数を超えているかどうか(完了)-1024x561.png)
次に、行数がリスト1の個数を超えていた場合、リスト2の何番目の値を表示すればよいかを計算します。リスト1のデータがゼロ個ならリスト2の表示は行数を完全に一致するわけですからROW()となりますが、リスト1の個数分だけオフセットをかける必要がありますから、G列(リスト1のデータ個数)を引きます。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト2の何行目のデータを表示するか-1024x456.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト2の何行目のデータを表示するか(完了)-1024x467.png)
これで、リスト1の範囲を超えているかどうかと、リスト2の何行目のデータを表示するか、という2つのポイントがクリアできました。この状態でリスト1のデータ行を増やしたり減らしたりすることで、この数式がどのように作用するのかを知ることが出来ます。
次に、これらの列を利用して、リスト1の範囲を超えていたら(H列がTRUEなら)リスト2の指定行(I行)のデータを表示する、という数式を入力してみます。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト1の範囲を超えていたらリスト2を表示する-1024x472.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/リスト1とリスト2の結合-1024x467.png)
一部データに0が表示されていますが、INDIRECTの特徴として参照先のセルが空文字だと0が表示されます。参照先が空文字ではないときだけINDIRECTするように IF(INDIRECT(x)=””,””,INDIRECT(x))というような数式で囲めば、下記のようにきれいにリストが出ます。
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/空白だった場合の0を空文字に変換-1024x461.png)
![](https://blog.fits-inc.jp/wp-content/uploads/2020/07/ドロップダウンリストに複数のリストを指定する例-1024x455.png)
この後、F列をドロップダウンリストのデータ元として設定すれば、常にリスト1とリスト2を合わせたものをドロップダウンリストから選択させることが可能です。また、ドロップダウンリスト末尾の空白セルが気になる場合は、OFFSETとCOUNTIF(“?*”)でリストの範囲を適切に設定することで空白を除去することが可能です。
前任者不在のエクセルにお困りなら
弊社FITSは仙台でWebシステム、業務システムの受託開発のほか、エクセルを利用した業務改善や、基幹システム構築による脱エクセルなども支援しています。また、前任者が退職してしまったExcelのマクロやVBAについての相談対応や、解析業務も行っております。これらのことや、システム開発、導入にお困りでしたら無料のお見積りや相談をご利用ください。
株式会社FITSお問い合わせページ
https://www.fits-inc.jp/