概述
在工作中,我们常会用到将EXCEL拆分/合并为多个工作表/簿,如全国的订单表,需要根据省份列拆分下发至对应的省。那么具体应该如何操作呢?
1. EXCEL拆分成工作表
①插入数据透视表:
单击数据区域任一单元格 -- 插入 -- 数据透视表 -- 查看 单元格区域 是否为全部数据区域(如有遗漏,可手动调整)--确定
②将字段拖入数据透视表区域:
将拆分字段“省份”拖入“筛选框”,“金额”字段拖入“值”,其他展示字段拖入“行”
③调整页面展示样式:
单击筛选页面单元格 -- 设计 -- 报表布局:以表格形式显示、重复所有项目标签 -- 分类汇总:不显示分类汇总
④拆分:
分析 -- 选项 -- 显示报表筛选页 -- 确认
确认后,生成了 以省份命名的各省份的工作表。
⑤调整格式:
点击到最后一个省份的页面 -- 按住 shift 键,点击 第一个省份。
转为值:点击工作表左上角的 箭头 ,实现工作表内容全选 -- ctrl+c 快捷键 复制 -- 右键 选择 “粘贴为数值”
列重命名:将“求和项:金额”列,重命名为“金额”
至此,已全部完成。
2. Excel合并多个工作表
用到的为Excel的Power Query功能,WPS无该功能;
需合并数据源如下所示:(建议存放于单独的一个工作簿中)
新建一个工作薄,作为存放最终合并结果;
①选择数据源:
数据 -- 新建查询 -- 从文件 -- 从Excel工作薄 -- 选择要 处理的 工作簿 -- 导入
②选择要合并的工作表:
勾选 选择多项 -- 勾选excel工作表的 工作表名 -- 转换数据
③数据转换:
主页 -- 追加查询 -- 将查询追加为新查询 -- 选择 三个或更多表 -- 将要合并的表依次添加 -- 确定 -- 双击表名 重命名为 订单汇总表
④加载到Excel:
点击 关闭并上载 -- 保留汇总表 即可
3. EXCEL拆分成多个工作薄
首先,我们需要先按照 EXCEL拆分成多个工作表;(详情见1)
接下来我们进行将多个工作表转换成多个工作表;
代码
Sub 拆分工作薄()
Dim xpath As String
xpath = ActiveWorkbook.Path
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
sht.Copy
ActiveWorkbook.SaveAs Filename:=xpath & "\" & sht.Name & ".xlsx"
ActiveWorkbook.Close
Next
MsgBox "工作薄拆分完毕!"
End Sub
操作
开发工具 -- Visual Basic -- 插入 -- 模块 -- 粘贴代码 -- 运行 (点击上方的小三角)-- 提示(工作薄拆分完毕)-- 完成
结果
拆分后结果如下所示:
4. Excel合并多个工作簿
该部分与2高度重合,下面讲一下不一致的地方:
选择数据源:
数据 -- 新建查询 -- 从文件 -- 从Excel工作薄 -- 选择要 处理的 工作簿 -- 导入
导入数据
勾选 选择多项 -- 勾选excel工作表的 工作表名 -- 加载
此处:有几个工作簿,就需要导入几次(单次只能选择一个工作薄)
Power Query编辑器
查询 -- 编辑
数据转换:
主页 -- 追加查询 -- 将查询追加为新查询 -- 选择 三个或更多表 -- 将要合并的表依次添加 -- 确定 -- 双击表名 重命名为 订单汇总表
④加载到Excel:
点击 关闭并上载 -- 保留汇总表 即可
总计
至此,Excel合并/拆分多个工作表/簿的已完成;
你学会了吗?