Excel。OFFSET関数を使って関数の範囲を自動的に調整してくれる方法
OFFSET関数
概ね計算結果は、データの範囲の下に求めたりすることが多いですが、場合によっては、違う所に算出することもあったります。
その時に、SUM関数などの範囲が、一定ならいいのですが、前回はA5:A8だけど、
今回はA5:A88とか範囲がバラバラの時、
イチイチ数式の範囲を変更しているのは面倒ですよね。
関数の範囲がおおむねこの範囲とかでしたら、名前の定義を使うと楽かもしれませんが、
OFFSET関数を使うというテクニックもオススメなんですよ。
ということで、AVERAGE関数を使って、
その範囲を自動的に調整してくれるようにする方法をご紹介していきます。
下記のような表があります。
A列のテスト結果の平均を求める場合は、A3:A9という範囲ですが、
仮にこのデータが、C列のように件数が増えたとしたら、
当然範囲は、A3:A17と範囲が変わるわけですね。
これをデータの都度修正していくのが面倒なので、
範囲を自動的にコントロールしてくれないかな？というのが今回のやりたい事なのです。
今回登場する関数は、AVERAGE関数 OFFSET関数そして、COUNT関数です。
では、F3をクリックして、AVERAGE関数ダイアログボックスを表示しましょう。
なかなか、AVERAGE関数ダイアログボックスってみませんよね。
さて、範囲1には、OFFSET関数をネストしていきますので、
OFFSET関数ダイアログボックスを表示しましょう。
引数が多くて、ちょっとビックリですが慌てずに作っていきましょう。
参照ですが、これは、範囲のスタート地点ということになりますので、A3
行数は、その行から移動する行数ですが、移動しませんので、0（ゼロ）
列数も、その列から移動する列数も、移動しませんので、0（ゼロ）
高さは、このデータの範囲になりますから、このデータを数えてあげますから、
このボックスには、数値を数える関数であるCOUNT関数をネストしていきます。
COUNT関数ダイアログボックスを表示していきます。
値1は、データの範囲がわかりませんので、A:Aと入力します。
これは、A列という意味になります。
では、OFFSET関数に戻りましょう。
幅は、省略できますので、空欄のままで大丈夫です。
それでは、OKボタンをクリックしましょう。
F3の数式は、
=AVERAGE(OFFSET(A3,0,0,COUNT(A:A)))
となっています。
では、A10:A17に、C列のデータをコピーして、F6と同じ結果になるか確認してみます。
貼り付けてみましょう。
F6の値とF3の値が同じになりましたよね。つまり、データが増えましたが、
自動的に範囲選択が拡張されたわけですね。
OFFSET関数を使うとこのようなことができますので、結構役に立つスキルのひとつですね。
