[Excel] 如何將不連續儲存格套用於XIRR ( XIRR for Non Contiguous Data )
最近使用 XIRR的函數進行年化報酬率的試算，遇到了 XIRR必須使用連續儲存格，但我的資料就不是連續的阿 XD，原本想說這就是小問題，但上網找了居然大部分給出的答案都是錯的 !!!! XD，所以紀錄正確的使用方法。
先說一下XIRR的使用方法，XIRR有兩個參數，第一個參數是現金流，分為兩段，前段是你每期給了多少錢 (Figure 1, B2:B9)，並在最後一列給出終值 (Figure 1, B10)，但終值必須是負數(或每期金額為負，終值為正)；第二個參數是日期 (Figure 1, A2:A10)。
公式寫成 “ = XIRR(B2:B10, A2:A10)”，這個結果是 0.5896，見Figure 1 D3。
現在的問題是，如果B10的數值不在B9下面的B10，那我該怎樣寫?
“ = XIRR(IF({1,0},B2:B9,B10), IF({1,0},A2:A9,A10))”
上面是我在網路上看到的解法，結果如 Figure 2 的 D4，這個解法確實將儲存格以不連續的方式塞進XIRR裡面，但結果是錯的 ! 他的結果與我們的 0.5896 明顯不同。不過還是先了解這個公式是怎樣運作的。
我們的目的是把不連續的儲存格接續在一起，那 IF 的想法是這樣，如果結果是True，顯示第一個參數結果；False，顯示第二個。所以如果我把不連續儲存格的上段放在 True 的位置，下段放在 Flase 的位置，這時我讓條件同時符合 True 和 False，這樣不就接起來了 ? 所以就有了下面的寫法。
“ = IF({1,0},B2:B9,B10)”
可以看到 IF 的第一個參數是 {1, 0} 的陣列，並同時包含 True 和 False，所以他會同時顯示 B2:B9 與 B10，確實串聯了不連續的儲存格。這點沒問題，問題是答案是錯的，所以是怎樣 ?
Figure 3 的 D7 是 “ = IF({1,0},B2:B9,B10)” 的執行結果，可以看到結果是4，但這不是他的執行結果，這只是他陣列的其中一個項目。
如果將儲存格位置定在 D4 上，接著把游標點到公式列，並按下鍵盤的 F9。這時就可以看到 D4 真正的數值 ，是由大括號{...}包起來的陣列，如下。
“ ={1,-20;1,-20;2,-20;2,-20;3,-20;4,-20;5,-20;1,-20} ”
在 Excel 裡面，“ , ” 代表的是不同欄，而 “；” 代表的是不同的列，所以這個IF 的結果其實是 8 x 2 的二維陣列。所以他並不是將不連續的儲存格相連，而是前段儲存格一一對應最後一個儲存格，才造成 XIRR 的答案是錯的。
另一個相似的寫法是用CHOOSE，直接列在 Figure 5 的 E5。還有一點，因為 IF 跟 CHOOSE 的結果是陣列，所以XIRR對應的兩個位置都要是陣列，否則無法計算。
好吧，上面講了一堆錯的寫法，所以到底怎樣才是對的 XD。
這個方法是請一位多年好友才知道的，先在此感謝他的卓越貢獻。我初次看到這個解法直是驚為天人!!!!
詳細如下，結果如 Figure 6 ，可以看到 D6 結果與 D3 的結果相同。
“ = XIRR(IF(1-FREQUENCY(100^100, A2:A9), B2:B9, B10), A2:A10) ”
如果有理解上面錯誤過程的寫法，應該挺容易可以知道這個公式的原理。關鍵在於 FREQUENCY 這個函數。
FREQUENCY 這個函數是依據給定區間統計數值次數，舉一個例子，如果今天區間為 1 至 5 間隔為 1 的數列，接著我有 0、1 與 6 三個數字，函數結果會是一個陣列，如 Figure 7 。
他統計了 小於1、介於12、介於23、介於34、介於45、大於5 的 6個區間，並做成一個陣列，如 Figure 7 的 E3。
與錯誤寫法不同的是，FREQUENCY的結果是一維陣列，這個結構可以幫助我們連結不相連的儲存格。
了解 FREQUENCY 的用法後，直接看 “ = FREQUENCY(100^100, A2:A9)” 的結果，見Figure 8 的 D8 ，第一個參數，我放了一個極大的數值，所以他會在我設定區間的最後一個位置顯示1次計次，而其他區間全為0；
第二個參數放時間，我只是要給一個順序數值，基本上放什麼都可以，只要注意長度要是我們需求的陣列大小，也就是不連續儲存格的前段長度。這時我們可以得到 {0;0;0;0;0;0;0;0;0;1} 的陣列。
接著用 1 - FREQUENCY(100^100, A2:A9) 得到 {1;1;1;1;1;1;1;1;1;0} 。這個結構就是我們不連續儲存格前段，與一個後段儲存格的結構。
這時，我們將這個結果套用至 IF 中，IF(1-FREQUENCY(100 ^ 100, A2:A9), B2:B9, B10)，可以得到一個連續一維陣列 {1;1;2;2;3;4;5;1;-20} !!!! 而這個結構就可以滿足 XIRR 兩參數均是連續儲存格的要求。
所以就能得到 Figure 8 中 D6 的結果，並讓他與D3的結果相同。
