Excel用Sum函數實現多表同位置與數字帶單位及新增數據後自動求和

在 Excel 中，除在同一表格求和外，有時還要跨表求和，後一種情況又可分為對多表同一位置求和與不同位置求和。無論是哪種情況，都可能遇到數字帶單位或空格的情況，此時，需要先把單位或空格去掉再求和，這就需要用到 Left、Find、Trim、Clean、Substitute 等函數；另外，可能還會遇到在表格中新增加數據後要求自動求和，這需要用Sum與OffSet和CountA函數組合實現。以下就是它們的具體實現方法，共有5個實例，實例操作中所用版本均為 Excel 2016。

一、Excel多表同位置求和

1、假如要求10月到12月每件服裝的與所有服裝的銷量之和，每個月的銷量在不同的表中。當前工作表為「10月」，依次單擊工作表標籤「11月和12月」切換到它們，各工作表的服裝都一樣，只是銷量不同；雙擊 E2 單元格，把公式 =SUM('10月:12月'!D2) 複製到 E2，按回車，返回「長袖白襯衫」3個月的銷量之和；選中 E2，把滑鼠移到 E2 右下角的單元格填充柄上，滑鼠變為黑色加號後，雙擊左鍵，則求出剩餘服裝的3個月銷量和。雙擊 D7，把公式 =SUM('10月:12月'!D2:D6) 複製到 D7，按回車，返回所有服裝 10 到 12 月的銷量之和；操作過程步驟，如圖1所示：

圖1

2、公式說明：

A、公式 =SUM('10月:12月'!D2) 中，'10月:12月'表示從工作表「10月」到「12月」，D2 表示引用三個表格的 D2 單元格，感嘆號 ! 用於分隔工作表與引用單元格；公式的意思是：對三個工作表的 D2 中的數值求和。

B、公式 =SUM('10月:12月'!D2:D6) 與上一個公式是一個意思，只是對三個工作表的 D2:D6 這片單元格求和。

C、以上兩個公式都是對多個不同的表格同一位置求和，它們把表格合寫在一起，如果要對不同的表格的不同位置求和，需要把表格分開寫，假如要對 Sheet1 的 B2:B10 和 Sheet2 的 C2:C10 求和，則公式要這樣寫：=SUM(Sheet1!B2:B10,Sheet2!C2:C10)。

二、Excel數字帶單位的求和

1、有一個銷量帶單位「件」的服裝表，現要求對銷量求和。雙擊 D8 單元格，把公式 =SUM(--LEFT(D2:D7,FIND("件",D2:D7)-1)) 複製到 D8，如圖2所示：

圖2

2、由於公式為數組公式，需要按 Ctrl + Shift + 回車，按此三組合鍵後，返回求和結果 5680，如圖3所示：

圖3

3、公式說明：

A、FIND("件",D2:D7) 用於返回 D2:D7 中每個數字後「件」字的位置，目的是計算每個單元格數字有幾位，從而為下一步截取數字提供要截取的長度，這主要是因為每個單元格的數字位數不一致，有的三位有的四位，如果全是三位或四位，則可不用Find函數。執行時，第一次取出 D2 中的「458件」，然後返回「件」字的位置為 4；第二次取出 D3 中的「962件」，也返回「件」的位置 4，其它的以此類推，最後返回{4;4;5;4;5;4}。

B、由於返回的是「件」的位置且比數字多一位，因此要用Find函數的返回值減 1，即 {4;4;5;4;5;4}-1，計算結果為 {3;3;4;3;4;3}。

C、則 LEFT(D2:D7,FIND("件",D2:D7)-1) 變為 LEFT(D2:D7,{3;3;4;3;4;3})，進一步計算，第一次從 D2:D7 取出 D2（即「458件」），再從數組中取第一個元素 3，然後截取三位數字 458；第二次取 D3（即「962件」），再從數組取第二個元素 3，也截取三位數字 962，其它的以此類推，最後返回數組 {"458";"962";"1240";"625";"1630";"765"}。

D、則公式變為 =SUM(--{"458";"962";"1240";"625";"1630";"765"})，接著把數組中的數字由文本轉為數值，-- 在這裡的作用是把字符轉為數值，它相當於Value函數；則公式變為 =SUM({458;962;1240;625;1630;765})，最後對數組中的所有數值求和。

三、Excel數字帶空格或非列印字符的求和

（一）Sum + Substitute 實現數字帶Trim去不掉空格的求和

1、選中 D8 單元格，輸入公式 =sum(d2:d7)，按回車，返回 0；雙擊 D2，數字的左邊有空格，雙擊 D8，把公式改為 =sum(--trim(d2:d7))，按 Ctrl + Shift + 回車，返回值錯誤 #VALUE!；雙擊 D7，選中數字前的空格，按 Ctrl + C 複製，再雙擊 D8，把 TRIM 改為 Substitute，再在 D7 後輸入「,"」，接著按 Ctrl + V 粘貼剛才複製的空格，繼續輸入「",""」，則公式變為 =SUM(--SUBSTITUTE(D2:D7,"",""))，按 Ctrl + Shift + 回車，返回求結果 4080；操作步驟，如圖4所示：

2、公式說明：

A、公式 =sum(d2:d7) 之所以返回 0，是因為 d2:d7 中數字前或後有空格，Sum函數不能把它們去掉，而又無法把空格轉為數值，因此導致返回 0。

B、公式 =sum(--trim(d2:d7)) 返回錯誤，是因為trim也不能把數字前或後的空格去掉，把公式複製到 C8，按「Ctrl + Shift + 回車」求和後，按住 Alt，分別按 M 和 V 打開「公式求值」窗口，按回車求值，經 trim 去空格後，數字前仍然還有空格，演示如圖5所示：

圖5

C、公式 =SUM(--SUBSTITUTE(D2:D7,"","")) 中，Substitute 用於用 "" 取代 D2:D7 中的空格 ""（該空格是操作中複製數字前的空格），取代空格後，它以數組形式返回 D2:D7 中的值，則公式變為 =SUM(--{"458";"962";"640";"625";"630";"765"})，接著，把數組的文本型數值轉為數值型，最後對數組中的數值求和，跟上面介紹的數字帶單位求和一樣。

（二）Sum + Clean 實現數字帶非列印字符的求和

1、選中 C8 單元格，輸入公式 =sum(c2:c7)，按回車，返回 2043；雙擊 C9，把公式 =SUM(--CLEAN(C2:C7)) 複製到 C9，按 Ctrl + Shift + 回車，返回 4013；操作如圖6所示：

圖6

2、公式說明：

A、從操作中可知，公式 =sum(c2:c7) 只返回了部分數值的求和結果，有非列印字符的三個單元格未參與求和，因為Sum函數不能把這些字符去掉，所以此三個數值被忽略。

B、公式 =SUM(--CLEAN(C2:C7)) 返回的是 C2 到 C7 中所有數值之和，因為Clean函數能把非列印字符去掉，使所有數字都參與了求和。

3、如果遇到用Clean函數去不掉的非列印字符，也需要用Substitute函數，只要把去不掉的字符複製到該函數的第二個參數，把它取代即可，如圖7所示：

圖7

四、Excel新增數據後自動求和

1、假如要求每天輸入收益後自動計算總收益。雙擊 C2 單元格，把公式 =SUM(OFFSET(B1,1,,COUNTA(B:B)-1)) 複製到 C2，按回車，返回求和結果 657.3；選中 A10，輸入「9日」，按 Tab 鍵把光標移到 B10，輸入 92，單擊 A11，則 C2 中自動累加新輸入的 92，值變為 749.3；繼續輸入「10日」，再輸入 88.3，單擊 C2，也自動累加 88.3，求和結果變為 837.6；操作步驟，如圖8所示：

圖8

2、公式=SUM(OFFSET(B1,1,,COUNTA(B:B)-1)) 說明：

A、B:B 表示引用 B 列，COUNTA(B:B) 用於統計 B 列的非空單元格數，當表格只有 9 行時，它返回 9，9-1 等於 8，恰好是數值的個數；新增加一行後，它返回 10，再減 1，也恰好是數值的個數，其它的以此類推。

B、則 OFFSET(B1,1,,COUNTA(B:B)-1) 變為 OFFSET(B1,1,,8)，接著以 B1 單元格為基準，返回 B1 下 1 行、寬度為1和高度為 8 的單元格引用，即返回對 $B$2:$B$9 的引用。OffSet函數省略了第三個參數「列號」，默認取 0，即取 B1 右側 0 列，也就是取 B 列；另外還省略了第四個參數「寬度」，默認取 B1 的寬度，即取 1。

C、則公式變為 =SUM($B$2:$B$9)，最後用Sum函數對 B2:B9 中的所有數值求和。