エクセルで連続回数などを調べたいです。

質問

エクセルで連続回数などを調べたいです。
なるべく簡単に出来る方法があればぜひ宜しくお願いします。

例えば

　　　　　A列　　　B列
1行目　　　い　　　 64
2行目　　　い　　　100
3行目　　　あ　　　 30
4行目　　　あ　　　 40
5行目　　　あ　　　 20
6行目　　　い　　　-77
7行目　　　い -60
8行目　　　あ　　　-80
9行目　　　あ　　　-50
10行目　　 あ -100
・　　　　・
・　　　　・
・
800行目

というように数字が入っているとして、

1．A列が「あ」で、B列に「正」が連続で出てきた回数の最も多い回数を調べたい。
　（A列が「い」の場合や、「正」では無く「負」の場合も）

2．さらに、1の結果それぞれの数字を合計したい。

というときはどんな式になりますでしょうか？

上の、1行目～10行目で言えば、

「あ」の場合の「正」の最大連続回数は「3」
「い」の場合の「正」の最大連続回数は「2」

「あ」の場合の「負」の最大連続回数は「3」
「い」の場合の「負」の最大連続回数は「2」

それぞれの合計数は
「あ」の「正」の最大連続回数「3」の合計数が「90」
・・・・

のように出したいのです。

それぞれの答えを入力するセルは違います。

こちら、関数もよく分からない状態です・・・。

マクロなどは、「マクロ」という言葉しか分からない状態です。（すいません・・・）

大変お手数をおかけ致しますが、お詳しい方がいらっしゃれば、どうぞ宜しくお願いします。

回答No.1

KURUMITO · ベストアンサー

お示しの表のデータが２行目から下方に有るとします。Ｃ列は作業列としてＣ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(AND(A1=A2),C1+1+ABS(B2)/10000,1+ABS(B2)/10000))

次に答えの表ですが例えばＤ２セルから下方にあ、い、などとＡ列で使われている文字列を入力します。
Ｅ１セルには正の最大連続回数、Ｆ１セルには不の最大連続回数、Ｇ１セルには正の最大連続回数における合計値、Ｈ１セルには不の最大連続回数における合計値と入力します。
Ｅ２セルには次の式を入力して式を決定する段階でCtrlキーとShiftキーを同時に押しながらEnterキーを押します。配列形式といって数式バー上の式には前と後ろの部分に{　}が付いた状態の式となります。この式をＨ２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($D2="","",IF(COLUMN(A1)=1,INT(MAX(IF(($A$2:$A$800=$D2)*($B$2:$B$800>0),$C$2:$C$800))),IF(COLUMN(A1)=2,INT(MAX(IF(($A$2:$A$800=$D2)*($B$2:$B$800<0),$C$2:$C$800))),IF(COLUMN(A1)=3,MOD(MAX(IF(($A$2:$A$800=$D2)*($B$2:$B$800>0),$C$2:$C$800)),1)*10000,IF(COLUMN(A1)=4,MOD(MAX(IF(($A$2:$A$800=$D2)*($B$2:$B$800<0),$C$2:$C$800)),1)*-10000,"")))))

補足

ご回答ありがとうございます。

新たに表を作成してみて、教えていただいたとおりに入力してみました。
結果、数字が出てちゃんと式が成り立っているようです。

ただ、私の伝え方が悪かったのだと思いますが、思っていたのと違う感じでした・・・。

A列には「あ」と「い」の2種類の文字がランダムに入っています。
B列には、「正の数字」と「負の数字」がランダムに入っています。

※「あ」なおかつ「正の数字」という同じ条件が縦に2つ連続する場合や3つ連続する場合や4つ連続する場合などさまざまです。
（「あ」で「負」・「い」で「正」・「い」で「負」もです）

そこで、

C2セル→「あ」でなおかつ「正の数字」の最大連続回数。（※の例で言えば「4回」）
D2セル→「あ」でなおかつ「負の数字」の最大連続回数。
E2セル→「い」でなおかつ「正の数字」の最大連続回数。
F2セル→「い」でなおかつ「負の数字」の最大連続回数。

G2セル→C1セルの最大連続回数が4回なら、その4つの数字の合計値。
H2セル→上と同じくD2セルに出た最大連続回数の合計値。
I2セル→上と同じくE2セルに出た・・・・
J2セル→上と同じくF2セルに出た・・・・

というように、答えを出すことは出来ますでしょうか？
（上に書いた答えを入れるセル「C2セル」などは例えです。どこでもいいんですが）

【例】

あ　100　　　　←の場合、「あ」なおかつ「正」の連続は2回と3回があり、一番大きい回数は「3」。
あ　100　　　　　　　　　求めたい最大連続数は「3」となる。
あ　100　　　　　　　　　そして、求めたい最大連続数の合計値は「300」となる。
い　100
い　100
い　100
あ　100
あ　100
い　100
い　100
あ　-100
あ　-100
あ　-100
い　-100
い　-100
い　-100
あ　-100
あ　-100
い　-100
い　-100

というような感じです・・・。

上の表のように、少なかったらもう手作業で数えようかとも思うのですが・・・。

本当に説明が下手で申し訳ありません。

あと、もし最大連続回数が「4」として、4回連続するのが何度かある場合は、
一番合計値が高い数値が欲しいです。

どうぞ宜しくお願いします。

回答No.4

layy

簡単とは言っても求めることがいろいろあるので、そうもいきません。

正か負か
前行と同じ「あ」か
同じ「あ」なら連続ｎ番めか、ちがえば１にしたか
前行との合計はいくらか、ちがえば０にしたか

最大はどれか、その合計はいくつか

これだけのことをするので、Ｃ列Ｄ列以右列を有効に使うことです。
機能分けて組み立てを考えてどこまでできますか。関数で使えるものあります。

回答で飛躍しすぎて何やっているかわからないようなら、さらに説明もらうか、今回は使わない、が良いです。関数とか覚えて来たら使うでいいでしょう。

ＶＢＡを使うとまた違った手法になります。

お礼

お礼が遅くなり申し訳ございません。

無事、求めたい数字を出すことが出来ました。

もっと勉強したいと思います。

何度もご回答を頂きまして、本当にありがとうございました。

回答No.3

KURUMITO

回答Ｎｏ１です。こちらが示した通りに作業列を作って試験されているのでしょうね。
あ　の文字をＤ２セルに入力することで、「あ」で正の最大連続回数はＥ２セルに、「あ」で負の最大連続回数はＦ２セルに、「あ」で正の最大連続回数での合計はＧ２セルに、「あ」で負の最大連続回数での合計はＨ２セルに表示されますね。
同様に　い　の文字をＤ３セルに入力することで、「い」で正の最大連続回数はＥ３セルに、「い」で負の最大連続回数はＦ３セルに、「い」で正の最大連続回数での合計はＧ３セルに、「い」で負の最大連続回数での合計はＨ３セルに表示されますね。

あなたが繰り返して述べておられますがＮｏ１で回答した方法は「あ」や「い」の文字が、また、正や負の数値がばらばらに入力されていても連続した状態を調べてその連続が最大の回数を求めているのです。また、同じ最大の回数になっている場合にはその合計が最も大きい合計を求めています。
Ａ２セルとＢ２セルから下方にあなたが示されたような組み合わせをたくさん入力してみてください。
Ｅ２セルからＨ３セルの範囲に正しい答えが表示されることでしょう。

あなたはエクセルの初心者といっておられますがご質問の内容は高度なものです。式の内容を理解することは難しいかもしれませんが努力してください。

お礼

KURUMITO様

お世話になっております。

出来ました！

NO1で頂いたご回答を何度も試して、きちんと求めたい数字が出すことが出来ました。
（出来なかった時はどこで間違っていたのかが結局分からないままですが・・・）

何度もご回答を頂きまして、本当にありがとうございました。

補足

再度のご回答をありがとうございます。

少し前進しました。

最初に頂いたご回答の通りに、試験をしていたつもりでしたが、

＞Ｄ２セルから下方にあ、い、などとＡ列で使われている文字列を入力します。

という所を間違って解釈しており、A列の「あ」「い」の並びを
そのままD２セルから下へコピーしてしまっていました。

その為、E～H列にたくさんの数字が出てきてしまっていたので、
そこで戸惑ってしまっていました。

今回頂いたご回答で、D2のセルだけに「あ」という文字。D3のセルだけに「い」の文字を
入力するということがやっと分かりました。

やってみたところ、E～Hの2、3セルだけにそれぞれ数字が出ました。

でも、全部「0」と出るので、まだ何か間違いを犯しているのだと思います。

あとは自分なりに頑張ってやってみます。
（KURUMITO様のご回答を私がどこかで間違えているはずなので・・・）

エクセルの初心者ですが、調べながら他の関数を知り、使い、なんとか表が出来てきました。

質問させて頂いた箇所で、どうにもこうにも進める事が出来なくなって、
質問をしてしまったのですが、甘えだったのかも知れません。

努力はしていたつもりですが、出来きれていなかったのかも知れません・・。

質問内容がそれほど高度なことだという事も分からない位でした。
ご迷惑をおかけして申し訳ありませんでした。

ちゃんと完成したら補足では無くお礼を書かせて頂きたいので
今はこちらから失礼します。

回答No.2

layy

数式としては
C1に1、D1に64、
C2に2、D2に164、
E3に1、F3に30、
E4に2、F4に70、
E5に3、F5に90、
となるように考えると
MAX関数が使えます。
CDとEFに分けるのは、あだけ、いだけ、の区別です。

あ、い、２通りしかないのでしょうか。

連続３回がほかにもあって２回以上出現したらどういう結果を期待しますか。

補足

ご回答ありがとうございます。

私の説明不足で、申し訳ありません。

NO1の回答者様へも補足をさせて頂きました。

宜しければ見ていただけますか？

なお、

＞あ、い、２通りしかないのでしょうか。

はい。2通りです。

＞連続３回がほかにもあって２回以上出現したらどういう結果を期待しますか。

一番合計値が大きいものが欲しいです。