こんにちは。
昔から使っているExcel版家計簿の改善をちまちまやってます。
勘定科目を「大科目」、「中科目」、「小科目」のように細分化した科目から選択する仕組み（ドロップダウンリスト）にした場合、ある大科目を選んだらそれに関連した中科目が絞り込まれて表示されると便利ですよね。
そんなドロップダウンリストを連動されるようなテクニックを備忘録していきます。
■ 動作環境
OS: Windows 8.1（64ビット）
Excel: 2013（32ビット）
■ 事前準備
簡単な家計簿として大科目、小科目の２段でデータを準備します。
例えば毎日の明細入力において、大科目で『光熱費』を選択したら、小科目には『上下水道』、『電気』、『ガス』（と、運用上は『（その他）』などのイレギュラー受け口になるような項目も追加しておきますが。笑）から選択したい訳です。
期待するのはこんな感じです。
■ 名前の定義
最終的に使う機能は「データの入力規則－リスト」です。
そこで各科目情報を指定するために『名前』を事前に定義しておくことで、設定しやすくなります。
□ 大科目の名前定義
まずは大科目の選択肢候補へ名前を定義します。
大科目の選択肢の候補となるB1:I1までを選択状態（図の①）にします。
その状態で、エクセルウィンドウの左上にある「名前ボックス（図の②）」に付与する名前「大科目」と入力し、ENTERキーを押すことで、名前が定義されます。
定義する範囲（個数）が固定されていて、隣り合うセルに対しての名前定義であれば、このやり方が一番簡単です。
□ 小科目の名前定義
冒頭の「事前準備」の章でも図でお見せしたとおり、大科目の各項目ごとにぶら下がっている項目が小科目です。大科目ごとにぶら下がる小科目の個数はマチマチであり、そのような凸凹を吸収しながら名前定義する必要があります。
まずは、『交通費』の小科目を定義してみます。
選択肢の候補となるA1:A10までを選択状態（図の①）にします。
その状態で「数式」タブ（図の②）の「定義された名前」→「選択範囲から作成」（図の③）を選択します。最後に、表示された「選択範囲から名前を作成」（図の④）ダイアログ上で「上端行」→「OK」することで完了です。
これで、『定期』、『Suicaチャージ』、『（その他）』の選択肢に対して、『交通費』という名前を定義しました。
この調子で、『住宅』～『その他』までの小科目を定義していきます。
定義した結果はご覧のとおりです。（CTRL＋F3で表示されるダイアログです。）
■ ドロップダウンリストの設定
では、名前定義した大科目、小科目を有効活用して、ドロップダウンリスト型のセルを作成していきます。
□ 大科目ドロップダウンリストの作成
大科目のドロップダウンリストとするセルを選択（図の①）します。
その状態で「データ」タブ（図の②）の「データツール」→「データの入力規則」（図の③）を選択します。最後に、表示された「データの入力規則」ダイアログ上で「入力値の種類」欄へ『リスト』（図の④）、「元の値」欄へ『=大科目』（図の⑤）と入力→「OK」することで完了です。
このようにリスト設定において「元の値」欄に、定義された名前をイコールで記載することで、次のような動きになります。 （その名前が定義された項目群がリストの選択肢となります）
□ 小科目ドロップダウンリストの作成
「小科目」欄では、大科目で選択した項目に従って、その科目にぶら下がる小項目がドロップダウンリストに表示されるようにします。
この解決には、今回はIndirect関数を使います。
> Indirect(参照文字列, [参照形式])
指定される文字列への参照を戻す
引数「参照文字列」は、任意のセルを参照する文字列を指定、セル参照も指定可能
引数「参照形式」は、（省略可能）参照形式をA1形式にするかR1C1形式にするかの切り替え、Trueまたは省略でA1形式で参照（Falseはその逆）
小科目を設定する場合は、「元の値」欄に、大科目のセルをIndirect関数にて指定する（図の⑤）だけです。
ここで冒頭の図を再掲しますね。
Indirect関数がどう作用してるかと言うと、Indirectで指定したE23は、この図では『光熱費』という値が入力されています。では、『光熱費』という参照文字列が指す物は何だったかというと、名前定義の『光熱費』（＝"上下水道"、"電気"、"ガス"、"（その他）"）であり、結果、リストの設定（元の値）においてこれら項目が選択肢として指定されている意味になるわけです。
■ まとめ
いかがでしたでしょうか？
ドロップダウンリストを連動させて入力条件を絞り込んでいく要望はよく見かけますので、マスターしておくと便利ですよね。
今度は、機会があれば、選択肢が増減した場合にでも動的にリスト選択肢が変更されるような動きを調査、実現したいと思います。
次回以降も、苦労した点を備忘録しようと思います。
Comments