為什麼這篇excel線性回歸公式鄉民發文收入到精華區：因為在excel線性回歸公式這個討論話題中，有許多相關的文章在討論，這篇最有參考價值！作者Vulpix (Sebastian)看板Statistics標題[程式] 關於Excel中趨勢線...
[軟體程式類別]:
Microsoft Excel 2007
[程式問題]:
線性迴歸的公式問題
[軟體熟悉度]:
熟悉
[問題敘述]:
想藉由程式給出的數值做進一步分析，卻不知道該怎麼下手。
一般的單變數線性迴歸公式在高中數學課程中已有提及：
迴歸直線的斜率、y 截距、相關係數的公式及意義。
在分析數據時偶爾會先規定好 y 截距(例如已知二變量成正比)，
Excel 也有提供這個功能，而且仍能算出一個「R^2」，
但此時這個 R 與原始數據的相關係數並不相同。
(至於斜率則確定是由最小平方法決定：Sum(XY)/Sum(X^2)。)
經過測試，前述的「R^2」的公式如下：
<XY>^2-<X^2><Y>^2 + 2b * (<X^2><Y>-<X><XY>) - b^2 * (<X^2>-<X>^2)
-------------------------------------------------------------------，
<X^2>(<Y^2>-<Y>^2)
其中 b 是預設的 y 截距，<Z> 代表數據 {Z_i}_{i=1}^{n} 的平均值。
若考慮的是「Y 與 X 應該要成正比」，即 b=0，
則「R^2」的公式簡化為
<XY>^2-<X^2><Y>^2
--------------------。
<X^2>(<Y^2>-<Y>^2)
已經知道的是：R^2≦1，且隨著 b 而變化的最大值就是相關係數^2。
如同一開始所說，想要針對 R^2 這個數值去分析數據，
但是卻遇到 R^2 可能出現負值(這點由公式可簡單看出)，
所以似乎難以將它詮釋成與「相關係數」類似的概念。
那到底應該怎麼去詮釋才合理呢？
以上就是想問的問題。因為關於統計大概只知道
高中程度+能用微積分計算一些分佈，所以寫得可能不是那麼專業，
還望各位大大海涵並先謝謝各位指導。
[題外]:
打完才發現我並不是對程式碼有問題，這樣的話分類在程式真的對嗎？
不對的話請告訴我，我會改標題。
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.230.122.4
※ 文章網址: https://www.ptt.cc/bbs/Statistics/M.1483264557.A.71F.html
→ andrew43: 「先規定好 y 截距(例如已知二變量成正比)」你確定？ 01/02 11:52
擬合用的模型是 y=ax (這就是手動規定 y 截距=0)
極小化 Σ(y_i - a*x_i)^2 得到 a=[Σ(x_i*y_i)]/[Σ(x_i^2)]
不過至少物理上這個做法滿常用到。
因為很多模型已經確定是正確的，例如測量一物體的密度：D=M/V。
在校正過所使用的儀器後，
1. 把測得的質量對體積作圖，計算迴歸直線斜率。
2. 把測得的質量對體積作圖，計算最佳過原點直線的斜率。
3. 對每筆數據計算 M/V，再求其平均。
以上三個方法應該都可行，只是今天剛好選了2而已。
還是說這個方法就是胡說八道而已呢？
※ 編輯: Vulpix (61.230.122.4), 01/02/2017 13:08:51
推 andrew43: 你的意思應該是截距為0而不是固定截距。就算不為0，變 01/02 13:14
→ andrew43: 數間仍可存在正相關。 01/02 13:14
→ andrew43: 有截距項的最小平方法回歸的R2才會是Pearson相關的平方 01/02 13:17
→ andrew43: 。 01/02 13:17
→ Vulpix: 我說的「先」是指在選定模型的時候就不考慮截距非0的可能 01/02 13:20
→ Vulpix: 那……這樣跟我先固定了截距=0有差嗎？ 01/02 13:21
→ arbiteron: 畫資料散佈圖跟你的模型看看,配適結果可能比水平線還差 01/02 14:33
→ Vulpix: 這個我知道，可是我想問的是這樣的「R^2」應該如何詮釋。 01/02 14:50
→ andrew43: 我沒檢查你的R^2算對了沒，但它不可能小於0。 01/02 15:41
→ andrew43: 如果算對了，R^2的意義一直是存在的，和相關不必有關係 01/02 15:42
→ andrew43: 意義就是「總變異能被迴歸線解釋的量」。 01/02 15:43
→ Vulpix: Excel給的數字就是會有負的，所以才難以想像… 01/02 17:29
→ b99703117: 不放截距項，R2就可能會是負的；而且也不能用一般 01/02 18:14
推 andrew43: 那就是算錯了，或者不知道算成什麼東西。 01/02 18:15
→ b99703117: 教科書上的敘述去解釋。 01/02 18:15
→ b99703117: 另外一提，我認為你的資料很可能不同意截距為0的假設 01/02 18:18
→ b99703117: 你可以跑一個一般的OLS，然後檢定常數項=0試試看 01/02 18:18
我想應該是我的說法有問題……
Excel 給了 R^2，
但沒有一個地方找的到公式，那個公式是我自己嘗試幾筆數據後歸納而得。
(也經過多筆隨機產生的數據驗證過，所以程式給的 R^2 就是這麼算的。)
目前遇到的數據若真的「應該」成正比，那用這個模型擬合的效果其實不差。
而 R^2 也頗接近 1，通常也比相關係數^2小。
但是「從公式來看」，R^2 有可能是負值，
所以不應該單純以相關係數的角度去詮釋它。
甚至，它似乎應該當成某種更廣義的東西，但那到底是什麼？
經過配方，公式可改寫成：
R^2 = r^2 - (σ_X/σ_Y)^2 / <X^2> * (b-"最佳b")^2
其中 r 是 Pearson 相關係數，
σ_Z 表示 Z 這組數據的樣本標準差，
"最佳b" 則是一般線性迴歸所得的 y 截距。
※ 編輯: Vulpix (61.230.122.4), 01/02/2017 18:49:06
→ Vulpix: 抱歉，這麼晚才看到這篇。我要找的就是樓上給的，感恩！ 04/09 14:24