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

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

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

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

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

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

 

 

 

 

 

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

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

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

 

 

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

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

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

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

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

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

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

これを各セルにコピーしているだけです。

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

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

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

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

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

今回の事例でも、同じようにセルをクリックしていくことを12回繰り返せば、セルの数値を集計できます。
（B列に入っている数式をC列に表示しています。）

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

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

こういったときに、=INDIRECT（A1&”!D38″)といれれば、意味が正しく伝わります。
「A1」はセルを示すので、「!D38」は文字なので””で囲みます。

 

=INDIRECT（A1&”!”&D38)とすると、エラーが出ます。
A1はシート「集計」のセルを指定していますが、D38はシート「集計」のセルD38ではなく、別のシートを示すため意味合いが変わるのです。

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

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

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

=counta(
と入力して（大文字でも小文字でも可）、シート「4月」のD列をマウスで選択すると、
=counta(4月! D:D)
という数式になり、結果は38となります。

 

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

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

「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セミナーの講師でした。
その後、書店で自分の本『社長！「経理」がわからないと、あなたの会社潰れますよ』があるコーナーに行くと、立ち読みしてくださっている方が。
ちょっと離れて「買ってくれるのかな。。」と見ていました（笑）。
数ページ読んだ後、そのままレジへ。
自分の本が実際に買っていただいているシーンを見ることができ感激です（^_^）