Excelのリスト（プルダウン）の内容を変える方法～その１～
エクセルのプルダウンリストで臨機応変に内容を変えたい！ということを実現させるためのTipsです。私はエクセルのテーブル機能が好きなので、今回はテーブルを活用した状態でリストの内容を変える方法をご紹介いたします。
最終的なゴール
以下画像のようなコマンドと引数をペアにしたリストを作ります。
一応下記URLからワークショップ用のデータも用意してみました。完成品もあります。
Excelのリスト（プルダウン）の内容を変える方法～その１～ - Google ドライブ
得られるノウハウ
そもそもプルダウンリストってどうやるの？
エクセルでプルダウンリストをやる方法を軽く解説します。エクセル上部メニューにある「データ」＞「データの入力規則」でセルに対して設定することが出来ます。具体的な設定も追々解説いたします。
そもそもプルダウンリストってテーブルの内容を表示できるの？
リストに表示する内容は柔軟に設定が可能です。カンマ区切りで指定しても良いですし、セル範囲（1列か1行）を指定しても良いです。セル範囲に名前を付けていればそれも可能です。ただし、テーブル名は使用することが出来ません！／(^o^)＼ﾅﾝﾃｺｯﾀｲ
でも実は回避方法が2つあります。そのうちの1つをこちらで解説いたします。
その１ではプルダウンリストには１項目だけです
プルダウンリストに複数項目があり、一気に切り替えたい！という方はこちらです。
だたし、ちょっと複雑で難しいので、その１から段階を踏むを理解が進むかもしれないです。
目次
状況説明
コマンド一覧テーブル、実行コマンドテーブルの２つがあります。シートが別れていますが、特に意味はありません。
実行コマンドテーブルの[コマンド名]列にコマンドを入力していき、[引数1]列、[引数2]列にコマンドにあった引数を入力させる想定で作ります。
- コマンド入力箇所は、コマンド一覧がプルダウンリストで表示される
- 引数入力箇所は、引数内容説明がプルダウンリストで表示される
やり方
テーブルの名前をあらかじめ調整
コマンド一覧テーブルの名前を調整しておきましょう。最初にテーブルを作成した際はテキトウな名前が割り振られているので早いうちに名前を付けておきます。
入力規則からリストの設定
プルダウンリストの設定をセルに対して設定する訳ですが…っとここで例のトラップを踏んでみましょう。「リストにテーブル名が使えない」というやつです。実行コマンドテーブルのセルを選択して、上部メニューにある「データ＞データの入力規則」を選択します。（ウィンドウのサイズが小さいと表示が若干違いますのでご注意ください）
入力値の種類を「リスト」、元の値を「=コマンド一覧[コマンド名]」にしてOKします。するとエラーが出ます。コマンド一覧という名前が分かっていないようです。
リストでテーブル名を使う回避方法
リストでテーブル名が使えないことが分かりました。ではどうするかというと、名前の管理からテーブル名を使ったセル範囲を名前を定義してあげます。下記画像の場所から名前を新規作成します。
以下画像のようにして、テーブル名を使ってセル範囲を名前定義します。これにより、コマンド一覧テーブルにコマンドを追加/削除しても修正が必要なくなります。
こんな感じに範囲指定されています。 完璧ですね。
では実行コマンドテーブルに戻り、もう一度リストを設定してみましょう。今度は名前の管理で設定した名前を使います。
これで無事にプルダウンリストにコマンド一覧が表示されました。
最後に縦一列に書式をセルコピーします。これでコマンドを間違えてもエラーを表示できますし、一覧から探すことも出来るようになりました。
引数内容をリストで表示
さて、やっとこ本題に入ります。
引数内容の説明をプルダウンリストで表示するには、元の値に
”実行コマンドテーブル[コマンド名]” から ”コマンド一覧テーブル内” を検索する関数が必要
ということになります。
使用する関数
検索というと最初に考えられる関数がVLOOKUP等のLOOKUP系関数です。しかし、「リストの元の値に複数行列を用いたセル範囲を使うな」といったエラーが出てしまう（バグ？）ので、リストではVLOOKUPとは別の関数を使います。
利用する関数は、INDEX関数とMATCH関数です。この２つの関数を組み合わせて使います。VLOOKUPより柔軟なので、セル検索を覚えるならこの組み合わせ！！
つまるところ、以下のように入力すれば良いです。
=INDEX(CMDTABLE, MATCH(検索コマンド名, CMDLIST, 0), 列番号)
MATCH関数で、CMDLISTからコマンド名を検索し、行番号を返します。
INDEX関数で、CMDTABLEから行番号と列番号にあたる、セルの内容を返します。
CMDLISTは先程登場した「コマンド一覧[コマンド名]」です。
CMDTABLEは未登場の「コマンド一覧テーブル」そのものです。
コマンド一覧テーブルに名前を付ける
リストの元の値にはテーブル名は使えないので、先程と同じ手順でテーブルの全セル範囲に名前を付けます。
実行コマンドテーブルに戻り、セルを選択して入力規則を設定します。元の値は先程の内容を打ち込みますが、最後の数字はコマンド一覧テーブルの列番号なことに注意です。
引数１ =INDEX(CMDTABLE, MATCH($C5, CMDLIST, 0), 2)
引数２ =INDEX(CMDTABLE, MATCH($C5, CMDLIST, 0), 3)
引数３ =INDEX(CMDTABLE, MATCH($C5, CMDLIST, 0), 4)
引数４ =INDEX(CMDTABLE, MATCH($C5, CMDLIST, 0), 5)
…
最後の引数は引数番号毎にずらして入力していきます。あとは後続のセルにコピーしたら終わりです！！お疲れさまでした！
最後に
今回はコマンドを例に挙げておりますが、実際にこのような使い方をすると運用が大変なのでご利用の際はご留意ください。最後にセルコピーをしたと思いますが、これはセルに対しての”書式”設定ですので、テーブル外のセルをコピペしても書式は適応されません。条件付き書式もそうですが、この辺何とかしてほしいですマイクロソフトさん。
参考
コマンドの内容について端折ったので参考URLを。
Excel（エクセル）のLOOKUP関数とVLOOKUP関数とHLOOKUP関数の違い | Prau（プラウ）Office学習所
