写过的能把自己绕晕的东西

SQL

select t7.班组, t7.编码,t7.物料名称,t7.规格,t7.型号,t7.领料用途,t7.实际领用,t6.定额应领 from (select t4.名称1,t4.物料编码1,t4.物料名称1,t4.规格1,t4.型号1,sum(t4.定额数量) as 定额应领 from (select t2.名称1,t3.物料编码1,t3.物料名称1,t3.规格1,t3.型号1,t2.入库数*t3.子项主数量/t3.底数 as 定额数量 from (select t1.名称1,t1.物料编码,t1.物料名称,t1.规格,t1.型号,sum(t1.实收主数量) as 入库数 from [产成品入库单列表] t3 where t2.物料编码=t3.物料编码) t4 group by t4.名称1,t4.物料编码1,t4.物料名称1,t4.规格1,t4.型号1) t6 right outer join (select t5.班组, t5.编码,t5.物料名称,t5.规格,t5.型号,t5.领料用途,sum(t5.实发主数量) as 实际领用 from [材料出库单列表$] t5 group by t5.班组, t5.编码,t5.物料名称,t5.规格,t5.型号,t5.领料用途) t7 on t7.班组=t6.名称1 and t7.编码=t6.物料编码1

EXCEL函数

{=IFERROR(M152/(SUM((123:4919=A152)(123:4919=E152)(123:4919))/(AVERAGEIFS(123:4919,123:4919,A152,123:4919,E152)*(AVERAGEIFS(123:4919,123:4919,A152,123:4919,E152)))),"错误")}

写的自己都佩服自己的逻辑

Option Explicit
Sub huizong()
On Error Resume Next
Dim rng As Range, d As Object, i%, arr, n%, m%
[a2:b63356].ClearContents
Set d = CreateObject("scripting.dictionary")
'i = Application.WorksheetFunction.CountA(Worksheets("车间临时工时").[a:a])
arr = Worksheets("车间临时工时").Range("d2:aa" & 63356)

For n = 1 To 20000
    For m = 3 To 24
        If Not arr(n, m) = "" Then
            d(arr(n, m)) = d(arr(n, m)) + arr(n, 1)
        End If
    Next
Next
[a1] = "姓名"
[b1] = "工时合计"
[a2].Resize(d.Count) = Application.Transpose(d.keys)
[b2].Resize(d.Count) = Application.Transpose(d.items)

Set d = Nothing

End Sub