1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. OFFSET関数の使い方

OFFSET関数の使い方(指定した位置のセルを参照する):Excel関数

• 基準としたセルからのシフトした位置のセルやセル範囲を指定することができる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】OFFSET関数とMATCH関数を使って、出発地〜到着地の運賃を求めなさい。

  • 	B	C	D	E
    2	運賃表
    3		東京	名古屋	大阪
    4	鹿児島	50,000	40,000	35,000
    5	宮崎	45,000	35,000	30,000
    6	熊本	40,000	30,000	25,000
    7	福岡	30,000	20,000	15,000
    8
    9	到着地	名古屋
    10	出発地	熊本
    11	運賃は	30,000

• 【解答例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列が数値である時はこの数式は使用できません。
  

スポンサードリンク

PageViewCounter

Since2006/2/27