OFFSET関数とは

OFFSET関数は、値や範囲を参照する関数です。基準となるセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照することができます。

ざっくりいうと、「移動」と「範囲選択」ができる関数なので、単独で使うよりも、他の関数と組み合わせて使うことが効果的な関数です。

OFFSET関数の書き方

OFFSET関数は、以下のように書きます。

＝OFFSET(基準, 行数, 列数, [高さ], [幅])

それぞれの意味は以下の通りです。

• OFFSET：この位置から次の範囲を指定してください
• 基準：基準となるセル範囲の参照を指定
• 行数：基準のセルを縦にずらす数
• 列数：基準のセルを横にずらす数
• 高さ：基準のセルをずらした先の取得したい行数
• 幅：基準のセルをずらした先の取得したい行数

OFFSET関数では、基準のセル（もしくはセル範囲）から指定された行数、列数のぶんだけ移動した位置にある、指定の高さと幅のセル範囲の参照を返してくれます。

つまり、「基準位置から指定した数だけ移動したセルを起点として、指定の大きさのセル範囲を取得する」というイメージです。

ExcelでのOFFSET関数の使い方

ここからは、ExcelでのOFFSET関数の使い方をご紹介します。

以下のようなデータがあるとします。

東急東横線で移動した場合（一部の駅を抜粋）の運賃をまとめた表です。

まずは、運賃計算でOFFSET関数を使う例でご紹介していきます。

基準となるセルはB2として、そこからずらして運賃を探していきます。

まずは、渋谷駅から武蔵小杉駅に行く場合について考えてみましょう。

基準となるセルから、「渋谷」は右に1列ずれています。

また、「武蔵小杉」は下に4行ずれています。

これより、OFFSET関数では、以下のように指定することで、渋谷～武蔵小杉間の運賃を出力することが出来ます。

=OFFSET(B2,1,4)

この場合は特定の単独セルを参照するだけなので、範囲選択の引数（高さ、幅）の部分は省略して記述することができます。

また、特定のセルでなく範囲を指定することもできます。

たとえば、=OFFSET(B2,4,1,3,3)というふうに記載すると、以下のように範囲で結果を出力することができます。

これがOFFSET関数の基本的な使い方です。

ビジネスにおけるOFFSET関数の活用例

ここからはビジネスにおけるOFFSET関数の活用例をご紹介します。

MATCH関数との組み合わせ　出発地～到着地の運賃を求める

ここでも先ほどと同様の運賃表を使って考えていきます。

（分かりやすくするために、駅名の前に記載していた数字は取り除いています）

今回も、渋谷～武蔵小杉で考えてみましょう。

まず、基準のセルを「B2」として、出発地を「B3:B8」とし一致する行数を、到着地を「C2:H2」とし一致する列数を求めます。

行方向の位置はMATCH(C10,B3:B8,0)で求め、列方向の位置はMATCH(C11,C2:H2,0)で求めていきます。

※MATCH関数の使い方は以下の記事で詳しく解説しています。
MATCH関数とは　Excelでの使い方やビジネスにおける活用事例をご紹介

そうして出来上がった式は、以下の通りになります。

=OFFSET(B2,MATCH(C10,B3:B8,0),MATCH(C11,C2:H2,0))

結果、「渋谷～武蔵小杉」が227円という結果を返すことが出来ました。

MATCH関数を使うメリットとして、今回の場合でいう「C10」「C11」の部分を変えれば、自動で出力結果も変わってくれるという点です。

例えば、出発「中目黒」、到着「横浜」に変えてみます。

そうすると、式はそのままで以下のように結果が変わりました。

出発地と到着地を入力するだけで結果が分かるので、すごく便利ですね。

この使い方は、運賃の計算だけでなく、たとえば「社員番号」と「アンケート回答の結果」といったように様々な活用方法ができます。

SUM関数との組み合わせ　契約件数の合計

ここからはSUM関数と組み合わせた活用例をご紹介します。

以下は、とある会社の営業社員の契約件数の架空データです。

3位までの契約件数を合計する場合を考えます。

まず、基準セルをD3にし、行数、列数は0にします。

続いて高さをF3に、幅を1にします。

※今回の場合、F3のセルに合計するまでの順位の値を入れています。

出来上がった式は、以下の通りです。

=SUM(OFFSET(D3,0,0,F3,1))

そうすると、以下のように結果が出力されます。

順位を入れているF3のセルの数字を「5」に変えてみると、5位までの合計が求められるように自動で結果が変わります。

このように自動で結果が変わるようにできる点がOFFSET関数とSUM関数を組み合わせるメリットです。

一度作成しておけば、簡単に順位までの合計を求められるので大変便利ですね。

契約件数だけでなく、商品ごとの売上データなど、様々な場面で活用することが出来ます。

まとめ

OFFSET関数について、概要と活用方法をご紹介しました。

OFFSET関数は少しマニアックな関数ですが、SUM関数やMATCH関数など、他の関数と組み合わせて活用出来ると大変便利な関数の一つです。

是非この機会に覚えておきましょう。

＼ この記事を読んでいる人におすすめ！ ／