前回のエクセルの問題の続きです。

質問

同じ内容を再投稿して申し訳ありません。

エクセルにて、

　　A　B　C
一　
二　2　3　5
三　2　5　4
四　1　5　4
五　5　5　2
六　

このように、試験の解答の集計をしており、A,B,C列のそれぞれで、連続した数字のセット個数を数えたい。その中で、A列の２が連続している２連続と、B列の５が３連続している物とを、分けてカウントしたい、また、その二つをかぶらせずに別のものとしてカウントしたいのですが、どのようにしたらいいか、アドバイスを頂ければ嬉しいです。（２連続と３連続のカウント数を別のところで集計してます。）
（３以上は連続したものがありません。２連続か、３連続のどちらかです。
しかし、数字のパターンが１～５の選択問題の為、５パターンあります。）
（また、同じ列に２連続と３連続が混ざり合っており、それが複数出る場合があります。）

ちなみに、今使っている物は
ROUNDDOWN(SUMPRODUCT((A2:A5<>"")*SIGN((A2:A5=A3:A6)+(A2:A5=A1:A4)))/2,0)
という式を使って数えているのですが、B列の５のように３連続が２つきてしまうと、数が２つ増えてしまい、切り捨てできずに１増えてしまう状態です。

お手数ですが、ご回答のほどよろしくお願いします。

回答No.3

mt2015 · ベストアンサー

何度も失礼。

1～5と言うのが課題番号で、課題ごと、列ごとに2連投が何回、3連投が何回かを出したいと言う事ですね。
で、4連投以上は無いと言う前提ならこんな感じでどうでしょう。
添付の図を見てください。質問文に提示の4行しかない例では2連投と3連投が混じった例が出来ないので、勝手に5行の例を作っています。

・A8:A12、A16:A20に1～5の数値を入れておきます。
・B9に↓の式を入れ、B9:D13にコピー
　=SUMPRODUCT(1*($A9=B$2:B$5)*(B$2:B$5=B$3:B$6))-B17*2

・B17に↓の式を入れ、B17:D21にコピー
　=SUMPRODUCT(1*($A17=B$2:B$4)*(B$2:B$4=B$3:B$5)*(B$2:B$4=B$4:B$6))

同じ数が2回続いた2連投の回数と3回続いた3連投の回数を求め、2連投回数－3連投回数×2　を単独で2連投の回数としています。

お礼

何度も回答ありがとうございます。ほんとすいません。（汗）
回答欄を増やすことによって、細分化するという発想がありませんでした。
やはり、発想の転換というのは、とても大事だと痛感させられました！
本当に何度もありがとうございます！

回答No.4

bunjii

貼付画像のデータを使ってExcel 2013で検証してみました。
当方で検証した結果を画像で添付します。
B24=SUMPRODUCT((B2:B20=B3:B21)*(B2:B20<>B1:B19)*(B2:B20<>B4:B22))
B25=SUMPRODUCT((B2:B20=B1:B19)*(B2:B20=B3:B21))
B24:B25を右へオートフィルコピーしました。
数式の考え方は配列同士比較して数値化すれば良いことになります。
B2=B3であり、且つ、B2<>B1で、且つ、B2<>B4のとき2連続になる。
B2=B1であり、且つ、B2=B3のとき3連続になる。（4連続以上は無い）

お礼

ご回答ありがとうございます。
とてもシンプルで、わかりやすいので、すごく参考になり助かります！
さっそく活用してみて、自分の目的と合うかやってみたいと思います！
ご回答ありがとうございます。

回答No.2

imogasi

前回にＶＢＡで回答したものです。コードの１行に誤りの箇所があったので、訂正させてください。
ーー
訂正後
実行前に初めにＳｈｅｅｔ２に列見出しをＡ１：Ｃ１に、各々「2連 　3連 4連」　と入れておく。
もっとデータが多いときはＭｓｇｂｏｘの行がうるさいので削除してください。
ーーー
Sub test01()
Worksheets("Sheet2").Range("A2:AX100").Clear
'Exit Sub
'---
Worksheets("Sheet1").Activate
lc = Worksheets("Sheet1").Range("ax2").End(xlToLeft).Column '最右列
MsgBox lc
lr = Worksheets("Sheet1").Range("a1000").End(xlUp).Row '最下行
MsgBox lr
Worksheets("Sheet1").Activate
'----
For c = 1 To lc '各列列繰り返し
rn = Cells(2, c)
For r = 2 To lr '行繰り返し
If Cells(r, c) = Cells(r + 1, c) Then
'---同じ場合
rn = rn & Cells(r, c)
Else
'--違う場合
If Len(rn) > 1 Then
MsgBox rn '連の最終結果表示
rx = Worksheets("Sheet2").Cells(1000, Len(rn) - 1).End(xlUp).Row + 1
MsgBox rx & "G"
Worksheets("Sheet2").Cells(rx, Len(rn) - 1) = rn
End If
rn = "" '連を消去
'次の行の値を設定
rn = rn & Cells(r + 1, c)

End If
Next r
'--行の最下データ処理後
rn = "" '連を消去
Next c
End Sub
ーー
新しい質問の画像データでテスト
結果　参考
2連 3連
22 111
44
11
33
44
44
11
44
33
55
11
55
22
55
44
11
44
22
22
11
11
55
22
22
33
今回結果と元データを粗くチェック済み。

お礼

前回に引き続き、ご回答ありがとうございます。
なんとなく、こうなるんだろうなぁ。と、漠然としたものぐらいしか、現段階の自分ではわからないので、一つ一つ調べて、この内容を活用させていただきたいと思います！
２度わたり、本当にありがとうございます。

回答No.1

mt2015

=SUMPRODUCT(1*(A1:A4<>A2:A5)*(A2:A5=A3:A6))
先ほどの回答と同じです。考え方としては、列ごとに「上のセルとは値が違い、下のセルとは値が同じ」セルの数を数えています。

お礼

さっそくご回答ありがとうございます。
申し訳ありません。私の説明不足です。（汗）
2連投
課題１　4 4 5 1 3
課題２　1 6 3 4 3

３連投
課題１　1 2 0 1 1
課題２　1 1 0 0 0

という感じで、分けたいのです。

これで式を組んでみたら、２連続も３連続も一緒にカウントをしてしまいました。
やはり、このように、別でカウントをするとしたら、関数では無理なのでしょうか？

私の分量力、説明力の無さでご迷惑をお掛けして申し訳ありません。