VBA 使用Excel数据库：行转列

最近工作中有需求，要从Excel表中提取一些统计信息，因为需求变动可能比较频繁，所以用VBA来做了。调试数据操作部分颇费周折，特记录一下。

1.数据源文件:[D:\4月份统计表.xlsx]：

2.最终的统计表格式：

3.计算规则：

要求：

A. 外观等级A，且WLD在451.5-458之间，且LOP在82-200之间判定为OK，其他为NG。

B.将NG的按照表面等级→WLD→LOP的优先顺序，判定是哪一项引起的不良，更新到数据源的 [筛选结果] 中。

C.在统计表中按照入库日期分组，统计总数量、OK数量、NG数量，以及NG产品中外观等级、WLD、LOP各占多大比例。

4.代码：

Option Explicit

Public Sub DataInquire()
    Dim strCnn As String, sqlStr As String, subSqlStr As String, strSql(1 To 4) As String '判断条件 1=OK,2=外观等级NG,3=WLDNG,4=LOPNG
    Dim MainBook As Workbook
    Dim Arr() As Variant, rngA As Range, rngB As Range
    Dim cnn As Object, rs As Object
    Dim i As Long, itemStr(1 To 4) As String '判定标志 1=OK,2=外观等级,3=WLD,4=LOP
    Dim sourceFile As String
    
    Set cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set MainBook = ThisWorkbook
    sourceFile = "D:\4月份统计表.xlsx"
    strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sourceFile + "';Extended Properties='Excel 12.0;HDR=YES,IME=1'"
        
    With ThisWorkbook.Worksheets(1)
        strSql(1) = " And [外观等级] ='A' And [WLD] >=451.5 And [WLD] <=458 And LOP>=82 And LOP<200"
        strSql(2) = " And [外观等级] <>'A'"
        strSql(3) = " And ([WLD] <451.5 or [WLD] >458)"
        strSql(4) = " And (LOP>=82 or LOP<200"
        itemStr(1) = "OK"
        itemStr(2) = "外观等级"
        itemStr(3) = "WLD"
        itemStr(4) = "LOP"
        Set rngA = .Range("B" & .Cells(6000, "B").End(xlUp).Row + 1).Resize(1, 12) '//找到写数据的位置
    End With
    
    cnn.Open strCnn
    If cnn.State = 1 Then
        For i = 4 To 1 Step -1 '//更新筛选结果
            sqlStr = "update [数据源$] set [筛选结果]= '" & itemStr(i) & "' where 1=1 " & strSql(i)
            cnn.Execute sqlStr
        Next i
        '//Excel/Access不支持case when then end结构，要用IIF来代替
'        subSqlStr = "(select [序号], case [筛选结果] when 'OK' then 1 end as [OK]," & _
'                " case [筛选结果] when '外观等级' then 1 end as [外观等级]," & _
'                " case [筛选结果] when 'WLD' then 1 end as [WLD]," & _
'                " case [筛选结果] when 'LOP' then 1 end as [LOP]," & _
'                " from [数据源$]) B "
'       ///
        subSqlStr = "(select [衬底编号], iif( [筛选结果] = 'OK',1,0 ) as [OK]," & _
                " iif( [筛选结果] = '外观等级',1,0 ) as [外观等级]," & _
                " iif( [筛选结果] = 'WLD',1,0 ) as [WLD]," & _
                " iif( [筛选结果] = 'LOP',1,0 ) as [LOP]," & _
                " from [数据源$]) B "
        
        sqlStr = "select A.[入库日期],count(A.[入库日期]) as 总数量,sum(B.[OK]) as OK,(count(A.[入库日期])-sum(B.[OK])) as NG,sum(B.[外观等级])/(count(A.[入库日期])-sum(B.[OK]))," & _
                " sum(B.[WLD])/(count(A.[入库日期])-sum(B.[OK])),sum(B.[LOP])/(count(A.[入库日期])-sum(B.[OK]))" & _
                "from [数据源$] A, " & subSqlStr & " where A.[序号]=B.[序号] " & _
                "group by A.[入库日期] order by A.[入库日期] asc"
        Set rs = cnn.Execute(sqlStr)
        If Not rs.EOF Then
            Arr = WorksheetFunction.Transpose(rs.GetRows())
            Set rngA = rngA.Resize(UBound(Arr, 1), 12)
            rngA = Arr
            rngA.Borders.LineStyle = xlContinuous
            rngA.Resize(, 1).Offset(, 4).Resize(, 8).NumberFormatLocal = "0.00%"
        End If
        Set rs = Nothing
    End If
    cnn.Close
End Sub

说明：

A.要将[筛选结果]做细项分析，就要用到行转列。而Excel/Access中没有case when then end 结构，更没有pivot可以用，幸好有IIF可用。

B.Transpose函数使用有些限制，要求结果集不超过1024行[未验证]，且结果集不包含null[已验证]。

C.Excel作为数据库，查询速度明显比Access数据库以及SqlServer、Oracle速度慢很多。5万多行数据，要查询大约20秒了。

D.Excel表作为数据库使用，select和update可正常使用，但insert和delete不能用。使用select into 可以创建新的Excel工作表。