一个工作簿包含多少个工作表(你会跨工作簿统计吗)

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(1)

大多数情况下,我们对表格统计都会在同一张工作簿进行,这样操作起来非常方便,不过有时也会进行跨工作簿统计。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(2)

跨工作簿条件求和最常见的两个问题:

01 跨工作簿区域应该如何写?

02 关闭工作簿后,统计出来的结果变成错误值怎么回事?

条件求和,首先想到的是用SUMIF函数进行统计。

函数语法:

=SUMIF(条件区域,条件,求和区域)

其实跨工作簿的情况下,区域的选取跟在同一个工作簿一样,都是用鼠标选取,而不是手写。有一点必须要记住:两个工作簿必须同时打开。

鼠标选取区域详见动画

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(3)

最终公式为:

=SUMIF([跨工作簿统计1.xlsx]Sheet1!$B:$B,A2,[跨工作簿统计1.xlsx]Sheet1!$D:$D)

一旦将跨工作簿统计1.xlsx关闭,修改统计月份,金额就变成错误值。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(4)

在Excel中,并不是所有函数都支持跨工作簿,如SUMIF、COUNTIF函数就不支持,而VLOOKUP、SUMPRODUCT函数就支持。这里可以借助SUMPRODUCT函数实现跨工作簿统计。

函数语法:

=SUMPRODUCT((条件区域=条件)*求和区域)

我们重新看一下出错的单元格,公式变成:

=SUMIF('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B:$B,A2,'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D:$D)

C:\Users\chenxilu\Desktop这个是路径,意思就是说这个表格存在卢子电脑的桌面。

[跨工作簿统计1.xlsx]Sheet1这个是工作簿名称跟工作表名称。

不要看公式很长,其实拆分开真的没什么,都是很简单的东西。

SUMPRODUCT函数不支持引用整列,这里只要将原来的区域改小,稍作变动就完成了最终的统计。

=SUMPRODUCT(('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B$2:$B$100=A2)*'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D$2:$D$100)

这样即使工作簿不打开的情况下,也能正确统计。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(5)

SUMPRODUCT函数也可以换成SUM函数,不过需要按Ctrl Shift Enter三键结束。

=SUM(('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B$2:$B$100=A2)*'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D$2:$D$100)

这2个函数90%的情况下可以互相代替,用SUMPRODUCT函数的好处就是支持数组公式,不用按三键。

再说一个特殊案例,就是在输入数据的时候,可能会中间出入文本,多敲个空格之类的,如果直接求和会出错。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(6)

用*这种方法,文本*数字就是错误值,不管用SUMPRODUCT函数还是SUM函数都无法避免出错。

不过SUMPRODUCT函数还隐藏了另外一种用法,参数用,(逗号)隔开,可以将文本当做0处理。

=SUMPRODUCT(--(MONTH($A$2:$A$26)=F2),$D$2:$D$26)

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(7)

语法:

=SUMPRODUCT(数字区域1,数字区域2,数字区域3)

这里的(MONTH($A$2:$A$26)=F2)得到的是逻辑值,并不是数字,所以用--转换成数字1、0,从而可以正确求和。

关于条件计数,可以看看COUNTIF与SUMPRODUCT这俩函数过招!

1.下面请看第一场比赛:如何统计值班经理的值班次数?

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(8)

COUNTIF函数首先应战,在H2单元格输入公式,并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函数语法:

=COUNTIF(条件区域,条件)

SUMPRODUCT函数也不甘示弱,在I2单元格输入公式,并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*1)

SUMPRODUCT函数单条件计数语法:

=SUMPRODUCT((条件1)*1)

或者

=SUMPRODUCT(--(条件1))

2.第一场比赛可谓势均力敌,不分胜负。下面请看第二场比赛:统计值班经理在中午时间段的值班次数。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(9)

两个条件?COUNTIF函数顿时傻眼了,多条件计数是COUNTIF函数心里永远的痛。然而,SUMPRODUCT函数却气定神闲,在H2单元格输入公式,并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*($B$2:$B$10=$H$1))

SUMPRODUCT函数多条件计数语法:

=SUMPRODUCT((条件1)*(条件2)*(条件n))

“打虎亲兄弟,上阵父子兵”,看到兄弟COUNTIF函数有难,擅长多条件计数的COUNTIFS函数果断出手了,在I2单元格输入公式,并向下填充。

=COUNTIFS(A:A,G2,B:B,$H$1)

COUNTIFS函数语法:

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

3.第二场比赛的结果大家有目共睹,胜利属于SUMPRODUCT函数。下面请看第三场比赛:值班经理都用了一个字作为自己的简称,如何根据简称统计值班次数?

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(10)

SUMPRODUCT函数没有了之前的淡定从容,陷入了沉思中。而COUNTIF函数却露出了久违的笑容,它拿出了自己的绝活,在H2单元格输入公式,并向下填充。

=COUNTIF(A:A,"*"&G2&"*")

在这里,“*”代表通配符,表示任意一个或者多个字符。在Excel函数中,能与通配符配合使用的函数并不多,COUNTIF函数是其中的一个,当然也包括了COUNTIFS函数,SUMIF函数,SUMIFS函数,VLOOKUP函数,MATCH函数等等。

SUMPRODUCT函数想破了脑袋,借助其他函数,终于也统计出来了。

=SUMPRODUCT(--ISNUMBER(FIND(G2,$A$2:$A$10)))

这个公式比较复杂,下面我们按步骤来分析。

Step 01 先看最里层的FIND函数, FIND函数的语法:

=FIND(查找的字符,查找的地方)

在I2,I3单元格分别输入公式:

=FIND("风","风清扬")

=FIND("风","东方不败")

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(11)

在第一个公式中,因为字符“风”在字符串“风清扬”的第一个位置,所以结果返回1。而第二个公式中,因为字符“风”没有在字符串“东方不败”中,所以结果返回错误值。

Step 02 熟悉了FIND函数的基本运用后,我们在I2单元格输入公式:

=FIND(G2,$A$2:$A$10)

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(12)

我们知道,在“A2:A10”区域中,存在了两个“风清扬”,按道理,字符“风”是能查找到的,应该返回数字才对啊,但是却返回错误值,这究竟是为什么呢?

FIND函数的第二个参数是一个区域,所以返回的结果是若干个数据,多个数据放在一个单元格中,当然会出错了。这个时候,我们需要借助一个神器:独孤九剑,也就是F9键。选择公式所在单元格,点击编辑栏,按F9键。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(13)

Step 03 带有红色方框的数字个数就代表了该值班经理的值班次数。那么怎么统计数字的个数呢?可以使用ISNUMBER函数,如果是数字就返回TRUE,否则就返回FALSE。

=ISNUMBER(FIND(G2,$A$2:$A$10))

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(14)

Step 04 再结合SUMPRODUCT函数,结果便出来了,公式在上面已经给出。

第三场比赛,虽然SUMPRODUCT函数最后完成了任务,但评委的眼睛是雪亮的,这一次,评委把票投给了COUNTIF函数。

比赛的结果并不重要,重要的是,在什么时候该使用什么函数,怎么简单怎么来,作为这次比赛的吃瓜观众,你们说呢?

本文来源:Excel不加班,税小课整理发布,文章版权归原作者所有,如有不妥,请联系删除。

一个工作簿包含多少个工作表(你会跨工作簿统计吗)(15)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页