Tips 「OFFSET関数」
集計対象を自由自在に変えれるため、使いこなすと超強力な関数。
単体で使うことは少なく、他の関数と複雑に組み合わせて使用することで真価を発揮する。
OFFSET関数
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。
OFFSET関数 - support.office.com
formula
=OFFSET(1_基準セル, 2_行方向シフト数, 3_列方向シフト数, 4_基準から選択する行数, 5_基準位置から選択する列数)
つかいかた
- OFFSETのカッコの中身の第1引数（カンマで区切られた1つ目の部分）で「基準セル」を指定。
- 第2引数（カンマで区切られた2つ目の部分）で「基準セルから縦に何行下を参照するのか」を指定。
- 第3引数で「基準セルから横に何列右を参照するのか」を指定。
ここまでの指定で集計対象のセルの一番左上を指定し、
- 第4引数で「そこから縦に何行分が集計対象なのか」を指定。
- 第5引数で「そこから横に何列分が集計対象なのか」を指定。
下の図では、「B3」セルから4行下、3列右のセルを起点として、
3行2列の範囲に含まれる数値を合計した値をA1セルに出力している。
計算例
例1) A1から1行下、2列右のセル（=C2）を参照する
example
=OFFSET(A1, 1,2, 1,1)
例2) 「$B$3から4行下、3列右のセル」から3行2列分（=C2:C3）を参照する
example
=OFFSET($B$3, 4,3, 3,2)
=SUM(OFFSET($B$3, 4,3, 3,2))は=SUM($E$7:$F$9)と同じ
基準から選択する行数, 基準位置から選択する列数がそれぞれ1のとき、
OFFSET関数の第4引数、第5引数は省略することができる
以下の2つの例は同じ結果を返す
example
=OFFSET(A1, 1,2, 1,1)
example
=OFFSET(A1, 1,2)
OFFFSETの参照範囲は数式の検証機能を使用すれば簡単に確かめることができる
OFFSET関数の引数には自由に数式を入れることができるので、
例えば、MATCH関数, INDEX関数のような関数を組み合わせて使用することにより、
状況に合わせて集計範囲を自由自在に変える超柔軟な数式を作ることができる。
使いこなすと本当に強力。
→【Excel】OFFSETで過去n日間平均を計算
→【Excel】 SUMIFS入門からOFFSETを活用した動的集計まで