エクセルシートを追加しても自動的に計算串刺し演算3D参照

串刺し演算で集計対象シートを追加

串刺し演算で集計シートを追加 Excel2016

複数の支店などの売上等を串刺し演算をしているブックで、集計対象となる支店が増えたときはどうすればよいのかを解説します。

Excel2016,2013中級技38 複数シートのデータ集計

串刺し演算でシートを追加する

串刺し演算(3-D 参照)を使うと、複数のシートにあるデータの合計を、1 つのシートにまとめることができるので便利なことがわかりました。
今回は、集計の対象となるシートを追加した場合について考えましょう。 例のように支店別の集計表が複数あり、「総売上」シートというまとめシートに総合計を出力している場合に、支店が増えた時のケースです。
串刺し演算は、Excel 2002,2003,2007,2010,2013 共通です。
(サンプルファイルは、こちらから 中級技38回サンプルデータ

合計の対象シートを追加したら 3D集計串刺し演算

串刺し演算しているブックに、集計対象のシートを追加した時に、数式を修正しなければいけないのがめんどうというケースです。
すでに式があるとはいえ、それを1つずつ修正するのは、非常に手間ですし、ミスが起こらないとも限りません。

まとめシートの式は、このような式になっていました。
=SUM(東京:大阪!B4) つまり、 東京から大阪シートのB4を合計する。

対象シートを追加するには 

式を変更せずに、串刺し演算集計対象のシートを追加する場合は、東京から大阪シートの間に追加すれば良いのです。 間にあるシートは、串刺し演算ですべて集計対象になります。
でも、追加の新シートが前からあるシートの前に来るのは都合が悪いこともあります。 例では、雛形のシートをSTARTという名前にして、空のシートをまとめシートの前に作りENDとしました。 シートの名前は、式を作るための仮の名前ですから、変更してください。
そして「START」シートと、「END」シートの間に新規で福岡支店のシートを追加しました。
各シートはすべて同じ形式で作成されていますが、東京と大阪の間ではないので式は修正する必要があります。
まとめシートである、「総売上結果」シートの数式を修正します。
元の式は、=SUM(東京:大阪!B4)です。
東京というシート名をSTARTに置換。
続けて大阪というシート名をENDに置換します。数式の置換ですが、普通に置換して大丈夫です。
数式は、=SUM(START:END!B4)のようにすべて変更されました。
置換に対して不安がある場合は、きちんとオプションを設定して置換してください。(この方がより安心です。) 数式は、=SUM(START:END!B4)のようにすべて変更されました。
追加したシートも自動的に集計の対象になります。

集計のための空のダミーシートは非表示に

空のダミーシートが目障りなら、非表示にしてください。
しかし新規シートを追加する際には、必ず式に設定したシートとシートの間に来るようにしてください。