Excel收支理財管理及財務知識分析系統(tǒng)

1、excel收支管理系統(tǒng)程序功能：l 銀行信息記錄a) 存款利息計算b) 銀行總資金匯總c) 銀行年收益計算l 收支記錄a) 收入項目記錄，增加到銀行賬戶b) 支出項目記錄，選擇支出賬戶c) 可對每條記錄進行修改，并與賬戶關聯(lián)d) 收支項目管理，可增加或刪除收支項目本程序操作靈活，界面人性化，比如刪除“銀行記錄”金額，可將本條記錄信息全部刪除（需要確認）；收支記錄中信息輸入完整，自動與銀行賬戶信息關聯(lián)；可自己添加銀行并修改利率。使用本程序可快速判別存款方式對收益的影響，比如5萬存入工商銀行：1. 整存整取兩年，利息44002. 整存整取一年，利息3561（兩年后?。┈F(xiàn)在銀行利率也有差別，存不同銀

2、行收益相差多少也能方便了解。界面“銀行記錄”“銀行記錄”中復制代碼如下：private sub calendar1_click()activecell = calendar1calendar1.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 to 1

3、000)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.

4、screenupdating = false'數(shù)據(jù)初始化if sheet1.cells(activecell.row, 1) = "" and sheet1.cells(activecell.row, 3) = "" and sheet1.cells(activecell.row, 2) <> "" then sheet1.cells(activecell.row, 1) = "中國銀行"end iffor y = 3 to rn if sheet1.cells(y, 1) <> &

5、quot;" then 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)

6、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_3

7、6(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)

8、 = sheet4.cells(activecell.row + 15, activecell.column + 1) '返回sheet“銀行項目” sheet1.selectnext'-'格式初始化with range(sheet1.cells(3, 1), sheet1.cells(rn + 30, cn).interior .pattern = xlnone .tintandshade = 0 .patterntintandshade = 0end with'-'取消列表 with sheet1.range("a:a").val

9、idation .delete .add type:=xlvalidateinputonly, alertstyle:=xlvalidalertstop, operator _ :=xlbetween .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitle = "" .inputmessage = "" .errormessage = "" .imemode = xlimemodenocontrol .showinput = t

10、rue .showerror = true end with with sheet1.range("c:c").validation .delete .add type:=xlvalidateinputonly, alertstyle:=xlvalidalertstop, operator _ :=xlbetween .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitle = "" .inputmessage = "" .err

11、ormessage = "" .imemode = xlimemodenocontrol .showinput = true .showerror = true end with '銀行列表更新 rn4 = sheet4.range("e65536").end(xlup).row f = "=基本信息!e5:e" & rn4 & "" with range("a3").validation .delete .add type:=xlvalidatelist, alerts

12、tyle:=xlvalidalertstop, operator:= _ xlbetween, formula1:=f .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitle = "" .inputmessage = "" .errormessage = "" .imemode = xlimemodenocontrol .showinput = true .showerror = true end with'項目列表 w

13、ith range("c3").validation .delete .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _ xlbetween, formula1:="活期,整存整取,整存零取,零存整取,存本取息,定活兩便" .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitle = "" .inputmessage = ""

14、; .errormessage = "" .imemode = xlimemodenocontrol .showinput = true .showerror = true end withfor z = 3 to rn '銀行列表更新 rn4 = sheet4.range("e65536").end(xlup).row f = "=基本信息!e5:e" & rn4 & "" with range("a" & z + 1).validation .delete .

15、add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _ xlbetween, formula1:=f .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitle = "" .inputmessage = "" .errormessage = "" .imemode = xlimemodenocontrol .showinput = true .show

16、error = true end with '項目列表 with range("c" & z + 1).validation .delete .add type:=xlvalidatelist, alertstyle:=xlvalidalertstop, operator:= _ xlbetween, formula1:="活期,整存整取,整存零取,零存整取,存本取息,定活兩便" .ignoreblank = true .incelldropdown = true .inputtitle = "" .errortitl

17、e = "" .inputmessage = "" .errormessage = "" .imemode = xlimemodenocontrol .showinput = true .showerror = true end with '- '年利率 if sheet1.cells(z, 3) = "huo" or sheet1.cells(z, 3) = "活期" then lv = lv_huo(z) sheet1.cells(z, 6) = lv end if if s

18、heet1.cells(z, 3) = "zz" or sheet1.cells(z, 3) = "整存整取" then if sheet1.cells(z, 5) < 6 then lv = lv_ding1_3(z) '3個月 end if if sheet1.cells(z, 5) >= 6 and sheet1.cells(z, 5) < 12 then lv = lv_ding1_6(z) '半年 end if if sheet1.cells(z, 5) >= 12 and sheet1.cells(z,

19、 5) < 24 then lv = lv_ding1_12(z) '1年 end if if sheet1.cells(z, 5) >= 24 and sheet1.cells(z, 5) < 36 then lv = lv_ding1_24(z) '2年 end if if sheet1.cells(z, 5) >= 36 and sheet1.cells(z, 5) < 60 then lv = lv_ding1_36(z) '3年 end if if sheet1.cells(z, 5) >= 60 then lv = lv_

20、ding1_60(z) '5年 end if sheet1.cells(z, 6) = lv end if if sheet1.cells(z, 3) = "zl lz bx" or sheet1.cells(z, 3) = "零存整取" or sheet1.cells(z, 3) = "整存零取" or sheet1.cells(z, 3) = "存本取息" then if sheet1.cells(z, 5) >= 12 and sheet1.cells(z, 5) < 36 then lv

21、 = lv_ding2_12(z) '1年 end if if sheet1.cells(z, 5) >= 36 and sheet1.cells(z, 5) < 60 then lv = lv_ding2_36(z) '3年 end if if sheet1.cells(z, 5) >= 60 then lv = lv_ding2_60(z) '5年 end if sheet1.cells(z, 6) = lv end if if sheet1.cells(z, 3) = "定活兩便" then if sheet1.cells(z

22、, 4) = "" and sheet1.cells(z, 5) = "" then dh = msgbox("未區(qū)分各家銀行計算方法，結果不一定準確，按利率60%計算？", vbyesno, "提示") if dh = vbyes then if sheet1.cells(z, 5) < 6 then lv = lv_ding1_3(z) '3個月 end if if sheet1.cells(z, 5) >= 6 and sheet1.cells(z, 5) < 12 then lv

23、= lv_ding1_6(z) '半年 end if if sheet1.cells(z, 5) >= 12 and sheet1.cells(z, 5) < 24 then lv = lv_ding1_12(z) '1年 end if if sheet1.cells(z, 5) >= 24 and sheet1.cells(z, 5) < 36 then lv = lv_ding1_24(z) '2年 end if if sheet1.cells(z, 5) >= 36 and sheet1.cells(z, 5) < 60 the

24、n lv = lv_ding1_36(z) '3年 end if if sheet1.cells(z, 5) >= 60 then lv = lv_ding1_60(z) '5年 end if lv = lv * 0.6 else sheet1.cells(z, 3) = "" end if else if sheet1.cells(z, 5) < 6 then lv = lv_ding1_3(z) '3個月 end if if sheet1.cells(z, 5) >= 6 and sheet1.cells(z, 5) <

25、12 then lv = lv_ding1_6(z) '半年 end if if sheet1.cells(z, 5) >= 12 and sheet1.cells(z, 5) < 24 then lv = lv_ding1_12(z) '1年 end if if sheet1.cells(z, 5) >= 24 and sheet1.cells(z, 5) < 36 then lv = lv_ding1_24(z) '2年 end if if sheet1.cells(z, 5) >= 36 and sheet1.cells(z, 5)

26、< 60 then lv = lv_ding1_36(z) '3年 end if if sheet1.cells(z, 5) >= 60 then lv = lv_ding1_60(z) '5年 end if lv = lv * 0.6 end if sheet1.cells(z, 6) = lv end if '- on error resume next if target.column = 4 and target.value <> "存入日期" and target.row > 2 and target.row

27、<= rn then calendar1.visible = true '日歷控件 calendar1.left = cells(target.row, 4).left calendar1.top = cells(target.row + 1, 4).top with calendar1 '當前日期 .year = year(now) .month = month(now) .day = day(now) end with elseif target.column = 1 and target.row = 1 then calendar1.visible = true &

28、#39;日歷控件 calendar1.left = cells(target.row, 1).left calendar1.top = cells(target.row + 1, 1).top with calendar1 '當前日期 .year = year(now) .month = month(now) .day = day(now) end with else calendar1.visible = false end if on error resume next nian = int(sheet1.cells(z, 5) / 12) '年數(shù) yue = sheet1

29、.cells(z, 5) - 12 * nian '月數(shù) lixishui = 0 '利息稅率 daoqi = sheet1.cells(z, 2) + sheet1.cells(z, 2) * (lv / 100) * (sheet1.cells(z, 5) / 12) * (1 - lixishui) '到期總數(shù) '當前日期與存入日期相差月數(shù) if year(sheet1.cells(1, 1) >= year(sheet1.cells(z, 4) and month(sheet1.cells(1, 1) >= month(sheet1.cell

30、s(z, 4) and day(sheet1.cells(1, 1) >= day(sheet1.cells(z, 4) then m = (year(sheet1.cells(1, 1) - year(sheet1.cells(z, 4) * 12 + month(sheet1.cells(1, 1) - month(sheet1.cells(z, 4) end if if year(sheet1.cells(1, 1) >= year(sheet1.cells(z, 4) and month(sheet1.cells(1, 1) >= month(sheet1.cells

31、(z, 4) and day(sheet1.cells(1, 1) < day(sheet1.cells(z, 4) then m = (year(sheet1.cells(1, 1) - year(sheet1.cells(z, 4) * 12 + month(sheet1.cells(1, 1) - month(sheet1.cells(z, 4) - 1 end if if year(sheet1.cells(1, 1) >= year(sheet1.cells(z, 4) and month(sheet1.cells(1, 1) < month(sheet1.cell

32、s(z, 4) and day(sheet1.cells(1, 1) >= day(sheet1.cells(z, 4) then m = (year(sheet1.cells(1, 1) - year(sheet1.cells(z, 4) * 12 - month(sheet1.cells(1, 1) + month(sheet1.cells(z, 4) end if if year(sheet1.cells(1, 1) >= year(sheet1.cells(z, 4) and month(sheet1.cells(1, 1) < month(sheet1.cells(

33、z, 4) and day(sheet1.cells(1, 1) >= day(sheet1.cells(z, 4) then m = (year(sheet1.cells(1, 1) - year(sheet1.cells(z, 4) * 12 - month(sheet1.cells(1, 1) + month(sheet1.cells(z, 4) - 1 end if if year(sheet1.cells(1, 1) < year(sheet1.cells(z, 4) then m = 0 end if if year(sheet1.cells(1, 1) = year(

34、sheet1.cells(z, 4) and month(sheet1.cells(1, 1) < month(sheet1.cells(z, 4) then m = 0 end if if 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) then m = 0 end if '- zhuan = int(m /

35、 sheet1.cells(z, 5) - 1 '轉存次數(shù) if zhuan < 0 then zhuan = 0 end if '數(shù)據(jù)寫入 if z > 2 and sheet1.cells(z, 6) <> "" then sheet1.cells(z, 7) = daoqi '-到期總數(shù) if sheet1.cells(z, 4) <> "" then sheet1.cells(z, 8).formular1c1 = _ "=date(year(rc-4),month(rc-4)

36、+rc-3,day(rc-4)" '-到期日期 end if end if if sheet1.cells(z, 7) <> "" then sheet1.cells(z, 9) = (sheet1.cells(z, 7) - sheet1.cells(z, 2) '-利息 else sheet1.cells(z, 9) = "" end if sheet1.cells(z, 10) = zhuan '-轉存次數(shù) zhuancun(1) = sheet1.cells(z, 7) + sheet1.cells(

37、z, 7) * (lv / 100) * (sheet1.cells(z, 5) / 12) * (1 - lixishui) '第一次轉存到期總數(shù) if zhuan >= 2 then for i = 2 to zhuan zhuancun(i) = zhuancun(i - 1) + zhuancun(i - 1) * (lv / 100) * (sheet1.cells(z, 5) / 12) * (1 - lixishui) next end if if int(m / sheet1.cells(z, 5) = m / sheet1.cells(z, 5) then sh

38、eet1.cells(z, 11).formular1c1 = _ "=date(year(rc-7),month(rc-7)+rc-6*(rc-1+1),day(rc-7)" '-轉存到期日期 else sheet1.cells(z, 11).formular1c1 = _ "=date(year(rc-7),month(rc-7)+rc-6*(rc-1+1),day(rc-7)" '-轉存到期日期 end if if zhuan < 1 then sheet1.cells(z, 10) = "" sheet1

39、.cells(z, 11) = "" sheet1.cells(z, 12) = sheet1.cells(z, 7) sheet1.cells(z, 13) = sheet1.cells(z, 9) else sheet1.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 '當前行數(shù)除以2的余數(shù)為1（奇

40、數(shù)） with range(sheet1.cells(z, 1), sheet1.cells(z, 14).interior .pattern = xlsolid .patterncolorindex = xlautomatic .color = 10198015 .tintandshade = 0 .patterntintandshade = 0 end with elseif z mod 2 = 0 then '當前行數(shù)除以2的余數(shù)為0（偶數(shù)） with range(sheet1.cells(z, 1), sheet1.cells(z, 14).interior .pattern

41、= xlsolid .patterncolorindex = xlautomatic .color = 13421823 .tintandshade = 0 .patterntintandshade = 0 end with end if '- '未選擇銀行不計利率 if sheet1.cells(z, 1) = "" then lv = 0 sheet1.cells(z, 6) = "" sheet1.cells(z, 7) = "" sheet1.cells(z, 9) = "" end if

42、'活期不考慮到期時間，按存入時間到當前日期利息計算 if sheet1.cells(z, 3) = "活期" then tian = date - sheet1.cells(z, 4) nian1 = int(tian / 365) '年數(shù) yue1 = sheet1.cells(z, 5) - 12 * nian '月數(shù) sheet1.cells(z, 7) = sheet1.cells(z, 2) + sheet1.cells(z, 2) * lv / 100 * (tian / 365) sheet1.cells(z, 9) = sheet1.

43、cells(z, 7) - sheet1.cells(z, 2) sheet1.cells(z, 5) = "" sheet1.cells(z, 6) = lv sheet1.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

44、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) sheet1.cells(z, 12) = sheet1.cells(z, 2) end if '到期提示 if sheet1.cells(z, 8) &l

45、t; sheet1.cells(1, 1) then with sheet1.cells(z, 8).font .color = -65281 .tintandshade = 0 end with else with sheet1.cells(z, 8).font .themecolor = xlthemecolorlight1 .tintandshade = 0 end with end if if sheet1.cells(z, 11) < sheet1.cells(1, 1) then with sheet1.cells(z, 11).font .color = -65281 .t

46、intandshade = 0 end with else with sheet1.cells(z, 11).font .themecolor = xlthemecolorlight1 .tintandshade = 0 end with end ifnext'-for j = 3 to rn + 1000 if sheet1.cells(j, 2) = "" and sheet1.cells(j, 3) <> "" then sc = msgbox("確認刪除該項目？", vbyesno, "提示&qu

47、ot;) if sc = vbyes then sheet1.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) = "" shee

48、t1.cells(j, 10) = "" sheet1.cells(j, 11) = "" sheet1.cells(j, 12) = "" sheet1.cells(j, 13) = "" sheet1.cells(j, 14) = "" end if rows(j).delete end ifnext'圖表數(shù)據(jù)處理'圖表1，餅圖，顯示各銀行資金分布sheet6.columns("a:b").clearsheet6.cells(1, 1) = "圖

49、表1"zong = 0ben = 0nianli = 0for k = 3 to rn rn1 = sheet6.range("a65536").end(xlup).row if rn1 < 2 then rn1 = 2 end if flag = 0 for k1 = 3 to rn1 if sheet1.cells(k, 1) = sheet6.cells(k1, 1) then flag = flag + 1 sheet6.cells(k1, 2) = sheet6.cells(k1, 2) + sheet1.cells(k, 12) end if n

50、ext if flag = 0 then sheet6.cells(rn1 + 1, 1) = sheet1.cells(k, 1) sheet6.cells(rn1 + 1, 2) = sheet1.cells(k, 12) end if zong = zong + sheet1.cells(k, 12) ben = ben + sheet1.cells(k, 2) nianli = nianli + (sheet1.cells(k, 13) / (sheet1.cells(k, 5) + sheet1.cells(k, 5) * sheet1.cells(k, 10) * 12nextsh

51、eet1.cells(1, 15) = "銀行總額:" & format(zong, "#,#0.0")sheet1.cells(2, 15) = application.text(format(zong, "#0"), "dbnum2") '大寫sheet1.cells(3, 15) = "本金：" & application.text(format(ben, "#0"), "dbnum2") '大寫sheet1.cells(4, 15) = "年收益：" & application.text(format(nianli, "#0"), "dbnum1") & "" & format(nianli, &qu