Part 1に引き続き、学校のテスト成績表の作成方法についての秘訣を説明します(^^)
これからご紹介するのは、ちょっと難しいかもしれません。
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 生徒番号 | 名前 | 性別 | 国語 | 数学 | 英語 | 理科 | 社会 | 合計 | 平均点 | 評価 |
| 2 | 0201 | 伊藤 武 | 男 | 39 | 58 | 86 | 50 | 70 | 303 | 60.6 | |
| 3 | 0202 | 井上 勝 | 男 | 60 | 29 | 88 | 78 | 80 | 335 | 67.0 | |
| 4 | 0203 | 榎本 幸治 | 男 | 70 | 86 | 100 | 92 | 96 | 444 | 88.8 | |
| 5 | 0204 | 太田 博 | 男 | 58 | 65 | 44 | 40 | 56 | 263 | 52.6 | |
| 6 | 0205 | 萱島 真 | 男 | 88 | 98 | 75 | 68 | 50 | 379 | 75.8 | |
| 7 | 0206 | 木下 宍道 | 男 | 60 | 63 | 39 | 55 | 65 | 282 | 56.4 | |
| 8 | 0207 | 久部 明弘 | 男 | 98 | 96 | 78 | 88 | 82 | 442 | 88.4 | |
| 9 | 0221 | 綾野 亜希 | 女 | 60 | 58 | 77 | 82 | 80 | 357 | 71.4 | |
| 10 | 0222 | 江本 綾子 | 女 | 74 | 38 | 55 | 77 | 40 | 284 | 56.8 | |
| 11 | 0223 | 岡田 優利子 | 女 | 95 | 89 | 99 | 90 | 98 | 471 | 94.2 | |
| 12 | 0224 | 加藤 祐美 | 女 | 78 | 45 | 66 | 62 | 48 | 299 | 59.8 | |
| 13 | 0225 | 河野 恵美子 | 女 | 45 | 80 | 48 | 52 | 60 | 285 | 57.0 | |
| 14 | 0226 | 斎藤 美恵 | 女 | 90 | 82 | 68 | 95 | 92 | 427 | 85.4 | |
| 15 | 0227 | 篠原 信子 | 女 | 48 | 77 | 42 | 50 | 78 | 295 | 59.0 | |
| 16 | クラス平均 | 68.8 | 68.9 | 68.9 | 69.9 | 71.1 | 347.6 | ||||
| 17 | 男子平均 | ||||||||||
| 18 | 女子平均 | ||||||||||
| 19 | クラス最高 | 98 | 98 | 100 | 95 | 98 | 471 | ||||
| 20 | 男子最高 | ||||||||||
| 21 | 女子最高 | ||||||||||
クラスには、男子と女子がいますね（共学の場合ですけど(^^;）
性別ごとに平均点を求めたい場合は、データベース関数のひとつ、DAVERAGE関数を使います。
しかし、DAVERAGE関数を使う前に、やっておくべきことがあります。
Criteriaの部分を作らなければ前に進まないのです(ｰｰ;)
Criteriaというのは、どのデータを求めるか、ということですね。
まず、空いているセルに下の表のようにデータ入力（仮にM列以降の部分に）してください。
| M | N | |
|---|---|---|
| 1 | 性別 | 性別 |
| 2 | 男 | 女 |
はじめに、男子の国語の平均点を求めましょう。
D17にカーソル移動させて、「関数貼り付け」ボタン
を押してください。
上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DAVERAGE」を選択して、OKボタンを押してください。
まず、データベースとなる部分、C1からH15までの範囲をマウスで選択して、F４キーを1度押してください。
（絶対参照にするために必要です！…絶対参照と相対参照についての説明は→こちら）
フィールド欄は、国語を求めますので、国語の見出しセル、D1を指定します。
Criteria部分は、男子のデータを求めるので、先ほど作成した表のM1からM2までを範囲選択して、F4キーを1度押してください。
最後に、OKボタンを押します。すると、67.6点であることが求められましたね？
小数第1位まで表示するためには、書式設定ツールバーの
や
のボタンで調整してくださいね。
数式の構造をわかりやすく説明すると・・・
=DAVERAGE($C$1:$H$15,D1, $M$1:$M$2)
C1からH15までのデータベースを固定して参照（絶対参照）し、フィールド部分はD1（つまり、国語のフィールド）を指定し、CriteriaはM１からM2までの範囲（男子の性別）を固定して参照するように平均値を求めてください。
ということになります。
その後、他の科目の男子の平均点を求めるように、マウスで数式コピーしてくださいね。
次に、女子の国語の平均点も求めてみましょう。
D18にカーソル移動させて、先ほどと同様にDAVERAGE関数を使ってみてください。
ただし、Criteriaの部分は女子なので、N１からN2までを範囲選択して、F4キーを1回押してください。
ちなみに、数式は次のようになるはずです。
=DAVERAGE($C$1:$H$15,D1,$N$1:$N$2)
数式の結果は70と求められましたね？
そして、値を小数点第1位まで表示するようにしておいてください。
その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。
性別ごとに最高点を求めたい場合は、データベース関数のひとつ、DMAX関数を使います。
はじめに、男子の国語の最高点を求めましょう。
D20にカーソル移動させて、「関数貼り付け」ボタンを押してください。
上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DMAX」を選択して、OKボタンを押してください。
まず、データベースとなる部分、C1からH15までの範囲をマウスで選択して、F４キーを1度押してください。
（絶対参照にするために必要です！…絶対参照と相対参照についての説明は→こちら）
フィールド欄は、国語を求めますので、国語の見出しセル、D1を指定します。
Criteria部分は、男子のデータを求めるので、先ほど作成した表のM1からM2までを範囲選択して、F4キーを1度押してください。
最後に、OKボタンを押します。すると、98点であることが求められましたね？
(98点をとった久部君はクラスでもトップですからねヾ(^^;）
数式の構造をわかりやすく説明すると・・・
=DMAX($C$1:$H$15,D1,$M$1:$M$2)
C1からH15までのデータベースを固定して参照（絶対参照）し、フィールド部分はD1（つまり、国語のフィールド）を指定し、CriteriaはM１からM2までの範囲（男子の性別）を固定して参照するように最大値を求めてください。
ということになります。
その後、他の科目の男子の最高点を求めるように、マウスで数式コピーしてくださいね。
次に、女子の国語の最高点も求めてみましょう。
D21にカーソル移動させて、先ほどと同様にDMAX関数を使ってみてください。
ただし、Criteriaの部分は女子なので、N１からN2までを範囲選択して、F4キーを1回押してください。
ちなみに、数式は次のようになるはずです。
=DMAX($C$1:$H$15,D1,$N$1:$N$2)
数式の結果は95と求められましたね？
その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。
成績をランク付けするというのは、あまり私としては気が進まないのですが・・・(-_-;)
学校の先生をしていらっしゃる方は、評価をつけなければならない立場だと思いますので・・・
Excelの関数を使って自動的に評価をつける方法を説明します。
まず、評価を求めるためのリストを作成してください。
| M | N | ||
|---|---|---|---|
| 4 | 点数 | 評価 | →ここにわかりやすいようにタイトルを入力しておきます |
| 5 | 0 | E | 0以上20未満の場合はE |
| 6 | 20 | D | 20以上40未満の場合はD |
| 7 | 40 | C | 40以上60未満の場合はC |
| 8 | 60 | B | 60以上80未満の場合はB |
| 9 | 80 | A | 80以上の場合はA |
その後、VLOOKUP関数を使います(^^)
実は、VLOOKUP関数の使い方は2種類あります。
一致するものを検索、またはその値を満たすものを検索して求める方法があるのです。
前者は「請求書の作成」にてご紹介しています(^_^;)
ここでは、後者の「その値を満たすものをリストから検索して求める」方法を紹介いたします。
まず、K2にカーソルを移動させて、ボタンを押してください。
そして、「関数の分類(C)」欄には、「検索/行列」、
「関数名(N)」欄では、一番下の「VLOOKUP」を選択してOKボタンを押してください。
「検索値」は平均点のセル、J2を選択します。
「範囲」は、先ほど作成したM5からN9までの範囲をマウスで選択して、F4キーを1回押します（絶対参照のため）。
「列番号」は、2列目を検索するので「２」と入力します。
「検索の型」は、「その数字を満たすもの（近似値を含めて）」検索するので、「TRUE」と必ず入力して、
最後にOKボタンを押してください。
K2のセルの数式は、このようになっているはずです。
=VLOOKUP(J2,$M$5:$N$9,2,TRUE)
最後に、全員の評価を求めるために、マウスで下へドラッグして数式コピーしてくださいね。
これで、検索の型の2通りの使い分け、理解できましたか？
ちょっと複雑ですが、VLOOKUP関数はとても便利なので、ぜひマスターしてくださいね(^^)
操作方法、設定方法等で困ったら、Microsoft Office Users Forum に質問してください！
