どうも、ミスターマーケット(@mrmarket_japan)です。
ひさしぶりに休日にまとまった時間を確保できたので、配当集計講座を追加しました！
現在、配当集計講座では「損益推移グラフ」の作成方法を紹介しています。
損益推移グラフとはミスターマーケットが各企業の配当を紹介する際に利用している下↓のようなグラフです。
損益推移グラフでは評価損益(含み損益・黄棒)と青棒の配当受取累計(配当益・青棒)、さらに実現損益(利益確定済み売買損益・薄青棒)を区別しながらも、最終損益の推移を見える化しています。
損益推移グラフの１番の効果は配当の効果を実感できることです。時間がたてばたつほど、配当が積み重なっていくことがわかります
証券会社の画面では主に含み損益ばかり表示されるため、配当の力は忘れがちです。特に長期投資では本来、配当が累積されることで最終収益が押し上げられるため、配当の力を見える化することはマスト(必要)だと思っています（ ･`ー･´） + ｷﾘｯ
もし、含み損だけど、配当も含めて考えると大きな収益をもたらしてくれていた、そんなこともありえると思います。
そして、長期投資はヒマでつまらない投資といわれますが、「投資収益の見える化」で少しは楽しい投資になるかなっと思っています。
損益推移グラフの作成は全4回でできたらいいなと思っています。現在の予定としては下↓のとおりです。
3-3損益推移グラフをつくろう-集計シートの作成←今回
3-4損益推移グラフをつくろう-グラフの作成
よかったらおつきあいください。
ちなみに、そんなグラフを作ってみたいけど一からつくるのは大変だなと思う方は【投資の見える化・簡易版】10分で投資配当グラフをつくる方法がオススメです。
また、配当よりも先にポートフォリオの管理（見える化）をしたいという方は【米国株も！日本株も！管理できる】ポートフォリオ推移グラフの作り方がオススメです。
3-3-1集計シートをつくる
まずは①をポチ。
そして、②をダブルポチポチして新しくできたシートの帯に「集計」と名前をつけましょう。
（もう、もう解説なしでもできるようになってきているかな…？）
3-3-2集計シートの見出しと関数を入力する
ここからはコピペできるところはコピペして処理していただくのが楽かなと思います。
まずは各項目の見出しをつけしょう
①下記のように見出しをつけます。
A1：「日付」
B1：「年/月」
C1：「保有株数」
D1：「投資金額($)」
E1：「実現損益($)」
F1：「株価($)」
G1：「評価額($)」
H1：「評価損益($)」
I1：「配当受取金額($)」
J1：「配当受取累計($)」
次に各項目に数式等を埋め込みます。
A2には適当な年月の月末の日付を入力します。
そしてA3にはA2でつけた年月の翌月の日付を入力します。
今回はサンプルのコカコーラ(KO)の配当データが2009年4月であったため、
A2には「2009/3/31」
A3には「2009/4/30」
と入力しました。
そして、（つぎが少し大切です）
②のAの部分をポチでA列を選択し、
そのまま③「表示形式」をポチ、④「数字」をポチ、⑤****/**(今回は2008/09になっています)をポチします。
日付の表示方式を変更したわけです。
下↓のようになっていたらgoooooodです！
そして、
B2には「=CONCATENATE(LEFT(A2,4),“/”,RIGHT(A2,2))」と入力します。
（A2の左の４つの文字と、/、A2の右の２つの文字を組み合わせを組み合わせた文字列をつくる関数です）
下↓のようになります。
そして、C2、D2、E2、は飛ばして、次に入力するにはF2です。
上↑のようにF２には「=VLOOKUP(A2,‘株価データ’!$1:$10000,3)」と入力します。
（株価データシートにある、A2と同じ日付データの株価を持ってきてくれという関数です）
そうすると…
関数が、株価データシートから2009年3月31日の株価データを持ってきてくれました！
さらに行きます。
G2には「=C2*F2」(保有株数＊株価)
H2には「=G2–D2」(評価額-投資金額)
次のI2は少し長い関数になります。
I2には上↑のように「=IF(ISERROR(VLOOKUP(B2,‘個別企業配当($)データ’!$1:$1000,2,FALSE)),0,
VLOOKUP(B2,‘個別企業配当($)データ’!$1:$1000,2,FALSE))」といれます。
（データあれば個別企業配当($)データシートのB2と同じ文字列データの配当データをもってきてちょって関数）
んで、最後の、
J2には「=IF(J1=“配当受取累計($)”,0,J1+I2)」と入力します！
3-3-3集計シートを一気につくりあげる！
関数を入れなかったA列（日付データは入れた）、C列、D列、E列は入力が必要な列ですので、わかりやすいように色をつけます。
①をポチしてA列を選択します。
②のバケツをポチして、③で好きな色を選択します。
C列・D列・E列も同じように色をつけましょう。
そして、C2、D２、E2に実際の数字を入力します（適当な数字でもかまいません）。
(↓ミスターマーケットは実際の数字：C2に100、D２に2957、E2に０を入力しました)
さっそく、2009年3月末の評価損益が-759と計算されました。
んで、次はB２からJ２をしたのセルにコピーします。
オートフィル機能をつかいます。
（【1-3-3:集計のために少しデータを加工する】でも説明しましたので、下の解説でわからない場合は詳しくはそちらを見てください）
④をポチってしたままでー、⑤までづらーとマウスをもってきて、一回指を離してB2からJ２を選択します。
そして、選択して青色かかっている枠の右下⑥にマウスカーソル：矢印↑を近づけると矢印↑が「十」になるので、
ポチーとしたまま、下におろします。
そうすると、コピーされますB2からJ２がB3からJ２にコピーされます。すると…
2009年4月30日の株価データや、その月の配当データも持ってきてくれます。
そして、いよいよ、集計シートを一気につくりあげる時がきました。
さきほどと同じように、オートフィル機能をつかってさらに下にコピーしていきます。
今度は⑦A2から⑧J３を選択して、
⑨で「十」にして、ずらーとしたにのばしてください（最新の年月まで）。
そうすると…
今回は2017年12月のデータまでをつくりました。KOからの配当受取金額は1300ドルにもなっていました！
全体をみると下↓のような感じです！
つ、ついに、
2009年から2017年までの株価推移や配当データが一気に集計されたのです！
あとはグラフをつくるだけです。下↓のような感じで！
グラフをつくるのはとっても簡単ですよ！
っとはいっても、少し疲れたので、
グラフ作成の説明は後日にしたいと思います。
ミスターマーケットの本日のノイズでした。
