单簿合并多表,起始招数分两派 - <直接查询> 和 <建立表后在查询>
********一种是直接从工作簿建立查询************
1.不建立表,直接新建空白查询 - 导入工作簿 - 完成合并
数据/查询/文件/从工作簿
2.查询界面打开, 如要合并全部表, 直接 右键选编辑(Edit).
若是只合并若干表,则勾选 多选(Select multiple items),再勾选待合并表后选编辑(Edit)
3. 编辑界面打开后, 在右侧的步骤点击源(Source)
再转到公示栏, 先在 = 号后添加 Table.Combine( 然后在结尾处添加 )[Data] 完成后回车
* = #shared 可查询M用法
4.现在只需要做些整理,就能就可以上传合并后的表去Excel里面了
转换/使用第一行做标题
点击筛选,反选 bb, 只保留数值
5, Home 下面选 关闭且上传至表(Close & Load)
6. 完成~ *** 支持增加新表后自动加入查询结果.***
***或者用合并多工作薄的方式合并单簿多表
将要合并的工作薄单独存放在一个文件夹中,然后 建立查询: 数据/查询/文件/从文件夹
在打开的查询界面选着 添加自定义列(Add Custom Column), 自定义公示栏中输入 =Excel.Workbook([Content],true) 回车
选中结果中的 Name 和 Custom列, 管理列/删除其他列 , 然后点击Custom列的展开, 接着点 Data 继续展开
得到下表, 删除多余列上传Excel即为所得.
**PS:如果往这个文件夹中添加工作薄, 或者在这个工作薄中添加工作表,都会被自动添加到查询结果.
********另一种是先建立表,在查询************
先建立表:可以手动建表(Ctrl+T/L),或者VBA(文末分享代码) - 纯点击 / 或者 M 代码完成合并
1. 所有需要合并的工作表都分别建立成格式表, (Ctrl + T/L)
Ctrl+F3, 在名称管理器中,查看你所要合并的表, 带特征码重命名之(方便之后合并时做时筛选), 或者将不要合并的表重命名(增加新表时不用特意特征命名,直接刷新)
2. 步骤
-- 2.1数据/从表 建立查询
-- 2.2 右侧步骤/删除其他步骤
-- 2.3 点击 源(Source)
-- 2.4 点击公示栏, 修改公式为:= Excel.CurrentWorkbook() 后回车
3. 点击筛选, 过滤 为 只显示 不包含 "_不合并"的表
4, ,反选下框, 将结果的列名不使用原列名为前缀, 点击 Expand
5. 之后上传excel
6. 完成. *** 支持增加新表后自动加入查询结果.***
添加新表后刷新,自动加入查询结果:
****先建立表: 用M 代码完成合并
1.先将所有待合并表建立成格式表(Table)
2. 数据/ 从表格 建立查询
3. 删除步骤,只保留 源(Source), 选择 合并/追加 (只是为了帮助理解M,可省略步骤)
3. 追加 本身 (只是为了帮助理解M,可省略步骤)
4. 查看/高级编辑 打开后可看见M
此处代码为高级编辑 打开
let
Source = Excel.CurrentWorkbook(){[Name=”tblNorth”]}[Content],
#”Appended Query” = Table.Combine({Source, Source})
in
#”Appended Query”
显然, Table.Combine 语句是把前一句 定义的 源(Source) 进行了自我合并.
于是简单了, let 后面把所有需要合并的表定义加进去, 再用Table.Combine 合并起来就完成了.
**当然, 有新表加入的时候,只能手动编辑了.
此处代码:
let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Source4 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Appended Query" = Table.Combine({Source1,Source2,Source3,Source4})
in
#"Appended Query"
******************VBA建立格式表(Convert Range To Table in all worksheets in one workbook)*************
Sub CreatTableRenameAll() '#1 Creat Table and Rename as sheet for All sheets '
Dim wrksht As Worksheet
Dim objTable As ListObject
Application.ScreenUpdating = False
For i = 2 To 25 '表2到表25,Excel窗口里的排序,不是VBE中顺序
'or ActiveWorkbook.Worksheets.Count ' 或者,最大工作表数
Set sh = ActiveWorkbook.Worksheets(i)
If sh.Index > 0 Then 'Conditions for traversal 'For i = 1 To ActiveWorkbook.Worksheets.Count
Debug.Print sh.Index
TName = sh.Name
sh.Activate
sh.Range("A27:F39").Select '固定区域建立为表
'or sh.range("A1").CurrentRegion.select '或者, A1 所在数据区域建立成表
'ActiveCell.CurrentRegion.Select '或者,当前激活的单元格所在区域建立表
'sh.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "a" 'Creat Table,Rename Table as sheet name '另一种建表方式
Set objTable = sh.ListObjects.Add(xlSrcRange, Selection, , xlYes) 'Creat Table
objTable.Name = TName
objTable.TableStyle = None '表格式为 无格式 可选TalbeStyleLight1 等等
End If
Next
Application.ScreenUpdating = Ture
End Sub
*********VBA取消格式表(Convert Table to Range in all worksheets in one workbook)********
Sub UnlistTableAll() '#1 Convert Table to range for All sheets '多表取消table
Dim wrksht As Worksheet
Dim objListObj As ListObject
Dim rList As Range
Application.ScreenUpdating = False
For i = 1 To ActiveWorkbook.Worksheets.Count
Set wrksht = ActiveWorkbook.Worksheets(i)
Set objListObj = wrksht.ListObjects(1) 'the No.1 Tabel in current worksheet
Set rList = objListObj.Range
'objTable.TableStyle = None '先将表格式设为无, 再转换成区域,可将区域恢复建表前的默认格式. 同时可省略下面语句:重设所有格式
objListObj.Unlist 'Convert Table to range '取消表,转化为区域
With rList 'Reset Table format 重设所有格式. 建表前如有格式被重设.
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
.Borders.LineStyle = xlLineStyleNone
End With
Next
Application.ScreenUpdating = Ture
End Sub
用时2小时.......
