Excel(二)之批量创建工作簿及多表合并

还在用ctrl+c和ctrl+v来搬运数据么？本文分享了excel中五个常用的批量操作技巧，分分钟提高工作效率。

批量创建工作簿

案例一：批量创建如下名称的工作簿

步骤一：选中全部——插入数据透视表——将表名放入"筛选中"

步骤二：数据透视表工具"分析"——选项——显示报表筛选页

步骤三：右键工作簿——选中全部工作表——将表头删除即可

批量修改工作簿名

案例二：批量修改如员工信息表修改为职员信息表
步骤一：将案例一生成的文件(工作簿1.xlsx)保存，修改其文件后缀为rar

步骤二：打开xl文件——将workbook.xml文件复制到桌面——以记事本方式打开

步骤三：快捷键ctrl+H 调出"查找-替换"工作栏。查找内容填入员工，替换内容填入职员，全部替换，保存文件。重新替换原workbook.xml文件

步骤四：修改文件后缀为xlsx，并打开查看

多表合并

案例三：合并多个地区的数据分析岗位招聘信息(EXCEL 2016版)
由于需要使用Power Query功能，低版本需要自行下载插件

步骤一：数据——新建查询——从文件——从工作簿

步骤二：导入数据后，选择多项——转换数据

步骤三：追加查询——将查询追加为新查询——三个或更多表

步骤四：修改表名，关闭并上载至

步骤五：仅创建表链接——右键"合并数据"——加载到——表
合并完成：

多表拆分

案例四：将如下招聘信息表按照地区进行拆分

步骤：开发工具——Visual Basic——插入——模块
VBA代码如下：

Sub 多表拆分()

    Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String

    Application.ScreenUpdating = False

    With Worksheets("Sheet1")    ##sheet1为工作簿名

     iRow = .Range("A4869").End(xlUp).Row  #数字只要大于数据表的行数即可

     iCol = .Range("IV1").End(xlToLeft).Column  #工作表的行数若从2开始，则修改为"IV2"

     t = 2   ##拆分列

        For i = 2 To iRow  #从第二行开始拆分

            str = .Cells(i, t).Value  #获取单元格(i,t)的值作为工作簿名

            On Error Resume Next

            Set sh = Worksheets(str) #创建工作簿

            If Err.Number <> 0 Then  #若不存在这个工作表，则添加一个命名

                Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))

                sh.Name = str

            End If

           

            sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value   #获取工作表标题

            iNum = sh.Range("A" & Rows.Count).End(xlUp).Row

            sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value  #新表中粘贴数据

            Next i

     End With

Application.ScreenUpdating = True

End Sub

拆分结果如下：

多文件合并

案例五：合并如下五个城市的招聘信息xlsx文件

步骤一：数据——新建查询——从文件——从文件夹——转换数据

步骤二：选中Content列——删除其他列

步骤三：添加列——自定义列——自定义公式输入=Excel.Workbook([Content],true)(大小写不能错)

步骤四：点击自定义旁边的<>按钮——勾选"Data"，并取消勾选"使用原始列名作为前缀"

步骤五：再次点击<>按钮，取消勾选"使用原始列名作为前缀"，删除Content列，关闭并上传

合并结果如下：