[Excel2016] 相関表等の自動作成

＜やりたいこと＞　※参考画像のように
①過年度のデータをもとに、高校ごとの偏差値と内申点の相関表を作成する。重複データは数をカウントして表示したい。
　→　もし可能であるなら、年度ごとに色や記号を変えて表示できるとなおよい。
②ある人物のデータを①で作成した相関表の上に（できたら色を変えて）表示させる。

＜作成上の注意事項＞
・Excel2016で動作すること。（関数／マクロどちらも可）
・高校名や偏差値データ等は、別シートから読み込む。（プルダウンで高校名等を指定する）
・データ数は高校ごとに可変。

やりたいことが全部できるのが理想ですが、一部分だけでも構いませんので、教えていただけると幸いです。

No.2 回答者： fujillin 回答日時： 2022/08/02 00:01

こんばんは

ご質問の内容がてんこ盛りなので、個々の部分に回答してはいられなさそうなので、概略としての回答になります。
また、ご提示の表示とは異なる表示方法にしていますので、あくまでもご提示の結果にこだわるのであれば、以下は無視してください。

＞Excel2016で動作すること。（関数／マクロどちらも可）
質問者様がどこまで関数やマクロをご存じなのかが不明ですが、概略を示しただけの全くの丸投げ状態なので、以下は、エクセルの基本機能だけで実現できる方法です。
（office2019で試していますが、2016でも同様のはずと思います）

＜考え方の方針について＞
・グラフはエクセルのバブルチャートで表示するものとし、
　重複はバブルのサイズで表現するものとします。
・色分けしたい部分を、系列で分けて色分けします。
（添付図では、合格、不合格と個人の3系列にしてあります。）
（年度ごとに色を変えたければ、系列を増やすことで可能です）
・個人用のデータを同じ方法で表示するために、添付図では5行目を
　個人用データ専用蘭として固定してあります。
（行数を増やせば、複数人の個人を表示することも可能です）
・個人用データは、C2:D2に入力があれば5行目に関数で反映され、
　その時だけ、I5セルの値も1になるようにしておきます。
・A6:F6以下がご提示の過年度のデータと仮定しています。
　対象校を指定したら、VLOOKUP関数等で6行目以降のデータを入れ
　替える仕組みにしておけば宜しいでしょう。
（方法がわからなければ、「プルダウン　VLOOKUP関数」等で検索
　すれば、似た様な例が見つかると思います。）

＜グラフの作成について＞
・グラフの範囲はC5：I5以降の適当な行数迄を予め指定しておきます。
・系列1（添付図ではG列）に偏差値、内申点が同じ合格者の数を
　カウントします。
　（COUNTIFSなどでカウントできます。）
　（丁寧にやるなら、最初のデータのみ値を入れて後は0とでもする
　　ところですが、重ねて表示されるだけなので、そのままかまわず
　　カウントしてあります）
・同様に、系列2（添付図ではH列）に不合格の数をカウントしています
・I列は個人系列なので、データがある場合は1、無ければ0または空白に
　なるように設定しておきます。
・上記のG：I列をバブルのサイズとして指定できるようにグラフを作成
　します。
　系列1では、横軸をC列（偏差値）、縦軸をD列（内申点）、
　バブルサイズをG列に指定してあります
・系列2、3も同様にして、H列、I列を指定します。


以上で、設定終了です。
A:F列のデータが変われば、それに応じて自動的にグラフの内容も書き換えられるという仕組みです。
（普通に利用されている関数と、グラフ機能だけで実現できる方法になっているはずです）

※ 以上、概略ですがご参考にでもなれば。

No.1 回答者： 銀鱗 回答日時： 2022/08/01 21:40

……で、質問者さんはその作業の中の何が分からないのですか。

ここは「代わりにExcelの表を作ってください」と作業依頼する場所ではありません。
もしもそのつもりでいらっしゃるのであれば、有料サイトで対価を払って作業依頼をしましょう。

ここは「自力で問題を解決するためのアドバイス」を貰う場所ですからね。
何が分からないのかを示すようにしましょう。