Excelソルバーで解く! 単回帰分析の予測の秘密【ChatGPT統計解析】
単回帰分析では最初に散布図を作成し、データ間の関係を視覚化します。次に、Excelのソルバーを用いて最小二乗法で残差平方和を最小化し、最も適合する直線を見つけます。この方法では、観測値と予測値の差（残差）の二乗和を最小にし、データに基づく科学的な予測を可能にします。ソルバーの利用により、データの背後にある関係性を定量化し、未知のデータポイントに対する正確な予測を行うことができます。
単回帰分析・散布図
今、ある病院で同じ治療を受けている４人のⅡ型糖尿病患者で以下のデータが得られたとしましょう。
体重（kg） 食前血糖値（mg/dL） HbA1c（%）
患者A 65 119 6.8
患者B 73 125 7.4
患者C 75 135 7.7
患者D 81 140 8.2
患者E 90
さて、患者Eの食前血糖値とHbA1cであるが、測定会社の事情でデータが出るのが来週になったというのです。
患者Eのこれらの値を何とか予測できないでしょうか。
このような場合、先ず散布図を作成します。
Excelのグラフツールの使い方はここでは省略しますが、様々な解説が公開されていますので検索等で確認してください。
また、綺麗な図表を作ろうとしないでください。
雑でもいいので手早く作ることが大事です。
すると、右のような右肩上がりの散布図になります。
先の年齢と体重の例は直線関係でしたが、上の例は直線というよりやや折れ線になっているので正確な予測は目視では難しそうです。
それでも、体重90kgの患者Eの食前血糖値は、大体152位かなというあたりをつけておくことは大切です。
もちろんこの予想は、４つの点を通る直線を引いた場合の予想です。
しかし、大体というのは科学的ではありません。
ある根拠をもって、いくらだと予想される、と主張しなければいけません。
言い換えれば、適当に線を引いてはいけないのです。
ある根拠をもって線を引く、その根拠がすなわち最小２乗法です。
最小2乗法：残差の平方和を最小
最小２乗法とは一言で言うと、
観測値と予測値との差（残差といいます）の２乗の和を最小にする方法です。
上の図でいうei（残差）をすべて２乗して足したもの（残差平方和）を最小にするのが最小２乗法です。
ではなぜ２乗の和なのか、和そのもの（そのまま足す）のでもいいではないかと思うかもしれません。
しかしながら、そのまま足すと、予測値の方が大きいとマイナスになるという符号の問題があるため、最適な按配で直線を引くことができないわけです。
そして面白いことに、残差平方和の最小値というのはたった１つの値です。
絶対に２つとはないのです。
一次式の回帰係数b0、b1をいろいろ変化させたとき残差平方和がどう変化するかを示したのが上の図です。
図のように、残差平方和の方が半紡錘形になり、その底部が最小値となり、回帰係数が一意に決まるのがおわかりいただけると思います。
エクセルソルバーによる最小2乗法
では実際に最小２乗法を行ってみましょう。
Excelには、ソルバーというアドインツールがあり、これを利用すると残差平方和を最小化することができます。
結果を先に示すと以下になります。
D列の予測値は、aX+bという一次式で予測するのですが、先ずセルD2、D3にa=1、b=50とかの仮の値（初期値といいます）を適当に設定します。
XをB列の体重とし、D列でaX+bを計算します（D5に、＝D$2*B5+D$3 と入力し、これを下までコピーします）。
E列の残差はC列の食前血糖値からD列の予測値を引いた値です（E5に、＝C5－D5）。
このE列の残差を、患者A～Dについて２乗し全部足します。
セルE11で計算します。
素直に２乗して足してもいいですが＝SUMSQ(E5:E8)という関数を用いることにより一発で２乗和が計算できます。
今度は、セルE11を最小にします。
アドインの「ソルバーアドイン」のチェックボクスにチェックを入れてから、データタブの分析、ソルバーを選択します。
すると以下の画面が出ます。最上部の目的セルの設定のところでE11を最小値、変数セルの変更のところをD2,D3とし、解決ボタンを押すとE11が最小化され結果がシート上に表示されます。
結局、残差の２乗和を最小にした直線と正確な予測値を得ることができたわけです。
欠落していた患者Ｅの食前血糖値は、124.98と予測することができました。
直線回帰分析では、先ずは最小２乗法の考え方を理解しましょう。
理解できたら、即Excelソルバーで計算を実践し、最小２乗法を実感としてつかみましょう。
ChatGPTに聞いてみた
プロンプト
あなたは最高のデータサイエンティストです。
エクセルソルバーについて解説してください。
#制約条件：
サルでもわかる平易な日本語で
文字数は300字程度
ChatGPTの回答
エクセルソルバーとは、エクセルの中にある便利な道具で、難しい問題を解くのを手伝ってくれます。例えば、ある条件のもとで、最大の利益を出すにはどうしたらいいか、または最少のコストで目標を達成するにはどうするか、というような問題です。使い方はこんな感じです。まず、目標とする値（例えば「利益を最大にする」）をエクセルのあるセルに設定します。次に、この目標を達成するために変えられる値（例えば「売る個数」や「価格」）を入力します。そして、これらの値が守るべきルール（「予算はこれ以上使えない」など）を設定します。ソルバーを起動すると、エクセルが自動で計算を始め、設定した条件とルールに合わせて、目標を達成する最適な解を探してくれます。これにより、手計算では大変な問題も、簡単に解決策を見つけることができるわけです。要するに、エクセルソルバーは難しい最適化問題を解くための強力なツールで、ビジネスの意思決定や研究など、さまざまな場面で役立ちます。
