シートを跨いだ集計

状況：

タイトルが解りづらいかもしれないが、要はこういう事。
月ごとの集計をする、１月、２月、３月といった名前のシートがあり、そして年間の集計をする集計という名前のシートで、各月シートの集計から年間集計を求めるような場合だ。

こんな感じ。
すなわち、集計シートのB4:G4に各月の集計結果を表示したい場合だ。

単純に、'４月'!G4、'５月'!G4と打ち込んでいってもいいが、かなり面倒な作業だ。
この例では大したことはないが、毎日の月集計ともなると・・・気が遠くなる・・・
ここはなんとか数式のコピーでできないか？

一応、直に打ち込んだ'４月'!G4をこのままコピーするとどうなるかを確認しておこう。
'４月'!G4をC4～G4にコピーすると、
'４月'!H4～'４月'!L4
となってしまうな。これじゃダメ。

ここで問題は、
１．'４月'!の部分はシートを判別するためのところなので、ここはコピーした時に変わってほしい。
２．G4の部分は各月のシートの同じ列を参照するので、行は下にコピーした時に変わってほしいが、列は変わってはダメ。
この２点だ。

つまり、
　'４月'!　→　列可変、行固定
　G4　→　列固定、行可変
になるような数式を考える。

考え方：

まず、式全体について、
各月のG4の値を参照してきたいので、
　INDIRECT(各月の集計セルを示す参照の文字列)
というふうに、INDIRECT関数を使うといいだろう。

そして、”各月の集計セルを指し示す参照の文字列”には
　CONCATENATE(各月シートを示す可変文字列,"!G",集計セルの行を示す可変文字列)
とCONCATENATE関数を使う。

そして、
各月シートを示す可変文字列
集計セルの行を示す可変文字列
この２つを数式で表すことを考える。

実際の式：

１．集計セルの行を示す可変文字列
　　　ROW('４月'!$G4)
　これじゃ４月固定にならないのか？
　まあそうだが、ここは各月のシートは同じ構造をしているという前提を付けると、これでいい。
　しかしここで問題が１つ。
　これだと月シートのG4より前に列が追加されたときに対応できない。そこで列も可変にするために、ROW,COLUMN,ADDRESSと３つの関数を組み合わせて、

　　ADDRESS(ROW('４月'!$G4),COLUMN('４月'!$G4),1,1)

とする。

ちなみに、上の式単独の戻り値は$G$4という文字列

またこれに伴い、CONCATENATE関数の引数の１つ"!G"を"!"に変更する。

２．各月シートを示す可変文字列
　表のヘッダー行に注目する。

これはシート名と同じだ。（まあ、普通はそう設計するだろうが。）
　と言うわけで、これを利用する。
　すなわち、B3～G3を参照すると、それぞれ４月～９月という文字列が返ってくるということだ。
　従って簡単に、
　　B$3

　これらを合わせて、

　　INDIRECT(CONCATENATE(B$3,"!",

　　　　ADDRESS(ROW('４月'!$G4),COLUMN('４月'!$G4),1,1)))

この式をB4に入力して、それを残りのセルにコピーすればＯＫ。

結果：

各月シートに行や列を追加してテストしてみたが、これもＯＫ。
これでだいぶ楽になったな。

以上、完了。