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

はてなブックマーク - Excelで複数のシートのデータをかんたんに集計できるINDIRECT関数
LINEで送る
Pocket

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分遅いという内訳です。
ボランティアの方から応援がうれしい大会でした。

■スポンサードリンク







■サービスメニュー
『雇われない・雇わない生き方』無料送付
■執筆・講演
■ブログリクエストフォーム 
■メルマガ「税理士進化論」 
■士業交流会登録受付 
直近100記事一覧


■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』




■ブログ「EX-IT」(管理人 井ノ上陽一)は、次の方法で購読できます。
・Facebook(Facebookにブログの更新情報が流れます。

・RSS(feedly。更新情報を受け取れます→詳細follow us in feedly
・Push

・Twitter(Twitterで更新情報を受け取れます) https://twitter.com/yoichiinoue 
・メールで更新情報を受け取れます。 詳細
The following two tabs change content below.
井ノ上 陽一
1972年生まれ。大阪生まれ宮崎育ち東京在住。 ひとりビジネスを、時間・お金の両面でサポート。 ・株式会社タイムコンサルティング代表取締役 ・MicrosoftMVP for Excel ・アイアンマン(スイム3.8km、バイク180.2km、ラン42.2km) ・著書に『ひとり社長の経理の基本』、『社長!「経理」がわからないと、あなたの会社潰れますよ』、『ひとり社長の経理の基本』,『そのまま使える経理&会計のためのExcel入門』ほか。 さらに詳細なプロフィールはこちら