Excelで2階層のメニューを作る

Excel

家計簿を付け始めたのだが、Excelの使い方がいまいち分からない。リボンインターフェースにイライラさせられるのはどうしようもないけど。

支出を「食費」や「交通費」のようにカテゴリで分けられれば、どんな生活をしているか分かる。しかし、これらの項目をセルごとに入力すると以下の問題がおこる。そのため、列挙項目として一箇所で管理しておきたい。

  • 間違いのためにフィルタから漏れる
  • 変更する際に漏れが生じる


また、列挙項目は将来増えるかもしれないので、自動的に選択肢が増えるようにしたい。更に、食費なら「間食」や「飲み会｣のように階層化もしたい。

例として、シートの1行目にカテゴリを列挙し、2行目以降にサブカテゴリを列方向に列挙する場合について、2階層のメニューを作る方法を調べた。

    • カテゴリ表始点: $A$1
    • カテゴリ行: $1$1

[1] セルの値を選択式にするには入力規則を使う
セルに対して入力規則(データメニュー)を適用することが分かった。入力規則の設定において、値として許可するものに「リスト」を選択し、リストの範囲を指定してやればよい。こうすることで、リスト範囲内のセルの値が、セル上でプルダウンメニューから選べるようになる。

ただリスト範囲を直接指定してしまうと、変更の際に面倒なことになる。リストの項目を増やしたり移動したときに、無効な値の入ったリストを選択肢としてしまう。こうならないためにも、間接的な参照方法を活用すべきらしい。INDIRECT関数や、名前定義がその手段と分かった。

[2] カテゴリ数を可変にするためにはCOUNTAで数え、OFFSETで範囲を返す
リスト範囲を自動的に変更するためには、関数を使って範囲が設定されるようにしておく必要がある。
INDIRECT関数は、セルの中身を範囲とするC言語でいうポインタ的な役割。これは範囲を書いたセルか名前(あらかじめ名前定義する)が必要なので、今回は使わない。

ここでは、OFFSET関数とCOUNTA関数を用いる方法を採用する。以下のリンクに方法がそのまま載っている。

  • 参考: OFFSET関数
  • COUNTA関数: 選択範囲(列、行全体も可能)にある空でないセル数を数え上げる
  • OFFSET関数: 基準範囲とオフセット、(基準範囲の始点からの)幅と高さを指定することで、選択範囲を返す


これを使うと、以下のようにカテゴリリスト範囲を求められる。

  • カテゴリリスト範囲: OFFSET( カテゴリ表始点, 0, 0, 1, カテゴリ数)
  • カテゴリ数: COUNTA( カテゴリ行)
  • 最終的なカテゴリリスト範囲: OFFSET( $A$1, 0, 0, 1, COUNTA( $1:$1))


[3] リストを多階層化するには
これには方法がいくつかあるはずだが、MATCH関数を使う方法をとる。


これを使うと、以下のようにサブカテゴリリスト範囲を求められる。重複があるのが気になるけど。

  • カテゴリ始点: 実際のデータにおける、カテゴリ列のラベル
  • サブカテゴリ列基準: $A:$A ...こうするとOFFSET関数の戻り値が有限範囲ではなく列となる
  • カテゴリセル: OFFSET( カテゴリ始点, ROW() - 1)
  • サブカテゴリリスト範囲: OFFSET( カテゴリ表始点, 1, カテゴリ列オフセット, サブカテゴリ数, 1)
  • カテゴリ列オフセット: MATCH( カテゴリセル, カテゴリ行, 0) - 1
  • サブカテゴリ数: COUNTA( サブカテゴリ列) - 1
  • サブカテゴリ列: OFFSET( サブカテゴリ列基準, 0, カテゴリ列オフセット)

最終的なサブカテゴリリスト範囲:

=OFFSET( 分類!$A$1, 1, MATCH( OFFSET( $C$1, ROW() - 1, 0), 分類!$1:$1, 0) - 1,COUNTA( OFFSET( 分類!$A:$A, 0, MATCH( OFFSET( $C$1, ROW() - 1, 0), 分類!$1:$1, 0)-1))-1))

こんな感じでやったら、サブカテゴリも自動で増減するようにできた。