OFFSET関数でベスト5の売上件数を求めよ

OFFSET関数は合計範囲を変更できる

OFFSET関数の使い方
書式 :OFFSET(基準,行数,列数,高さ,幅)
機能 :OFFSET関数は、基準のセルあるいは、セル範囲から
指定した行数・列数分をシフトさせた位置にある高さと幅の
セルやセル範囲のオフセット参照を返します。

簡単にいえば、合計範囲を自由に変更できるということ。
(サンプルファイルは、こちらから 関数技10回サンプルデータ

合計範囲を自由に変える「OFFSET関数」

【問題】ベスト5の売上件数を求めよ。
全員の売上合計を出したいときは、SUM関数を使えばいいですね。
しかし、Xが一定ではなく、上位3位までの合計とか、5位までの合計を出したいというように、Xが一定ではなく、合計するセルの範囲を可変にしたいときは、SUM関数のネストに検索・行列関数の OFFSET(オフセット)関数を使います。

書式
OFFSET(基準, 行数, 列数, 高さ, 幅)
■ある一つのセルを基準として、引数で指定した行数、列数を移動した位置を
始点として、引数で指定した高さ、幅のセル範囲を求める関数です。
引数には「基点、移動行数、移動列数、高さ、幅」を指定します。

OFFSET関数で合計範囲を変更する

 OFFSET(基準, 行数, 列数, 高さ, 幅)  で範囲を指定します。
 「行数」と「列数」を指定することは少ないです。
 「基準」と「高さ」「幅」を覚えておきましょう。
OFFSET関数を使って1位から5位まで5人分の件数を合計しましょう。

 OFFSET(C2,,,5,1)
 C2が「基準」になります。
 そして「高さ」に5、「幅」に1を指定。
 
 つまり、C2セルを基準として高さ(行)が5、幅(列)が1のセル範囲を指定したことになります。これは、C2:C6セル範囲のことです。
 
 では、6位までの範囲は、 OFFSET(C2,,,6,1)
 では、7位までの範囲は、 OFFSET(C2,,,7,1)
 では、8位までの範囲は、 OFFSET(C2,,,8,1)
 
 というようになりますね。
 
では、A13セルに高さ(行)を入力して参照するようにしましょう。
スポンサーリンク
スポンサーリンク

ポイント
 ここで注目したいのは、「高さ」を変更することでセル範囲の行数を変えられるということです。
 
 他のセルに行数に該当する数値を入力し、引数「高さ」でそのセルを参照すれば、セルの数値を変えるだけで合計範囲を変更できるようになります。
 
A13セルに入れる数字を変えることで、合計範囲を変更できます。

式は、見やすくするために、0を省略していますが、
どちらで入力しても結果は同じです。

OFFSET(C2,,,5,1)=OFFSET(C2,0,0,5,1)
OFFSET関数の式の意味を図にしました。
A1を基準にして、行数、列数が0ということは、始点はA1です。
次の例では、同じく基準はA1desuga,下に0行、1列移動したセルが始点となります。
スポンサーリンク
スポンサーリンク