【Excel】OFFSET関数の使い方｜範囲を動的に指定して集計・平均も自由自在！

みなさんは、ExcelのOFFSET関数をご存じでしょうか？

「仕組みが分かりにくい」「どう活用したらいいかイメージがつかない」と、最初はとっつきにくいと感じる方も多い関数だと思います。
しかし、OFFSET関数を活用することで、最新データの自動集計やグラフ作成などを実現でき、作業効率を大幅にアップすることができます。

本記事では、OFFSET関数の基本的な使い方から、SUM・COUNTAなど他の関数と組み合わせる応用テクニックまで図解で分かりやすく解説します。。

🔍今回の記事で分かること

Excel初心者でも迷わない！OFFSET関数の基本の使い方と他関数との組み合わせ方法を紹介
どういうときに便利？OFFSET関数のメリット
実務でも使える方法を紹介！グラフを自動で更新する方法/条件分岐で集計範囲を自由に変更する方法

INDEX

OFFSET関数って何？
1. OFFSET関数の書式
2. OFFSET関数の注意点・エラー対処法
OFFSET関数を使うメリットとは？
OFFSET関数の基本的な使い方
OFFSET関数の活用例（実務+応用編）
1. OFFSET関数で「直近7日間」をグラフ化する方法
2. COLUMN関数×OFFSET関数で「直近○ヶ月分のデータ」を自動集計する方法
まとめ：OFFSET関数を使いこなせば、大幅な業務効率化が可能に！

OFFSET関数って何？

OFFSET関数は、エクセルで指定したセルを基準にして、行数や列数を指定してその先のセルを参照する関数です。

例えば、あるセルから数行下や数列右にあるセルを動的に参照することができます。
データの範囲が変わる場合や、データが追加される場合でも、OFFSET関数を使うことで柔軟に対応できます。

OFFSET関数の書式

OFFSET関数の引数は以下の通りとなっています。

=OFFSET（参照セル,行数,列数,[高さ],[幅]）

それぞれの説明は以下の通りです。

引数	説明
参照セル	基準となるセル
行数	参照セルから何行移動するかを指定
列数	参照セルから何列移動するかを指定
高さ（省略可能）	取得する範囲の行数　※デフォルトは1行
幅（省略可能）	取得する範囲の列数　※デフォルトは1列

これだけだとイメージしにくいので、実際にOFFSET関数を使って説明します。

たとえば、

=OFFSET（A1,2,3）

と入力した場合の結果を見ていきます。

この場合、
・A1セルから、2行下→3列右に移動
することになります。

図解で表すと、以下の通りです。
OFFSET関数を入力しているセルB5に、「ゴール！」を表示していることが確認できます。

このように、ある基準点から行・列分移動した先にある値を返してくれる関数であることはご理解いただけたと思います。

💡ポイント

行・列を動かしたくないという場合は、「空白」もしくは「0」を入力します
行で上方向、列で左方向に移動させたい場合、「-1」などマイナス値を指定します

OFFSET関数の注意点・エラー対処法

上記のとおり、OFFSET関数は基準点から指定した行・列分移動するという関数です。
便利な関数ですが、参照範囲がずれるというエラーが発生しがちな関数でもあります。

よくあるのが、以下2つのエラーです。

エラー内容	原因	対処法
#REF!	範囲外に参照してしまっている	行数・列数を見直す
値がずれる	行・列分の指定が合っていない	基準セルとずらし方を再確認

※範囲外の参照は、「マイナス値」を入力したときに発生しやすいエラーとなります。
・行をマイナス値で指定　→　基準値より上の行に移動
・列をマイナス値で指定　→　基準値より左の列に移動

値がずれるのは単純に移動値の指定ミスが多いので、指定する行数・列数を見直せば解消するケースがほとんどです。後述する他関数との組み合わせでグッと減らせるエラーになると思います。

OFFSET関数を使うメリットとは？

その前に、OFFSET関数ってどのような使い方ができるの？と思いますよね。
実はOFFSET関数は様々な場面で活用できる、汎用性の高い関数です！

実際にどのようなメリットがあるか、紹介していきます。

セル範囲を動的に扱うことができる！

OFFSET関数のメリットは、セル範囲を動的に扱うことができることです！

どういうことかというと、例えば行数にセルA1を入力した場合、
・セルA1が「3」であれば3行目
・セルA1を「5」に打ち換えたら5行目
といった感じで、参照先を変えることができるということです。

複数セルの範囲を簡単に指定できる！

OFFSET関数は、単一のセルだけでなく、複数セルを参照することができます！

OFFSET関数の引数に、「高さ」と「幅」があり、
・高さ = 行数
・幅 =　列数
として、参照範囲から指定した行数分・列数分の範囲が指定できるようになっています。

他の関数との組み合わせで、さらに強力な使い方が可能に！

OFFSET関数だけでも便利なのですが、他の関数と組み合わせると更に強力に！

OFFSET関数はセル範囲を動的に扱えるため、
・参照範囲の計算ができる
・データ件数が増減しても、自動で参照範囲を変えてくれる
・データが増えても、必ず直近7日間の平均を求めるなど、対象を自動で変えてくれる
など、非常に汎用性の高い、強力なツールとなります。

OFFSET関数の基本的な使い方

では、OFFSET関数の基本的な使い方を解説していきます。

こちらでは、
・単一のセルを参照する場合
・複数のセルを参照する場合
・SUMやCOUNTA関数と組み合わせた基本パターン
を紹介します。

単一のセルを参照する場合

まずは、単一のセルを参照するケースです。
今回は、セルD6からセルH8に移動するケースを図解で説明します。

この場合、セルH8に移動するには、
・行→下に2行
・列→右に4列
移動する必要がありますので、以下のように入力します。

=OFFSET(D6,2,4）
※今回は行数・列数を表示しているため、=OFFSET(D6,C8,H5）でも同じ結果を反映できます。

これで、セルH8の値を表示することができます。

複数のセルを参照する場合

次に、複数のセルを参照するケースです。
今回は、セルD6からセルH8～I9の範囲に移動するケースを図解で説明します。

この場合、まずセルH8に移動する必要がありますが、それだけでは一つのセルしか参照できません。

そこで、セルH8から2行分×2列分の範囲を取得する必要があるため、
・高さ→2
・幅→2
と指定します。

=OFFSET(D6,2,4,2,2）
※今回は行数・列数を表示しているため、=OFFSET(D6,C8,H5,2,2）でも同じ結果を反映できます。

これで、セルH8から、2行×2列の範囲を表示することができます。

SUMやCOUNTA関数と組み合わせた基本パターン

OFFSET関数は動的な範囲を選択できるので、他の関数との組み合わせることにより、さらに便利な使い方ができます。

こちらでは、
・SUM関数と組み合わせて、参照範囲の合計を計算する
・COUNTA関数と組み合わせて、参照範囲を自動で設定する
方法を紹介します。

SUM関数と組み合わせて、参照範囲の合計を計算する

SUM関数と組み合わせることで、OFFSET関数で参照した範囲を足し算することができます。
複数のセルを参照する場合と同じ条件で、今度はセル範囲を足し算するケースを図解で説明します。

組み合わせ方はシンプルで、OFFSET部分をSUM関数で囲うことにより足し算できます。

=SUM(OFFSET(D6,2,4,2,2))
※表示している行数・列数を参照しており、=SUM(OFFSET(D6,C8,H5,2,2))でも同じ結果になります。

これで、セルH8～I9の範囲を足し算することができます。

COUNTA関数と組み合わせて、参照範囲を自動で設定する

COUNTA関数と組み合わせることで、参照範囲を自動で変更することができます。
データの追加などがあった場合に活躍する組み合わせです。同じように図解で説明します。

例えば、下図のように対象範囲の値の合計を算出するには、SUM関数で計算しますよね。

ここにデータを追加してみるとどうでしょう。
データを追加しても、SUMの参照範囲は変わらないため、11行目は計算結果に含まれません。
計算範囲を手動で変更する必要があるため、この方法では不便ですよね。

そこで、データが追加されても自動で計算できるように、OFFSETとCOUNTA関数を組み合わせます。
今回はB1から最下行までを足し算するため、セルB1から最下行の行数を取得する必要があります。
その行数を取得するため、「高さ」をCOUNTA関数で指定することで、結果的に「=SUM(B1:B10）」と同じ結果を出すことができます。

=SUM(OFFSET(B1,0,0,COUNTA(B:B),1))

本題として、この方法で追加されたデータ分も計算されるかを確認していきます。
同じく11行目にデータを追加します。

COUNTA関数が最下行数（=11）を計算したため、結果的に「=SUM(B1:B11）」と同じ結果となり、自動で計算範囲が変更されました。

OFFSET関数の活用例（実務+応用編）

OFFSET関数の基本的な使い方を紹介してきましたが、ここからは実務でも役立てられる活用テクニックを紹介していきます。私も実務で使っているテクニックとなります。

ロジックも含めて細かく紹介していきますので、少し長い内容になってしまいましたがぜひご参考ください。

OFFSET関数で「直近7日間」をグラフ化する方法

OFFSET関数を使うことで、手作業で範囲変更しなくても、グラフの参照範囲を変更することができます。

今回は、ホームページのアクセス状況をグラフで確認したいとして、「直近7日間のアクセス推移」をグラフで作成するというシミュレーションをやっていきます。

OFFSETで直近7日間だけを参照する

基本的な使い方でも紹介したCOUNTA関数と組み合わせることで、常に直近7日分のデータを範囲指定することができます。

例えば、以下の表部分に直近7日間のアクセス数を表示したいとします。

この場合、数式は以下の通りに入力します。

=OFFSET(B2,COUNTA(B:B)-8,0,7,1)

これで、直近7日間のアクセス数を表示することができました。

名前の定義で動的範囲を設定する

ではこちらをどのようにグラフに反映していくかを説明していきます。

上記の方法では、直近7日分のアクセス数が表示されますので、このままグラフの範囲としても大丈夫です。ですが、「名前を定義」することで、セルに表示させなくてもグラフを作ることが可能です。

1.「数式」タブより、「名前の定義」を選択

2.「名前」に付けたい名前（今回は「直近7日間」としました）を入力し、「参照範囲」に以下の数式を入力

=OFFSET(Sheet!$B$2,COUNTA(Sheet!$B:$B)-8,0,7,1)

これでグラフに反映するための数式に名前を登録することができました。
あとはこれを使い、グラフを作成していきます。

グラフを作成する

最後に、自動で範囲変更するグラフを作っていきます。
今回は、「マーカー付き折れ線グラフ」で作ってみたいと思います。
（グラフの種類はお好みで選択してください）

1.使用したいグラフを選択

2.グラフ部分で右クリックし、「データの選択」を選択

3.「凡例項目（系列）の「追加」を選択

💡ポイント

「グラフデータの範囲」で入力すると、範囲が自動変換されてしまうため、必ず「系列」で入力してください。

4.「系列値」に、シート名と先ほど登録した名前を入力し、「OK」を選択

=Sheet!直近7日間

5.「OK」を選択

これで、グラフを作ることができました。
しかし、これでは横軸が何なのか分からないですよね。

そこで、先ほどと同じ手順で「名前の定義」を行うことにより、横軸ラベルも自動的に範囲が変更されて表示されます。
「名前の定義」は先ほどと同じ通りで作成できますので、グラフへの反映方法を紹介していきます。

6.「横（項目）軸ラベル」の編集」を選択

7.「軸ラベルの範囲」に、シート名と登録した名前を入力し、「OK」を選択

今回は「直近7日間_横軸」という名前で、数式は以下のように登録しました。

=OFFSET(Sheet!$A$2,COUNTA(Sheet!$A:$A)-8,0,7,1)

これで、横軸に日付が表示され、分かりやすいグラフを作ることができました。

では、データを追加した場合、ちゃんとグラフが更新されるのでしょうか。
実際にデータを追加して検証しました。

下のように、バッチリ更新されました！

このように、自動で更新されるグラフが作られるのは便利ですよね。
ぜひ参考にして、業務に活用してみてください。

ちなみにグラフをコピーするときに書式が勝手に変わってしまうことがあります。
こちらの記事で解説しています。

COLUMN関数×OFFSET関数で「直近○ヶ月分のデータ」を自動集計する方法

OFFSET×COLUMN関数を組み合わせることで、一見複雑そうな計算も簡単にすることができます。

今回は、営業計画でもよくあるような売上シミュレーションをやってみたいと思います。
・1年間の新規契約で積みあがった利用料の売上を計算（新規契約は毎月増える想定）
・ただし、契約月から6か月は利用料の50%を割引
・その割引を差し引いた年間売上を計算

この条件だと、単純に通常単価×件数の合計ではなく、新規契約から6か月分は割引、7か月目からは通常単価ということを考慮しないといけません。

こちらを計算するために、以下内容で考えていきます。
・Column関数で列番号を取得する（＝月数のイメージで利用する）
・IF関数で、新規契約から5か月以内か、6か月以上かを判別する
・OFFSET関数で、5か月以内であれば1～5か月分足し算、6か月以上であれば6か月分を常に足し算

上記ロジックを、以下青枠部分に反映していきます。

COLUMN関数で列番号を取得する

月数を指定するために、まずはCOLUMN関数を使います。

COLUMN関数は、指定されたセルの列番号を返します。
「=COLUMN(C1)」であれば3など、列を数値に置き換える関数となります。

=COLUMN([範囲])

※「範囲」を指定しない場合、column関数が入力されているセルの列番号を取得します。

以下の表だと、2列目より月数を計算しているため、実際の列から1を引くことで月数を表すことができます。

=COLUMN()-1

※R1C1形式だと、何列目かが分かりやすくなります。
興味のある方は、こちらの記事をチェックしてみてください。

IF関数とOFFSET関数を組み合わせて計算を切り替える

次にIF関数を使って、新規契約から5か月以内か、6か月以上かを判別していきます。
以下の図だと、F列目までが5か月以内、G列目からが6か月以上で計算する必要があります。

そのため、IF関数で
・「COLUMN()-1 <= 5」であれば、1か月〜5か月分を足し算する
・そうでなければ、直近6か月分を足し算する
と条件分岐し、範囲をOFFSET関数で指定、その範囲を合計するという流れとなります。

実際の計算例（12か月分 vs 累計合計）

では、実際に計算してみましょう。

OFFSET関数では、
・5か月以内であれば、1列〜5列分の範囲を選択
・6か月以上であれば、直近の6列分の範囲を選択
ができるように設定していきます。

こちらを計算するには、以下の数式を入力します。

=IF(COLUMN()<=5,SUM(OFFSET($B9,0,0,1,COLUMN()-1)),SUM(OFFSET($B9,0,COLUMN()-7,1,6)))

・SUM(OFFSET($B9,0,0,1,COLUMN()-1))
→セルB9から一つ前までのセルを範囲指定して足し算する。
※セルE4では、SUM(B9:E9）と同じ）
・SUM(OFFSET($B9,0,COLUMN()-7,1,6))
→セルB9から一つ前のセルまで移動し、そこから6列分範囲指定して足し算する
※セルL9では、SUM(G9:L9)と同じ

というため、1～5か月と直近6か月を分けて計算することができます。