エクセルでテストの平均を出す時未受験と０点を区別する方法

質問

観点Ａ・Ｂ・Ｃに分かれているテストを２回やり，各観点の達成率の平均をそれぞれ出したい場合の数式はどのようにすればスッキリするでしょうか？
ある観点に０点があった場合や未受験のテストがあった場合はどのようにすればいいのか悩み中です。
未受験の扱いはどうのようにすればいいでしょうか？
シート２，シート３の数式の組み方を教えていただけるとうれしいです。よろしくお願いします。

【シート１】→各観点の得点を入力
　　　　　　　　　テスト１　　　　　　　　テスト２　
　　　　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ
　　　　観点Ａ　観点Ｂ　観点Ｃ　観点Ａ　観点Ｂ　観点Ｃ
１太郎　０　　　　　４　　　　７　　　　２　　　　２　　　　２
２次郎　未　　　　未　　　　未　　　 ４　　　　５　　　　５
３三郎　３　　　　　５　　　　５　　　　１　　　　３　　　　６

【シート２】→各観点の達成率(％)が出るように数式を組む
　　　　　　　　　テスト１　　　　　　　　テスト２　
　　　　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ
　　　　観点Ａ　観点Ｂ　観点Ｃ　観点Ａ　観点Ｂ　観点Ｃ
１　太郎　　
２　次郎 　
３　三郎
４　　　　　４　　　　６　　　　７　　　　５　　　　５　　　　６
※　４行目の数字は各観点の満点が入力されている　　

【シート３】→観点Ａだけの達成率の平均が出るように数式を組む
　　　　 　　Ａ　　　　Ｂ　　　 Ｃ　　　
　　　　　テスト１　テスト２　平均
１　太郎　　　 　　　
２　次郎 　
３　三郎

【シート４】→観点Ｂだけの達成率の平均が出るように数式を組む
【シート５】→観点Ｃだけの達成率の平均が出るように数式を組む

回答No.1

noname#71905 · ベストアンサー

意図に合うかわかりませんが、一例です。

【シート１】・・・太郎のデータ【テスト１、観点Ａ】が、セルB3　としています
名前設定をしておきます(★全角文字に統一してください)
●テスト１以下を{テスト１}と{定義}・・・{=Sheet1!$B$2:$D$5}
●テスト２以下を{テスト２}と{定義}・・・{=Sheet1!$E$2:$G$5}

【シート２】・・・【シート１】と同じ位置という前提です
●セルの書式設定で　％　表示に設定し
●セルB3　に以下を入力
=IF(ISTEXT(Sheet1!B3),Sheet1!B3,Sheet1!B3/Sheet2!B$6)
・・・入力したものを残りのセルにコピー
《結果》
0% 67% 100% 40% 40% 33%
未 未 未 80% 100% 83%
75% 83% 71% 20% 60% 100%

【シート３】・・・{B1に、観点Ａ}、{B2,C2,にテスト１,テスト２}としています
●テスト１以下のセル{B3,B4,B5}に、以下を入力
=HLOOKUP($B$1,INDIRECT(B$2),2,FALSE)・・・太郎の行
=HLOOKUP($B$1,INDIRECT(B$2),3,FALSE)・・・次郎の行
=HLOOKUP($B$1,INDIRECT(B$2),4,FALSE)・・・三郎の行
・・・入力したものを、右のC列にコピー
●平均以下のセル{D3}に入力
=AVERAGE(B3:C3)
・・・入力したものを、下にコピー
《結果》
0 2 1
未 4 4
3 1 2

【シート４】【シート５】は、観点Ａを観点Ｂ、観点Ｃに変える以外は
【シート３】とまったく同じです。

お礼

遅くなりましたが，大変詳しい回答をありがとうございました！