Sub 统计()
Dim cnn
Dim rs
Set cnn = CreateObject("adodb.connection") '创建ado对象
Set rs = CreateObject("ADODB.recordset") '创建记录集
Dim SQL As String
cnn.Open "Provider=Microsoft.Ace.OleDB.12.0;extended properties=excel 8.0;data Source=" & ThisWorkbook.FullName
SQL = "select 职场楼层,分配部门,count(1) as 分配工号,sum(iif(姓名<>'' or 员工编号<>'',1,0)) as 分配已用,count(1)-sum(iif(姓名<>'' or 员工编号<>'',1,0)) as 分配未用 from [工位表$A:E] group by 职场楼层,分配部门"
rs.Open SQL, cnn
Range("B2").CopyFromRecordset rs
rs.Close
cnn.Close
End Sub