エクセルで満点が混在、欠席も考慮した平均点の求め方

質問

エクセルで満点が混在、欠席も考慮した平均点の求め方を教えてください。
同じ表を受け継いで行こうと思いますので、マクロを使用しない方法をご教授ください。

添付の表の様に、大テスト小テスト混在のテスト点数から、各個人の平均点を求めたいのです。
水色バックのセルには、任意の数値を入力します。
黄色バックのセルを自動計算＆表示させたいのです。

A）欠席でテストを受けなかった回は平均点から除外します。
　ex.Ａさん：2回目を欠席しているので、分母は100+10+100+50+100=360
　5回分の総点数295点を360点で割り算すると81.94％と算出される。

Ｂ）各回のテストの重みは、満点数に比例するとします。
　100点満点の70点と、10点満点の5点の場合、↓で計算します。
　(70+5)/(100+10)＝68.18％

Ｃ）しきい値の項目に数値を記入すれば、各個人が上中下どのランクなのか表示させたい。
　映像で言うと、赤文字と青文字です。

よろしくお願いいたします

回答No.4

keithin · ベストアンサー

アナタの画像から，「満点」とＡ６セルに書いてあり，「Ａさん」とＡ７セルに書いてあるとして，回答１ではＡさん平均のＩ７セルに
=SUM(B7:G7)/SUMIF(B7:G7,"<>",B$6:G$6)
と記入し以下コピーします。と回答しましたが，やってみたのでしょうか？

回答１では条件付き書式を使う方法も説明しましたが，バージョンによって具体的な操作の方法が変わります。こちらも回答を見て実際に手を動かしていれば，「回答して貰ったけどどこを操作したらいいのか判らない」という返事が返って然るべきでしたが（いや勿論，詳細な説明など聞かなくても楽勝で出来ましたというのならそれが一番です），エクセルの２００７を使っているのでしたら次のように操作します。

手順：
Ｉ７：Ｉ１１を選ぶ
ホームタブから条件付き書式▼で「セルの強調表示ルール」から「指定の値より大きい」を開始
Ｉ２セルを設定し，書式のプルダウンから赤文字を設定しＯＫする。
（ダイアログには　=$I$2　と自動記入される）

改めてＩ７：Ｉ１１を選ぶ
ホームタブから条件付き書式▼で「セルの強調表示ルール」から「指定の値より小さい」を開始
Ｉ３セルを設定し，書式のプルダウンから赤文字を設定しＯＫする。
（ダイアログには　=$I$3　と自動記入される）

お礼

2度にわたり、ご回答ありがとうございました。

実際にエクセルで試してみましたが、私の希望がほとんど叶えられています。正に100点満点です！

回答No.3

KURUMITO

B5セルからG5セルにかけて1から6までの数値が、A6セルには満点、A7セルから下方にAさん、Bさんと入力されており、その他のセルにはお示しのデータが入力されているとします。
平均の文字がI5に有るとしてI7セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(A7="","",ROUND(SUM(B7:G7)/SUMPRODUCT((B7:G7<>"")*(B$6:G$6)),4)*100)

赤文字青文字の件ですが閾値のデータがI1セルに80、I2セルに60と入力されているとして次のようにします。
I7セルから下方のセルを範囲として選択したのちに「ホーム」タブの「条件付き書式」をクリックして「新しいルール」を選択します。
表示される画面で「数式を使用して書式設定するセルを決定」を選択してから下方の窓には　=I7>=I$1 と入力し、同じ画面の「書式」で「塗りつぶし」のタブから「色」に赤色を指定してOKします。
同じ操作を「条件付き書式」で行い表示の窓には　=I7<=I$2 と入力して、「書式」の「塗りつぶし」のタブでは青色を指定してOKします。

お礼

ご回答ありがとうございました。

SUMPRODUCT関数って、単なる積算だけではないのですね。
改めてお勉強をさせていただきました。

ROUND(●,4)*100というのも、後に引き継ぐ為には重要ですね。
細かい配慮ですが、うっかりとセルの書式設定を外されても大丈夫ですものね。

回答No.2

asuncion

＞Ｂ）各回のテストの重みは、満点数に比例するとします。
＞　100点満点の70点と、10点満点の5点の場合、↓で計算します。
＞　(70+5)/(100+10)＝68.18％

この部分は、Aさんの例でいうところの81.94%を算出するために必要な内容でしょうか。
A)の説明で言い表わせてはいないでしょうか。
B)の説明自体、いらないような気がします。そんなことはないでしょうか。

補足

ご回答ありがとうございます。

asuncionさん>B)の説明自体、いらないような気がします。

そうですね。
10点位の小テストでも、ある回のテストは5倍位の意味を持たせる計算方法も有ると思うのです。
でも、そんな事を言い出すときりが無いので、満点数に比例して得点を考えれば良いと、単純化する意味で加えた項目でした。

かえって混乱させてしまいました。ごめんなさい。

回答No.1

keithin

Ａさんの平均は
=SUM(B7:G7)/SUMIF(B7:G7,"<>",B$6:G$6)
といった具合の計算式で計算できます。

各平均値セルに条件付き書式を設定し，
セルの値が　次の値以上　赤の閾値のセル（絶対参照で）
セルの値が　次の値より小さい　青の閾値のセル（同上）
のように設定します。
以上なのか以下なのか，より大きいなのか小さいなのか，言葉でキチンと考えて正しい設定をしてください。

＃補足
「以上」「以下」ってのは，その値を含むという意味です。
「より大きい」「より小さい」ってのは，その値は含まないという意味です。

＃セル番地も不明。ご利用のエクセルのバージョンも不明。ナイナイ尽くしのご相談です。
もしも手取り足取り教わらないと出来ないようなら，何列何行に何を記入している，バージョン幾つのエクセルをご自分は使っている，などの説明をご相談に記入することを憶えてください。

補足

早速の回答ありがとうございます。

エクセルのバージョンがそれほど重要とは不勉強でした。
普段使っているのが2007ですが、バージョンって2007とかいう数字でしょうか?

セル番地を表示した映像にしたかったのですが、方法が分かりませんでした。
添付映像の左から右へ　Ａ→Ｉ
上から下へ　1→10
です。