応用した計算式を教えて欲しいです。

質問

皆さん、すいません、1つ下ぐらいに質問した者ですが、
基本の計算式を教えてもらったのですが、応用した計算式ができず
困っていまして、できましたらもう一度ご教授ください。

Aの1～3000までのセルの中に
ランダムなプラスの数字、ランダムなマイナスの数字が縦にランダムに並んでます。

（例えば、0.54、0.15、0.3、0.015などが連続で並んでいたり、-0.57、-0.01、-0.25が連続で並んでいたり、0.25が1回だったり、-1.2が2回連続だったり、0があったり、等々）

※0も出現するので、その時はプラスマイナスゼロ扱いでお願いします。

0.54
0.15
0.3
0
0.015
-0.57
-0.01
-0.25
0.25
0
-1.2
-1.2
0
-1.2

その時に、

プラスの数字が並んでいる数の合計、
マイナスの数字が並んでいる数の合計、

を隣のセル（どこでもいいです）に出したいのですが、
この計算式のやり方がわかりません。。。

上記の例なら、下記のような答えになるはずなんです。

1.005
-0.83
0.25
-3.6

皆さんよろしくお願いします。

回答No.4

KURUMITO · ベストアンサー

作業列を使って行います。
Ｂ1セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A1="","",IF(ROW(A1)=1,IF(OR(AND(A1>=0,A2>=0),AND(A1<=0,A2<=0)),A1,""),IF(OR(AND(OFFSET(B1,-1,0)>=0,A1>=0),AND(OFFSET(B1,-1,0)<=0,A1<=0)),OFFSET(B1,-1,0)+A1,IF(OR(AND(OFFSET(B1,-1,0)>=0,A1<=0),AND(OFFSET(B1,-1,0)<=0,A1>=0)),A1,""))))
Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(AND(B1>=0,B2<=0),AND(B1<=0,B2>=0)),B1,"")
Ｄ1セルには次の式を使って下方にオートフィルドラッグします。
=IF(C1="","",ROW(A1))
Ｅ1セルには次の式を使って下方にオートフィルドラッグします。
=IF(ROW(A1)>COUNT(D:D),"",INDEX(C:C,SMALL(D:D,ROW(A1))))
Ｅ列に答えが表示されます。

お礼

KURUMITOさん、回答ありがとうございます。
（お礼が遅くなってすいません）

シンプルかつわかりやすく、答えも完璧で、
すぐに使えることができました！

まさに理想としていたものです。
感謝感謝です！ありがとうございました！

回答No.7

imogasi

作業列を使ってやる方法。
例データ(質問のデータに少し増やした）
Ａ列　　Ｂ列　　Ｃ列
（第1行は空白にすること）
0.54 1 1
0.15 1 1
0.3 1 1
0 1 1
0.015 1 1
-0.57 -1 2
-0.01 -1 2
-0.25 -1 2
0.25 1 3
0 1 3
0 1 3
-1.2 -1 4
-1.2 -1 4
0 -1 4
-1.2 -1 4
0 -1 4
0 -1 4
-2 -1 4
23 1 5
Ｂ２には　=IF(A2=0,B1,SIGN(A2))
下方向に式を複写。
Ｃ２には　=IF(B1=B2,C1,C1+1)
下方向に式を複写。
＝＝＝
（必須ではないが判りやすくするため）Ａ列をＤ列に移動して
データー集計でＣ列を元にＤ列を集計する
グループの基準　Ｃ列
集計するフィールド　Ｄ列
結果
Ｃ列　　Ｄ列
1 0.54
1 0.15
1 0.3
1 0
1 0.015
1 合計 1.005
2 -0.57
2 -0.01
2 -0.25
2 合計 -0.83
3 0.25
3 0
3 0
3 合計 0.25
4 -1.2
4 -1.2
4 0
4 -1.2
4 0
4 0
4 -2
4 合計 -5.6
5 23
5 合計 23
総計 17.825

お礼

imogasiさん、またもや回答ありがとうございます。

なるほどなるほどです、こういう風なやり方も、
と思いながら、試しています。

とても参考になり、是非使わせてもらいます。

色んなやり方があるんだ、Excel、奥が深いなぁって思いつつ、
imogasiさん筆頭に、皆さんの知識の深さにも感服しています。

ありがとうございました！

回答No.6

grumpy_the_dwarf

基本的には日ごとの小計を出すのと同じ、データ列の自行までの小
計から小計列の直前行までの小計を引く話。符号の変動はsign関数
で補足出来るんだけど、ゼロを無視するにはどうすればいいかが、
頭の使いどころですね。

実はゼロが連続しない条件があれば作業列なしで簡単に出来るんで
すが、そういうわけにもいかないでしょうから作業列を使います。

B1: =if(A1,sign(A1),B2)
「データ列がゼロでないなら符号を出す。ゼロなら下行に同じ」

これで準備ができたので、普通に小計を出します。
c1: =IF(A1*(B1<>B2),SUM(A$1:A1),"")
C2: =if(A2*(B2<>B3),sum(A$1:A2)-sum(C$1:C1),"")
「作業列が下行で変わる場合、データ列の累計から直前の小計まで
の累計を引く」

シンプルでしょ。

お礼

grumpy_the_dwarfさん、回答ありがとうございます。
（お礼が遅くなってすいません）

なるほどです！とてもシンプルでわかりやすく
答えも完璧にでます。使わせてもらいます！

ありがとうございました！感謝です。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

もう一案
D2セル =A2
D3セル =IF(SIGN(A3)=SIGN(D2),SUM(A3,D2),IF(A3=0,D2,A3))
下へオートフィル

E1セル 1
E2セル =IF(SIGN(D2)=SIGN(D3),E1,SUM(E1,1))

F1セル =MAX(E:E)
F2セル =IF($F$1>ROW()-1,ROW()-1,"")

G2セル =IF(F2="","",INDEX(D:D,MATCH(F2,E:E)+1))

E2:G2セル範囲を下へオートフィル
符号の判定は #3のtom04さんの方法の方がわかりやすいかも。
参考まで

お礼

CoalTarさん、回答ありがとうございます。
（お礼が遅くなってすいません）

参考にします。ありがとうございました！

回答No.3

tom04

こんにちは！
無理矢理関数での方法になります。
今回の質問の場合は「0」が邪魔をしているみたいなので

↓の画像のようにA列のデータにフィルタをかけ
オートフィルタのオプションで「0」以外を表示させ、
それを形式を選択して貼り付けで「値」にチェックを入れC列に貼り付けたものを利用しています。

D2セルは単純に
=C2

D3セルは
=IF(C3="","",IF(C3*C2>0,D2+C3,C3))
として、フィルハンドルの（＋）マークでダブルクリック
E2セルに
=IF(D3="",D2,IF(D3*D2>0,"",D2))
とし、これもフィルハンドルでダブルクリック

最後にG2セルの数式は配列数式になりますが、
=IF(COUNT($E$2:$E$3000)<ROW(A1),"",INDEX($E$2:$E$3000,SMALL(IF($E$2:$E$3000<>"",ROW($1:$2999)),ROW(A1))))

（これをShift＋Ctrl＋Enterキーで確定してください。）

と入れて、オートフィルで下へコピーしています。

これで何とか希望に近い形にならないでしょうか？

以上、かなり強引な方法ですが、
参考になれば幸いです。m(__)m

お礼

tom04さん、回答ありがとうございます。
（お礼が遅くなってすいません）

画像付きで、わかりやすく説明もして頂き、ありがとうございます。
凄く参考になり、使わせてもらいます。

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

回答No.2

noname#99913

関数でやると大変そうなので、マクロで作りました。B列に合計を出します。空白セルが出現するまで、計算を続けます。試してみてください。

Option Explicit

Private Sub GetTotal()

Dim Total As Single
Dim i As Integer

i = 1

Do While Cells(i, 1) <> ""

Total = Total + Cells(i, 1).Value
Cells(i, 2).Value = ""

If Total * Cells(i + 1, 1).Value < 0 Or Cells(i + 1, 1) = "" Then

Cells(i, 2).Value = Total
Total = 0

End If

i = i + 1

Loop

End Sub

お礼

kozirou54さん、回答ありがとうございます。
（お礼が遅くなってすいません）

マクロ使わせてもらいます！感謝です。
ありがとうございました！

回答No.1

imogasi

もっと条件を詳しく文章で書かないと。推測させるないように。質問者自身が条件を整理できて無いのでは。
並ぶとは、
プラス、０、マイナスの符号の変化を捉えるようだ。
プラスの次ー＞プラス
プラスの次ー＞０
０の次ー＞プラス
マイナスの次ー＞マイナス
マイナスのの次ー＞０
０の次ー＞マイナス
ーーー
そして愚豪的な、「連」ごとの合計を出すのか。
ーー
関数では作業列を使うのが判りやすい式になるだろう
ーーー
一見してＶＢＡでやるのが適しているようだ。
例データ
Ａ列　　　Ｂ列（参考小計）
0.54
0.15
0.3
0
0.015 1.005
-0.57
-0.01
-0.25 -0.83
0.25
0
-1.2
-1.2
0
-1.2 -3.6
ーーー
コード　
標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row
'MsgBox d
k = 1 '書きだし最初行。第1行とする
t = Cells(1, "A") 'とりあえず合計に加算
m = Sgn(Cells(1, "A")) '第1行目の符号をセット
'--
For i = 2 To d
If Cells(i, "A") <> 0 Then '0だと何もしない。符号も継承
If Sgn(Cells(i, "A")) = m Then '符号が前と直前と同じなら
t = t + Cells(i, "A") '同じなら足すだけ。符号継承
Else
Cells(k, "F") = t '前行までの合計書き出し
k = k + 1 '一行下へ書き出す準備
t = 0 'ご破算
t = t + Cells(i, "A") '合計に加算
m = Sgn(Cells(i, "A")) '符号を変える
End If
End If
Next i
Cells(k, "F") = t '最後の書き出し
End Sub
ーー
結果Ｆ列
1.005
-0.83
0.25
-3.6

お礼

imogasiさん、回答ありがとうございます。
（お礼が遅くなってすいません）

＞もっと条件を詳しく文章で書かないと。推測させるないように。質問者自身が条件を整理できて無いのでは。

本当にその通りです、基本さえ教えてもらえれば、後は自分で改良してできる！と思ったばっかりに、二度手間三度手間になり、皆さんにご迷惑をおかけしてしまって、、すいませんでした。

注意だけじゃなく、とても参考になる答えも出して頂き、
ありがとうございました！