Excel 串刺し計算(3D集計)―複数シートを効率的に集計する―

今回の記事では、Excelで多数のシートがあっても驚くほど簡単に集計を完成させられる「串刺し計算(3D集計)」というテクニックを紹介する。

やたら沢山のExcelシートを不本意に扱わされ、集計に苦労している方にピッタリだと思う。

今まで10分掛かっていた作業が1分で終わるようにもできるだろう。

多数のシートを使った運用と集計

Excelにちょっと慣れてくると、とにかく目的ごととか日付ごとでシートを増やしたがるようになる。

実際のところ、シートはあまり増やさないようにすべきで、日付が分かれるならシート分けするのでなく、1つのシートにデータベース(表)としてまとめるべきだ。

でも、やっぱりシート分けした方が良い場合もあるし、人から渡されたファイルなら仕方ない場合もあるだろう。

しかし、多数のシートに分ける運用をするのならば、これから紹介する串刺し計算(3D集計)というテクニックは避けて通れない。

これを知らずに長々と集計の計算式を入れているような人が、貴方の会社にもいるかも知れないが、そんなことでは給料泥棒だ。
貴方はこの串刺し計算をマスターして、そんな状態からは抜け出そう。

設例―複数シートを集計する―

まず、設問を書く。

これについて、やり方を知らない人だと
=田中!B1+山田!B1+鈴木!B1+佐藤!B1+斉藤!B1
なんて数式をマジで書いたりする。

こんなことやってたら、前田さんとか人がもっと増えた場合にそのつど数式追加の手間が発生することになる。

でも実際、それをマジでやってる人を見てきた。

これの解法が串刺し計算(3D集計)であり、やり方は色々なサイトで紹介されているけど、ここでは一足飛びで最も効率的なやり方を書きたい。
そのやり方も、有名といえば有名とは思う。

解法

まず各人のシートの右端と左端に、何もない空白のシートを挿入しよう。
そしてそれらシートの名前は「START」「END」とでもすれば良い。

「START」「END」シートの間に、各人のシートを挟むのだ。合計用シートは決して挟まないこと。

そして「START」「END」シートは、内容が全くの空白であることが望ましく、少なくとも計算に影響するような数字データとかは一切入れないこと。

次に、合計用シートの合計セルに
=SUM(
まで入力する。
そのまま、Enterキーとかを押さずに次の手順に進む。

「START」シートをクリック→Shiftキーを押しながら「END」シートをクリック とする。
これにより、「=SUM(‘START:END’!」とまで数式が自動的に入る。

Shiftキーを押しながらやるのがミソで、これにより、「START」～「END」の間の複数シートを同時に選択する指定をしているわけだ。

そして、各シートのB1セルを合計したいなら、いま入力した「=SUM(‘START:END’!」文の後に追加で「B1)」と加えて式を完成させる。

この「B1)」文字は、手打ちでも良いし、いま見えているシートのB1セルをクリックすることでも入力できる。

これで合計シートの式「=SUM(START:END!B1)」ができあがるので、あとはこの式をコピペすることで合計シートが完成する。

串刺し計算(3D集計)という言葉そのものを知らなくても、この式
=SUM(START:END!B1)

を見れば、だいたい何をやっているかは想像がつくはずだ。

串刺し計算(3D集計)のススメ

複数シート集計にはまず串刺し計算(3D集計)だ

複数シートをまとめて効率的に集計するとなると、この串刺し計算(3D集計)が最適解だと思って掛かってまず良い。他には

とかいうのもあることはあるけど、どれも難しめだし、知識があっても手間が掛かるものばかりだ。

串刺し計算(3D集計)で解決できないようなら、複数シートを集計する資料の作り方が根本的におかしいことを疑って良いと思う。

串刺し計算(3D集計)はシンプルだ

串刺し計算(3D集計)のメリットを列挙しよう。

=SUM(START:END!B1)
という式を見て分かるように、あくまで「START」「END」シートの間にあるシートを全て集計するというだけの機能に絞り込まれているのがポイントだ。

余談：「START」「END」シートの意味

今回の説明では田中さんのシートが左端に、斉藤さんのシートが右端にあって、これらを集計するというだけのことなら
=SUM(田中:斉藤!B1)
という式でも良い。

というか多分、多くのテキストとかでは、そちらの方法がまず最初に紹介されているだろう。

でもこれだと、シートの順番が入れ替わって「田中」「斉藤」シートが端の位置でなくなったり、「田中」「斉藤」シートが削除されてしまったりした場合に、計算式修正の手間が発生する。

その点、空白の「START」「END」シートを設置してそれらは決して動かさないことにしておけば、メンテナンスの手間が大いに省けるというわけだ。

「START」「END」シートの間であれば、シートの順序が入れ替わったり削除があったりしても、問題なくなる。

デメリット・注意点

この串刺し計算だが、シンプルゆえデリケートで、注意点もある。

「START」「END」シートの間の、全て同じセルを集計するのでなければならない。

これが最大の注意点。串刺し計算(3D集計)は、あくまで「対象となる全てのシートの同じセルを集計する」機能だということ。

今回の例では「START」「END」シートの間にある全シートのB1セルを集計したわけだが、「田中さんのシートだけはC1セルを加算して、他の人のシートについてはB1セルを加算する」というやり方は残念ながらできない。

あくまでB1ならB1という同一のセルを集計するのに限られるということだ。これはどうしようもない。

運用していって、行の挿入とかをして、うっかりこれら集計セルがズレるということがないようにしよう。

Excelの関数が何でも串刺し計算に使えるわけではない。

何でも使えるわけではないというか、使える関数は非常に限定されていて、SUM関数による単純な足し算しかできないくらいに思って良い。

SUMIF関数なども使えそうに思えるが、実際は使えずエラーになってしまう。

たとえばCOUNTA関数による「=COUNTA(START:END!B1)」などは正しく計算できるけど、利用できる場面はほぼないだろう。

取り敢えず、SUM関数による計算だけ覚えておくこと。