Excelで余分な行を使わずに名前別で最大ドローダウンを出したいです。

質問

http://okwave.jp/qa5372059.html
前の質問で回答番号：No.1の方の補足のところには書いたのですが、質問欄の説明が中途半端で分かりづらく、回答していただいた方を混乱させてしまいすみません。
改めて質問させてください。
二度も同じ質問をしてしまいすみません。

Excelで余分な行を使わずに名前別で最大ドローダウンを出したいです。

A　　　　　　B
名前　　　　損益
株　　　　　50
先物　　　　30
株　　　　　-40
FX　　　　　-50
先物　　　　20
株　　　　　30
株　　　　　-20
FX　　　　　-40

このような表があるとします。
名前別で最大ドローダウンを出す場合、以下で求められます。
最大ドローダウンとは資産額を時系列に並べて、最大資産から差を差し引いた結果、最大のマイナス幅のことです。

株だけの最大ドローダウンであれば、
C1、株の値　として、
C2に
=IF(NOT(A2="株"),"0",B2)
以下オートフィル

D1、株の残高　として、
D2に
=C2
D3に
=D2+C3
以下オートフィル

E1、ドローダウン　として、　【厳密にはプラスの取引もあるのでドローダウンではない時もありますが便宜上ドローダウンとしました】
E2に
=D2
E3に
=D3-MAX($D$2:D2)
以下オートフィル

G1（どこでもいいですが）に
=MIN(E:E)
とすれば求められます。【G1の値が株の最大ドローダウンです】

しかし、これだとC、D、E列が余分に必要ですし、種類が増えてくると大変です。
そこで、マクロまたは関数で行を作らずに求められないかと思いました。
よろしくお願いします。

回答No.13

xls88

＞これでは駄目でした。

If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then
'↑条件の付け過ぎ、c2.Offset(, 2).Value = c1.Offset(, 1).Valueは後で判定する

If c1.Offset(, 1).Value <> "" Then
If c2.Offset(, 2).Value = c1.Offset(, 1).Value Then　'売り×売り、買い×買いなら
　・ここに計算式が無い、従って答えも出ない
End If
Else　'FX以外の場合

お礼

無事完成いたしました。
この度はいろいろとどうもありがとうございました。
感謝します。

回答No.12

xls88

＞それはどこに書けばいいですか。
内側のFor文で
If c2.Value = c1.Value Then
・
・
・
End If
の中です。

＞K列が空白の場合という文では処理できないでしょうか。
今の流れだと
If c1.Value = "FX" Then
を
If c1.Offset(, 1).Value <> "" Then
とすれば良いと思います。

補足

＞内側のFor文で
＞If c2.Value = c1.Value Then

この文がないのではっきりは分かりませんでした。
Sub Macro111()

Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c1 As Range, c2 As Range

For Each c1 In Range("J2:J9")
If c1.Value <> "" Then
For Each c2 In Range("A2:A3000")
If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then

If c1.Offset(, 1).Value <> "" Then
If c2.Offset(, 2).Value = c1.Offset(, 1).Value Then　'売り×売り、買い×買いなら
End If
Else　'FX以外の場合

bs = bs + c2.Offset(, 1).Value
If maxbs < bs Then
maxbs = bs
End If
If mindd > bs - maxbs Then
mindd = bs - maxbs
End If
End If
Next
c1.Offset(, 7).Value = mindd
bs = 0: maxbs = 0: mindd = 0
End If
Next
End Sub

これでは駄目でした。

回答No.11

xls88

同じ計算が2度出てくるのでスマートではないのですが、
ごく簡単に

If c1.Value = "FX" Then　'FXの場合
If c2.Offset(, 2).Value = c1.Offset(, 1).Value Then　'売り×売り、買い×買いなら
・
・計算
・
End If
Else　'FX以外の場合
・
・計算
・
End If

といった感じで出来ると思います。

お礼

ありがとうございます。
多分答えてはいただけないのでしょうが、最後に一応聞いてみます。

＞If c1.Value = "FX" Then　'FXの場合
＞If c2.Offset(, 2).Value = c1.Offset(, 1).Value Then　'売り×売＞り、買い×買いなら
＞・
＞・計算
＞・
＞End If
＞Else　'FX以外の場合
＞・
＞・計算
＞・
＞End If

それはどこに書けばいいですか。

これも答えてはくれないのでしょうが、K列が空白の場合という文では処理できないでしょうか。その場合、どこにどのように書けばいいですか。

よろしくお願いします。

回答No.10

xls88

＞回答番号：No.9 この回答への補足

＞If c1.Value <> "" Then 'ここです
＞c1.Offset(, 7).Value = mindd
＞End If 'ここです
セルへ書き出しのところでも良いのですが、内側のFor文で無駄なループが発生します。
内側のFor文にもかかるようにすると無駄なループを防止できます。
体感できるかどうかは別にしても、その分処理時間が速くなります。

For Each c1 In Range("J2:J6")
If c1 <> "" Then
For Each c2 In Range("A2:A10")
　　　　　　　・
　　　　　　　・処理
　　　　　　　・
Next
c1.Offset(, 2).Value = mindd
bs = 0: maxbs = 0: mindd = 0
End If
Next

＞ただ、K列が空白の場合には、C列に文字が入っていてもC列は無視して
＞そのまま計算という方はまだできてません。
現在のコードを活かすなら
c1.Value = "FX" の場合とそうでない場合
更に、c1.Value = "FX" の場合は
c2.Offset(, 2).Value = c1.Offset(, 1).Value
という条件判定すれば良いかも知れません。

ところで、計算結果はどうなのでしょうか。正しい答えが書き出されていますか？

お礼

＞現在のコードを活かすなら
＞c1.Value = "FX" の場合とそうでない場合

K列が空白の場合という文では処理できないでしょうか。

If not c1.Offset(, 1).Value <> "" And c2.Value = c1.Value Then
not が入るような気がしてきました。
ただ、これだけだと単に、
If c2.Value = c1.Value Then　と同じですよね。

If not c1.Offset(, 1).Value <> "" And c2.Value = c1.Value Then
If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then

この二つを書かなければいけませんが、この二つを書くとまともに計算されなくなります。

補足

ありがとうございます。
お世話になっております。

>ところで、計算結果はどうなのでしょうか。正しい答えが書き出されていますか？

まだ単純な計算しか試せていませんが今のところは合っているようです。

＞現在のコードを活かすなら
＞c1.Value = "FX" の場合とそうでない場合
＞更に、c1.Value = "FX" の場合は
＞c2.Offset(, 2).Value = c1.Offset(, 1).Value
＞という条件判定すれば良いかも知れません。

C2:C3000 ＝　K2:KJ9 が同じであればですか？
c2.Offset(, 2).Value = c1.Offset(, 1).Value
これを単純にどこかにはめこんでも駄目ですよね。
さっぱり分かりませんでした。

回答No.9

xls88

If c1.Value <> "" Then
　・
End If
を記述する位置が間違っています。

c1.Value <> ""
という条件で、内側のループが回るようにすれば良いのです。

あと、今までの補足説明から
C列にはA列の「FX」に対して「売り」「買い」が入力されている。
それ以外の、A列データに対応するC列セルは空白である、と理解しています。
違っていますか？

お礼

実は、株も先物もC列に売り買いが入っていますが、株と先物は売り買いを一緒くたにしたかったので
このような聞き方をしてしまいました。

End If
を違うところに入れてやってみましたができません。
僕にはあまりに難解すぎました。

明日の昼ごろに、この質問を閉じて改めて、マクロのIfについて
質問してみようと思います。

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

補足

お世話になっております。
ずっとやっていたら、
If c1.Value <> "" Then
End Ifの位置は分かりました。
最後の方の、

Next
If c1.Value <> "" Then 'ここです
c1.Offset(, 7).Value = mindd
End If 'ここです
bs = 0: maxbs = 0: mindd = 0
Next
End Sub

ただ、K列が空白の場合には、C列に文字が入っていてもC列は無視して
そのまま計算という方はまだできてません。

If c1.Offset(, 1).Value <> "" And c2.Value = c1.Value Then
End If

文が合っているのかも分かりませんし場所も分かりませんが、朝まで四苦八苦してみます。

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

回答No.8

xls88

例えば、c1セルが空白でなければ処理を実行

If c1.value <> "" Then
　・
　・処理
　・
End If

処理速度をあげるなら、オートフィルタを使えばよいかもしれません。
オートフィルタ操作を「マクロの記録」すれば参考コードが得られます。

補足

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

Sub Macro111()

Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c1 As Range, c2 As Range

For Each c1 In Range("J2:J9")
For Each c2 In Range("A2:A3000")

If c1.Value <> "" Then　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　'もし、J2:J9が空白でなければ
If c1.Offset(, 1).Value <> "" And c2.Value = c1.Value Then　　　　　　　　'もし、 K2: K9が空白ならば、 A2: A3000 J2: J9

If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then

bs = bs + c2.Offset(, 1).Value
If maxbs < bs Then
maxbs = bs
End If
If mindd > bs - maxbs Then
mindd = bs - maxbs

End If
End If
End If
End If
Next

c1.Offset(, 7).Value = mindd
bs = 0: maxbs = 0: mindd = 0
Next

End Sub
---------------------------------------------
If c1.Value <> "" Then
For Each c1 In Range("J2:J9")
とあることから、J2:J9をあらわすと思ったのですが違ったみたいです。

If c1.Offset(, 1).Value <> "" And c2.Value = c1.Value Then 'もし、 K2: K9が空白ならば、 A2: A3000 ＝ J2: J9
どちらもあっていないようでちゃんと計算されませんでした。
書き方が違いますか。
よろしくお願いします。

回答No.7

xls88

例えば
For Each c1 In Range("J2:J9")
上記For文が回る度に、c1は、Range("J2")からRange("J9")まで変化します。
次に
For Each c2 In Range("A2:A3000")
このFor文は、For Each c1 In Range("J2:J9")の内側にあります。
ひとつのc1毎に、c2がRange("A2:A3000")のセル数回ループします。
毎回のことですから、時間がかかることになります。

If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then
で、例えば
A2=J2 C2=K2 という条件をつけています。
各セルの値が
FX=FX 売り=売り
とか
株=株 空白=空白
というように、同じ値ならIf文内の処理が行われるはずです。

訂正されたコードを纏めてみました。

Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c1 As Range, c2 As Range

For Each c1 In Range("J2:J9")
For Each c2 In Range("A2:A3000")If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then
bs = bs + c2.Offset(, 1).Value
If maxbs < bs Then
maxbs = bs
End If
If mindd > bs - maxbs Then
mindd = bs - maxbs
End If
End If
Next
c1.Offset(, 7).Value = mindd
bs = 0: maxbs = 0: mindd = 0　　'★変数の初期化
'mindd = "" '★この行は不要です（上の行と重複しています）
Next

＞bs = bs + c2.Offset(, 1).Value
＞'ここをいじると駄目みたいでした。この(, 1)は何を意味するのでしょうか。
c1.Offset(, 7).Value = mindd
上記で、既にやられているので御分かりになるとおもいますが
c2.Offset(行, 列)
というように、c2セルを基準にOffsetする、行数と列数を記入します。
Offsetしない場合は、0あるいは省略できます。
マイナス記号を付ければ、行は上、列は左にOffsetします。

＞J列K列が空白の場合に、0ではなく空白にすることはできませんか。
K列はともかく
For Each c1 In Range("J2:J9")
で、対象にしているJ列セルにも空白があるということですか？
c1が空白なら
とか
c1が空白でないなら
というIf文をかませればよいと思います。

補足

お答えいただきどうもありがとうございます。

＞　For Each c1 In Range("J2:J9")
＞　で、対象にしているJ列セルにも空白があるということですか？

J列セルの空白は、枠は作ってあり、近いうちに入るだろうというところです。
マクロをその都度いじらなくていいようにしたかったのです。

＞＞　J列K列が空白の場合に、0ではなく空白にすることはできませんか。

＞c1が空白ならとかc1が空白でないなら
＞というIf文をかませればよいと思います。

関数でセルであれば
=IF(J15="","",？？？)なのですが行の中で空白というと
=IF(J:J="","",？？？)になるのでしょうか。
？？？もよく分かりませんがそれがマクロになるとお手上げです。

＞＞　K列が空白の場合、C列に文字が入っていても
＞＞　C列は無視してそのまま計算させることはできないでしょうか。

IF(K:K="",A:A=J:J,A:A=JJ C:C=K:K)
これでは、関数にもなっていませんが。

すいません、教えていただけませんか。
よろしくお願いします。

回答No.6

xls88

回答番号：No.5で提示したコードでは、変数の初期化を忘れていました。
追加してください。
書き込むセルの列は、Offsetプロパティの列数値を変えてみてください。
現状では、J列から2列隣の列を指定しています。

c1.Offset(, 2).Value = mindd
bs = 0: maxbs = 0: mindd = 0

提示したコードをすぐに使えるわけですから、マクロ初心者の方ではないと思います。
提示されたコードの理解をするようにしてください。

＞それと、行の制限をなくしたくて、
＞For Each c1 In Range("J:J")
＞For Each c2 In Range("A:A")
＞こうしたらIf mindd > bs - maxbs Thenの部分にエラーが出てしまいました。
エラーは別の原因だと思います。
全行数分ループするのは無謀です。
対象セル範囲に限定しましょう。
VBA 最終セル 等のキーワードでWeb検索すればたくさんヒットすると思います。

お礼

どうもありがとうございます。
マクロはマクロの記録を押すことと、それをちょこっと変更したりコピペしかした事がありませんでした。

なので以下を1行ずつにコピペして実験しました。
c1.Offset(, 7).Value = mindd
bs = 0: maxbs = 0: mindd = 0
---------------------------------------------------------------------
Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c1 As Range, c2 As Range

For Each c1 In Range("J2:J9")　　　　　’アドバイスいただいたとおり範囲指定しました
For Each c2 In Range("A2:A3000")　　　’アドバイスいただいたとおり範囲指定しました

If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value The　　　 '前者はC列なら2、後者はK列なら1だということがわかりました。

bs = bs + c2.Offset(, 1).Value　　　　’ここをいじっては駄目みたいでした。

If maxbs < bs Then

maxbs = bs

End If
If mindd > bs - maxbs Then
mindd = bs - maxbs
End If
End If
Next

c1.Offset(, 7).Value = mindd　　　　’ここを7にすることでQ列にできました。

’c1.Offset(, 2).Value = mindd　　　'ここに挿入しましたが、この行はなくても動作しました。多分上の行と重複してるからだと思います。
　　　　　　　　　　　　　　　　　　’今は’を行頭に入れて読み込まないようにしてます。
bs = 0: maxbs = 0: mindd = 0　　　　'ここに挿入しました

mindd = ""
Next
---------------------------------------------------------------------
いじってみてもわからなかったので教えていただけないでしょうか。
bs = bs + c2.Offset(, 1).Value　　　　’ここをいじると駄目みたいでした。この(, 1)は何を意味するのでしょうか。

K列が空白の場合、C列に文字が入っていてもC列は無視してそのまま計算させることはできないでしょうか。

J列K列が空白の場合に、0ではなく空白にすることはできませんか。

ここまでいろいろ教えていただいて申し訳ありませんが、よろしくお願いします。

補足

＞　bs = bs + c2.Offset(, 1).Value
ここは、いじっていたらB列を指定していることが分かりました。

残りの2点だけ教えていただけないでしょうか。

・K列が空白の場合、C列に文字が入っていてもC列は無視してそのまま計算させることはできないでしょうか。

・J列K列が空白の場合に、0ではなく空白にすることはできませんか。

ここまでいろいろ教えていただいて申し訳ありませんが、よろしくお願いします。

回答No.5

xls88

＞回答番号：No.4 この回答へのお礼
こんな感じでしょうか。

Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c1 As Range, c2 As Range

For Each c1 In Range("J2:J5")
For Each c2 In Range("A2:A9")
If c2.Value = c1.Value And c2.Offset(, 2).Value = c1.Offset(, 1).Value Then
bs = bs + c2.Offset(, 1).Value
If maxbs < bs Then
maxbs = bs
End If
If mindd > bs - maxbs Then
mindd = bs - maxbs
End If
End If
Next
c1.Offset(, 2).Value = mindd
mindd = ""
Next

お礼

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

L列ではなくQ列に出すことはできませんか。

それと、行の制限をなくしたくて、
For Each c1 In Range("J:J")
For Each c2 In Range("A:A")
こうしたらIf mindd > bs - maxbs Thenの部分にエラーが出てしまいました。
他に変更しないといけませんか。

売りと買いを、("K:K")を参照するようにはできませんか。

("J:J")と("K:K")の両方が空欄の場合は空欄、
("J:J")がないが"K:K")がある場合には空欄（Kだけある場合には空欄）
("J:J")はあるが("K:K")が空欄ない場合には("J:J")の条件だけで
("J:J")と("K:K")の両方がある場合には両方の条件でということは可能でしょうか。

FX　売り　-70
FX　買い　-110
と出ますが、実際は
FX　売り　-50
FX　買い　-40
です。

何度も申し訳ありません。
よろしくお願いします。

回答No.4

xls88

マクロの例です。
検証不足かもしれません。

Dim bs As Variant
Dim maxbs As Variant
Dim mindd As Variant
Dim c As Range

For Each c In Range("A2:A10")
If c.Value = "株" Then
bs = bs + c.Offset(, 1).Value
If maxbs < bs Then
maxbs = bs
End If
If mindd > bs - maxbs Then
mindd = bs - maxbs
End If
End If
Next
Range("G1").Value = mindd

お礼

教えてくださった通りにしたらできました。
どうもありがとうございます。

申し訳ありませんが、これを応用して他の文字列にも使えるように、

If c.Value = "株" Then
この株というのを、J列のセルに書いてある文字にし、
Range("G1").Value = mindd
このG1というのをQ列にできませんか。

J列以降に別の表があり、J列には名前が書いてあり、Q列には最大ドローダウンと書いてあります。

J列　　　K列　　　　　　Q列
名前　売り・買い　　最大ドローダウン
株
先物
FX　　　売り
FX　　　買い

それと、FXだけは、C列に売りと買いの種別があるのですが、
A　　　 　B　　　　　C
名前　　損益　　　売り・買い
株　　　50
先物　　30
株　　　-40
FX　　　-50　　　　　売り
先物　　-20
株　　　30
株　　　-20
FX　　　-40　　　　　買い

そこだけは、J列（名前）とK列（売り・買い）を両方条件にしたいです。

お手数おかけして申し訳ありません。
よろしくお願いします。