Excelで複数のシートのデータをかんたんに集計できるINDIRECT関数

Excelで複数のシートからデータを集計するにはINDIRECT関数が便利です。
ややマイナーな関数ですが、覚えておくと役に立ちます。
スクリーンショット 2012 06 25 6 18 32

他のシートから数値を参照

たとえば、次のような事例があります。
1月〜6月の月ごとに給与一覧表がある例です。
スクリーンショット 2012 06 25 6 38 55

月ごとのデータを集計します。
スクリーンショット 2012 06 25 6 14 39

通常だと、次のような数式を入れるでしょう。
=を入れて、マウスで選択して、という操作を6回繰り返せば、完成します。
スクリーンショット 2012 06 25 6 33 08

この操作をもっと簡単にするのがINDIRECT関数です。

INDIRECT関数ならオートフィルで簡単に集計

先ほどの数式は、

=’1月’!D19

という数式です。

このうち、’1月’の部分だけが変化します。
[1月]はセルにすでに入力されていますので、これを利用するのです。


■スポンサードリンク


セルA2に[1月]とありますので、

=A2&”!D19″

と入力します。
(この[!]は、シート名とセル名の区切りに入れるものです)

すると、次のようになってしまいます。

スクリーンショット 2012 06 25 7 08 12
単純に[1月]と[!D19]という文字をつなげただけだからです。
Excelは指示通りのことをしてくれているので、なんら悪くありません。

文字列ではなく、「セルの参照として読み取ってほしい」と正しくExcelに伝える必要がありますので、INDIRECT関数を使い、

=INDIRECT(A2&”!D19″)

と入力します。(画面では小文字で入力しています)

INDIRECT関数は、「この文字列はセルの参照だよ」とExcelに伝えることができる関数です。
前述の「’1月’!D19」を「A2」と「!D19」で表現しています。

スクリーンショット 2012 06 25 6 17 46

1月のセルD19(合計値)が表示されますので、
スクリーンショット 2012 06 25 7 13 56

これをコピーすれば、簡単に複数のシートの値を合計することができます。
スクリーンショット 2012 06 25 6 18 06

さらにコピーすると、7月以降のシートがないため、エラーになります。
スクリーンショット 2012 06 25 6 18 14

この場合、7月のシートを追加すれば、自動的に連動できます。
スクリーンショット 2012 06 25 6 18 32

合計欄を各シートの同じセルに入れる

この方法で集計するときに重要なのは、各シートで合計すべき数値が同じセルにあることです。
「1月のシートではセルD19にあり、2月はセルD20にある」という状況では正しく集計できません。

そのため、事例では、合計欄を表の左側に置いています。
右側に置いていると、社員が増えた場合に合計欄の位置がずれるからです。
もちろん、項目の追加もしてはいけません。
スクリーンショット 2012 06 25 6 15 34

「一定のルールに従って規則正しくデータを作る」のはExcelの鉄則です。

Excelでは原則として、データを複数のシートに分散させない方が好ましいですが、どうしてもシートを分けなければいけない場合には、このINDIRECT関数が役立ちます。
(もちろん、マクロで集計することもできます)

集計したい数値の位置がシートごとに異なる場合はこういった方法もあります。
Excel複数シート集計ー各シート最下部の合計値をINDIRECT関数・COUNTA関数で瞬時に集計する方法ー[サンプルあり] | EX-IT

複数シートに関する記事です。
ピボットテーブルがベスト! Excelで複数のシートから集計する方法の比較(サンプルあり) | EX-IT

Excel複数シート集計ーマクロを使う方法【サンプルあり】ー | EX-IT


Excel複数シート集計 複数シートにあるすべてのデータをマクロで1枚のシートに【サンプルあり】 | EX-IT





【編集後記】

■スポンサードリンク

昨日は、幕張でトライアスロンでした。
やはり海はきれいとはいえませんでしたね(^_^;)
自己タイムは2分縮めましたが、スイムが2分半遅くなり、バイクが5分半速く、ランが1分遅いという内訳です。
ボランティアの方から応援がうれしい大会でした。