ecel的sum函數(shù)種公式設(shè)置范例

1、Excel的SUM函數(shù)9種公式設(shè)置范例1、數(shù)組求和： =SUM(G12:G21100)*G12:G21)公式說明 ：本公式為數(shù)組公式，可以對 G12:G21 區(qū)域中大于 100的數(shù)據(jù)進(jìn)行求和， 而排除小于等于 100 的數(shù)據(jù)。輸入公式時(shí)必須按【 Ctrl+Shift+Enter 】組合鍵結(jié)束， 否則無法得到正確結(jié)果。使用注意:1、公式中G12:G21100部分表示求和條件，后跟實(shí)際求和區(qū)域G 1 2 : G2 1 。如果有多個(gè)條件，可一并羅列出來。例如求大于100且小于 115的數(shù)據(jù)之和，公式如下：二SUM(G12:G21100)*(G12:G21100)*G12:G21),SUM(H12:H

2、21100)*H12:H21) 。 3、 對于 SUM 函數(shù)的數(shù)組公式， 可以用 SUMPRODUCT 函數(shù)來代替， 從而將數(shù)組公式轉(zhuǎn)換成 普通公式 。 例如 本案例的公式 用 SUMPRODUCT 函 數(shù) 后， 普通公式 如 下：二SUMPR0DUCT(G12:G21100)*G12:G21)。2 、 數(shù) 據(jù) 類 型 轉(zhuǎn) 換 求 和 :=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10) ；=SUM(-(H5),H6:H10,J5:J10,L5:L10)；=SUM(H5)*1,H6:H10,J5:J10,L5:L10)；=SUM(H5)/1,H6:H10,J5:J10,L

3、5:L10)公式說明 : SUM 函數(shù)用于對單元格區(qū)域的數(shù)據(jù)或者邏輯值、表達(dá)式進(jìn)行求和，它有1-255個(gè)參數(shù)。鑒于本題的特殊性，公式也可以改為 =SUM(區(qū)域1)，函數(shù)會(huì)忽略區(qū)域中的文本使用注意 ：1、 SUM 函數(shù)有 1-255 個(gè)參數(shù)。參數(shù)可以是區(qū)域，也可以是表達(dá)式。如:二SUM(G5,10*25,G6*8)2、如果是文本型數(shù)字參數(shù)，SUM函數(shù)是可以直接求和的； 如果是引用單元格中的文本型數(shù)字(單元格的數(shù)字前添加半角單引號“” ) ,則求和時(shí)將忽略。例如G5的值是“ 95”那么以下公式的結(jié)果大不同。二SUM(10,95)結(jié)果為105,=SUM(10,G5)結(jié)果為10。3、如果一定要對單元格

4、中文本型數(shù)字進(jìn)行求和， 可以將它轉(zhuǎn)換成數(shù)值。 轉(zhuǎn)換方法包括多種 :利用 VALUE 函數(shù)轉(zhuǎn)換、利用-轉(zhuǎn)換和 *1、 /1 方式轉(zhuǎn)換。例如 :=SUM(10,VALUE(G5) 、 =SUM(10,-(G5)、 =SUM(10,(G5)*1)3、多條件求和 1：=SUM(G23:G32110)*G23:G32)公式說明 ：對于兩個(gè)范圍求和，即滿足兩個(gè)條件中任意一個(gè)條件都進(jìn)行求和，這和 同時(shí)滿足兩個(gè)條件才求和的公式思路不同。 案例 2的使用注意 中已提到，多個(gè)條 件同時(shí)滿足才求和需要用 *連接所有條件，表示同時(shí)滿足多條件，而本案例中對多 個(gè)條件用 + 來連接， 表示對于多條例中滿足任意一個(gè)條件就可

5、以進(jìn)行求和。 本公式 為數(shù)組公式。使用注意 ：1、多條件滿足任意條件即求和，在設(shè)置公式時(shí)對于所有條件需要用 +連接，如本例中(G23:G32110),然后對條件用括號括起來再與實(shí)際 求和區(qū)相乘。如果缺少條件的外括號將得到錯(cuò)誤結(jié)果。2、如果不只兩個(gè)條件，也用同樣方式將所有條件羅列出來并相加。例如求區(qū)域中小于 100 或 者 等 于 120 的 數(shù) 據(jù) 和 ， 那 么 數(shù) 組 公 式 如下:=SUM(G23:G32 3、本公式可以用另一種簡化寫法，仍然可以返回 正確結(jié)果 :=SUM(H34:H43&I34:I43= 一車間男 )*J34:J43)5、多工作表求和時(shí)表名稱快捷輸入：二SUM(附表一:

6、附表五！B3:B10)公式說明 ：對多表相同區(qū)域求和，不需要錄入每個(gè)工作表的求和地址，只需將第一 個(gè)工作表名和最后一個(gè)表名用冒號連接，后跟!與單元格地址，以此作為SUM函數(shù)的參數(shù)即可。其中 ！表示前面的字符為工作表名稱。使用注意 ： 1、本方法僅適用于每個(gè)工作表地址一致的情況，否則只能逐個(gè)輸入地 址再求和。 2、多表求和， SUM 函數(shù)的參數(shù)，工作表與單元格地址可以手動(dòng)輸入， 也可以通過選擇工作表中單元格地址來產(chǎn)生引用。具體步驟如下：輸入=SUM(，然后選擇待匯總的第一個(gè)工作表B3:B10區(qū)域，按住【Shift】鍵再單擊工作表標(biāo)簽中待求和的最后一個(gè)工作表名，最后按【Enter】鍵結(jié)果結(jié)束即可。

7、3、如果工作表名中包 含一些特殊字符，例如! 、 ，公式中的工作表名需要用單引號括起來，例如:二SUM（A 組:!E 組!B3:B10）6、 快速設(shè)置當(dāng)前表以外的所有工作表相同區(qū)域的總和公式：=SUM（*!B3）公式說明 1 、公式中兩個(gè)單引號中間加 * 表示除當(dāng)前表以外的所有工作表。 2、在 輸入公式后，程序會(huì)自動(dòng)將 *轉(zhuǎn)換成實(shí)際的工作表名。當(dāng)按下【 Enter】鍵后，公 式將變成：=SUM（附表一：附表五!B3）使用注意 1、 *只能手動(dòng)輸入，而不能用鼠標(biāo)選擇工作表來產(chǎn)生引用。2、公式中B3必須使用相對引用。3、本公式用法和案例7是不同的。在本例中雖然也可以用案例7的方法按住【Shift】

8、鍵來選擇需要求和的工作表產(chǎn)生引用，但是操作煩瑣， 特別是總表不在最后或者最前面， 而是在待求和的工作表中間時(shí)， 用案例 7的方法 需要選擇五次，而本例公式中的 *可以自動(dòng)的找到排除當(dāng)前表之外的所有工作表數(shù) 據(jù)。也就是說 總表不管在任何位置，都可以用同樣的公式完成求和，而不需手動(dòng) 修改。 4、公式中 * 左右的單引號必須是半角符號。7、 用 SUM 函數(shù)計(jì)數(shù)：=SUM（H94:H102二男）*（194:1102二漢）公式說明 ： SUM 函數(shù)雖然是求和函數(shù)，在很多時(shí)候可以用來計(jì)數(shù)。本例中利用 H94:H101= 男這一表達(dá)式返回一串包含 TRUE 和 FALSE 的數(shù)組，最后用 *1將邏輯值轉(zhuǎn)換

9、成數(shù)值，并用 SUM 函數(shù)匯總使用注意:1、表達(dá)式H94:H101=男得到的是包含邏輯值的數(shù)組。SUM函數(shù)可以對邏輯參數(shù)進(jìn)行求和，但是包含于數(shù)組中的邏輯值值卻會(huì)被忽略。例如以下公式:二SUM(TRUE,1)結(jié)果為2,二SUM(TRUE,1)結(jié)果為1。但是通過*1將邏輯值轉(zhuǎn) 換成數(shù)值后就可以直接求和了。例如 :=SUM(TRUE,1*1) 結(jié)果為 2。 2、將邏輯值 轉(zhuǎn)換成數(shù)值，除了用 *1之外，也可以改用 -或者/1 等手法，但是用 VALUE 函 數(shù)卻不行，它只能將文本型數(shù)字轉(zhuǎn)換成數(shù)值。例如:=SUM(-(TRUE,1)結(jié)果等于2；=SUM(TRUE,1)/1) 結(jié)果等于 1；=SUM(VA

10、LUE(TRUE,1) 結(jié)果為錯(cuò)誤值，因 為 VALUE 函數(shù)無法轉(zhuǎn)換成 TRUE。8、求 1 累加到 100之和: =SUM(ROW(1:100)公式說明:本公式利用ROW函數(shù)返回1到100的序列，然后用SUM函數(shù)將這個(gè) 序列匯總得到 1100的合計(jì)。使用注意:1、ROW函數(shù)只能產(chǎn)生1到1048576之間的自然數(shù)(Excel2003版本為65536), 且不帶小數(shù)。但是通過換算也可以完成其他序列的求和。例如1/1、1/2、1/3、1/4 1/99、1/100 之和?？梢杂靡韵鹿?:=SUM(1/ROW(1:100) 。如果是求 1 開1 次方， 2 開 2 次方， 3 開 3 次方至 10

11、0 開 100 次方的總和，那么可以用以下數(shù)組公 式:=SUM(R0W(1:100)A(1/R0W(1:100)。如果是對1100之間的奇數(shù)進(jìn)行求和， 可以用以下數(shù)組公式:=SUM(ROW(1:50)*2-1) 0 2、ROW(1:100)產(chǎn)生的結(jié)果是一個(gè)包 含1到100之間的自然數(shù)數(shù)組。用 SUM函數(shù)對這種數(shù)組求和時(shí)必須用數(shù)組公式，否 則只能對數(shù)組中的第一個(gè)數(shù)值進(jìn)行求和。如果將 ROW(1:100)寫成1,2,3,4這種形式的數(shù)組則可以按照普通公式處理9、多個(gè)工作表相同區(qū)域求前三名產(chǎn)量之和：=SUM(LARGE(CHOOSE(1,2,3,4,5, 附表一 !B3:B10附表二！B3:B10附表三！B3:B10附表四！B3:B10附表五！B3:B10),ROW(1:3)公式說明:如以下公式：二SUM(LARGE(附表一:附表五!B3:B13,ROW(1:3)o因?yàn)長ARGE 函數(shù)不支持多個(gè)工作表地址作為第一參數(shù)，即三維引用