Shimesaba-ba blog

大手日系企業で経営管理や経理業務を行い、英語や海外とは無縁な状態から海外で業務を行い、グローバル企業へ転職。英語、経営管理、ビジネスについての経験や学んだことをアウトプットします。

OFFSET関数の基本。基準セルから動かして指定した範囲を可変的に足し算させる方法

おはようございます。

 

会社の同僚がエクセルの

OFFSET関数を良く使っていました。

 

実務でも使う場面を想定した

関数の組み合わせや、

可変的な仕組みの1つも同時に

ご紹介したいと思います。

 

Excelの学習は自分の個別の悩みを

ドンピシャで解決してくれる

答えが中々ネットにはないですよね。

 

結局はその調べたことを

ヒントに各引数を自分の思い通りに

可変的にアレンジできるかかが重要であり

そこが難しい所ではないでしょうか?

 

そのために実務で使う例を示して

少しでも皆さんのやりたいことに

繋がるヒントになればと

思っています。

 

f:id:Shimesaba-ba:20201108102738p:plain

 

そしてエクセル学習は

見るより触ってなんぼ

というポリシーのもと

無料でファイル提供します。

drive.google.com

 

 

OFFSET関数とは?

ある基準セルを起点に行と列をずらし、

特定のセルを探します。そのセルから

特定の範囲を選択して返します。

範囲が複数セル(複数行または複数列または両方複数)

選択になる場合はSUM関数で

その範囲の合計値を求めること等に使います。

 

基本

OFFSET関数の構成は5つの引数で

構成されます。

 

OFFSET(基準行数、列数、高さ

 

引数の中身解説

このようなテーブルがあったとします。

f:id:Shimesaba-ba:20201108093837p:plain


右の集計エリアで

7月の売上を抽出したいとします。

f:id:Shimesaba-ba:20201108094128p:plain

 

先に答えを書くとこのようになります。

f:id:Shimesaba-ba:20201108095221p:plain


基準

起点となるセル。この場合例えばA2とします。

 

行数

A2から何行下がりますか?

この場合は1行です。つまりA3

ポイントは基準セルを0とし次のセルから1と

カウントすることです。

 

列数

今度はセルA3から何列右にずれますか?

この場合4列です。つまりE3になります。

ポイントは同じく起点とするセルを0とし

次のセルから1とカウントすることです。

 

ここで元の基準セルA2から

移動先のセルの指定が終わりました。

ここから選択したい範囲を指定します

 

高さ

今度はE3を起点として

下に何行分選択しますか?

ここでは1行分とします。E3です。

ポイントは起点とするセルを1として

カウントすることです。次のセルは2に

なります。行数や列数指定時と異なります。

 

今度はE3を起点として

右に何列分選択しますか?

ここでは1列分です。E3です。

ポイントは起点とするセルを1として

カウントすることです。次のセルは2に

なります。行数や列数指定時と異なります。

 

ご覧の通りこの例なら

これならOFFSET関数なんて不要で

普通にVLOOKUP関数で

取ればいいじゃんとなるわけです。

 

では例えば7月累計の値も取りたいという場合

どうでしょうか?

VLOOKUPだけではできません。

 

OFFSET関数の威力が出るのはここからです。

SUM関数との組合せ

下記のように累計額も求めたいとします。

 

その時には、

OFFSET関数で選択範囲を決めて

その範囲を合計するSUM関数を

くっつけます。

f:id:Shimesaba-ba:20201108095433p:plain

 

先ほどの7月単月の時と

異なるのは幅の設定が

1ではなく4になっている点です。

 

幅が4というのは4月から7月の

複数セルを返してねという

指示になります。

f:id:Shimesaba-ba:20201108095741p:plain

 

こうするとOFFSET関数で

範囲が選択されますので、

その範囲を合計するために

頭にSUM関数を入れます。

 

しかし、ここで問題です。

7月は4列分と指定しましたが

毎月変わるたびに式を修正するのは

エラーの元です。

 

なので

この幅の選択を可変にしたいですよね?

例えば6月累計なら3列分

となるようにしたい。

 

その時には4と手入力している部分を

VLOOKUP関数に置き換えて可変にします。

 

VLOOKUP関数との組合せで可変

VLOOKUPを使うために

下準備として脇の方に下記テーブルを

用意します。

 

f:id:Shimesaba-ba:20201108100244p:plain

そのうえで幅の指定を

手入力からVLOOKUP関数に置き換えます。

下記オレンジ色部分です。

 

変更前

f:id:Shimesaba-ba:20201108100438p:plain

 

 変更後

f:id:Shimesaba-ba:20201108100605p:plain

 

こうすることで月を選択すれば

その単月及び累計額を自由自在に

集計できます。例えば6月を選択すると

下記のようになります。

f:id:Shimesaba-ba:20201108100800p:plain

 

OFFSET関数で幅を指定できる特徴を生かして

わざわざ累計の計算を別でしなくても

累計額を可変的に返してくれるように

なりました。

 

まとめ

OFFSET関数はある基準セルから下と横に

動かして新たな起点を指定する。

その起点セルを中心に

下と横に範囲を指定し返してくれます。

 

範囲が複数セルの場合は

そのままだとエラー値が

返ってしまいますので、

 

SUM関数などを使って

複数セルを返すために

他の関数と一緒に使います。

 

可変にするために

今回はさらにVLOOKUP関数を

使って月を選択するだけで

その月の単月と累計の集計を自動化

させてみました。

 

エクセル学習、特に関数の使い方は

数学と少し似ているのではないかと感じます。

 

数学は猛烈に苦手なのであまり変なことは

言えませんが、

 

ただ公式やルールを覚えるのではなく、

その公式の本質が何かを理解し、

それを応用して、ロジックを頭で描いて

使いこなす作業というのは

どこか数学と近しいものを感じています。