【Excel】ロジスティック回帰分析をエクセルで行う方法は？分析の考え方と実践手順

ロジスティック回帰分析は、購入するかしないか、合格か不合格か、離反するか継続するかのように、結果が二択になるデータを予測したい場面で役立つ分析手法です。

Excelにはロジスティック回帰を専用ボタンだけで完了する標準機能はありませんが、数式、ソルバー、回帰係数の考え方を組み合わせることで実践的な分析は可能です。

サンプルデータは1行目に見出しがあり、A列に説明変数、B列に0または1の目的変数を置く構成で進めます。

予測の精度だけではなく、係数の向き、オッズ比、しきい値の決め方まで確認すると、分析結果を業務判断に活かしやすくなります。

 

Excelでロジスティック回帰分析を行う全体手順

A列	B列	C列
閲覧回数	購入有無	予測確率
3	0	計算値
8	1	計算値

それではまず、Excelでロジスティック回帰分析を進める全体の流れについて解説していきます。

通常の回帰分析とは異なり、結果をそのまま数値で予測するのではなく、目的変数が1になる確率を0から1の範囲で求める点がロジスティック回帰の特徴です。

 

分析目的と目的変数の設定

ロジスティック回帰分析では、最初に何を予測したいのかを明確にします。

購入した場合を1、購入しなかった場合を0とするように、目的変数は原則として二値で入力します。

合格と不合格、退会と継続、故障と正常のような分類も同じ考え方で扱えます。

目的変数に文字列を直接入力するより、分析用には0と1へ変換しておくほうが数式やソルバーで扱いやすいでしょう。

なお、購入を0、未購入を1としても計算自体はできますが、係数の意味が逆方向になります。

レポートや社内共有で混乱しないよう、成功事象に1を割り当てる方法が一般的です。

 

説明変数と分析用シートの準備

説明変数とは、目的変数に影響すると考えられる項目です。

購入有無であれば閲覧回数、価格、会員ランク、広告接触回数、クーポン利用などが候補になります。

はじめて分析する場合は、説明変数を1つに絞ると、係数と予測確率の関係を理解しやすくなります。

複数の説明変数を入れる場合も、欠損値、文字列、単位の違いを先に確認しましょう。

1行が1人または1件の観測データになるようにそろえることが、集計ミスを防ぐ基本です。

 

係数の仮置きからソルバー実行までの流れ

ロジスティック回帰では、切片と係数を最初は仮の値として置きます。

次に各行の予測確率と対数尤度を計算し、全行の対数尤度合計が最も大きくなるようにソルバーで係数を調整します。

この手順により、データへ最も適合する確率曲線を求められます。

ソルバーの結果だけを見るのではなく、係数を使って元データの予測確率が更新されたことも確認しましょう。

 

ロジスティック回帰分析に必要な数式と確率計算

D列	E列	F列
線形予測子	予測確率	対数尤度
切片＋係数×閲覧回数	0.00から1.00	計算値

続いては、予測確率と対数尤度をExcelで計算する数式を確認していきます。

ロジスティック回帰の中心となるのは、線形予測子を指数関数で変換して確率にする処理です。

 

線形予測子とロジスティック関数

切片をH2、閲覧回数の係数をH3に置く場合、D2には線形予測子を計算します。

この値は正にも負にもなりますが、まだ確率ではありません。

次にE2へロジスティック関数を入力すると、どのような値でも0から1の間へ変換されます。

この式で得られるE2が、閲覧回数などの条件から推定した購入確率です。

予測確率が0.75なら、同じ条件の対象が購入する見込みを75パーセント程度と読むことができます。

D2とE2の数式は、データ最終行までオートフィルでコピーします。

 

対数尤度の計算式

係数の良し悪しを判定するために、実績値と予測確率から対数尤度を計算します。

B列が実績の購入有無、E列が予測確率である場合、F2には次の式を入力します。

実績が1なら予測確率そのものを、実績が0なら1から予測確率を引いた値を評価する構造です。

確率が実績に近いほど対数尤度は高くなり、全体の合計も大きくなります。

G2などに合計セルを作り、=SUM(F2:F101) のように全行の対数尤度を合計します。

データ件数に合わせて最終行は変更してください。

 

エラーを防ぐ確率の補正

予測確率が完全な0または1に近づくと、LN関数でエラーや極端な値が出ることがあります。

実務では確率をわずかに補正し、計算を安定させる方法が有効です。

MAX関数は確率が極端に小さくなった場合でも、対数計算が停止しないようにする安全策です。

ただし、補正値を大きくしすぎると結果へ影響するため、通常は非常に小さい値にします。

 

ソルバーによる回帰係数の推定設定

H列	I列	J列
項目	初期値	推定後
切片	0	ソルバー結果
閲覧回数の係数	0	ソルバー結果

続いては、対数尤度を最大にする回帰係数をソルバーで推定する設定を確認していきます。

Excelのソルバーが表示されない場合は、ファイル、オプション、アドインからExcelアドインを選び、ソルバーアドインへチェックを入れて有効化します。

 

目的セルと変数セルの指定

データ全体の対数尤度合計を計算したセルを、ソルバーの目的セルに指定します。

目的は最小化ではなく最大化です。

変数セルには、切片と各説明変数の係数を置いたセル範囲を指定します。

閲覧回数だけを使う例なら、H2の切片とH3の係数が変数セルです。

目的セルは対数尤度の合計、変数セルは係数であり、予測確率の列を変数セルにしないことが重要です。

予測確率は係数から自動計算される結果のセルだからです。

 

解決方法と初期値の考え方

解決方法はGRG非線形を選択します。

ロジスティック関数にはEXP関数が含まれるため、線形モデル向けの単体法ではなく非線形の解法が適しています。

初期値は切片と係数を0にしても始められますが、収束しにくい場合には小さな値を入れて試します。

ソルバーが解を見つけられないときは、説明変数の桁が大きすぎないか、欠損値がないか、同じ内容の列が重複していないかを確認しましょう。

 

推定結果の保存と再計算

ソルバーで解を見つけたら、結果を保持する選択肢を選びます。

係数セルの値が更新されると、線形予測子、予測確率、対数尤度も連動して更新されます。

このとき、目的セルの合計値が実行前より大きくなっていることを確かめてください。

同じデータで何度も分析する場合は、係数セルと数式セルを保護し、元データだけを更新できるシート構造にすると便利です。

推定結果のコピーは別シートへ値として保存し、いつのデータを使った結果なのかも記録しておくとよいでしょう。

 

係数とオッズ比の読み取り方

項目	係数	オッズ比
閲覧回数	0.28	=EXP(0.28)

続いては、推定された係数を業務上の意味へ置き換える読み取り方を確認していきます。

 

係数の正負が示す方向

係数が正なら、説明変数が大きくなるほど目的変数が1になる方向へ働く傾向を示します。

閲覧回数の係数が0.28であれば、閲覧回数が増えるほど購入確率が高まる関係です。

反対に係数が負なら、その説明変数が大きいほど購入確率は低下する方向になります。

ただし、係数の大きさをそのまま確率の増減と読むことはできません。

ロジスティック回帰では、確率の変化はもとの確率水準によって変わるためです。

 

EXP関数で求めるオッズ比

係数をより解釈しやすくするため、EXP関数でオッズ比を求めます。

係数0.28のオッズ比は約1.32となり、閲覧回数が1回増えるごとに購入のオッズが約1.32倍になると解釈できます。

オッズは確率そのものではなく、確率を1から確率を引いた値で割った比率です。

オッズ比が1より大きければプラス方向、1より小さければマイナス方向、1なら影響がほとんどない可能性を示します。

 

複数の説明変数を比較する注意点

閲覧回数と金額のように単位が異なる説明変数では、係数の絶対値だけを比較して重要度を決めることはできません。

必要に応じて標準化を行うか、実務で意味のある単位へ変換してから比較します。

また、似た説明変数を同時に多く入れると、多重共線性によって係数が不安定になることがあります。

分析の説明力を高めるためにも、業務上の意味を説明できる変数を厳選する姿勢が大切です。

 

予測精度としきい値の評価方法

実績	予測確率	判定
1	0.72	1
0	0.41	0

続いては、予測確率を0と1の判定へ変換し、精度を評価する方法を確認していきます。

予測確率が計算できても、どこから購入見込みありと判断するかで集計結果は変わります。

 

しきい値による予測分類

予測確率がE列にある場合、0.5以上を1、それ未満を0とするには、G2へ =IF(E2>=0.5,1,0) と入力します。

0.5はよく使われる基準ですが、常に最適とは限りません。

見込み客を幅広く抽出したい場合は0.3へ下げることがあり、誤った案内を減らしたい場合は0.7へ上げることもあります。

しきい値は統計上の慣例ではなく、誤判定によるコストや業務の目的に合わせて決める値です。

 

混同行列と正解率の確認

実績と予測分類を比較すると、正解率を計算できます。

実績がB列、判定がG列なら、H2へ =IF(B2=G2,1,0) と入力し、H列の平均を求めれば正解率です。

ただし、購入者が全体の数パーセントしかいないデータでは、全員を未購入と判定しても高い正解率になる場合があります。

そのため、購入者を正しく抽出できた割合である再現率や、購入予測のうち実際に購入した割合である適合率も見る必要があります。

 

分析データと検証データの分離

同じデータで係数の推定と精度評価を行うと、実際より良い結果に見えることがあります。

可能ならデータを学習用と検証用に分け、学習用で求めた係数を検証用へ適用します。

検証用でも一定の精度が出れば、未知のデータに対しても役立つモデルである可能性が高まります。

ロジスティック回帰は予測式を作ることが目的ではなく、未知の対象に対して使えるかを確かめることまでが分析です。

 

まとめ　ロジスティック回帰分析をエクセルで行う方法

Excelでロジスティック回帰分析を行う場合は、目的変数を0と1で用意し、ロジスティック関数で予測確率を計算します。

その後、対数尤度の合計を目的セルにして、ソルバーのGRG非線形で切片と係数を調整する流れです。

最初に見るべきなのは、係数が正しく更新され、予測確率が0から1の範囲にあり、対数尤度合計が最大化されていることです。

係数は正負で影響の方向を確認し、EXP関数でオッズ比へ変換すると、業務上の説明がしやすくなります。

また、正解率だけで結論を急がず、しきい値、再現率、適合率、検証用データでの結果も確認しましょう。

Excelは高度な統計ソフトほど自動化されていませんが、数式の構造を理解しながら分析できる点が大きな利点です。

小規模なデータから手順を試し、説明変数の選び方と結果の読み方を身につけていきましょう。