EXCEL VBA 计算财务报表透视图计算汇总透视表
Sub 应退税额()
Dim wbm As Workbook
SelectedFile = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", , "请选择公司整体利润明细表", , False)
Set wbm = Workbooks.Open(SelectedFile)
Dim wm As Worksheet
Set wm = wbm.Worksheets("返利明细表")
Dim diccai As Object
Set diccai = CreateObject("scripting.dictionary")
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
For i = 2 To wm.Range("a" & wm.Cells.Rows.Count).End(xlUp).Row
k = wm.Cells(i, 3).Value
kdate = Year(k) & "年" & Month(k) & "月"
kitem = wm.Cells(i, "j") / 1.22 * 0.22
If Not diccai.exists(kdate) Then
diccai.Add kdate, kitem
Else
diccai(kdate) = diccai(kdate) + kitem
End If
kshoudate = CDate(Year(wm.Cells(i, 6)) & "/" & Month(wm.Cells(i, 6)) & "/1")
kdic = kdate & "-" & kshoudate
kdicitem = wm.Cells(i, "k")
If Not dic.exists(kdic) Then
dic.Add kdic, kdicitem
Else
dic(kdic) = dic(kdic) + kdicitem
End If
Next
wbm.Close
Dim wz As Worksheet
Set wz = ThisWorkbook.Worksheets("FLASH COMMERCE S.R.L 回票情况汇总表")
For i = 3 To wz.Range("a" & wz.Cells.Rows.Count).End(xlUp).Row - 1
k = wz.Cells(i, 2).Value
wz.Cells(i, 3) = diccai(k)
endcol = wz.Range(wz.Cells(2, 2), wz.Cells(2, 2)).End(xlToRight).Column
For j = 5 To endcol
k = wz.Cells(i, 2).Value & "-" & wz.Cells(2, j)
wz.Cells(i, j) = dic(k)
Next
wz.Cells(i, 4).FormulaR1C1 = "=RC[-1]-SUM(RC[1]:RC[" & encol - 4 & "])"
Next
End Sub