あるセルで選んだ文字に応じて別のセルで表示するリストの内容を変える

この記事は自分用の覚書です。

Excel2010 であるセルで選んだ文字に応じて別のセルで表示するリストの内容を変える。
リストの元になる文字列に半角スペース、&（アンパサンド）、ハイフンが含まれている場合。

■実現したい仕組みについて
sheet1 にリストの元を作る。
sheet2 のA2セルとB2セルでリストから文字列を選択できるようする。その際に、A2セルでの選択に応じてB2セルのリストの内容が変わるようにしたい。（それを行方向にコピーする。）
ただし、sheet1 のリストの元になる文字列には、半角スペース、&（アンパサンド）、ハイフンが含まれているので、セル範囲の名前付けをしたときにそれらが _ （アンダーバー）に置き換えられる。
sheet1 でリストの元にした文字列にはアンダーバーは付いていないので、そのままでは、sheet2 のA2セルでリストから文字列を選択してもB2セルのリストが連動しない。
( Windows 8 Pro 64ビット + Excel2010 )

■手順1: sheet1 の用意
・1行目（A1セルからD1セル）に入力する文字列
Training System & Delivery,Career Development,Non-Training Professionals,Govermment

コピー貼り付けでA1セルに全部の文字列が入ってしまったら、カンマで区切っているので次のようにすると複数のセルに分けられる。

1. Training System & Delivery から Govermment までをA1セルに貼り付けたら、「データ」タブの「区切り位置」を選択

2. 「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」にチェックを入れて「次へ」をクリック

3. 「区切り文字」で「カンマ」をチェックして、「次へ」をクリック
標準で「タブ」にチェックが入っているがそのままでもよい。

4. 「列のデータ形式」で「G/標準」にチェックが入っていることを確認して、「完了」をクリック
A1セルからD1セルに上記の文字列が入力される。

■手順2: A1セルからD1セルの範囲に名前を付ける。
1. 手順1で文字列を入力したら、A1セルからD1セルを選択

2. 数式バーの左横にある名前ボックスに「大分類」と入力
これで、A1セルからD1セルの範囲に「大分類」という名前を付けたことになる。

■手順3: リストで連動させる文字を入力
Training System & Delivery など各文字の下の行に、その大分類に属する小分類を入力していく。
たとえば、A2セルからA4セルには次の文字を入れる。
delivery methods
Working with SMEs
Innovations in design

■手順4: A2セルから下の行のセル範囲にも名前を付ける。
1. A1セルの Training System & Delivery から最終行のA4セルまでを選択

2. 「数式」タブの「範囲選択から作成」をクリック

3. 「以下に含まれる値から名前を作成」の画面が表示されるので「上端行」にチェックを入れて、「OK」をクリック
次はB1セルから最終行まで、その次はC1セルから最終行までというように同じ操作をD列まで行う。

※セル範囲の名前にはスペース、ハイフン、&（アンパサンド）が使えない。
上記の操作で「上端行」にチェックを入れると、A1セルからD1セルまでの文字列をセル範囲の名前として使うことになる。
名前にはスペース、ハイフン、&（アンパサンド）が使えないので自動的に _ （アンダーバー）に置換される。
それぞれの文字列は、セル範囲の名前としては次のようになる。
Career_Development
Govermment
Non_Training_Professionals
Training_System___Delivery

「数式」タブの「名前の管理」を開くと名前を確認できる。

■手順5: sheet2 でリストの設定をする。
sheet2 のA2セルを選択して操作を始める。

1. 「データ」タブの「データの入力規則」を選択

2. 「データ入力規則」の画面になるので、「入力値の種類」で「リスト」を選択

3. 「元の値」の欄を一度クリックして、F3キーを押すと「名前の貼り付け」の画面が出る。「大分類」を選択して「OK」をクリック

4. 「データ入力規則」の画面でも「OK」をクリック
これで、sheet2 のA2セルの右端に小さい▼が表示されてリスト選択ができるようになる。
リストの元になるのは、sheet1のA1セルからD1セル。

■手順6: sheet2 で連動する側のリストを設定する。
sheet2 のB2セルを選択して操作を始める。

1. 「データ」タブの「データの入力規則」を選択

2. 「データ入力規則」の画面になるので、「入力値の種類」で「リスト」を選択

3. 「元の値」の欄を一度クリックして、次の式を入力して「OK」をクリック
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","_"),"&","_"),"-","_"))

4. A2セルでまだ何も選択していなくて空欄の場合、「元の値はエラーと判断されます。続けますか」が表示される。かまわないので「はい」をクリック

これでA2セルで選んだ値に連動してB2セルでリスト表示される値が変わる。
A2セルとB2セルを選んで必要なだけ行方向へコピーする。

■=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","_"),"&","_"),"-","_")) について
この式の意味は、
SUBSTITUTE関数で A2セルの文字列に含まれる
・半角スペース
・&（アンパサンド）
・-（ハイフン）
をすべて
_（アンダーバー）に置き換える。さらに、置き換え後の文字列をINDIRECT関数でセル範囲に変換する、ということ。

あとで行方向へコピーするので、A2セルについては $ マークを使って絶対参照にしていない。

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","_"),"&","_"),"-","_") の部分で、sheet2 のA2セルで選んだ文字列が、上記の手順4で作ったセル範囲の名前と同じ文字列になる。
たとえば、A2セルで
Training System & Delivery
を選ぶと、式のなかで
Training_System___Delivery
に置き換えられる。

INDIRECT関数でそれをリスト表示の元になるセル範囲に変換したいので、

=INDIRECT(Training_System___Delivery)

という式を作ったことになる。

セル範囲「Training_System___Delivery」では、sheet1 のA2セルからA4セルまでを参照範囲にしているので、INDIRECT関数は sheet1 のA2からA4までのセル番地を返す。
リストの「元の値」の欄に
=Sheet1!A2:A4
のように設定したのと同じ意味になる。
結果として、sheet2 のA2セルで選んだ Training System & Delivery に応じたリスト表示が sheet2 のB2セルでされることになる。

sheet2 のA2セルで別の文字列を選べば、INDIRECT関数の式をそれぞれ
=INDIRECT(Career_Development)
=INDIRECT(Non_Training_Professionals)
=INDIRECT(Govermment)
に変えたことになる。それは、sheet2 のB2セルに設定したリストの「元の値」になるセル範囲を変えるのと同じ意味。

=INDIRECT(Career_Development) は、Sheet1 のB2セルから最終セルまでを、
=INDIRECT(Non_Training_Professionals) は、Sheet1 のC2セルから最終セルまでを、
=INDIRECT(Govermment) は、Sheet1 のD2セルから最終セルまでを、
「元の値」に指定したことになる。

つまり、sheet2 のA2セルで選んだ文字列に応じてB2セルでリスト表示される選択肢が変わる。

■留意点
手順4のセル範囲を作るとき、範囲に指定するセル内の文字列に半角スペース、&（アンパサンド）、ハイフンが含まれていなければ、セル範囲の名前はセル内の文字と同じになる。
「数式」タブの「名前の管理」を開いて名前を確認して、セル内の文字と同じなら、手順6の式でSUBSTITUTE関数は必要ない。
「元の値」に入れる式は、
=INDIRECT(A2)
でよい。