sql 聚合函数 多行数据合并一行_Excel多表数据合并解法总结（函数、VBA、PQ、SQL...）...

每天一篇Excel技术图文 微信公众号：Excel星球 NO.673-多表数据查询 作者：看见星光  微博：EXCELers / 知识星球：Excel

HI~大家好，我是星光。今天给大家分享一下Excel合并多工作表数据的多种方法，涵盖了函数、VBA、PQ、SQL等……。

打个响指，举个例子。

如上图所示▲，一张工作簿包含了多张工作表，现在需要将一部门、二部门、三部门、四部门和后勤部等5张工作表的数据合并到一张表中......

01 | 函数解

正像我们在INDIRECT函数教程里所讲的……

不相信函数也可以合并多表数据？其实是可以的，只是相比VBA等方式，效率不高，操作难度却很高，这就好比星光这家伙，长的不帅，却总爱照镜子，所以通常只建议私下里没人时摆酷，不建议在工作中使用。

A1单元格输入公式向下复制填充
代码如看不全 可以左右拖动..▼

=INDIRECT(LOOKUP(ROW(A1)-1,MMULT(N(ROW($1:$5)>COLUMN(A:E)),COUNTIF(INDIRECT({"一部门";"二部门";"三部门";"四部门";"后勤部"}&"!a:a"),"<>")),{"一部门";"二部门";"三部门";"四部门";"后勤部"})&"!R"&ROW(A1)-LOOKUP(ROW(A1)-1,MMULT(N(ROW($1:$5)>COLUMN(A:E)),COUNTIF(INDIRECT({"一部门";"二部门";"三部门";"四部门";"后勤部"}&"!a:a"),"<>")))&"C",0)&""

可参考函数教程第39章：说来你不信，INDIRECT函数其实是个快递员 02 | VBA

by：Excel星球姐妹

代码解析见注释
代码如看不全，可以左右拖动..▼

Sub ByVBA()    Dim strFindSht As String, sht As Worksheet, k As Long    Dim m As Long, aData    Application.ScreenUpdating = False    strFindSht = ",一部门,二部门,三部门,四部门,后勤部,"    Worksheets("VBA").Select    Cells.ClearContents    Cells.NumberFormatLocal = "@" '设置文本格式，避免工号变形    k = 1    For Each sht In Worksheets '遍历工作表        If InStr(strFindSht, "," & sht.Name & ",") Then '判断工作表名称是否符合条件            k = k + m  '放置数据的开始行            If k = 1 Then                aData = sht.UsedRange            Else                aData = sht.UsedRange.Offset(1) '扣掉标题行            End If            m = UBound(aData) + (k > 1) '注意在VBA中True等于-1            Cells(k, 1).Resize(m) = sht.Name '工作表名称            Cells(k, 2).Resize(m, UBound(aData, 2)) = aData '数据        End If    Next    Cells(1, 1) = "工作表名称"    Application.ScreenUpdating = TrueEnd Sub

03 | PowerQuery
by：Excel星球兄弟 A2单元格使用以下公式获取当前工作簿的完整路径。

=CELL("filename",A1)

将上图所示的表1，以"从表格"的形式加载到PowerQuery， 假设该表格名称为表2 。在高级编辑器编写代码如下。 代码如看不全，可以左右拖动..▼

let    文件路径 = Excel.CurrentWorkbook(){[Name="表2"]}[Content],    整理路径 = Text.Replace(Text.BeforeDelimiter(文件路径[当前文件路径]{0},"]",{0,RelativePosition.FromEnd}),"[",""),    获取文件 =  Excel.Workbook(File.Contents(整理路径), true, true),    目标表 = "一部门,二部门,三部门,四部门,后勤部",    筛选表 = Table.SelectRows(获取文件,each Text.Contains(目标表,[Item])),    合并 = Table.Combine(                List.Transform({0..Table.RowCount(筛选表)-1},                        (a)=>Table.AddColumn(筛选表[Data]{a},"表名",each 筛选表[Name]{a})                            )                        )    //合并并添加工作表名称in    合并

04 | SQL

by：Excel星球大叔大婶

不想多打字，所以使用了VBA+ADO+SQL的形式，借助VBA循环语句简化SQL语句(你这打字不是更多吗？不！除了SQL部分，其它都是SQL系列教程里复制的)

代码如看不全，可以左右拖动..▼

Sub bySQL()    Dim cnADO As Object    Dim rsADO As Object    Dim strSQL As String    Dim i As Long, strShtName, aShtName    Set cnADO = CreateObject("ADODB.Connection")    Set rsADO = CreateObject("ADODB.Recordset")    cnADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _        & "Extended Properties=Excel 12.0;" _        & "Data Source=" & ThisWorkbook.FullName    aShtName = Split("一部门,二部门,三部门,四部门,后勤部", ",")    For Each strShtName In aShtName '多表合并语句        strSQL = strSQL & "SELECT 姓名,工号 ,'" & strShtName & " ' AS 工作表名称 FROM [" & strShtName & "$]  UNION ALL "    Next    Set rsADO = cnADO.Execute(Left(strSQL, Len(strSQL) - 10))    Cells.ClearContents    For i = 0 To rsADO.Fields.Count - 1        Cells(1, i + 1) = rsADO.Fields(i).Name    Next i    Range("a2").CopyFromRecordset rsADO    rsADO.Close    cnADO.Close    Set cnADO = Nothing    Set rsADO = NothingEnd Sub

后台回复SQL即可获取SQL in Excel系列教程。
……

木了，打完收工，下期再见。

文件下载百度网盘..▼ https://pan.baidu.com/s/15mtMdSdZSuAZSU4YU7lYUg
提取码: nfx8 ▎全方位系统学习Excel
推荐加入我的Excel社群 ▎   点击图片可获取相关推文..▼