- ?
几个工作表创建一份数据透视表,你会吗?
Goole
展开
工作中常常会将不同类别的数据放在不同的工作表或工作簿中,然后需要将这些数据汇总到一个表中。
怎样实现多表求和呢?
实例1
下面有3个表格,需要将3个表格的销量汇总到一张表上。
解决办法1:
很简单啊,才三个表格而已嘛。输入公式“=SUM(解晴!B2,李毅!B2,珊珊!B2)”就好啦(每个数据之间用英文半角逗号分隔)。
确实如此。可是如果要汇总的是12个月的销量呢?一个一个手动点击要求和的数据,累不累?
解决办法2:
输入公式“=SUM(第一个工作表名称:最后一个工作表名称!B2)”即“=SUM(解晴:Sheet10!B2)”。其中第一个工作表名称和最后一个工作表名称之间用英文半角分号连接,以感叹号“!”结束。
这样,再多的工作表汇总也不怕了呢。
实例2
当要求和的数据比较多时,除了使用实例的办法2,还可以使用数据透视表多重合并计算功能。
解决办法:
步骤1:插入数据透视表,选择“使用多重合并计算区域”,接着点击“选定区域”。
Excel2016上找不到“多重合并计算”,可以通过快捷键“Alt + D + P”打开数据透视表向导,从而找到多重合并计算。(注意先按“Alt + D”,然后按住“Alt”键不放,同时按下“P”键即可。
步骤2:将所有需要合并计算的表格一一添加到数据透视表向导中。
这样,多表联合的数据透视表就创建好啦。
好处:
这就是一个正常的数据透视表,切片器、数据透视图等功能也都支持。不仅可以快速汇总,还可以同时查看各类原始数据。当数据更新时,直接刷新即可更新汇总数据。
注意:
很多人在创建多重合并计算数据透视表时,将“页1”拖动到“行”字段下,得到的是“项1”、“项2”、“项3”,这是因为创建数据透视表时选择了“创建单页字段”。
如果你想显示工作表名称,也可以选择“自定义页字段”,先选择数据区域,接着指定页字段数目,然后在字段中输入自定义名称。
当数据中有多列非数值的数据时,不适合于使用多重合并计算数据透视表。
谢谢阅读,每天学一点,省下时间充实自己。欢迎点赞、评论、关注和点击头像。
- ?
掌握这10个EXCEL求和技巧,从此再无求和难事
Jessie
展开
求和,是最常见的统计工作,但花样繁多的求和要求和条件,常常让不少人限入苦思冥想不得其解的困惑之中,今天运用EXCEL函数与公式,总结从初级到高级的10个求和技巧,与大家一起学习和交流。
初级篇
1、运用“=”、“+”来计算
这是最基础的求和方法,适合无规律或指定单元格求和。如下图:计算1、3、5月份的销售量。J2单元格:=E2+G2+I2,然后,向下复制
2、运用函数SUM求和
这是基础求和函数,适合连续区域求和,高级应用多为与其他函数结合形成SUM数组公式。说明一点,SUM函数的参数,可以是单元格区间,也可以是单个单元格。如下图:计算1、3、5月份的销售量。J2单元格:=SUM(E2,G2,I2),然后,向下复制
中级篇
3、忽略文本跨列求和
如下图,计算全部销售人员的销售额。C10单元格:=SUM(A2:I7)
利用Excel中sum函数本身对文本类型数值自动忽略的规定,所以,可以用来直接对跨列的数值型数据求和。
4、单条件求和,运用求和函数:SUMIF。
如图,计算1月份冰箱的销售量。K2单元格:=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18)
SUMIF函数基本结构:SUMIF(条件区域,条件,求和区域)
5、多条件求和,运用求和函数:SUMIFS。
如图,计算销售一部1月份相机销售量。L2单元格:=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机")
SUMIFS函数基本结构:SUMIF(求和区域,条件区域1,条件1,条件区域2,条件2...)
6、避开错误值求和。
如下图:计算1-5月份总销售量。C12单元格:=SUMIF(A2:E10,"<9.9E+307")
应用函数SUMIF设置条件完成,其中设置条件"<9.9E+307",其含义是,9.9*10^307,即9.9乘10的307次方,9.9E+307是一个极大的数值,根据EXCEL的通过规则:数值<文本
7、通配符求和
如下图:计算1月份包含电脑的的总销量。G2单元格:=SUMIF(C2:C18,"*电脑",D2:D18)
应用函数SUMIF设置条件完成,通配符*代表任何字符或字符串,只要总字符不超过255个,设置条件"*电脑",或“*电脑*”,在选定区域中查找包含“电脑”的所有商品,在计算区域求和。
8、快速累计求和
利用“$”对求和区域首个单元格进行绝对引用,马上就能对一列数值快速累计求和,如下图,C2单元格:=SUM($B$2:B2),向下复制即可。
9、求和的快捷键:"Alt + =",选中要求和的区域和拟计算结果的空白区域,一键即可在空白区域产生求和结果
高级篇
10、求和之王:SUMPRODUCT
SUMPRODUCT函数跟SUM函数的数组公式条件求和语法一样。
=SUMPRODUCT((条件区域=条件)*求和区域)
如上面案例,计算1月份冰箱的销售量。K2单元格:=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18),也可以写成:=SUMPRODUCT((D2:D18="冰箱")*E2:E18)
如上面案例,计算销售一部1月份相机销售量。L2单元格:=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机"),也可写成:=SUMPRODUCT((A2:A18="销售一部")*(D2:D18="相机")*E2:E18)
下面,举个复杂一点点的例子,昨天,世界杯开始,首场比赛,俄罗斯队5:0大胜沙特队,拟统计本次世界杯,各小组球队进、失球队总数。
以A组为例,第一场有真实比分,其余为模拟分。就可以用SUMPRODUCT计算,进球数:J2单元格:=SUMPRODUCT(($B$2:$B$7=$I3)*($C$2:$C$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($E$2:$E$7));失球数:K2单元格:=SUMPRODUCT(($B$2:$B$7=$I3)*($E$2:$E$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($C$2:$C$7))
SUMPRODUCT与其他函数结合还可以解决你意想不到的求和需求,以后我们会专门进行分享。请关注后,期待好文。
每天学习一点点,每天进步一点点,积硅步,必能致千里,得大成。
- ?
excel如何实现跨工作表求和
香岚
展开
excel求和很简单,我们大家都会。但是excel求和仅仅是同工作表求和,如果我们需要跨工作表求和,又该怎么实现呢?这时候就需要我们运用到公式。下面我就给大家一步一步慢慢解说,看过之后,你一定也会觉得非常简单。
为了方便大家学习本经验,我特地制作了几个简单的表,这个表总共是有四个工作表:一个是汇总表,另外三个是单独的个人表。
通过上面几个工作表我们可以看到,张三、李四、王五的成绩都是已知的,而汇总表却是空白的,那么,我们应该怎么将他们的成绩汇总到汇总表内呢。
首先,我们先来填汇总表中所有人第一学期的语文成绩
这里需要一个公式,就是跨工作表的公式:
=(sheet1!X1+sheet2!X1+sheet3!X1)
我给大家解说一下这个公式--sheet代表工作表;X代表工作表内纵坐标的英文字母;1则代表工作表内横坐标的数字。
我们现在将公式带入:
很简单的,我们得到了第一个数值,也就是汇总表内所有人的第一学期的语文成绩
用同样的方法,我们可以得到第一学期所有人的各科成绩。
那么合计又怎么填呢?没错,用求和。之前也写过关于求和的经验,有不明白的大家也可以参考一下噢
总计也是用同样的方法~
就这样,我们填完了所有汇总表的格子啦。是不是很容易呢~
- ?
怎样设置两张不同EXCEL表格上数据的求和公式?
阮若剑
展开
1.首先在桌面上新建两张EXCEL表格,表格A和表格B,如图所示:
2.打开两张表格,并在表格A内录入“张三”的语文和数学成绩,在表格B内录入“张三”的物理和化学成绩,如图所示:
3.接下来由小如何求“张三”各科成绩的合计值。
首先输入“=”,然后将表A中的语文和数学成绩选中,如图所示:
4.语文和数学成绩选中后,在后面录入符号“+”,如图所示:
5.然后打开表B,将物理和化学成绩选中,如图所示:
6.最后直接按键盘上的“回车”键,EXCEL就会立刻跳转到表A,计算得出“张三”的各科成绩的合计值了,如图所示:
(本文内容由百度知道网友茗童贡献)
- ?
实际工作中的跨多张EXCEL表格统计COUNT&SUM函数
笑翠
展开
在我的实际工作中,需要对22名员工平时的工作表现做一份考核表,本次具体会分享如下几个Excel操作的小细节,希望对有计算需求的朋友带来方便。
1,这项“团队精神”评判是泛范围的(即评论时可评某人,也可以选择不评),这样一来,参评的人数并不相同,我们就无法简单粗暴地按收到多张评测纸就除以几来计算其平均分值,此时我们可以借助于=SUM('1:22'!C6)进行跨多张表格求和,用=COUNT('1:22'!$C6)来统计参评个数,
如下图共有22人参与评论(就有22份格式一致但内容不同的表)。
在汇总表格的C6储存格输入=SUM('1:22'!C6),这是将表1--表22的C6格求和,然后拖拽此公式向右向入填充。操作细节:输入=SUM(之后点住名字表1,然后按住SHIFT键不放,再点到名字表-22,以此操作进行区域选择,到时步时公式显示为:=SUM('1:22'!,此时再在选区域中的任意一张中点选C6格,再“)”完结公式;
接下来是统计每项评的人数,由于每评一个人必须把10项全评完,这样我们就可以直接以C列为依据进行参评人数的统计了。公式为在N6中输入=COUNT('1:22'!$C6),表式统计1-22份表中C6格为非空格的数量;
对22份格式一致的参评表格进行统计
接下来再奉献一个非常非常实用的复制+选择性粘帐贴组合,绝对实用:大家再看上图,每项原本最高分都10,可现在全部都是多人之和,也就是与表格表现方式不符。此时我们可以利用shift键(或鼠标拖拉)选择区域到N6:N27------->然后按右键点开“选择性粘贴”--->点选“公式”---->“除”---确认。(具体见下短视频)
当然,我们也可以使用同样的方式直接=AVERAGE('1:22'!C6)来进行求平均,此份主要讲了几个平时工作中比较实用的EXCEL小技巧,希望有用哈!
- ?
Excel求和出错是怎么回事
郁芊
展开
使用Excel/WPS表格计算很方便,可有些时候连最简单的求和运算也会算错,就像下表,得出了“1.0+1.0=2.1”,别不相信你的眼睛!
是数值格式不对吗?是公式输入错了吗?
都不是啊!可结果却和期望相差了0.1、0.01、0.001!
很多做财务和会计的朋友会遇到这个问题。虽然结果看似相差不大,但是就像上面的表格,结账时相差0.1,这可是1000元啊。假如数字的单位变成了“百万”、“千万”,那可是不小的一笔钱啊。
细思极恐!
为什么求和会出错
既然单元格格式和公式都没有错,那我们有理由相信Excel没有计算错,错误只可能出现在原始数据和小数位数上。我们分别点击两个金额的单元格,在输入栏上可以看到原始的数据是“1.0345”、“1.0435”,虽然原始数据显示为小数点后保留一位,但Excel计算时还是用“1.0345+1.0435”,得到结果2.0780,保留一位小数点后得到2.1。
求和出错怎么办
终于找到了原因,我们可以用ROUND函数来处理。输入公式“=ROUND(C2,1)”\“=ROUND(C3,1)”,然后再用SUM函数求和就没有问题啦。
ROUND函数怎么用
语法:ROUND(数值,保留的小数位数)功能:按照指定的小数位数对数值进行四舍五入。
ROUND函数有两个参数,第一个表示要处理的数据,第二个表示要保留的数值位数/小数位数,它有3种情况。
第二个参数为正数,如2,表示小数点后保留两位数字,也就是对小数点后的第3位进行四舍五入。第二个参数为0,表示去掉小数点后所有的数字,结果取整,也就是对小数点后第1位数字进行四舍五入。第二个参数为负数,表示对小数点左边的数字进行四舍五入。如“=ROUND(15.5356,-1)”表示对小数点左侧第1位数字“5”四舍五入,得到20;“=ROUND(1.5356,-1)”表示对小数点左侧第1位数字“1”四舍五入,结果得到0。
每一个都要添加ROUND函数好麻烦怎么办
网上有些朋友给出了解决方案是:设置工作簿选项为“以显示精度为准”/“将精度设为所显示的精度”。
不过,这样做,会导致原始数据自动被四舍五入!原本的“1.0345”、“1.0435”都变成了“1.0”。
这等于丢失了原始数据!
所以还是建议使用ROUND函数。当数据很多时,可以先输入前面的几个公式,然后选中单元格,双击一下,即可批量输入公式。
如果表格中的数据很多,可以另外再做一个修正表,双击快速填充公式,还是很好用的。
相关阅读:《WPS Excel 日期变数字、变“ ”号怎么办》。
谢谢阅读,每天学一点,省下时间充实自己。欢迎点赞、评论、关注和点击头像。
- ?
Excel如何跨表格求和
浑鞅
展开
日常我们工作中经常会需要汇总不同地区发来的表格,或者是需要把不同小组发来的数值叠加到一块计算一个汇总值,除了拿计算器一个一个敲之外,下面分享两种方法:
一、直接用华中B2+华南B2+。。。然后左右拖拉即可。
此方法适用于表格相对较少,简单好记不需要技巧
excel
二、运用函数
=SUM(华东:华北!B2),其中华东是第一个sheet,华北是最后一个sheet,然后向右向下拖动即可。
此方法适合于sheet较多的情况,直接一个公式就行了,不用一个一个去加还容易遗漏某个sheet,但是就是需要去记住此公式了。
Excel的公式真的太多了,长期不用就忘了,用的时候又想不起来了,小编的办法就是收藏起来说不定哪天就用到了(#^.^#)
- ?
EXCEL多条件求和、跨表多条件求和函数DSUM,让你求和效率更高!
Cornelius
展开
Excel求和函数,除了Sum、Sumif和Sumifs以外,你还用过其它的函数吗?今天分享一个简单实用又高效的数据库函数DSUM,它集「查找」和「求和」功能为一身,能多条件求和,还能跨表多条件求和,让你一看到就会爱上它!
一、函数解析
DSUM函数:将数据库中符合条件记录的字段列中的数字求和。使用它可以对数据进行多条件累加,这种方式可以很方便地修改求和的条件。
DSUM有3个参数:Dsum(数据区域,求和的列数,条件区域)
① 数据区域:一组数据列表,即需要对该组数据列表中的某些数据进行计算。
② 列数:需要求和数据所在列数(也可以是列标题)比如:如果该参数为“3”,则表示需要计算的数据在参数①第3列中,如果该参数为“销量”,则表示需要计算的数据是参数①中的“销量”那一列。
③ 条件区域:由标题行和条件构成的多行区域(条件为公式时,若使用函数标题应为空)。
二、多条件求和案例
如下图所示,计算侯采和马来军的总销量。
在F2单元格中,输入公式:=DSUM(A1:E13,E1,G1:G3)
其中A1:E13是数据区域,E1是统计列“销售量“,G1:G3是条件区域,即计算侯采和马来军2人的销售量。
三、模糊统计案例
统计销售一科以打印机开头的所有产品销量和销售二科的台式机销售之和。
在I2单元格输入公式=DSUM(A1:E13,E1,G1:H3)
DSUM()函数中的判定条件,支持使用通配符 “*” 和 “?”,如下图所示,H2在单元格中使用了通配符“*“表示包含以打印机开头的A、B型号,但不包括D13的激光打印机,如果要包括则前面要加通配符。案例中,同一行的,销售二科和台式机必须同时满足,即并条件,而不同行的销售一科的打印机和销售二科的台式机是或条件,写在不同的行。
四、符合时间条件的求和案例
统计销售日期在2018-1-7至2018-1-14之间的销售量。
I2单元格输入公式:=DSUM(A1:E13,E1,G1:H2),销售日期是一个区间,可以在一行用两个销售日期的条件。一个是大于2018-1-6,另一个是小于2018-1-15。
五、条件为公式时,函数标题应为空
统计销售一科销量小于平均销量人员的销量总和,销量小于平均销量必须用到公式=E2
H2单元格输入公式=E2
I2单元格输入公式=DSUM(A1:E13,E1,G1:H2)
六、多数据批量汇总案例
分别计算销售一科、销售二科和销售三科的销量。
H2单元格输入公式=DSUM($A$1:$E$13,$E$1,$G$1:G2)-SUM($H$1:H1)
1、由于公式要往下填充,所以数据区域和列都用了绝对引用
2、条件区域是一个从G1开始的活动区域,所以G1是绝对引用,但G2是相对引用
3、因为不同的行是“或条件”所以在统计销售二科销售总量时,结果会包含销售一科的销售总量,需要减去销售一科的销量,同样的道理,统计销售三科销量时要减去销售一科和销售二科的销售和。
七、跨工作表统计案例
如下图所示,有表1、表2和表3三个工作表,要统计三个表中,张1、张2、张3的销量和。
C2单元格输入公式=
SUM(DSUM(INDIRECT({"表1";"表2";"表3"}&"!A1:D11"),4,A$1:B2))-SUM(C$1:C1)
1、数据区域,跨表引用了3个工作表,所以用工作表引用函数INDIRECT。
2、列数,第4列“销量”。
3、条件区域是从A1开始的活动区域,所以A1是绝对引用,但B2是相对引用。
4、由于是的跨表引用,因此dsum返回的是数组,不是单值,因此要外套sum。
5、因为不同的行是“或条件”所以在统计张2第一周的销量时,结果会包含张1第一周的销量,需要减去张1第一周销量,同样的道理,统计张3第一周的销售时要减去张1和张2第一周的销售和。
温馨提示:
1、条件区域列标题内容要与数据区域一致,比如数据区域列标题是“销售产品”,那你要统计某一产品时,条件列标题也要是“销售产品”如果你改为产品,统计会出错
2、切记“并条件”需要横着写,即写在同一行,“或条件”需要竖着写,即写在不同行。
3、条件为公式时,若使用函数标题应为空。
我是EXCEL学习微课堂,如果我的分享对您有帮助,欢迎点赞、收藏、评论、转发!更多的EXCEL技能,可以关注百家号“EXCEL学习微课堂”。
- ?
简单点,跨表求和的方式简单点
杰弗瑞
展开
在实际工作中,我们会遇到跨表求和的情况,需要汇总连续多个表中相同区域的数据 (所有的表结构相同,项目不存在重复情况),如何跨表快速汇总呢?
如下所示:在1~12月每个工作表中都存在相同的表格形式,现在需要在汇总表中计算1~12月所有品牌手机的销售数量和金额
方法其实很简单的,我们先看销量计算的公式,销售额同理
方法一:
输入公式=sum('*'!E6)
技能get: * 为通配符,代表任意多个字符的意思,在这里代表除了汇总表之外的其他所有表,!表示工作表的意思,输入此公式后会自动转化为=sum('1月:12月'!E6)
方法二:
先输入=sum( , 然后鼠标点住1月工作表标签,按住shift,再点击最后一个工作表标签(12月),然后鼠标选择E6单元格,按enter结束公式输入,公式显示结果=sum('1月:12月'!E6)
什么情况下用这种方式呢,如果工作簿中除了需要汇总的表格之外,还有其他的不需要汇总的表格,为了防止使用通配符造成不需要汇总的表格也参与汇总,可以使用这种方式
需要注意的是以上两种方法针对的表格的结构上的要求:所有需要汇总的表结构相同,项目不存在重复情况,小伙伴们,如果你学会了,现在就尝试一下吧
- ?
Excel跨工作表、跨行与跨列求和
莫问安
展开
在 Excel 中,除在同一工作表求和外,还可以在不同工作表与不同 Excel 文档的工作之间求和;如果只在不同的工作表求和,只需标明工作表名称,如果在不同 Excel 文档之间求和,还要标明文档名称。另外,还可以跨行与跨列求和,它们可以用SUMPRODUCT函数。以下就是Excel跨工作表、跨行与跨列求和的具体操作方法,实例中操作所用版本均为 Excel 2016。
一、Excel跨表求和
1、假如要求一个服装表中 1月、2月和3月的销量之和。选中 E2 单元格,把公式 =SUM('1月'!D2:D6,'2月'!D2:D6,'3月'!D2:D6) 复制到 E2,按回车,则求出一件服装的 1 至 3 月销量之和;把鼠标移到 E2 右下角的单元格填充柄上,按住左键并往下拖,则求出其它服装的 1 至 3 月的销量之和;操作过程步骤,如图1所示:
图12、公式说明。公式中 '1月' 是工作表(即 Sheet)的名称,工作表与单元格之间用感叹号 ! 隔开,“2月和3月”的求和单元格的引用方法也一样。
3、跨文档求和。
A、如果待求和的单元格分布在两个或多个 Excel 文档中,则还需要在表格名称的前面加上文档名称。假如要对“服装销量1.xlsx”和“服装销量2.xlsx”两个文档的“1月”工作表的服装销量求和,操作过程步骤,如图2所示:
图2B、操作过程步骤说明:在“服装销量1”窗口,在 E2 单元格输入公式 =SUM(),把光标定位在括号中,单击一下工作表“1月”,框选 D2:D6,公式变为 =SUM('1月'!D2:D6),在 D6 后输入逗号(,);切换到“服装销量2”窗口,单击一下工作表“1月”,同样框选 D2:D6,切换回“服装销量1”窗口,公式变为 =SUM('[服装销量1.xlsx]1月'!D2:D6,'[服装销量2.xlsx]1月'!D2:D6),即在“服装销量2”文档中选择的单元格自动填到了公式中;按回车,则计算出一件服装的销量之和,同样用往下拖的方法求出其它服装的销量之和。
C、如果要求“服装销量1.xlsx”和“服装销量2.xlsx”1月与2月的服装销量之和,公式可以这样写:=SUM('1月'!D2:D6,'2月'!D2:D6,'[服装销量2.xlsx]1月'!$D$2:$D$6,'[服装销量2.xlsx]2月'!$D$2:$D$6),如图3所示:
图3按回车,则求出两个文档的“1月和2月”的服装销量之和,如图4所示:
图4二、Excel跨行求和
1、假如要求偶数行的和。把公式 =SUMPRODUCT((MOD(ROW($2:$10),2)+1=ROW(A1))*F$2:F$10) 复制到 F11 单元格,按回车,则求出第 2、4、6、8 和 10 行的和,操作过程步骤,如图5所示:
2、公式说明:
A、选中公式所在单元格 F11,按住 Alt 键,按一次 M,按一次 V,打开“公式求值”窗口,单击“求值”一直到求出最终的结果,如图6所示:
图6B、公式中 ROW($2:$10) 返回一个 2 到 10 的数组,即{2;3;4;5;6;7;8;9;10};MOD(ROW($2:$10),2) 是把数组中的每个元素与 2 求模;然后再把求得的数组每个元素都加 1;再判断数组的每个元素是否等于 ROW(A1)(即 1,ROW(A1) 是取 A1 行号),如果等于,返回 True,否则返回 False。
C、经过上述计算,求得数组{True;False;True;False;True;False;True;False;True},数组中为 True 的用 F 列中的数字代替,为 False 的用 0 代替,即得数组{329;0;874;0;982;0;897;0;563},最后用 SUMPRODUCT函数求出和。
3、如果要求第 3、6、9 行的求,公式可以这样写:=SUMPRODUCT((MOD(ROW($3:$9),3)+1=ROW(A1))*F$3:F$9),如图7所示:
图7按回车,计算结果,如图8所示:
图8三、Excel跨列求和
1、假如要求偶数列的和。把公式 =SUMPRODUCT((MOD(COLUMN($B:$H),2)+1=COLUMN(A1))*$B2:$H2) 复制到 I2 单元格,按回车,则求出第2行指定列的和,同样用往下拖的方法求出其它行指定列的和,操作过程步骤,如图9所示:
图92、公式说明:公式与求偶数行的和一样,要注意的是:把对行的绝对引用改为对列的绝对引用,即把 F$3:F$9 改为 $B2:$H2,$ 由在数字前改为在字母前。