Excel 「2列連続してマイナスの数値が入っている場所の、その直後のセルの数値がプラスである」確率

質問

数学やExcelが苦手で困っています。
どなたかお力を貸して下さい。

Excel2002以降の環境下での場合です。

1行に100個程度の数値が入っている状況です。
その行の中で、「2列連続してマイナスの数値が入っている場所の、その直後のセルの数値がプラスである」確率を導き出したいのですが、可能でしょうか？

確率自体は、統計関数の何れかを使えば出来そうですが、肝心なところはさっぱり分かりません。
IFを使ってマイナスならば1行下に「1」とか何らかの値を入れて、COUNTIFで手前のセルに値があるか見るという方法も一旦は思いついたのですが、手前のセルとか直後のセルとかを数式でどう表現して良いかも分からず・・・・挫折してしまいそうです。宜しくお願いします。

回答No.4

sige1701 · ベストアンサー

No3　さんの回答を利用して

=SUMPRODUCT((SIGN($A1:$CT1)=-1)*(SIGN($B1:$CU1)=-1)*(SIGN($C1:$CV1)=1))/SUMPRODUCT((SIGN($A1:$CT1)=-1)*(SIGN($B1:$CU1)=-1))

=SUMPRODUCT(($A1:$CT1<0)*($B1:$CU1<0)*($C1:$CV1>0))/SUMPRODUCT(($A1:$CT1<0)*($B1:$CU1<0))

お礼

ご回答有難う御座います。大分求めるものの完成形に近い計算式なようで、とても嬉しいです。初めて見る関数の意味が正しく理解できないと、応用も利かないので、よく勉強してみます。前述の友人の要望を再度よく確認して、完成とできましたら、また改めて皆様に御礼を申し上げたいと思います。本当に有難う御座います。

回答No.3

mshr1962

確立というより出現率ですね。
初めの2列は対象外ということでいいのでしょうか？
A1:CV1にデータがあるとして
=SUMPRODUCT((SIGN($A1:$CT1)=-1)*(SIGN($B1:$CU1)=-1)*(SIGN($C1:$CV1)=1))/COLUMNS($C1:$CV1)

お礼

ご回答有難う御座います。SUMPRODUCT関数・SIGN関数は初めてしりました。自分の中では、かなり画期的なご回答でした。これならば、単独の数式で求める数字が出せるようですね。複数列・複数行を組み合わせて計算しなくて済むのは大分Sheet内が整理できて嬉しいです。

補足

追加で教えて頂けますでしょうか？
このようにSIGN関数に単一のセルでなく、範囲選択されている場合の意味というか、どういう結果を返すのかがよく解りません。また、SUMPRODUCT中の値の間が、「,」でなく「*」の場合どういう働きをするのかがやはり理解できません。この場合、SUMPRODUCTにとっての配列はどこなのでしょうか？宜しければお教え下さい。

回答No.2

zap35

確率を求めるだけならＢ３セルに
　=IF(AND(A1<0,A2<0,A3>=0),1,0)
を入力して下までコピーします。

条件に合致するセルは数字の１が入るので
　=SUM(B1:B100)/COUNT(B1:B100)
の式で出現頻度は求められなくはないです。

でもＡ列の値はどのようなルールで発生しているのですか？　その関数？を無視して、確率を求めようとしても不正確な値しか得られませんよ。

数学には大数の法則がありますから、サンプル数が１万、５万、１０万…と増えるに従って正確になって行きますが、１００件のサンプル数で求めた確率は信用が置けない気がします。

お礼

済みません、ご心配をお掛けしています。
実は友人に相談されて一緒に悩んでいる状態です。
彼によれば、ファイナンス理論という勉強をしているらしく、１社の数年分の利益が１行に記録されており、２ヶ月連続して赤字であった直後の月（３ヶ月目）が黒字に戻っている確立を出し、そのサンプルが数百社から数千社分（つまり数千行のデータ）ある・・・ということらしいのですが、私も実際のところ、これがどの様に運用されるデータでどれ程の信頼性を求めているのか、さっぱり解りません。自分もExcelの勉強が出来る機会だと思い、挑戦し始めたがどうにも自力ではできなくてここの駆け込んだと、そういう状況です。力になって下さり、本当に有難う御座います。

回答No.1

redowl

仮にA列に下記のデータが入っているとして
B列に( 下式を　B1に入力し、下方向にドラッグ）
=AND(A1<0,A2<0,A3<0,A4<>"")*2+AND(A1<0,A2<0,A3>0,A4<>"")*1

式解説：　数式の入っているセル　の　左データ（１段目）が　正数　なら　０
　　　　　　　　　数式の入っているセル　の　左データ（１段目）が　負数で、その下（２段目）も負数
　　　　　でその下（３段目）が負数なら　2 、　正数なら　１　とする。　　ただし（４段目）が空白なら　０

　　　　　　　　(-,-,-)なら２　　(-,-,+)なら１　　これ以外は　0　となる。
後は、　２、１、０の出現個数をカウントすれば確率を求められるかと・・・・

A 　　 B
3　　　0
-2　　　2
-6　　　1
-5　　　0
9　　　0
7　　　0
-4　　　0
-1　　　0

お礼

有難う御座います。考え方が見えてきました。サンプルするデータはＡ行に入っているので、行列入れ替えて考えてみれば良いのでしょうね。別にＩＦと組み合わせる訳ではないのでしょうか？勉強不足なようで、×２、×１をする意味がまだ理解できていないので、勉強します。