www.wanichan.com

Microsoft Office全般(Excel/Word/PowerPoint/Access/Outlook/OneNote)の使用方法を解説。

PC > Excel > 2000

条件を満たしたデータの合計や平均点、成績評価を求めるには

テスト成績表の作成 Part 2
  • このエントリーをはてなブックマークに追加

データベース関数、DSUMとDAVERAGEを使用して、条件を満たしたデータの集計を求める方法、および、VLOOKUP関数で成績評価を求める方法について説明します。

はじめに

Part 1に引き続き、学校のテスト成績表の作成方法についての秘訣を説明します(^^)
これからご紹介するのは、ちょっと難しいかもしれません。

 A BCDEFGHIJK
1生徒番号名前性別国語数学英語理科社会合計平均点評価
20201伊藤 武395886507030360.6 
30202井上 勝602988788033567.0 
40203榎本 幸治7086100929644488.8 
50204太田 博586544405626352.6 
60205萱島 真889875685037975.8 
70206木下 宍道606339556528256.4 
80207久部 明弘989678888244288.4 
90221綾野 亜希605877828035771.4 
100222江本 綾子743855774028456.8 
110223岡田 優利子958999909847194.2 
120224加藤 祐美784566624829959.8 
130225河野 恵美子458048526028557.0 
140226斎藤 美恵908268959242785.4 
150227篠原 信子487742507829559.0 
16クラス平均 68.868.968.969.971.1347.6  
17男子平均         
18女子平均         
19クラス最高 98981009598471  
20男子最高         
21女子最高         

性別ごとの平均点を求めたい場合は?

クラスには、男子と女子がいますね(共学の場合ですけど(^^;)
性別ごとに平均点を求めたい場合は、データベース関数のひとつ、DAVERAGE関数を使います。

しかし、DAVERAGE関数を使う前に、やっておくべきことがあります。
Criteriaの部分を作らなければ前に進まないのです(ーー;)
Criteriaというのは、どのデータを求めるか、ということですね。

まず、空いているセルに下の表のようにデータ入力(仮にM列以降の部分に)してください。

 MN
1性別性別
2

男子の国語の平均点を求めましょう

はじめに、男子の国語の平均点を求めましょう。
D17にカーソル移動させて、「関数貼り付け」ボタン関数貼り付けを押してください。


上の図のように、「関数の分類(C)」には、「データベース」、
「関数名(N)」には、「DAVERAGE」を選択して、OKボタンを押してください。

DAVERAGE関数を使った数式入力画面
まず、データベースとなる部分、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位まで表示するようにしておいてください。

その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。

▲Page TOP

性別ごとの最高点を求めたい場合は?

性別ごとに最高点を求めたい場合は、データベース関数のひとつ、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と求められましたね?

その後、同様に他の科目の女子の最高点を求めるために数式コピーしてくださいね。

▲Page TOP

成績ごとに評価を付け加えたい場合は?

成績をランク付けするというのは、あまり私としては気が進まないのですが・・・(-_-;)
学校の先生をしていらっしゃる方は、評価をつけなければならない立場だと思いますので・・・
Excelの関数を使って自動的に評価をつける方法を説明します。

まず、評価を求めるためのリストを作成してください。

 MN 
4点数評価→ここにわかりやすいようにタイトルを入力しておきます
50E0以上20未満の場合はE
620D20以上40未満の場合はD
740C40以上60未満の場合はC
860B60以上80未満の場合はB
980A80以上の場合はA

その後、VLOOKUP関数を使います(^^)

実は、VLOOKUP関数の使い方は2種類あります。
一致するものを検索、またはその値を満たすものを検索して求める方法があるのです。
前者は「請求書の作成」にてご紹介しています(^_^;)

ここでは、後者の「その値を満たすものをリストから検索して求める」方法を紹介いたします。

まず、K2にカーソルを移動させて、ボタンを押してください。
関数の貼り付け/VLOOKUP
そして、「関数の分類(C)」欄には、「検索/行列」、
「関数名(N)」欄では、一番下の「VLOOKUP」を選択してOKボタンを押してください。


「検索値」は平均点のセル、J2を選択します。
「範囲」は、先ほど作成したM5からN9までの範囲をマウスで選択して、F4キーを1回押します(絶対参照のため)
「列番号」は、2列目を検索するので「」と入力します。
「検索の型」は、「その数字を満たすもの(近似値を含めて)」検索するので、「TRUE」と必ず入力して、
最後にOKボタンを押してください。

K2のセルの数式は、このようになっているはずです。

=VLOOKUP(J2,$M$5:$N$9,2,TRUE) 

最後に、全員の評価を求めるために、マウスで下へドラッグして数式コピーしてくださいね。

どうしてFALSEじゃダメなの?

試しに、検索の型に「FALSE」と入力して、OKボタンを押してください。
すると、「#N/A」というエラーが出るはずです。
これはNot Availableの意味で、「使えない」から、つまり、一致する数字がリストにはないからです(-_-;)

これで、検索の型の2通りの使い分け、理解できましたか?
ちょっと複雑ですが、VLOOKUP関数はとても便利なので、ぜひマスターしてくださいね(^^)

スポンサーリンク
INDEX
  • このエントリーをはてなブックマークに追加

コメント

※技術的な質問はMicrosoftコミュニティで聞いてください!

▲このページのトップへ