入力規則の「リスト」。非常によく使われるエクセルの基本的な機能です。
本記事は、そのリスト機能の応用編を紹介します。
ひとつの項目を選択すると、次のリストの候補内容が変更されるという連動リストです。
連動リストとは
日本の地方名と、都道府県名を入力できるように、A2セルと B2セルに入力規則の「リスト」が設定されています。
A2セルに、地方名をリストから選択して入力。
たとえば「東北」を選択すると、B2セルのリストには、東北の県名が候補として挙がります。
A2セルに「近畿」を選択。
B2セルのリストには、近畿の都道府県名が候補に。
このように初めのリストの選択に応じて、後のリストの候補が切り替わるような設定にできます。
例では 2段階ですが、3段階、4段階に連動したリストもつくれます。
どんな機能を使う？
このリストを作るのに必要なのは、おもに次の 3つの機能です。
- 入力規則
- 名前の定義
- INDIRECT関数
まずはデータリストを用意
冒頭の 2段階連動リストのつくり方を説明していきます。
まずはリストの元になるデータを準備しましょう。
「リスト」という名前のシートにつぎの表を作成しました。
1行目に「地方名」、その下にそれぞれの地方の都道府県名がならぶ表です。
リストの候補範囲に名前定義
データ範囲に名前を定義します。
まず「地方名」という名前を定義。
1行目の地方名を選択して、「数式」タブ →「定義された名前」→「名前の定義」
選択範囲に「地方名」という名前を定義します。
つづいて、地方ごとにそれぞれの都道府県のデータ範囲を名前定義します。
表をすべて選択して、「数式」タブ →「定義された名前」→「選択範囲から作成」。
もしくはショートカットキーで Ctrl + Shift +F3
「上端行」のみにチェックを入れて OK
「北海道」「東北」「関東」…、それぞれの名前で、都道府県名の範囲が定義されました。
入力規則「リスト」を設定
リストの「元の値」に名前を指定
「リスト」の設定をします。
A2セルを選択して、「データ」タブ →「データツール」→「データの入力規則」
「入力値の種類」に「リスト」
「元の値」として、「地方名」という名前を指定します。
=地方名
リストの候補名として、「地方名」のセル範囲が設定されました。
INDIRECT関数の活用
つぎに B2セルです。
A2セルのリストで入力された値に応じて、B2セルリストの候補を変化させます。
そこで INDIRECT関数の登場です。
B2セルに「リスト」を設定。
「元の値」として A2セルを参照した INDIRECT関数をいれます。
=INDIRECT(A2)
INDIRECT関数が A2セルを参照することによって、定義された名前のセル参照が「元の値」に設定されます。
2段階の連動リストが設定されました。
「空白」について
リスト設定時、「空白を無視する」のチェックボックスが表示されます。
これは２ケ以上の空白があっても、それを無視するという意味でひとつの空白は表示されてしまいます。
リストに空白を表示したくないときは、空白を入れないように名前をひとつづつ定義しましょう。
3段階以上
3段階以上の連動リストも、基本的には同じ理屈です。
もうひとつリストを増やしました。
「東日本」「西日本」を選択するリストです。
選択肢が少ないので、「元の値」にはカンマで区切った選択肢を直接入力しました。
つぎに「東日本」「西日本」という名前を定義します。
「北海道」～「中部」の範囲を「東日本」と定義します。
同じように「近畿」～「沖縄」を「西日本」と定義。
B2セルの設定を変更します。
「=地方名」と入っていた「元の値」を変更。
=INDIRECT(A2)
A2セルの値に連動して、B2セルのリスト候補が変化するようになりました。
C2セルのリストは前回と同様です。
3段階連動リストが完成しました。
まとめ
前のリストの値を名前で定義して、
その値を次のリストの INDIRECT関数で参照する。
という理屈さえわかれば、何段階の連動化でもできます。