教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010-2016:指定した数だけシフトした範囲を返す(OFFSET関数)

 

 

 

 

 

 


 

 

 

 

 

 

基準位置から指定した行数、列数ぶん移動した『位置』 や『セル範囲』を返すことができます。

  • OFFSET (基準値 , 行数 , 列数 , 高さ , 幅 )
    OFFSET(基準とするセルを指定 , ○行移動 , ○列移動 ,○行選択 , ○列選択)

※ OFFSET関数ではセルの位置 (や範囲)を返す関数です。通常このままでは使わず、他の関数と組み合わせて使います。

※ 「高さ」と「幅」は、返り値がセル範囲の場合に指定します。(省略した場合、基準値と同じ行(高さ)、列(幅)が返ります)

※ OFFSET関数で【行全体】【列全体】を返したい場合は、高さと幅を省略した場合は基準値と同じ範囲を返すことを利用して最初の基準値を行(列)全体とします。

例えばOFFSET($C:$C,0,1)とするとD列のデータを全て返します。

 


◆ 例 (1) 「氏名」を基準として、2行1列移動した値を返す ◆

= OFFSET (A1 , 2 ,1 )

答え 200 (A1から2行1列移動したセル)


◆ 例 (2) オフセットした先のセル範囲の合計金額を返す ◆

= SUM (OFFSET (A1 , 1 ,1 , 4, 1 )

答え 1450 (A1から1行1列移動し、4行1列分のセル範囲の合計を返す)


◆ 例(3) 今後データ入力が更新される場合、常にそれらも合計の対象範囲にするには?(重要)

= SUM (OFFSET (A1 , 1 ,1 , COUNT(B:B), 1 )

A1から1行1列移動し、COUNT関数その列にあるデータ個数(=セル範囲の行(高さ))と幅1列分をセル範囲としてそれらを合計する

 

つまり、B2から5行(データ個数)1列分の範囲を合計するので、答 は2251 (A1から1行1列移動し、5行1列分のセル範囲の合計)今後データ が更新されるとCOUNT関数によって対象セル範囲も更新される。(COUNT関数で数えないセル(項目名など)がある場合は、COUNT(B:B)-1 などとします。)

 

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows