|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Private Sub CommandButton1_Click()
d.RemoveAll
ReDim br(1 To UBound(ar), 1 To 3)
If ComboBox1.Text = "" Then MsgBox "请输入汇总日期!": Exit Sub
If ComboBox1.Text = "全部" Then
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
rq = Format(ar(i, 1), "yyyy/m/d")
t = d(rq)
If t = "" Then
k = k + 1
d(rq) = k
t = k
br(k, 1) = rq
End If
If Trim(ar(i, 11)) = "贷" Then
br(t, 2) = br(t, 2) + Val(ar(i, 6))
ElseIf Trim(ar(i, 11)) = "借" Then
br(t, 3) = br(t, 3) + Val(ar(i, 6))
End If
End If
Next i
ElseIf IsDate(ComboBox1.Text) Then
tj = ComboBox1.Text
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
rq = Format(ar(i, 1), "yyyy/m/d")
If DateValue(rq) = DateValue(tj) Then
t = d(rq)
If t = "" Then
k = k + 1
d(rq) = k
t = k
br(k, 1) = rq
End If
If Trim(ar(i, 11)) = "贷" Then
br(t, 2) = br(t, 2) + Val(ar(i, 6))
ElseIf Trim(ar(i, 11)) = "借" Then
br(t, 3) = br(t, 3) + Val(ar(i, 6))
End If
End If
End If
Next i
End If
With Sheets("透视表1")
.UsedRange.Offset(3) = Empty
.[a4].Resize(k, 3) = br
.Cells(k + 4, 1) = "总计"
For j = 2 To 3
.Cells(k + 4, j) = Application.Sum(.Range(.Cells(4, j), .Cells(k + 3, j)))
Next j
End With
MsgBox "ok!"
End Sub
Private Sub CommandButton2_Click()
End
End Sub
Private Sub UserForm_Initialize()
Set d = CreateObject("scripting.dictionary")
ar = Sheets("原始数据").[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To 1)
kk = 1
br(kk, 1) = "全部"
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
rq = Format(ar(i, 1), "yyyy/m/d")
t = d(rq)
If t = "" Then
kk = kk + 1
d(rq) = kk
t = kk
br(kk, 1) = rq
End If
End If
Next i
ComboBox1.List = br
End Sub
|
|