指定位置からデータを取得するOFFSET関数-GSS/Excel/関数
monroe指定位置からデータを取得するOFFSET関数-GSS/Excel/関数
大きなデータテーブルから特定の位置にあるデータを効率的に取得したい、基準点から相対的な位置のセルを参照したいことってありますよね。
今回はOFFSET関数をご紹介します。基準セルから行と列を指定した分だけずらした位置のデータを取得できる便利な関数です！早速やっていきましょう。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 基準 | 商品A | 商品B | 商品C | 商品D |
| 2 | 1月 | 150 | 200 | 120 | 180 |
| 3 | 2月 | 160 | 220 | 110 | 190 |
| 4 | 3月 | 140 | 210 | 130 | 170 |
いつものシートです。[F2]セルに取得したいデータの条件が書かれています。
基準セル[A1]から2行下、3列右の位置（D3セル）のデータを取得していきます。
構文
基準セルから指定した行数・列数分移動した位置のセルまたは範囲を返します。高さと幅を指定すると範囲として取得できます。
正解例（仮）
まず正解例は下記の通り。
ただこれも最適解ではないので、その辺りも説明していきます。
A1セルを基準として、2行下（行のオフセット=2）、3列右（列のオフセット=3）の位置、つまりD3セルの値を取得します。
上の式で言うと、基準セルは[A1]。
行のオフセットは[2]（2行下）。
列のオフセットは[3]（3列右）。
合体させた結果が[F2]=OFFSET(A1,2,3)になります。
先ほどこれが最適解ではないと言いました。それはなぜかというと、参照が全て固定されていない状態のままだからです。
絶対参照と相対参照
通常セルを使った関数を別のセルにコピペすると、相対参照され、位置がずれます。OFFSET関数でも同じことが起こります。
基準セルを固定したい場合は「$」を使って絶対参照にします。
範囲指定での活用
OFFSET関数は高さと幅を指定することで、範囲を取得することもできます。これをSUM関数などと組み合わせると強力です。
| 月 | 商品A | 商品B | 商品C | 商品D | 月間合計 |
|---|---|---|---|---|---|
| 1月 | 150 | 200 | 120 | 180 | =SUM(OFFSET($A$2,ROW()-2,1,1,4)) |
| 2月 | 160 | 220 | 110 | 190 | =SUM(OFFSET($A$2,ROW()-2,1,1,4)) |
動的データ取得の実践
OFFSET関数の真価は、行と列のオフセットを動的に変更できることです。ユーザーの入力に応じてデータを取得する仕組みを作れます。
| 月選択 | 商品選択 | 取得値 |
|---|---|---|
| 2 | 3 | =OFFSET($A$1,B2,C2) |
本当の正解
実際の業務では、基準セルは固定し、オフセット値は動的に変更できるようにするのが最も実用的です。
このように書くことで：
- 基準セル（$A$1）は固定
- 行のオフセット（$E$2）と列のオフセット（$F$2）は別セルで指定
- E2、F2の値を変更するだけで異なるデータを取得可能
| 行オフセット | 列オフセット | 取得結果 |
|---|---|---|
| 1 | 2 | =OFFSET($A$1,$E$2,$F$2) |
これで完璧！E2とF2の値を変更するだけで、テーブル内の任意の位置のデータを動的に取得できます。
まとめ
OFFSET関数の使い所
- 基準点から相対的な位置のデータを取得したいとき
- 動的にデータの参照位置を変更したいとき
- 可変サイズの範囲を扱いたいとき
構文
=OFFSET(基準セル, 行のオフセット, 列のオフセット, [高さ], [幅])
例文
- OFFSET($A$1,2,3) - A1から2行下、3列右の値
- OFFSET($A$1,ROW()-1,1,1,4) - 動的な行指定で4列分の範囲
- SUM(OFFSET($A$1,1,1,5,3)) - 指定範囲の合計
活用のコツ
- 基準セルは絶対参照（$）で固定する
- オフセット値は別セルで管理して動的に変更
- 範囲指定と組み合わせて集計関数と連携
- エラー処理を忘れずに実装する
OFFSET関数をマスターすることで、より柔軟で動的なデータ参照が可能になります。ぜひ実際の業務で活用してみてくださいね！
