如你所愿,一篇文章搞定8种Excel多表统计! |
您所在的位置:网站首页 › excel查找全部工作簿 › 如你所愿,一篇文章搞定8种Excel多表统计! |
=SUM(A2&"!C:C") =SUM(A2&"!C:C") 我们正常的引用都是直接引用,如区域C:C。间接引用就是通过第三者才能获得的,如INDIRECT("C:C")。也就是说,只要嵌套个INDIRECT函数就可以。 3.跟上一个例子很像,唯一的差别就是最后多一个合计的实发数量,而每一个表格的行数不确定。现在要获取每个表合计的实发数量。 这种有很多种处理方法,最简单的办法有两种。 01 查找每个表,最后一个数量。 =LOOKUP(9E+307,INDIRECT(A2&"!B:B")) =LOOKUP(9E+307,INDIRECT(A2&"!B:B")) 相关文章《 VLOOKUP函数滚一边去,我才是Excel真正的查找之王! 》 02 统计每个表的数量后再除以2。 =SUM(INDIRECT(A2&"!B:B"))/2 =SUM(INDIRECT(A2&"!B:B"))/2 4.现在换成统计商品的销售量。 明细表 汇总表 难度瞬间就上来了,要结合好多函数。 看起来很复杂,但实际上也不是太难理解。 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 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个工作表,全部需要合并起来。 将代码所在工作簿放在同一个文件夹。 点击头像运行,稍等片刻即可导入,调整列宽即可。 源代码: Sub 合并当前目录下所有工作簿的全部工作表 Dim MyPath, MyName, AWbName Dim Wb As Workbook, WbN As String Dim G As Long, ShRcou As Long, ShCol As Integer Dim Num As Long, WbSht As String Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path MyName = Dir(MyPath & "\" & "*.xls") AWbName = ActiveWorkbook.Name Num = 0 Do While MyName "" If MyName AWbName Then Set Wb = Workbooks.Open(MyPath & "\" & MyName) Num = Num + 1 With Workbooks(1).ActiveSheet For G = 1 To Sheets.Count WbSht = Replace(Left(MyName, Len(MyName) - 4), ".", "") & ";" & Wb.Sheets(G).Name If .UsedRange.Count = 1 Then ShCol = Wb.Sheets(G).UsedRange.Columns.Count ShRcou = Wb.Sheets(G).UsedRange.Rows.Count .Cells(1, ShCol + 1) = "合并名称" .Cells(1, ShCol + 1).Font.Bold = 1 .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, ShCol + 1).Resize(ShRcou - 1) = WbSht Wb.Sheets(G).UsedRange.Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1) Else ShRcou = Wb.Sheets(G).UsedRange.Rows.Count .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, ShCol + 1).Resize(ShRcou - 1) = WbSht Wb.Sheets(G).Cells(2, 1).Resize(ShRcou, ShCol).Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) End If Next WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If MyName = Dir Loop ActiveSheet.UsedRange.Borders.LineStyle = 1 Range("A1").Select Application.ScreenUpdating = True MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示" End Sub Sub 合并当前目录下所有工作簿的全部工作表 Dim MyPath, MyName, AWbName Dim Wb As Workbook, WbN As String Dim G As Long, ShRcou As Long, ShCol As Integer Dim Num As Long, WbSht As String Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path MyName = Dir(MyPath & "\" & "*.xls") AWbName = ActiveWorkbook.Name Num = 0 Do While MyName "" If MyName AWbName Then Set Wb = Workbooks.Open(MyPath & "\" & MyName) Num = Num + 1 With Workbooks(1).ActiveSheet For G = 1 To Sheets.Count WbSht = Replace(Left(MyName, Len(MyName) - 4), ".", "") & ";" & Wb.Sheets(G).Name If .UsedRange.Count = 1 Then ShCol = Wb.Sheets(G).UsedRange.Columns.Count ShRcou = Wb.Sheets(G).UsedRange.Rows.Count .Cells(1, ShCol + 1) = "合并名称" .Cells(1, ShCol + 1).Font.Bold = 1 .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, ShCol + 1).Resize(ShRcou - 1) = WbSht Wb.Sheets(G).UsedRange.Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1) Else ShRcou = Wb.Sheets(G).UsedRange.Rows.Count .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, ShCol + 1).Resize(ShRcou - 1) = WbSht Wb.Sheets(G).Cells(2, 1).Resize(ShRcou, ShCol).Copy .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) End If Next WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If MyName = Dir Loop ActiveSheet.UsedRange.Borders.LineStyle = 1 Range("A1").Select Application.ScreenUpdating = True MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示" End Sub 不懂使用代码的,可以戳文章领取源文件。 《 爽!一键合并多表、多工作簿(附详细说明) 》 《 你要的一键合并所有Excel工作簿,来了! 》 卢子能想到的所有多表都汇总都在这里了。 陪你学Excel,一生够不够? 一次报名成为VIP会员,所有课程永久免费学,永久答疑,仅需 1500 元,待你加入。 报名后加卢子微信 chenxilu2019,发送报名截图邀请进群。 推荐: 多表、多工作簿合并拆分,随心所欲(完善版) 上篇: 太好用了!学了16年Excel,才知道有如此牛逼的新函数 请把「Excel不加班」推荐给你的朋友 别忘了点赞支持卢子哦↓↓↓返回搜狐,查看更多 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |