OFFSET関数の使い方: 可変の範囲と移動合計

=OFFSET(A1,3,2)は、A1から3行下、2列右のセル、つまりC4を返します。高さと幅も指定すると範囲全体を返し、OFFSETは主にこの使い方をします。移動したり広がったりする範囲の合計や平均です。

数式リセット

G2fx

=OFFSET(A1,E2,F2)

	A	B	C	D	E	F	G
1	Product	Category	Price		Rows	Cols	Result
2	Apple	Fruit	$1.20		3	2	$0.80
3	Pear	Fruit	$1.50
4	Carrot	Vegetable	$0.80
5	Bread	Bakery	$2.40
6	Milk	Dairy	$1.10

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

A1から3行下、2列右はC4で、Carrotの価格$0.80です。Colsを0にすると名前のCarrotに、Rowsを5にするとMilkの行になります。行と列はマイナスにして上や左に移動することもでき、シートの上端や左端を越えると#REF!になります。

OFFSET関数の構文

=OFFSET(reference, rows, cols, [height], [width])

• reference(参照): 基準のセル(または範囲)。
• rows(行数)、cols(列数): どれだけ移動するか。0なら動きません。
• height(高さ)、width(幅): 移動先のセルから数えた、返す範囲の大きさ。省略するとreferenceと同じ大きさです。

複数のセルを返すOFFSETを単独でセルに入れると、Excel 365ではスピルし、古いバージョンではたいてい#VALUE!と表示されます。SUM、AVERAGE、COUNT、MAXの中では範囲として動きます。

最後のN行を合計する

OFFSETの定番の使い方は、何行追加されても常に直近の行を対象にする合計です。COUNTが値の個数を求め、OFFSETが最後のN個の最初まで下に移動し、高さでN行を取ります。

数式リセット

F2fx

=SUM(OFFSET(B1,COUNT(B2:B13)-E2+1,0,E2,1))

	A	B	C	D	E	F
1	Month	Sales			Last N	Total
2	Jan	4,200			3	14,900
3	Feb	3,900
4	Mar	4,800
5	Apr	5,100
6	May	4,600
7	Jun	5,300
8	Jul	5,000

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

値は7つあるので、OFFSETはB1から7-3+1、つまり5行下のB6から始めて3行を取ります。MayからJulまでで14,900です。B9(8月)に4900と入力すると、COUNTが8を数えるので、合計はJun、Jul、Augに移ります。範囲B2:B13は1年の残りの分の余裕を取っています。列の途中に空のセルがあってはいけません。あるとCOUNTが少なく数え、範囲が違う場所に来てしまいます。

移動平均

行のオフセットをマイナスにしたOFFSETを列の下にコピーすると、各行が自分より上の行を範囲にします。ここでは当月と前の2か月の平均です。

数式リセット

C4fx

=AVERAGE(OFFSET(B4,-2,0,3,1))

	A	B	C
1	Month	Sales	3-month average
2	Jan	4,200
3	Feb	3,900
4	Mar	4,800	4,300
5	Apr	5,100	4,600
6	May	4,600	4,833
7	Jun	5,300	5,000
8	Jul	5,000	4,967

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

C4はB2:B4(JanからMar)を平均して4,300になります。下の各行では範囲が1つずつ下に動きます。3を6に、-2を-5に変えると6か月の平均になります(そのときは数式を7行目から始めます)。実はこの例にOFFSETは要りません。相対参照はもともと動くので、C4から=AVERAGE(B2:B4)を下にコピーしても同じになります。OFFSETが役に立つのは、範囲の大きさをセルで決めるときです。

INDEXのほうが向いていることが多い理由

OFFSETは揮発性です。どのセルを指すか前もってわからないので、Excelはブックのどこかを編集するたびにすべてのOFFSETを再計算します。何千もあるシートは遅くなります。INDEXも参照を返し、開始セル:INDEX(...)と書いた範囲は揮発性にならずに同じように広がります：

=SUM(OFFSET(B2, 0, 0, E2, 1))        first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2))           same rows, not volatile

どちらも列の最初のE2行を読みます。OFFSETは確認もしにくくなります。「参照元のトレース」や数式の編集中にExcelが描く色付きの枠は、基準のセルと引数を示すだけで、OFFSETが最終的に返す範囲は示しません。ちょっとしたモデルやグラフの範囲にはOFFSETを、大きなブックではINDEXを使ってください。範囲を返す方法はINDEXに詳しく、INDIRECTはもう1つの揮発性の参照関数です。

練習: 最初のNか月の合計

数式リセット

F2fx

	A	B	C	D	E	F
1	Month	Sales			First N	Total
2	Jan	4,200			4
3	Feb	3,900
4	Mar	4,800
5	Apr	5,100
6	May	4,600
7	Jun	5,300
8	Jul	5,000

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: F2で、SUMの中でOFFSETを使い、最初のNか月を合計してください。NはE2にあります。

答え合わせ答えを見る