sum函数两个同行的元素相加_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 中的所有数值求和。