【スプレッドシート】データを集計し、銘柄ごとの勝率などを求める
今回は、トレードにおいて重要な指標となる勝率やPF、PORなどを求めて、一覧で見られるようにしていきたいと思います。
それではやっていきましょう！
「集計」のシートを作る
まず初めに、新しくシートを追加します。
見出しを入力
見出しを入力します。
- A1 → 年間
- A2 → 利益
- A3 → 損失
- A4 → 合計
- A5 → 勝ち
- A6 → 負け
- A7 → 合計
- A8 → 勝率
- A9 → PF
- A10 → POR
- B1 → ='データ'!B1
- C1 → ='データ'!C1
- D1 → 合計
B1とC1は数式を入力してください。
利益・損失の値のみを加算していく
利益・損失を入力するために、SUMIF関数を使って、値がプラスの時、マイナスの時で分けて足していきます。
- B2 → =sumif('データ'!B3:B,">0")
- B3 → =sumif('データ'!B3:B,"<0")
これは、SUMIF関数によって範囲の中から、条件に合致するものだけの合計を返してくれます。なのでセルB2では銘柄１のプラスの値のみの合計が、セルB3では銘柄１のマイナスの値のみの合計が表示されています。
セルB4には利益と損失の合計を入力したいので、SUM関数でもいいですし、単純に足しても構いません。
- B4 → =sum(B2:B3)
もしくは、
- B4 → =B2+B3
利益・損失の回数をカウントする
勝率などを計算するために、利益と損失の回数をCOUNTIF関数を使ってカウントします。
- B5 → =countif('データ'!B3:B,">0")
- B6 → =countif('データ'!B3:B,"<0")
- B7 → sum(B5:B6)
これは、COUNTIF関数によって範囲の中から条件に一致する要素の個数を返してくれます。なのでそれぞれ0以上、0以下に一致する値の個数の合計が表示されています。
勝率、PF、PORを入力する
トレードの良し悪しを見る時に参考になる指標としていろいろなものがありますが、ここでは勝率、PF(プロフィットファクター)、POR(ペイオフレシオ)の3つを表示していきます。
- B8 → =B5/B7
- B9 → =if(B3=0,"-",abs(B2/B3))
- B10 → =if(and(B3<>0,B5<>0,B6<>0),abs( (B2/B5)/(B3/B6) ),"-")
※ABS関数は数値の絶対値を返してくれます。
これで全てのセルの入力ができたのでB2〜B10をC列にもコピーします。
合計の列の入力
セルD2にSUM関数を入力してセルB7までコピーします。
- D2 → =sum(B2:C2)
C8〜C10をD列にコピーします。
これで年間の集計の表ができたので見た目を整えます。
月ごとの集計の表作成
ここまでで年間通しての集計ができたので、月ごとの集計をプルダウンリストから選択できるように作っていきます。
「データ」シートの準備
「データ」シートから月で絞り込むために、「データ」シートの日付の左に列を追加します。
- A列を選択した状態で
- 「挿入」タブを選択
- 「左に１列」を選択
新しくA列が追加できたら、以下を下の方までコピーします。
- A3 → =if(B3="","",month(B3))
新しく追加したA列の「表示形式」を「自動」に変更します。
これで「データ」シートの準備ができたので「集計」シートに移動します。
プルダウンリストの作成
指定の月を選択できるようにプルダウンリストを作ります。
作り方については以前にもやっているので、省略させていただきます。
各見出しを入力
各見出しを入力します。
利益、損失、勝ち、負けの項目の入力
利益、損失、勝ち、負けの項目は年間のときとは違い、選択された月の集計のみを表示します。なので、SUMIFS関数・COUNTIFS関数を使って複数条件によって合計や回数を計算していきます。
- B13 → =sumifs('データ'!C3:C,'データ'!$A$3:$A,$A$12,'データ'!C3:C,">0")
- B14 → =sumifs('データ'!C3:C,'データ'!$A$3:$A,$A$12,'データ'!C3:C,"<0")
- B16 → =countifs('データ'!C:C,">0",'データ'!$A:$A,$A$12)
- B17 → =countifs('データ'!C:C,"<0",'データ'!$A:$A,$A$12)
その他の「合計」、「勝率」、「PF」、「POR」は年間の方と同じなので省略します。
C列、D列についても年間の方と同じなので、
- C列は、B列をコピー
- D列は、D列の年間の部分をコピー
これで月ごとの集計表ができたので、見た目を整えて完成です。
お疲れ様でした！以上で集計表の作成は終わります。次回は、グラフを作っていきたいと思います。
第１回 データ
第２回 カレンダー１
第３回 カレンダー２
第４回 集計
第５回 グラフ
