条件を満たしたデータの合計や平均点、成績評価を求めるには
データベース関数、DSUMとDAVERAGEを使用して、条件を満たしたデータの集計を求める方法、および、VLOOKUP関数で成績評価を求める方法について説明します。
はじめに
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までの範囲をマウスで選択して、F4キーを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はM1からM2までの範囲(男子の性別)を固定して参照するように平均値を求めてください。
ということになります。
その後、他の科目の男子の平均点を求めるように、マウスで数式コピーしてくださいね。
女子の国語の平均点を求めましょう
次に、女子の国語の平均点も求めてみましょう。
D18にカーソル移動させて、先ほどと同様にDAVERAGE関数を使ってみてください。
ただし、Criteriaの部分は女子なので、N1からN2までを範囲選択して、F4キーを1回押してください。
ちなみに、数式は次のようになるはずです。
=DAVERAGE($C$1:$H$15,D1,$N$1:$N$2)
数式の結果は70と求められましたね?
そして、値を小数点第1位まで表示するようにしておいてください。
その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。
性別ごとの最高点を求めたい場合は?
性別ごとに最高点を求めたい場合は、データベース関数のひとつ、DMAX関数を使います。
男子の国語の最高点を求めましょう
はじめに、男子の国語の最高点を求めましょう。
D20にカーソル移動させて、「関数貼り付け」ボタンを押してください。
上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DMAX」を選択して、OKボタンを押してください。
まず、データベースとなる部分、C1からH15までの範囲をマウスで選択して、F4キーを1度押してください。
(絶対参照にするために必要です!…絶対参照と相対参照についての説明は→こちら)
フィールド欄は、国語を求めますので、国語の見出しセル、D1を指定します。
Criteria部分は、男子のデータを求めるので、先ほど作成した表のM1からM2までを範囲選択して、F4キーを1度押してください。
最後に、OKボタンを押します。すると、98点であることが求められましたね?
(98点をとった久部君はクラスでもトップですからねヾ(^^;)
数式の構造をわかりやすく説明すると・・・
=DMAX($C$1:$H$15,D1,$M$1:$M$2)
C1からH15までのデータベースを固定して参照(絶対参照)し、フィールド部分はD1(つまり、国語のフィールド)を指定し、CriteriaはM1からM2までの範囲(男子の性別)を固定して参照するように最大値を求めてください。
ということになります。
その後、他の科目の男子の最高点を求めるように、マウスで数式コピーしてくださいね。
女子の国語の最高点を求めましょう
次に、女子の国語の最高点も求めてみましょう。
D21にカーソル移動させて、先ほどと同様にDMAX関数を使ってみてください。
ただし、Criteriaの部分は女子なので、N1から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列目を検索するので「2」と入力します。
「検索の型」は、「その数字を満たすもの(近似値を含めて)」検索するので、「TRUE」と必ず入力して、
最後にOKボタンを押してください。
K2のセルの数式は、このようになっているはずです。
=VLOOKUP(J2,$M$5:$N$9,2,TRUE)
最後に、全員の評価を求めるために、マウスで下へドラッグして数式コピーしてくださいね。
どうしてFALSEじゃダメなの?
試しに、検索の型に「FALSE」と入力して、OKボタンを押してください。すると、「#N/A」というエラーが出るはずです。
これはNot Availableの意味で、「使えない」から、つまり、一致する数字がリストにはないからです(-_-;)
これで、検索の型の2通りの使い分け、理解できましたか?
ちょっと複雑ですが、VLOOKUP関数はとても便利なので、ぜひマスターしてくださいね(^^)
コメント
※技術的な質問はMicrosoftコミュニティで聞いてください!