Excelのソルバーと数式だけで軽量MLモデルを構築する完全ガイド

Excelは、基本的な機械学習タスクに活用できる意外に強力なツールです。本格的な機械学習プラットフォームではありませんが、組み込み関数と「ソルバー」を組み合わせることで、線形回帰やロジスティック回帰といった基礎的なML概念を効果的に実装・体感できます。

このチュートリアルでは、Excelのソルバーと数式を使って、軽量な機械学習モデルを構築する手順を解説します。

• 線形回帰: 売上高、住宅価格、テストの点数など、連続値を予測します。
• ロジスティック回帰: 顧客の購買有無、ローンの貸し倒れ、医療診断、合格/不合格など、Yes/Noの結果を予測します。

前提条件:

• Microsoft Excel(2016以降を推奨)
• ソルバー アドインの有効化
  • 「ファイル」タブ >>「オプション」>>「アドイン」>>「Excel アドイン」を選択します。
  • 「設定(Go)」をクリックします。

• • 「ソルバー アドイン」にチェックを入れます。
  • 「OK」をクリックします。

• 回帰分析に関する基礎知識があると理解がスムーズです。

パート1:線形回帰モデル

線形回帰は、データポイントを通る最適な直線を見つけ出し、連続的な数値を予測する手法です。ここでは、「広告費(X)から売上高(Y)を予測する」というシンプルなビジネスシナリオをモデル化します。各データ行は1か月分の実績データを表しています。

ステップ1:サンプルデータの準備

入力(広告費・千ドル単位)と出力(売上高・千ドル単位)の間に明確な線形関係が見られる、現実的なデータセットを作成します。

各行が1か月分のビジネスデータです。広告費が増えるほど売上も増加しますが、完全な比例関係ではありません。現実のデータには必ず多少のランダム性(ノイズ)が含まれるためです。

ステップ2:予測式の作成

モデルが最適な直線を見つけるために調整する「つまみ(パラメータ)」を設定します。線形回帰で必要なパラメータは2つです。

• 切片(b0): 直線がY軸と交わる点(広告費ゼロの場合の基本売上)。
• 傾き(b1): 広告費が1,000ドル増えるごとに売上がどれだけ増えるか。

モデルのパラメータをそれぞれ別のセルに配置します。

モデルパラメータ:

予測Y = b0 + b1 × X

• 切片(b0):初期値 0
• 傾き(b1):初期値 1

この線形方程式を使って、広告費に基づいて売上を予測します。これがモデルの中核であり、広告額を受け取って売上の見込みを算出する仕組みです。

数式的な意味:

• b0 = 0.5、b1 = 2 の場合、広告費3千ドルに対する売上予測は 0.5 + 2×3 = 6.5千ドル となります。
• モデルはデータから b0 と b1 の最適値を学習します。

予測式:

• 任意のセルに以下の数式を入力します。
• その後、数式をF11までオートフィル(ドラッグ)します。

ステップ4:残差と誤差の計算

予測がどれだけ外れているかを測定します。モデルはこれらの誤差を最小化しようとすることで学習するため、非常に重要な工程です。

• 残差: 各月の実際の売上と予測売上の差。
• 二乗誤差: 残差を2乗したもの(すべての誤差を正にするとともに、大きな誤差により重いペナルティを課すため)。

残差:

• 数式をG11までドラッグしてコピーします。

二乗誤差:

• 数式をH11までドラッグしてコピーします。

ステップ5:誤差指標の計算

モデルの性能をビジネス的に意味のある形で測定します。これらの指標によって、モデルが実務で使えるレベルかどうかを判断できます。

専用のエリアに主要な指標を設定しましょう。

誤差指標:

• 残差二乗和(SSE): 全予測の合計誤差。小さいほど良い。
• 二乗平均平方根誤差(RMSE): 元の単位(千ドル)での平均誤差。解釈しやすい。
• 決定係数(R二乗): 売上の変動のうち広告費で説明できる割合(0〜100%、高いほど良い)。

=1-(K2/SUMPRODUCT((B2:B11-AVERAGE(B2:B11))^2))

• 平均絶対誤差(MAE): 誤差の絶対値の平均。RMSEよりも外れ値の影響を受けにくい。

ステップ6:ソルバーでパラメータを最適化

予測誤差を最小化する切片と傾きの最適値を、Excelに自動的に探索させます。

• 「データ」タブ >>「ソルバー」を選択します。
• 目的セルの設定:K2(SSEのセル)。
• 目標:「最小値」を選択。
• 変数セル:E3,E5(パラメータのセル)。
• 「解決(Solve)」をクリックします。

• 「OK」をクリックします。

ソルバーの内部では次のような処理が行われています。

• b0 と b1 の無数の組み合わせを試行します。
• 各組み合わせについて合計誤差を計算します。
• 誤差が最小になる組み合わせが見つかるまで調整を繰り返します。
• 手作業で推測するより、はるかに速く正確です。

ステップ7:可視化による検証

モデルを目視で確認することは非常に有効です。予測直線がほとんどのデータポイントに近い位置を通っているかをチェックしましょう。

• 広告費と売上の列を選択します。
• 「挿入」タブ >>「グラフ」>>「散布図」を選択します。

• グラフを右クリック >>「データの選択」>>「系列の追加」を選択します。
  • 系列名: セルF1を選択。
  • Xの値: Xの範囲(例：B2:B11)を選択。
  • Yの値: 予測値の範囲F2:F11を選択。
• 予測系列の書式を折れ線に変更します。

良いモデルであれば、以下のような特徴が見られます。

• 予測直線がデータ全体の傾向に沿っている。
• ポイントが直線の上下にまんべんなく散らばっている(片寄っていない)。
• 残差に明確なパターンが見られない。

パート2:ロジスティック回帰モデル

ロジスティック回帰は、Yes/Noの意思決定に対する確率を予測します。具体的な数値を予測する線形回帰とは異なり、「何かが起こる可能性」(0〜100%)を予測するのが特徴です。

ステップ1:二値分類データの準備

顧客の購買行動をモデル化してみましょう。顧客の所得水準(X)をもとに、高級製品を購入する(1)/しない(0)を予測します。これはマーケティングのターゲティング、医療診断など、あらゆる二値判定に応用できる典型的なケースです。

ロジスティック回帰用のデータを準備します。

顧客の所得水準(1万ドル単位)と購買の有無です。低所得層(1〜5)は購入しない(0)傾向があり、高所得層(6〜10)は購入する(1)傾向があります。現実の購買パターンを反映したデータです。

ステップ2:ロジスティック予測式の作成

ロジスティックモデルのパラメータを初期化:

ロジスティック関数用のパラメータを設定します。線形回帰とは異なり、これらのパラメータはシグモイド関数というより複雑な数学的変換を通じて機能します。

• 切片(b0): 閾値(確率50%となる地点)を左右に移動させる。
• 傾き(b1): 「起こりそうにない」から「起こりそう」への遷移の急峻さを制御する。
• 初期値: まず妥当な推測値から始め、ソルバーが最適化します。

ロジスティックパラメータ:

確率 = 1 / (1 + e^(-(b0 + b1×X)))

• 切片(b0):初期値 -2
• 傾き(b1):初期値 0.5

ロジスティック予測式の作成:

シグモイド関数を使って、線形結合を確率へと変換します。予測値が常に0〜1の範囲に収まるようにする、数学的な仕掛けです。

• 線形結合: b0 + b1×X(線形回帰と同じ)。
• シグモイド変換: 1/(1+e^(-(線形結合))) が任意の数値を0〜1の範囲に変換。
• 結果: 滑らかなS字カーブとして確率を表現。確率が0.7なら、その顧客が購入する可能性は70%ということになります。

確率予測:

• 線形結合:

• 確率予測:

• セルの表示形式をパーセンテージ(%)に変更しておきましょう。

ステップ4:対数尤度の計算

確率予測が実際の結果とどれだけ一致しているかを測定します。確率を扱うため、単純な誤差計算よりも複雑な指標が必要です。

対数尤度の構成要素:

=IF(B2=1,LN(MAX(G2,0.0001)),LN(MAX(1-G2,0.0001)))

二値の結果に対しては、単純な引き算(実際 − 予測)は使えません。代わりに、「予測を踏まえた上で、実際の結果にどれだけ驚かされるか」を測定します。

• 購入確率90%と予測して顧客が実際に購入した場合 → 驚きは少ない(良いモデル)。
• 購入確率10%と予測したのに顧客が購入した場合 → 大いに驚く(悪いモデル)。

ステップ5:ロジスティックモデルの評価指標

分類性能をビジネスに関連づけて測定します。これらの指標は、モデルが実際のビジネス判断に耐えうるかを判断する材料になります。

精度(Precision)が高いほど、無駄なマーケティング費用が減ります(偽陽性が少ない)。再現率(Recall)が高いほど、見込み顧客を見逃しません(偽陰性が少ない)。

ロジスティック指標:

• モデル適合度/負の対数尤度: 値が小さいほど確率予測の精度が高い。
• 正解率(Accuracy): 50%を閾値とした場合に、顧客を正しく分類できた割合。

=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1)+(G2:G11<=0.5)*(B2:B11=0))/10

• 適合率(Precision): 「購入する」と予測した顧客のうち、実際に購入した割合。

=IF(SUMPRODUCT((G2:G11>0.5))=0,"No Predictions",SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT((G2:G11>0.5)))

• 再現率(Recall): 実際に購入した顧客のうち、モデルが正しく特定できた割合。

=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT(B2:B11)

ステップ6:ソルバーでロジスティックモデルを最適化

データ内の確率パターンに最もよく適合するパラメータ値を見つけます。ソルバーは負の対数尤度を最小化します。これは、実際に観測されたデータが得られる確率を最大化することと同じ意味を持ちます。

• 「データ」タブ >>「ソルバー」を選択します。
• 目的セルの設定:K2(負の対数尤度)。
• 目標:「最小値」を選択。
• 変数セル:E3,E5。
• 「解決(Solve)」をクリックします。

よくある問題とトラブルシューティング

• ソルバーが収束しない: 初期値を変えるか、反復回数を増やしてみてください。
• R二乗がマイナスになる: データ入力ミスやモデルの設定ミスがないか確認してください。
• ロジスティック回帰で完全分離が発生する: 特徴量の値を減らすか、正則化を追加してください。

まとめ

本チュートリアルでは、Excel上で直接機械学習モデルを構築する手順を段階的に解説しました。Excelは専門のMLツールと比べると機能面で限界がありますが、モデルの仕組みが透過的で、誰でもアクセスしやすいという大きな利点があります。ソルバーと基本の数式を活用すれば、軽量な機械学習モデルを素早く実装し、予測を可視化し、シンプルながら洞察に富んだ手法でモデルの精度を把握できます。ここで紹介したテクニックは、より複雑なシナリオへ拡張することも可能で、回帰の概念を学ぶ教育ツールとしても活用できます。