Excel收支理财管理及财务知识分析系统

1、Excel收支管理系统程序功能：银行信息记录a）存款利息计算b）银行总资金汇总c）银行年收益计算收支记录a）收入项目记录，增加到银行账户b）支出项目记录，选择支出账户c）可对每条记录进行修改，并与账户关联d）收支项目管理，可增加或删除收支项目本程序操作灵活，界面人性化，比如删除“银行记录”金额，可将本条记录信息全部删 除（需要确认）；收支记录中信息输入完整，自动与银行账户信息关联；可自己添加银行并 修改利率。使用本程序可快速判别存款方式对收益的影响，比如5万存入工商银行：1. 整存整取两年，利息 44002. 整存整取一年，利息 3561 （两年后取）3E11$1J?30界面“银行记录”*丁

2、b71 _J ii 1JIiF1-Mll-il-jti-frrf-JFTpjsIWT.BKEHflAHIBIHE1B枷33m t-t l目rt 111冲-rML ll?m_i_xfrfirt1.1DLL-tMIIISI305啊斤冷刚IL-E3-4MI. IIWblll6S*rTmaais-E i-iV. 319311TgitJlIM.rl1 1* *hl崟力取弭牯佰基巻 军芒直：-r：四十二匚仏訂时IB工简蚩厅eq. dm2010-11-44*42012 11 454,4：04j4DC工裔镇忏整?!諏2QLO11-4?f fl3*6lull L1 412QL2-1L-463.XGB1现在银行利

3、率也有差别，存不同银行收益相差多少也能方便了解。银行记录”中复制代码如下:Private Sub Cale ndar1_Click()ActiveCell = Cale ndar1Cale ndar1.Visible = FalseEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim lvDim zhuancun(1 To 100)Dim lv_huo(1 To 1000)Dim lv_ding1_3(1 To 1000)Dim lv_ding1_6(1 To 1000)Dim lv_ding1_12(1

4、To 1000)Dim lv_ding1_24(1 To 1000)Dim lv_ding1_36(1 To 1000)Dim lv_ding1_60(1 To 1000)Dim lv_ding2_12(1 To 1000)Dim lv_ding2_36(1 To 1000)Dim lv_ding2_60(1 To 1000)Dim rng As Rangern = Range(b65536).End(xlUp).Row最大行号cn = Range(b2).End(xlToRight).Column 最大列号Application.ScreenUpdating = False数据初始化If S

5、heet1.Cells(ActiveCell.Row, 1) = And Sheet1.Cells(ActiveCell.Row, 3) = And Sheet1.Cells(ActiveCell.Row, 2) ThenSheet1.Cells(ActiveCell.Row, 1) = 中国银行 End IfFor y = 3 To rnIf Sheet1.Cells(y, 1) ThenSheet4.Select查找银行名称Set rng = Sheet4.B:B.Find(Sheet1.Cells(y, 1)定位银行If Not rng Is Nothing Thenrng.Font.C

6、olorIndex = 3颜色暂不设置Application.Goto Reference:=rng.Address(, , xlR1C1) End IfEnd Iflv_huo(y) = Sheet4.Cells(ActiveCell.Row + 3, ActiveCell.Column + 1) lv_ding1_3(y) = Sheet4.Cells(ActiveCell.Row + 6, ActiveCell.Column + 1) lv_ding1_6(y) = Sheet4.Cells(ActiveCell.Row + 7, ActiveCell.Column + 1) lv_di

7、ng1_12(y) = Sheet4.Cells(ActiveCell.Row + 8, ActiveCell.Column + 1) lv_ding1_24(y) = Sheet4.Cells(ActiveCell.Row + 9, ActiveCell.Column + 1) lv_ding1_36(y) = Sheet4.Cells(ActiveCell.Row + 10, ActiveCell.Column + 1) lv_ding1_60(y) = Sheet4.Cells(ActiveCell.Row + 11, ActiveCell.Column + 1) lv_ding2_12

8、(y) = Sheet4.Cells(ActiveCell.Row + 13, ActiveCell.Column + 1) lv_ding2_36(y) = Sheet4.Cells(ActiveCell.Row + 14, ActiveCell.Column + 1)lv_ding2_60(y) = Sheet4.Cells(ActiveCell.Row + 15, ActiveCell.Column + 1)返回 sheet “银行项目Sheet1.SelectNext 格式初始化With Range(Sheet1.Cells(3, 1), Sheet1.Cells(rn + 30, c

9、n).Interior .Pattern = xlNone.TintAndShade = 0.PatternTintAndShade = 0End With取消列表With Sheet1.Range(A:A).Validation.Delete.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween.IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMes

10、sage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith Sheet1.Range(C:C).Validation.Delete.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween.IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage

11、 = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With银行列表更新rn4 = Sheet4.Range(e65536).End(xlUp).Rowf = = 基本信息 !E5:E & rn4 & With Range(A3).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropd

12、own = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With项目列表With Range(C3).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= 活期 ,整存整取 ,整存零取 ,零存整取 ,存本取息 ,定活两

13、便 .IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithFor Z = 3 To rn 银行列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = = 基本信息 !E5:E & rn4 & With Range(A & Z + 1).Validation.Dele

14、te.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With项目列表With Range(C & Z + 1).Valida

15、tion.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= 活期 ,整存整取 ,整存零取 ,零存整取 ,存本取息 ,定活两便 .IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = True

16、End With年利率If Sheet1.Cells(Z, 3) = Huo Or Sheet1.Cells(Z, 3) = 活期 Then lv = lv_huo(Z)Sheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = ZZ Or Sheet1.Cells(Z, 3) = 整 存整取 Then If Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cel

17、ls(Z, 5) = 60 Then lv = lv_ding1_60(Z) 5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = ZL LZ BX Or Sheet1.Cells(Z, 3) = 零存整取 Or Sheet1.Cells(Z, 3) = 整存零取 Or Sheet1.Cells(Z, 3) = 存本取息 ThenIf Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_din

18、g2_60(Z) 5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = 定活两便 ThenIf Sheet1.Cells(Z, 4) = And Sheet1.Cells(Z, 5) = Then dh = MsgBox( 未区分各家银行计算方法， 结果不一定准确， 按利率 60%计算？ , vbYesNo, 提示 )If dh = vbYes Then If Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And

19、Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_ding1_60(Z) 5 年End If lv = lv * 0.6Else Sheet1.Cells(Z, 3) = End IfElseIf Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_ding1_60(Z) 5

20、 年End If lv = lv * 0.6End If Sheet1.Cells(Z, 6) = lvEnd IfOn Error Resume Next=If Target.Column = 4 And Target.Value 存入日期 And Target.Row 2 And Target.Row rn ThenCalendar1.Visible = True 日历控件Calendar1.Left = Cells(Target.Row, 4).LeftCalendar1.Top = Cells(Target.Row + 1, 4).TopWith Calendar1 当前日期.Year

21、 = Year(Now).Month = Month(Now).Day = Day(Now)End WithElseIf Target.Column = 1 And Target.Row = 1 Then Calendar1.Visible = True 日历控件 Calendar1.Left = Cells(Target.Row, 1).Left Calendar1.Top = Cells(Target.Row + 1, 1).TopWith Calendar1 当前日期 .Year = Year(Now) .Month = Month(Now) .Day = Day(Now) End Wi

22、thElseCalendar1.Visible = False End IfOn Error Resume Nextnian = Int(Sheet1.Cells(Z, 5) / 12) 年数yue = Sheet1.Cells(Z, 5) - 12 * nian月数lixishui = 0 利息税率daoqi = Sheet1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1-lixishui) 到期总数当前日期与存入日期相差月数If Year(Sheet1.Cells(1, 1) =

23、 Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4) Thenm = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 + Month(Sheet1.Cells(1, 1) - Month(Sheet1.Cells(Z, 4)End IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cel

24、ls(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4) Thenm = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4)End IfIf Ye

25、ar(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4) Thenm = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4) - 1End IfIf Year(Sheet1.Cells(1, 1) Year(Sheet1.Cells(Z, 4) Thenm = 0End IfIf Ye

26、ar(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) Month(Sheet1.Cells(Z, 4) ThenEnd IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) =Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) Day(Sheet1.Cells(Z, 4) Thenm = 0End Ifzhuan = Int(m /

27、 Sheet1.Cells(Z, 5) - 1 转存次数If zhuan 2 And Sheet1.Cells(Z, 6) ThenSheet1.Cells(Z, 7) = daoqi- 到期总数If Sheet1.Cells(Z, 4) Then Sheet1.Cells(Z, 8).FormulaR1C1 = _ =DATE(YEAR(RC-4),MONTH(RC-4)+RC-3,DAY(RC-4) - 到期日期End IfEnd IfIf Sheet1.Cells(Z, 7) ThenSheet1.Cells(Z, 9) = (Sheet1.Cells(Z, 7) - Sheet1.Ce

28、lls(Z, 2) - 利息 ElseSheet1.Cells(Z, 9) = End IfSheet1.Cells(Z, 10) = zhuan- 转存次数zhuancun(1) = Sheet1.Cells(Z, 7) + Sheet1.Cells(Z, 7) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui) 第一次转存到期总数If zhuan = 2 ThenFor i = 2 To zhuan zhuancun(i) = zhuancun(i - 1) + zhuancun(i - 1) * (lv / 100) * (

29、Sheet1.Cells(Z, 5) / 12) * (1 - lixishui)NextEnd IfIf Int(m / Sheet1.Cells(Z, 5) = m / Sheet1.Cells(Z, 5) ThenSheet1.Cells(Z, 11).FormulaR1C1 = _ =DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7) - 转存到期 日期ElseSheet1.Cells(Z, 11).FormulaR1C1 = _=DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7)

30、- 转存到期日期End IfIf zhuan 1 ThenSheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11) = Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7)Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9) ElseSheet1.Cells(Z, 12) = zhuancun(zhuan) - 当前总额Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 12) - Sheet1.Cells(Z, 2) - 当前利息 End If格式If Z Mod 2 = 1 Then当

31、前行数除以 2 的余数为 1(奇数)With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior .Pattern = xlSolid.PatternColorIndex = xlAutomatic.Color = 10198015 .TintAndShade = 0 .PatternTintAndShade = 0End WithElseIf Z Mod 2 = 0 Then当前行数除以 2 的余数为 0(偶数)With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior .P

32、attern = xlSolid.PatternColorIndex = xlAutomatic.Color = 13421823 .TintAndShade = 0 .PatternTintAndShade = 0End WithEnd If未选择银行不计利率If Sheet1.Cells(Z, 1) = Then lv = 0Sheet1.Cells(Z, 6) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 9) = End If活期不考虑到期时间，按存入时间到当前日期利息计算If Sheet1.Cells(Z, 3) = 活期 Then tian = Da

33、te - Sheet1.Cells(Z, 4) nian1 = Int(tian / 365) 年数 yue1 = Sheet1.Cells(Z, 5) - 12 * nian 月数Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * lv / 100 * (tian / 365) Sheet1.Cells(Z, 9) = Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2)Sheet1.Cells(Z, 5) = Sheet1.Cells(Z, 6) = lvSheet1.Cells(Z, 8)

34、 = Sheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11) = Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7)Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9)End If未选择存入时间If Sheet1.Cells(Z, 4) = Then Sheet1.Cells(Z, 9) = Sheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11) = Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) Shee

35、t1.Cells(Z, 12) = Sheet1.Cells(Z, 2)End If到期提示If Sheet1.Cells(Z, 8) Sheet1.Cells(1, 1) Then With Sheet1.Cells(Z, 8).Font .Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.Cells(Z, 8).Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0End WithEnd IfIf Sheet1.Cells(Z, 11) Sheet1.Cells(1, 1)

36、 ThenWith Sheet1.Cells(Z, 11).Font.Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.Cells(Z, 11).Font.ThemeColor = xlThemeColorLight1.TintAndShade = 0End With End IfNextFor j = 3 To rn + 1000If Sheet1.Cells(j, 2) = And Sheet1.Cells(j, 3) Then sc = MsgBoxC确认删除该项目？, vbYesNo,提示)If sc = vbYes The

37、nSheet1.Cells(j, 1) = Sheet1.Cells(j, 3) = Sheet1.Cells(j, 4) = Sheet1.Cells(j, 5) = Sheet1.Cells(j, 6) = Sheet1.Cells(j, 7) = Sheet1.Cells(j, 8) = Sheet1.Cells(j, 9) = Sheet1.Cells(j, 10) = Sheet1.Cells(j, 11) = Sheet1.Cells(j, 12) = Sheet1.Cells(j, 13) = Sheet1.Cells(j, 14) = End IfRows(j).DeleteE

38、nd IfNext图表数据处理图表 1，饼图，显示各银行资金分布 Sheet6.Columns(A:B).Clear Sheet6.Cells(1, 1) = 图表 1zong = 0 ben = 0 nianli = 0For K = 3 To rnrn1 = Sheet6.Range(a65536).End(xlUp).RowIf rn1 = 2 ThenActiveSheet.ChartObjects( 图表 1).ActivateActiveChart.SetSourceData Source:=Sheet6.Range(A2:A & rn1 & , B2:B & rn1 & ) Sh

39、eet1.Cells(r, c).Select设置绘图区格式With Sheets(1).ChartObjects( 图表 1).Name.Left.Top .Height = 250 .Width .Chart.ChartTypeEnd With End IfWith Range(O3, O4) .HorizontalAlignment = xlRight .VerticalAlignment = xlCenter .WrapText = False.Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = Fals

40、e .ReadingOrder = xlContext .MergeCells = FalseEnd WithWith Range(O3).Font.Color = -16751104 .TintAndShade = 0End WithWith Range(O4).Font.Color = -3368704 .TintAndShade = 0End WithRange(O3, O4).Font.Size = 9End Sub語吋干 审H作丁虚T ffiUJh,i z*ir J lL1N0曰WA：i32OL4HT6IX)IIIA20L2-J1-6ZJCj會910砒时1112L314界面“收支记录

41、”“收支记录”中复制代码如下：Dim ri4Dim r(0 To 1)Dim r1(0 To 1)Dim r2(0 To 1)Dim r3(0 To 1)Dim r10(0 To 1)Dim r11(0 To 1)Dim r12(0 To 1)Dim r13(0 To 1)Dim kongDim kong1Dim kong2Dim kong10Dim kong11Dim kong12Dim flag_sha nDim flag_sha n10Private Sub Cale ndar1_Click() ActiveCell = Cale ndar1Cale ndar1.Visible = Fa

42、lse End SubPrivate Sub Worksheet_Selectio nCha nge(ByVal Target As Range) ri = ri + 1 ri定义为鼠标点击次数rik = ri Mod 2 rik 为 0 或 1rns = Ran ge(B65536).E nd(xlUp).Row收入最大行号rnz = Ran ge(L65536).E nd(xlUp).Row支出最大行号If rns = rnz The nrn = rnsElsern = rnzEnd Ifcns = Range(B2).End(xlToRight).Column 最大列号 cnz = Ra

43、nge(L2).End(xlToRight).Column 最大列号格式初始化With Range(Sheet2.Cells(3, 1), Sheet2.Cells(rn + 10, cnz).Interior .Pattern = xlNone .TintAndShade = 0.PatternTintAndShade = 0End WithWith Sheet2.Range(A:N).Validation.Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .

44、IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True.ShowError = TrueEnd With收入项目列表更新rn7s = Sheet7.Range(A65536).End(xlUp).Rowf = = 收支项目 !A2:A & rn7s & With Range(B3).Validation.Delete.Add Type:=xlValida

45、teList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True.ShowError = TrueEnd With支出项目列表更新rn7z = Sheet7.Range(C65536).End(xlUp).Rowf

46、= = 收支项目 !C2:C & rn7z & With Range(L3).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With收入账户列表更新rn4 = Sheet4.Range(e65536).End(xlUp).Rowf = = 基本信息 !E5:E & rn4 & With Range(D3).Validation.Delete.Add Type:=xlValidateList, AlertSty