Excelで SUMIFS関数を別シートにまたがって使いたい ときは、まず「1枚だけを参照するのか」「シート名を切り替えたいのか」「複数シートを横断したいのか」を分けて考えると整理しやすくなります。
結論を先に書くと、考え方は次の通りです。
- 1つの別シートだけを参照する: シート名を付けた SUMIFS をそのまま書く
- シート名をセルで切り替えたい: INDIRECT を使う
- 複数シートをまとめて集計したい: INDIRECT + SUMIFS を縦に並べるか、集計用の表構造を見直す
この記事では、別シート参照の基本形、INDIRECT を使った切替、複数シート集計の考え方、重くなりやすい原因まで、実務で迷いやすい点を順番に整理します。
まず押さえたいポイント
- SUMIFS関数だけでは、複数シートを一発で横断集計することはできません。
- 参照先が固定なら、INDIRECT を使わずにシート名を直接書く方が軽くて安全です。
- シート名をセルで切り替えたい時だけ、INDIRECT を使う意味があります。
- 定期的に多シート集計するなら、元データを1つの表へまとめた方が管理しやすいです。
1つの別シートをそのまま参照する基本形
たとえば、Sheet2 の A列に商品名、B列に売上があり、商品名が B4 と一致する売上を合計したいなら、基本形は次のようになります。
=SUMIFS(Sheet2!B:B,Sheet2!A:A,B4)シート名が固定でよいなら、この形が最もシンプルです。無理に INDIRECT を使う必要はありません。
シート名をセルで切り替えたい場合
集計シートの F4 にシート名を入れて切り替えたい場合は、INDIRECT 関数を組み合わせます。
=SUMIFS(INDIRECT("'"&F4&"'!B:B"),INDIRECT("'"&F4&"'!A:A"),F5)この方法なら、F4 のシート名を変えるだけで参照先を切り替えられます。ただし、INDIRECT は揮発性関数なので、多用するとブックが重くなりやすい点には注意が必要です。
複数シートを合計したい場合の考え方
SUMIFS関数だけで複数シートを一度に横断することはできません。そのため、次のどちらかで対応します。
- 各シートごとに SUMIFS を書いて、最後に合計する
- シート名一覧を用意して、INDIRECT + SUMIFS を縦に並べる
たとえば A12:A14 にシート名一覧があるなら、B12 に次の式を入れて下へコピーし、最後に SUM します。
=SUMIFS(INDIRECT("'"&$A12&"'!B:B"),INDIRECT("'"&$A12&"'!A:A"),B$11)このやり方は実用的ですが、シート数が多いと重くなりやすく、管理も複雑になります。定期運用するなら、データ構造を1表へ統合する方が長期的には安全です。
INDIRECT を使うときの注意点
| 注意点 | 内容 |
|---|---|
| 揮発性関数である | 再計算が増えやすく、ブックが重くなることがあります。 |
| 参照文字列がずれるとエラーになる | シート名の空白、全角半角、引用符の位置を確認します。 |
| シート名変更に弱い | セルに入れたシート名が古いままだと壊れます。 |
| 範囲全体参照は重くなりやすい | B:B ではなく必要範囲に絞る方が安全です。 |
とくに、揮発性関数 が多いブックでは、INDIRECT を追加するだけで体感速度が落ちることがあります。
よくあるエラーと確認ポイント
- #REF!: シート名文字列が間違っている
- 結果が 0 になる: 条件の文字列が一致していない、参照列を取り違えている
- ブックが重い: INDIRECT と列全体参照の組み合わせになっている
- 式が長すぎて管理しづらい: 集計専用シートを作るか、元データの構造を見直した方がよい
固定シート参照で済むのに INDIRECT を入れていないか、まずそこを見直すだけでもかなり整理しやすくなります。
どの方法を選ぶべきか
- 参照先が1枚で固定: そのままシート名を書いた SUMIFS
- 参照先を切り替えたい: INDIRECT + SUMIFS
- 多くのシートをまたいで定期集計する: 集計用の表構造を見直す
複数シートを毎回またぐ設計は、あとで壊れやすく重くなりやすいです。可能なら、明細を1つの表にまとめる設計の方が管理しやすくなります。
FAQ
SUMIFSだけで複数シートを一発集計できますか？
基本的にはできません。INDIRECT を組み合わせるか、シートごとの集計を並べて最後に合計します。
INDIRECTを使わない方がよい場面はありますか？
はい。シート数が多い、ブックが重い、再計算が多い場合は避けた方が安全です。参照先が固定なら、シート名を直接書く形を優先してください。
まとめ
- 別シート1枚なら SUMIFS にシート名を直接付ければよい
- シート名を切り替えるなら INDIRECT を組み合わせる
- 複数シート横断はできるが、重さと管理性に注意する
- 重いときは表設計の見直しも検討する
複数シート集計の応用例をもう少し詳しく見たい場合は、複数のシートからのデータ集計: INDIRECT関数とSUMIFS関数を使った効率的な方法 も参考になります。今回の記事は、その前に整理しておきたい 固定参照・切替・複数シート集計の使い分け を中心にまとめた実務向けガイドです。
コメント