Excelで取引データをSUMIFSで集計するとき、範囲の最終行が増減して「H6:H1608」のように固定してしまうのはよくある悩みです。本記事では、最終行番号を別セル（例：S5）に持たせた場合に、SUMIFSの範囲を動的に伸縮させる方法を実務目線で整理します。
SUMIFSの「集計範囲が固定になる」問題を整理する
たとえば元データが Sheet1（シート名：All transactions）にあり、集計側の Sheet2 で次のように書いているケースを考えます。
| 項目 | 例 | 補足 |
|---|---|---|
| 集計対象（合計する列） | H列（例：金額） | SUMIFSの第1引数（sum_range） |
| 条件1の列 | U列（例：口座/取引先など） | criteria_range1 |
| 条件2の列 | K列（例：月/カテゴリなど） | criteria_range2 |
| データ開始行 | 6行目（H6 から） | ヘッダー行が1〜5行にある前提 |
| 最終行番号 | S5 に計算結果（例：1608） | 「この行までがデータ」と決めるための値 |
固定範囲で書くと、集計式は次のようになりがちです。
=SUMIFS(
'All transactions'!H6:H1608,
'All transactions'!U6:U1608, $B6,
'All transactions'!K6:K1608, C$4
)しかし、データは日々追加・修正されるため、1608 という最終行番号を式に直書きするとメンテナンスが破綻しやすくなります。そこで、Sheet1 の S5 に入っている最終行番号を参照し、SUMIFS の範囲を動的にするのが今回のテーマです。
前提：S5の最終行番号は「必ず埋まる列」を基準に作る
動的範囲を作る以前に、S5 の「最終行番号」の精度が低いと、集計結果がズレます。最終行を判定する列は、必ず値が入る列（取引ID、日付、伝票番号など）を選ぶのが鉄則です。
例として、A列が必ず埋まる（空白がない）前提なら、S5 に次のような式で「最終行番号」を求められます（列は環境に合わせて置換してください）。
=LOOKUP(2,1/('All transactions'!A:A<>""),ROW('All transactions'!A:A))この式は、A列の最終入力行の行番号を返します。空白行が途中にあっても「最後に値が入っている行」を拾いやすいのが利点です。
一方で、全列参照（A:A）はブックの規模によっては重く感じることがあるため、データ行数が多い場合はテーブル化（後述）や、判定列を必要最小限の範囲に絞る運用も検討してください。
解決策A：INDIRECTで「文字列から範囲参照」を作る
最終行番号（S5）を文字列として連結し、INDIRECT で「文字列→参照」に変換して SUMIFS に渡す方法です。質問の状況では、最も直感的で採用されやすいアプローチになります。
=SUMIFS(
INDIRECT("'All transactions'!H6:H"&'All transactions'!$S$5),
INDIRECT("'All transactions'!U6:U"&'All transactions'!$S$5), $B6,
INDIRECT("'All transactions'!K6:K"&'All transactions'!$S$5), C$4
)- ポイント：
"H6:H"&S5のように「末尾の行番号」を文字列で組み立てる - シート名の注意：シート名にスペースがある場合は
'All transactions'のようにシングルクォートで囲む - 参照の固定：S5 は通常固定したいので
$S$5にしておくとコピペ事故を防げる
ただし、ここで必ず知っておきたいのが INDIRECT は揮発性関数（ブック内の変更に応じて再計算が走りやすい）だという点です。集計表が数百〜数千セルに増えると、体感で重くなることがあります。
式の読みやすさを上げたい場合は、Microsoft 365 などで使える LET を併用すると、同じ参照を何度も書かずに済みます（揮発性そのものは変わりませんが、保守性は上がります）。
=LET(
last,'All transactions'!$S$5,
sumR,INDIRECT("'All transactions'!H6:H"&last),
c1R,INDIRECT("'All transactions'!U6:U"&last),
c2R,INDIRECT("'All transactions'!K6:K"&last),
SUMIFS(sumR,c1R,$B6,c2R,C$4)
)解決策B：OFFSETで「開始セル＋高さ（行数）」の範囲を作る
OFFSET は、開始セルから「何行・何列ずらすか」と「高さ・幅」を指定して範囲を返します。最終行番号 S5 があるなら、高さ＝(S5 − 開始行 + 1) で範囲を作れます。
=SUMIFS(
OFFSET('All transactions'!$H$6,0,0,'All transactions'!$S$5-6+1,1),
OFFSET('All transactions'!$U$6,0,0,'All transactions'!$S$5-6+1,1), $B6,
OFFSET('All transactions'!$K$6,0,0,'All transactions'!$S$5-6+1,1), C$4
)- メリット：開始点（H6）を固定したまま、S5 で高さだけ変えられる
- 注意点：OFFSET も揮発性関数。INDIRECT と同様に大規模だと重くなり得る
- 落とし穴：S5 が 6 未満（データなし）だと高さが 0 以下になり、#REF! になりやすい
データが空のときに 0 を返したい場合は、ガードを入れておくと安全です。
=LET(
last,'All transactions'!$S$5,
h,last-6+1,
IF(h<=0,0,
SUMIFS(
OFFSET('All transactions'!$H$6,0,0,h,1),
OFFSET('All transactions'!$U$6,0,0,h,1), $B6,
OFFSET('All transactions'!$K$6,0,0,h,1), C$4
)
)
)解決策C：Excelテーブル（構造化参照）で「自動で伸びる」ようにする
データ範囲を Excel のテーブルに変換すると、行が増えても列参照が自動で拡張されます。結果として、最終行番号（S5）を管理する発想そのものが不要になりやすいのが最大の強みです。
手順のイメージは次の通りです。
- All transactions のデータ範囲内をクリック
- 「挿入」→「テーブル」（または Ctrl+T）
- ヘッダーがある場合は「先頭行をテーブルの見出しとして使用する」を有効
- テーブル名を分かりやすく変更（例：
Trans）
テーブル化できれば、SUMIFS は列名で書けます（列名は環境に合わせて置換してください）。
=SUMIFS(
Trans[金額],
Trans[口座], $B6,
Trans[月], C$4
)- メリット：非揮発で軽い／範囲伸縮のミスが減る／列追加・並び替えに強い
- 注意点：列名（見出し）を整備する必要がある／慣れるまで少し戸惑うことがある
実務では、ブックが育つほど「最終行番号をどこかで持つ」運用は破綻しがちです。将来的に安定させたいなら、まずはテーブル化を最優先で検討するのが堅実です。
補足：INDEXで終端セルを作る（非揮発で高速になりやすい）
「S5 は既にある」「でも INDIRECT/OFFSET の揮発性が気になる」という場合に、定番となるのが INDEX で範囲終端を作る方法です。揮発性を避けられるため、集計式が増えても比較的安定して動きやすくなります。
=SUMIFS(
'All transactions'!$H$6:INDEX('All transactions'!$H:$H,'All transactions'!$S$5),
'All transactions'!$U$6:INDEX('All transactions'!$U:$U,'All transactions'!$S$5), $B6,
'All transactions'!$K$6:INDEX('All transactions'!$K:$K,'All transactions'!$S$5), C$4
)この書き方の考え方はシンプルで、「H6 から、H列の S5 行目まで」という範囲を作っています。SUMIFS は「合計範囲」と「条件範囲」のサイズ（行数・列数）が一致している必要があるため、U列・K列も同じ考え方で揃えます。
さらに読みやすくするなら LET で整理すると、メンテナンスがぐっと楽になります。
=LET(
last,'All transactions'!$S$5,
sumR,'All transactions'!$H$6:INDEX('All transactions'!$H:$H,last),
c1R,'All transactions'!$U$6:INDEX('All transactions'!$U:$U,last),
c2R,'All transactions'!$K$6:INDEX('All transactions'!$K:$K,last),
SUMIFS(sumR,c1R,$B6,c2R,C$4)
)現場感のあるおすすめ
「テーブル化できない事情がある」「それでも速く・壊れにくくしたい」なら、まず INDEX 終端（＋必要なら LET）を検討すると失敗が少なくなります。
方法別の比較：どれが最適かを一目で判断する
動的範囲の作り方は複数ありますが、選び方を間違えると「最初は動いたのに、後から重くて使い物にならない」となりがちです。特徴を比較して、ブックの規模に合う方法を選びましょう。
| 方法 | 実装の分かりやすさ | 処理の軽さ | 壊れにくさ | 向いているケース |
|---|---|---|---|---|
| INDIRECT | ◎（直感的） | △（揮発性で重くなりやすい） | ○（参照は明確だが大量だと負荷） | まず動かしたい／式が少ない／短期運用 |
| OFFSET | ○（高さの概念が分かれば簡単） | △（揮発性） | ○（開始点固定が強いが空データに注意） | 開始セルからの「高さ」で管理したい |
| INDEX終端 | ○（慣れると読みやすい） | ◎（非揮発で安定） | ◎（参照が壊れにくい） | 集計式が多い／動作速度を重視／長期運用 |
| Excelテーブル | ○（最初だけ設定が必要） | ◎（非揮発、保守性が高い） | ◎（列名で管理、拡張に強い） | データが増え続ける／運用を標準化したい |
迷ったら次の順で考えると判断が早いです。
- 最優先：テーブル化できるならテーブル（範囲の悩みを根本から消せる）
- 次点：テーブル化が難しいなら INDEX 終端（非揮発で速い）
- 暫定：小規模なら INDIRECT / OFFSET でも可（ただし増殖に注意）
SUMIFSの動的範囲で「よくある失敗」とチェックポイント
動的にしたのに結果がおかしい／エラーになる、というときは、原因の多くが次のパターンです。
範囲のサイズが揃っていない（#VALUE!の原因になりやすい）
SUMIFS は、sum_range と各 criteria_range の行数・列数が同じである必要があります。
例：合計範囲だけ H6:H1608、条件範囲が U6:U1607 のように 1 行ズレると、結果が不正になったりエラーになったりします。
対策はシンプルで、開始行（6行目）と終端（S5）を全範囲で統一することです。INDIRECT/INDEX/OFFSET のいずれを使う場合も、式をコピペするときに「どこかだけ開始セルが H7 になっていた」などの事故が起きやすいので注意してください。
S5の値が想定外（空白、文字列、途中で途切れる）
S5 が空白のままだと、INDIRECT では H6:H のような中途半端な文字列になり、#REF! の原因になります。S5 が文字列になっている場合も、意図した範囲にならないことがあります。
対策として、S5 を作る列は「必ず埋まる列」にする、または S5 側で数値化（VALUE）や空白時の既定値を入れるのが効果的です。
集計結果が0になる（条件の型違い）
SUMIFS が 0 を返すとき、範囲の問題よりも「条件の一致」が崩れているケースが少なくありません。特に多いのが以下です。
- 日付が「日付」ではなく文字列として入っている
- 数字が文字列（先頭に ‘ が付いている等）
- 全角・半角スペースの混入、表記ゆれ
動的範囲化は「参照の伸縮」だけを変えるので、元々のデータ品質も合わせて整えると、集計の信頼性が一段上がります。
実務でさらに扱いやすくする：名前定義と「式の短縮」
動的範囲の式は、どうしても長くなりがちです。長い式は修正ミスを生みやすいので、実務では「名前定義（名前の管理）」や LET を使って短縮すると安定します。
名前定義で「動的範囲」を部品化する
たとえば名前の管理で、次のような名前を作っておくと、Sheet2 の式が読みやすくなります。
- SumAmt：
'All transactions'!$H$6:INDEX('All transactions'!$H:$H,'All transactions'!$S$5) - CritU：
'All transactions'!$U$6:INDEX('All transactions'!$U:$U,'All transactions'!$S$5) - CritK：
'All transactions'!$K$6:INDEX('All transactions'!$K:$K,'All transactions'!$S$5)
その上で、集計側は次のようにシンプルになります。
=SUMIFS(SumAmt,CritU,$B6,CritK,C$4)「どの列を合計しているか」「どの列を条件にしているか」が一目で分かるため、引き継ぎやすいのも利点です。
まとめ：SUMIFSの最終行可変は「速さ」と「保守性」で選ぶ
SUMIFS の集計範囲を S5 の最終行番号で可変にする方法は、INDIRECT、OFFSET、INDEX 終端、Excelテーブルの4つに整理できます。
- 短期・小規模でまず動かすなら INDIRECT
- 開始セル＋高さで考えたいなら OFFSET
- 速度と安定性を重視するなら INDEX 終端
- 運用を根本から楽にするなら Excelテーブル
データが増え続ける現場ほど、集計式は増殖し、揮発性関数の負荷が効いてきます。最初から「壊れにくい設計」を選ぶことで、集計表は長く使える資産になります。
コメント