自动统计每天的销售数量

mengrs

=CHOOSECOLS(GROUPBY(A:D,B:B,counta,3,0,,A:A>0),1,4,2,5,3)

沈默00

=DROP(GROUPBY(HSTACK(A:A,D:D,B:B),B:C,HSTACK(ROWS,UNIQUE),3,0,,A:A>0),1)
如果同一天同一个人，相同的产品出现现两种价格就需要再修改

朝露孤烟

姓名排序没法得到与要求一致，有点遗憾。

zhangcheng6688

借楼学习一下字典，打扰了
Sub test()
lastr = Cells(Rows.Count, "a").End(xlUp).Row
arr = Range("a1:d" & lastr)
[g1].Resize(, 5) = Array("时间", "销售者", "货品", "数量", "价格")
ReDim brr(1 To UBound(arr), 1 To 5)
Set dic = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   If arr(i, 1) <> "" Then
      strkey = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 4)
      If Not dic.exists(strkey) Then
         m = m + 1
         brr(m, 1) = arr(i, 1)
         brr(m, 2) = arr(i, 4)
         brr(m, 3) = arr(i, 2)
         brr(m, 4) = 1
         brr(m, 5) = arr(i, 3)
         dic(strkey) = m
      Else
         r = dic(strkey)
         brr(r, 4) = brr(r, 4) + 1
      End If
   End If
Next
For i = 1 To m - 1
   For j = i + 1 To m
      If brr(i, 1) > brr(j, 1) Then
         For k = 1 To UBound(brr, 2)
            temp = brr(i, k): brr(i, k) = brr(j, k): brr(j, k) = temp
         Next
      End If
   Next
Next
If m > 0 Then
   [g2].Resize(m, 5) = brr
End If
Set dic = Nothing
End Sub

守候_CJ

参考公式：

1. =CHOOSECOLS(GROUPBY(HSTACK(A1:A33,D1:D33,B1:C33),C1:C33,COUNT,3,0,,A1:A33<>""),1,2,3,5,4)

复制代码

效果：

秃头发师

=CHOOSECOLS(LET(x,DROP(SORT(UNIQUE(A2:D50)),-1),
HSTACK(x,DROP(REDUCE("",SEQUENCE(ROWS(x)),LAMBDA(arr,brr,VSTACK(arr,ROWS(FILTER(A2:D35,BYROW(A2:D35=CHOOSEROWS(x,brr),LAMBDA(crr,AND(crr)))))))),1))),{1,4,2,5,3})