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

2012年6月25日

Excelで複数のシートからデータを集計するにはINDIRECT関数が便利です。
ややマイナーな関数ですが、覚えておくと役に立ちます。

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

たとえば、次のような事例があります。
1月〜6月の月ごとに給与一覧表がある例です。

月ごとのデータを集計します。

通常だと、次のような数式を入れるでしょう。
＝を入れて、マウスで選択して、という操作を6回繰り返せば、完成します。

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

先ほどの数式は、

＝’1月’!D19

という数式です。

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

セルA2に［1月］とありますので、

=A2＆"!D19″

と入力します。
（この［!］は、シート名とセル名の区切りに入れるものです）

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

単純に［1月］と［!D19］という文字をつなげただけだからです。
Excelは指示通りのことをしてくれているので、なんら悪くありません。

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

=INDIRECT(A2&"!D19″)

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

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

1月のセルD19（合計値）が表示されますので、

これをコピーすれば、簡単に複数のシートの値を合計することができます。

さらにコピーすると、7月以降のシートがないため、エラーになります。

この場合、7月のシートを追加すれば、自動的に連動できます。

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

そのため、事例では、合計欄を表の左側に置いています。
右側に置いていると、社員が増えた場合に合計欄の位置がずれるからです。
もちろん、項目の追加もしてはいけません。

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

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

集計したい数値の位置がシートごとに異なる場合はこういった方法もあります。

複数シートに関する記事です。

【編集後記】

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