收藏excel擬合曲線並輸出公式「excel擬合函數方程」

【面試題】

一個社交APP, 它的新增用戶次日留存、7日留存、30日留存分別是52%、25%、14%。

請模擬出來，每天如果日新增6萬用戶，那麼第30天，它的日活數會達到多少？請使用Excel進行分析。

【分析思路】

第1日(次日)留存用戶數=第1日新增用戶數*次日留存率

第2日活躍用戶數=第2日新增用戶數+第1日留存用戶數

第3日活躍用戶數=第3日新增用戶數+第2日留存用戶數（第2日新增用戶數*第2日留存率）+第1日留存用戶數

…

第30日活躍用戶數=第30日新增用戶數+第29日留存用戶數+第28日留存用戶數+…+第1日留存用戶數

也就是，

第30日活躍用戶數=1日-29日每天的留存用戶數（第1日留存用戶數+第2日留存用戶數+…+第29日留存用戶數）+第30日新增用戶數

現在只需要計算出1日-29日每天的留存用戶數就可以了，而第N日的留存用戶數=第N日新增用戶數（6萬）*第N日留存率。所以現在的問題是需要知道每天的留存率是多少。

那麼，問題就來了。題目只有3個留存率（新增用戶次日留存、7日留存、30日留存分別是52%、25%、14%）。

如何根據已有的幾個留存率去預測剩下那些天的留存率呢？

很簡單，用excel 1分鐘就能搞定。

1.用現有的數據做散點圖

2.對散點圖添加趨勢線

趨勢線有以下幾種類型，應該添加哪一類型的趨勢線呢？

一般來說，正常的留存曲線是一開始快速下降，然後開始緩慢下降，最後逐步平穩的曲線。

所以，留存曲線的形狀會類似於下圖：初始在震蕩期快速下降；選擇期開始緩慢下降；過了選擇期就是平穩期，留存率會進入一個相對穩定的階段。

這種留存曲線的形狀和乘冪函數十分接近，所以，在這裡我們用乘冪函數來對留存曲線進行擬合。同時勾選“顯示公式”和“顯示R平方值”。最終得到了如下曲線，函數公式為y = 0.5227x^-0.385，R² = 0.9997，可以看到擬合後的冪函數的形狀和上圖的留存曲線的形狀幾乎一樣。

拓展：

你肯定想知道這個趨勢線的可靠性有多大？

這就涉及到趨勢線的 R 平方值。R 平方值是介於 0 和 1 之間的數值。當趨勢線的 R 平方值為 1 或者接近 1 時，趨勢線最可靠。如案例演示中，R的平方值達到了0.9997，因此可以說這條趨勢線可靠性非常大。

要獲得最精確的預測，為數據選擇最合適的趨勢線非常重要。

那麼，什麼情況下選用什麼樣的趨勢線呢？

指數：指數趨勢線適用於速度增加越來越快的數據。

線性：線性趨勢線是適用於簡單線性數據集合的最佳擬合直線。如果數據點的構成的趨勢接近於一條直線，則數據應該接近於線性。線性趨勢線通常表示事件以恆定的比率增加或減少。

對數：如果數據一開始的增加或減小的速度很快，但又迅速趨於平穩，那麼對數趨勢線則是最佳的擬合曲線。

多項式:多項式趨勢線是數據波動較大時使用的曲線。

乘冪：乘冪趨勢線是一種適用於以特定速度增加的曲線。但是如果數據中有零或負數，則無法創建乘冪趨勢線。

移動平均：移動平均趨勢線用於平滑處理數據中的微小波動，從而更加清晰地顯示了數據的變化的趨勢。（在股票、基金、匯率等技術分析中常用）

3.計算第n天留存率

擬合出留存曲線後， 我們就可以根據擬合的函數公式（y = 0.5227x^-0.385）去計算次日到30日的留存率。

也就是把x=1,x=2…x=30,分別代入函數公式，這裡可以藉助Excel的power冪函數，求出結果。如下圖，可以看到按照模型函數公式計算出來的留存率，即模型留存率與給定的留存率幾乎完全一致（黃色的行）。

4.計算30天後日活數

通過上面的步驟，我們已經得到了每天的留存率。接下來我們就可以計算第30天的日活用戶數有多少。

第1日(次日)留存用戶數=第1日新增用戶數*次日留存率

第2日活躍用戶數=第2日新增用戶數+第1日留存用戶數

第3日活躍用戶數=第3日新增用戶數+第2日留存用戶數（第2日新增用戶數*第2日留存率）+第1日留存用戶數

…

第30日活躍用戶數=第30日新增用戶數+第29日留存用戶數+第28日留存用戶數+…+第1日留存用戶數

也就是，

第30日活躍用戶數=1日-29日每天的留存用戶數（第1日留存用戶數+第2日留存用戶數+…+第29日留存用戶數）+第30日新增用戶數

（1）在下圖Excel步驟1的地方是題目的設定，每天固定新增用戶數為6萬

在下圖步驟2的地方算出1日-29日每天的留存用戶數，也就是第N日新增用戶數（6萬）*第N日留存率（F列對應的留存率）。

（3）下圖步驟3將這一列的值求和，就是第30日活躍用戶數=1日-29日每天的留存用戶數+第30日新增用戶數。

5.總結

Excel里進行預測分析的2種辦法：

1）時間序列數據如何預測？用預測工作表

2）其他數據如何預測？先畫散點圖，然後添加趨勢線和公式