今回は、Excelの3D参照を使い、月別シートの同じセルをまとめて集計する方法を紹介します。
3D参照は同じ形のシート集計に向いている
Excelで月別、店舗別、部署別のシートを分けて管理している場合、各シートの同じ位置にある数値を合計したいことがあります。たとえば、1月から12月まで同じ形式の売上表を作り、集計シートで年間合計を出すような場面です。
このときに使えるのが3D参照です。3D参照は、複数シートにまたがる同じセルや同じ範囲をまとめて参照する方法です。シートの構成がそろっていれば、各月の同じ項目をまとめて集計しやすくなります。
代表的な式は次のような形です。
- =SUM(‘1月:12月’!B5)
この式では、1月シートから12月シートまでのB5セルを合計します。月別シートの同じ位置に同じ項目がある場合、集計式を短く保てます。
3D参照を使う前の準備
3D参照を使うには、対象シートの形をそろえておくことが大切です。各シートで項目の位置がずれていると、同じセルを参照しても意味の違う数値を集計してしまいます。
準備で確認したい点は次の通りです。
- 各月シートの表の位置が同じ
- 集計したい項目が同じセルにある
- 不要なシートが集計範囲に挟まっていない
- シート名が分かりやすい順番で並んでいる
- 途中にメモ用シートを置いていない
3D参照は、指定した開始シートから終了シートまでをまとめて参照します。その間に別のシートが入っていると、そのシートも対象になります。集計対象のシートは連続して並べ、メモや設定用のシートは範囲の外に置くと安全です。
月別シートを合計する手順
月別シートの同じセルを集計する基本手順は次の通りです。ここでは、1月から12月までのシートがあり、各シートのB5セルに売上合計が入っている想定です。
- 集計結果を表示するシートを作る
- 合計を表示したいセルを選ぶ
- =SUM( と入力する
- 最初の月のシートを選ぶ
- Shiftキーを押しながら最後の月のシートを選ぶ
- 集計したいセルを選ぶ
- 数式を閉じてEnterキーで確定する
式を直接入力しても構いませんが、最初はシートを選びながら作ると参照範囲を確認しやすくなります。式が完成したら、開始シートと終了シートの名前、参照セルが意図通りか確認します。
集計表にコピーして使う
3D参照の式は、集計表の中でコピーして使えます。たとえば、各月シートのB5からB10までに項目別の数値が入っている場合、集計シートでも同じ並びにして式を下へコピーできます。
コピーすると参照セルも相対的に変わります。B5の式を下へコピーすればB6、B7のように変わります。各月シートの表の位置がそろっていれば、項目ごとの年間合計を作りやすくなります。
ただし、シート名の範囲はコピーしても同じです。1月から12月までの合計を出したい場合は問題ありませんが、上半期だけ、下半期だけの集計を作る場合は、開始シートと終了シートを分けた式を用意します。
使うときの注意点
3D参照は便利ですが、シート構成に影響されます。あとからシートを追加したり移動したりすると、集計範囲が変わることがあります。特に、開始シートと終了シートの間に新しいシートを入れると、そのシートも集計対象になります。
運用時のポイントは次の通りです。
- 集計対象のシートを連続して並べる
- 対象外シートを集計範囲の間に入れない
- 月別シートのレイアウトをそろえる
- 行や列を追加するときは全シートで同じ位置に入れる
- 集計式の参照範囲を定期的に確認する
テンプレートとして使う場合は、月別シートを作る前に1枚の原本シートを整え、それをコピーして各月のシートを作ると位置ずれを防ぎやすくなります。表の途中に行を追加する場合も、全月で同じように追加します。
集計範囲を管理しやすくする工夫
3D参照を長く使うブックでは、集計範囲の始まりと終わりを分かりやすくしておくと管理しやすくなります。月別シートが増えるたびに式を直す運用だと、対象に入れ忘れることがあります。
実務では、集計対象の先頭と末尾を示すための空のシートを置く方法があります。たとえば、「集計開始」と「集計終了」のようなシートを用意し、その間に1月から12月までのシートを並べます。式では開始用シートから終了用シートまでを参照します。新しい月のシートをその間に入れれば、集計範囲へ含めやすくなります。
ただし、この方法でも、対象外のシートを間に入れると集計に含まれます。シートの並びを変える人が複数いる場合は、ブック内に短い説明を置いておくと安全です。
- 集計対象シートを連続して置く
- 開始と終了の目印を作る
- 対象外シートは範囲の外に置く
- 新しい月のシートは同じテンプレートから作る
- 集計式の対象範囲を確認してから共有する
3D参照は式が短くなる一方で、シートの並びに意味を持たせる機能です。並びを変えたときは、集計結果も確認する習慣を付けます。
集計結果が合わないとき
合計が想定と違う場合は、まず対象シートの範囲を確認します。次に、各月シートの同じセルに同じ項目が入っているかを見ます。途中の月だけ行が追加されていると、参照しているセルの意味が変わることがあります。数式だけでなく、シートの並びと表の位置を合わせて確認することが大切です。
テンプレート運用のポイント
毎月使う集計ブックでは、月別シートを直接作り込むより、原本シートをコピーして使うほうが位置ずれを防ぎやすくなります。原本には入力欄、集計欄、注意書きをそろえておき、月ごとの変更は入力値だけにします。表の構造を変えるときは、すべての月へ同じ変更を入れてから集計式を確認します。
集計シートには、どのシートを対象にしているかを近くに書いておくと確認しやすくなります。式だけを見て判断するより、対象範囲の説明があるほうが、後から別の人が更新するときにも迷いにくくなります。
まとめ
Excelの3D参照は、同じ形式で並んだ複数シートの同じセルをまとめて集計する方法です。月別シート、店舗別シート、部署別シートのように、構成がそろったシートを集計するときに役立ちます。
使う前には、対象シートの並びと表の位置を確認します。開始シートから終了シートまでの間にあるシートが集計対象になるため、不要なシートを挟まないことが大切です。シート構成を整えてから使えば、月別データの集計表を作りやすくなります。