Excel:指定した数だけシフトした範囲を返す(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 などとします。)
|