配当集計講座1-3 配当集計シートの作成しよう

どうも

ミスターマーケットです。

Mr.マーケットの配当集計講座です。

今回は配当集計講座1-3です。

 

 

配当集計講座1-2では入力したデータのスプレッドシートを作成しました。

今回はそのスプレッドシートをデータ集計のために加工します。

【1-3-1:集計用のスプレッドシートをつくる】

前回の1-2-2で作成したスプレッドシートは

入力したデータを格納専用のスプレッドシートとし、

集計には集計用のスプレッドシートを利用します。

新しいグラフの作成等でスプレッドシートを触っているときに

誤ってデータを触り壊さないためです。

まずはフォルダに戻り、

①をポチ。

②をポチ。

新しい真っ白なスプレッドシートが誕生します。

赤枠の「無題のスプレッドシート」に名前をつけます。

Mr.マーケットは「配当集計」とつけました。

で、次は少しレベルがあがります。

慎重に聞いてくださいね。

【1-3-2:データ格納用Sのデータを集計用Sにリンクさせる】

まずは1-2-2で作成した格納用S「配当入力(回答)」を開きます。

まずは格納用S「配当入力(回答)」のURLの

赤横棒の部分(https://docs.google.com/spreadsheets/u/0/d/○○○○○○○○○○○○○○○○○○○○○○○○○○○○○○/editの○の部分をどこかにコピーします。

次に格納用S「配当入力(回答)」のシートの名前を変更します。

画面左下の①をポチ。

②名前を変更…をポチ。

そして「配当データ」という名前にしてください。

↓のようになります。

そして1-3-1で作成した真っ白なスプレッドシートに戻ります。

そしてさきほどコピーした

○○○○○○○○○○○○○○○○○○○○○○○○○○○○○○の部分を

=IMPORTRANGE(○○○○○○○○○○○○○○○○○○○○○○○○○○○○○○,“配当データ!A1:AX2000”)の○に入れてどこかに関数Aを作成しておきます。

(データを移行させる関数です)

次にA1のマスをダブルクリックします。

マスに入力できるようになったらさきほど作った関数Aをコピペします。

そうすると…

#REFというエラーがでますが、大丈夫です。

③の「#REF!」をポチっ。

④の「アクセスを許可」をポチっ。

そうすると…

こんな感じで格納用Sから集計用Sに

データをリンクされます。

1で作成した入力画面に入力すると

2で作成した格納用スプレッドシートにデータが入り

3の今回作成した集計用スプレッドシートにもデータが反映されます。

【1-3-3:集計のために少しデータを加工する】

さきほどの画面を右にずらします。

AXまでは配当のデータが詰まっています。

AからAXまでは触りません

(触らないようにわかりやすいように色を塗っておくといいですよ)。

AY1をクリックします「年」と入力します。

同様に、

AZ1に「月」

BA1に「銘柄コード」

BB1に「受取配当金額(¥)」

を入力します。

AY2をダブルクリックして

「=IF(B2=“国内”,TEXT(D2,“yyyy”),TEXT(L2,“yyyy”))」

をコピペしてください。

・・・・解説・・・・

意味はもしB2が「国内」だったら、

「D2を西暦4桁で」いれてくれ、

違ったら「L2を西暦4桁で」いれてくれって感じです。

国内か、海外かで日付のデータがある位置が変わるため

必要な処理になります。

・・・・・・・・・・・

同様に

AZ2に「=IF(B2=“国内”,TEXT(D2,“yyyy/MM”),TEXT(L2,“yyyy/MM”))」

をコピペしてください。

BA2に「=IF(B2=“国内”,$C2,$O2)」をコピペしてください。

BB2「=IF($B2=“国内”,$K2,ROUNDDOWN($AF2*$AO2,0))」をコピペしてください。

そして

①AY2からBB2までを選択(ドラッグ)し、

②マウスのカーソルを②の位置に持ってきます。

カーソルが十字になったらドラックして下↓にひっぱります。

(こんな説明でいいのでしょうか汗)

(オートフィル機能と言います。以降も使いますので覚えておくべし)

一番した↓までひっぱって離すとコピーされます。

一番右下の関数をみると

=IF($B1000=“国内”,$K1000,ROUNDDOWN($AF1000*$AO1000,0))」

になっていると思います。

勝手に参照するマスを変更してくれています。

これで1000回ほどの配当までは耐えてくれます。

その時には小金持ちになっているでしょうか笑

まずは上に戻ります。

2つ目のコカコーラのデータも確かに計算されています。

BB3には受取配当金が円建で計算されています。

これで国内株と海外株の配当を同時に集計できるようになりました。

【1-3-4:ピボットデーブルをつくる】

そして、次が「ピボットデーブル」です。

表計算の最も役にたつ機能の一つです。

①の左上のマスをポチっ(シート全選択します)

②「データ」をぽち。

③「ピボットテーブル」をぽちっ。

(データの範囲を確認する画面がでた場合)

①を押して、データ範囲をシート全体にしてOKをぽち)

これでできました!

した↓のシートができたと思います。

これがピボットテーブルです。

右にある「レポートエディタ」の

「行」「列」「値」「フィルタ」を操作すると

自動的に集計して表を作成してくれます。

長くなりましたので

ピボットテーブルの使い方は次回にします。

今までは事務作業ばかりで大変だったと思います。

ピボット使い方はきっと楽しいと思います。

ミスターマーケットの本日のノイズでした。

今後ともよろしくお願いします。

SPONSORED LINK

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


ABOUTこの記事をかいた人

どうも!ミスターマーケットです。 Mr.マーケットは30代半ばの普通の会社員です。20歳から株式投資をはじめ、現在は日本株と米国株で配当を中心に長期投資を行なっています。よろしくお願いします。