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

Excel收支管理系统

程序功能：

? 银行信息记录

a) 存款利息计算 b) 银行总资金汇总 c) 银行年收益计算 ? 收支记录

a) 收入项目记录，增加到银行账户 b) 支出项目记录，选择支出账户

c) 可对每条记录进行修改，并与账户关联 d) 收支项目管理，可增加或删除收支项目

本程序操作灵活，界面人性化，比如删除“银行记录”金额，可将本条记录信息全部删除（需要确认）；收支记录中信息输入完整，自动与银行账户信息关联；可自己添加银行并修改利率。

使用本程序可快速判别存款方式对收益的影响，比如5万存入工商银行：

1. 整存整取两年，利息4400

2. 整存整取一年，利息3561（两年后取）

现在银行利率也有差别，存不同银行收益相差多少也能方便了解。

界面“银行记录”

“银行记录”中复制代码如下： Private Sub Calendar1_Click() ActiveCell = Calendar1 Calendar1.Visible = False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lv

Dim 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 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 Range

rn = Range(\最大行号 = Range(\最大列号 Application.ScreenUpdating = False

'数据初始化

If Sheet1.Cells(ActiveCell.Row, 1) = \And Sheet1.Cells(ActiveCell.Row, 2) <> \ Sheet1.Cells(ActiveCell.Row, 1) = \中国银行\End If

For y = 3 To rn

If Sheet1.Cells(y, 1) <> \ Sheet4.Select '查找银行名称

Set rng = Sheet4.[B:B].Find(Sheet1.Cells(y, 1)) '定位银行

If Not rng Is Nothing Then

'rng.Font.ColorIndex = 3 '颜色暂不设置

Application.Goto Reference:=rng.Address(, , xlR1C1) End If End If

lv_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_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_ding2_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.Select Next

'-------------- '格式初始化

With Range(Sheet1.Cells(3, 1), Sheet1.Cells(rn + 30, )).Interior .Pattern = xlNone .TintAndShade = 0

.PatternTintAndShade = 0 End With

'---------------

'取消列表

With Sheet1.Range(\ .Delete

.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween

.IgnoreBlank = True .InCellDropdown = True .InputTitle = \ .ErrorTitle = \ .InputMessage = \ .ErrorMessage = \

.IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With

With Sheet1.Range(\ .Delete

.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween

.IgnoreBlank = True .InCellDropdown = True .InputTitle = \ .ErrorTitle = \ .InputMessage = \ .ErrorMessage = \

.IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With

'银行列表更新

rn4 = Sheet4.Range(\ f = \基本信息!E5:E\ With Range(\ .Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = \ .ErrorTitle = \ .InputMessage = \ .ErrorMessage = \

.IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With '项目列表

With Range(\ .Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=\活期,整存整取,整存零取,零存整取,存本取息,定活两便\

.IgnoreBlank = True .InCellDropdown = True .InputTitle = \ .ErrorTitle = \ .InputMessage = \ .ErrorMessage = \

.IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With

For Z = 3 To rn '银行列表更新

rn4 = Sheet4.Range(\ f = \基本信息!E5:E\ With Range(\ .Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True