-
Home »
-
エクセル関数一覧表 »
-
Excel関数の目次 »
-
OFFSET関数の使い方
- 基準としたセルからのシフトした位置のセルやセル範囲を指定することができるOFFSET関数の使い方を説明します。
OFFSET関数を使うことで、可変な位置の指定や、可変な範囲の計算できますのでいろいろと応用ができます。
更新:2021/11/08;作成:2009/7/21
オフセット
=OFFSET(基準,行数,列数 [,高さ,幅])
基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル
またはセル範囲の参照 (オフセット参照) を返します。
- OFFSET関数の引数の「基準」「行数」「列数」「高さ」「幅」の関係を下図に示します。
- 基準:基準にするセルの番地を指定します。下図では A1 です。
- 行数は行方向の移動距離
列数は列方向の移動距離となります。
- =OFFSET(A1,2,3) は A1セルから下へ2つ移動し、右へ3つ移動したところになります。
セル位置はD3となるので、D3セルの値「34」が表示されます。
- 高さと幅はセル範囲を表します。
=OFFSET(A1,2,3,2,3) は =OFFSET(A1,2,3) までで D3セル を指しています。
=OFFSET(A1,2,3,2,3)で高さ「2」、幅「3」のセル範囲 D3:F4 となります。
よって、=SUM(OFFSET(A1,2,3,2,3)) で =SUM(D3:F4) を求めたことになります。
- OFFSET関数を使うことで、可変な位置の指定や、可変な範囲の計算ができます。
- 【解答例1】
- 基準のセルを「B3」として、出発地を「B4:B7」と一致する行数、到着地を「C3:E3」と一致する列数を求めます。
行方向の位置はMATCH(C10,B4:B7,0)で求め、列方向の位置はMATCH(C9,C3:E3,0)で求めています。
- MATCH関数については MATCH関数の使い方 をご覧ください。
- =OFFSET(B3,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0)) は =OFFSET(B3,3,2) となります。
基準セルB3から3行下へ、さらに2つ右の列へ移動しますので、D6セルの値が表示されます。
- なお、問題の意図からは外れますが、INDEX関数とMATCH関数でも求めることができます。
=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0))
- INDEX関数の時は 何行目、何列目といった指定方法ですので、
=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0)) は =INDEX(C4:E7,3,2) となり、下図のような数え方になります。
- セル範囲C3:E7の3行目で2列目のセル(D6セル)の値が表示されます。
- INDEX関数については INDEX関数の使い方 をご覧ください。

- 【問題2】OFFSET関数とMATCH関数を使って4月度〜7月度(C7セルに入力されている月まで)の合計を求めなさい。
-
|
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
2 |
商品名 |
4月度 |
5月度 |
6月度 |
7月度 |
8月度 |
9月度 |
10月度 |
11月度 |
12月度 |
1月度 |
2月度 |
3月度 |
3 |
りんご |
3,687 |
8,810 |
3,429 |
281 |
8,804 |
6,694 |
3,633 |
9,038 |
5,216 |
9,392 |
8,042 |
1,890 |
4 |
みかん |
7,373 |
6,340 |
95 |
8,430 |
9,153 |
2,760 |
6,763 |
4,192 |
3,316 |
3,491 |
1,170 |
7,926 |
5 |
バナナ |
4,187 |
1,413 |
7,999 |
7,373 |
3,256 |
1,498 |
9,640 |
8,942 |
7,368 |
5,687 |
9,376 |
1,196 |
6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
7月度 |
までの合計 |
|
|
|
|
|
|
|
|
|
8 |
りんご |
16,207 |
|
|
|
|
|
|
|
|
|
|
|
9 |
みかん |
22,238 |
|
|
|
|
|
|
|
|
|
|
|
10 |
バナナ |
20,972 |
|
|
|
|
|
|
|
|
|
|
|
- 【解答例2】
- 基準のセルを「C3」として、C7セルに入力された「7月度」と一致するセル範囲を合計します。
=OFFSET(基準,行数,列数 ,高さ,幅) の高さは「1」として、幅をMATCH関数で求めています。
なお、行数と列数は「0」なので、=SUM(OFFSET(C3,,,1,MATCH(C$7,$C$2:$N$2,0))) と書くこともできます。
- =SUM(OFFSET(C3,0,0,1,MATCH(C$7,$C$2:$N$2,0))) は =SUM(OFFSET(C3,0,0,1,4)) となります。
C3セルを基準に高さ1、幅4のセル範囲 C3:F3 になりますので、 =SUM(C3:F3) の結果と同じになります。
- 【問題3】OFFSET関数とMATCH関数を使って8月度(C7セルに入力されている月)〜11月度(E7セルに入力されている月まで)の合計を求めなさい。
|
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
2 |
商品名 |
4月度 |
5月度 |
6月度 |
7月度 |
8月度 |
9月度 |
10月度 |
11月度 |
12月度 |
1月度 |
2月度 |
3月度 |
3 |
りんご |
3,687 |
8,810 |
3,429 |
281 |
8,804 |
6,694 |
3,633 |
9,038 |
5,216 |
9,392 |
8,042 |
1,890 |
4 |
みかん |
7,373 |
6,340 |
95 |
8,430 |
9,153 |
2,760 |
6,763 |
4,192 |
3,316 |
3,491 |
1,170 |
7,926 |
5 |
バナナ |
4,187 |
1,413 |
7,999 |
7,373 |
3,256 |
1,498 |
9,640 |
8,942 |
7,368 |
5,687 |
9,376 |
1,196 |
6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
8月度 |
〜 |
11月度 |
|
|
|
|
|
|
|
|
|
8 |
りんご |
28,169 |
|
|
|
|
|
|
|
|
|
|
|
9 |
みかん |
22,868 |
|
|
|
|
|
|
|
|
|
|
|
10 |
バナナ |
23,336 |
|
|
|
|
|
|
|
|
|
|
|
- 【解答例3】
- 基準のセルを「C3」として、C7セルに入力された「11月度」と一致するセル範囲を合計します。
これから、4月度〜7月度の合計を差し引いて求めるのですが、-SUM(OFFSET(B3,0,0,1,MATCH(C$7,$C$2:$N$2,0)) としています。
基準のセルをB3と一つ左にずらしています。
- -SUM(OFFSET(C3,0,0,1,MATCH(C$7,$C$2:$N$2,0)-1) としてもよさそうですが、C7セルに「4月度」としたときにエラーとなってしまいます。
これを防ぐために、基準セルをずらすという小細工をしています。
- 引き算の部分 -SUM(OFFSET(B3,0,0,1,MATCH(C$7,$C$2:$N$2,0) の説明です。
MATCH(C$7,$C$2:$N$2,0) は 5 となります。OFFSET(B3,0,0,1,5)と基準をB3にしているため B3:F3が計算対象になります。
- なお、B列の商品名は文字列であるので、SUM関数では計算されないことをもとに作成したものです。よって、B列が数値である時はこの数式は使用できません。
スポンサードリンク
Home »
エクセル関数一覧表 »
OFFSET関数の使い方
PageViewCounter

Since2006/2/27