EXCELで関数を使ったデータ分析

質問

質問タイトルを上手くまとめきれませんでした。
ご協力をお願いします。
EXCEL(2000)でこのような事ができますか？
例えば、下記の表のように、
毎月のテストの結果をEXCELで一覧表にまとめてあったとします。

　　　　人　国語　算数　理科　社会
　１月　Ａ　合格　　　　　　　合格　
　　　　Ｂ　合格　合格　合格　合格
　　　　Ｃ　　　　合格　合格　合格
　２月　Ｂ　合格　合格　合格
　　　　Ｃ　合格　合格　合格　合格
　　　　Ｄ
　　　　　　～中略～
１１月　Ａ　　　　合格　　　　合格
　　　　Ｂ　合格　合格　合格　合格　
　　　　Ｃ　　　　合格　合格
　　　　Ｆ　合格　合格　合格　合格
１２月　Ａ　　　　合格　　　　合格
　　　　Ｃ　合格　合格　合格　合格　
　　　　Ｅ　　　　合格　合格
　　　　Ｆ　合格　合格　合格　合格

＊A～Ｆの人は、毎月テストを受けている訳ではありません。
　受けない月もあります。
ここで調べたいのは、２つ有りまして、
(1)「＊さんは国語のテストを１２月から遡って見たときに何回連続で合格をしていたか？」
（１１・１２月連続合格で１０月が不合格だったら、２回と表示させたい）
(2)「＊さんは国語のテストでこの一年間での最高連続合格回数は何回だったか」

エクセルで難しいと思われた方、このような集計に適したソフトをご存知の方も、
情報を教えて下さい。

回答No.3

imogasi

全提
（１）ある生徒が１ヶ月で２回以上テストは無い
（２）ある生徒がある月はテストを受けないことはある
（３）「１２月から遡って見たときに」は、ある時点で考える際は、７月ー１２月のように固定して考えてよいか。
でよいか。
ーー
国語だけの列のことをとりあえず考えればよいと思うが、
連続と言う条件が関数では難しいと思う。
「月」列（質問ではＡ列か）は毎（行）データに入れないと複雑化する。

例データ
月 人 国語 算数 理科 社会
9 Ａ 合格 合格
9 Ｂ 合格 合格 合格 合格
9 Ｃ 合格 合格 合格
10 ２月 Ｂ 合格 合格 合格
10 Ｃ 合格 合格 合格 合格
10 Ｄ
11 Ａ 合格 合格
11 Ｂ 合格 合格 合格 合格
11 Ｃ 合格 合格
11 Ｆ 合格 合格 合格 合格
12 Ａ 合格 合格
12 Ｃ 合格 合格 合格 合格
12 Ｅ 合格 合格
12 Ｆ 合格 合格 合格 合格
（１）まず上記（３）で言った、７月ー１２月の期間のデータ以外を削除する。
それには、月（A列）でソートし対象以外の行部分を削除する。
月 人 国語 算数 理科 社会
10 ２月 Ｂ 合格 合格 合格
10 Ｃ 合格 合格 合格 合格
10 Ｄ
11 Ａ 合格 合格
11 Ｂ 合格 合格 合格 合格
11 Ｃ 合格 合格
11 Ｆ 合格 合格 合格 合格
12 Ａ 合格 合格
12 Ｃ 合格 合格 合格 合格
12 Ｅ 合格 合格
12 Ｆ 合格 合格 合格 合格
こういうのは、もちろんコピーしたシートで行う。
（２）（１）のデータを生徒別（B列か）＋月別でソート
月 人 国語 算数 理科 社会
11 Ａ 合格 合格
12 Ａ 合格 合格
10 Ｂ 合格 合格 合格
11 Ｂ 合格 合格 合格 合格
10 Ｃ 合格 合格 合格 合格
11 Ｃ 合格 合格
12 Ｃ 合格 合格 合格 合格
10 Ｄ
12 Ｅ 合格 合格
11 Ｆ 合格 合格 合格 合格
12 Ｆ 合格 合格 合格 合格
これで目視でも結果の表を出すことが出来ると思うがが
ーーー
VBAででもやってみるか。
Sub test01()
d = Range("A65536").End(xlUp).Row
MsgBox d
t = Cells(2, "A")
m = Cells(2, "B")
k = 1
ren = 0
'---
For i = 3 To d
If Cells(i, "B") = m Then
If Cells(i, "C") = "合格" And Cells(i, "A") = t + 1 Then
ren = ren + 1
Else
Cells(k, "H") = m
Cells(k, "I") = ren
k = k + 1
End If
t = Cells(i, "A")
m = Cells(i, "B")
Else
Cells(k, "H") = m
Cells(k, "I") = ren
k = k + 1
' ---
ren = 0
t = Cells(i, "A")
m = Cells(i, "B")
End If
Next i
Cells(k, "H") = m
Cells(k, "I") = ren
End Sub
ーー
結果
H1：I6
Ａ 1
Ｂ 1
Ｃ 2
Ｄ 0
Ｅ 0
Ｆ 1
1は連続合格２回（合格ー合格）、２は連続合格３回（合格ー合格ー合格）を示すものです。
時間が無いので、月を飛んで連続合格が起こるケースをテストできてない。つみませんよろしく。
このコントロールのロジックは少し難しいと思う。

お礼

詳しくご回答頂き有難うございます。
全提
（１）ある生徒が１ヶ月で２回以上テストは無い
実は、月4回あります。例を簡略した方が分りやすいかとおもって、省略しました。
（２）ある生徒がある月はテストを受けないことはある
その通りで。
（３）「１２月から遡って見たときに」は、ある時点で考える際は、７月ー１２月のように固定して考えてよいか。
少し、ニュアンスがちがうのですが、最近の調子を見たいので、
昨日（例の表では１２月から）以前での直近の状態をみてみたいのです。

私は、高校の時にBASICをちょっといじった程度で、
VBは触った事がありません。
今回の事を機にやってみようかと思っています。
ご回答いただいたVBAで挑戦してみます。
少し、やってみます。
上手く出来なければ、補足説明で質問させて頂きます。
ご迷惑でなければ、お付き合い下さい。

回答No.2

jijyho

全体の集計を取らないのでしたら関数ではなくデータフィルタ→オートフィルタを使えば個人を選択できます。データはNO.1さんに書いているように個人ごとに月をもたせること、科目は人によってずれていなければ使えます。ついでに合格を数字の１に変換しとけば集計もできます。ただ、連続での合格回数はちょっと細工しなければ無理ですね。
もう一つはデータ→ピポットテーブルを使うかです。
合格→100・不合格→１・受けていない→０等に変換すれば集計も簡単です。ただこれも、連続合格回数にはちょと無理があるのでさ作表後の工夫が必要です。

お礼

ご回答有難うございます。
（補足欄へ記入してしまい失礼しました。）
例は簡略化していますが、実際には人数が10,000人分ほどあり、
科目も７種類あります。
これを一人ずつ手作業で操作していると、時間がいくらあっても足らない ><
どちらかと言うと、調子がどうなのか？を把握したいので、
もう少し考えて見ます。

補足

ご回答有難うございます。
例は簡略化していますが、実際には人数が10,000人分ほどあり、
科目も７種類あります。
これを一人ずつ手作業で操作していると、時間がいくらあっても足らない ><
どちらかと言うと、調子がどうなのか？を把握したいので、
もう少し考えて見ます。

回答No.1

noname#62235

EXCEL(2000)でこのような事ができますか？
という質問に対しての回答は、可能です。というか、Excelにはほとんど不可能というものがないので、Excelで不可能な処理は他のどのようなソフトを使用しても容易にはできないと思います。

ご要望の処理にぴったり合った関数などはないと思いますので、結論としてはマクロで組むことになると思います。
その際、データの形式（データ構造）は今のままでもマクロの組みようによってはもちろん処理できるのですが、もっと簡単に処理できるようにするためデータを正規化するのが良いと思います。

正規化というのはデータを以下のような形式にすることです。

月　　　人　科目　合否
１月　　Ａ　国語　合格
１月　　Ｂ　国語　合格
１月　　Ｃ　国語　不合格
：
１２月　Ｆ　社会　合格

例示されているデータを正規化すると６０個のデータ（レコードといいます）になります。
もし自動的に結果を表示させたりということを望まないのであれば、このような形式にデータを変換するだけで目的の処理は実現できます。
たとえばＡさんの連続合格回数を知りたい場合、「オートフィルタ」を使って「Ａさん」の「国語」のみをフィルタリングします。
その結果、月の部分を見て連続していればそれは連続合格を意味します。

マクロを組んで連続合格回数のカウントを自動化する場合も基本的には上記のロジックと同じことをマクロで実現します。ループを組んで数えてもいいですし、AdvancedFilter関数を使ってオートフィルタと同じ処理を実現してもかまわないと思います。

お礼

ご回答有難うございます。
例は簡略化していますが、実際には人数が10,000人分ほどあり、
科目も７種類あります。
これを一人ずつ手作業で操作していると、時間がいくらあっても足らない ><
マクロや、VBAはいままで触った事がなく、必要性は感じていたので、
今回の事を機に触ってみたいとおもいます。