エクセルでマイナスやプラスの値をカウントしたい

質問

Excelの関数で、以下の条件でマイナスの値をカウントしたいと考えています。
　　　A　　　　B　　　　　C
１　 Put　　　0.03
２　　　　　　-0.75
３　　　　　　 0.3
４　　　　　　-2.5　　　　1
５　　　　　　-7.5
６　 Pull　　 -3.6
７　　　　　　 4.2
８　　　　　　-5.6　
９　　　　　　 3.5　　　　-1

条件は以下のとおりです。
A列にPutが出現し、B列にマイナスの数値が2回出現したら、C列に1を表示

A列にPullが出現し、B列にプラスの数値が2回出現したら、C列に－１を表示

つまりC列には4行目（PutがA列に出現してから2回目のマイナス）に１が表示され、
9行目（PullがA列に出現してから2回目のプラス）に-1が表示されることになります。

IFやらCOUNTIFやら色々調べたのですが、どんどん増えていくデータに対して少なくとも2回マイナスが出たら、１を返すということができませんでした。。
Putは一度出ると継続し、C列に１が出現した時点で終了します。次にPutが出るか、Pullが出るかは別のデータによって決まるので、PutとPullが交互に出るというわけではありません。

例が分かりにくいかもしれませんが・・・補足いたしますので何卒よろしくお願い致します。

回答No.3

Nouble · ベストアンサー

解説ですが

お示し頂いた表でいうところの
Ｃ７に焦点を当ててご説明します。

Ｃ７の結果はＡ１:Ｂ７の積み重ねの後にあるものですよね
Ａ１:Ｂ７の中には"Pull"や"Put"が何回か出てきます
その中でC７に関係があるのは"Pull"や"Put"が記載されているセル中の
最もセルナンバーの大きいセルですよね
詰まり
=MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))
ですね

この式から導き出されるナンバーよりＣ７のセルナンバーである７までが対象となるわけですね

このことを元にOFFSET構文を組み立てると

=OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,1,ROWS($B$1:B7)-MAX(INDEX(($A$1:F7={"Put","Pull"})*ROW($A$1:A7),))+1,1)
で調査対象となるセル範囲の配列が求まり
此に
SIGN(OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,1,ROWS($B$1:B7)-MAX(INDEX(($A$1:F7={"Put","Pull"})*ROW($A$1:A7),))+1,1))
と、いう風にＳＩＧＮ関数を被せることにより
値が正の数か負の数かが求まります

また
=OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,0,1,1)
で最も間近な"Pull"や"Put"の記載内容が求まりますので
=(OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,0,1,1)="Pull")*2-1
としてやれば、探すべき値が正の数なのか負の数なのかが解ります

後は探すべき対象の個数を数えて
初めて２個になったときに
詰まり直前のＣ６で１を、自らのセルＣ７で２を算出したならば
=(OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,0,1,1)="Pull")*2-1
の反対である
(OFFSET($A$1,MAX(INDEX(($A$1:A7={"Put","Pull"})*ROW($A$1:A7),))-1,0,1,1)="Put")*2-1
を表示させてやればいい

ということになります

今回はCOUNTIFを使わずSUMPRODUCTを使って個数をカウントしましたが
これは
=COUNTIF(SIGN(B1:B7),1)
が認識されず拒否されるのと同時に
=COUNTIF(INDEX(SIGN(B1:B7),),1)
すらも駄目だったためです

=COUNTIF(B1:B7,">0")
とすればいけるようですが
今回の式にはそぐいませんね

もう１点
今回は総括して"Pull"も"Put"一括して対象とするように式を立てましたが
そもそも"Pull"と"Put"は二律背反なので
解として１が帰るように"Pull"と"Put"に別々な式を立てて
"Put"の式から"Pull"を減算するように組み立てても良いかもしれませんね

ただ、"Pull"や"Put"の記載セル位置の割り出しや
それ以降の値読みだし、
対象値のカウントなどは両式においてほぼ同じとなるよう予想されるので
此も先に示させて頂いた式と同様
結構単調なものとなるかも知れませんね

お礼

再度のご投稿ありがとうございます。
こちらで丁寧にご説明いただいたので、私にも理解することができました。
こちらの式で当てはめてみたところ、無事できました。
ありがとうございました。

回答No.2

Nouble

余りエレガントじゃないのですが
再現できました

Ｃ２に
=IF(AND(SUMPRODUCT((SIGN(OFFSET($A$1,MAX(INDEX(($A$1:A2={"Put","Pull"})*ROW($A$1:A2),))-1,1,ROWS($B$1:B2)-MAX(INDEX(($A$1:A2={"Put","Pull"})*ROW($A$1:A2),))+1,1))=((OFFSET($A$1,MAX(INDEX(($A$1:A2={"Put","Pull"})*ROW($A$1:A2),))-1,0,1,1)="Pull")*2-1))+0)=2,SUMPRODUCT((SIGN(OFFSET($A$1,MAX(INDEX(($A$1:A1={"Put","Pull"})*ROW($A$1:A1),))-1,1,ROWS($B$1:B1)-MAX(INDEX(($A$1:A1={"Put","Pull"})*ROW($A$1:A1),))+1,1))=((OFFSET($A$1,MAX(INDEX(($A$1:A1={"Put","Pull"})*ROW($A$1:A1),))-1,0,1,1)="Pull")*2-1))+0)<2),(OFFSET($A$1,MAX(INDEX(($A$1:A1={"Put","Pull"})*ROW($A$1:A1),))-1,0,1,1)="Put")*2-1,"")
と入力
必要なだけ下にコピーしてください

如何でしょうか？

お礼

とりあえずNo.1でお答えいただいた回答者様の式で試してみましたが、もしその後に続くエクセルの処理で何か問題が発生するようであれば、こちらも試してみたいと思います。
何分、、初心者なもので式を見ただけで、なるほど！と分かるわけではなく・・・せっかくお答えいただいたのにすみません。
作業列がいらない？ようですので、折を見てこちらも試したいと思います。

回答No.1

cafe_au_lait

ネスト制限に引っかかったので作業列を使用します。

C1:=MAX(INDEX(NOT(A$1:A1="")*ROW(A$1:A1),))
D1:=IF(A1="",IF(SUM(OFFSET($D$1,C1-1,,ROW()-C1))=0,
IF(INDIRECT("A"&C1)="Put",
IF(COUNTIF(OFFSET($B$1,C1-1,,ROW()-C1+1),"<0")=2,1,""),
IF(COUNTIF(OFFSET($B$1,C1-1,,ROW()-C1+1),">0")=2,-1,"")),""),"")

改行は消去してください。
マクロの方がスマートかもしれません。

お礼

ありがとうございました。
とりあえず色々といじっていたらできたようです。
ここからさらに式を繋げていく必要があるので、少し方法を考えてみたいと思います。