|
今天介紹的案例來源于一個問題： 在Excel中如何計算CAGR。 除了CAGR這個詞有點唬人外,，這個計算其實非常簡單,。不過實際上這個問題有兩問：
第一個問題很簡單,，只要了解CAGR的計算規(guī)則即可,。第二個問題卻要復(fù)雜得多。 CAGR,，全稱是Compound Annual Growth Rate,，中文叫做復(fù)合年均增長率。 這個詞的本意是描述一項投資在給定周期內(nèi)的年度增長率,。但是實際上,，很多時候會將相同的分析用在其他業(yè)務(wù)領(lǐng)域，比如公司銷售額,，利潤率,，或者某個產(chǎn)品的用戶增長，不管對象如何變化,，它的計算方式都是一樣的,。 而且，雖然名字中含有“年度”,，在實際應(yīng)用中,，周期也不限定于年，可以是季度,，月,，天等等，一般都統(tǒng)稱為周期,。 我們以下面的數(shù)據(jù)為例：
我們有一只基金連續(xù)7年（或者7個周期）的累計凈值，計算一下在這7個周期中這只基金的CAGR,。 我們有如下的公式：
所謂終值就是最后一個周期的累計凈值,，初值就是最初一個周期的累計凈值。 套用這個公式,，
確實很容易吧,。 其實，在Excel中計算CAGR有很多方法,，比如,，有幾個財務(wù)函數(shù)就可以用在這里，還可以用幾何平均法,。這里就不贅述了,。 上面的公式很簡單，但是面對真實的問題場景就不一樣了,。
真實場景也不復(fù)雜,，就是數(shù)據(jù)中有多只基金，每只基金的統(tǒng)計周期也不一致,，起止年份也不相同,，現(xiàn)在需要做一個包含每只基金的CAGR報表,。 每只基金的CAGR你已經(jīng)會計算了，但是除了手工計算每只基金的CAGR,，你還有什么好辦法嗎,？ 在思考這個問題的時候，不妨想想如果你面對的數(shù)據(jù)量比較多的情況,，比如,，有100只基金。 其實,，你已經(jīng)會計算每只基金的CAGR了,，但是你會的只是靜態(tài)計算的方法。而實際的場景中需要的是如何設(shè)計一個過程,，將問題進行分解，在這個過程中應(yīng)用前面的靜態(tài)計算方法,。 比如,，針對這個問題，我們可以考慮按如下方式分解這個問題：
要得到結(jié)果,，你肯定就需要得到表3,，也就是需要知道每只基金的初值，終值,，以及周期數(shù),。 要得到表3，你肯定需要得到表2,，也就是必須知道每只基金的起始日期和截止日期,。 要得到表2，你肯定需要得到表1,，也就是了解這個表中有哪些基金,。 有了這樣一個過程，就可以考慮如何實現(xiàn)這個過程了,。 其實有很多方法完成這個過程：如果你使用的是Excel 2019之前的版本,，可以考慮加輔助表實現(xiàn)這個過程。也可以考慮是用超級透視表實現(xiàn)這個過程,。還可以考慮用PQ實現(xiàn)這個過程,。 不過，我們這里介紹使用Excel新函數(shù)實現(xiàn)：
要得到表1,，可以使用UNIQUE函數(shù),。 要得到表2，可以根據(jù)表1,，使用MINIFS和MAXIFS函數(shù),。 要得到表3,，可以根據(jù)表2，使用SUMIFS函數(shù)得到初值和終值,，使用COUNTIFS得到周期數(shù),。 要得到結(jié)果表，只要根據(jù)表3,，使用前面的CAGR公式即可,。 下面就可以直接寫公式了：
其中， products - 表1 maxdae - 截止日期 mindate - 起始日期,，products,，maxdate，mindate合起來就是表2 ev - 終值 pv - 初值 nper - 周期數(shù),，products,，pv，ev,，nper合起來就是表3 cagr - cagr計算結(jié)果 rslt - 結(jié)果表,，使用HSTACK將products和cagr合起來就是結(jié)果表。
詳細解釋請看視頻：  加入E學(xué)會,，學(xué)習(xí)更多Excel應(yīng)用技巧 http://www./portal/learn/class_list Excel+Power Query+Power Pivot+Power BI 自定義函數(shù) 底部菜單：知識庫->自定義函數(shù) 面授培訓(xùn) 底部菜單：培訓(xùn)學(xué)習(xí)->面授培訓(xùn) Excel企業(yè)應(yīng)用 底部菜單：企業(yè)應(yīng)用 也可以在歷史文章中學(xué)習(xí)Excel,，Power Query，Power Pivot,，Power BI,，Power Automate各種技巧。 |
|
|
