Excelの質問です。名前別で最大ドローダウンを出したいです。

質問

Excelの質問です。名前別で最大ドローダウンを出したいです。

A　　　　　　B
名前　　　　損益
りんご　　　-50
みかん　　　30
りんご　　　40
もも　　　　-50
みかん　　　20
りんご　　　30
りんご　　　-20
もも　　　　-40

このような表があるとします。
名前別で最大ドローダウンを出す場合、名前ごとに残高の列をC列以下に作れば以下のページの方法で求められます。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1416554559

ただし、今後この名前がどんどん増えていくためその度に列を増やしていくのが大変です。
列を増やさずに最大ドローダウンを出すことはできませんか。
マクロでも関数でもいいですがどなかた教えて頂けないでしょうか。
よろしくお願い致します。

回答No.5

popuplt

「資産額を時系列で並べて、それぞれの時点以前の最大資産額からの差を計算して、そのうち最大のマイナス幅のものが最大ドローダウンです。」とリンクにありますね。
ということなので、
C列に各行でのドローダウン、D列を最大ドローダウン　として
C2=IF(COUNTIF($A$2:A2,A2)>1,B2-MAX(IF($A$2:A2=A2,$B$2:B2,"")),"")
D2=IF(C2<>"",MIN(IF($A$2:A2=A2,$C$2:C2,"")),"")
どちらもCTRL+SHIFT+ENTER（配列数式）して、下へコピー。
ちがうかな？？？

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

質問が分かりづらく申し訳ありません。
どうもありがとうございました。

回答No.4

tom04

No.3です！
たびたびごめんなさい。

投稿した後に数式の検証をしてみましたところ
前回の回答は正確な表示ができませんでした。

という訳で前回の書き込みは無視してください。

何度も失礼しました。m(__)m

お礼

質問が分かりづらく申し訳ありません。
どうもありがとうございました。

回答No.3

tom04

こんばんは！
まったくの的外れの可能性がありますので
その場合は無視してください。

最大ドローダウンの意味が良く判らないのですが、
結局品物ごとの最小値を求めれば良いのですかね？
（もしかしてマイナスの場合だけとか？）

とりあえず↓の画像のように表を作ってみました。
A列を作業用の列とさせてもらっています。
A2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式を入れて、10000行目くらいまでオートフィルでコピーします。
（データが増えても対応できるように表の数式を10000行まで対応できるようにしているためです）

E2セルに
=IF(COUNT($A$2:$A$10000)>=ROW(A1),INDEX($B$2:$B$1000,SMALL($A$2:$A$10000,ROW(A1))),"")

F2セル（配列数式）に
=IF(E2="","",INDEX($C$2:$C$10000,SMALL(IF($B$2:$B$10000=E2,ROW($A$1:$A$9999)),1)))

（この列は配列数式になりますので、この画面から数式をコピー＆ペーストしただけではエラーになると思います。
貼り付け後にF2キーを押すか、数式バー内で一度クリックして、編集可能にします。
そして、Shift＋Ctrl＋Enterキーで確定すると
数式の前後に｛　｝マークが入り配列数式になります。

最後にE2・F2セルを範囲指定し、F2セルのフィルハンドルで
下へコピーすると画像のような感じになります。

これでA列にデータを入力するたびに表に反映されるはずです。

ただ、一つ気になるのがプラスの場合も表示するのかどうか？ってコトなのですが・・・

以上、参考になれば幸いですが、
最初に書いたように的外れの場合は無視してくださいね。m(__)m

回答No.2

minosennin

最大ドローダウンの意味がよく分かりませんが、B列に表示されている数値から、単に品目別の最小値を求めるのならピボットテーブルで値フィールドを最小値とすればよいのではないでしょうか。

マクロにすれば操作も簡単です。

お礼

質問が分かりづらく申し訳ありません。
どうもありがとうございました。

回答No.1

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

まず、ドローダウンの説明がよくわかりません。

推測したので考え方があっているか数式で示したので補足してください
C2セル =IF($A2=C$1,MAX($B2,C1),C1)
該当する過去の最大値を算出する

D1,F1,H1セル =MAX($B:$B)-MIN($B:$B)
単なる差の最大値

D2セル
=IF(ISTEXT(C1),D$1,IF($A2=C$1,MIN(D1,$B2-C1),D1))
・過去に2回以上出たときのみ比較する
　・該当するときのみ、差を計算し比較する

C2:D2セルを下へオートフィル
C2:D9セルをコピーしてE2:H9セルへ貼り付け
求める値は D9,F9,H9

お礼

質問が分かりづらく申し訳ありません。
どうもありがとうございました。

補足

お答えいただきありがとうございます。
資産額を時系列に並べて、最大資産から差を差し引いた結果、最大のマイナス幅＝最大ドローダウンです。

りんごだけの最大ドローダウンであれば、
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)
とすれば求められます。

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