スキルアップ・問題解決なら、モーグにおまかせ！

6種類ある試験の得点表があり、以下のような分布図を作成したいと思っています。
点数は50点満点で、元の表は
 
受験者ID　　試験A　試験B　試験C　試験D　試験E　試験F
-------------------------------------------------------
1001　　　　19　　　25　　　-　　　28　　 32　　 39
1002　　　　28　　　32　　　35　　 31　　 28　　 41
1003　　　　40　　　37　　　40　 　38　　 32　　 50
：
のような形で人数は2000人ほどいます。
このデータを
 
　得点50｜　　　　　　　　　　　　　　　　　　●
　　　40｜　●　　●　　●　　●　　　　　　　●●
　　　30｜　●　　●　　●　　●　　　●●　
　　　20｜　●　　●　　　　　●　　　●
　　　10｜
　　　 0｜＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
　　　　　試験A　試験B　試験C　試験D　試験E　試験F
 
のような散布図？のようなグラフにしたいです。
この場合、どのグラフの種類で、どのように作成したらよろしいかご教示いただければと思います。
 
よろしくお願い致します。

引用:
Excel 2016

引用:
6種類ある試験の得点表があり、以下のような分布図を作成したい

引用:
この場合、どのグラフの種類で、どのように作成したらよろしいか

引用:
受験者ID　　試験A　試験B　試験C　試験D　試験E　試験F
-------------------------------------------------------
1001　　　　19　　　25　　　-　　　28　　 32　　 39
1002　　　　28　　　32　　　35　　 31　　 28　　 41
1003　　　　40　　　37　　　40　 　38　　 32　　 50

散布図で作った場合
 
得点50｜　　　　　　　　　　　　　　　　　　●
　　40｜　●　　●　　●　　●　　　　　　　●●
　　30｜　●　　●　　●　　●　　　●●　
　　20｜　●　　●　　　　　●　　　●
　　10｜
　　 0｜＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
　　　　　1　　2　　　3　　　4　　　　5　　　6
 
こんな散布図で、X軸が項目でない事について の対策という事で良いでしょうか？
 
 
散布図の場合、X軸 は 数値になります。
 
これは、散布図 の目的が、変数X と 変数Y 相関性のある値に対しての
ばらつき を 見る為のグラフであり、
 
今回の様な、X軸のグルーピングした物のそれぞれを表すグラフではありません。
 
X軸に、固有名称を出したいのであれば、
　B1〜G4 を選択、マーカー付き折れ線グラフを作成、各系統を選択し、線なし　でしょうか。
　1系統づつ「線なし」は大変なので、
　　　B1〜G2を選択、線なしのグラフを作成後、データ範囲を拡張してみてください。
 
　もっと泥臭くて良いなら、散布図で作って、X軸上に、テキストボックスを配置でしょうか。

　　　 A　　　B　　　C　　　D　　　E　　　F　　　G　H　I　　 J　K　　　L
 1　　ID　試験A　試験B　試験C　試験D　試験E　試験F　　　　得点　　　種類
 2　1001　　 19　　 25　　　-　　 28　　 32　　 39　　 1　　 5　0　試験A
 3　1002　　 28　　 32　　 35　　 31　　 28　　 41　　 1　　10　0　　　　
 4　1003　　 40　　 37　　 40　　 38　　 32　　 50　　 1　　15　0　　　　
 5　1004　　 34　　 46　　 36　　 15　　 45　　 31　　 1　　20　2　　　　
 6　1005　　 30　　 14　　 16　　 32　　 45　　 40　　 1　　25　2　　　　
 7　1006　　 16　　 47　　 12　　 29　　 19　　 10　　 1　　30　3　　　　
 8　1007　　 33　　 23　　 39　　 19　　 33　　 41　　 1　　35　2　　　　
 9　1008　　 47　　 49　　 26　　 49　　 34　　 11　　 1　　40　2　　　　
10　1009　　 48　　 41　　 23　　 45　　 18　　 27　　 1　　45　0　　　　
11　1010　　 24　　 21　　 29　　 33　　 22　　 27　　 1　　50　3　　　　
12　1011　　 49　　 20　　 47　　 19　　 39　　 31　　 2　　 5　0　試験B
13　1012　　 21　　 44　　 27　　 30　　 49　　 11　　 2　　10　0　　　　
14　1013　　 26　　 26　　 27　　 29　　 22　　 35　　 2　　15　1　　　　
15　1014　　 36　　 45　　 16　　 26　　 32　　 49　　 2　　20　1　　　　
16　　　　　　　　　　　　　　　　　　　　　　　　　　 2　　25　3　　　　
17　　　　　　　　　　　　　　　　　　　　　　　　　　 2　　30　1　　　　
18　　　　　　　　　　　　　　　　　　　　　　　　　　 2　　35　1　　　　
 
●I1とK1は空白、J1に得点、L1に種類と入力
 
I2
=IF(ROW(I1)>10*6,NA(),INT((ROW(I1)-1)/10)+1)
J2
=IF(I1<>I2,5,SUM(J1,5))
K2
=COUNTIF(INDEX(B:G,,I2),"<="&J2)-IF(J2=5,0,COUNTIF(INDEX(B:G,,I2),"<="&J2-5))
L2
=IF(I1<>I2,INDEX(B$1:G$1,I2),"")
 
●I2:L2を下方向にフィルコピー(#N/Aが数行現れるまで)
 
●I1:L列下の#N/Aあたりまでの範囲を選択
 
●グラフ バブル 挿入
 
●系列[得点]のバブルで右クリック
　→データ系列の書式設定
　→サイズの表示　バブルの幅を選択
　→バブルサイズの調整　30など、あまりバブルが重ならない数値に
 
●系列[種類]のバブルで右クリック
　→データラベルの追加
　→できたデータラベル[0]の上で右クリック
　→データラベルの書式設定
　→ラベルの内容　セルの値　にチェックを入れて　
　　　範囲L2:L列下の#N/AあたりまでCtrl+↓で選択
　→ラベルの内容でY値などほかの物はチェックを外す
　→ラベルの位置　下
 
●項目(横)軸[0,1,2...7]は邪魔なので削除

皆様、すばらしいご回答をありがとうございます。
 
sk様
ありがとうございます。
おっしゃるようにこのままのレイアウトですと、希望の形にはならないようです…
箱ひげグラフ、聞いたことがありますが使ってみたことがないので確認してみます。
 
Suzu様
ありがとうございます。
やはり散布図だとどうしても点数が横に出てくれず、実際の件数が多いため、私の見本の図のようにはなりませんでした。
ですが、ラベルを変更する方法は今後も使わせていただきます。
 
んなっと様
ありがとうございます。
教えていただきましたＩ列〜Ｌ列の集計表の作成方法、今後使わせていただきます。
私がやりたいことに近づいてきましたが、バブルでないと点数がＹ軸に出てこないのでしょうか・・・
バブルでは人数をバブルの大きさで表しますが、散布図のように一定の点の大きさで、たとえば「試験Ａ」ではどのあたりの点数に点が集まっているのか、というようなことが知りたいです。
 
他に方法がなければ、指示者に相談しますが、集計表を生かして上記のことができればと思います。
よろしくお願いいたします。

引用:
やはり散布図だとどうしても点数が横に出てくれず、実際の件数が多いため、私の見本の図のようにはなりませんでした。

濃淡のアプローチもありそうですけど、
 
高密度散布図(濃淡散布図)の作成 with Excel
https://bdastyle.net/tools/scatterplot/high-density-scatterplots.html
 
プロット である限り、X軸 が 同一に 重なるのは変わらないので 表現しきれない気がします。

　　I　　　J　　　K　　　L　　　M　　　N　　　O　　 P　　　Q　　 R　　 S　　 T
 1　　 試験A　試験B　試験C　試験D　試験E　試験F　1.98　　　　　　0　　 1　　 2
 2　0　　　9　　　8　　　4　　 10　　 13　　　9　　　　試験A　　 0　　 1　　 2
 3　1　　　7　　　7　　　8　　　4　　　5　　　9　　　　　　　　　0　　 1　　 2
 4　2　　 10　　　8　　　8　　　9　　　9　　　7　　　　　　　　　0　　 1　　 2
 5　3　　 10　　 14　　 15　　　9　　　9　　　3　　　　　　　　　0　#N/A　　 2
 6　4　　　4　　 11　　　7　　 11　　　4　　　8　　　　　　　 #N/A　#N/A　　 2
 7　5　　　8　　 10　　 20　　　7　　　7　　 11　　　　　　　 #N/A　#N/A　#N/A
 8　6　　　8　　　7　　 10　　　6　　　8　　 10　　　　　　　 #N/A　#N/A　#N/A
 9　7　　 10　　　8　　　9　　 13　　　8　　 13　　　　　　　 #N/A　#N/A　#N/A
10　8　　　9　　 11　　　7　　 13　　　8　　　9　　　　　　　 #N/A　#N/A　#N/A
11　9　　　6　　　3　　　7　　　9　　　5　　　6　　　　　　　 #N/A　#N/A　#N/A
 
I2
=ROW(I1)-1
J2
=COUNTIF(B:B,$I2)
右方向・→O2 まで
I2:J2を選択 下方向・↓52行までフィルコピー
 
P1
=MAX(J2:O52)*6/100
 
R1
=COLUMN(A1)-1
右方向・→BP1 まで
 
Q2
=IFERROR(IF(OR(ROW(Q2)=2,COUNT(R1:BP1)=0),INDEX($J$1:$O$1,COUNTIF(Q$1:Q1,"?*")+1),""),"")
R2
=IF(INDEX($J$2:$O$52,COLUMN(A1),COUNTIF($Q$1:$Q2,"?*"))/$P$1<ROW(R2)-MATCH(1,0/($Q$1:$Q2<>""))+1,NA(),R$1)
右方向・→BP2 まで
Q2:BP2を選択 下方向・↓101行までフィルコピー
 
Q2:BP101 選択して マーカー付き折れ線グラフ 挿入
 
最後に凡例[系列1,系列2,....系列36,...]を削除

例えば、極端な例ですが、下記のような試験結果の場合、全く同じ場所に「点」を9個。重ねて打つことになります。
 
受験者ID　　試験A　
1001　　　　40
1002　　　　50
1003　　　　50
1004　　　　50
1005　　　　50
1006　　　　50
1007　　　　50
1008　　　　50
1009　　　　50
1010　　　　50
 
つまり、「点」は全く同じ所に「上書き」される。なので、この場合、
・50点 に、「点が1個」
・40点 に、「点が1個」
・30〜 0 はゼロ個
と描写されます。つまり、下記のようなグラフになります。
 
　得点50｜　●　
　　　40｜　●　
　　　30｜　　　
　　　20｜　　　
　　　10｜　　　
　　　 0｜＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
　　　　　試験A　試験B　試験C　試験D　試験E　試験F
 
 
単なる「点」では偏りは区別出来きないと思うのですが。
なお、バブルでは、「点」の数を、大きさで表現しています。
 
 

MMYS様
ご丁寧な回答、ありがとうございます。
同じ点数の箇所にいくつか重なると上書きされるのですね。
よくわかりました。
少し考えます…
 
んなっと様
本当にありがとうございます。
ご案内いただきました通り式を入れてみたのですが、R2：BP7の範囲で「#N/A」エラー、
R8：BP101の範囲で「＃DIV/0！」が表示されました。
実際のデータは受験していない試験は空白になっています。
（受験した試験のみ平均値を出しているため0は入れておりません）
 
ためしに0を入れたり「-」を入れたりしても変わりませんでしたので、どこかに原因がある
のだと思いますが、集計表はできております。
 
大変申し訳ありませんが、MMYS様やこれまでご回答いただきました皆様がおっしゃるように、
データ数が多いですし、本来の正しい使い方ではないようなので散布図は諦めようと思っています。
 
ここまでお時間を頂戴し、感謝いたします。
大変勉強になりました。
ありがとうございました。