エクセルでテクニカル分析を自作する方法【計算式・設定手順を初心者向け解説】

SHARE

株式投資を始めたばかりの方にとって、テクニカル分析ツールは高価だったり操作が難しかったりして、なかなか手が出せないと感じていませんか。実は、普段の仕事で使っているエクセル（Excel）があれば、本格的なテクニカル指標を自分で計算し、チャート分析を行うことが可能です。

エクセルでテクニカル分析を自作すれば、パラメータの細かい調整が自由にできますし、計算のロジックを理解することで指標の本質的な意味も深く理解できるようになります。この記事では、エクセルを使って主要なテクニカル指標を計算する方法を、初心者の方にもわかりやすく順を追って解説していきます。

目次

目次

  • エクセルでテクニカル分析を行うメリット
  • テクニカル分析の準備：株価データの取得方法
  • 移動平均線（SMA）をエクセルで計算する
  • ボリンジャーバンドの計算と設定手順
  • RSI（相対力指数）をエクセルで算出する
  • MACD（移動平均収束拡散）の計算方法
  • エクセルでチャートを描画するコツ
  • テクニカル分析エクセルファイルの活用と注意点
  • まとめ

エクセルでテクニカル分析を行うメリット

市販の株価分析ソフトや証券会社が提供するチャートツールはとても便利ですが、エクセルで自分の手でテクニカル指標を作ることには大きなメリットがあります。

計算ロジックを理解できる

エクセルで一つひとつ計算式を組み立てることで、テクニカル指標がどういう計算式で算出されているのかを肌で理解できます。たとえば移動平均線なら「過去○日間の終値の平均」というシンプルな仕組みですが、これを自分で計算してみることで、なぜ価格が平均線を上回ると上昇トレンドと言われるのかが腹落ちするようになります。

パラメータの自由なカスタマイズ

多くのチャートツールでは、移動平均の期間やRSIのパラメータがあらかじめ決まっていることが多いです。しかしエクセルなら自分で好きな期間を設定でき、銘柄や市況に応じて細かく調整できます。たとえば「5日移動平均だと反応が早すぎるから7日で試してみよう」といった実験が簡単にできるのです。

コストがかからない

エクセルは多くの人がすでにパソコンに入れているソフトウェアです。追加で高額な分析ツールを購入する必要がなく、初期費用ゼロでテクニカル分析を始められます。

バックテストや検証が容易

過去の株価データを使って「この指標でトレードしていたらどうなっていたか」を検証するバックテストも、エクセルなら数式を少しコピーするだけで実行できます。検証結果を見ながら、自分に合った売買ルールを構築する助けになります。

テクニカル分析の準備：株価データの取得方法

エクセルでテクニカル分析を行うには、まず株価の過去データを準備する必要があります。ここでは代表的なデータ取得方法をご紹介します。

証券会社のサイトからダウンロード

多くの証券会社では、自社の取引ツールやウェブサイトから過去の株価データをCSV形式でダウンロードできます。たとえば楽天証券のマーケットスピードやSBI証券のHYPER SBIなどでは、銘柄ごとの日足データを取得可能です。

Yahoo!ファイナンスなどの無料サイト

Yahoo!ファイナンスをはじめとする株価情報サイトでは、個別銘柄の過去データをCSVでダウンロードできるサービスを提供していることがあります。無料で使えるため、初心者の方はまずここから試してみるのがおすすめです。

エクセルのデータ取り込み機能を活用

エクセルには「データの取得と変換」機能があり、ウェブ上の表を直接取り込むことも可能です。ただし、取得元サイトの利用規約を必ず確認し、スクレイピングが許可されているかどうかを確認してください。

データの整理と前処理

取得したデータには日付、始値、高値、安値、終値、出来高などが含まれています。エクセルシートに貼り付けたら、以下の点を確認しましょう。

  • 日付の並び順:古い日付が上、新しい日付が下の昇順に並んでいるか
  • データの欠損:祝日や取引停止日のデータが抜けていないか
  • 数値形式:価格や出来高が数値として認識されているか（文字列になっていないか）

これらを整えておくことで、後の計算がスムーズに進みます。

移動平均線（SMA）をエクセルで計算する

移動平均線（Simple Moving Average, SMA）は、テクニカル分析の中でも最も基本的で広く使われている指標です。一定期間の終値を平均することで、価格のトレンドを滑らかに表示します。

移動平均線の計算式

n日間の単純移動平均（SMA）は次の式で求められます。

\(
\text{SMA}_n = \frac{\text{終値}_1 + \text{終値}_2 + \cdots + \text{終値}_n}{n}
\)

たとえば5日移動平均なら、直近5日間の終値を足して5で割ります。

エクセルでの計算手順

以下の手順で、エクセルで移動平均線を算出しましょう。

  1. 株価データを配置:A列に日付、E列に終値があると仮定します（B列は始値、C列は高値、D列は安値など）。
  2. 計算列を用意:F列に「5日移動平均」という見出しをつけます。
  3. AVERAGE関数を使う:たとえばF6セルに =AVERAGE(E2:E6) と入力します。これでE2からE6までの5つの終値の平均が計算されます。
  4. 数式をコピー:F6セルを選択し、セルの右下角をダブルクリックするか下方向にドラッグして、数式を必要な行までコピーします。

これで、日ごとの5日移動平均が自動的に算出されます。

OFFSET関数で期間を可変にする

より柔軟な設定をしたい場合は、OFFSET関数とAVERAGE関数を組み合わせる方法があります。たとえばセルH1に期間（5や25など）を入力しておき、以下のような数式を使います。

=AVERAGE(OFFSET(E6, -$H$1+1, 0, $H$1, 1))

この数式を使えば、H1セルの数字を変えるだけで、移動平均の期間を一括で変更できるため、複数のパラメータを試したいときに便利です。

複数の移動平均線を同時に表示

実際のトレードでは、5日移動平均と25日移動平均を同時に表示し、短期線が長期線を上抜ける「ゴールデンクロス」を売買サインとすることがよくあります。G列に「25日移動平均」を追加し、同じ要領でAVERAGE関数を設定してください。

ボリンジャーバンドの計算と設定手順

ボリンジャーバンドは、移動平均線を中心に、価格の変動幅（標準偏差）を上下に表示するテクニカル指標です。価格が統計的にどの程度のばらつきを持っているかを視覚化し、買われ過ぎや売られ過ぎを判断する材料になります。

ボリンジャーバンドとは

ボリンジャーバンドは以下の3本のラインで構成されます。

  • 中心線（ミドルバンド）:n日移動平均線
  • 上側バンド（アッパーバンド）:中心線 + k × 標準偏差
  • 下側バンド（ローワーバンド）:中心線 − k × 標準偏差

一般的には、nは20日、kは2が標準パラメータとして使われます。

計算式の詳細

ボリンジャーバンドの各ラインは次のように計算されます。

\(
\text{ミドルバンド} = \text{SMA}_n
\)

\(
\text{アッパーバンド} = \text{SMA}_n + k \times \sigma
\)

\(
\text{ローワーバンド} = \text{SMA}_n – k \times \sigma
\)

ここで、σ（シグマ）は過去n日間の終値の標準偏差を表します。

エクセルでの計算手順

以下の手順でボリンジャーバンドを算出します。

  1. ミドルバンド（移動平均）の計算:F列に20日移動平均を作成します。 =AVERAGE(E2:E21) のような数式を使います。
  2. 標準偏差の計算:G列に20日間の標準偏差を計算します。 =STDEV.S(E2:E21) または =STDEV(E2:E21) を使います。
  3. アッパーバンドの計算:H列に =F21 + 2*G21 と入力します（F21がミドルバンド、G21が標準偏差）。
  4. ローワーバンドの計算:I列に =F21 - 2*G21 と入力します。
  5. 数式をコピー:それぞれの列を下方向にコピーして、全データに適用します。

これで、価格がどのバンド内に位置しているかを一目で確認できるようになります。

ボリンジャーバンドの活用方法

ボリンジャーバンドは、価格が統計的にどの程度「異常」な位置にあるかを教えてくれます。

  • バンド幅の拡大:価格変動が大きくなっている（ボラティリティが高い）状態
  • バンド幅の縮小:価格が落ち着いている（ボラティリティが低い）状態で、次の大きな動きの前兆とも言われます
  • 価格がアッパーバンドに接触:買われ過ぎの可能性があり、反転下落のサインとなることがあります
  • 価格がローワーバンドに接触:売られ過ぎの可能性があり、反転上昇のサインとなることがあります

ただし、トレンドが強いときはバンドに沿って価格が動き続けることもあるため、他の指標と組み合わせて判断することが重要です。

RSI（相対力指数）をエクセルで算出する

RSI（Relative Strength Index, 相対力指数）は、一定期間における価格の上昇幅と下落幅の比率から、買われ過ぎ・売られ過ぎを判断するオシレーター系のテクニカル指標です。

RSIの計算式

RSIは0から100の範囲で推移し、以下の式で計算されます。

\(
\text{RSI} = 100 – \frac{100}{1 + \text{RS}}
\)

ここで、RSは次のように定義されます。

\(
\text{RS} = \frac{\text{n日間の平均上昇幅}}{\text{n日間の平均下落幅}}
\)

一般的にはn=14が標準パラメータとして使われます。

エクセルでの計算手順

RSIの計算は少し複雑ですが、ステップバイステップで進めれば問題ありません。

  1. 前日比を計算:J列に前日との価格差を計算します。たとえばJ3セルに =E3-E2 と入力し、下方向にコピーします。
  2. 上昇幅と下落幅を分ける:K列に上昇幅（前日比が正ならその値、負なら0）、L列に下落幅（前日比が負ならその絶対値、正なら0）を記録します。
    • K3セル: =IF(J3>0, J3, 0)
    • L3セル: =IF(J3<0, ABS(J3), 0)
  3. 平均上昇幅・平均下落幅を計算:M列とN列に、それぞれ14日間の平均を計算します。
    • M16セル: =AVERAGE(K3:K16)
    • N16セル: =AVERAGE(L3:L16)
  4. RSを計算:O列に =M16/N16 と入力します（分母がゼロの場合はエラーになるので注意）。
  5. RSIを計算:P列に =100 - (100/(1+O16)) と入力します。
  6. 数式をコピー:M列からP列までを下方向にコピーして、全データに適用します。

RSIの読み方と活用

RSIは以下の基準で判断されることが一般的です。

  • 70以上:買われ過ぎの領域。価格が過熱している可能性があり、売りサインと捉えられることがあります。
  • 30以下:売られ過ぎの領域。価格が底を打っている可能性があり、買いサインと捉えられることがあります。
  • 50付近:中立の状態で、明確なトレンドがない場合が多いです。

RSIは特にレンジ相場（横ばいの動き）で有効ですが、強いトレンド相場では70以上や30以下に張り付いたままになることもあるため、他の指標と併用することが推奨されます。

POINT

RSIを使うときは、単独ではなく移動平均線やボリンジャーバンドなど他のテクニカル指標と組み合わせることで、より精度の高い判断ができるようになります。

MACD（移動平均収束拡散）の計算方法

MACD（Moving Average Convergence Divergence, 移動平均収束拡散）は、2本の指数平滑移動平均（EMA）の差をとることで、トレンドの転換点や勢いを捉えるテクニカル指標です。

MACDの構成要素

MACDは以下の3つの要素から成り立っています。

  • MACDライン:短期EMA（通常12日）− 長期EMA（通常26日）
  • シグナルライン:MACDラインの移動平均（通常9日EMA）
  • MACDヒストグラム:MACDライン − シグナルライン

指数平滑移動平均（EMA）とは

EMAは、単純移動平均（SMA）と異なり、直近のデータにより大きな比重を置く移動平均です。計算式は以下の通りです。

\(
\text{EMA}_{\text{今日}} = \text{終値}_{\text{今日}} \times k + \text{EMA}_{\text{昨日}} \times (1 – k)
\)

ここで、kは平滑化係数で次のように求めます。

\(
k = \frac{2}{n + 1}
\)

nは期間（たとえば12日なら k = 2/13 ≒ 0.1538）です。

エクセルでのMACD計算手順

  1. 12日EMAの計算:Q列に12日EMAを作成します。最初のEMAは12日間の単純平均から始めます。
    • Q13セル: =AVERAGE(E2:E13)
    • Q14セル以降: =E14*(2/13) + Q13*(1 - 2/13)
  2. 26日EMAの計算:R列に26日EMAを同様に作成します。
    • R27セル: =AVERAGE(E2:E27)
    • R28セル以降: =E28*(2/27) + R27*(1 - 2/27)
  3. MACDラインの計算:S列に =Q27-R27 のように、12日EMAから26日EMAを引きます。
  4. シグナルラインの計算:T列にMACDラインの9日EMAを計算します。
    • T35セル: =AVERAGE(S27:S35)
    • T36セル以降: =S36*(2/10) + T35*(1 - 2/10)
  5. ヒストグラムの計算:U列に =S35-T35 と入力し、MACDラインとシグナルラインの差を求めます。

MACDの活用方法

MACDを使った代表的な売買サインは以下の通りです。

  • ゴールデンクロス:MACDラインがシグナルラインを下から上に抜ける→買いサイン
  • デッドクロス:MACDラインがシグナルラインを上から下に抜ける→売りサイン
  • ゼロライン:MACDラインがゼロを上回っていれば上昇トレンド、下回っていれば下降トレンド
  • ヒストグラムの拡大・縮小:ヒストグラムが拡大していればトレンドが強まり、縮小していればトレンドが弱まっている

MACDはトレンドフォロー型の指標として、中長期のトレンド判断に向いています。

エクセルでチャートを描画するコツ

テクニカル指標を計算しただけでは、視覚的に状況を把握しにくいものです。エクセルのグラフ機能を使ってチャートを描画すると、トレンドやシグナルが一目瞭然になります。

折れ線グラフで株価と移動平均を表示

  1. データ範囲を選択:日付列、終値列、移動平均列を選択します（Ctrlキーを押しながら複数列を選択可能）。
  2. グラフの挿入:「挿入」タブから「折れ線グラフ」を選びます。
  3. 軸ラベルの設定:横軸に日付、縦軸に価格が表示されるように調整します。
  4. 凡例の追加:どの線が終値で、どれが移動平均かを示す凡例を表示します。

複合グラフでMACDやRSIを追加

MACDやRSIは価格とスケールが異なるため、複合グラフや第2軸を使うと見やすくなります。

  1. グラフを作成後、追加データを選択:グラフを右クリックし「データの選択」から系列を追加します。
  2. 第2軸を使用:追加した系列を右クリックし「データ系列の書式設定」から「第2軸」を選びます。
  3. グラフの種類を変更:価格は折れ線、MACDヒストグラムは縦棒グラフにするなど、視覚的に区別しやすくします。

ローソク足チャートの作成

エクセル2016以降では、ローソク足チャートも標準で作成できます。

  1. データの配置:日付、始値、高値、安値、終値の順に列を並べます。
  2. 株価チャートを挿入:「挿入」タブから「株価チャート」→「始値-高値-安値-終値」を選択します。
  3. 書式設定:色や線の太さを調整し、見やすくカスタマイズします。

ローソク足と移動平均線を同じグラフに表示すれば、本格的なチャート分析が可能になります。

テクニカル分析エクセルファイルの活用と注意点

ここまで作成したエクセルファイルは、継続的に株価データを更新することで、リアルタイムに近い分析ツールとして活用できます。ただし、いくつかの注意点もあります。

データの更新と自動化

株価データは日々更新されるため、毎日手動で入力するのは大変です。可能であれば、証券会社のツールやAPIを使って自動的にデータを取り込む仕組みを作ると効率的です。エクセルのVBA（Visual Basic for Applications）を使えば、ボタン一つでデータ更新を行うマクロを組むこともできます。

バックテストで検証する

作成したテクニカル指標が実際に有効かどうかは、過去のデータで検証することが重要です。たとえば「MACDのゴールデンクロスで買い、デッドクロスで売る」というルールを過去1年間のデータに適用し、どの程度利益が出たかをシミュレーションしてみましょう。

バックテストを行うことで、指標の有効性やパラメータの最適値を見つけることができます。

POINT

バックテストの結果が良好でも、過去のデータに過度に最適化（オーバーフィッティング）していないか注意が必要です。実際の相場では想定外の動きが起こることを常に意識しましょう。

複数の指標を組み合わせる

単一のテクニカル指標だけで売買判断をするのはリスクが高いです。たとえば、移動平均線でトレンドを確認し、RSIで買われ過ぎ・売られ過ぎを判断し、MACDでエントリータイミングを計るといった複合的なアプローチが推奨されます。

エクセルの限界を理解する

エクセルは非常に便利なツールですが、大量のデータを扱う場合は動作が重くなることがあります。また、リアルタイムのティック（秒単位）データには対応していません。本格的なアルゴリズムトレードを行いたい場合は、PythonやRなどのプログラミング言語を使った環境を検討する必要があります。

データの正確性を確認

取得した株価データに誤りがないか、必ず確認してください。特に株式分割や配当落ち日などのイベントがあると、価格データが不連続になることがあります。このような調整後株価を使うことで、より正確な分析が可能になります。

リスク管理を忘れずに

テクニカル分析はあくまで売買判断の一つの材料であり、100%確実なものではありません。必ず損切りルールを設定し、資金管理を徹底してください。エクセルで資金管理シートを作成し、ポジションサイズやリスク許容度を可視化するのも有効です。

まとめ

この記事では、エクセルを使ってテクニカル分析を自作する方法を、初心者の方にもわかりやすく解説しました。ポイントを振り返りましょう。

  • エクセルでテクニカル分析を行うメリット:計算ロジックを理解でき、パラメータを自由にカスタマイズでき、コストがかからない。
  • 株価データの取得:証券会社やYahoo!ファイナンスなどから過去データをダウンロードし、日付順に整理する。
  • 移動平均線（SMA）:AVERAGE関数やOFFSET関数を使って簡単に計算でき、トレンドの方向性を把握できる。
  • ボリンジャーバンド:移動平均と標準偏差を組み合わせて、価格の変動範囲を視覚化し、買われ過ぎ・売られ過ぎを判断する。
  • RSI（相対力指数）:一定期間の上昇幅と下落幅の比率から、オシレーター指標として買われ過ぎ・売られ過ぎを数値化する。
  • MACD:2本のEMAの差をとることで、トレンドの転換点や勢いを捉える中長期指標。
  • チャート描画:エクセルのグラフ機能を使って、視覚的にわかりやすいチャートを作成できる。
  • 活用と注意点:データ更新の自動化、バックテストによる検証、複数指標の組み合わせ、エクセルの限界を理解し、リスク管理を徹底する。

エクセルでテクニカル分析を自作することで、指標の本質を深く理解し、自分に合った投資スタイルを構築できるようになります。ぜひ今回ご紹介した方法を実践し、実際の株式投資に役立ててください。

前の記事

TOPIX100高配当銘柄の選び方とランキングTOP10を徹底…

次の記事

テクニカル分析におけるエントリーポイントの見つけ方完全ガイド