トップページ > Excel エクセル関数 > OFFSET関数 | 基準のセル・セル範囲からシフトしたセル・セル範囲の参照を返します

OFFSET関数

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


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

(省略可)
オフセット参照の列数を指定します。
※省略すると、基準セル範囲と同じ列数であると見なされます。

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

OFFSET関数 その1

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

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

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

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

OFFSET関数 その2

=SUM(OFFSET(D4, 0, 0, 1, D6))
説明 その1では、単月の売上金額を表示する式を作りましたが、今度は、指定した月までの累計売上を計算する式を作ってみたいと思います。

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

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

そして、第4引数「高さ」に「1」、第5引数「」に指定されたセルD6には「3」とあるので、セルD4を起点とする高さ「1」幅「3」のセル範囲、つまり「D4:F4」というセル範囲を返します。

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

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

そこでSUM関数の出番なのですが、セル範囲「D4:F4」への参照となっているOFFSET関数部分をSUM関数で合計することで、セル範囲「D4:F4」の中身である配列{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

Tagsカテゴリー:

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

トラックバックURL

http://excel.jiten.org/function/offset/trackback/