Sub Sumproduct_Arr()
Dim Tm1(), Tm2(), Tm3(), i
ReDim Tm1(31), Tm2(31), Tm3(31)
For i = 1 To 31
Tm1(i - 1) = (Sheet1.Cells(i, 1).Value = "A02") * 1
Tm2(i - 1) = (Sheet1.Cells(i, 2).Value = "B02") * 1
Tm3(i - 1) = Sheet1.Cells(i, 3)
Next
MsgBox WorksheetFunction.SumProduct(Tm1, Tm2, Tm3)
End Sub
Sub LOC_KTRA()
Dim endr As Long
endr = ActiveSheet.[A65000].End(xlUp).Row
'Range([A9], [A9].End(xlDown).Offset(1)).Resize(, 12).ClearContents
Range("DATA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets( _
"KTRA").Range("tieude"), CopyToRange:=Range("A8:I8"), Unique:=True
'---------------------------------------------------------------------------------
Dim Tm, Kq(), tu, den, i, j
Dim Rg As Range
Set Rg = Sheet2.Range([C9], [C65536].End(3))
tu = Sheet2.[E2]: den = Sheet2.[F2]
Tm = S104.Range(S104.[A2], S104.[A65536].End(3)).Resize(, 9)
ReDim Kq(1 To Rg.Count)
For i = 1 To Rg.Count
For j = 1 To UBound(Tm, 1)
If Tm(j, 1) >= tu And Tm(j, 1) <= den And Tm(j, 3) = Rg.Item(i) Then
Kq(i) = Kq(i) + Tm(j, 9)
End If
Next j
Next i
Sheet2.[j9].Resize(Rg.Count) = WorksheetFunction.Transpose(Kq)
End Sub
Thú thật là nhìn cái bảng mình chẳng hiểu mô tê gì cả.Chưa có tác dụng gì Bác ơi, thật ra chỉ cần làm sao tính nhanh dược 3 cột cuối ở sh data là được, nếu để công thức thế này thì k chạy nối?
Public Sub ThaySumProduct()
Dim Vung, J, I, Mg()
Vung = Range([b2], [b10000].End(xlUp)).Resize(, 8).Value
ReDim Mg(1 To UBound(Vung), 1 To 3)
For I = 1 To UBound(Vung)
If Vung(I, 1) = "Phi" & ChrW(7871) & "u " & ChrW(273) & ChrW(7863) & "t hàng" Then
For J = 1 To UBound(Vung)
If Vung(J, 1) = "Nh" & ChrW(7853) & "p c" & ChrW(7855) & "t dán" And Vung(J, 2) = Vung(I, 2) Then
Mg(I, 1) = Mg(I, 1) + Vung(J, 8)
ElseIf Vung(J, 1) = "Xu" & ChrW(7845) & "t kho" And Vung(J, 2) = Vung(I, 2) Then
Mg(I, 2) = Mg(I, 2) + Vung(J, 8)
End If
Next J
If Vung(I, 8) = Mg(I, 1) And Vung(I, 8) = Mg(I, 2) Then
Mg(I, 3) = ChrW(272) & "ã giao"
ElseIf Mg(I, 1) * Mg(I, 2) = 0 Then
Mg(I, 3) = "Ch" & ChrW(432) & "a làm"
ElseIf Vung(I, 8) = Mg(I, 1) And Mg(I, 2) = "" Then
Mg(I, 3) = "Chua giao"
Else
Mg(I, 3) = "Xem lai"
End If
End If
Next I
[U2:W10000].ClearContents
[U2].Resize(UBound(Vung), 3) = Mg
End Sub
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2