こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

エクセルにて、

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

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

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

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

投稿日時 - 2017-04-18 14:32:27

QNo.9318956

困ってます

質問者が選んだベストアンサー

何度も失礼。

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連投の回数としています。

投稿日時 - 2017-04-18 17:47:28

お礼

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

投稿日時 - 2017-04-19 08:22:26

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

貼付画像のデータを使って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連続以上は無い)

投稿日時 - 2017-04-18 19:03:59

お礼

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

投稿日時 - 2017-04-19 08:42:37

ANo.2

前回にVBAで回答したものです。コードの1行に誤りの箇所があったので、訂正させてください。
ーー
訂正後
実行前に初めにSheet2に列見出しをA1:C1に、各々「2連 3連4連」 と入れておく。
もっとデータが多いときはMsgboxの行がうるさいので削除してください。
ーーー
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連
22111
44
11
33
44
44
11
44
33
55
11
55
22
55
44
11
44
22
22
11
11
55
22
22
33
今回結果と元データを粗くチェック済み。

投稿日時 - 2017-04-18 17:14:45

お礼

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

投稿日時 - 2017-04-19 08:20:53

ANo.1

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

投稿日時 - 2017-04-18 15:07:37

お礼

さっそくご回答ありがとうございます。
申し訳ありません。私の説明不足です。(汗)
2連投
課題1 44513
課題2 16343

3連投
課題1 12011
課題2 11000

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

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

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

投稿日時 - 2017-04-18 15:40:54