エクセルのちょっといい話（１８）

エクセルで集計作業を行う際に、複数のシート間の値を串刺し的に集計したい場合があります。
例えば、次図のような日々の集計表内の特定データを、全日付に渡って別シートに一覧化したい時、一体どうすればいいのでしょうか。



もちろん、マクロを使えば簡単ではありますが、それでは芸がありません。そこで、エクセル関数のみで対処できないか、考えてみます。

次図は、集計結果の一例です。実はこのシートでは、直近行をその下の行にコピーして日付を変えるだけで、各データがその日付のシートのデータに変更されます。



以下に、その仕組みについて解説いたします。

シートを横断して集計するためには、通常は次式のように入力します。

　='シート名'!セル番地　・・・①

例えば、5月26日のシートのI8セルの値を表示するには、

　='5月26日'!I8

とすれば良いわけです。

しかしこの方法だと、各数式毎にシート名を入力してやる必要があり大変です。そこで、例えばA列にシート名と同じ日付を入力しておいて、それをシート名の代わりに参照してやることを考えます。
シート名はテキストですから、当然それに対応した形式に変換してやる必要があります。5月26日に対応するA101セルを使用して、次式のようにしてみます。

　=TEXT(A101,"m月d日")　・・・②

結果は期待通り「5月26日」と表示されます。これを①式に代入するわけですが、このままでは上手くいきません。
①式は参照式なのですが、②式はあくまで文字列です。そこで、文字列を参照式に変換してやる必要があります。それにはINDIRECT関数を用います。

　=INDIRECT(TEXT(A101,"m月d日")&"!I8")

ここで、参照先のセル番地を一旦文字列に変換して、それを結合した上で全体を参照式に変換するのがミソです。
これで目的シートの特定セル値を表示することが出来るようになりました。

この数式を、集計用シートに日付毎に並べておけば、あとはシートを追加する毎に対応行をコピーしていくだけで、集計表の更新が出来るようになります。
シート間のデータを自由に横断することにより、2次元から3次元へと分析の幅を広げることが可能になるのです。

タグ：INDIRECT関数 ワークシート集計 エクセル