Bạn hãy sử dụng Macro sau đây: (Chú ý đặt tên cho sheet chứa dữ liệu là Data)
Code:
Option Explicit
Public Sub Tonghop_SP()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim n As Long, i As Long, m As Long
Dim rngKho As Range
Dim shtData As Worksheet
Set shtData = Sheets("Data")
Dim sht_Tam As Worksheet
Dim sht_Active As Worksheet
Set sht_Active = Worksheets.Add
Set sht_Tam = Worksheets.Add
shtData.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht_Active.Range("A3"), Unique:=True
shtData.Columns("E:E").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht_Tam.Range("A1"), Unique:=True
n = WorksheetFunction.CountA(sht_Tam.Range("A:A")) - 1
For i = 1 To n
sht_Active.Cells(3, i + 3).Value = sht_Tam.Cells(i + 1, 1).Value
Next i
sht_Active.Cells(3, n + 4) = "Tong"
i = n
n = WorksheetFunction.CountA(sht_Active.Range("A:A")) - 1
m = WorksheetFunction.CountA(shtData.Range("A:A"))
sht_Active.Range("D4", Cells(4 + n - 1, i + 3).Address).Formula = _
"=SUMPRODUCT((Data!$A$2:$A$" & m & "=$A4)*(Data!$E$2:$E$" & m & "=D$3),Data!$D$2:$D$" & m & ")"
sht_Active.Range(Cells(4, i + 4).Address, Cells(4 + n - 1, i + 4).Address).FormulaR1C1 = "=SUM(RC[-" & i & "]:RC[-1])"
sht_Tam.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub