Excel各シート最下部の合計値をINDIRECT関数・COUNTA関数で瞬時に集計

「Excel 複数シート集計」「別シートへ合計」「「シート まとめる」といった検索キーワードで当ブログへ多くのアクセスをいただいております。
今日はちょっと違った視点で記事にしてみました。
合計値の位置がシートによって異なる場合です。
INDIRECT COUNTA

複数シートをまとめるならマクロ

「複数のシートをまとめる」「複数のシートから合計する」ときに、私自身はどうしているか?というとマクロを使っています。
マクロで、10数行のマクロをかけば、シートが10枚だろうと、100枚だろうと、1万枚だろうと集計してくれます。
「すべてのシートのデータをまとめる」という指示ができるからです。

マクロがベストとはいえ、なかなか敷居が高いのも事実でしょう。
そこで書いたのが、INDIRECT(インダイレクト)という関数を使った次の記事です。

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

合計値の位置が異なる場合

INDIRECT関数を使えば、複数シートから数値を集計できます。
ただし、普通に使う場合は、その数値が各シートで同じ位置になければいけません。

「4月」から「3月」のシート(年度分)があり、シート「4月」はセルD38に合計値があり、シート「5月」はセルD21に合計値がある場合は、一工夫必要となります。

各シートの最下部のデータ(合計値)を集める方法

サンプルでは、4月から翌年3月までの12枚のシートにデータ(交通費)が入っています。

「4月」は38行目に合計値があり、

スクリーンショット 2013 09 12 9 28 41

「5月」は32行目に合計値があります。

スクリーンショット 2013 09 12 9 29 00

この場合、前回のINDIRECT関数の使い方だと集計できません。
(合計値の位置を一定にすれば集計できます。たとえば100行目に固定するのです。)

今回はこのケースを考えてみましょう。
結論から書くと、セルB1には次のような数式が入っています。

=INDIRECT(A1&”!D”&COUNTA(INDIRECT(A1&”!D:D”)))

これを各セルにコピーしているだけです。
スクリーンショット 2013 09 12 10 03 22

サンプルをおいておきます。

EX-ITサンプル 複数シート集計 INDIRECT

以降は、その解説です。
ややこしいのですが、できれば理解して使っていただいた方がいいので、ぜひ読んでください(^_^;)

INDIRECT関数は、文字列をセルの参照にする関数です。
Excelに対して「これは文字ではなく、セルを指し示している」ことを伝えます。

たとえば、シート「集計」で、あるセルに「=」を入力してから、別シートである「4月」のセルA1をクリックすると、「=’4月’!A1」という数式ができます。
これは、「シート「4月」のA1の値を入れてください」という指示です。
Excelはその指示どおりに動いてくれます。
スクリーンショット 2013 09 12 9 34 12

今回の事例でも、同じようにセルをクリックしていくことを12回繰り返せば、セルの数値を集計できます。
(B列に入っている数式をC列に表示しています。)
スクリーンショット 2013 09 12 9 37 31

ただ、12回繰り返すのはめんどくさく時間もかかります。

そこで、A列に入っている月数(「4月」、「5月」・・・」を使えないか考えるわけです。
=’4月’!D38
だから、
=A1!D38
と変えればできそうな感じがします。
最初のA1はシート「集計」のA1(4月)。次のD38はシート「4月」のD38という意味ですが、Excelではエラーが出てしまうです。

スクリーンショット 2013 09 12 9 42 57

こういったときに、=INDIRECT(A1&”!D38″)といれれば、意味が正しく伝わります。
「A1」はセルを示すので、「!D38」は文字なので””で囲みます。
スクリーンショット 2013 09 12 9 44 30

=INDIRECT(A1&”!”&D38)とすると、エラーが出ます。
A1はシート「集計」のセルを指定していますが、D38はシート「集計」のセルD38ではなく、別のシートを示すため意味合いが変わるのです。
スクリーンショット 2013 09 12 9 48 27

この方法が前回の記事の内容ですが、これだと12回、数式を変えなければいけません。
各シートで合計値の位置が違うからです。

そこで合計値の位置も関数で自動的に出してみましょう。

シート「4月」は、セルD38に合計値があります。
この38という数字をどうやって表現すればいいかを考えてみるのです。
38は、データ数と一致します。
ということは、各シートのデータ数をカウントすれば、数式に使えそうです。
カウントする関数は、COUNT(数値を数える)とCOUNTA(空白でないセルの値、つまり数値、文字列などすべてを数える)があります。
今回は、COUNTAを使います。

=counta(
と入力して(大文字でも小文字でも可)、シート「4月」のD列をマウスで選択すると、
=counta(4月! D:D)
という数式になり、結果は38となります。
スクリーンショット 2013 09 12 9 56 01

「4月」の部分を自動化するために、ここでも、INDIRECT関数を使うのです。
=COUNTA(INDIRECT(A1&”!D:D”))

とすれば、同様に38が表示されます。
コピーすると各シートの最大値を瞬時にカウントできるのです。

スクリーンショット 2013 09 12 9 58 15

「4月」で考えると、最終的には、=4月!D38と表示すればいいので、INDIRECT関数を使って、

=INDIRECT(“4月”&!D”&38)

と分解し、
38の部分に=COUNTA(INDIRECT(A1&”!D:D”))をいれて、

=INDIRECT(“4月”&!D”&COUNTA(INDIRECT(A1&”!D:D”))

とし、”4月”をセルの参照に変えれば、

=INDIRECT(A1&!D”&COUNTA(INDIRECT(A1&”!D:D”))

となります。

この考え方はマクロを書くときにも役立つので、ぜひ自分で数式を入れてみてください。
マクロを覚えたほうが速いかもしれません。
マクロバージョンも別途記事にします。

興味がある方は、マクロ入門セミナーもご利用ください。
次回は9/26(木)です。

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

■同じ事例をマクロやる場合についてかいた記事です。
Excel複数シート集計ーマクロを使う方法【サンプルあり】ー | EX-IT





【編集後記】

■スポンサードリンク

昨日の午前中は経理担当者向けExcelセミナーの講師でした。
その後、書店で自分の本『社長!「経理」がわからないと、あなたの会社潰れますよ』があるコーナーに行くと、立ち読みしてくださっている方が。
ちょっと離れて「買ってくれるのかな。。」と見ていました(笑)。
数ページ読んだ後、そのままレジへ。
自分の本が実際に買っていただいているシーンを見ることができ感激です(^_^)