原创作者: 卢子 转自:Excel不加班
同样一个任务,一个人花了5小时,吃了很多苦,做到60分;另一个人花了5分钟,轻轻松松,做到了80分。你会给第一个人点赞,夸她棒吗?
在职场,过程不重要,结果才是最重要的。
1.格式相同的表格,要在第一个表汇总后面所有表格D列的总量。
格式相同的表,多少个都一样,一个SUM函数就秒杀了。
=SUM('4.1:4.7'!D3)
语法:
SUM(起始表格名称:结束表格名称!单元格)
表格名称没有规律也可以,在有特殊字符的情况下需要加单引号'。
我们知道*是通配符,代表所有的意思,在这里也可以用*来实现统计。
=SUM('*'!D3)
2.同样也是格式相同,但要汇总的形式不一样,要汇总每一个月份的总销量。
这里是用SUM+INDIRECT函数的组合实现。
可能很多人一开始直接就用SUM,可惜得到的是错误值。A2&"!C:C"被当成文本处理,而不是区域。
=SUM(A2&"!C:C")
我们正常的引用都是直接引用,如区域C:C。间接引用就是通过第三者才能获得的,如INDIRECT("C:C")。也就是说,只要嵌套个INDIRECT函数就可以。
3.跟上一个例子很像,唯一的差别就是最后多一个合计的实发数量,而每一个表格的行数不确定。现在要获取每个表合计的实发数量。
这种有很多种处理方法,最简单的办法有两种
01 查找每个表,最后一个数量。
=LOOKUP(9E+307,INDIRECT(A2&"!B:B"))
02 统计每个表的数量后再除以2。
=SUM(INDIRECT(A2&"!B:B"))/2
4.现在换成统计商品的销售量。
明细表
汇总表
难度瞬间就上来了,要结合好多函数。
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!B:B"),A2,INDIRECT(ROW($1:$6)&"月!C:C")))
看起来很复杂,但实际上也不是太难理解。
INDIRECT(ROW($1:$6)&"月!B:B")这部分就是获取1-6月的B列
INDIRECT(ROW($1:$6)&"月!C:C")这部分就是获取1-6月的C列
SUMIF(1-6月的B列,A2,1-6月的C列)就是分别统计这6个表
SUMPRODUCT(SUMIF())就是将最终这6个表进行求和
如果不会也没事,下面还有技巧法。
5.格式相同的多个表格,现在要统计所有人员的工资数据。
Step 01 新建一个空白的汇总表,点击汇总表任意空白单元格,再点击数据→合并计算,这时会弹出合并计算对话框。
Step 02 鼠标引用第一个表的区域,点击添加。
Step 03 重复添加剩下的所有表格,添加完毕以后,勾选首行和最左列,点击确定。
瞬间就统计出来,非常快。
Step 04 统一格式,搞定收工。
6.格式相同的12个表格,现在要统计每个销售人员销售的产品对应的总数量。
关于多表统计,高手可以借助VBA和SQL,普通人也有非常方便的方法。一起来看看超级透视表如何进行统计的?
Step 01 新建一个工作簿,点击数据→新建查询→从文件→从工作簿。
Step 02 浏览到工作簿所在位置,点击导入。
Step 03 选择整个工作簿,点击编辑。
Step 04 删除不需要的列,展开Data获取所有数据。
Step 05 将第一行作用作标题,取消多余的标题筛选,关闭并上载。
Step 06 根据合并后的数据创建数据透视表统计。不包含空格的数据,可以直接用勾选而不需要用拖拉字段,更方便。
Step 07 布局以表格形式显示,并对销售量进行降序。
这样一份年度数据统计表就完成了。
7.格式相同的多个表格,如何快速合并?
复制表头到汇总表,点击二维码运行即可搞定。
源代码:
Sub 合并()
Dim sht As Worksheet, arr
For Each sht In Worksheets
If sht.Name <> "汇总" Then
i = sht.Range("b65536").End(xlUp).Row
j = Sheets("汇总").Range("b65536").End(3).Row + 1
arr = sht.Range("a2:f" & i)
Sheets("汇总").Range("a" & j).Resize(UBound(arr), 6) = arr
End If
Next
End Sub
8.如何合并同一个文件夹内所有工作簿的所有工作表?
同一个文件夹,里面存着N个工作簿。
每一个工作簿,里面有N个工作表,全部需要合并起来。
将代码所在工作簿放在同一个文件夹。