技巧區‎ > ‎中級程度‎ > ‎

永恆的總和(節錄自《晉身200% Excel高手》[2000年7月]

張貼者:2010年5月29日 上午6:51Carson Cheng
「永恆」似乎有點故弄玄虛,沒辦法!不賣弄半點噱頭,就不能吸引你去看看這個不能再簡單的SUM()函數!


像下圖般用上SUM()函數的報表,相信你每天都會接觸得到。
  SUM()的引數是一個範圍,範圍內的數字都會給加起來。


那你一定試過為了加插新的資料,而在SUM()函數的一列上多加幾行空列(如圖)。
  你會發現引數中的範圍不會因此而改變。


但你不用太擔心,因為只要新加的資料是緊貼著舊有的資料,Excel會很聰明地把SUM()引數中的範圍擴大,把新加的數值也包括在總和裡。
  在這例子裡,範圍原來是B2:B6,現在就變成B2:B7。


問題是,如果用戶不是乖乖地貼著舊資料打,而是隔著一空列來打,你的公式就會有問題。
  這則秘訣就是要針對這個問題。



我認為最好的方法就是利用INDIRECT(),再配合R1C1欄名列號表示法。

INDIRECT()函數
首先解釋一下什麼是INDIRECT(),它可以讓你不用直接寫出一個儲存格的位址而使用一個儲存格的數值,只需把位址放在一個文字字串裡就可以。例如B1的數值是10,INDIRECT("B1")就是10。你可能會覺得這函數很無聊,但這個函數的好處是,無論你把INDIRECT()這個公式抄到哪裡去,它永遠都會顯示B1的數值,因為文字"B1"是不變的。

R1C1欄名列號表示法
總和的範圍就是在同一欄中,由某列(資料的第一列)開始至總和公式以上的一列,用A1方式根本不能簡單地寫出來,但用R1C1方式就很容易,只要寫「R2C:R[-1]C」(假定由資料由第二列開始):
R2  代表第二列,是絕對值,相等於A1表示法中的$2。
R[-1] 代表上一列,是相對值,會自動隨SUM()函數的位置而改變。
C    代表同一欄。

永恆的總和
「永恆的總和」的公式就是:

  =SUM(INDIRECT("R2C:R[-1]C",FALSE))

註:INDIRECT()的第二個引數FALSE,代表不用A1表示法,而使用R1C1表示法。


注意:
1)雖然在INDIRECT()函數裡用上R1C1表示法,但你不必剔選「工具|選項|一般|[R1C1]欄名列號表示法」,換句話說,你可以沿用A1表示法寫公式。
2)這個方法的短處是,「稽核工具列」的追縱功能不能用在此公式上。
Comments