個人記賬工具很多，可是都不常用。excel在大多數(shù)辦公室工作者來說，人手必備。用excel制作一個簡單，但是實用的記賬表格，多維度錄入資金來源和去向，自動計算余額，核算現(xiàn)實手中現(xiàn)金金額，方便存儲，方便記錄，操作簡單，易于統(tǒng)計匯總。

一、設(shè)置最左/中區(qū)域

1、整體效果如下圖，機緣巧合，自己弄了這個，使用起來，簡潔大方。主要有2大步驟，完成制作：

1)外觀：呈現(xiàn)的樣式

2)公式：用來計算金額

2、首先新建Excel之后，先按照下圖，編輯“賬本”標題和其他內(nèi)容設(shè)計。主要分為三個區(qū)域：

最左邊：從A-H列，為記賬區(qū)（表頭分別為記錄時間-類型-金額-子類型-子類型-支付來源-支付來源-備注）

中間為記賬類型區(qū)：從I-J列，規(guī)范和快速綠如數(shù)據(jù)（子類型設(shè)置）

最右邊：從K-P列，用于各類數(shù)據(jù)統(tǒng)計并展示（當(dāng)月時間-金額-資產(chǎn)名稱-資產(chǎn)名稱-原始本金-實際資產(chǎn)）

3、設(shè)置標題樣式

1）選中A-P列后，在“開始”菜單中，將字體修改為“微軟雅黑”。

2）將字體加粗，點擊字體下方的“B”按鈕，加粗字體。

3）設(shè)置字體顏色為白色，點擊字體下方的“A”按鈕，選擇白色作為字體顏色。

4）設(shè)置填充顏色為藍色，點擊字體下方的“油桶”按鈕，選擇藍色作為背景色。

4、設(shè)置邊框線顏色為白色

點擊字體顏色下方“邊框”下拉框，選擇“線條顏色”為白色。再次點擊“邊框”下拉框，選擇“所有框線”。選中標題(A-P列)，并應(yīng)用邊框線。

5、子類型設(shè)置

1）選中I1、J1單元格，點擊“合并單元格后居中”。

2）按照下圖內(nèi)容，錄入“類型”名稱。

錄入類型，有2大好處：

-1）為快速錄入，提供基本數(shù)據(jù)。

-2）為錄入規(guī)范，提供原始數(shù)據(jù)糾正作用。

6、設(shè)置類型-支出

支出類型分為“收入”和“支出”，在輸入金額，自動判斷，避免手動錄入錯誤。

使用公式：=IF(C2<0,"支出","收入")

在B2單元格輸入以上公式，C2代表單元格C2，其所在列指向“金額”。

公式解析：如果C2單元格的值 小于 0，則輸出“支出”，否則輸出“收入”。簡單的說，如果是負數(shù)，將設(shè)置B2單元格設(shè)置為“支出”，反之“收入”

7、設(shè)置E列子類型

此處就是上面說的快速錄入，和錄入規(guī)范性，在E列作為自動錄入列，通過與D列配合完成。

使用公式：=VLOOKUP(D2,$I$2:$J$15,2,0)

公式解說：$I$2:$J$15單元格區(qū)域，超找D2單元格的內(nèi)容，精確返回$I$2:$J$15區(qū)域匹配的第二列數(shù)據(jù)。簡單的說，在C2單元格輸入“cy”之后，D2則可以利用公式輸出“餐飲”(餐飲數(shù)據(jù)來自第五步)

8、錯誤處理

針對上面公式，假如沒有在D2單元格，使用了以上公式，輸入非設(shè)置內(nèi)容，將顯示錯誤信息(#N/A)，為此在上面公式的基礎(chǔ)上，完善顯示功能。

使用公式：=IFERROR(VLOOKUP(D2,$I$2:$J$15,2,0),"")

公式解析：如果出現(xiàn)錯誤信息，將返回空白字符，否則，按照上一步規(guī)則輸出信息。

9、設(shè)置F、G列子類型

1同樣為規(guī)范錄入，使其錄入信息保持一致性，沿用上面2個步驟(步驟7、步驟8)，設(shè)置好“支付來源”。備注信息則根據(jù)實際情況，對當(dāng)日支出或收入做補充說明。

二、設(shè)置最右邊區(qū)域

1、這部分功能，主要設(shè)計：

1）預(yù)算支出，用來計劃某一段時間內(nèi)，預(yù)計需要用掉的資金，“預(yù)算剩余”對“預(yù)算支出”進行反饋。

2）當(dāng)日/月收入支出求和，利用K1單元格的時間，計算當(dāng)月總支出和總收入。

3）設(shè)置資金多維度來源，自動計算當(dāng)前各類財產(chǎn)資金和余額。

2、設(shè)置M、N列支付類型

此列對應(yīng)前面步驟9，M列為N列的拼音簡拼，和前面設(shè)置E列子類型，作用相同。

3、L3單元格-預(yù)算剩余

預(yù)算剩余有兩套思路，計算當(dāng)月的和計算全部記錄的“預(yù)算剩余”。

當(dāng)月預(yù)算剩余公式：L2-ABS(SUMIFS(C:C,B:B,"支出",A:A,">="&DATE(YEAR(K1),MONTH(K1),1),A:A,"<"&DATE(YEAR(K1),MONTH(K1)+1,1)))

表格中所有支出計算預(yù)算剩余：=L2-ABS(SUMIFS(C:C,B:B,"支出"))

函數(shù)功能解釋：

1）ABS函數(shù)將內(nèi)容取其絕對值；

2）DATE函數(shù)，聯(lián)合year和month計算出每月第一天和每月最后一天

3）SUMIFS函數(shù)求和給定條件的數(shù)值之和

簡單的說，就是用 “預(yù)算 減去 當(dāng)月(根據(jù)K1單元格確定)支出總額”得到預(yù)算剩余。

4、L4、L5單元格-今日收入/支出

根據(jù)左邊收入和支出詳情記錄信息，利用公式，自動匯總當(dāng)日收入/支出金額。

使用公式：=SUMIFS(C:C,A:A,TODAY(),B:B,"支出")

公式解析：統(tǒng)計出為“支出”的總金額。

函數(shù)功能解釋：TODAY()函數(shù)返回當(dāng)日日期。

簡單的說，通過A列篩選出日期為今天，通過B列篩選出“支出”2個條件，再統(tǒng)計出符合以上條件的所有金額總和。計算收入，則將公式修改為：=SUMIFS(C:C,A:A,TODAY(),B:B,"收入")即可

5、原始本金

此部分無需公式，設(shè)置簡單。原始本金作為第一次或這以后校準資金存在。其作用代表了當(dāng)前所有資產(chǎn)余額。已分類“現(xiàn)金，工資卡-老公，工資卡-老婆...”等多項。可根據(jù)自己的資產(chǎn)，自定義分類。

6、實際資產(chǎn)

通過資產(chǎn)名稱，關(guān)聯(lián)G列的支付來源，自動計算該資產(chǎn)，從原始本金，到目前為止全部的金額，這就是前面不停的強調(diào)錄入規(guī)范，錄入一致性的重要性。

使用公式：=O2-ABS(SUMIFS(C:C,G:G,N2,B:B,"支出"))+SUMIFS(C:C,G:G,N2,B:B,"收入")

公式解析：O2表示資產(chǎn)名稱(N2)的原始本金，減去，支付來源為N2(資產(chǎn)名稱)，子類型為“支出”的金額，并加上，該資產(chǎn)名稱的“收入”

舉例來說，現(xiàn)金的實際資產(chǎn)=現(xiàn)金的原始本金-記錄中支出為現(xiàn)金的金額+記錄總收入為現(xiàn)金的金額。

7、實際資產(chǎn)-總和

對所有實際資產(chǎn)求和，計算出總金額。

使用公式：="實際資產(chǎn)"&SUM(P2:P6)

換行：使用Alt+Enter(回車)可換行

三、資金安全設(shè)密碼

1、設(shè)置excel打開密碼，一定程度上方式資金信息被泄露，所謂防君子不防小人，設(shè)置密碼是很有必要的。點擊左上角“文件”，如此文件沒有保存過，則點擊“保存”，如果已經(jīng)保存，則應(yīng)該點擊“另存為”

2、在保存界面上，找到“工具”，并下拉選擇“常規(guī)選項”，點擊后，在彈出的界面上，在“打開權(quán)限密碼”設(shè)置上自己的密碼，進設(shè)置這一項即可，再次啟動該Excel，則會要求輸入密碼，才能打開。

3、完成。

注意事項：

個人賬本.xlsx模版下載：http://pan.baidu.com/s/1qYd4pAW

相關(guān)推薦：

Excel怎么做銀行存款和庫存現(xiàn)金做賬？

excel怎么快速計算個人社保扣繳金額？

賬務(wù)管理必會Excel應(yīng)用：Excel 工資表里錄入數(shù)據(jù)

最新評論