前回の記事の続きです。
前回は、過去分析シートと評価基準シートの移植が終わったので、今回は銘柄表シートをExcelからスプレッドシート（以下「スプシ」）に移植していきます。
これが終われば、Microsoft365解約（年14,900円の節約）に向けて道のりを３割ほど消化できたことになります。
高配当銘柄分析ファイルの乗せ換え作業２ – HYPERLINK関数の修正・レイアウト変更
高配当銘柄分析ファイルについて
高配当銘柄分析ファイルについては、これらの記事に詳細を記載しています。
ちょっとずつ作り込んでいったこれらの機能をスプシに移植することになるので、それなりに時間はかかるのかなー？、といったところです。
銘柄表シートの移植
銘柄表シートは、YAHOO!ファイナンスの配当利回りランキングのページからデータをコピペするためのシートです。
ペーストするのはＢ〜Ｇ列の範囲です。
参考に、Excel版での動きを載せておきます。
（Excel版コピペの様子を動画掲載）
東証のみを選んでいるのは、単元未満で購入できるのが東証銘柄のみだからです。資金が潤沢にあって、「俺は１００株単位で買っていくぜ！」という方はこの工程は不要です（羨ましい…ｗ）。
というわけで、とりあえずそのままExcel→スプシへ貼り付けしてみた結果がこちらです。
左がExcel、右がスプシです。
例によって、関数その他は抹消されていると思われますので、調べていきます。
ついでに、条件付き書式も抹消されて、ただの色付きセルになってしまっています。
このままだと若干作業がやりにくいので、一旦「塗りつぶしなし、文字色黒」にしました。
H列：COUNTIF関数
Ｈ列には、COUNTIF関数を仕込んでいます。
これは、Ｃ列にペーストされた銘柄コード（１位の銘柄についてはＣ５セル）が、過去分析シートのＡ列に存在かどうかを判定しています（存在すれば「有」を表示）。
というわけでCOUNTIF関数をサックリコピペします。
範囲名が不明と出ましたので、過去分析シートに範囲名を設定していこう…と思ったんですが、前回設定した範囲名「銘柄コード」がそのまま使えることに気づいたので、数式の検索範囲の方を変更していきます。
検索範囲をすでにある範囲名に変更するだけなので、変更したら関数が動くようになりました。
Ｉ列：HYPERLINK関数
Ｉ列は、前回散々苦戦したHYPERLINK関数です。
以下は、Excel側のI４セルの数式を抜き出したものです。
=HYPERLINK(“#過去分析!A”&MATCH($C5,銘柄コード,0),IF($H4=”有”,VLOOKUP($C5,過去分析結果,$I$2,0)))
動きを説明すると、以下の通りです。
・リンク部分
→C5セルの銘柄コードが過去分析シートにあれば、そのセルへのリンク
・別名部分
→C5セルの銘柄コードが過去分析シートにあれば、その入力日
前回「過去分析」シートを貼り付けたときと同様、関数部分は削除されてしまっていますが、なぜかリンクだけは残ってしまっています。
このリンク情報が残っていると、作業に支障をきたしそうです。
これだけ消すのはどうするのかなー？と思って色々試してたんですが、「右クリック」→「セルでの他の操作項目を表示」と選択すると、「リンクを削除」メニューがあったので一括削除しておきました。
この時点では気づいていませんが、後々この操作が原因で混乱することになりますｗ
Excel側から関数を移植
それでは、ひとまずExcelから関数を移植します。
何も考えずI4セルに移植したいところですが、ここは元々過去分析シートにない銘柄なので、うまく動いているかどうか分かりづらいです。動いたときにわかりやすいよう、過去分析シートにすでにデータがあるI12セルに移植していきます。
そして、シート名・セル番号の指定形式を直して、
貼り付けた行に合わせて関数内の行番号を修正し、
セル範囲「過去分析結果」を設定していきます。
前回と同じく、範囲設定しただけだと関数がうまく動かなかったので、MATCH関数の中の検索の型を変更して動くようにしています。
リンク再設定
さて、ちゃんとHYPERLINK関数の別名表示（日付）は出るようになったものの、そのセルをクリックしてもリンクが出てきてくれません。
原因調査のため、J列を挿入し、MATCH関数とIF関数を単独で抜き出して見ましたが、それぞれちゃんと動作はしているようです。
ということは、問題はHYPERLINK関数と思われます。
何が問題なのかわからず、２時間くらいあれやこれやと試していたのですが、原因はコレでした。
前前項「Ｉ列：HYPERLINK関数」にて、Excelでのリンクが残っていたのを削除していましたが、それが原因でリンクにならなかったんです。
どうやらスプシの「リンクを削除」メニューは、
× その時点で貼り付けられているリンクを削除
◯ そのセルのリンクを無効にする
の設定のようで、前前項でリンクを削除していた設定がそのまま残っていて、新しいリンクまで無効になってしまっていました。
動くようになったので、Ｊ列は削除しておきます。
Yahoo!ファイナンスより貼り付け
Ｊ列より右は、すべてVLOOKUP関数とIF関数の組み合わせであり、前項のHYPERLINK関数の別名部分と形は同じなので、前項の時点で目処は付いています。
なのでここで一旦関数の移植を中断し、動作確認のためにYahoo!ファイナンスの配当利回りランキングページからデータを貼り付けしてみることにしました。
Excelとスプシで貼り付き方が違う件
結果がこちらです。
見事にレイアウトが崩れてしまっています。
Excelでは「名称・コード・市場」のデータがC列のセル４行に分かれて貼り付きますが、
スプシで同じことをすると、１つのセルにまとまって貼り付いてしまうようです。
というわけで、この貼り付き方に合わせてレイアウトを変更していきます。
コレを先にやっとけばよかったと凹みますが、Ｊ列以降の移植作業の前でちょっとだけ助かりました。たまたまですがｗ
スプシでの貼り付き方に合わせてレイアウト変更
まずは、「名称・コード・市場」のデータが一つのセルに貼り付いてしまうのに合わせ、セル結合を解いていきます。
そして、今までＨ列・Ｉ列の計算の引数としていた銘柄コードですが、他の情報と一括でＣ列のセルに貼り付いてしまっているので、別のセルに銘柄コードの４桁の数字のみを抜き出してやる必要があります。
どうしようかと考えたんですが、今のところは一株単位で購入できるのが楽天証券・ＳＢＩ証券ともに東証銘柄のみなので、Yahooから貼り付けるデータも東証のみに絞っています。
なので、Ｃ列には必ず「東証」の文字が入っています。
これを根拠に、「東証」の文字を検索して、その文字の前４文字を抜き出す関数を組みました。
これで、Ｈ列を引数にしてI列・J列の関数を動かせると思うので、引数とするセルを変更していきます。
名証とか大証とかの銘柄も買えるようになったら、この方法は使えなくなっちゃうんで、また別の方法を考えないといけないんですが、いまのところはこの方法で行っておきます。
4行目の「3205:ダイドーリミテッド」はまだ「過去分析」シートになくて、修正が成功かどうかわかりづらいので、5行目の「5950:日本パワーファスニング」で修正していきます（C6をH5に変更）。
I列は引数セルを修正したら動いたんですが、J列はそれだけでは動きませんでした。
一瞬「やべっ」って思ったんですが、H列にVALUE関数をかましたらちゃんと動いてくれました。
どうやらYahooファイナンスから貼り付けたデータは文字列だったようで（他の文字が日本語なので当たり前なのですが）、片や過去分析シートの方が数値なので、それで一致せずにエラーとなったようです（I列がなぜ動いたのかは謎ｗ）。
ちなみにVALUE関数とは、文字列となっているものを数値に変換する関数です。
VALUE関数とは（ググった結果）
というわけで、あとは５行の数式をその他の行全てに貼り付けて、H〜J列の数式は完了です。
K列以降の数式設定（VLOOKUP関数）
では、先程目処が付いていて後回しにしていた、K列以降の数式を設定していきます。
数式のみ抜き出すと以下の通りです。
=IF($I42=”有”,VLOOKUP($H42,過去分析結果,K$2,0))
こちらの式は、
① もしI列が”有”なら
② 過去分析シートから、H列の銘柄コードを検索し、その行の左から指定した数字だけ右に行ったセルの内容を表示する
③ どれだけ右にいくかは、このセル（上の写真ではK42セル）の同じ列の２行目に入った数字による
という動きをさせています。
条件付き書式の設定
あとで分析結果の確認や修正がしやすいよう、また購入判断の補助とするため、条件付き書式の設定をしていきます。
条件付き書式とは、セルの内容が特定の状態になったとき、文字色やフォント・塗りつぶし色などが所定の状態になるように設定できる機能です。
たとえば、J列（日付が入るセル）には、このように設定します。
分析から６ヶ月以上経てば薄黄色の塗りつぶし、６ヶ月以内なら薄緑の塗りつぶし、分析結果が無い場合は薄赤での塗りつぶし、と３パターン設定しています。
※ 説明がもれていましたが、過去分析結果が無い場合は「なし」の文字を表示するように、J列の数式を変更しています
K列については、景気敏感であれば「赤の背景に白抜き文字」、ディフェンシブであれば「水色背景」となるように設定しています。
設定方法はこんな感じです。
シートコピー用のGAS設定
スプレッドシートでシートをコピーすると、『「シート名」のコピー』という名前が自動で付いてしまいます。
さらにコピーすると「〜のコピーのコピー」なんてちょっとマヌケな名前になってしまいますｗ
Yahooファイナンスからデータをコピーしてるので、銘柄表のシートにはYahooファイナンスのページ番号と同じ番号を振りたいわけです。
Excelならその点、「シート名(番号)」という形で名前がついていくし、最初のコピーの番号が(2)から始まってくれたので、自然と番号が一致してたんです。
なんかうまいことコピーしたシートに連番をつける方法がないかなー、と探していたところ、こんな動画を見つけました。
スプレッドシートには、ExcelのVBAに相当する「GAS」という機能がありますが、その機能を使って、
・シートコピー
・コピーしたシートの名前変更
まで一気にやってしまう、というプログラムのようです。
これは概要説明用のショート動画ですが、概要欄の最下段に本編動画へのリンクがあります（３１分）。
これを丸パクリして、僕のファイルにもシートコピーボタンを作ってみました。
動きはこんな感じです。
GASの内容の解説はできません。僕が半分くらいしか理解してないのでｗ
ってなわけで、高配当株分析ファイルはひとまず移植完了です。
編集後記
現在、タイマムシン家は夫婦で日本通信ＳＩＭを使っております。
契約内容と月額料金は以下の通りです。
僕：月額2178円 通信上限30GB 無料通話70分
奥さん：月額1390円 通信上限10GB 無料通話70分
これで二人共十分に快適に使えているのですが、このほど日本通信ＳＩＭさんからとんでもない発表がありました。
なんと料金はそのままに、10GBプランは20GBへ、30GBプランは50GBへ増量とのことです。
相変わらずこの会社、料金設定がバグってます。良い方にｗ
奥さんは月々３〜５GB程度しか使わないので、もっと小さいプランがあればそっちにしたいところなのですが、残念なことに20GBより小さいプランだと、一気に1GBまで小さくなってしまって、あまり現実的ではないです。
なので奥さんは、今のプランのままとなる予定です。
問題は僕のプランで、多分50GBなんてどんなに頑張っても行かないわけです。
今まで一番何も考えずに使った月で、26GB程度だったんです。
ちょっと意識しておけば、多分20GBまでに抑えることは容易なんですよ。
ってなわけで、僕のプランは１段階落として、20GBのプラン（月額1390円）に変更するつもりです。
日本通信SIMさんには申し訳ない結果になってしまいました。なんせせっかく企業努力で増量してくれたのに、僕らの方は回線あたりの収入を下げることになっちゃいましたからｗ
だって50GBなんて絶対使わないんだもん…
代わりに宣伝しとくから許してちょｗ
というわけで、罪滅ぼしに日本通信ＳＩＭの申し込みページのリンクを貼っておきます。
ちなみに日本通信ＳＩＭにアフィリエイトはありません。
みなさんがこのリンクから申し込まれても、僕には１円も入って来ないですｗ
ではまた。
