|
1、數(shù)組求和：{=SUM((G12:G21>100)*G12:G21)} [公式說明]：本公式為數(shù)組公式，可以對G12:G21區(qū)域中大于100的數(shù)據(jù)進行求和，而排除小于等于100的數(shù)據(jù)。輸入公式時必須按【Ctrl+Shift+Enter】組合鍵結(jié)束，否則無法得到正確結(jié)果。 [使用注意]：1、公式中"G12:G21>100"部分表示求和條件，后跟實際求和區(qū)域"G12:G21"。如果有多個條件，可一并羅列出來。例如求大于100且小于115的數(shù)據(jù)之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此數(shù)組公式只適用于單個區(qū)域求和，如果有多個區(qū)域，只能用多個SUM求和，然后相加。例如對G12:G21和H12:H21區(qū)域中大于100的數(shù)匯總，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。3、對于SUM函數(shù)的數(shù)組公式，可以用SUMPRODUCT函數(shù)來代替，從而將數(shù)組公式轉(zhuǎn)換成普通公式。例如本案例的公式用SUMPRODUCT函數(shù)后，普通公式如下:=SUMPRODUCT((G12:G21>100)*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,L5:L10) [公式說明]：SUM函數(shù)用于對單元格區(qū)域的數(shù)據(jù)或者邏輯值、表達式進行求和，它有1-255個參數(shù)。鑒于本題的特殊性，公式也可以改為"=SUM(區(qū)域1)"，函數(shù)會忽略區(qū)域中的文本。 [使用注意]：1、SUM函數(shù)有1-255個參數(shù)。參數(shù)可以是區(qū)域，也可以是表達式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型數(shù)字參數(shù)，SUM函數(shù)是可以直接求和的；如果是引用單元格中的文本型數(shù)字(單元格的數(shù)字前添加半角單引號“'”),則求和時將忽略。例如G5的值是“'95”，那么以下公式的結(jié)果大不同。=SUM(10,"95")—結(jié)果為105,=SUM(10,G5)—結(jié)果為10。3、如果一定要對單元格中文本型數(shù)字進行求和，可以將它轉(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:G32<100)+(G23:G32>110))*G23:G32)} [公式說明]：對于兩個范圍求和，即滿足兩個條件中任意一個條件都進行求和，這和同時滿足兩個條件才求和的公式思路不同。"案例2"的使用注意"中已提到，多個條件同時滿足才求和需要用"*"連接所有條件，表示同時滿足多條件，而本案例中對多個條件用"+"來連接，表示對于多條例中滿足任意一個條件就可以進行求和。本公式為數(shù)組公式。 [使用注意]：1、多條件滿足任意條件即求和，在設置公式時對于所有條件需要用"+"連接，如本例中"(G23:G32<100)+(G23:G32>110)",然后對條件用括號括起來再與實際求和區(qū)相乘。如果缺少條件的外括號將得到錯誤結(jié)果。 4、多條件求和2：{=SUM((H34:H43="一車間")*(I34:I43="男")*J34:J43)} [公式說明]：本公式以H、I兩列數(shù)據(jù)作為限制條件，對J列數(shù)據(jù)匯總?！?H34:H43="一車間")*(I34:I43="男")”表示同時滿足兩個條件，如果還有更多條件，也可以同時羅列出來，利用“*”符號連接，然后再與求和區(qū)相乘，得到最后的匯總結(jié)果。 [使用注意]：1、前三個案例是以求和數(shù)據(jù)自身為條件對目標區(qū)域進行求和，本案例以不同的區(qū)域作為限制，條件區(qū)有兩個，對第三區(qū)域進行求和。在設置公式時，三個區(qū)域的單元格個數(shù)必須一致。例如以下公式將產(chǎn)生錯誤結(jié)果:=SUM((H34:H43="一車間")*(I34:I44="男")*J34:J43)、=SUM((H34:H43="一車間")*(I35:I44="男")*J34:J43)。2、多條件求和時，條件錄用區(qū)域與求和區(qū)域可以在不同列，三個區(qū)域只需要寬度、高度一致即可。它們所在的列可以不同，起止行也可以不同，甚至三個區(qū)域可以分別位于不同的工作表中，仍然可以得到需要的結(jié)果。例如條件區(qū)在Sheet2工作表，求和區(qū)在當前工作表，公式可以修改為:=SUM((Sheet2!H34:H43="一車間")*(Sheet2!I34:I43="男")*J34:J43)。3、本公式可以用另一種簡化寫法，仍然可以返回正確結(jié)果:=SUM((H34:H43&I34:I43="一車間男")*J34:J43) 5、多工作表求和時表名稱快捷輸入：=SUM(附表一:附表五!B3:B10) [公式說明]：對多表相同區(qū)域求和，不需要錄入每個工作表的求和地址，只需將第一個工作表名和最后一個表名用冒號連接，后跟"!"與單元格地址，以此作為SUM函數(shù)的參數(shù)即可。其中"!"表示前面的字符為工作表名稱。 [使用注意]：1、本方法僅適用于每個工作表地址一致的情況，否則只能逐個輸入地址再求和。2、多表求和，SUM函數(shù)的參數(shù)，工作表與單元格地址可以手動輸入，也可以通過選擇工作表中單元格地址來產(chǎn)生引用。具體步驟如下:輸入"=SUM("，然后選擇待匯總的第一個工作表B3:B10區(qū)域，按住【Shift】鍵再單擊工作表標簽中待求和的最后一個工作表名，最后按【Enter】鍵結(jié)果結(jié)束即可。3、如果工作表名中包含一些特殊字符，例如"!"、"@"，公式中的工作表名需要用單引號括起來，例如:=SUM('A組:!E組'!B3:B10) 6、快速設置當前表以外的所有工作表相同區(qū)域的總和公式：=SUM('*'!B3) [公式說明]1、公式中兩個單引號中間加"*"表示除當前表以外的所有工作表。2、在輸入公式后，程序會自動將"'*'"轉(zhuǎn)換成實際的工作表名。當按下【Enter】鍵后，公式將變成:=SUM(附表一:附表五!B3) [使用注意]1、"'*'"只能手動輸入，而不能用鼠標選擇工作表來產(chǎn)生引用。2、公式中B3必須使用相對引用。3、本公式用法和"案例7"是不同的。在本例中雖然也可以用"案例7"的方法按住【Shift】鍵來選擇需要求和的工作表產(chǎn)生引用，但是操作煩瑣，特別是總表不在最后或者最前面，而是在待求和的工作表中間時，用"案例7"的方法需要選擇五次，而本例公式中的"*"可以自動的找到排除當前表之外的所有工作表數(shù)據(jù)。也就是說"總表"不管在任何位置，都可以用同樣的公式完成求和，而不需手動修改。4、公式中"*"左右的單引號必須是半角符號。 7、用SUM函數(shù)計數(shù)：{=SUM((H94:H102="男")*(I94:I102="漢"))} [公式說明]：SUM函數(shù)雖然是求和函數(shù)，在很多時候可以用來計數(shù)。本例中利用"H94:H101="男""這一表達式返回一串包含TRUE和FALSE的數(shù)組，最后用"*1"將邏輯值轉(zhuǎn)換成數(shù)值，并用SUM函數(shù)匯總。 [使用注意]：1、表達式"H94:H101="男""得到的是包含邏輯值的數(shù)組。SUM函數(shù)可以對邏輯參數(shù)進行求和，但是包含于數(shù)組中的邏輯值值卻會被忽略。例如以下公式:=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(VALUE({TRUE,1}))—結(jié)果為錯誤值，因為VALUE函數(shù)無法轉(zhuǎn)換成TRUE。 8、求1累加到100之和：{=SUM(ROW(1:100))} [公式說明]：本公式利用ROW函數(shù)返回1到100的序列，然后用SUM函數(shù)將這個序列匯總得到1—100的合計。 [使用注意]：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次方至100開100次方的總和，那么可以用以下數(shù)組公式:{=SUM(ROW(1:100)^(1/ROW(1:100)))}。如果是對1—100之間的奇數(shù)進行求和，可以用以下數(shù)組公式:{=SUM((ROW(1:50)*2-1))}。2、ROW(1:100)產(chǎn)生的結(jié)果是一個包含1到100之間的自然數(shù)數(shù)組。用SUM函數(shù)對這種數(shù)組求和時必須用數(shù)組公式，否則只能對數(shù)組中的第一個數(shù)值進行求和。如果將"ROW(1:100)"寫成"{1,2,3,4}"這種形式的數(shù)組則可以按照普通公式處理。 9、多個工作表相同區(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)))。因為LARGE函數(shù)不支持多個工作表地址作為第一參數(shù)，即三維引用。所以本公式利用CHOOSE函數(shù)將三維引用轉(zhuǎn)換成二維數(shù)組后，就可以作為LARGE函數(shù)的參數(shù)進行運算了。用LARGE函數(shù)提取前三最大值后再用SUM函數(shù)匯總。 [使用注意]：公式的常量數(shù)組"{1,2,3,4,5}"可以轉(zhuǎn)換為"COLUMN(A:E)"，而不用"ROW(1:5)"。因為"ROW(1:5)"等于"{1;2;3;4;5})。區(qū)別為一個是橫向數(shù)組，一個是縱向數(shù)組。 |
|
|
