EXCEL在會計與財務(wù)管理中的應(yīng)用-會計賬簿

EXCEL在會計與財務(wù)管理中的應(yīng)用會計賬簿任務(wù)一科目匯總表任務(wù)二科目余額表任務(wù)四明細(xì)分類賬任務(wù)五設(shè)計導(dǎo)航頁面任務(wù)三編制總分類賬

憑證庫就相當(dāng)于整個賬務(wù)處理系統(tǒng)的數(shù)據(jù)中心，它為賬簿和報表提供了所需的數(shù)據(jù)。從Excel應(yīng)用的角度來看，賬簿和報表只是用不同的分析角度來看待憑證庫數(shù)據(jù)。本章介紹了如何創(chuàng)建科目匯總表、科目余額表、總分類賬、明細(xì)分類賬以及導(dǎo)航頁面的設(shè)計等內(nèi)容。項目三會計賬簿【知識準(zhǔn)備】

Excel中有一個MicrosoftQuery組件，該組件的作用相當(dāng)于數(shù)據(jù)庫的查詢分析器，能夠通過該組件從數(shù)據(jù)庫中提取數(shù)據(jù)并顯示到工作表中。雖然Excel并不是真正意義上的數(shù)據(jù)庫，但是MicrosoftQuery組件依舊能夠從指定的工作簿（包括當(dāng)前正在使用的工作簿）中提取數(shù)據(jù)。如果把“科目匯總表”工作表當(dāng)作數(shù)據(jù)表看待，就可以解決會計科目的來源問題，如果把“憑證庫”工作表當(dāng)數(shù)據(jù)表來看，就可以解決金額來源的問題。任務(wù)一科目匯總表【任務(wù)目標(biāo)】1.制作科目匯總表；2.完成科目匯總表數(shù)據(jù)。任務(wù)一科目匯總表【任務(wù)資料】科目匯總表包括的內(nèi)容:（1）日期信息。（2）科目代碼。（3）科目名稱。（4）指定月借方發(fā)生額。（5）指定月貸方發(fā)生額。任務(wù)一科目匯總表2022年1月科目匯總表年份：2022月份：1科目代碼總賬科目名稱借方發(fā)生額貸方發(fā)生額

【任務(wù)操作】

一、科目匯總表界面1.在“賬務(wù)處理.xlsm”中新建一張工作表，將其重命名為“科目匯總表”。2.選中A1:D1單元格區(qū)域，單擊“開始”選項卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，將指定的內(nèi)容合并居中，然后在A1單元格中輸入“=B2&"年"&D2&"月科目匯總表"”，完成表頭標(biāo)題的設(shè)置。3.從A2單元格開始輸入如圖3-1所示的內(nèi)容，完成科目匯總表界面的設(shè)置。【任務(wù)操作】

一、科目匯總表界面【任務(wù)操作】二、科目匯總表數(shù)據(jù)1.選中A4單元格，選中“數(shù)據(jù)”選項卡，在“獲取外部數(shù)據(jù)”組中，執(zhí)行“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-2所示的“選擇數(shù)據(jù)源”對話框?！救蝿?wù)操作】二、科目匯總表數(shù)據(jù)2.在如圖3-2所示的“選擇數(shù)據(jù)源”對話框中，選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開“選擇工作簿”對話框。3.在驅(qū)動器中選擇D盤，在目錄中選擇“data正文”，數(shù)據(jù)庫的名稱就指定為“賬務(wù)處理.xlsm”，單擊“確定”按鈕，打開“查詢向?qū)?選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”號，展開該表所包含的列，選中要顯示的列為科目代碼、總賬科目、借金額和貸金額。【任務(wù)操作】二、科目匯總表數(shù)據(jù)4.單擊“下一步”按鈕，完成要顯示的列的選擇，進入“查詢向?qū)?篩選數(shù)據(jù)”對話框，如圖3-5所示。【任務(wù)操作】二、科目匯總表數(shù)據(jù)5.單擊“下一步”按鈕，跳過篩選的過程，打開如圖3-6所示的“查詢向?qū)?排序順序”對話框，在該步驟中不需要設(shè)置任何內(nèi)容?！救蝿?wù)操作】二、科目匯總表數(shù)據(jù)6.單擊“下一步”按鈕，打開“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中，選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”,單擊“完成”按鈕。7.在MicrosoftQuery窗口中，選中“科目代碼”列中任意一個數(shù)據(jù)，執(zhí)行“記錄”菜單下的“編輯列”命令，打開“編輯列”對話框，將“字段”下的內(nèi)容更改為“Left(科目代碼,4)”，單擊“確定”按鈕，完成列的設(shè)置?！局R準(zhǔn)備】科目余額表是本期所有會計科目的發(fā)生額和余額的表格，它反映了某一會計期間相關(guān)會計科目的期初余額、本期發(fā)生額和期末余額。與科目匯總表類似，科目余額表反映的是各個科目的余額，但是各個月份的會計科目并不是固定的，部分明細(xì)科目會隨著企業(yè)業(yè)務(wù)的發(fā)展而逐漸加入其中。因此，直接使用公式引用科目代碼表并不是一個很好的選擇。上節(jié)內(nèi)容己經(jīng)介紹了一種導(dǎo)入變動數(shù)據(jù)的方法，那就是利用MicrosoftQuery組件。用戶通過MicrosoftQuery組件將“科目代碼表”中的科目導(dǎo)入工作表后，只需要單擊刷新。則不論什么樣的會計科目總能夠出現(xiàn)在工作表中，并且這些數(shù)據(jù)是和科目代碼表中的數(shù)據(jù)完全一致的。同樣，科目余額表也會遇到余額的處理問題?？颇坑囝~表中涉及的科目余額計算包括期初余額、本期發(fā)生額和期末余額。本節(jié)中，企業(yè)從年初開始建賬，因此期初余額就是年初數(shù)，本期發(fā)生額是指定期限中的累計借方和累計貸方的發(fā)生額，期末余額則是根據(jù)期初余額和本期發(fā)生額計算的結(jié)果。任務(wù)二科目余額表【任務(wù)目標(biāo)】1.制作科目余額表；2.導(dǎo)入科目代碼；3.完成科目余額。任務(wù)二科目余額表【任務(wù)資料】科目余額表需要實現(xiàn)的功能包括：（1）期初各個會計科目的借方和貸方余額。（2）當(dāng)期各會計科目的借方和貸方發(fā)生額。（3）計算本期期末各個會計科目的借方和貸方余額。任務(wù)二科目余額表【任務(wù)操作】一、科目余額表界面1.新建工作表，將工作表重命名為“科目余額表”。2.選中E1:G1單元格區(qū)域，執(zhí)行“開始”選項卡“對齊方式”組中的“合并后居中”命令，設(shè)置字體為“仿宋_GB2312”，字號為24，并設(shè)置雙下畫線，完成字體字號的設(shè)置。3.選中E1單元格，在編輯欄內(nèi)輸入公式“=B2&"年"&G2&"月科目余額表"”月科目余額表，完成科目余額表標(biāo)題的設(shè)置。4.從第2行開始輸入如圖3-20所示的科目余額表中的各項。【任務(wù)操作】二、導(dǎo)入科目代碼1.選中“數(shù)據(jù)”選項卡，在“獲取外部數(shù)據(jù)”組中，執(zhí)行“自其他來源”中的“來自MicrosoftQuery”命令，打開“選擇數(shù)據(jù)源”對話框。2.在“選擇數(shù)據(jù)源”對話框中，選擇“數(shù)據(jù)庫類型”為“ExcelFiles*”,單擊“確定”按鈕，打開“選擇工作簿”對話框。【任務(wù)操作】三、科目余額1.選中E6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=1,[@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2)-SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2)),0)”,完成期初余額的計算。2.選中F6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=-1,ABS([@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2)-SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],"<"&$G$2))),0)”，完成期初貸方余額的計算。【任務(wù)操作】【任務(wù)操作】

1.選中“科目余額表”,選中“開發(fā)工具”選項卡，執(zhí)行“控件”組中的“表單控件”，選擇“按鈕控件”，在J1單元格內(nèi)拖出一個命令按鈕，將命令按鈕內(nèi)的文字更改為“刷新”，如圖3-30所示。完成命令按鈕的放置?！救蝿?wù)操作】

2.選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組內(nèi)的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新科目”，如圖3-31所示，單擊“確定”按鈕，開始錄制宏。【任務(wù)操作】

3.選中A6單元格，右擊，執(zhí)行“刷新”命令。單擊“開發(fā)工具”選項卡的“代碼組”內(nèi)的“停止錄制”命令，完成宏的錄制。4.在“刷新”數(shù)據(jù)按鈕上右擊，執(zhí)行“指定宏”命令，打開“指定宏”對話框，選擇“刷新科目”項，單擊“確定”按鈕，完成指定宏的設(shè)置。假設(shè)在“科目代碼表”中添加1個科目代碼為122103的其他應(yīng)收款的子科目，在科目代碼表中添加完成后的情況如圖3-32所示?！救蝿?wù)操作】

【任務(wù)操作】

【知識準(zhǔn)備】

總分類賬就是我們通常所稱的總賬，它是根據(jù)總分類科目開設(shè)賬戶，用來登記全部經(jīng)濟業(yè)務(wù)，進行總分類核算，提供總括核算資料的分類賬簿?？偡诸愘~提供了編制會計報表的主要依據(jù)，是所有單位都必須設(shè)立的賬簿?？偡诸愘~全面且總括地反映了業(yè)務(wù)單位的財務(wù)收支和經(jīng)濟活動情況?？偡诸愘~的結(jié)構(gòu)本身并不復(fù)雜，使用Excel進行設(shè)計的時候，只需要更改日期信息和會計科目，就能夠得到期初余額、當(dāng)期的借方發(fā)生額和貸方發(fā)生額以及期末余額，但是并不反映具體的業(yè)務(wù)信息。日期信息是由用戶手工更改的，會計科目信息可以通過數(shù)據(jù)驗證的方式交由用戶選擇而不是直接輸入從而避免錯誤，對于各種余額和發(fā)生額則都是通過函數(shù)從憑證庫中提取的。任務(wù)三編制總分類賬【任務(wù)目標(biāo)】1.制作總分類賬；2.完成總分類賬數(shù)據(jù)。任務(wù)三編制總分類賬【任務(wù)資料】要求根據(jù)已經(jīng)完成的科目余額表中的期初數(shù)和憑證庫中的憑證數(shù)據(jù)編制總分類賬。任務(wù)三編制總分類賬【任務(wù)操作】

一、總分類賬界面1.新建一張工作表，將工作表名稱更改為“總分類賬”,選中D1?E1單元格，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，“字體”為“仿宋_GB2312”，在D1單元格內(nèi)輸入“總分類賬”,完成總分類賬標(biāo)題的設(shè)置。2.從A2單元格開始輸入如圖3-34所示的表格內(nèi)容?！救蝿?wù)操作】

一、總分類賬界面3.選中C3單元格，選中“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗證”按鈕，打開如圖3-35所示的“數(shù)據(jù)驗證”對話框，在“允許”下拉列表中選擇“序列”，在“來源”下的文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)驗證設(shè)置。【任務(wù)操作】

一、總分類賬界面4.選中F3單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=4,VLOOKUP($C$3,kmdm,2,FALSE),(VLOOKUP(LEFT($C$3,4)+0,kmdm,2,FALSE))&"-"&VLOOKUP($C$3,kmdm,2,FALSE))”，完成會計科目名稱的設(shè)置。將年份信息設(shè)置為2022，月份信息設(shè)置為1，科目代碼設(shè)置為1001，最終顯示的科目代碼?！救蝿?wù)操作】二、總分類賬數(shù)據(jù)1.選中C2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!B2”，選中F2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!G2”，選中A4單元格，在編輯欄內(nèi)輸入公式“=C2&"年"”，完成年份和月份信息的設(shè)置。2.選中A6單元格，在編輯欄內(nèi)輸入公式“=$F$2”,按回車鍵確認(rèn)，然后將A6單元格的內(nèi)容向下拖曳到A7和A8單元格內(nèi)，完成月份信息的填制。3.在B6單元格內(nèi)輸入數(shù)值1，選中B7單元格，在編輯欄內(nèi)輸入公式“=DAY(DATE($C$2,$F$2+1,0))”，計算指定月份的天數(shù)，選中B8單元格，在編輯欄內(nèi)輸入公式“=B7”，完成日信息的填制。【任務(wù)操作】三、優(yōu)化顯示1.選中G7:G8單元格，右擊，執(zhí)行“設(shè)置單元格格式”命令，打開如圖3-41所示的“設(shè)置單元格格式”對話框。選擇“數(shù)字”選項卡，在分類中選擇“自定義”,在類型中輸入“#,##0.00;#,##0.00”，單擊“確定”按鈕，完成數(shù)值顯示方式的設(shè)定?！救蝿?wù)操作】三、優(yōu)化顯示【任務(wù)操作】三、優(yōu)化顯示2.選中整張工作表，右擊，執(zhí)行“設(shè)置單元格格式”命令，選中“保護”選項卡，保持“鎖定”被勾選，單擊“確定”按鈕，完成全部單元格的鎖定操作。3.選中C3單元格，右擊，執(zhí)行“設(shè)置單元格格式”命令，選中“保護”選項卡，保持“鎖定”不被勾選，單擊“確定”按鈕，完成可編輯單元格的指定操作。4.單擊“開始”選項卡，執(zhí)行“單元格”組內(nèi)“格式”下的“保護工作表”命令，打開“保護工作表”對話框，設(shè)置一個合適的密碼，完成工作表的保護操作?！局R準(zhǔn)備】

用戶從總分類賬中得到的僅僅是某個會計科目在指定期間內(nèi)總的發(fā)生額，但是該科目到底發(fā)生了什么業(yè)務(wù)并不明確，這就要求使用明細(xì)分類賬來顯示具體的業(yè)務(wù)信息。明細(xì)分類賬是根據(jù)明細(xì)分類賬戶進行分類登記的賬簿，是根據(jù)單位開展經(jīng)濟管理的需要對經(jīng)濟業(yè)務(wù)的詳細(xì)內(nèi)容進行的核算，是對總分類賬進行的補充反映。任務(wù)四明細(xì)分類賬【任務(wù)目標(biāo)】1.制作明細(xì)分類賬；2.完成明細(xì)分類賬數(shù)據(jù)。任務(wù)四明細(xì)分類賬【任務(wù)資料】明細(xì)分類賬要實現(xiàn)如下幾個功能：（1）列出指定月份和科目代碼的期初數(shù)。（2）列出本期指定會計科目的發(fā)生記錄，并將這些記錄填到表中。（3）計算本期期末的借方和貸方余額。任務(wù)四明細(xì)分類賬【任務(wù)操作】

一、明細(xì)分類賬界面1.新建一張工作表，將工作表名稱更改為“明細(xì)分類賬”。選中A1?H1單元格，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，在編輯欄內(nèi)輸入公式“=F3&"明細(xì)分類賬"”，完成明細(xì)分類賬標(biāo)題的設(shè)置。2.從A2單元格開始設(shè)置如圖3-43所示的內(nèi)容?！救蝿?wù)操作】

一、總分類賬界面3.選中C3單元格，選中“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗證”按鈕，打開如圖3-44所示的“數(shù)據(jù)驗證”對話框，將“允許”下拉列表中選擇“序列”，在“來源”下的文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)有效性設(shè)置。【任務(wù)操作】

二、導(dǎo)入業(yè)務(wù)信息1.選中“數(shù)據(jù)”選項卡，在“獲取外部數(shù)據(jù)”組中執(zhí)行“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-45所示的“選擇數(shù)據(jù)源”對話框。2.在“選擇數(shù)據(jù)源”對話框中選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-46所示的“選擇工作簿”對話框?！救蝿?wù)操作】3.選擇本工作簿所在的路徑，單擊“確定”按鈕，打開如圖3-47所示的“查詢向?qū)?選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”號，展開該表所包含的列，選中要顯示的列為月、日、憑證號、摘要、借金額和貸金額?！救蝿?wù)操作】三、添加匯總行1.選中A8單元格，選中“設(shè)計”選項卡，執(zhí)行“表樣式選項”組內(nèi)的“匯總行”命令，完成添加“匯總行”的命令。2.刪除“月”字段所在列中的“匯總”字樣，在匯總行中，在“摘要”字段所在的單元格內(nèi)輸入文字“本月合計”。3.在“匯總”行中，選中“借金額”所在的列，在下拉列表中選擇“匯總方式”為“求和”，如圖3-55所示，完成本月借方發(fā)生額的計算?！救蝿?wù)操作】【任務(wù)操作】四、獲取最新數(shù)據(jù)1.選中“明細(xì)分類賬”工作表，選中“開發(fā)工具”選項卡，執(zhí)行“控件”組中“插入”下的“表單控件”，選擇按鈕控件，在H2:H3單元格內(nèi)拖曳一個命令按鈕，如圖3-59所示，將命令按鈕內(nèi)的文字更改為“數(shù)據(jù)刷新”，完成命令按鈕的放置。2.選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組內(nèi)的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新明細(xì)分類賬”,單擊“確定”按鈕，開始錄制宏。3.選中A8單元格，單擊“數(shù)據(jù)”選項卡，執(zhí)行“連接”組內(nèi)的“全部刷新”命令，完成數(shù)據(jù)刷新操作。【任務(wù)操作】【知識準(zhǔn)備】

用戶為自己設(shè)計的軟件添加了諸多功能后，可能會碰到一個問題，就是如何快速找到自己所需的功能。在一般的軟件中是通過添加菜單、導(dǎo)航欄或者添加鏈接來解決的。由于我們設(shè)計的財務(wù)管理系統(tǒng)并沒有采用編程的方式，因此無法自定義菜單和選項卡。用戶要達(dá)到相同的目的，可以在工作簿中添加一個導(dǎo)航頁面，利用工作表鏈接的方法快速到達(dá)指定的頁面。科目匯總表、科目余額表、總分類賬和明細(xì)分類賬都涉及指定月份的功能，而科目余額表又都為總分類賬和明細(xì)分類賬提供了期初的余額，這就要求上述表格的數(shù)據(jù)要保持一致性。因此，可以在導(dǎo)航頁面上設(shè)置月份信息，而其他表格的月份信息都和導(dǎo)航頁面中指定的月份信息一致，從而保證不至于因為月份不同步而發(fā)生期初余額引用數(shù)據(jù)上的錯誤。任務(wù)五設(shè)計導(dǎo)航頁面【任務(wù)目標(biāo)】利用導(dǎo)航頁面來完成在各個頁面之間的跳轉(zhuǎn)。任務(wù)五設(shè)計導(dǎo)航頁面【任務(wù)資料】導(dǎo)航頁面要實現(xiàn)的功能包括：(1) 通過導(dǎo)航快速到達(dá)指定的頁面。(2) 科目匯總