OFFSET関数

OFFSET関数 | 基準のセル・セル範囲からシフトしたセル・セル範囲の参照を返します

OFFSET関数
種類	検索/行列関数
できること	基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照を返します

書式	OFFSET(基準セル範囲、行数、列数、高さ、幅)
基準セル範囲 (必須)	基準となるセル範囲の参照を指定します。
行数 (必須)	基準セル範囲の左上隅のセルを上方向または下方向へシフトする行数を指定します。行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。
列数 (必須)	基準セル範囲の左上隅のセルを左方向または右方向へシフトする列数を指定します。列数に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。
高さ (省略可)	オフセット参照の行数を指定します。 ※省略すると、基準セル範囲と同じ行数であると見なされます。
幅 (省略可)	オフセット参照の列数を指定します。 ※省略すると、基準セル範囲と同じ列数であると見なされます。

OFFSET関数の使い方　その１　セルの参照を返す

OFFSET関数　その１

式	=OFFSET(C3, 1, D6)
説明	セルD6に「月」を数字で入力すれば、対応する月の売上金額を表示する式を作成していきます。この式では、基準セル範囲であるセルＣ３から、第２引数「行数」に指定された１行下にシフトし、第３引数「列数」に指定されたセルＤ６にある値である３列右にシフトしたセルＦ４への参照を返します。セルＦ４の値が 250 となっている為、250 という結果を表示しています。 ※第４引数「高さ」、第５引数「幅」が省略されていますので、基準セル範囲の高さと幅である「１」がそれぞれ「高さ」と「幅」に使われています。

式

=OFFSET(C3, 1, D6)

説明

セルD6に「月」を数字で入力すれば、対応する月の売上金額を表示する式を作成していきます。

この式では、基準セル範囲であるセルＣ３から、第２引数「行数」に指定された１行下にシフトし、第３引数「列数」に指定されたセルＤ６にある値である３列右にシフトしたセルＦ４への参照を返します。セルＦ４の値が 250 となっている為、250 という結果を表示しています。

※第４引数「高さ」、第５引数「幅」が省略されていますので、基準セル範囲の高さと幅である「１」がそれぞれ「高さ」と「幅」に使われています。

OFFSET関数の使い方　その２　セル”範囲”の参照を返す

OFFSET関数　その２

式	=SUM(OFFSET(D4, 0, 0, 1, D6))
説明	その１では、単月の売上金額を表示する式を作りましたが、今度は、指定した月までの累計売上を計算する式を作ってみたいと思います。この式では、SUM関数とセル範囲参照するOFFSET関数を組合せています。ちょっと、ややこしいので、OFFSET(D4, 0, 0, 1, D6) の部分を先に見ていきます。まず、基準セル範囲であるセルＤ４から、第２引数「行数」に指定された０行下にシフトし、第３引数「列数」に指定された０列右にシフトします。つまり、セルＤ４そのものをまず参照します。そして、第４引数「高さ」に「１」、第５引数「幅」に指定されたセルＤ６には「３」とあるので、セルＤ４を起点とする高さ「１」幅「３」のセル範囲、つまり「Ｄ４：Ｆ４」というセル範囲を返します。 1月から3月までの各月の単月売上を含むセル範囲「Ｄ４：Ｆ４」を参照できているのであれば、OFFSET関数部分の =OFFSET(D4, 0, 0, 1, D6) だけ入力すれば良いように思われます。しかし、この数式を入力しただけだと、「３０」という１月の単月売上が結果セルに表示されるだけに終わります。これは、OFFSETにより、セル範囲「Ｄ４：Ｆ４」への参照は出来ているものの、セル１つだけの参照ではなく、セル３つを含むセル範囲への参照ですので、実際には、{30, 80, 250} という配列が返されていると考えることが出来ます。で、結果セルには、この配列の１つめ「30」だけが表示された、ということになります。そこでSUM関数の出番なのですが、セル範囲「Ｄ４：Ｆ４」への参照となっているOFFSET関数部分をSUM関数で合計することで、セル範囲「Ｄ４：Ｆ４」の中身である配列{30, 80, 250}に含まれる全要素を合計して、3月までの累計売上である「360」という結果を得ることができます。この式をステップに毎に読み替えて、このような感じで理解できるかと思います。 = SUM(OFFSET(D4, 0, 0, 1, D6)) = SUM(OFFSET(D4, 0, 0, 1, 3)) = SUM(D4:F4) = SUM({30,80,250}) = 360

式

=SUM(OFFSET(D4, 0, 0, 1, D6))

説明

その１では、単月の売上金額を表示する式を作りましたが、今度は、指定した月までの累計売上を計算する式を作ってみたいと思います。

この式では、SUM関数とセル範囲参照するOFFSET関数を組合せています。ちょっと、ややこしいので、OFFSET(D4, 0, 0, 1, D6) の部分を先に見ていきます。

まず、基準セル範囲であるセルＤ４から、第２引数「行数」に指定された０行下にシフトし、第３引数「列数」に指定された０列右にシフトします。つまり、セルＤ４そのものをまず参照します。

そして、第４引数「高さ」に「１」、第５引数「幅」に指定されたセルＤ６には「３」とあるので、セルＤ４を起点とする高さ「１」幅「３」のセル範囲、つまり「Ｄ４：Ｆ４」というセル範囲を返します。

1月から3月までの各月の単月売上を含むセル範囲「Ｄ４：Ｆ４」を参照できているのであれば、OFFSET関数部分の =OFFSET(D4, 0, 0, 1, D6) だけ入力すれば良いように思われます。しかし、この数式を入力しただけだと、「３０」という１月の単月売上が結果セルに表示されるだけに終わります。

これは、OFFSETにより、セル範囲「Ｄ４：Ｆ４」への参照は出来ているものの、セル１つだけの参照ではなく、セル３つを含むセル範囲への参照ですので、実際には、{30, 80, 250} という配列が返されていると考えることが出来ます。で、結果セルには、この配列の１つめ「30」だけが表示された、ということになります。

そこでSUM関数の出番なのですが、セル範囲「Ｄ４：Ｆ４」への参照となっているOFFSET関数部分をSUM関数で合計することで、セル範囲「Ｄ４：Ｆ４」の中身である配列{30, 80, 250}に含まれる全要素を合計して、3月までの累計売上である「360」という結果を得ることができます。

この式をステップに毎に読み替えて、このような感じで理解できるかと思います。
= SUM(OFFSET(D4, 0, 0, 1, D6)) = SUM(OFFSET(D4, 0, 0, 1, 3))
= SUM(D4:F4) = SUM({30,80,250}) = 360