QQ登录

只需一步,快速开始

Excel用Sum函数实现多表同位置与数字带单位及新增数据后自动求和

依梦瑶 发表于 2019-4-2 21:32:52 | 显示全部楼层 |阅读模式

在 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.jpg
图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.jpg
图2
  2、由于公式为数组公式,需要按 Ctrl + Shift + 回车,按此三组合键后,返回求和结果 5680,如图3所示:
3.jpg
图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所示:
4.jpg
图4
  2、公式说明:
  A、公式 =sum(d2:d7) 之所以返回 0,是因为 d2:d7 中数字前或后有空格,Sum函数不能把它们去掉,而又无法把空格转为数值,因此导致返回 0。
  B、公式 =sum(--trim(d2:d7)) 返回错误,是因为trim也不能把数字前或后的空格去掉,把公式复制到 C8,按“Ctrl + Shift + 回车”求和后,按住>
5.jpg
图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.jpg
图6
  2、公式说明:
  A、从操作中可知,公式 =sum(c2:c7) 只返回了部分数值的求和结果,有非打印字符的三个单元格未参与求和,因为Sum函数不能把这些字符去掉,所以此三个数值被忽略。
  B、公式 =SUM(--CLEAN(C2:C7)) 返回的是 C2 到 C7 中所有数值之和,因为Clean函数能把非打印字符去掉,使所有数字都参与了求和。
  3、如果遇到用Clean函数去不掉的非打印字符,也需要用Substitute函数,只要把去不掉的字符复制到该函数的第二个参数,把它取代即可,如图7所示:
7.jpg
图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.jpg
图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 中的所有数值求和。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

精华推荐
  • 探究!做设计之前的“构思”

    探究!做设计之前的“构思”

  • 汉字之美!中文字体设计原则

    汉字之美!中文字体设计原则

  • 一支互联网雪糕的诞生

    一支互联网雪糕的诞生

  • 设计灵感来自何处?

    设计灵感来自何处?

  • 自行车停靠架和旧自行车变废为宝家居创意作品大全

    自行车停靠架和旧自行车变废为宝家居创意作

  • 造车生死局:要么转型,要么死

    造车生死局:要么转型,要么死

  • 从欠8千万到年赚8亿

    从欠8千万到年赚8亿

  • 一座非典型五线小城的日常

    一座非典型五线小城的日常

QQ客服热线
QQ:1090281100 周一至周日:09:00 - 21:00
WeChat:duzhe1069
Email:kaixin1069@vip.qq.com

优创意logo

勿要吝啬你无形资产,请为创新续源,知识、点子、灵感、经验、需求等均是创新源泉,你不经意的一句话将是另一个人的灵感。明天的明天,还有明天,我们应该把握今天,每一个今天,都有一个新的事物在出现,今天的漠视明天的落后,不浪费每一个学习的时刻,学习助力非凡。

技术支持 Discuz! X3.4 - 3.5 beta © 2001-2019 Comsenz Inc.

小黑屋|手机版|优创意 ( 粤ICP备16085288号-1 )|申请友链

粤公网安备 44011102001144 号 GMT+8, 2020-10-29 06:38 , Processed in 0.098674 second(s), 29 queries , Gzip On.

快速回复 返回顶部 返回列表