Excelで月次の集計作業をしていると、
データが増えるたびにセル範囲を手動で修正するのが面倒…
と感じることはありませんか？
- データを追加すると、SUM関数の範囲を修正しないといけない
- ドロップダウンリストに新しいデータを自動で反映させたい
- もっと効率的に集計作業をしたい！
そんな悩みを解決できるのが、「OFFSET関数」です。
OFFSET関数を使うと、動的な範囲を設定できるため、データが増減しても手動で調整する必要がなくなります。
この記事では、OFFSET関数の構造や特徴、具体的な活用例をわかりやすく解説します。
この記事のゴール
- OFFSET関数の構造・特徴がわかる
- OFFSET関数の具体的な使い方がわかる
- OFFSET関数活用時の注意点がわかる
2. OFFSET関数について
OFFSET関数の構造
OFFSET関数の基本構文は以下の通りです。
=OFFSET(基準セル, 移動行数, 移動列数, [高さ], [幅])
| 基準セル： | 検索する値を指定します。 |
| 移動行数： | 基準セルから何行移動するか（正の値で下方向、負の値で上方向 |
| 移動列数： | 基準セルから何列移動するか（正の値で右方向、負の値で左方向） |
| 高さ ： | （省略可）返す範囲の行数を指定します。 |
| 幅 ： | （省略可）返す範囲の列数を指定します。 |
たとえば、=OFFSET(C1,2,-1) は、A1を基準に 2行下、1左のセルを取得します。
OFFSET関数の特徴
OFFSET関数には、以下のような特徴があります。
①動的な範囲を設定できる
- データが増えても、数式を修正する必要がない
②他の関数と組み合わせて柔軟に活用できる
- SUM関数、MATCH関数などと併用するとさらに便利
③セルを直接参照しないため、柔軟なデータ操作が可能
- 名前の定義と組み合わせて使うと、より分かりやすい
3. OFFSET関数の活用例
「直近〇件のデータ」を集計する
例えば、契約台帳のように行が挿入され、集計範囲が増えていく場合は、OFFSET関数を使うと自動的に最新のデータを反映できます。
設定方法
最新の10行分のデータを合計する場合、以下の数式を使います。
=SUM(OFFSET(E1,MATCH(10000000,E:E)-10,0,10,1))
この数式でやっていること
- MATCH(10000000,E:E)でE列のデータの最終行を取得
- そこから10行上（-10）のセルから、10行下のセルまでを合計
これで、データが増えても手動で修正する必要がなくなります。
ドロップダウンリストへの自動反映
例：プルダウンメニューに新しいデータを自動追加
名前の定義と組み合わせることで、データリストが増えても、ドロップダウンリスト（データの入力規則）を自動更新できます。
設定方法
- 「数式」タブ の「名前の定義」を選択する
- 任意の名前をつけ、参照範囲に「
=OFFSET(【参照シート名】!$A$1, 0, 0, COUNTA(【参照シート名】!$A:$A), 1)」を入力し「OK」をクリックする
今回はシート名：「サービスメニュー」のセルを引用するので、定義する名前も「サービスメニュー」という名前にします。
- データの入力規則を設定したいセルを選択し、「データ」タブの「データの入力規則」をクリック
- 「入力値の種類」をリスト、「ドロップダウンリストから選択する」にチェックを入れる
- 元の値に「=サービスメニュー」と入力し「OK」をクリックする
- プルダウンメニューからサービスメニューが選択できるようになりました。
このように設定すると、新たなサービスを追加した場合でも自動的にドロップダウンリストに表示されるようになります。
データが追加されると、自動でリストに反映されるので
手作業でリストを更新する必要がなくなりますね。
前月差/前年差の自動計算
例：列方向に追加される月次データの前月差を自動計算
Excelでは、月ごとに売上データなどを横方向（列方向）に追加していくケースがあります。
この場合、最新月と前月のデータを自動で参照し、前月差を計算する方法をOFFSET関数で実現できます。
設定方法
最新月と前月のデータを取得し、前月との差を計算するには、以下のOFFSET関数を使用します。
=OFFSET(AL3,0,-1) -OFFSET(AL3,0,-2)
この数式でやっていること
- OFFSET(AL3,0,-1)で前月のデータを参照
- OFFSET(AL3,0,-2)で2月前のデータを参照
4. OFFSET関数の注意点
OFFSET関数は便利ですが、いくつかの注意点があります。
揮発性関数
OFFSET関数は 「揮発性関数」 であり、ワークシートが更新されるたびに再計算されます。
参照範囲がわかりにくい
OFFSET関数はセル範囲を直接指定せずに取得するため、どの範囲を参照しているのか分かりにくいことがあります。
チームで活用する Excelの場合は、参照先をメモするなど、わかりやすくしておくとよいでしょう。
INDEX関数 + MATCH関数の方が適している場合もある
特定のデータを取得する場合、INDEX関数とMATCH関数の組み合わせの方が効率的なことがあります。
用途に応じて使い分けると良いでしょう
5. まとめ
OFFSET関数を活用すると、データ範囲が変動しても自動で対応できるようになります。
OFFSET関数の活用に際しては、以下の点に留意しましょう。
- 基本構造を理解する
- 集計の自動化やドロップダウンリストの更新に活用する
- 計算速度の低下に注意する
OFFSET関数を活用して、Excelの自動化を進めてみてください！
