【Excel】OFFSET関数を使って集計する範囲を指定する

そう頻繁に使う関数ではないかもしれませんが、とても便利だったので備忘録としてまとめ。
Excel の OFFSET 関数という、集計する範囲を指定できる関数です。

使用する際に参考にさせていただいた記事はこちら。

OFFSET関数の便利な使い方 | ノンプログラミングWebアプリ作成ツール – Forguncy(フォーガンシー)| グレープシティ株式会社
https://www.forguncy.com/blog/20170130_offset

 

具体的な使用場面ですが、私の場合は、アンケートの回答データを日ごとに集計したいときに使用しました。
日ごとの回答数はあったのですが、アンケート回答データに回答日時が保存されていなかったので、OFFSET 関数を利用して回答データから範囲を指定して集計しました。

さて、関数の使用方法ですが、まず書式は下記のとおりです。

=OFFSET(基準となるセル, 基準セルからいくつ縦にずれるか, 基準セルからいくつ横にずれるか, 範囲の行数, 範囲の列数)

基準セルは、基本的には集計したいデータの一番最初のセルで問題ないと思います。
で、第2、3引数で、基準セルからいくつズレるかを指定し、第4、5引数で、集計範囲を指定します。

たとえば、日ごとの回答数ごとにデータを集計したい場合、まず隣の列に作業セルとして、その日時点までの合計値を追加します。

上記のスクリーンショットでは、C列 にあたります。
こちらが、基準セルからいくつ縦にずれるか、を指定するときに使用する値になります。

あとは、この値と、日ごとの回答数を使って、式を作成します。

=COUNTIF(OFFSET([基準セル], $C1, 0, $B2, 1), [カウントしたい値])

こんな感じです!
今回は、集計したいデータが 1列だったので、横のずれは指定していません。
ちなみに、絶対列参照を使っているので、横にコピーしても参照位置は横には移動しません(縦には移動します)。
なお、基準セルも絶対参照にすることをおすすめします。

使い方としては以上です。
最初、この関数を知らなかったので、どちらかというと OFFSET 関数を探す方が手間取りましたね。
なお、OFFSET 関数が返すのは「範囲」なので、OFFSET 関数単体で使用すると #VALUE エラーになりますので、ご注意ください。

 

以上、Excel の OFFSET 関数を使って集計する範囲を指定する方法でした。
ご参考になれば幸いです。

>株式会社シーポイントラボ

株式会社シーポイントラボ

TEL:053-543-9889
営業時間:9:00~18:00(月〜金)
住所:〒432-8003
   静岡県浜松市中央区和地山3-1-7
   浜松イノベーションキューブ 315
※ご来社の際はインターホンで「316」をお呼びください

CTR IMG