工作再忙，這5組Excel公式也要看，保你準點下班！

點擊藍字關注【秋葉 Excel】

發送【7】

免費領 1000+篇 Excel 精選教程！

本文作者：小花

本文編輯：竺蘭

大家好，這里是秋葉 Excel~

在財務投資測算中，回收期是很重要的參考指標，它是指從投資到收回本金的時間。

與凈現值 NPV 和內部收益率 IRR 不同，Excel 中并不存在專門的回收期函數。

于是，為了計算回收期，財務朋友們，可謂八仙過海，各顯神通！

我見過的最卑微的一種方法，竟然是用 IF 函數構建輔助行，再通過求和得出回收期。

▋例 1：IF+輔助行求精確回收期

C4 公式如下：

=IF(C3>0,MAX(-B3/C2,0),1)

公式說明：

使用 IF 函數進行條件判斷，當期累計經營性現金流小于或等于 0 的，返回 1。

如果當期累計經營性現金流大于 0，則返回上期累計經營性現金流與當期經營性現金流之比的負數和 0 之間的較大值 m（使用 Max 來完成大小判斷）。

此時，只有累計經營性現金流在當期首次實現回正，m 才為正數，否則 Max 函數返回 0。將輔助行求和的結果即為精確回收期。

不難發現，在累計經營性現金流回正后，當期值出現負值或累計值再次轉負時，該公式均無法正確計算。

輔助行+邏輯復雜，這樣的公式仍漏洞百出，回收期計算問題真的這么難解嗎？

其實不然！

學會我分享的這些公式，讓你輕松拿捏它。

什么，你想系統提升 Excel 技能？

那么千萬別錯過秋葉專為 0 基礎小白打造的《Excel 3 天集訓營》課程！

現在掃碼報名，你還能免費領取函數手冊+快捷鍵大全+110 套 Excel 模板！

別再猶豫了，趕緊掃碼

搶占免費學習名額

助力自己在職場中脫穎而出吧！

求整數回收期的方法

很多時候，我們計算投資回收期時，并不需要像例 1 那樣精確到小數，只需求整數位即可（相當于例 1 結果向上取整）。

這種情況下，可用的公式非常多，以下，僅分享其中比較經典的三種方法。

▋例 2：Countif 法求整數周期

如果累計經營性現金流回正后的剩余經營期間都不會變為負數，那么首次回正時間就是投資回收期。

如下圖中，累計經營性現金流在第 4 期回正后，剩余的第 5-6 期都是正數，沒有轉為負數，此時，首次回正時間是第 4 期，投資回收期間即為 4。

這種情況下，計算回收期問題就等同于在表示累計經營性現金流的一組數值中統計負數的個數 n，如果這組數值包括代表投資首期期初的第 0 期，那么 n 即為投資回收期，否則 n+1 為投資回收期。

因此，使用 Countif 函數來統計負數的個數進而計算投資回收期，就順理成章了。

公式如下：

=COUNTIF(B3:H3,"<0")

公式說明：

Countif 函數用于統計滿足條件的單元格個數，它的首個參數（條件區域）B3:H3 為包含第 0 期的累計經營性現金流數值組，第二個參數設置為"<0"，即可統計累計經營性現金流數值組中小于 0 的個數，其結果就是投資回收期。如果條件區域不包含第 0 期，則公式如下：

=COUNTIF(C3:H3,"<0")+1

▋例 3：Frequency 法求整數周期

在一組數值中統計負數的個數 n，Frequency 函數也是一把好手，而且似乎公式更為簡潔。

公式如下:

=FREQUENCY(B3:H3,0)

公式說明：

Frequency 函數用于計算數據范圍內的單元格數值在指定范圍中的分布頻率，怎么理解？

Frequency 函數的基礎語法：

=FREQUENCY(Data_array,Bins_array)=FREQUENCY(統計的區域,分段點)

相當于將第一個參數（數據范圍）上的所有數值依次在數軸上描點，再按第二個參數（指定范圍）的 n 個數值將數軸分為 n+1 段，統計每一數軸上的數據點個數。

本例中的第二個參數為 0，Frequency 函數以 0 為分界點，返回 B3:H3 中小于等于 0 的數據點個數 4，即投資回收期。

需要注意的是，如果累計經營性現金流可能出現嚴格等于 0 的情況，就會有點問題 , 如下圖：

如果數據點包含 0 , 分段點為 0 的情況下 , 0 會被包含進去。

更加嚴謹的公式應該使用-0.1^9 這樣接近于 0 的負數來作為分界點，公式如下：

=FREQUENCY(B3:H3,-0.1^9)

公式說明：

B3:H3 中小于等于-0.1^9 的值有 4 個（包含第 0 期），大于 0 的值有 3 個，Frequency 計算得到{4;3}，公式返回 4。

▋例 4：Match 法取整數周期

有些時候，累計經營性現金流在短暫回正后，會重新轉為負數，然后在一段時間后再次實現回正。

此時，使用上述兩種方法計算投資回收期就會出錯。

例如下圖中，累計經營性現金流在第 2 期首次回正后，在 3-4 期右轉為負數，第 5 期才完全實現回正，該例中的投資回收期應該為 5，但上述兩個公式的計算結果都為 4，顯然錯誤。

這是因為，這種情況下計算回收期不再等同于求負數的個數，而是求最后一個負數出現的位置序數，我們需要使用 Match 的模糊查找來實現。

公式如下：

=MATCH(-0.1^9,B3:H3,1)

公式說明：

=MATCH(查找目標,查找范圍,查找方式)

Match 的最后一個參數為 1，表示模糊查找，公式返回條件區域 B3:H3 中不大于第 1 個參數-0.1^9（無限接近于 0）的最后一個值所處的位置，B3:H3 中滿足這個條件的值為-6，它是 B3:H3 中的第 5 個值，因此，公式返回 5。

求精確回收期的方法

如果我們需要計算精確的投資回收周期，則上述三種方法都將不再適用。

這是因為，累計現金流回正的當期，所對應的回收期不再為 1，而是取上期累計經營性現金流回正缺口占當期經營性現金流的比值。

例 4 中，累計經營性現金流在第 5 期實現回正，但第 4 期累計經營性現金流為-6，經營性凈流入只需再實現+6，即可實現回正，而第 5 期經營性現金流為+140，相當于實現+6 僅占用了 6/140=0.04 期時間，所以精確回收期應該為 4.04，而不是 5。

此時，我們可以使用 Lookup 來計算精確回收期，公式簡單，但理解起來可能有點難度。

B6 單元格公式如下：

=LOOKUP(-0.1^9,B3:H3,COLUMN(A:G)-1-B3:H3/C2:I2)

公式說明:

查詢區域 Column(A:G)-1-B3:H3/C2:I2 的設置是本公式的核心。

其中 Column(A:G)-1 返回 0-6 組成的數組，表示當前期間以前經歷的期數，-B3:H3/C2:I2 為上期累計經營性現金流回正缺口占當期經營性現金流的比值，只有在現金流回正的前一期，查詢區域對應位置的值才等于投資回收期，其余數值均為無效結果。

而 Lookup 的原理與 Match 模糊查找類似，剛好能夠準確定位累計現金流回正前一期的位置，它根據條件區域 B3:H3 中不大于第 1 個參數-0.1^9 的最后一個值所處的位置 F3，返回查詢區域中對應位置的值Columu(E:E)-1-F3/G2，即 4.04，從而完成投資回收期的精確計算。

以上，就是我分享的5 種計算回收期的方法，包括：

?使用 IF+Max 構建輔助行再進行求和；

?使用 Countif 統計小于 0 的數值個數；

?使用 Frequency 統計數據范圍小于等于 0 的頻率；

?使用 Match 模糊匹配最后一個負數的位置序數；

?使用 Lookup 構建內含數組計算精確回收周期。

這些方法，特別是 Match 和 Lookup 兩種方法，是否解決了你在計算投資回收期方面的困惱呢？

掌握 Excel，真的可以助你解決數據難題，讓你的工作更高效！

如果你想更深入系統地學習，那么一定要加入秋葉 Excel 3 天集訓營~

秋葉金牌講師 @拉登Dony 手把手帶你提升 Excel，現在加入，還能獲得秋葉學員實戰用的10 套精選表格模板：

先給大家看看部分截圖 ↓

名額有限，學到就是賺到！

秋葉 Excel 3 天集訓營

現在僅需 0 元

▲ 現在報名，即可免費領取超多福利資料