エクセルの計算式で質問です

質問

エクセルで連勝連敗数を計算し、それぞれの合計額を計算する方法を教えて下さい。

セルA列に,以下のように数値が並んでいます。マイナス値は負け、プラス値は勝ちです。この列で最大連敗数と最大連勝数を計算し、其々の連敗時、連勝時の合計額も同時に計算できる方法をご存知なら教えて下さい。

A列
¥14,000
¥6,000
¥1,000
¥-11,000
¥21,000
¥-10,000
¥20,000
¥-3,000
¥-11,000
¥-13,000
¥-17,000
¥-36,000
¥9,000
¥5,000
¥21,000

回答No.9

kagakusuki · ベストアンサー

どうしてANo.8の補足欄に記されておられる様な結果になったのか、明確には判りかねますが、少なくとも私の使用しているパソコン上では、ここまでの私の回答にある方法で、正しい結果が表示されております。
　断言する事は出来ませんが、おそらくは色々と弄られておられる間に、数式を誤ったものに変更されてしまったのではないかと推測致します。
　ここまでの私の回答で示した数式を以下に再度列記致しますので、各セルの数式を再度入力し直されては如何でしょうか。

Sheet1のD2セルの数式

=IF(COUNT($A:$A,">0"),MAX(Sheet2!$A:$A),"")

Sheet1のE2セルの数式

=IF(COUNTIF($A:$A,"<0"),MIN(Sheet2!$A:$A),"")

Sheet1のD3セルの数式

=IF($D2="","",COUNT($A:$A)-MAX(Sheet2!$C:$C))

Sheet1のE3セルの数式

=IF($E2="","",COUNT($A:$A)+MIN(Sheet2!$C:$C))

Sheet2のA1セルの数式

=IF(OR($B1="",SIGN(($B1&"0")+0)=SIGN(($B2&"0")+0)),"",SUM(INDEX(Sheet1!$A:$A,ROW()-ABS($B1)+1):INDEX(Sheet1!$A:$A,ROW())))

Sheet2のB1セルの数式

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW()-1)),ROW()>1),INDEX(B:B,ROW()-1)*(SIGN(INDEX(Sheet1!$A:$A,ROW()-1))=SIGN(INDEX(Sheet1!$A:$A,ROW()))),0)+SIGN(INDEX(Sheet1!$A:$A,ROW())),"")

Sheet2のC1セルの数式

=IF(AND(ISNUMBER($A1),OR($A1=Sheet1!$D$2,$A1=Sheet1!$E$2)),COUNT(Sheet1!$A:$A)*SIGN($B1)-$B1,"")

　尚、上記の数式を各セルに入力した後、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付ける事も、忘れずに行って頂く様御願いします。

お礼

返信遅くなりました。　どうもすみません。

今回まとめて頂いた事を再度試したところ、数値も変わり確かめましたが、間違いないです。どうもありがとうございました。

解決まで教えて頂き、本当に助かりました。感謝しています。

どうもありがとうございました。

回答No.8

kagakusuki

>今回教えて頂きました数式でも、Sheet1のD4とE4の数値は変わりません。D3とE3にそれぞれ最大連勝時の金額と最大連敗時の金額が表示され、D４とE4には、その時の最大連勝数と最大連敗数が表示されると理解していますが、

　Sheet1のD列とE列がANo.5で示した例と比較して、1行だけ下方にずれている様に思います。
　ANo.5以降の私の回答にある数式は、

Sheet1のD2セルに、連勝時の合計金額が最大となっている箇所の合計金額を、、
Sheet1のE2セルに、連敗時の合計金額が最もマイナス方向に傾いている箇所の合計金額を、
Sheet1のD3セルに、連勝時の合計金額が最大となっている箇所の内、連勝数が最小となっている箇所の連勝数を、
Sheet1のE3セルに、連敗時の合計金額が最もマイナス方向に傾いている箇所の内、連敗数が最小となっている箇所の連敗数を、

それぞれ表示する場合の数式です。
　特に、Sheet1のD2セルとE2セルの値は、Sheet1のD3セルとE3セル及びＳｈｅｅｔ2のC列の値を計算する際にも、データとして使用されるものですから、

Sheet1のD2セルに、連勝時の合計金額が最大となっている箇所の合計金額が、、
Sheet1のE2セルに、連敗時の合計金額が最もマイナス方向に傾いている箇所の合計金額が、

表示されていない場合には、正常な動作は行われません。
　再度、セルの配置と数式中の参照先の関係を、確認し直して頂くよう御願いします。
　又もしも、質問者様御自身では、セルの配置と数式中の参照先の関係を確認するのが大変な場合には、お礼欄か或いは補足欄を使用して、元となるデータがどのシートの何列の何行目から始まっていて、それを基に、どのシートのどのセルに、どの様な結果を表示させたいと考えておられるのかを、余すところなく御知らせ頂ければ、その情報に沿った数式を組む事も可能になるかと思います。

補足

何度もご教示頂き本当にありがとうございます。本当に助かります。

セルの配置や数式中の参照先等は教えて頂いた例に従っておりますので、こちらで特にどこと言うのはありません。

ただ、何度も何度も質問ばかりでは恐縮ですので、最初に皆様に質問させて頂いた例を使い、これまで教えて頂いた数式に入力した結果、今どういう表示になっているかをそれぞれSheet1とSheet2 の状態を以下に記します。

大変お手数ですが、ご確認して頂けますかでしょうか?

先ず、以下Sheet1です。

　A　　　　　　　　　　　D　　　　E
¥14,000
¥6,000 105,000 -400,000
¥1,000 11 13
¥-11,000
¥21,000
¥-10,000
¥20,000
¥-3,000
¥-11,000
¥-13,000
¥-17,000
¥-36,000
¥9,000
¥5,000
¥21,000

次にSheet2です。

A　　　B　　　C　
1
2
63000 3
-11000 -1
21000 1
-10000 -1
20000 1
-1
-2
-3
-4
-400000 -5 -2
1
2
105000 3 4

最初に皆様にご質問した例で行けば、5連敗で金額が80000円。連勝は3連勝で金額が35000円という様になるのではないでしょうか?しかし、計算するとD列の2行目には105000円で連勝は11、E列の2行目には-400000円で13連敗となります。

本当に申し訳ありませんが、もう一度ご確認お願い致します

回答No.7

kagakusuki

何度も申し訳御座いません。
　Sheet2の数式を組んで行く際に、1行目と2行目のみ修正し忘れて、古い数式が残っていて、それを気付かずにそのままANo.5に記載してしまっておりました。（1行目と2行目は結果に影響しないため、見落としておりました）
　ですから、Sheet2のA1セルの数式を

=IF(OR($B1="",SIGN(($B1&"0")+0)=SIGN(($B2&"0")+0)),"",SUM(INDEX(Sheet1!$A:$A,ROW()-ABS($B1)+1):INDEX(Sheet1!$A:$A,ROW())))

に変更し、Sheet2のC1セルの数式を

=IF(AND(ISNUMBER($A1),OR($A1=Sheet1!$D$2,$A1=Sheet1!$E$2)),COUNT(Sheet1!$A:$A)*SIGN($B1)-$B1,"")

に変更して下さい。
　Sheet2のB1セルの数式はANo.5に記したものと同じままで構いません。

お礼

何度もご教示頂きありがとうございました。

今回教えて頂きました数式で試しましたところ、Sheet1のD3とE3の数値は変わり、妥当な数値になったと思います（100%正確には未だ確認していませんが）。

ただ、今回教えて頂きました数式でも、Sheet1のD4とE4の数値は変わりません。D3とE3にそれぞれ最大連勝時の金額と最大連敗時の金額が表示され、D４とE4には、その時の最大連勝数と最大連敗数が表示されると理解していますが、今表示されているのは、Sheet2のC列の数値が表示されるべき最後のセルの次のセルです。

また、引き続きSheet2のC列には何も表示されません。

大変お手数かとは思いますが、もう一度ご確認お願い出来ないでしょうか?

よろしくお願いします。

回答No.6

kagakusuki

>教えて頂いた通りやってみましたが、何度試してもSheet2のC列が教えて頂いた式では最初から最後まで０が表示されます。A列とB列には数字が表示されます。

　申し訳御座いません、何か勘違いをしていたらしく、ANo.5で挙げたSheet1のD2セルとE2セルの数式が、添付画像のExcel表を作成した際に入力したものとは異なっておりました。
　正しくは、Sheet1のD2セルに入力する数式は

=IF(COUNT($A:$A,">0"),MAX(Sheet2!$A:$A),"")

で、Sheet1のE2セルに入力する数式は

=IF(COUNTIF($A:$A,"<0"),MIN(Sheet2!$A:$A),"")

でした。
　御迷惑をかけて、申し訳御座いませんでした。

お礼

お礼の返信遅れてすみません。どうもありがとうございました。

追加でのアドバイス感謝致します。今回教えて頂いた計算式でSheet1のD2とE2に数値が反映されました。しかし、表示されている最大連勝数の金額とその合計、最大連敗数の金額とその合計の数値が違うと思います。

Sheet2に表示される筈のC列は空欄です。教えて頂いた計算式をC1に入力して下にコピーしています。

感覚的に、Sheet1にはSheet2のA列から最大連勝や連敗の金額を計算してSheet1に表示し、Sheet2のB列から連勝数や連敗数を計算しているのではないかと思っていますが。。。

大変お手数ですが、再度確認して頂けないでしょうか?

よろしくお願いします。

回答No.5

kagakusuki

回答番号ANo.1、4です。

>この場合は連続回数の最も少ないものを教えて下さい。

　それでしたらまず、Sheet2のB1セルに次の数式を入力して下さい。（ANo.4と同じ式）

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW()-1)),ROW()>1),INDEX(B:B,ROW()-1)*(SIGN(INDEX(Sheet1!$A:$A,ROW()-1))=SIGN(INDEX(Sheet1!$A:$A,ROW()))),0)+SIGN(INDEX(Sheet1!$A:$A,ROW())),"")

　次に、Sheet2のA1セルに次の数式を入力して下さい。（ANo.4と同じ式）

=IF(OR($B1="",SIGN(($B1&"0")+0)=SIGN(($B2&"0")+0)),"",SUM(INDEX(Sheet1!$A:$A,ROW()-ABS($B1)+1):INDEX(Sheet1!$A:$A,ROW()))*ABS($B1))

　次に、Sheet2のC1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($A1),OR($A1=Sheet1!$D$2,$A1=Sheet1!$E$2)),COUNT($A:$A)*SIGN($B1)-$B1,"")

　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet1のD2セルに次の数式を入力して下さい。（ANo.4と同じ式）

=IF($D3="","",MAX(Sheet2!$C:$C))

　次に、Sheet1のE2セルに次の数式を入力して下さい。（ANo.4と同じ式）

=IF($E3="","",MIN(Sheet2!$C:$C))

　次に、Sheet1のD3セルに次の数式を入力して下さい。

=IF($D2="","",COUNT($A:$A)-MAX(Sheet2!$C:$C))

　次に、Sheet1のE3セルに次の数式を入力して下さい。

=IF($E2="","",COUNT($A:$A)+MIN(Sheet2!$C:$C))

　以上です。

お礼

どうもありがとうございます。

教えて頂いた通りやってみましたが、何度試してもSheet2のC列が教えて頂いた式では最初から最後まで０が表示されます。A列とB列には数字が表示されます。

Sheet１のC列で数字が出ないので、Sheet2では計算が出来ません。

すみません、このC列の確認よろしくお願い致します。

回答No.4

kagakusuki

回答番号ANo.1です。

>これを、連勝と連敗時に連勝利益とれんぱい損失の其々の金額ベースの最大連勝と最大連敗を記録するにはどうすれば宜しいでしょうか？

　つまり、連続回数が最大となっている箇所ではなく、連続している部分ごとの合計の最大値と最小値を求めて、同時に、その該当箇所における連続回数を求めたい、という事なのでしょうか？
　その場合問題となりますのは、連続している部分ごとの合計値が、同値で最大値、或いは同値で最小値となっている箇所が複数存在している場合には、連続回数は最も多いものを表示すべきなのか、それとも最も少ないものを表示すべきなのか、どちらが宜しいのでしょうか？
　取り敢えず、連続回数に関しては、合計値が最大になっている箇所や最小になっている箇所における連続回数が最も多い回数となっているものを表示するものとします。

　まず、Sheet2のB1セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW()-1)),ROW()>1),INDEX(B:B,ROW()-1)*(SIGN(INDEX(Sheet1!$A:$A,ROW()-1))=SIGN(INDEX(Sheet1!$A:$A,ROW()))),0)+SIGN(INDEX(Sheet1!$A:$A,ROW())),"")

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(OR($B1="",SIGN(($B1&"0")+0)=SIGN(($B2&"0")+0)),"",SUM(INDEX(Sheet1!$A:$A,ROW()-ABS($B1)+1):INDEX(Sheet1!$A:$A,ROW()))*ABS($B1))

　次に、Sheet2のC1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($A1),OR($A1=Sheet1!$D$3,$A1=Sheet1!$E$3)),Sheet2!$B1,"")

　次に、Sheet2のA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF($D3="","",MAX(Sheet2!$C:$C))

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=IF($E3="","",MIN(Sheet2!$C:$C))

　次に、Sheet1のD3セルに次の数式を入力して下さい。

=IF(COUNT($A:$A,">0"),MAX(Sheet2!$A:$A),"")

　次に、Sheet1のE3セルに次の数式を入力して下さい。

=IF(COUNTIF($A:$A,"<0"),MIN(Sheet2!$A:$A),"")

　以上です。

お礼

返信ありがとうございました。

つまり、連続回数が最大となっている箇所ではなく、連続している部分ごとの合計の最大値と最小値を求めて、同時に、その該当箇所における連続回数を求めたい、という事なのでしょうか？

=>そうです。金額ベースで連敗時の最大マイナス値と連勝時の最大プラス値を知りたいです。それが２連敗とかで、連続回数では少なくとも連続時の金額ベースでは値は最大というのを知りたいです。

その場合問題となりますのは、連続している部分ごとの合計値が、同値で最大値、或いは同値で最小値となっている箇所が複数存在している場合には、連続回数は最も多いものを表示すべきなのか、それとも最も少ないものを表示すべきなのか、どちらが宜しいのでしょうか？

=>この場合は連続回数の最も少ないものを教えて下さい。５回連続で-1000と２回連続で-1000ならば、2回連続の方が表示されると助かります。

度々すみません。宜しくお願いします。

回答No.3

KURUMITO

ごめんなさい回答2です。以下の文章は

合計の金額が100万円以上になる場合があるのでしたら10^6を10^-7にします。

10^-7となっているのは10^7の誤りでした。

回答No.2

KURUMITO

連勝や連敗数が同じ場合にはその合計額を決めることが難しくなりますね。合計額の絶対値が多い方を採用することにします。そこで作業列を作って対応するのがよいでしょう。
例えばＡ1セルは項目名などでＡ２セルから下方にお示しのデータが入力されているとします。
Ｂ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(ROW(A1)=1,IF(A2>0,1*10^6+A2,-1*10^6+A2),IF(AND(A1*A2<0,A2*A3>0),IF(A2>0,1*10^6+A2,-1*10^6+A2),IF(A1*A2>0,B1+IF(A2>0,1*10^6+A2,-1*10^6+A2),0))))

合計の金額が100万円以上になる場合があるのでしたら10^6を10^-7にします。
作業列が目障りでしたらＢ列を選択して右クリックし「非表示」を選択すればよいでしょう。
Ｄ1セルに連勝数、Ｄ2セルに合計金額、Ｆ1セルには連敗数、Ｆ2セルには合計金額と入力することにしてＥ1セルには次の式を入力します。

=INT(MAX(B:B)*10^-6)

Ｅ2セルには次の式を入力します。

=MOD(MAX(B:B),10^6)

Ｇ1セルには次の式を入力します。

=INT(-MIN(B:B)*10^-6)

Ｇ2セルには次の式を入力します。

=MOD(-MIN(B:B),10^6)

お礼

本当にありがとうございました。

教えて頂きました方法でも出来ました。助かりました。　どうもありがとうございました。

回答No.1

kagakusuki

今仮に、元の数値がA列に並んでいるシートのシート名がSheet1であるものとして、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW()-1)),ROW()>1),INDEX(A:A,ROW()-1)*(SIGN(INDEX(Sheet1!$A:$A,ROW()-1))=SIGN(INDEX(Sheet1!$A:$A,ROW()))),0)+SIGN(INDEX(Sheet1!$A:$A,ROW())),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。
　次に、Sheet1のD1セルに「最大連勝数」、E1セルに「最大連敗数」、C3セルに「合計」と入力して下さい。
　次に、Sheet1のD2セルに次の数式を入力して下さい。

=MAX(Sheet2!$A:$A)

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=-MIN(Sheet2!$A:$A)

　次に、Sheet1のD3セルに次の数式を入力して下さい。

=IF($D$2=0,0,SUM(INDEX($A:$A,MATCH($D$2,Sheet2!$A:$A,0)-$D$2+1):INDEX($A:$A,MATCH($D$2,Sheet2!$A:$A,0))))

　次に、Sheet1のE3セルに次の数式を入力して下さい。

=IF($E$2=0,0,SUM(INDEX($A:$A,MATCH(-$E$2,Sheet2!$A:$A,0)-$E$2+1):INDEX($A:$A,MATCH(-$E$2,Sheet2!$A:$A,0))))

　以上です。

お礼

早速のご回答ありがとうございました。いろいろと数字変更して時間が掛かってしまいました。計算できました。本当にありがとうございました。とても助かりました。

因みに、最大連敗数は必ずしも連敗時の最大損失金額を意味しません。

これを、連勝と連敗時に連勝利益とれんぱい損失の其々の金額ベースの最大連勝と最大連敗を記録するにはどうすれば宜しいでしょうか？