令和四谷怪談。井戸の底から日産ホルダーの声が聞こえる…「一枚…、二枚…、三枚、…、…ああ…足りない、配当金が足りない…」
— 千鳥足＠投資初心者 (@chidoriashi_sh) February 22, 2020
株式投資で得られるいちばんの愉しみって、入金された配当金を数えることですよね。
入金履歴を確認しながら配当金を数えるとき、投資家はきっと越後屋の店主のような顔をしておかねを数えます。
支払われた配当金の履歴をEXCEL等でちまちま管理している投資家はたくさん居ると思います。もちろんわたくし千鳥足も次のような配当金管理簿を作成して記録しています。
配当金の履歴を管理していると、ふと前回の配当に対する増減率を知りたくなります。配当金を愉しむ投資家にとって株式の増配率って大事ですものね。
そこで、配当金の管理簿に「増減率」と云う列をおもむろに足したあとで、ハテ、足した列にどう数式を入れて良いか迷う個人投資家さんが多いのではないか？と思うので、解決方法の一例を示します。
配当金の増減率を記録するのはチョット面倒
手間暇を惜しまず「一枚…、二枚…、」と手間暇かけて数えたい方は、以下のような数式を入れていけば増減率を計算できますね。
でも保有銘柄の数が多くなると配当金管理簿の行数は瞬く間に増えていきます。各行に個別の数式を入力するのは面倒です。またたくさんの履歴の中から、同じ銘柄の前回の配当に該当する履歴を探すのも面倒になります。
まあそりゃね、手間と云っても大層な手間ではありません。数式は簡単な割り算ですし、前回の履歴を探すのもフィルタで絞って探せばほんの数秒でしょう。
でも手間暇惜しむ賢い投資家は、わざわざフィルタ絞って参照先を確認しいちいち個別の数式を入力する時点で面倒くさくて発狂してしまいます。面倒すぎる!! と思う気持ちが人の小技を進歩させますｗ
ちなみにこれくらいの面倒ならへっちゃら!! と云う方も、上の画像の数式のままですと、一覧を並べ替えたときなどに参照先がずれてしまい記録が壊れてしまうので注意ですよ。前回履歴の参照は、$F$22 のように絶対参照にしておきましょうね。
手間暇を惜しむ投資家の配当金増減率の算出方法
と云うことで、手間暇を惜しむ賢い投資家のみなさまのために、配当金増減率の算出の仕方をお教えしましょう。
XLOOKUPが使える環境なら超簡単
2020年1月30日にリリースされた Office365 の更新プログラムでEXCELにXLOOKUPなる関数が正式リリースされています。
関数名を入力して構文を確認してみると、こんな構文になっています。
追加したG列「増減率」の先頭行（セルG2）に以下のような数式を入れます。
=F2/XLOOKUP(B2,B$1:B1,F$1:F1,F2,0,-1)-1,”-“)
検索範囲と戻り範囲の指定で「B$1:B1」「F$1:F1」のように始点だけ行が絶対参照になっている点がミソです。
先頭行（セルG2）に入れた数式を列の最終行までコピーしましょう。
コピーを終えると増減率が算出されます。超簡単!! なお、各銘柄の最初の履歴は増減率を算出できないので0.0%となるようにしています。
XLOOKUPの引数「検索モード」に「-1」を指定すると「末尾から先頭へ検索」となります。
そして「検索範囲」と「戻り範囲」の指定で「B$1:B1」「F$1:F1」のように範囲指定の始点の行だけが絶対参照にしてあったために、コピーした数式の参照先は以下のように始点の行はそのまま、終点の行だけ相対参照でコピーされます。数式がある行以前を検索範囲として、同銘柄がある行を探す数式になっています。
VLOOKUPを用いて実現する場合（XLOOKUPが使えない場合）
XLOOKUPが使えないバージョンのEXCELでは少し工夫が必要です。
最左端に一列追加します。タイトルを適当に付けて、先頭行（セルA2）の数式は以下のようにします。
=C2&”-“&COUNTIF(C$2:C2,C2)
COUNTIF関数で指定する「範囲」が「C$2:C2」と始点の行のみ絶対参照にしている点がミソです。
この数式を列の最終行までコピーしましょう。以下のようになりますね。
銘柄ごとに先頭行からの登場回数を数えて、銘柄のコードとハイフン「-」で結んでいます。
ここまでできてしまえば、あとは簡単です。
増減率の列の先頭行に以下の数式を入力します。
=G2/IFNA(VLOOKUP(C2&”-“&COUNTIF(C$2:C2,C2)-1,A:G,7,FALSE),G2)-1
数式の参照先は以下のようになりますね。 なお、各銘柄の最初の履歴は増減率を算出できないので0.0%となるようにしています。
この数式を増減率の列の最下行までコピーしましょう。以下のように増減率が算出されます!!
何が起きているのか分からん!! と云う方は、コピーした先のセルに設定された数式の参照先を確認してみてください。COUNTIFの「範囲」の指定で「C$2:C2」と範囲指定の始点の行だけが絶対参照にしてあったために、コピーした数式の参照先は始点の行はそのまま、終点の行だけ相対参照でコピーされています。
COUNTIFでカウントした回数からマイナス1することで一つ前の履歴を探すキー値を作成し、A列を検索します。
増配率の列の書式設定
個人的な好みなのですけれども、増減率は増えた場合には「+9.9%」、減った場合には赤字で「-9.9%」と表示したいです。
増減率の列の書式設定で「表示形式」で「ユーザー定義」を選択して、書式を以下のように入力して設定します。
+0.0%;[赤]-0.0%
OKをクリックして反映すると、以下のようになります。お好みは人それぞれだと思うので、ご自由にしてくださいね。
