2个统计

chj6533

hcm19522 发表于 2023-6-8 16:05
K2{=COUNT(0/IFERROR(MMULT(N(TEXT(A$2:A$14,{"EMD","EM","E"})-INDEX(TEXT(H$2,{"EMD","EM","E"}),ROW(A1) ...

这个功力不是一般的

ykcbf1100

chj6533 发表于 2023-6-8 17:51
这个功力不是一般的

这么高深的公式一般人学不会的。

chj6533

‘这是我写的，总感觉有点啰嗦。
Sub 固定日期计算()
    Dim m, n, k, i, s, ss, arr
    Dim d1 As New Scripting.Dictionary
    Dim d2 As New Scripting.Dictionary
    Dim d3 As New Scripting.Dictionary
    Dim d4 As New Scripting.Dictionary
    Dim jr, by, dn, qb, jr0, by0, dn0
    jr = Format(Date, "yyyy/mm/dd")
    by = Format(Date, "yyyy/mm")
    dn = Format(Date, "yyyy")
    n = Sheets("数据").Cells(Rows.Count, "b").End(3).Row
    arr = Sheets("数据").[b2].Resize(n - 1, 4)
    For i = 1 To UBound(arr)
       jr0 = Format(arr(i, 1), "yyyy/mm/dd")
       by0 = Format(arr(i, 1), "yyyy/mm")
       dn0 = Format(arr(i, 1), "yyyy")
       s = arr(i, 2): ss = arr(i, 4)
       If jr0 = jr Then d1(s) = d1(s) + ss
       If by0 = by Then d2(s) = d2(s) + ss
       If dn0 = dn Then d3(s) = d3(s) + ss
       d4(s) = d4(s) + ss
    Next
    Dim m1, m2, m3, m4
    If d1.Count = 0 Then m1 = 0 Else m1 = WorksheetFunction.Sum(d1.Items)
    If d2.Count = 0 Then m2 = 0 Else m2 = WorksheetFunction.Sum(d2.Items)
    If d3.Count = 0 Then m3 = 0 Else m3 = WorksheetFunction.Sum(d3.Items)
    m4 = WorksheetFunction.Sum(d4.Items)
    Sheets("统计").[c4:c7] = WorksheetFunction.Transpose(Array(d1.Count, d2.Count, d3.Count, d4.Count))
    Sheets("统计").[d4:d7] = WorksheetFunction.Transpose(Array(m1, m2, m3, m4))
End Sub