1. 入力に応じて計算対象の範囲を指定したい
テストの得点は、下方向へ入力していく。この入力に応じて「平均」を計算するための「範囲」が変化し、計算が行われるようにしたい。
平均を計算するとき、以下のように列まるごと対象にした式を書くことはできる。
=AVERAGE(A:A)
ここでは、列全体を AVERAGE 関数に与えるのではなく、入力された範囲を引数に与えるようにしたい。
2. COUNT 関数でデータ数を得る
最初に、データ数をカウントするための
- COUNT 関数
について確認する。Excel のヘルプによると、
COUNT 関数では、数値、日付、数値を表す文字列が計算の対象となります。エラー値、数値に変換できない文字列は無視されます。
=COUNT(A:A)
このとき、先頭の見出しの文字列はカウントされない。
D2 セルに上記の式を記述し、そのセルを「データ数」と名付けた。
3. OFFSET 関数で計算対象の「範囲」を得る
次に、計算対象の「範囲」を取得する。そのためには、OFFSET 関数を利用する。
OFFSET の意味は、オフセットとは 【offset】 - 意味・解説 ： IT用語辞典 によると、
あるデータの位置を、基準点からの差(距離)で表した値のこと。
この意味からは、範囲を返す関数であることは分からない。関数の名前が良くないと思う。
Excel のヘルプによると、
基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。
使い方は 「[XL2002] OFFSET 関数の使用方法」 を参照。
OFFSET 関数の引数をイメージしやすいように図にしておく。
表は、基本的に「行 → 列」という順序で考えるのが普通。よって、引数の並びとして、「行数」「列数」が並んでいるので覚えやすい。「高さ」「幅」というのは、言い換えれば、「対象となる行のデータ」と「対象となる列のデータ」ということ。これも「行 → 列」という順序に並んでいると見なせる。
今回は OFFSET の意味に相当する引数「行数、列数」は考えない。必要なのは、基準となるセルとそこからの範囲指定を行うための「高さ」と「幅」。
OFFSET 関数を適用する前に、セル A2 を「データの先頭」と名付けた。平均を計算するため、セル D3 に次のように記述。
=AVERAGE(OFFSET(データの先頭,0,0,データ数,1))
セルに名前を付けず、途中の計算も書かないで書くなら、
=AVERAGE(OFFSET(A2,0,0,COUNT(A:A),1))
となる。
0コメント:
コメントを投稿