学生と彼らが受講したクラスの大規模なデータセットがあります。各学生は、約 80 の利用可能なクラスのうち 12 ～ 18 クラスを受講しています。Excel (2013) を使用して、任意の 2 つのクラスについて、両方のクラスを受講した学生の数を調べたいと思います。80 のクラスを行と列の両方に持つテーブルを想定し、各交差ごとに、その組み合わせを受講した学生の数を確認します。
データは、クラスごとに生徒 1 人あたり 1 行の Excel ファイルとして届きます。
Student Class
Smith E101
Jones E101
Parker E101
Brown E102
Green E102
Smith E201
Jones E202
Parker E201
Brown E202
Green E203
...
想定される出力:
E101 E102 E201 E202 E203 ...
E101 0 2 1 0
E102 0 0 1 1
E201 2 0 0 0
E202 1 1 0 0
E203 0 1 0 0
...
(明らかに、上記の対角半分だけが必要です。もう半分はそれを反転したものです。)
ピボット テーブルを使用して、学生が行、すべてのクラスが列になっているテーブルにデータを取得し、学生が特定のクラスを受講した場所に 1 を表示しました。
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
しかし、その後、手動による介入をできるだけ少なくして、希望する出力を得るにはどうすればよいかに行き詰まってしまいます。
Excel で必要な出力を実現する方法を提案してくれる人はいますか? かなり広範囲に検索しましたが、何も見つかりません。
それとも他のソフトウェアを探したほうがよいでしょうか?
答え1
これは、ピボット テーブルで動作する数式を使用して Excel で簡単に実行できます。
2つのテーブルをこのように設置すると
次の数式を入力しJ2、Ctrl キーを押しながら Enter キーを押すか、コピーして貼り付けるか、下方向に移動して右方向に移動するか、表の残りのセルに自動入力します。
=
IF(
J$1=$I2,
"",
COUNTIFS(
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)),
1,
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)),
1
)
)
説明：
関数の最初の引数は、出力テーブルの列ヘッダーに対応するピボット テーブルの動的に生成された列です。中間評価手順 (セルの場合)COUNTIFS()を見ると、少し理解しやすくなります。L2
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→$D$1:$D$6
(それぞれの 2 番目の引数はINDEX()、ピボット テーブルの完全に動的な開始行と終了行であることに注意してください。)
関数の3番目の引数も同様ですCOUNTIFS()が、今回はピボットテーブルの動的に生成された列が行出力テーブルのヘッダー。セルの場合L2は と評価されます$B$1:$B$6。
したがって、関数COUNTIFS()はL2
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
これは、行（生徒）の数を数える標準的な方法です。両方列には が含まれます1(つまり、学生は両方のクラスに登録されています)。
カプセル化IF()関数は、対角セルが空白であることを確認するためだけに存在します。