エクセルでクラスの平均点などの表を作る

質問

塾のクラスで生徒のテスト結果や平均点の一覧を作成しようと思います。
ソフトはエクセルです。

例えばクラスAとクラスBがあるとして、クラスA　鈴木　95点　のような形で入力していくのですが、
今まではクラスAのメンバーを上からいれ、その下にクラスBを入力し、AVERAGEで平均点を出していました。

ただこれから毎月のようにクラス間の移動があるようで、そうなると「今月はこの子はクラスAだが、来月はクラスB」という状況も出てきます。

そうなると上記の方法でクラスAの平均点などを出す場合、大変になるので、
「ある一定のセルの範囲内で『クラスA』という記載のある生徒の平均点を出す」という関数を入れたいと思います。

要するに「クラスA」というセルが横にある生徒の名前（＆点数）のところのみを計算するようにしたいのです。

このような事は可能でしょうか。また方法があれば教えてください。

回答No.5

KURUMITO · ベストアンサー

かなりの時間を使って作成しましたのでぜひこちらの指示通りで試験をしてみてください。参考になれば幸いです。
シート1はデータを連続して入力していくためのシートです。
A2セルには試験日、A3セルには教科、A4セルにはクラスの文字をそれぞれ入力します。B2,B3,B4セルにはA列に対応したデータを入力します。例えばB2セルには6/15、B3セルには数学、B4セルにはAのように入力します。
その後にA4セルから下方にはそのクラスでの生徒の名前を、B4セルから下方にはそれぞれの生徒の点数を入力します。
クラスが違ったデータに移るときは上のデータの境に空の行を入れることなくA列のセルにはクラスと文字を入力し、B列のセルにはそのクラス名を例えばBと入力します。その下行にはAクラスと同じようにクラスでの生徒の名前と点数を入力します。
試験日が次に移ったデータを入力する場合には引き続いて空の行を作ることなくA列に試験日の文字でB列にその日付を入力します。また、その下の行のA列には教科の文字を、B列にはその教科名を国語のように入力します。
その下の行のA列にはクラスの文字を、B列にはそのクラス名を入力します。以下はこれまで述べた方法を繰り返していくことになります。すなわち同じ試験日でクラスが変わった場合には途中で、クラスの文字とそのクラス名を入力しますし、日付が変わった場合には2行目から4行目までの様式を繰り返すことになります。このようにして、それぞれの試験日におけるデータを下方に入力してゆけばよいことになります。
そこで生徒の氏名ごと、試験日ごとのクラス名、点数などをシート2で整理することにします。その上でお求めの指定した期間におけるクラスごとの平均点をシート3で表示させることにします。
シート2に表示させるために必要な作業列をシート1に作ります。
C2セルには次の式を入力します。

=IF(OR(A2="",A2="試験日",A2="クラス",A2="教科"),"",IF(COUNTIF(A2:A$5,A2)=1,MAX(C1:C$4)+1,""))

D2セルには次の式を入力します。

=IF(A2="試験日",(RANK(B2,B:B,1)-COUNTIF(B:B,"<40000"))*1000,IF(OR(A2="教科",A2="クラス"),D1,IF(A2<>"",ROUNDDOWN(D1,-3)+INDEX(C:C,MATCH(A2,A:A,0)),"")))

この式では入力した試験日が必ずしも早い日を先に入力する必要も無いように工夫されています。
E2セルには次の式を入力します。

=IF(OR(A2="試験日",A2="教科"),"",IF(A2="クラス",B2,IF(A2<>"",E1,"")))

C2セルからE2セルまでを選択してからそれらの式を下方にドラッグコピーします。
以上でシート1での作業は終わります。
次にシート2ですが1行目は空白の行としてB2セルには次の式を入力してB4セルまでドラッグコピーしたのちに右横方向にもドラッグコピーします。

=IF(B$5="","",IF(ROW(A1)=1,IF(MOD(COLUMN(A1),2)=1,"試験日","教科"),IF(ROW(A1)=2,IFERROR(IF(MOD(COLUMN(A1),2)=1,INDEX(Sheet1!$B:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000,Sheet1!$D:$D,0)),IF(MOD(COLUMN(A1),2)=0,INDEX(Sheet1!$B:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000,Sheet1!$D:$D,0)+1),"")),""),IF(ROW(A1)=3,IF(MOD(COLUMN(A1),2)=1,"クラス","点数"),""))))

3行目は日付が入るセルが有りますのでセルの表示形式を日付にします。なお、A3セルには0を必ず入力してください。
A5セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!C:C),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!C:C,0)))

これでA列には生徒名が表示されます。
B5セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(IF(MOD(COLUMN(A1),2)=1,INDEX(Sheet1!$E:$E,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),Sheet1!$D:$D,0)),IF(MOD(COLUMN(A1),2)=0,INDEX(Sheet1!$B:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1),Sheet1!$D:$D,0)),"")),"")

これで各試験日における生徒のクラス名や点数が表示されます。
最後にお求めの表ですがシート3に表示することにしてシート3では次のようにします。
例えばA1セルには各クラス平均点などと入力します。
A2セルにはデータをまとめたい期間の初めで「期間の初め」とでも文字を入力し、B2セルには4/1とでも入力します。また、A3セルには期間の終わりとでも入力しB3セルには6/31とでも入力します。
A4セルから下方にはクラス名を入力します。例えばA,B、C・・・のように
B4セルには次の式を入力し右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR(B$2="",$A4=""),"",AVERAGEIF(INDEX(Sheet2!$1:$1048576,5,MATCH(B$2,Sheet2!$3:$3,1)):INDEX(Sheet2!$1:$1048576,10000,MATCH(B$3,Sheet2!$3:$3,1)),$A4,INDEX(Sheet2!$1:$1048576,5,MATCH(B$2,Sheet2!$3:$3,1)+1):INDEX(Sheet2!$1:$1048576,10000,MATCH(B$3,Sheet2!$3:$3,1)+1)))

なお、2行目と3行目の日付を右横のセルに入力することで、自由な期間を設定してクラスの平均を表示させることができます。

お礼

本当に助かりました！ありがとうございました！御礼が遅れてしまい申し訳ございません！

回答No.4

d3kk485

エクセルのデータベース関数を使ってみてはいかがでしょうか。
条件が書かれたセルをどこかに書いておかなければなりませんが、別のシートに書く、見えないように白文字で書いておくなどすれば見かけ上問題ありません。
複数条件をセルに入力して管理ができたりと便利な点もあります。2007より前のエクセルでも使えたはずです(若干仕様が違うようですが)。

参考になりそうなサイトのURLを張っておきます。

Excel(エクセル)基本講座：データベース関数
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/d_bas.htm

DAVERAGE関数－データベース関数－Excelオンライン教室
http://ohpa.net/modules/xlnote/content0008.html

回答No.3

esupuresso

・例えばクラスAとクラスBがあるとして
・ある一定のセルの範囲内で『クラスA』という記載のある生徒の平均点を出す
・「クラスA」というセルが横にある生徒の名前（＆点数）のところのみを計算する
要するにクラス別の平均を取りたいわけですよね？

取り敢えずテーブルを作って添付しましたので、参考になるかどうか分かりませんが…
Excelのバージョンが2007＆2010の場合・・・
セルC13に　=IFERROR(ROUND(AVERAGEIF($A$2:$A$10,$B13,C$2:C$10),1),"")　の式を入れてG13までコピー、そのままG14までコピーします

バージョンが2003以下の場合・・・
セルC13に　=IF(COUNT(C$2:C$10)=0,"",ROUND(SUMIF($A$2:$A$10,$B13,C$2:C$10)/COUNTIF($A$2:$A$10,$B13),1))　の式を入れてG13までコピー、そのままG14までコピーします

平均を・・・、という事なので小数点以下、下2けた目を四捨五入した式です

＞ただこれから毎月のようにクラス間の移動があるようで・・・
この場合、A列のクラス（AorB）を変更すると、式自体はセル参照をしてますので当然平均値が狂ってきます
例えば4月の平均値が出たところでC13～C14を範囲選択し、コピーして「値」として同じセルに貼り付けてからA列のAorBを変更します（重要）

「Aクラス」「Bクラス」をはっきり分けたい場合は、セルA1を選択しておいて「昇順」で並べ替えるとよりわかりやすくなります

回答No.2

keithin

ご利用のエクセルのバージョンが不明です。
ご相談投稿では、ソフト名は元より、普段あなたが使うソフトのバージョンまでキチンと明記する事を憶えて下さい。

Excel2007以降を使っている場合はAVERAGEIFS関数を使います。
=AVERAGEIFS(点数列，クラス列，クラス指定，名前列，名前)

Excel2003以前を使っているときは、クラス＋名前の点数合計をクラス＋名前の回数で割り算して平均を求めます。
=SUMPRODUCT((クラス列=指定のクラス)*(名前列=指定の名前),点数列)/SUMPRODUCT((クラス列=指定のクラス)*(名前列=指定の名前))

回答No.1

tom04

こんばんは！
Excel2007以降のバージョンをお使いであれば
AVERAGEIF関数で対応できると思いますが、
手元にExcel2003しかないので、Excel2003以前のバージョンでの一例です。

セル配置はこちらで勝手に↓の画像のような感じでやってみました。

F2セルに
=AVERAGE(IF($A$1:$A$1000=E2,$C$1:$C$1000))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からF2セルにコピー＆ペーストする場合は
F2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これをオートフィルで下へコピーすると、画像のような感じになります。

※　Excel2007以降のバージョンですと
=ABEREGEIF(A:A,E2,C:C)
（当然のコトながら配列数式ではありません）
といった数式になると思います。m（_ _）m