根据查询条件，统计不重复值的和

ww87725244

1. Function aa(s As Range)
   
2. Dim my_sql$, my_strConnect$, my_ADOConn, d, i, ar(), j, k, rng As Range
   
3. Set my_ADOConn = CreateObject("ADODB.connection")
   
4. my_strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;data source="
   
5. my_ADOConn.Open my_strConnect & ThisWorkbook.FullName
   
6. Set d = CreateObject("ADODB.recordset")
   
7. my_sql = "select  项目,sum(订单金额) as 金额 from (select distinct 项目,订单号,订单金额 from [Sheet1$a1:c65535]) group by 项目 having count(项目)>=1"
   
8. d.Open my_sql, my_ADOConn, 1, 1
   
9. ReDim Preserve ar(1 To d.RecordCount, 1 To d.Fields.Count)
   
10. For i = 1 To d.RecordCount
    
11.   For j = 1 To d.Fields.Count
    
12.     ar(i, j) = d(j - 1)
    
13.   Next
    
14.   d.movenext
    
15. Next
    
16. For k = 1 To UBound(ar)
    
17.   If ar(k, 1) = s Then aa = ar(k, 2)
    
18. Next
    
19. End Function

复制代码

LIUZHU

参与下，执行效果

LIUZHU

JS代码，供参考

1. function 汇总求和(){
   
2.         const arr=Range("a1").CurrentRegion.Value2;
   
3.         let obj=arr.slice(1).reduce((obj,[a,b,c])=>{
   
4.                 let temp=obj[a]=obj[a] || [a,0,b];
   
5.                 if (temp[2]==b && temp[1]!=0) return obj;
   
6.                 return temp[1]+=c, obj;
   
7.         },{"表头":["项目","订单总金额"]});
   
8.         let res=Object.values(obj);
   
9.         Range("h1").Resize(res.length, res[0].length).Value2=res;
   
10. }

复制代码

LIUZHU

附件，需用WPS打开测试

zhangcheng6688

学习一下字典，打扰了
Sub zcm()
arr = [a1].CurrentRegion
ReDim brr(1 To UBound(arr), 1 To 2)
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   s = arr(i, 1)
   s1 = arr(i, 2)
   If Not d.exists(s & "|" & s1) Then
      d(s) = d(s) + arr(i, 3)
      d(s & "|" & s1) = ""
   End If
Next
For Each vkey In d.keys
   If InStr(vkey, "|") = 0 Then
      m = m + 1
      brr(m, 1) = vkey
      brr(m, 2) = d(vkey)
   End If
Next
[e2].Resize(m, 2) = brr
End Sub