以前の記事（スプレッドシートで「○」や「★」の数を条件付きで自動集計する方法）では、Google スプレッドシートで 自分の行より上のデータだけをカウントする方法として、INDIRECT 関数を用いる方法をご紹介しました。
=COUNTIF(INDIRECT("H4:H" & ROW()-1), "○") + COUNTIF(INDIRECT("H4:H" & ROW()-1), "★")この方法は、セルをコピーしても行番号に応じて参照範囲が動くという目的を達成するのに便利です。
しかし、実際の運用の中で以下のような課題が見えてきました。
INDIRECT関数の課題点
1. 参照先が文字列依存で壊れやすい
行の挿入・削除や列の移動に弱く、思わぬタイミングで意図しない参照になる可能性があります。
2. パフォーマンスの低下
INDIRECTは毎回文字列を評価し直すため、シートが大きくなると重くなる原因になります。
3. 列の相対コピーができない
たとえば "AO" のように列を文字列で指定している場合、コピー先でも "AO" 固定となり、自動的に "AP" や "AQ" に変化してくれません。
INDEX関数を用いた代替方法
より堅牢で柔軟な方法として、INDEX 関数を使った動的範囲の指定方法をご紹介します。
改善後の数式（INDIRECTを使わない）
=COUNTIF(AO4:INDEX(AO:AO, ROW()-1), "○") + COUNTIF(AO4:INDEX(AO:AO, ROW()-1), "△")数式の解説
AO4:は固定の開始セル（4行目以降に適用する想定）INDEX(AO:AO, ROW()-1)は、現在の行の1つ上のセルを指定します
たとえば現在の行が10行目なら、AO4:AO9という範囲になります- 結果として「自分より上のデータのみを集計」する動的範囲が実現します
INDEXを使うメリット
| 項目 | INDEX関数を使うメリット |
|---|---|
| 柔軟性 | コピー先で列番号を自動で調整してくれる（相対参照が効く） |
| 安定性 | 行の挿入・削除に強く、構造変更でも壊れにくい |
| パフォーマンス | INDIRECTに比べて処理が軽い |
まとめ
INDIRECT 関数は一見便利に見えますが、大規模シートや運用フェーズにおいては脆弱性があります。
その代替として INDEX を使えば、より安定した、保守性の高い数式設計が可能です。
既存のシートで INDIRECT を多用している場合も、少しずつ INDEX に置き換えていくことで、長期的に扱いやすいスプレッドシートを維持することができるでしょう。
コメント