おはようございます。
会社の同僚がエクセルの
OFFSET関数を良く使っていました。
実務でも使う場面を想定した
関数の組み合わせや、
可変的な仕組みの１つも同時に
ご紹介したいと思います。
Excelの学習は自分の個別の悩みを
ドンピシャで解決してくれる
答えが中々ネットにはないですよね。
結局はその調べたことを
ヒントに各引数を自分の思い通りに
可変的にアレンジできるかかが重要であり
そこが難しい所ではないでしょうか？
そのために実務で使う例を示して
少しでも皆さんのやりたいことに
繋がるヒントになればと
思っています。
そしてエクセル学習は
見るより触ってなんぼ
というポリシーのもと
無料でファイル提供します。
OFFSET関数とは？
ある基準セルを起点に行と列をずらし、
特定のセルを探します。そのセルから
特定の範囲を選択して返します。
範囲が複数セル（複数行または複数列または両方複数）
選択になる場合はSUM関数で
その範囲の合計値を求めること等に使います。
基本
OFFSET関数の構成は5つの引数で
構成されます。
OFFSET（基準、行数、列数、高さ、幅）
引数の中身解説
このようなテーブルがあったとします。
右の集計エリアで
7月の売上を抽出したいとします。
先に答えを書くとこのようになります。
基準
起点となるセル。この場合例えばA2とします。
行数
A2から何行下がりますか？
この場合は１行です。つまりA3
ポイントは基準セルを０とし次のセルから１と
カウントすることです。
列数
今度はセルA3から何列右にずれますか？
この場合4列です。つまりE3になります。
ポイントは同じく起点とするセルを０とし
次のセルから１とカウントすることです。
ここで元の基準セルA2から
移動先のセルの指定が終わりました。
ここから選択したい範囲を指定します。
高さ
今度はE3を起点として
下に何行分選択しますか？
ここでは1行分とします。E3です。
ポイントは起点とするセルを１として
カウントすることです。次のセルは２に
なります。行数や列数指定時と異なります。
幅
今度はE3を起点として
右に何列分選択しますか？
ここでは1列分です。E3です。
ポイントは起点とするセルを１として
カウントすることです。次のセルは２に
なります。行数や列数指定時と異なります。
ご覧の通りこの例なら
これならOFFSET関数なんて不要で
普通にVLOOKUP関数で
取ればいいじゃんとなるわけです。
では例えば7月累計の値も取りたいという場合
どうでしょうか？
VLOOKUPだけではできません。
OFFSET関数の威力が出るのはここからです。
SUM関数との組合せ
下記のように累計額も求めたいとします。
その時には、
OFFSET関数で選択範囲を決めて
その範囲を合計するSUM関数を
くっつけます。
先ほどの７月単月の時と
異なるのは幅の設定が
１ではなく４になっている点です。
幅が4というのは4月から7月の
複数セルを返してねという
指示になります。
こうするとOFFSET関数で
範囲が選択されますので、
その範囲を合計するために
頭にSUM関数を入れます。
しかし、ここで問題です。
7月は4列分と指定しましたが
毎月変わるたびに式を修正するのは
エラーの元です。
なので
この幅の選択を可変にしたいですよね？
例えば6月累計なら3列分
となるようにしたい。
その時には４と手入力している部分を
VLOOKUP関数に置き換えて可変にします。
VLOOKUP関数との組合せで可変
VLOOKUPを使うために
下準備として脇の方に下記テーブルを
用意します。
そのうえで幅の指定を
手入力からVLOOKUP関数に置き換えます。
下記オレンジ色部分です。
変更前
変更後
こうすることで月を選択すれば
その単月及び累計額を自由自在に
集計できます。例えば6月を選択すると
下記のようになります。
OFFSET関数で幅を指定できる特徴を生かして
わざわざ累計の計算を別でしなくても
累計額を可変的に返してくれるように
なりました。
まとめ
OFFSET関数はある基準セルから下と横に
動かして新たな起点を指定する。
その起点セルを中心に
下と横に範囲を指定し返してくれます。
範囲が複数セルの場合は
そのままだとエラー値が
返ってしまいますので、
SUM関数などを使って
複数セルを返すために
他の関数と一緒に使います。
可変にするために
今回はさらにVLOOKUP関数を
使って月を選択するだけで
その月の単月と累計の集計を自動化
させてみました。
エクセル学習、特に関数の使い方は
数学と少し似ているのではないかと感じます。
数学は猛烈に苦手なのであまり変なことは
言えませんが、
ただ公式やルールを覚えるのではなく、
その公式の本質が何かを理解し、
それを応用して、ロジックを頭で描いて
使いこなす作業というのは
どこか数学と近しいものを感じています。
