OFFSET+MATCHで動的範囲を作る方法は?
結論
OFFSET+MATCHでデータの最終行を自動判定し、動的に広がる範囲を作成できます。
詳しい解説
OFFSETとMATCHを組み合わせることで、データが追加されても自動で範囲が広がる動的範囲を作成できます。パターンは`=SUM(OFFSET(A1, 0, 0, MATCH(9.99E+307, A:A), 1))`で、A列の最終行まで自動で範囲を拡張します。全バージョンで使用可能ですが、OFFSETは揮発性関数のため多用するとパフォーマンスに影響します。Excel 2021以降ではテーブル機能やスピル機能での代替が推奨されます。名前の管理で名前を付けてグラフの範囲に使う等の用途があります。
執筆・検証:田中 けんた|Excel歴15年・MOS資格保持
バージョン対応
| Excel 2016 | Excel 2019 | Excel 2021 | Excel 2024 | Microsoft 365 |
|---|---|---|---|---|
| 対応 | 対応 | 対応 | 対応 | 対応 |
実務での使用例
データ追加に対応する自動集計範囲
A列にデータが追加されるたびに自動で範囲が広がり、常に全データを合計
=SUM(OFFSET(A1, 0, 0, MATCH(9.99E+307, A:A), 1))動的範囲をグラフに適用
名前の管理でOFFSET式を定義し、グラフの範囲に指定することでデータ追加に自動対応
=OFFSET(データ!$A$1, 0, 0, COUNTA(データ!$A:$A), 1)