[程式] 關於Excel中趨勢線的R^2

[Vulpix (Sebastian)]

[軟體程式類別]:
        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 截距(例如已知二變量成正比)」你確定？
擬合用的模型是 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，變
→ andrew43: 數間仍可存在正相關。
→ andrew43: 有截距項的最小平方法回歸的R2才會是Pearson相關的平方
→ andrew43: 。
→ Vulpix: 我說的「先」是指在選定模型的時候就不考慮截距非0的可能
→ Vulpix: 那……這樣跟我先固定了截距=0有差嗎？
→ arbiteron: 畫資料散佈圖跟你的模型看看,配適結果可能比水平線還差
→ Vulpix: 這個我知道，可是我想問的是這樣的「R^2」應該如何詮釋。
→ andrew43: 我沒檢查你的R^2算對了沒，但它不可能小於0。
→ andrew43: 如果算對了，R^2的意義一直是存在的，和相關不必有關係
→ andrew43: 意義就是「總變異能被迴歸線解釋的量」。
→ Vulpix: Excel給的數字就是會有負的，所以才難以想像…
→ b99703117: 不放截距項，R2就可能會是負的；而且也不能用一般
推 andrew43: 那就是算錯了，或者不知道算成什麼東西。
→ b99703117: 教科書上的敘述去解釋。
→ b99703117: 另外一提，我認為你的資料很可能不同意截距為0的假設
→ b99703117: 你可以跑一個一般的OLS，然後檢定常數項=0試試看
我想應該是我的說法有問題……
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
→ andrew43: https://goo.gl/nJ97Ub 情況不太一樣但應該是答案。
→ andrew43: 還有 https://goo.gl/MLKMR 和 https://goo.gl/INERWa
→ Vulpix: 抱歉，這麼晚才看到這篇。我要找的就是樓上給的，感恩！