同一工作薄多表汇总问题

lwztyz

我第一次用透视表及合并计算，我要的是多表汇总求和，结果成了计数，表格形式和内容都不是我要的，该怎么改？求教各位。

zhaogang1960

由于“线路”数字中有文本，使用导入数据会把文本当作空白，使用vba吧：
Sub Macro1() '每个表“线路”的第一个单元格加上一个撇号“'”，否则会把“19红”、“19兰”当作空白
'引用Microsoft ActiveX Data Objects 2.x Library
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL$, s, arr(1 To 6), i&, j&, d As Object, t
    Set d = CreateObject("scripting.dictionary")
    For i = 1 To 6
        With Sheets(i)
            d(.Name) = .[c2].Value
            If i = 1 Then
                s = .[a2] & "," & .[b2] & ",sum(" & .[c2] & ") as " & .[c2]
            Else
                s = s & ",sum(" & .[c2] & ") as " & .[c2]
            End If
        End With
    Next
    t = d.items
    For i = 1 To 6
        With Sheets(i)
            For j = 0 To d.Count - 1
                If d(.Name) = t(j) Then '该工作表存在该字段
                    arr(i) = arr(i) & "," & t(j)
                Else
                    arr(i) = arr(i) & "," & "0 as " & t(j)   '该工作表不存在该字段要添加 0 as 字段
                End If
            Next
            arr(i) = "select 线路,车号," & Mid(arr(i), 2) & " from [" & .Name & "$a2:c65536] where 车号<>'合计' and 车号 is not null "
        End With
    Next
    SQL = Join(arr, " UNION ALL ")
    SQL = "select " & s & " from (" & SQL & ") group by 线路,车号"
    cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;imex=1';Data Source=" & ThisWorkbook.FullName
    rs.Open SQL, cnn, 1, 3
    ActiveSheet.UsedRange.ClearContents
    For i = 1 To rs.Fields.Count
        Cells(1, i) = rs.Fields(i - 1).Name
    Next
    Cells(1, i) = "合计"
    [a2].CopyFromRecordset rs
    Cells(2, i).Resize(rs.RecordCount) = "=SUM(RC[-6]:RC[-1])"
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

zhaogang1960

这是附件：
合并计算及透视表问题.rar (41.75 KB, 下载次数: 88)

2011-2-5 21:39 上传

点击文件名下载附件

亡者天下

不必用要VBA

用SQL语句合并即可！

zhaogang1960

原帖由 亡者天下 于 2011-2-5 22:54 发表
不必用要VBA

用SQL语句合并即可！

请教什么处理1楼附件中第一列“线路”字段数字加文本（19红，19兰），文本被当作空白，我是解决不了这个问题才用vba的
vba中前几个中有一个是文本(数字的加上一个撇号)，Extended Properties='Excel 8.0;imex=1'就可以解决此问题

[ 本帖最后由 zhaogang1960 于 2011-2-5 23:10 编辑 ]

lwztyz

谢谢楼上朋友的回复，我从未用过VBA，要学习了。我打开工作薄后出现，“由于安全级被设高，并且没有附加文字签字可信任证书，因此这些宏被禁用，若要运行这些宏，请将安全级别设置更低级别。”，我用的是EXCEL2003，怎么解决这个问题？

zhaogang1960

原帖由 lwztyz 于 2011-2-6 16:46 发表
谢谢楼上朋友的回复，我从未用过VBA，要学习了。我打开工作薄后出现，“由于安全级被设高，并且没有附加文字签字可信任证书，因此这些宏被禁用，若要运行这些宏，请将安全级别设置更低级别。”，我用的是EXCEL2003， ...

工具——选项——安全性——宏安全性——选择“中”——确定
打开陌生的带有宏的工作簿时，先选择“禁用宏”，打开后检查有没有恶意代码，如果没有可以关闭后再打开时选择“启用宏”

lwztyz

VBA真是太好用了，谢谢楼上朋友的回复。