Excel收支理财管理与财务知识分析系统方案

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

2、，存不同银行收益相差多少也能方便了解。界面“银行记录”“银行记录”中复制代码如下： Private Sub Calendar1_Click() ActiveCell = Calendar1 Calendar1.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_1

3、2(1 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 最大行号= Range(b2).End(xlToRight).Column 最大列号Application.ScreenUpdating = False 数据初始化I

4、f Sheet1.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.F

5、ont.ColorIndex = 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)

6、lv_ding1_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_din

7、g2_12(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 +

8、 30, ).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 = .Erro

9、rMessage = .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 = .ErrorMes

10、sage = .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.InCellD

11、ropdown = 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:=活期 , 整存整取 , 整存零取 , 零存整取 , 存

12、本取息 , 定活两便.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).Rowf = =基本信息 !E5:E & rn4 & With Range(A & Z + 1).Validatio

13、n.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 项目列表With Range(C & Z + 1).V

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

15、 = TrueEnd With 年利率活期 Then整存整取 ThenIf Sheet1.Cells(Z, 3) = Huo Or Sheet1.Cells(Z, 3) = lv = lv_huo(Z)Sheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = ZZ Or Sheet1.Cells(Z, 3) = 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 Sheet

16、1.Cells(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) = Sheet1.Cells(Z, 3) = 整存零取 Or Sheet1.Cells(Z, 3) = 存本取息If Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_ding2_60(Z)

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

18、36 And Sheet1.Cells(Z, 5) = 60 Thenlv = lv_ding1_60(Z) 5年End Iflv = lv * 0.6ElseSheet1.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 Thenlv = lv_ding1_60(Z) 5年End Iflv = lv * 0.6End If

19、Sheet1.Cells(Z, 6) = lvEnd IfOn Error Resume Next存入日期 And Target.Row 2 AndIf Target.Column = 4 And Target.Value Target.Row = 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(Shee

20、t1.Cells(Z, 4) * 12 + Month(Sheet1.Cells(1, 1) - Month(Sheet1.Cells(Z, 4)End 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)= Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4) Thenm =

21、(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.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

22、.Cells(1, 1) + Month(Sheet1.Cells(Z, 4) - 1End IfIf Year(Sheet1.Cells(1, 1) Year(Sheet1.Cells(Z, 4) Thenm = 0End IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) Month(Sheet1.Cells(Z, 4) Thenm = 0End IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(

23、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 / 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(R

24、C-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.Cells(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) * (

25、1 - lixishui) 第一次转存到期总数If zhuan = 2 ThenFor i = 2 To zhuanzhuancun(i) = zhuancun(i - 1) + zhuancun(i - 1) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui)Next End IfIf Int(m / Sheet1.Cells(Z, 5) = m / Sheet1.Cells(Z, 5) Then Sheet1.Cells(Z, 11).FormulaR1C1 = _ =DATE(YEAR(RC-7),MONTH(RC-7)+R

26、C-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) - 转存到期日期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

27、) = zhuancun(zhuan) -当前总额当前Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 12) - Sheet1.Cells(Z, 2) - 利息End If 格式If Z Mod 2 = 1 Then 当前行数除以 2 的余数为 1(奇数)With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior .Pattern = xlSolid.PatternColorIndex = xlAutomatic .Color = 10198015.TintAndShade = 0 .PatternTint

28、AndShade = 0End WithElseIf Z Mod 2 = 0 Then 当前行数除以 2的余数为 0(偶数) With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.Color = 13421823.TintAndShade = 0.PatternTintAndShade = 0End WithEnd If 未选择银行不计利率If Sheet1.Cells(Z, 1) = Then lv = 0Sheet1.Cell

29、s(Z, 6) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 9) = End If 活期不考虑到期时间，按存入时间到当前日期利息计算If Sheet1.Cells(Z, 3) = 活期 Thentian = Date - 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)S

30、heet1.Cells(Z, 9) = Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 5) = Sheet1.Cells(Z, 6) = lvSheet1.Cells(Z, 8) = 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) = ThenSheet1.Ce

31、lls(Z, 9) = Sheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11) = Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 2)End If 到期提示If Sheet1.Cells(Z, 8) Sheet1.Cells(1, 1) ThenWith Sheet1.Cells(Z, 8).Font.Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.C

32、ells(Z, 8).Font.ThemeColor = xlThemeColorLight1 .TintAndShade = 0End WithEnd IfIf Sheet1.Cells(Z, 11) Sheet1.Cells(1, 1) ThenWith Sheet1.Cells(Z, 11).Font.Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.Cells(Z, 11).Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0End WithEnd IfNextFor

33、 j = 3 To rn + 1000提示 )If Sheet1.Cells(j, 2) = And Sheet1.Cells(j, 3) Then sc = MsgBox(确认删除该项目？ , vbYesNo, If sc = vbYes ThenSheet1.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.

34、Cells(j, 10) = Sheet1.Cells(j, 11) = Sheet1.Cells(j, 12) = Sheet1.Cells(j, 13) = Sheet1.Cells(j, 14) = End IfRows(j).DeleteEnd IfNext 图表数据处理 图表 1 ，饼图，显示各银行资金分布Sheet6.Columns(A:B).ClearSheet6.Cells(1, 1) = 图表 1zong = 0 ben = 0 nianli = 0For K = 3 To rnrn1 = Sheet6.Range(a65536).End(xlUp).RowIf rn1 =

35、2 ThenActiveSheet.ChartObjects( 图表 1).ActivateActiveChart.SetSourceData Source:=Sheet6.Range(A2:A & rn1 & , B2:B & rn1 & )Sheet1.Cells(r, c).Select 设置绘图区格式图表 1)With Sheets(1).ChartObjects( .Name .Left .Top .Height = 250 .Width .Chart.ChartTypeEnd WithEnd IfWith Range(O3, O4) .HorizontalAlignment = x

36、lRight .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = FalseEnd WithWith Range(O3).Font .Color = -16751104 .TintAndShade = 0End WithWith Range(O4).Font .Color = -3368704 .TintAndShade =

37、0End WithRange(O3, O4).Font.Size = 9End Sub界面“收支记录”“收支记录”中复制代码如下：Dim riDim 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_shanDim flag_shan10Private Sub Calendar1_Click()A

38、ctiveCell = Calendar1 Calendar1.Visible = False End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) ri = ri + 1 ri定义为鼠标点击次数rik = ri Mod 2 rik为 0 或 1rns = Range(B65536).End(xlUp).Row 收入最大行号rnz = Range(L65536).End(xlUp).Row 支出最大行号If rns = rnz Thenrn = rnsElsern = rnz最大列号最大列号End Ifcns =

39、 Range(B2).End(xlToRight).Columncnz = Range(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:=xlValidA

40、lertStop, Operator _ :=xlBetween.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).Valid

41、ation.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支出项目列表更新rn7z = Sheet7.Ra

42、nge(C65536).End(xlUp).Row f = = 收支项目 !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 = xlIMEModeNoC

43、ontrol.ShowInput = True.ShowError = TrueEnd With 收入账户列表更新rn4 = Sheet4.Range(e65536).End(xlUp).Rowf = = 基本信息 !E5:E & rn4 & With Range(D3).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle =

44、 .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With 支出账户列表更新rn4 = Sheet4.Range(e65536).End(xlUp).Rowf = = 基本信息 !E5:E & rn4 & With Range(n3).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 WithFor Z =