ピボットテーブルとグラフで日経平均をサクッと分析！Excelでカンタン株価チェック

以前、S&P500でやった株価指数の見える化を、日経平均株価でやってみます。
ピボットテーブルやピボットグラフを使う取っ掛かりとして、ゆるく読んでいただけたら嬉しいです！

前回はS&P500の株価データでいろいろ試してみましたが、今回はその日経平均株価バージョンです。

といっても、やることはほとんど同じ。

Power Queryで取り込んで整えたデータを、ピボットテーブルでいじるだけ！

入力データ（銘柄や指数）を変えるだけで、年別・月別・曜日別・日別の分析がそのまま使いまわせるってところが、この仕組みのいいところなんです。

ちょっと手を加えるだけで、他の銘柄や指数にも応用できます。

難しいことはナシで、「へ～、Excelってこんなこともできるんだ」くらいの軽いノリで楽しんでいってください！

注意

本記事で使用しているデータは、あくまでピボットテーブル、ピボットグラフ、Power QueryなどのExcelの機能を紹介するためのものであり、内容の正確性や整合性を確認したものではありません。
データはサンプルとして取り扱っており、実際の株価や市場データとは異なる場合があります。
本記事の内容をもとに、株式売買などの投資判断を行うことは推奨しておりません。
株式取引はリスクを伴う行為であり、最終的な判断はすべてご自身の責任でお願いいたします。
データの活用については、目的に応じて適切な確認・加工を行った上で使用してください。

まずは準備！加工済みの株価データを読み込み
年ごとのデータを見てみよう！
日別データをグラフで見てみよう！
まとめ

まずは準備！加工済みの株価データを読み込み

今回は、日経平均株価を使っていきます！

期間は1970年から2025年5月までと、かなり長めのスパンです。

データをExcelにテーブル形式で読み込み、Power Queryや関数を使って「前年差」「年リターン（前年比）」「対数」なんかを計算してみました。

年リターンはシンプルに「（年末の終値 ÷ 前年末の終値）－１」で求めます。

対数の計算は常用対数を取っていますが、グラフの「軸の書式設定」で対数スケールに切り替えることもできますよ。

※あくまで素人の計算なので、完璧な精度じゃないかもですがご容赦を！

グラフを作るときは、メニューの「挿入」→「ピボットグラフ」を選んで、あとは好きなグラフの種類をクリックするだけ！

ピボットグラフじゃできないタイプのグラフは、「挿入」からグラフの種類を選んで作ればOKです。

今回もグラフの見た目にはあまりこだわらず、とにかくサクッと作っていきますね！

年ごとのデータを見てみよう！

まずは、Power Queryで加工した「年別データ」を使っていきます！

とりあえずグラフにして、ざっくり株価の流れをチェック！

シンプルに、その年の「高値」「安値」「年末の終値」を折れ線グラフにしてみましょう。

青：年末終値
オレンジ：年の高値
グレー：年の安値

やっぱり株価といえば「ローソク足」も見ておきたいですよね。
ということで、ローソク足チャートでも表示してみました！

対数スケールで見ると印象が変わる！？

まずは、通常のグラフと対数グラフを並べて比較してみます。

青：ふつうの株価グラフ
オレンジ：対数スケールのグラフ

1970年と2024年の位置がだいたい同じになるように、縦軸をちょっと調整してます。

青い線（日経平均）は、1990年ごろにドーンと高値をつけたあと、ガクッと下がってます。

一方、オレンジの線（対数スケール）で見ると、実際よりも変動がゆるやかに見えて、あの急落も少しマイルドな印象になってます。

長い目で見ると、対数のグラフでは株価が上昇トレンドを持っているようにも見えますね。

次は、「年末終値・年高値・年安値」をぜんぶ対数で表示してみましょう！

対数グラフへの切り替えは簡単。
グラフの「軸の書式設定」→「対数目盛を表示する」にチェックを入れるだけ！

青：年末終値
オレンジ：年高値
グレー：年安値

対数グラフだと、特に昔（前半）の値動きの違いがよく見えるんです！

ローソク足チャートも対数で表示してみます！

これもやり方は同じで、「軸の書式設定」→「対数目盛を表示する」にチェックするだけ。

見やすくするために、「軸のオプション」で目盛の数値も少し調整していますが、対数グラフへの変更自体はとっても簡単ですよ！

年ごとのリターンをグラフで見てみよう！

株価グラフに「年リターン」を重ねて表示してみます！

続いて、対数グラフにも「年リターン」を重ねてみました。

見てください、1970〜80年代の伸びっぷり！

逆に1990年代の元気のなさ…はっきり出てますね。

リターン（1年ごとの上がり幅・下がり幅）は、けっこうアップダウンが激しいことが分かりますね！
振れ幅がジェットコースター級…！

おそろしい世界です……。

年平均リターンとリスクを出してみよう！

ここでは、年ごとの平均リターンを出してみます！

計算するのは「算術平均リターン」と「幾何平均リターン」の２パターン。

以下の計算式で計算しています。

算術平均（各年のリターンを全部足して、年数で割る！）
各年の年リターン / 年数
幾何平均
{ ( 期間の最後の終値 / 期間の最初の前日終値 ) ^ ( 1 / 年数 ) } – 1

算術平均は、ピボットテーブルで「平均」に設定すればOK！

一方、幾何平均は期間の最初と最後の値が必要なので、Excel関数で計算するのがラクです。

そしてリスク＝標準偏差。

これもピボットテーブルでカンタン。
「値フィールドの設定」→「標準偏差」に変更すれば自動で計算してくれますよ！

また、検算用にいくつか関数も使ってチェックしています。

=SUBTOTAL(2,範囲)

範囲内のデータの個数（COUNTA）を数えます。
※フィルターがかかってるときにも対応してくれる便利関数！

=SUBTOTAL(1,範囲)

範囲内の平均（AVERAGE）を計算します。
こちらもフィルター対応！

=STDEV.P(範囲)

範囲内の標準偏差（全体母集団）を求める関数。
いわゆるリスク（値動きのブレ）を数値化してくれます。

実際の計算結果がこちら。
気になる期間を抜き出してます！

期間	年数	算術平均リターン	幾何平均リターン	リスク
1970年～2024年	55年	7.86%	5.19%	23.69%
1970年～1989年	20年	17.23%	15.07%	23.73%
1990年～2011年	22年	-4.22%	-6.70%	21.77%
2012年～2024年	13年	13.88%	12.68%	16.98%
1990年～2024年	35年	2.51%	0.07%	21.94%