▲ 平均スコアの計算式
§ セルF1
- 入力：
=AverageIf(B5:B1000,E1,F5:F1000)
- 解説： セルB5〜B1000（開催年）の値が、セルE1 に等しいセルF5〜F1000（Out）の平均値。
- 補正後： “=AVERAGEIF($B$5:$B$1000,$E1,F$5:F$1000)”
- セルB5〜B1000 は、式のコピー方向（下、右）を問わず固定。
- セルE1 は、
下にコピーした時は E2 に、右にコピーした時は E1 なので、列のみ固定する。 - セルF5〜F1000 は、下にコピーしても F5〜F1000、右にコピーした時は G5〜G1000 なので、行のみ固定する。
ヒント
- この計算式の問題点は「行の最大値を 1000 にしている」こと。
- この値を超えた行は計算の対象外になってしまう。大きくすると計算（検索）に時間が掛かる。小さくする、その後 追加したデータが反映されない等の問題点があり悩ましい。
- マクロでは、通常、Ctrl＋↓ を上手く使って計算するので無駄がない。
- Indirect 関数を使ってみたが、式のコピーが出来ない（コピーしても行や列が自動的に変わらない）ので、ここでは使えない‥‥ ことがわかった。（たぶん）
- 試しに‥‥ 行を挿入してみた。
(1)挿入前のセルF1の計算式：=AVERAGEIF($B$5:$B$1000,$E1,F$5:F$1000)
(2)行番号 250の下に、10行挿入する。（行251〜260を行選択し、右クリック⇒［挿入］）
(3)挿入後のセルF1の計算式=AVERAGEIF($B$5:$B$1010,$E1,F$5:F$1010) ‥‥ 1000が1010になっていた。
この技？、使える!!!!!
- 平均スコアの年を変えると、その年の平均値が表示される。
- 参考）回数は “=CountIf($B$5:$B$1000,E1)” で求められる。（開催年の個数）
▲ グラフ作成に必要な表を作る
新しいシートに、次のような、年毎の平均値の表を作る。
- シート名： 平均値表 （名前は任意）
- 項目
- 開催年 ‥‥ 検索に使うので、スコア表の「開催年」と同じ設定にする。
- 平成年 ‥‥ これはグラフ作成に必要な項目で、項目名は任意。
- 回数 ‥‥ 年間の回数
- Out、In、中、グロス、Net、パット数 ‥‥ セルの書式設定は、スコア表に合わせた方がよい。
- これに伴い、ゴルフスコア表があるシート見出しを「スコア表」とした。（名前は任意）
■ 作り方
§ 開催年
- 入力は数字のみ（2008，2009，2010…）とする。
- 何年まで作るかは任意。（後から解るが、多く作ってもあまり意味がない）
§平成年
文字型にしておかないと、グラフ作成時トラブル可能性がある。
作り方は任意であるが、開催年と整合性が取れていることが条件。
- 例１：セルB2に “平成20年” と入力し、フィルハンドルでコピーする。
- 例２：セルB2に “="平成"&(A8-1988)&"年"” と入力し、フィルハンドルでコピーする。
- 西暦年−1988＝平成年 になることを利用したもの。
- 例３：セルB2に
=TEXT(A2&"/1/1","gggee年")
と入力し、以下はフィルハンドルでコピーする。- これは、西暦年の1月1日の日付を作り元号年に変換する方法で、開催年と完全に整合性が取れている。
§ 回数
“CountIf” 関数を使い、スコア表の「開催年」（B列）から平均値表の「開催年」（A列）に一致するセルの数を取得する。
- セルC2をクリックする。
- “=countif(”と入力し、
（関数の挿入）をクリックする。- 「範囲」にカーソルを移し、
- ［スコア表］シートを前に出し、
- セルB5からB10までドラッグして選択する。
- “スコア表!B5:B10”と表示されるので、“スコア表!B5:B1000” に修正する。
- 本来ならば B5から B1000まで選択するのであるが、面倒なので便法をとったもの。
- 「検索条件」にカーソルを移し、
- ［平均値表］シートのセルA2をクリックする。
→検索条件に“A2”が入る。
- ［OK］をクリックする。
→数式バーに=COUNTIF(スコア表!B5:B1000,A2)
と表示される。 - この数式を下にコピーすると、B列の範囲が変わってしまうため、絶対番地に変更する。
→“=COUNTIF(スコア表!$B$5:$B$1000,A2)” - フィルハンドルを使って、数式を下にコピーする。
§ Out
“AverageIf” 関数を使い、スコア表の「開催年」（B列）から平均値表の「開催年」（A列）に一致する「Out」（F列）の平均値を取得する。
- セルD2をクリックする。
- “=averageif(”と入力し、
- （関数の挿入）をクリックする。
- 「範囲」にカーソルを移し、
- ［スコア表］シートを前に出し、
- セルB5〜B10を選択する。
- “スコア表!B5:B10”と表示されるので、“スコア表!B5:B1000” に修正する。
- 「条件」にカーソルを移し、
- ［平均値表］シートのセルA2をクリックする。
→条件に“A2”が入る。 - ［平均対象範囲］にカーソルを移し、
- ［スコア表］シートを前に出し、
- セルF5〜F10を選択する。
- “スコア表!F5:F10”と表示されるので、“スコア表!F5:F1000” に修正する。
- ［OK］をクリックする。
→数式バーに=AVERAGEIF(スコア表!B5:B1000,A2,スコア表!F5:F1000)
と表示される。 - この数式を下にコピーすると、
(1)第1引数の B列の範囲が変わってしまうため、絶対番地に変更する。
(2)右へコピーすると第2引数は列が変わるため、列のみ固定する。
(3)第3引数は行のみ固定する。
→“=AVERAGEIF(スコア表!$B$5:$B$1000,$A2,スコア表!F$5:F$1000)” - フィルハンドルを使って、数式を下にコピーする。
§ In, 中, グロス，Net, パット数
「Out」の関数をコピーする。基本の数式は次のようになる。
#DIV/0! と表示される理由
- これは「0で割った」ことを表す。
- 「平均値」は「データ数で割る」ことになる。対象データが無いゼロで割ることになり、このエラーが出る。
- スコア表の「パット数」を見ると‥‥ 開催年の 2008年と 2009年は未入力となっている。
回避するには、スコア表の任意のレコードにゼロを入力すると、平均値表はゼロが表示される。
▲ 目次へ
