大家好,欢迎回来。
这节课,继续跟大家分享EXCEL的一些操作技巧——数据求和问题。
大家可能会说,数据求和,不就是用单元格地址直接相加,或用求和函数SUM(SUMIF、SUMIFS),或多个乘积求和函数SUMPRODUCT。
但是小编在本节课跟大家分享的是下面几种情况的求和。
【学习内容】
1、多个表格中相同位置求和。
2、多个空单元格中批量输入求和公式。
3、求合并单元格中的数据和。
4、忽略隐藏的行列求和
【学习目标】
1、会灵活使用相关函数解决问题;
2、提高处理数据能力。
【学习过程】
1、多个表格中相同位置求和
这样的情况,一般出现在已设定了规范的数据结构的表格中。比如工厂里的一台机器,它每次工作时,都会自动产生一份运行情况报告表,其中一项就是运行时间(如下表)。
这个报告表就是一个规范的数据表,现在想知道的是机器在一个月总共运行了多少小时。
假如机器每天只工作一次,一个月工作24天,也就产生了24张运行情况报告表。
这24张报告表,除了数据可能不同外,其结构是相同的。也就是运行时间所在的单元格(C12)的位置是一样的。
在使用EXCEL进行统计时,就是在多个表格中相同的位置求和。
其各个数据表的名称为0301、0302……0324,如果要用公式来计算,其公式只能是这样:
=‘0301’!C12 '0302'!C12 ……'0324'!C12
长长的一串,如果要求和的表更多,有100多个,相信大家都会觉得头痛。
像这样的情况,可以使用求和函数SUM,其公式为:
=SUM('0301:0324'!C12)
就可以解决问题了。
注意的是表格名称范围,用的是“ ‘ ”单引号。
如果是一百张表,名称为表1,表2……表100,要求和的单元格是A9,其公式可以写作:
=SUM('表1:表100'!A9)
用这样的技巧,是不是挺简单?
2、在多个空的单元格中输入同一求和公式
有时为了表格的美观,在数据结构的设计上,可能会将多个数据较小的放在一张表格里,以方便查看。比如下表,要统计一周以内,上午和下午参加的人数,像这样的不连续的求和统计,可以采用在空单元格中粘贴同一公式的操作来完成。
首先,要记住求和的快捷键【ALT】 【=】。
当表中要统计的数据没有空值,比如上面的数据,可以先选定所有数据,然后按【F5】,在弹出的“定位”对话框中,选择“定位条件”,按【确定】。
弹出“定位条件”对话框,选择“空值”,按【确定】,就可以将空的单元格全选中了。
然后再按【ALT】 【=】,就可以快速求和了,如下表所示。
3、对已合并了的单元格数据求和
出于对表格的美观设计的设计,有些表格将单元格合并,然后再来计算合并了的单元格的数据,如下表。
要求计算第十周每天的销售额。
可以看到的是,在本例中,每天销售的商品不同,如果将每天的销售客都用求和公式计算,那是比较浪费时间的, 而小编可以告诉大家一个快捷的方法。
(1)先选中E3单元格,输入公式:
=SUM(D3:$D$17)-SUM(E4:$E$17)
按回车,计算出星期一的销售总额。
(2)选中E列中所有已合并的单元格,单击编辑栏公式,使光标定位于公式的后面,如下表所示。
(3)然后按【Ctrl】 【Enter】组合键,就可以计算出每天的销售总额了,如下表所示。
4、忽略隐藏的行或列求和
在日常工作中,有时会遇到隐藏了行或列的数据表格。
而最麻烦的,可能是还要将隐藏了的行或列忽略,而只求显示的单元格数据之和。
如上面的成绩表,升中总分是不计算生物和地理学科的,将其隐藏起来,得到下面的表格。
现在要计算升中的总分,也就是忽略隐藏的列求和。
可以看到,如果用单元格直接相加的方式计算,也是可以的,但如果数据足够多时,一个单元格一个单元格地加,工作量也是比较大的。
在这里,要用到的两个函数CELL()和SUMIF()。
选中单元格N2,输入如下公式:
=CELL("width",C2)
按回车,返回的值是C2单元格的列宽。往右填充到T2,注意的是列数要跟成绩表的列数相等,得到如下的结果:
可以看到,被隐藏的列宽为0。
选中K2单元格,输入如下公式:
=SUMIF($N$2:$T$2,">0",C2:I2)
按回车,就可以得到忽略了生物和地理两个学科的成绩和了,如下表所示。
这里要说明的是:
CELL()是用于返回单元格的格式、位置或内容等信息的函数,本例第一个参数"width"表示返回单元格的列宽,当单元格被隐藏时,其列宽值为0。
SUMIF()是用于对区域中符合条件的值求和的函数,这里以辅助行中的值是否大于0作为求和的条件。当值大于0表示是未隐藏的数据。
下面看下怎样计算忽略行的求和。
就要用到一个函数SUBTOTAL()
如上面的成绩表,如果将4,5,6,7行的学生成绩隐藏,要求要算出其它学生的成绩和。
单击C11,输入如下公式:
=SUBTOTAL(109,C2:C10)
按回车,就可以计算出语文科忽略了隐藏数据的成绩总和了。
往右填充,得到所有学科的成绩和,如下表所示。
在这里要说明的是:
SUBTOTAL()函数的格式是SUBTOTAL(参数,数据区域)。
其功能是返回列表中数据的汇总值,可以计算隐藏行或忽略隐藏行的数据。
其参数决定是计算隐藏行或忽略隐藏行。
数字1至11,表示包含隐藏值。
数字101至111,表示忽略隐藏值。
采用何种运算方式,如下表所示。
【小结】
本课跟大家分享的内容比较多,要花较多的时间来理解,尤其是第4点有关忽略隐藏的列行的计算,其使用了CELL函数配合SUMIF函数求和,确实是需要一定的技巧的。
再来总结一下:
1、使用SUM(数据区域,单元格)的方式实现多个表格中相同位置求和。
2、使用【Alt】 【=】实现在多个空单元格中批量输入求和公式。
3、使用【Ctrl】 【Enter】组合键,以倒求的方式,实现合并单元格中的数据和。
4、SUBTOTAL()、SUM()、CELL()等函数,实现忽略隐藏的行或列求和。
好了,今天的课就分享到这里,欢迎大家继续关注。
,