Excelのリスト（プルダウン）の内容を変える方法～その２～
エクセルのプルダウンリストで臨機応変に内容を変えたい！
を実現させるためのTipsです。
最終的なゴール
以下のGIFのような条件タイプに合わせて条件引数１／条件引数２を連動して変更するリストを作ります。クエストのクリア条件を設定するエクセルだと思ってください。
一応下記URLからワークショップ用のデータも用意してみました。完成品もあります。
Excelのリスト（プルダウン）の内容を変える方法～その２～ - Google ドライブ
前回の「Excelのリスト（プルダウン）の内容を変える方法～その１～」より難しい内容となりますので、ご覧でない方はご一読された方が良いかもしれないです。
coposuke.hateblo.jp
私はエクセルのテーブル機能が好きなので、
今回もテーブルを活用した状態でリストの内容を変える方法をご紹介いたします。
得られるノウハウ
そもそもプルダウンリストって内容変えられるの？
入力規則の元の値をセル毎に個別に設定せずとも、参照するセル範囲を変えることでリストの内容を変えることが出来ます。今回はそのテクニックを中心に解説いたします。
そもそもプルダウンリストってテーブルの内容を表示できるの？
～その１～では名前の管理を用いてテーブルの範囲を間接的に参照する解決方法を解説いたしましたが、今回はINDIRECT関数というものを用いた解決方法です！
状況説明
クエスト一覧シート と 条件テーブルシート の2つのシートがあります。
クエスト一覧シートにあるテーブルはクエストのクリア条件等のデータを管理。
条件テーブルシートには複数のテーブルがあります。今回は左の２つの青いテーブル（条件一覧参照テーブル、条件内容参照テーブル）が連動で大きな役割を持ちます。
本件の実装のポイントは、入力規則のリストが参照するセル範囲が以下のように変わることです。ここ重要です。
キャラクターマスター、エネミーマスター等はそれぞれのデータを管理するテーブルです。マスターとはマスターデータのことで、ゲームを作る際のレベルデザインをしやすいようにパラメータを外部データに逃がしたデータ群のことです。
通常これらのマスターデータはエクセルがバラバラになっているのですが、別エクセルのセル参照も可能ですので、今回の内容を利用することが出来ます。それは又別の機会に解説いたします。
- 条件タイプ入力箇所は、条件タイプがプルダウンで表示
- 条件引数１の入力箇所は、条件タイプに沿った内容をプルダウンで表示
- 条件引数２の入力箇所も、条件タイプに沿った内容をプルダウンで表示（ただし、数値入力もさせることも想定）
- 各入力内容をIDで自動入力
やり方
テーブルの名前をあらかじめ調整
これは前回の～その１～で解説したので簡単に「名前の管理」を。
今回はテーブルが多いので、テーブルの上のセルに名前を入れておきました。
（クエストマスター以外は）
しれっとConditionList等のセル範囲も入っちゃってますが、次に解説いたします。
条件タイプの入力規則リストの設定
まずは簡単に条件タイプのリスト設定をします。
前回（その１）で解説いたしました「リストにテーブル名が使えない」という問題があるので、早速条件タイプの選択肢『条件一覧参照テーブル[説明]』に名前を付けましょう。名前はConditionListにします。
名前を付けたら、該当セルに入力規則のリストを設定します。これも前回同様です。
下列セルのコピーしたほうが後の作業がスムーズです。
前回と同じなのでだいぶ端折りましたが、次から複雑になってきます。
条件タイプのIDとテーブル行を自動入力
マスターデータなので、IDの出力は必要になります。
例えばどのクエストをクリアしたのかの判定や保存にこのIDを使います。
IDの出力方法は前回にも登場したINDEX関数とMATCH関数を使います。
いわゆる、
”条件一覧参照テーブル[説明]” から ”条件タイプの内容” を検索し
検出した行の ”ID” をピックアップする
という感じです。
・INDEX関数は、特定セル範囲の行、列の番号にあるセルの内容を返す関数。
・MATCH関数は、特定セル範囲内から同じ内容のセルの列(または行)を返す関数。
つまり、
= INDEX( 条件一覧参照テーブル, 検出行、ID列番号 = 1)
検出行 … MATCH(条件タイプの内容, 条件一覧参照テーブル[説明], 0)
このようなイメージになります。条件一覧参照テーブル[説明]は先程のConditionListと同じですが、今回はセルに直接入力する為テーブル名を使います。
このMATCH関数で帰ってくる行番号は頻繁に使う為、「条件行番号」という列がテーブルにあります。なので先にこちらから作っていきましょう。入力内容は、
=MATCH( [@条件タイプ], 条件一覧参照テーブル[説明], 0)
になります。
[@条件タイプ]はそのセルと同じ行の「条件タイプ列」のセル内容を示します。
「$D5」のようなセル番地を直接入力しても大丈夫ですが、[@ほげほげ]の方が視認性が高いです。これで条件行番号に数値が自動入力されます。
次にINDEX関数で条件IDを自動入力していきます。せっかくテーブルに「条件行番号列」があるので、これを使っちゃいましょう。
=INDEX(条件一覧参照テーブル、[@条件行番号], 1)
これで条件IDが自動入力されました。
条件引数１の入力規則リストの設定をする前に…INDIRECT関数とは
さて、ここで本題に突入しました。
前回はセル範囲に名前をつけ、それをリストの元の値に入力することでテーブル内からプルダウンリストを作ることが出来ましたが、今回は「条件タイプに合わせて内容を変える」必要があります。それにはINDIRECT関数という便利な関数があります。
INDIRECT関数とは、セル番地等を文字列を使ってセルの内容を返す関数です。
こちらのサイトはとても分かりやすく解説されています
Office TANAKA - ワークシート関数の解説[INDIRECT関数]
「=A1」と「INDIRECT("A1")」は、セル番地A1の内容を返してくれます。
これを応用するとこんな使い方ができます。
内容は「INDIRECT(C2)」ですが、C2は"A1"の文字列が入力されている為、「INDIRECT("A1")」と同等の処理がされ、結果「コポコポ」が表示されます。
ちょっとややこしいかもしれないですが、ここはとても重要なポイントです。
さらにINDIRECTのすごい所は、セル範囲も指定できるというところです。
簡単に入力規則リストを使って試すことが出来ます。
どのセルでも構いませんので選択肢し、
入力規則リストに以下のように入力してみてください。
=INDIRECT( "B5:B14" )
するとB5～B14の間にあるセルがプルダウンリストに表示されます。
更にこのINDIRECT関数はテーブル名も使うことが出来るのです。
次に入力規則リストに以下のように入力してみてください。
=INDIRECT( "キャラマスター[キャラ名]" )
するとキャラマスター[キャラ名]の列の内容が全てプルダウンリストに表示されます。
…なんとなくやるべきことがわかりましたでしょうか？
この機能を応用し、"キャラマスター[キャラ名]"という文字列をどこかのセルに入力しておき、INDIRECT関数で見に行くセルを変えるだけでリストの内容を変えることが出来るのです。
条件引数１の入力規則リストの設定
INDIRECT関数の役割が分かったところで、条件引数１を作っていきます。
こんなこともあろうかと、条件一覧参照テーブル[検索参照セル範囲]に見に行くセルを打ち込んでおきました！！
これを条件タイプ毎に行を変えるだけ…ってその行も先程の「条件行番号」で自動入力済みです！！
あとは「リストにテーブル名が使えない」問題があるので、条件一覧参照テーブルのセル範囲に名前を付けましょう。名前はConditionListTableにします。
入力するリストの内容は以下の通りです。
=INDIRECT( INDEX(ConditionListTable, $F5, 3) )
$F5はほんっとうは[@条件行番号]としたいところですが、テーブル名は使えないのでセル番地を直接していしています。下列にコピーするときは自動的に$F6, $F7となるので大丈夫です。
条件引数２の入力規則リストの設定
この調子で条件引数２も作ります。
使用するテーブルは「条件内容参照テーブル」です。まずは名前登録から始めます。
名前はConditionValueTableにします。
入力するリストの内容は以下の通りです。
=INDIRECT( INDEX(ConditionValueTable, $F5, 3) )
さっきの条件引数１とほぼ同等の内容です。参照するテーブルがConditionValueTableになっただけの変更となります。
こちらの条件内容参照テーブルの見に行くセルを打ち込んでおきました。
数値入力を促す場合は、セル範囲ではなくセル番地が入力されています。
条件引数のIDの自動入力
これで完成！！と言いたいところですが、これはマスタデータですので、
条件引数１・２をそれぞれIDにする必要があります。
たぶんこれが一番面倒ですが、頑張っていきましょう。
条件タイプにあったテーブルから、条件引数で入力された行のIDを出力します。
セル検索なのでINDEX関数とMATCH関数ですが、テーブルがタイプ毎に変えるためにINDIRECT関数を使うので若干ややこしいです。
INDEX関数とMATCH関数は以下のような引数でした。
=INDEX(検索テーブル、行、列)
=MATCH(検索値、検索テーブル、0 - 完全一致)
つまり下の画像のように参照してゆけば切り替えることができます。
…ちょっとGIFの切り替わりが速いと思うので順を追っていきます。
まずは最初の検索テーブル特定はこのような感じ。
=INDIRECT(エネミーマスタ)
=INDIRECT(条件一覧参照テーブル[@全体参照セル範囲])
だいぶ簡略化して書きましたが、＠は他人テーブルには使えない（クエストマスターテーブルから見て他人）ので、条件一覧参照テーブルに対してもINDEX関数を用いる必要があります。行は「条件行番号」で分かっているので、
=INDEX(条件一覧参照テーブル[全体参照セル範囲]、[@条件行番号]) 又は
=INDEX(条件一覧参照テーブル、[@条件行番号]、4)
で検索テーブルの"名前"が特定できるので、これをINDIRECTして
=INDIRECT( INDEX(条件一覧参照テーブル[全体参照セル範囲]、[@条件行番号]) )
とすれば、図の矢印のような参照ができあります。
次に行の特定はこのような感じ。
=MATCH("ミサイル団"、エネミーマスター[敵名]、0)
=MATCH("ミサイル団"、INDIRECT(条件一覧参照テーブル[@検索参照セル範囲])、0)
図の場合エネミーマスターテーブルの"ミサイル団"の行を特定したいので、MATCH関数を用いる必要があります。こちらも＠が使われているので、直して書くと
=INDIRECT(条件一覧参照テーブル[@検索参照セル範囲])
=INDIRECT( INDEX(条件一覧参照テーブル[検索参照セル範囲]、[@条件行番号])
このようになります。"ミサイル団"に当たる箇所は「条件引数１」ですので、
=MATCH(
[@条件引数1]、
INDIRECT( INDEX(条件一覧参照テーブル[検索参照セル範囲]、[@条件行番号]))、
0)
このようになります。結構複雑になってきました。ここまでくると「条件行番号」を用意した意味が分かっていただけたかと思います。
さて最後に列の特定ですが、これはID列番号を用意してあります。
=条件一覧参照テーブル[@列番号]
関数を書き忘れましたが、上記のようになります。例のごとく＠は使えないので
=INDEX(条件一覧参照テーブル[列番号]、[@条件行番号]) 又は
=INDEX(条件一覧参照テーブル、[@条件行番号], 5)
この様になります。
つまり最終的に記述される内容は、
こうなります！！！！（見やすくなるように努力した）
ちなみに条件引数２の方はこちら！
条件引数１と全く同じやり方です。疲れたのでこの辺にしておきます…
最後に
今回はマスタデータを想定して作りましたが、例のごとく、テーブル外のセルをコピペしても書式は適応されません。エクセルに慣れていない方が触る場合ですと運用が大変になる可能性がある為、利用者のレベルに合わせて設計を変えていきましょう。
条件付き書式もですが、この辺何とかしてほしいですマイクロソフトさん（哀願）
そういえば1点注意があります。
今回リストで「説明文」を選択させ、「ID」を自動入力しました。
ですが本来は、「ID」を選択させ、「説明文」を自動入力したほうが安全です。
何故かというと説明文は変わりやすく、IDは変わりにくいという特性があります。
どちらにしても変更されると困るのですが、そのあたりは考慮しておいたほうがよさそうです。（尚、業務で説明文を選択させたエクセルを作ったことがありますが問題になったことはないです）
参考
INDEXとMATCHの内容について端折ったので参考URLを。
