求助大神们,统计不同日期段，求平均价格

dnvjfdeuff

求助大神们,根据不同日期段，求平均价格

yfan17

1. =AVERAGEIFS(D$2:D$125,A$2:A$125,$G2,B$2:B$125,$H2,C$2:C$125,">="&LEFT(J$1,FIND("/",J$1)-1),C$2:C$125,"<="&RIGHT(J$1,LEN(J$1)-FIND("/",J$1)))

复制代码

baofa2

1. Sub test0() '
   
2.   
   
3.   Dim Conn As Object, rs As Object
   
4.   Dim strConn As String, strSQL As String, strTab As String, i As Long
   
5.   
   
6.   'Application.ScreenUpdating = False
   
7.   
   
8.   Set Conn = CreateObject("ADODB.Connection")
   
9.   If Application.Version < 12 Then
   
10.     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source="
    
11.   Else
    
12.     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source="
    
13.   End If
    
14.   Conn.Open strConn & ThisWorkbook.FullName
    
15.   
    
16.   With Sheet1
    
17.     strTab = "[" & .Name & "$" & .Range("A1").CurrentRegion.Address(0, 0) & "]"
    
18.   End With
    
19.   
    
20.   strSQL = "SELECT 网站,规格,IIF(DAY(日期)>20,FORMAT(日期,'YYYY-M-21')&'/'&FORMAT(DATEADD('M',1,日期),'YYYY-M-20'),FORMAT(DATEADD('M',-1,日期),'YYYY-M-21')&'/'&FORMAT(日期,'YYYY-M-20')) AS 日期段,最低价 FROM " & strTab
    
21.   strSQL = "TRANSFORM AVG(最低价) SELECT 网站,规格,'' AS 查询列 FROM (" & strSQL & ") GROUP BY 网站,规格 PIVOT 日期段"
    
22.   Set rs = Conn.Execute(strSQL)
    
23.   
    
24.   With Sheet1.Range("G5")
    
25.     .CurrentRegion.Clear
    
26.     For i = 0 To rs.Fields.Count - 1
    
27.       .Offset(, i) = rs.Fields(i).Name
    
28.     Next
    
29.     .Offset(1).CopyFromRecordset rs
    
30.     With .CurrentRegion
    
31.       .Borders.Weight = xlHairline
    
32.       .HorizontalAlignment = xlCenter
    
33.     End With
    
34.   End With
    
35.   
    
36.   rs.Close
    
37.   Set rs = Nothing
    
38.   Conn.Close
    
39.   Set Conn = Nothing
    
40.   
    
41.   Application.ScreenUpdating = True
    
42.   Beep
    
43. End Sub
    

复制代码

沈默00

沈默00

=ROUND(AVERAGE(TAKE(FILTER($B:$D,($B:$B=$H2)*(ISNUMBER($D:$D))*($C:$C>=--LEFT(J$1,FIND("/",J$1)-1))*($C:$C<=--RIGHT(J$1,LEN(J$1)-FIND("/",J$1)))),,-1)),2)
右拉  下拉

quqiyuan

供参考

quqiyuan

1. Sub test()
   
2. arr = Sheet1.[a1].CurrentRegion
   
3. brr = Sheet1.[g1].CurrentRegion
   
4. ReDim crr(1 To UBound(brr) - 1, 1 To UBound(brr, 2) - 3)
   
5. m = 1
   
6. For i = 2 To UBound(brr)
   
7.     n = 1
   
8.     For j = 4 To UBound(brr, 2)
   
9.         s = Split(brr(1, j), "/")
   
10.         date1 = CDate(s(0)): date2 = CDate(s(1))
    
11.         s = brr(i, 1) & brr(i, 2)
    
12.         For k = 2 To UBound(arr)
    
13.             If s = arr(k, 1) & arr(k, 2) And arr(k, 3) >= date1 And arr(k, 3) <= date2 Then
    
14.                 If Val(arr(k, 4)) Then sums = sums + Val(arr(k, 4)): x = x + 1
    
15.             End If
    
16.         Next
    
17.         crr(m, n) = sums / x
    
18.         sums = 0: x = 0
    
19.         n = n + 1
    
20.     Next
    
21.     m = m + 1
    
22. Next
    
23. Sheet1.[j2].Resize(UBound(crr), UBound(crr, 2)).ClearContents
    
24. Sheet1.[j2].Resize(UBound(crr), UBound(crr, 2)) = crr
    
25. Beep
    
26. End Sub
    

复制代码

baofa2

1. Sub test1()  '再来一个，也自动生成
   
2.   Dim data, results(), count_() As Long, dict As Object
   
3.   Dim i As Long, j As Long, strKey As String
   
4.   Dim rowSize As Long, colSize As Long, posRow As Long, posCol As Long
   
5.   
   
6.   Application.ScreenUpdating = False
   
7.   
   
8.   Set dict = CreateObject("Scripting.Dictionary")
   
9.   data = Sheet1.Range("A1").CurrentRegion.Value
   
10.   ReDim results(1 To UBound(data), 1 To 63), count_(1 To UBound(data), 1 To 63)
    
11.   
    
12.   rowSize = 1
    
13.   For colSize = 1 To 2
    
14.     results(rowSize, colSize) = data(1, colSize)
    
15.   Next
    
16.   results(rowSize, colSize) = "查询列"
    
17.   
    
18.   For i = 2 To UBound(data)
    
19.     If Val(data(i, 4)) Then
    
20.       strKey = data(i, 1) & data(i, 2)
    
21.       If Not dict.Exists(strKey) Then
    
22.         rowSize = rowSize + 1
    
23.         For j = 1 To 2
    
24.           results(rowSize, j) = data(i, j)
    
25.         Next
    
26.         dict.Add strKey, rowSize
    
27.       End If
    
28.       posRow = dict(strKey)
    
29.       If Day(data(i, 3)) > 20 Then
    
30.         strKey = Format(data(i, 3), "YYYY-M-21") & "/" & Format(DateAdd("M", 1, data(i, 3)), "YYYY-M-20")
    
31.       Else
    
32.         strKey = Format(DateAdd("M", -1, data(i, 3)), "YYYY-M-21") & "/" & Format(data(i, 3), "YYYY-M-20")
    
33.       End If
    
34.       If Not dict.Exists(strKey) Then
    
35.         colSize = colSize + 1
    
36.         results(1, colSize) = strKey
    
37.         dict.Add strKey, colSize
    
38.       End If
    
39.       posCol = dict(strKey)
    
40.       count_(posRow, posCol) = count_(posRow, posCol) + 1
    
41.       results(posRow, posCol) = results(posRow, posCol) + Val(data(i, 4))
    
42.     End If
    
43.   Next
    
44.   For j = 4 To colSize
    
45.     For i = 2 To rowSize
    
46.       If count_(i, j) Then results(i, j) = results(i, j) / count_(i, j)
    
47.     Next
    
48.   Next
    
49.   
    
50.   With Sheet1.Range("G5")
    
51.     .CurrentRegion.Clear
    
52.     With .Resize(rowSize, colSize)
    
53.       .HorizontalAlignment = xlCenter
    
54.       .Borders.LineStyle = xlContinuous
    
55.       .Value = results
    
56.     End With
    
57.   End With
    
58.   
    
59.   Set dict = Nothing
    
60.   Application.ScreenUpdating = True
    
61.   Beep
    
62. End Sub

复制代码

dnvjfdeuff

baofa2 发表于 2024-5-18 08:37

大神太厉害，谢谢

dnvjfdeuff

quqiyuan 发表于 2024-5-18 08:30

大神太厉害，