Excel在會計(jì)與財(cái)務(wù)管理中的應(yīng)用 課件 項(xiàng)目3、4 會計(jì)賬簿、固定資產(chǎn)管理

Excel在會計(jì)與財(cái)務(wù)管理中的應(yīng)用項(xiàng)目三會計(jì)賬簿(1)了解MicrosoftQuery的使用方法；(2)了解數(shù)據(jù)驗(yàn)證的方法；(3)掌握總分類賬的結(jié)構(gòu)；(4)掌握科目余額表的結(jié)構(gòu)和MicrosoftQuery提取數(shù)據(jù)的方法；(5)掌握明細(xì)分類賬的設(shè)計(jì)方法；(6)學(xué)會應(yīng)用Excel解決實(shí)際案例中遇到的會計(jì)賬簿問題。學(xué)習(xí)目標(biāo)憑證庫相當(dāng)于整個賬務(wù)處理系統(tǒng)的數(shù)據(jù)中心，它為賬簿和報(bào)表提供了所需的數(shù)據(jù)。從Excel應(yīng)用的角度來看，賬簿和報(bào)表只是從不同的分析角度來看待憑證庫數(shù)據(jù)。本項(xiàng)目介紹了如何創(chuàng)建科目匯總表、科目余額表、總分類賬、明細(xì)分類賬以及導(dǎo)航頁面的設(shè)計(jì)等內(nèi)容。情景引入目錄CONTENTS科目匯總表1科目余額表2總分類賬3明細(xì)分類賬4設(shè)計(jì)導(dǎo)航頁面5任務(wù)一科目匯總表知識準(zhǔn)備Excel中有一個MicrosoftQuery組件，該組件的作用相當(dāng)于數(shù)據(jù)庫的查詢分析器，能夠通過該組件從數(shù)據(jù)庫中提取數(shù)據(jù)并顯示到工作表中。雖然Excel并不是真正意義上的數(shù)據(jù)庫，但是MicrosoftQuery組件依舊能夠從指定的工作簿(包括當(dāng)前正在使用的工作簿)中提取數(shù)據(jù)。如果把“科目匯總表”工作表當(dāng)作數(shù)據(jù)表看待，就可以解決會計(jì)科目的來源問題；如果把“憑證庫”工作表當(dāng)作數(shù)據(jù)表看待，就可以解決金額來源的問題。任務(wù)目標(biāo)(1)制作科目匯總表；(2)完成科目匯總表數(shù)據(jù)。任務(wù)資料2022年1月科目匯總表如表3-1所示?？颇繀R總表包括如下內(nèi)容：(1)日期信息。(2)科目代碼。(3)科目名稱。(4)指定月借方發(fā)生額。(5)指定月貸方發(fā)生額。任務(wù)操作(1)在“賬務(wù)處理.xlsm”中新建一張工作表，將其重命名為“科目匯總表”。(2)選中A1:D1單元格區(qū)域，選擇“開始”選項(xiàng)卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，將指定的內(nèi)容合并居中，然后在A1單元格中輸入“=B2&″年″&D2&″月科目匯總表″”，完成表頭標(biāo)題的設(shè)置。(3)從A2單元格開始輸入如圖3-1所示的內(nèi)容，完成科目匯總表界面的設(shè)置。1.科目匯總表界面圖3-1

科目匯總表結(jié)構(gòu)任務(wù)操作(1)選中A4單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-2所示的“選擇數(shù)據(jù)源”對話框。2.科目匯總表數(shù)據(jù)圖3-2“選擇數(shù)據(jù)源”對話框任務(wù)操作(2)在“選擇數(shù)據(jù)源”對話框中選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-3所示的“選擇工作簿”對話框。2.科目匯總表數(shù)據(jù)圖3-3“選擇工作簿”對話框任務(wù)操作(3)在“驅(qū)動器”中選擇D盤，在“目錄”中選擇“data正文”，數(shù)據(jù)庫的名稱指定為“賬務(wù)處理.xlsm”，單擊“確定”按鈕，打開如圖3-4所示的“查詢向?qū)?選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”按鈕，展開該表所包含的列，選中要顯示的列為科目代碼、總賬科目、借金額和貸金額。2.科目匯總表數(shù)據(jù)圖3-4“查詢向?qū)?選擇列”對話框任務(wù)操作(4)單擊“下一頁”按鈕，完成要顯示的列的選擇，打開“查詢向?qū)?篩選數(shù)據(jù)”對話框，如圖3-5所示。2.科目匯總表數(shù)據(jù)圖3-5“查詢向?qū)?篩選數(shù)據(jù)”對話框任務(wù)操作(5)單擊“下一頁”按鈕，跳過篩選過程，打開如圖3-6所示的“查詢向?qū)?排序順序”對話框，在該步驟中不需要設(shè)置任何內(nèi)容。2.科目匯總表數(shù)據(jù)圖3-6“查詢向?qū)?排序順序”對話框任務(wù)操作(6)單擊“下一頁”按鈕，打開如圖3-7所示的“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”單選按鈕，單擊“完成”按鈕。完成查詢向?qū)У牟僮?，進(jìn)入MicrosoftQuery窗口，如圖3-8所示。2.科目匯總表數(shù)據(jù)任務(wù)操作2.科目匯總表數(shù)據(jù)圖3-7“查詢向?qū)?完成”對話框圖3-8“查詢向?qū)?完成”對話框任務(wù)操作(7)在MicrosoftQuery窗口中選擇“科目代碼”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-9所示的“編輯列”對話框，將“字段”下的內(nèi)容更改為“Left(科目代碼,4)”，單擊“確定”按鈕，完成列的設(shè)置。(8)在MicrosoftQuery窗口中選擇“借金額”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-10所示的“編輯列”對話框，在“字段”下拉列表中選擇“借金額”，在“總計(jì)”下拉列表中選擇“求和”，單擊“確定”按鈕，完成“借金額”字段的求和。2.科目匯總表數(shù)據(jù)任務(wù)操作2.科目匯總表數(shù)據(jù)圖3-9編輯列圖3-10編輯“借金額”字段任務(wù)操作(9)在MicrosoftQuery窗口中選擇“貸金額”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-11所示的“編輯列”對話框，在“字段”下拉列表中選擇“貸金額”，在“總計(jì)”下拉列表中選擇“求和”，單擊“確定”按鈕，完成“貸金額”字段的求和。2.科目匯總表數(shù)據(jù)圖3-11編輯“貸金額”字段任務(wù)操作(10)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-12所示的“添加條件”對話框，保持“總計(jì)”下拉列表為空，“字段”設(shè)置為“年”，“運(yùn)算符”設(shè)置為“等于”，“指定值”設(shè)置為“[nian]”，單擊“添加”按鈕，完成年份條件的設(shè)置；保持“總計(jì)”下拉列表為空，“字段”設(shè)置為“月”，“運(yùn)算符”設(shè)置為“等于”，“指定值”設(shè)置為“[yue]”，單擊“添加”按鈕，完成月份條件的設(shè)置。2.科目匯總表數(shù)據(jù)圖3-12“添加條件”對話框【提示】指定值設(shè)置為“[nian]”的含義是要指定的“年”字段的值不是一個固定的值，而是一個名為nian的變量(也稱為參數(shù))。該參數(shù)的值是可變的，可以將該參數(shù)鏈接到一個單元格中，若單元格的值發(fā)生變化，則意味著查詢參數(shù)的值發(fā)生了變化。在設(shè)置第二個條件時，要指定兩個條件之間的關(guān)系是“與”，即最終顯示的數(shù)據(jù)是兩個條件同時成立時的結(jié)果。任務(wù)操作2.科目匯總表數(shù)據(jù)任務(wù)操作在“添加條件”對話框中單擊“添加”按鈕后，會打開如圖3-13所示的“輸入?yún)?shù)值”對話框，由于暫時并不指定值的大小，因此直接單擊“確定”按鈕，完成參數(shù)值的設(shè)置。單擊“添加條件”對話框中的“關(guān)閉”按鈕，關(guān)閉“添加條件”對話框，完成條件的設(shè)置。2.科目匯總表數(shù)據(jù)圖3-13“輸入?yún)?shù)值”對話框任務(wù)操作由于沒有指定值，因此MicrosoftQuery中顯示的內(nèi)容如圖3-14所示。2.科目匯總表數(shù)據(jù)圖3-14MicrosoftQuery中顯示的內(nèi)容任務(wù)操作(11)在MicrosoftQuery窗口中選擇“文件”→“將數(shù)據(jù)返回MicrosoftExcel”命令，打開如圖3-15所示的“導(dǎo)入數(shù)據(jù)”對話框，將數(shù)據(jù)放置的位置設(shè)置為“科目匯總表”的A4單元格，完成數(shù)據(jù)導(dǎo)入起始位置的設(shè)置。2.科目匯總表數(shù)據(jù)圖3-15設(shè)置導(dǎo)入數(shù)據(jù)的起始位置任務(wù)操作(12)單擊“導(dǎo)入數(shù)據(jù)”對話框中的“確定”按鈕，打開如圖3-16所示的“輸入?yún)?shù)值”對話框，單擊“nian”下文本框后的折疊按鈕，選中工作表中的B2單元格，選中“在以后的刷新中使用該值或該引用”和“當(dāng)單元格值更改時自動刷新”復(fù)選框，單擊“確定”按鈕，完成年份參數(shù)的設(shè)置。單擊“yue”下文本框后的折疊按鈕，選中工作表中的D2單元格，選中“在以后的刷新中使用該值或該引用”和“當(dāng)單元格值更改時自動刷新”復(fù)選框，完成月份參數(shù)的設(shè)置。2.科目匯總表數(shù)據(jù)圖3-16“輸入?yún)?shù)值”對話框任務(wù)操作(13)選中A4單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，選擇“表樣式”組中的第一種樣式“無”，選中“表樣式”組中的“匯總行”項(xiàng)，添加一個匯總行，并將借金額和貸金額的匯總方式都設(shè)置為“求和”。(14)選中A3:D19單元格區(qū)域，設(shè)置指定的單元格區(qū)域包含邊框線。(15)選中C5:D19單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，選擇“數(shù)值類型”為“會計(jì)專用”，不顯示“貨幣符號”類型。2.科目匯總表數(shù)據(jù)任務(wù)操作(16)選中A4單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，執(zhí)行“外部表數(shù)據(jù)”組中的“屬性”命令，打開如圖3-17所示的“外部數(shù)據(jù)屬性”對話框，取消選中“調(diào)整列寬”復(fù)選框，單擊“確定”按鈕，完成格式和布局的設(shè)置。2.科目匯總表數(shù)據(jù)圖3-17“外部數(shù)據(jù)屬性”對話框任務(wù)操作(17)選中第4行，右擊，在彈出的快捷菜單中選擇“隱藏”命令，隱藏表對象的標(biāo)題行。通過上述步驟就完成了科目匯總表的所有設(shè)計(jì)，設(shè)計(jì)完成后的科目匯總表如圖3-18所示。2.科目匯總表數(shù)據(jù)圖3-18

1月份科目匯總表任務(wù)操作當(dāng)“匯總”行的“借方發(fā)生額”和“貸方發(fā)生額”兩個數(shù)據(jù)一致時，說明所有的憑證借貸方金額沒有發(fā)生輸入錯誤，從而達(dá)到試算平衡。當(dāng)要查看其他月份時，只要修改D2單元格中的值即可。例如，當(dāng)月份修改為2時，由于2月份并沒有輸入憑證，因此顯示的結(jié)果如圖3-19所示。2.科目匯總表數(shù)據(jù)圖3-192月份科目匯總表任務(wù)二科目余額表知識準(zhǔn)備科目余額表是記錄本期所有會計(jì)科目的發(fā)生額和余額的表格，它反映了某一會計(jì)期間相關(guān)會計(jì)科目的期初余額、本期發(fā)生額和期末余額。與科目匯總表類似，科目余額表反映的是各個科目的余額，但是各個月份的會計(jì)科目并不是固定的，部分明細(xì)科目會隨著企業(yè)業(yè)務(wù)的發(fā)展而逐漸加入其中，因此直接使用公式引用科目代碼表并不是一個很好的選擇。任務(wù)一已經(jīng)介紹了一種導(dǎo)入變動數(shù)據(jù)的方法，即利用MicrosoftQuery組件。知識準(zhǔn)備用戶通過MicrosoftQuery組件將“科目代碼表”中的科目導(dǎo)入工作表后，只需要單擊刷新，則不論什么樣的會計(jì)科目總能出現(xiàn)在工作表中，并且這些數(shù)據(jù)和科目代碼表中的數(shù)據(jù)完全一致。同樣，科目余額表也會遇到余額的處理問題。科目余額表中涉及的科目余額計(jì)算包括期初余額、本期發(fā)生額和期末余額。本任務(wù)中，企業(yè)從年初開始建賬，因此期初余額就是年初數(shù)，本期發(fā)生額是指定期限中的累計(jì)借方和累計(jì)貸方的發(fā)生額，期末余額則是根據(jù)期初余額和本期發(fā)生額計(jì)算的結(jié)果。任務(wù)目標(biāo)(1)制作科目余額表；(2)導(dǎo)入科目代碼；(3)完成科目余額。任務(wù)資料科目余額表需要實(shí)現(xiàn)的功能如下：(1)期初各個會計(jì)科目的借方和貸方余額。(2)當(dāng)期各會計(jì)科目的借方和貸方發(fā)生額。(3)計(jì)算本期期末各個會計(jì)科目的借方和貸方余額。任務(wù)操作(1)新建工作表，將工作表重命名為“科目余額表”。(2)選中E1:G1單元格區(qū)域，選擇“開始”選項(xiàng)卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，設(shè)置字體為“仿宋_GB2312”，字號為24，并設(shè)置雙下畫線，完成字體字號的設(shè)置。(3)選中E1單元格，在編輯欄內(nèi)輸入公式“=B2&″年″&G2&″月科目余額表″”，完成科目余額表標(biāo)題的設(shè)置。(4)從第2行開始輸入如圖3-20所示的科目余額表中的各項(xiàng)。1.科目余額表界面圖3-20科目余額表表頭部分任務(wù)操作(1)選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開“選擇數(shù)據(jù)源”對話框。(2)選擇“數(shù)據(jù)庫類型”為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-21所示的“選擇工作簿”對話框。2.導(dǎo)入科目代碼圖3-21“選擇工作簿”對話框任務(wù)操作(3)選擇工作簿所在的文件路徑，單擊“確定”按鈕，打開如圖3-22所示的“查詢向?qū)?選擇列”對話框，單擊“科目代碼表”前的“+”按鈕，展開該表所包含的字段，選中要顯示的列為科目代碼、科目名稱、性質(zhì)和年初數(shù)。2.導(dǎo)入科目代碼圖3-22“查詢向?qū)?選擇列”對話框任務(wù)操作(4)單擊“下一頁”按鈕，完成要顯示的列的選擇。單擊“下一頁”按鈕，打開“查詢向?qū)?篩選數(shù)據(jù)”對話框，單擊“下一頁”按鈕，跳過篩選過程，打開“查詢向?qū)?排序順序”對話框，在該步驟中不需要設(shè)置任何內(nèi)容。單擊“下一頁”按鈕，打開“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”單選按鈕，單擊“完成”按鈕，完成查詢向?qū)У牟僮?，進(jìn)入MicrosoftQuery窗口。2.導(dǎo)入科目代碼任務(wù)操作(5)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-23所示的“添加條件”對話框，選擇“字段”為“是否明細(xì)”，“指定值”為“y”，單擊“添加”按鈕，完成條件設(shè)置，單擊“關(guān)閉”按鈕。2.導(dǎo)入科目代碼圖3-23“添加條件”對話框任務(wù)操作(6)在MicrosoftQuery窗口中選擇“文件”→“將數(shù)據(jù)返回MicrosoftExcel”命令，打開如圖3-24所示的“導(dǎo)入數(shù)據(jù)”對話框，將“數(shù)據(jù)的放置位置”設(shè)置為科目余額表的A5單元格，完成數(shù)據(jù)導(dǎo)入操作。2.導(dǎo)入科目代碼圖3-24設(shè)置導(dǎo)入數(shù)據(jù)的起始位置任務(wù)操作(7)選中A5單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，在“表樣式”組中選擇一種樣式。選中D5單元格，右擊，在彈出的快捷菜單中選擇“插入”→“在右側(cè)插入表列”命令，插入一個新列。使用同樣的操作方法插入其他5列，完成表主體結(jié)構(gòu)的設(shè)置。(8)選中A5單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“排序和篩選”組中的“篩選”命令，退出篩選狀態(tài)。2.導(dǎo)入科目代碼任務(wù)操作(9)選中A5單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，將“屬性”組內(nèi)的表名稱更改為“kmye”，完成表名稱的設(shè)置。最終完成的科目余額表如圖3-25所示。2.導(dǎo)入科目代碼圖3-25完成后的科目余額表結(jié)構(gòu)任務(wù)操作(1)選中E6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=1,[@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)-SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)),0)”，完成期初余額的計(jì)算。(2)選中F6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=-1,ABS([@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)-SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2))),0)”，完成期初貸方余額的計(jì)算。3.科目余額任務(wù)操作(3)選中G6單元格，在編輯欄內(nèi)輸入公式“=SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月借方發(fā)生額的計(jì)算。(4)選中H6單元格，在編輯欄內(nèi)輸入公式“=SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月貸方發(fā)生額的計(jì)算。(5)選中I6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=1,[@列1]+[@列3]-[@列4],0)”，完成借方余額的計(jì)算。(6)選中J6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=-1,[@列2]+[@列4]-[@列3],0)”，完成貸方余額的計(jì)算。3.科目余額任務(wù)操作(7)選中C6:J79單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對話框，選擇數(shù)值類型為“會計(jì)專用”，不顯示貨幣符號類型。(8)選中A5單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，執(zhí)行“表格樣式”組內(nèi)的“匯總行”命令，如圖3-26所示，完成匯總行的添加。3.科目余額圖3-26添加匯總行任務(wù)操作(9)在“匯總”行中選中列5所在的列，從下拉列表中選擇匯總方式為“求和”，如圖3-27所示，完成期末借方余額的匯總。3.科目余額圖3-27匯總期末借方余額任務(wù)操作(10)用同樣的方法，將匯總行中列1、列2、列3和列4字段所在的列匯總方式設(shè)置為“求和”。(11)在“匯總”行中選中年初數(shù)所在的列，在編輯欄內(nèi)輸入公式“=SUM([性質(zhì)]*[年初數(shù)])”，同時按Shift+Ctrl+Enter鍵，完成數(shù)組公式的輸入。(12)選中第5行，即字段名稱所在的行，右擊，在彈出的快捷菜單中選擇“隱藏”命令，隱藏該行。(13)選中C列和D列，右擊，在彈出的快捷菜單中選擇“隱藏”命令，隱藏指定的列。3.科目余額任務(wù)操作(14)選中A6單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，執(zhí)行“外部表數(shù)據(jù)”組中的“屬性”命令，打開“外部數(shù)據(jù)屬性”對話框，如圖3-28所示。在“數(shù)據(jù)格式和布局”下取消選中“調(diào)整列寬”復(fù)選框，單擊“確定”按鈕，完成外部數(shù)據(jù)屬性的設(shè)置。3.科目余額圖3-28“外部數(shù)據(jù)屬性”對話框任務(wù)操作通過上述方式，最終形成的科目余額表如圖3-29所示。3.科目余額圖3-29包含匯總行的科目余額表任務(wù)操作(1)選中“科目余額表”，選擇“開發(fā)工具”選項(xiàng)卡，選擇“控件”組中“插入”下“表單控件”中的“按鈕控件”，在J1單元格內(nèi)拖出一個命令按鈕，將命令按鈕內(nèi)的文字更改為“刷新”，如圖3-30所示，完成命令按鈕的設(shè)置。4.獲取最新數(shù)據(jù)圖3-30添加命令按鈕任務(wù)操作(2)選擇“開發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新科目”，如圖3-31所示，單擊“確定”按鈕，開始錄制宏。4.獲取最新數(shù)據(jù)圖3-31“錄制宏”對話框任務(wù)操作(3)選中A6單元格，右擊，在彈出的快捷菜單中選擇“刷新”命令。選擇“開發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“停止錄制”命令，完成宏的錄制。(4)在“刷新”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開“指定宏”對話框，選擇“刷新科目”項(xiàng)，單擊“確定”按鈕，完成指定宏的設(shè)置。假設(shè)在“科目代碼表”中添加1個科目代碼為122103的其他應(yīng)收款的子科目，在科目代碼表中添加完成后的情況如圖3-32所示。4.獲取最新數(shù)據(jù)圖3-32科目代碼表中添加會計(jì)科目任務(wù)操作選擇“科目余額表”，單擊“刷新”按鈕，完成數(shù)據(jù)的刷新操作。刷新后的科目余額表如圖3-33所示。4.獲取最新數(shù)據(jù)圖3-33刷新后的科目余額表從圖3-33可以看出，只需要單擊“刷新”按鈕，科目代碼表中的科目代碼就會同步到科目余額表中，從而保證兩個表之間的科目一致性。任務(wù)三總分類賬知識準(zhǔn)備總分類賬就是我們通常所稱的總賬，它是根據(jù)總分類科目開設(shè)賬戶，用來登記全部經(jīng)濟(jì)業(yè)務(wù)，進(jìn)行總分類核算，提供總括核算資料的分類賬簿?？偡诸愘~提供了編制會計(jì)報(bào)表的主要依據(jù)，是所有單位都必須設(shè)立的賬簿?？偡诸愘~全面且總括地反映了業(yè)務(wù)單位的財(cái)務(wù)收支和經(jīng)濟(jì)活動情況。知識準(zhǔn)備總分類賬的結(jié)構(gòu)本身并不復(fù)雜，使用Excel進(jìn)行設(shè)計(jì)時，只需要更改日期信息和會計(jì)科目，就能夠得到期初余額、當(dāng)期的借方發(fā)生額和貸方發(fā)生額以及期末余額，但其并不反映具體的業(yè)務(wù)信息。日期信息是由用戶手工更改的，會計(jì)科目信息可以通過數(shù)據(jù)驗(yàn)證的方式交由用戶選擇而不是直接輸入，從而避免發(fā)生錯誤，各種余額和發(fā)生額則都是通過函數(shù)從憑證庫中提取的。任務(wù)目標(biāo)(1)制作總分類賬；(2)完成總分類賬數(shù)據(jù)。要求根據(jù)已經(jīng)完成的科目余額表中的期初數(shù)和憑證庫中的憑證數(shù)據(jù)編制總分類賬。任務(wù)資料(1)新建一張工作表，將工作表名稱更改為“總分類賬”，選中D1:E1單元格區(qū)域，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，“字體”為“仿宋_GB2312”，在D1單元格內(nèi)輸入“總分類賬”，并設(shè)置雙下畫線，完成總分類賬標(biāo)題的設(shè)置。(2)從A2單元格開始輸入如圖3-34所示的表格內(nèi)容。任務(wù)操作1.總分類賬界面圖3-34總分類賬表格(3)選中C3單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗(yàn)證”命令，打開如圖3-35所示的“數(shù)據(jù)驗(yàn)證”對話框，在“允許”下拉列表中選擇“序列”，在“來源”文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)驗(yàn)證的設(shè)置。任務(wù)操作1.總分類賬界面圖3-35“數(shù)據(jù)驗(yàn)證”對話框【提示】dm指的是科目代碼表中“科目代碼”列中的值，是用戶自定義的名稱。使用數(shù)據(jù)驗(yàn)證時，來源不能夠直接引用某個列表中的列，如直接指定來源為“=kmdm[科目代碼]”就會產(chǎn)生一個錯誤。其解決辦法是為指定的單元格區(qū)域定義一個名稱：選擇“公式”選項(xiàng)卡，執(zhí)行“定義的名稱”組中的“定義名稱”命令，打開如圖3-36所示的“新建名稱”對話框，在“名稱”文本框內(nèi)輸入“dm”，在“引用位置”文本框內(nèi)輸入“=kmdm[科目代碼]”，單擊“確定”按鈕，完成名稱的設(shè)置。任務(wù)操作1.總分類賬界面圖3-36“新建名稱”對話框(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))”，完成會計(jì)科目名稱的設(shè)置。將年份信息設(shè)置為2022，月份信息設(shè)置為1，科目代碼設(shè)置為1001，最終顯示的科目代碼如圖3-37所示。任務(wù)操作1.總分類賬界面圖3-37設(shè)置總分類賬條件(1)選中C2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!B2”；選中F2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!G2”；選中A4單元格，在編輯欄內(nèi)輸入公式“=C2&″年″”，完成年份和月份信息的設(shè)置。(2)選中A6單元格，在編輯欄內(nèi)輸入公式“=$F$2”，按Enter鍵確認(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))”，計(jì)算指定月份的天數(shù)；選中B8單元格，在編輯欄內(nèi)輸入公式“=B7”，完成日信息的填制。任務(wù)操作2.總分類賬數(shù)據(jù)(4)在C6單元格內(nèi)輸入“期初余額”，在C7單元格內(nèi)輸入“本月發(fā)生額”，在C8單元格內(nèi)輸入“本月合計(jì)”，完成摘要內(nèi)容的填制。(5)選中D6單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列1],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列1],0)))”，同時按Ctrl+Shift+Enter鍵，完成借金額期初數(shù)的設(shè)置。(6)選中E6單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列2],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列2],0)))”，同時按Ctrl+Shift+Enter鍵，完成貸金額期初數(shù)的設(shè)置。任務(wù)操作2.總分類賬數(shù)據(jù)(7)選中F6單元格，在編輯欄內(nèi)輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質(zhì)])>0,″借″,″貸″)”，完成借貸方向的設(shè)置。(8)選中G6單元格，在編輯欄內(nèi)輸入公式“=IF(F6=″借″,D6-E6,E6-D6)”，完成期初余額的設(shè)置。(9)選中D7單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列3],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列3],0)))”，同時按Ctrl+Shift+Enter鍵，完成指定月借方發(fā)生額的設(shè)置。任務(wù)操作2.總分類賬數(shù)據(jù)(10)選中E7單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列4],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列4],0)))”，同時按Ctrl+Shift+Enter鍵，完成指定月貸方發(fā)生額的設(shè)置。(11)選中G7單元格，在編輯欄內(nèi)輸入公式“=IF($F$6=″借″,D7-E7,E7-D7)”，完成當(dāng)月借方發(fā)生額差額的計(jì)算。(12)選中F7單元格，在編輯欄內(nèi)輸入公式“=IF(AND($F$6=″借″,G7<0),″貸″,IF(AND($F$6=″貸″,G7<0),″借″,IF(G7=0,″平″,$F$6)))”，完成借貸方向的設(shè)置。(13)選中D8單元格，在編輯欄內(nèi)輸入公式“=SUM(D6:D7)”，完成本月借方合計(jì)數(shù)的計(jì)算。(14)選中E8單元格，在編輯欄內(nèi)輸入公式“=SUM(E6:E7)”，完成本月貸方合計(jì)數(shù)的計(jì)算。任務(wù)操作2.總分類賬數(shù)據(jù)(15)選中G8單元格，在編輯欄內(nèi)輸入公式“=IF($F$6=″借″,D8-E8,E8-D8)”，完成指定科目當(dāng)月余額的計(jì)算。(16)選中F8單元格，在編輯欄內(nèi)輸入公式“=IF(AND($F$6=″借″,G8<0),″貸″,IF(AND($F$6=″貸″,G8<0),″借″,IF(G8=0,″平″,$F$6)))”，完成指定科目余額借貸方向的設(shè)置。當(dāng)用戶輸入不同的科目代碼時，會顯示不同的內(nèi)容。①輸入科目代碼為1001時，長度為4且為一個明細(xì)科目，顯示的內(nèi)容如圖3-38所示。任務(wù)操作2.總分類賬數(shù)據(jù)圖3-38科目代碼為1001時顯示的內(nèi)容②輸入科目代碼為100201時，長度為6且為一個明細(xì)科目，顯示的內(nèi)容如圖3-39所示。③輸入科目代碼為1002時，長度為4且不為一個明細(xì)科目，顯示的內(nèi)容如圖3-40所示。任務(wù)操作2.總分類賬數(shù)據(jù)圖3-39科目代碼為100201時顯示的內(nèi)容圖3-40科目代碼為1002時顯示的內(nèi)容(1)選中G7:G8單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開如圖3-41所示的“設(shè)置單元格格式”對話框。選擇“數(shù)字”選項(xiàng)卡，在“分類”列表框中選擇“自定義”，在類型中輸入“#,##0.00;#,##0.00”，單擊“確定”按鈕，完成數(shù)值顯示格式的設(shè)置。任務(wù)操作3.優(yōu)化顯示圖3-41設(shè)置數(shù)值自定義顯示格式【提示】自定義類型“#,##0.00;#,##0.00”的含義是不論該數(shù)字為正數(shù)還是負(fù)數(shù)，都會用正數(shù)的形式來顯示。此時余額的正負(fù)不再是通過正負(fù)號來看，而是通過借貸方向獲得，如科目代碼設(shè)置為1002時的最終結(jié)果如圖3-42所示。任務(wù)操作3.優(yōu)化顯示圖3-42總是以正數(shù)顯示(2)選中整張工作表，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，選擇“保護(hù)”選項(xiàng)卡，選中“鎖定”復(fù)選框，單擊“確定”按鈕，完成全部單元格的鎖定操作。(3)選中C3單元格，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，選擇“保護(hù)”選項(xiàng)卡，取消選中“鎖定”復(fù)選框，單擊“確定”按鈕，完成可編輯單元格的指定操作。(4)選擇“開始”選項(xiàng)卡，執(zhí)行“單元格”組內(nèi)“格式”下的“保護(hù)工作表”命令，打開“保護(hù)工作表”對話框，設(shè)置一個合適的密碼，完成工作表的保護(hù)操作。任務(wù)操作3.優(yōu)化顯示任務(wù)四明細(xì)分類賬知識準(zhǔn)備用戶從總分類賬中得到的僅僅是某個會計(jì)科目在指定期間內(nèi)總的發(fā)生額，但是該科目到底發(fā)生了什么業(yè)務(wù)并不明確，這就要求使用明細(xì)分類賬來顯示具體的業(yè)務(wù)信息。明細(xì)分類賬是根據(jù)明細(xì)分類賬戶進(jìn)行分類登記的賬簿，是根據(jù)單位開展經(jīng)濟(jì)管理的需要對經(jīng)濟(jì)業(yè)務(wù)的詳細(xì)內(nèi)容進(jìn)行的核算，是對總分類賬進(jìn)行的補(bǔ)充反映。知識準(zhǔn)備從技術(shù)角度來說，創(chuàng)建明細(xì)分類賬需要用戶指定的信息如下：(1)會計(jì)科目：用戶查詢的是哪個明細(xì)分類賬戶的信息。(2)日期信息：用戶查詢的是哪個月份的信息。(3)期初余額：明細(xì)分類賬期初余額信息，有了期初余額和本月發(fā)生信息，就可以計(jì)算期末的科目余額。會計(jì)科目是由用戶指定的，月份信息可以由用戶在科目余額表中指定，期初余額數(shù)據(jù)通過公式從科目余額表中獲得。具體的業(yè)務(wù)信息數(shù)據(jù)來自“憑證庫”工作表，為此需要通過MicrosoftQuery組件創(chuàng)建查詢來獲得相關(guān)的數(shù)據(jù)。期末余額是通過期初余額和本期借貸方發(fā)生額獲得的。任務(wù)目標(biāo)(1)制作明細(xì)分類賬；(2)完成明細(xì)分類賬數(shù)據(jù)。明細(xì)分類賬要實(shí)現(xiàn)如下幾個功能：(1)列出指定月份和科目代碼的期初數(shù)。(2)列出本期指定會計(jì)科目的發(fā)生記錄，并將這些記錄填到表中。(3)計(jì)算本期期末的借方和貸方余額。任務(wù)資料(1)新建一張工作表，將工作表名稱更改為“明細(xì)分類賬”。選中A1:H1單元格區(qū)域，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，在編輯欄內(nèi)輸入公式“=F3&″明細(xì)分類賬″”，并設(shè)置雙下畫線，完成明細(xì)分類賬標(biāo)題的設(shè)置。(2)從A2單元格開始輸入如圖3-43所示的內(nèi)容。任務(wù)操作1.明細(xì)分類賬界面圖3-43明細(xì)分類賬界面(3)選中C3單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗(yàn)證”命令，打開如圖3-44所示的“數(shù)據(jù)驗(yàn)證”對話框，在“允許”下拉列表中選擇“序列”，在“來源”文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)有效性的設(shè)置。任務(wù)操作1.明細(xì)分類賬界面圖3-44“數(shù)據(jù)驗(yàn)證”對話框(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))”，完成會計(jì)科目名稱的設(shè)置。(5)選中F2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!G2”；選中A6單元格，在編輯欄內(nèi)輸入公式“=F2”，完成月份的設(shè)置。(6)在B6單元格中輸入數(shù)值1，在D6單元格內(nèi)輸入“期初余額”。任務(wù)操作1.明細(xì)分類賬界面(7)選中E6單元格，在編輯欄內(nèi)輸入公式“==SUMIF(kmye[科目代碼],$C$3,kmye[列1])”，完成期初借方金額的設(shè)置。(8)選中F6單元格，在編輯欄內(nèi)輸入公式“=SUMIF(kmye[科目代碼],$C$3,kmye[列2])”，完成期初貸方金額的設(shè)置。(9)選中G6單元格，在編輯欄內(nèi)輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質(zhì)])>0,″借″,″貸″)”，完成借貸方向的設(shè)置。(10)選中H6單元格，在編輯欄內(nèi)輸入公式“=IF(G6=″借″,E6-F6,F6-E6)”，完成期初余額的設(shè)置。任務(wù)操作1.明細(xì)分類賬界面(1)選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-45所示的“選擇數(shù)據(jù)源”對話框。(2)在“選擇數(shù)據(jù)源”對話框中選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-46所示的“選擇工作簿”對話框。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-45“選擇數(shù)據(jù)源”對話框圖3-46“選擇工作簿”對話框(3)選擇本工作簿所在的路徑，單擊“確定”按鈕，打開如圖3-47所示的“查詢向?qū)?選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”按鈕，展開該表所包含的列，選中要顯示的列為月、日、憑證號、摘要、借金額和貸金額。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-47選擇要顯示的列(4)單擊“下一頁”按鈕，完成要顯示的列的選擇，進(jìn)入“查詢向?qū)?篩選數(shù)據(jù)”對話框，直接單擊“下一頁”按鈕，跳過篩選過程，打開如圖3-48所示的“查詢向?qū)?排序順序”對話框，“主要關(guān)鍵字”設(shè)置為“憑證號”，完成排序操作。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-48“查詢向?qū)?排序順序”對話框(5)單擊“下一頁”按鈕，打開“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”單選按鈕，單擊“完成”按鈕，完成查詢向?qū)У牟僮鳎M(jìn)入MicrosoftQuery窗口。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息(6)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-49所示的“添加條件”對話框，選擇“字段”為“月”，“指定值”為“[yue]”，完成第一個參數(shù)條件的設(shè)置。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-49指定月參數(shù)(7)單擊“添加”按鈕，打開如圖3-50所示的“輸入?yún)?shù)值”對話框，此時不需要設(shè)置任何參數(shù)，單擊“確定”按鈕，完成第一個參數(shù)的設(shè)置。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-50“輸入?yún)?shù)值”對話框(8)在“添加條件”對話框中選擇“字段”為“科目代碼”，“指定值”為“[kmdm]”，如圖3-51所示，完成第二個參數(shù)條件的設(shè)置。單擊“添加”按鈕，打開“輸入?yún)?shù)值”對話框，此時不需要設(shè)置任何參數(shù)，單擊“確定”按鈕，完成第二個參數(shù)的設(shè)置。再次單擊“添加條件”對話框中的“關(guān)閉”按鈕，完成所有參數(shù)條件的設(shè)置。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-51添加科目代碼條件(9)在MicrosoftQuery窗口中選擇“文件”→“將數(shù)據(jù)返回MicrosoftExcel”命令，打開如圖3-52所示的“導(dǎo)入數(shù)據(jù)”對話框，將數(shù)據(jù)放置的位置設(shè)置為“明細(xì)分類賬”的A7單元格，完成數(shù)據(jù)導(dǎo)入操作。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-52指定導(dǎo)入數(shù)據(jù)的起始單元格(10)單擊“導(dǎo)入數(shù)據(jù)”對話框中的“確定”按鈕，打開如圖3-53所示的“輸入?yún)?shù)值”對話框，在“yue”文本框中選擇工作表的F2單元格，并且選中“在以后的刷新中使用該值或該引用”和“當(dāng)單元格值更改時自動刷新”復(fù)選框。同樣，在打開的指定參數(shù)kmdm中指定科目代碼鏈接的單元格為C3單元格，完成參數(shù)的指定操作。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-53指定參數(shù)yue和kmdm的值(11)選中“貸金額”字段所在的F7單元格，右擊，在彈出的快捷菜單中選擇“插入”→“在右側(cè)插入表列”命令，插入一個新列，列名稱自動設(shè)置為“列1”。選中“列1”所在的單元格，右擊，在彈出的快捷菜單中選擇“插入”→“在右側(cè)插入表列”命令，插入一個新列，列名稱為“列2”，完成列的插入操作。(12)選中A7單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，在“表樣式”組內(nèi)選擇“表樣式淺色8”，完成表樣式的選擇。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息(13)選擇“設(shè)計(jì)”選項(xiàng)卡，在“屬性”組內(nèi)將表名稱更改為“flz”。(14)選中第7行，右擊，在彈出的快捷菜單中選擇“隱藏”命令，完成表頭行的隱藏操作。操作完成后的明細(xì)分類賬如圖3-54所示。任務(wù)操作2.導(dǎo)入業(yè)務(wù)信息圖3-54明細(xì)分類賬(1)選中A8單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，執(zhí)行“表樣式選項(xiàng)”組中的“匯總行”命令，完成添加匯總行的操作。(2)刪除“月”字段所在列中的“匯總”字樣，在匯總行中，在“摘要”字段所在的單元格內(nèi)輸入文字“本月合計(jì)”。(3)在“匯總”行中選中“借金額”所在的列，在下拉列表中選擇匯總方式為“求和”，如圖3-55所示，完成本月借方發(fā)生額的計(jì)算。任務(wù)操作3.添加匯總行圖3-55指定匯總行計(jì)算類型為“求和”(4)在“匯總”行中選中“貸金額”所在的列，在下拉列表中選擇匯總方式為“求和”，完成本月貸方發(fā)生額的計(jì)算。(5)在“匯總”行中選中“列1”所在的列，在編輯欄中輸入公式“=G6”，完成借貸方向的設(shè)置。(6)在“匯總”行中選中“列2”所在的列，在編輯欄內(nèi)輸入公式“=IF($G$6=″借″,$H$6+flz[[#匯總],[借金額]]-flz[[#匯總],[貸金額]],H6+flz[[#匯總],[貸金額]]-flz[[#匯總],[借金額]])”，完成期末余額的計(jì)算。任務(wù)操作3.添加匯總行(7)調(diào)整各列的寬度到合適的位置，選中A7單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，執(zhí)行“外部表數(shù)據(jù)”組中的“屬性”命令，打開如圖3-56所示的“外部數(shù)據(jù)屬性”對話框，取消選中“調(diào)整列寬”復(fù)選框，單擊“確定”按鈕，完成列寬格式的設(shè)置。任務(wù)操作3.添加匯總行圖3-56“外部數(shù)據(jù)屬性”對話框通過上述操作，最終的表樣式如圖3-57所示。任務(wù)操作3.添加匯總行圖3-57現(xiàn)金明細(xì)分類賬將C3單元格的科目代碼更改為100201，就會顯示工行的明細(xì)分類賬，如圖3-58所示。任務(wù)操作3.添加匯總行圖3-58工行的明細(xì)分類賬(1)選中“明細(xì)分類賬”工作表，選擇“開發(fā)工具”選項(xiàng)卡，執(zhí)行“控件”組中“插入”下的“表單控件”命令，選擇“按鈕控件”，在H2:H3單元格區(qū)域內(nèi)拖曳一個命令按鈕，如圖3-59所示，將命令按鈕內(nèi)的文字更改為“數(shù)據(jù)刷新”，完成命令按鈕的放置。任務(wù)操作4.獲取最新數(shù)據(jù)圖3-59添加“數(shù)據(jù)刷新”按鈕(2)選擇“開發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新明細(xì)分類賬”,單擊“確定”按鈕，開始錄制宏。(3)選中A8單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“連接”組中的“全部刷新”命令，完成數(shù)據(jù)刷新操作。(4)選擇“開發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“停止錄制”命令，完成宏的錄制。(5)在“刷新數(shù)據(jù)”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開“指定宏”對話框，選擇“刷新明細(xì)分類賬”項(xiàng)，單擊“確定”按鈕，完成為按鈕指定宏的操作。任務(wù)操作4.獲取最新數(shù)據(jù)任務(wù)五設(shè)計(jì)導(dǎo)航頁面知識準(zhǔn)備用戶為自己設(shè)計(jì)的軟件添加了諸多功能后，可能會遇到一個問題，即如何快速找到自己所需的功能。這在一般的軟件中是通過添加菜單、導(dǎo)航欄或者添加鏈接來解決的。由于本書設(shè)計(jì)的財(cái)務(wù)管理系統(tǒng)并沒有采用編程方式，因此無法自定義菜單和選項(xiàng)卡。用戶要達(dá)到相同的目的，可以在工作簿中添加一個導(dǎo)航頁面，利用工作表鏈接的方法快速到達(dá)指定頁面。知識準(zhǔn)備科目匯總表、科目余額表、總分類賬和明細(xì)分類賬都涉及指定月份的功能，而科目余額表又為總分類賬和明細(xì)分類賬提供了期初余額，這就要求上述表格中的數(shù)據(jù)要保持一致性。因此，可以在導(dǎo)航頁面上設(shè)置月份信息，而其他表格的月份信息都和導(dǎo)航頁面中指定的月份信息一致，從而保證不至于因?yàn)樵路莶煌蕉l(fā)生期初余額引用數(shù)據(jù)上的錯誤。任務(wù)目標(biāo)利用導(dǎo)航頁面完成在各個頁面之間的跳轉(zhuǎn)。任務(wù)操作(1)創(chuàng)建一張新的工作表，將工作表的名稱更改為“首頁”。(2)選擇“文件”→“選項(xiàng)”命令，打開“Excel選項(xiàng)”對話框，選擇“高級”選項(xiàng)卡，在“此工作表的顯示選項(xiàng)”中取消選中“顯示行和列標(biāo)題”和“顯示網(wǎng)格線”復(fù)選框，如圖3-60所示，單擊“確定”按鈕，完成去除網(wǎng)格線和行列標(biāo)題的操作。1.導(dǎo)航頁面圖3-60不顯示網(wǎng)格線和行列標(biāo)題任務(wù)操作(3)選擇“插入”選項(xiàng)卡，執(zhí)行“插圖”組中的“聯(lián)機(jī)圖片”命令，打開如圖3-61所示的“聯(lián)機(jī)圖片”對話框，在搜索范圍內(nèi)輸入“計(jì)算機(jī)”，按Enter鍵確認(rèn)后，選擇一張合適的圖片，單擊“插入”按鈕，完成圖片的插入操作。1.導(dǎo)航頁面圖3-61插入圖片任務(wù)操作(4)將圖片放置在合適的位置，調(diào)整到合適的大小，并在圖片下端輸入文字“賬務(wù)處理”，完成后的結(jié)果如圖3-62所示。1.導(dǎo)航頁面圖3-62插入圖片后的首頁任務(wù)操作(1)在H8單元格內(nèi)輸入“科目代碼表”。(2)在I8單元格內(nèi)輸入“憑證輸入”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組內(nèi)的“超鏈接”命令，打開如圖3-63所示的“插入超鏈接”對話框，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“憑證輸入”工作表，單擊“確定”按鈕，完成憑證輸入超鏈接的指定。2.設(shè)置導(dǎo)航功能圖3-63超鏈接到指定頁面任務(wù)操作(3)在J8單元格內(nèi)輸入“憑證編輯”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組內(nèi)的“超鏈接”命令，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“憑證編輯”工作表，單擊“確定”按鈕，完成憑證編輯超鏈接的指定。(4)在K8單元格內(nèi)輸入“憑證審核打印”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組內(nèi)的“超鏈接”命令，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“憑證審核與打印”工作表，單擊“確定”按鈕，完成憑證審核與打印超鏈接的指定。2.設(shè)置導(dǎo)航功能任務(wù)操作(5)在G10單元格中輸入“查詢月份”，選中H10單元格，設(shè)置邊框?yàn)殡p下畫線。選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)”組中的“數(shù)據(jù)驗(yàn)證”命令，打開如圖3-64所示的“數(shù)據(jù)驗(yàn)證”對話框，在“設(shè)置”選項(xiàng)卡的“允許”下拉列表中選擇“序列”，手工輸入序列的內(nèi)容“1,2,3,4,5,6,7,8,9,10,11,12”。選擇“出錯警告”選項(xiàng)卡，標(biāo)題為“月份錯誤”，錯誤信息指定為“指定的月份信息錯誤”，單擊“確定”按鈕，完成月份的指定。輸入2月份的記錄。2.設(shè)置導(dǎo)航功能任務(wù)操作2.設(shè)置導(dǎo)航功能圖3-64設(shè)置月份任務(wù)操作(6)在I10單元格內(nèi)輸入“科目余額表”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組中的“超鏈接”命令，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“科目余額表”工作表，單擊“確定”按鈕，完成科目余額表超鏈接的指定。(7)在J10單元格內(nèi)輸入“總分類賬”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組內(nèi)的“超鏈接”命令，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“總分類賬”工作表，單擊“確定”按鈕，完成總分類賬超鏈接的指定。(8)在K10單元格內(nèi)輸入“明細(xì)分類賬”，選擇“插入”選項(xiàng)卡，執(zhí)行“鏈接”組內(nèi)的“超鏈接”命令，選擇鏈接到的位置為“本文檔中的位置”，并指定文檔中選擇的位置為“明細(xì)分類賬”工作表，單擊“確定”按鈕，完成明細(xì)分類賬超鏈接的指定。2.設(shè)置導(dǎo)航功能任務(wù)操作(9)選中“科目匯總表”工作表，選中D2單元格，在編輯欄內(nèi)輸入公式“=首頁!H10”，完成查詢月份的指定。(10)選中“科目余額表”工作表，選中G2單元格，在編輯欄內(nèi)輸入公式“=首頁!H10”，完成查詢月份的指定。(11)選中“總分類賬”工作表，選中F2單元格，在編輯欄內(nèi)輸入公式“=首頁!H10”，完成查詢月份的指定。(12)選中“明細(xì)分類賬”工作表，選中F2單元格，在編輯欄內(nèi)輸入公式“=首頁!H10”，完成查詢月份的指定。創(chuàng)建完成后的導(dǎo)航頁面如圖3-65所示。2.設(shè)置導(dǎo)航功能任務(wù)操作2.設(shè)置導(dǎo)航功能圖3-65創(chuàng)建完成后的導(dǎo)航頁面本項(xiàng)目主要介紹了科目匯總表、科目余額表、總分類賬、明細(xì)分類賬的創(chuàng)建以及導(dǎo)航頁面的設(shè)計(jì)等內(nèi)容。通過學(xué)習(xí)本項(xiàng)目內(nèi)容，學(xué)習(xí)者可以了解MicrosoftQuery的使用方法和數(shù)據(jù)驗(yàn)證方法，掌握總分類賬和科目余額表的結(jié)構(gòu)以及MicrosoftQuery提取數(shù)據(jù)的方法，掌握設(shè)計(jì)明細(xì)分類賬的方法。項(xiàng)目小結(jié)項(xiàng)目練習(xí)1.根據(jù)福興公司發(fā)生的業(yè)務(wù)，編制公司1月份的科目匯總表。2.根據(jù)福興公司發(fā)生的業(yè)務(wù)，編制公司1月份的科目余額表。3.根據(jù)福興公司發(fā)生的業(yè)務(wù)，編制公司1月份的總分類賬。4.根據(jù)福興公司發(fā)生的業(yè)務(wù)，編制公司1月份的明細(xì)分類賬。5.創(chuàng)建一個導(dǎo)航頁面，通過單擊超鏈接能夠快速到達(dá)憑證輸入、憑證修改與刪除、憑證審核與打印頁面,輸入指定的月份信息能夠快速生成科目余額表、總分類賬和明細(xì)分類賬。謝謝觀看Excel在會計(jì)與財(cái)務(wù)管理中的應(yīng)用項(xiàng)目四固定資產(chǎn)管理(1)理解固定資產(chǎn)折舊的基礎(chǔ)知識；(2)掌握固定資產(chǎn)的管理；(3)掌握固定資產(chǎn)折舊的計(jì)算；(4)學(xué)會應(yīng)用Excel解決實(shí)際案例中遇到的固定資產(chǎn)管理問題。學(xué)習(xí)目標(biāo)固定資產(chǎn)是指企業(yè)為生產(chǎn)產(chǎn)品、提供勞務(wù)、出租或者經(jīng)營管理而持有的、使用時間超過一年及以上，價值達(dá)到一定標(biāo)準(zhǔn)并且在使用過程中能夠保持其原有的物資形態(tài)的非貨幣性資產(chǎn)，如房屋、建筑物、生產(chǎn)用的各種機(jī)器設(shè)備等。固定資產(chǎn)是企業(yè)開展生產(chǎn)經(jīng)營的必備資產(chǎn)，具有使用時間長、單位價值大等特點(diǎn)。在日常生產(chǎn)經(jīng)營活動中，固定資產(chǎn)會隨著使用年限的增加而產(chǎn)生相應(yīng)的損耗，并且固定資產(chǎn)的價值也會越來越低，所以企業(yè)需要對固定資產(chǎn)進(jìn)行折舊，同時企業(yè)還面臨日常維護(hù)、修理等問題，這都是需要進(jìn)行會計(jì)處理的經(jīng)濟(jì)活動。在智能化大數(shù)據(jù)的時代背景下，固定資產(chǎn)的損耗在Excel中應(yīng)當(dāng)如何處理？采用什么方法？不同的方法在Excel中是否有不同的計(jì)算方法？本項(xiàng)目就針對這些問題展開講解。情景引入目錄CONTENTS固定資產(chǎn)的管理1固定資產(chǎn)折舊的計(jì)算方法2創(chuàng)建折舊費(fèi)用數(shù)據(jù)透視表3任務(wù)一固定資產(chǎn)的管理知識準(zhǔn)備固定資產(chǎn)的取得登記是固定資產(chǎn)進(jìn)入會計(jì)信息系統(tǒng)的第一步，可以看作固定資產(chǎn)管理的起點(diǎn)，所以企業(yè)在取得固定資產(chǎn)后就要在固定資產(chǎn)卡片中進(jìn)行登記。固定資產(chǎn)卡片是固定資產(chǎn)管理的基礎(chǔ)數(shù)據(jù)，在手工賬時代，企業(yè)使用的是一張一張的卡片；在數(shù)字化智能時代可以將數(shù)據(jù)引入計(jì)算機(jī)領(lǐng)域，避免手工賬難以儲存、易丟失等缺點(diǎn)。本任務(wù)介紹的是一種比較普遍的固定資產(chǎn)卡片在Excel中的編制方法，不同的企業(yè)在實(shí)操上對于固定資產(chǎn)卡片的設(shè)計(jì)可能會存在一定的格式誤差。知識準(zhǔn)備本任務(wù)主要從以下三個方面展開：在Excel中編制固定資產(chǎn)卡片參數(shù)表、增加固定資產(chǎn)和查詢固定資產(chǎn)。在參數(shù)設(shè)置環(huán)節(jié)，根據(jù)企業(yè)需求進(jìn)行選擇設(shè)置，如原值、型號、采購日期、折舊方法、折舊年限、凈殘值、凈殘值率、折舊時間等。任務(wù)目標(biāo)(1)在Excel中編制固定資產(chǎn)卡片參數(shù)表，根據(jù)企業(yè)需求設(shè)置相關(guān)的基礎(chǔ)信息；(2)掌握增加固定資產(chǎn)的兩種方法；(3)掌握查詢固定資產(chǎn)的兩種方法。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，該公司目前的固定資產(chǎn)采購信息如表4-1所示。任務(wù)操作(1)啟動Excel2010應(yīng)用程序，將工作表保存為“固定資產(chǎn)管理.xlsm”，將“Sheet1”改為“固定資產(chǎn)卡片”，如圖4-1所示，然后輸入相應(yīng)的數(shù)據(jù)，如卡片編號、固定資產(chǎn)名稱、型號規(guī)格、所屬部門，原值、開始使用時間、預(yù)計(jì)使用年限、預(yù)計(jì)凈殘值、折舊方法、年折舊額等。1.在Excel中編制固定資產(chǎn)卡片參數(shù)表圖4-1

新建表格任務(wù)操作(2)設(shè)置固定資產(chǎn)使用狀態(tài)。選中“使用狀態(tài)”列，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框，選擇“設(shè)置”選項(xiàng)卡，在“允許”下拉列表中選擇“序列”，在“來源”文本框中輸入“在用，季節(jié)性閑置，閑置”，單擊“確定”按鈕，如圖4-2和圖4-3所示?！咎崾尽吭凇皝碓础毕吕斜碇校煌x項(xiàng)之間要用英文狀態(tài)下的“,”隔開。1.在Excel中編制固定資產(chǎn)卡片參數(shù)表任務(wù)操作1.在Excel中編制固定資產(chǎn)卡片參數(shù)表圖4-2選擇“數(shù)據(jù)有效性”命令圖4-3“數(shù)據(jù)有效性”對話框任務(wù)操作(3)如果“折舊方法”“部門名稱”等列也想采用先編輯好后選擇的方式，則重復(fù)步驟(2)的操作即可。(4)根據(jù)企業(yè)購入的固定資產(chǎn)，按照對應(yīng)的基本信息填入對應(yīng)的表格，如圖4-4所示。1.在Excel中編制固定資產(chǎn)卡片參數(shù)表圖4-4輸入基本信息任務(wù)操作(5)在實(shí)際工作中，如果表格列數(shù)較多，不方便查看，則可以使用拆分窗口的方式將表格拆分為幾個窗口，用戶可以根據(jù)需求滾動查看每個窗口中的內(nèi)容。選中需要隔開的單元格，選擇“視圖”選項(xiàng)卡，執(zhí)行“窗口”組中的“拆分”命令即可。如果要取消拆分，可采用同樣的操作步驟，再次執(zhí)行“拆分”命令即可，如圖4-5所示。1.在Excel中編制固定資產(chǎn)卡片參數(shù)表圖4-5拆分表格任務(wù)操作當(dāng)企業(yè)增加各種固定資產(chǎn)時，按照會計(jì)核算的及時性等原則，需要及時進(jìn)行記錄。當(dāng)需要將新的固定資產(chǎn)進(jìn)行記錄時，可以采取兩種方法：直接手動輸入和使用“記錄單”功能添加，這里只介紹通過“記錄單”功能添加。(1)如果Excel頁面沒有“記錄單”命令，則需要用戶手動將其添加到頁面。隨機(jī)選中表格中的一列，選擇“文件”→“選項(xiàng)”命令，打開“Excel選項(xiàng)”對話框，選擇“快速訪問工具欄”選項(xiàng)卡，在“從下列位置選擇命令”下拉列表中選擇“不在功能區(qū)中的命令”，在其下的列表框中選擇“記錄單”，單擊“添加”按鈕，再單擊“確定”按鈕，則快捷功能按鍵添加完成，如圖4-6和圖4-7所示。2.增加固定資產(chǎn)任務(wù)操作2.增加固定資產(chǎn)圖4-6選擇“選項(xiàng)”命令圖4-7“Excel選項(xiàng)”對話框任務(wù)操作(2)選中添加的區(qū)域，單擊“記錄單”按鈕，在打開的“固定資產(chǎn)卡片”對話框中輸入新增加的固定資產(chǎn)的信息，單擊“新建”按鈕，則新增信息輸入完成，如圖4-8所示。2.增加固定資產(chǎn)圖4-8增加固定資產(chǎn)信息任務(wù)操作固定資產(chǎn)在因?yàn)楦鞣N原因減少或者內(nèi)部調(diào)劑以及狀態(tài)發(fā)生變化時，需要對其各項(xiàng)信息進(jìn)行變更或者登記。要想在眾多的信息中快速準(zhǔn)確地找到需要編輯的數(shù)據(jù)欄，可以使用Excel中的查找、篩選等功能。其具體操作方法有以下兩種。方法一：查找。打開Excel表格，選擇“開始”選項(xiàng)卡，執(zhí)行“編輯”組中的“查找和選擇”命令，打開“查找和替換”對話框，輸入需要查找的數(shù)據(jù)，單擊“查找全部”按鈕。也可以按Ctrl+F組合鍵，打開“查找和替換”對話框，如圖4-9所示。3.查詢固定資產(chǎn)任務(wù)操作3.查詢固定資產(chǎn)圖4-9查找任務(wù)操作方法二：篩選。打開Excel表格，選中要篩選的單元格區(qū)域，選擇“開始”選項(xiàng)卡，執(zhí)行“編輯”組中的“排序和篩選”下的“篩選”命令，此時選中的行列會出現(xiàn)倒三角按鈕，單擊倒三角按鈕，在搜索框中輸入篩選的內(nèi)容，選中出現(xiàn)的篩選結(jié)果復(fù)選框，單擊“確定”按鈕，如圖4-10和圖4-11所示。通過以上兩種方法都可以很快捷地找到用戶需要的數(shù)據(jù)，從而節(jié)約時間。3.查詢固定資產(chǎn)任務(wù)操作3.查詢固定資產(chǎn)圖4-10篩選圖4-11篩選設(shè)置任務(wù)二固定資產(chǎn)折舊的計(jì)算方法知識準(zhǔn)備固定資產(chǎn)的確認(rèn)條件之一是達(dá)到一定標(biāo)準(zhǔn)，而該標(biāo)準(zhǔn)的價值是比較大的，所以固定資產(chǎn)的成本采取的方式是在使用過程中按月分?jǐn)偤陀?jì)提，將其成本轉(zhuǎn)移到產(chǎn)品或者勞務(wù)成本中。所以，企業(yè)需要對固定資產(chǎn)計(jì)提折舊。折舊的計(jì)算方法主要有平均年限法、雙倍余額遞減法、年限總和法等。注意：企業(yè)會計(jì)的折舊時間點(diǎn)當(dāng)月增加當(dāng)月不計(jì)提，次月開始計(jì)提；當(dāng)月減少當(dāng)月計(jì)提。本任務(wù)將采用這三種方法對固定資產(chǎn)的折舊方法在Excel中的運(yùn)用進(jìn)行闡述。任務(wù)目標(biāo)掌握三種不同的折舊方法——平均年限法、雙倍余額遞減法、年限總和法在Excel中運(yùn)用公式進(jìn)行折舊的計(jì)算。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，該公司目前的固定資產(chǎn)信息如表4-1所示。任務(wù)操作(1)建立一張固定資產(chǎn)折舊表，輸入以下基本信息：固定資產(chǎn)名稱、型號、開始使用日期、資產(chǎn)性質(zhì)、原值、凈殘值、折舊方法、已計(jì)提月份、月折舊額等。如果表格中設(shè)置的是凈殘值率，則需要計(jì)算凈殘值，如圖4-12所示。1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份圖4-12新建工作表任務(wù)操作(2)凈殘值計(jì)算方法：根據(jù)計(jì)算公式“固定資產(chǎn)殘值=固定資產(chǎn)原值×凈殘值率”，在H2單元格中輸入公式“=F2*G2”，按Enter鍵得到計(jì)算結(jié)果。將鼠標(biāo)指針放在H2單元格右下角，當(dāng)鼠標(biāo)指針呈實(shí)心十字架時，按住鼠標(biāo)左鍵往下拖，直到需要填充的最后一個單元格，松開鼠標(biāo)，則所有數(shù)據(jù)填充完成，數(shù)據(jù)生成，如圖4-13所示。1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份圖4-13計(jì)算凈殘值任務(wù)操作(3)已計(jì)提月份的計(jì)算方法：在M2單元格中輸入公式“=INT(DAYS360(D2,DATE(2022,6,30))/30)”，按Enter鍵得到計(jì)算結(jié)果。將鼠標(biāo)指針放在M2單元格右下角，當(dāng)鼠標(biāo)指針呈實(shí)心十字架時，按住鼠標(biāo)左鍵往下拖，直到需要填充的最后一個單元格，松開鼠標(biāo)，則所有數(shù)據(jù)填充完成，數(shù)據(jù)生成，如圖4-14所示。1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份圖4-14計(jì)算已計(jì)提月份【提示】1)DATE函數(shù)含義：返回日期時間代碼中代表日期的數(shù)字。函數(shù)語法：DAY(year,month,day)任務(wù)操作1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份參數(shù)說明：?year：可以為1～4位數(shù)字。MicrosoftExcel將根據(jù)所使用的日期系統(tǒng)解釋year參數(shù)。默認(rèn)情況下，MicrosoftExcelforWindows將使用1900日期系統(tǒng)。如果year的值為0(零)～1899(包含)，則Excel會將該值加上1900后再計(jì)算年份。例如，DATE(108,1,2)將返回2008年1月2日(1900+108)。如果year的值為1900～9999(包含)，則Excel將使用該數(shù)值作為年份。例如，DATE(2008,1,2)將返回2008年1月2日。如果year小于0或大于等于10000，則Excel將返回錯誤值“#NUM!”。任務(wù)操作1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份?month：每年中月份的數(shù)字。如果所輸入的月份大于12，將從指定年份的一月份開始往上加算。例如，DATE(2008,14,2)返回代表2009年2月2日的序列號。?day：在該月份中第幾天的數(shù)字。如果day大于該月份的最大天數(shù)，則將從指定月份的第一天開始往上累加。例如，DATE(2008,1,35)返回代表2008年2月4日的序列號。任務(wù)操作1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份2)Days函數(shù)含義：可以返回兩個日期相差的天數(shù)。函數(shù)語法：DAYS(end_date,start_date)參數(shù)說明：end_date和start_date：必需參數(shù)，可以手動輸入，或者是引用單元格。第一個為結(jié)束日期，第二個為開始日期，按Enter鍵就可以得到兩個日期相差的天數(shù)。任務(wù)操作1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份3)INT函數(shù)含義：將數(shù)字向下舍入到最接近的整數(shù)。語法格式：INT(number)參數(shù)說明：number：需要進(jìn)行向下舍入取整的實(shí)數(shù)。使用此函數(shù)需要注意：INT函數(shù)是取整函數(shù)，不進(jìn)行四舍五入，而是直接去掉小數(shù)部分取整。任務(wù)操作1.計(jì)算資產(chǎn)的殘值和已經(jīng)計(jì)提的月份任務(wù)操作平均年限法又稱為直線法，是將固定資產(chǎn)在有限的使用年限內(nèi)平均分?jǐn)傆?jì)提折舊的一種攤銷方法。采用平均年限法時，每年或者每個月計(jì)提的折舊額是相等的，在計(jì)算過程中影響計(jì)算數(shù)據(jù)的主要因素有三個，即固定資產(chǎn)原值、凈殘值和預(yù)計(jì)使用年限。平均年限法的計(jì)算公式如下：2.平均年限法

任務(wù)操作(1)根據(jù)公式“年折舊額=(原值-預(yù)計(jì)凈殘值)/預(yù)計(jì)使用年限”，在K2單元格中輸入公式“=(G2-I2)/F2”，按Enter鍵得到結(jié)果，如圖4-15所示。2.平均年限法圖4-15計(jì)算年折舊額任務(wù)操作(2)將鼠標(biāo)指針放在M2單元格右下角，當(dāng)其呈實(shí)心十字時(填充柄)按住鼠標(biāo)左鍵往下拖，得出表格數(shù)據(jù)(注意，往下填充的前提是必須要采用同樣的折舊方法)，如圖4-16所示。2.平均年限法圖4-16填充數(shù)據(jù)任務(wù)操作(3)計(jì)算月折舊額。在L2單元格中輸入公式“=K2/12”，按Enter鍵得到結(jié)果，如圖4-17所示。2.平均年限法圖4-17計(jì)算月折舊額任務(wù)操作(4)將鼠標(biāo)指針放在N2單元格右下角，當(dāng)其呈實(shí)心十字時(填充柄)按住鼠標(biāo)左鍵往下拖，得出表格數(shù)據(jù)，如圖4-18所示。2.平均年限法圖4-18填充數(shù)據(jù)【提示】在Excel中，除了采用上述方法計(jì)算月折舊額外，還可以使用SLN函數(shù)計(jì)算月折舊額。含義：基于直線折舊法返回某項(xiàng)資產(chǎn)每期的線性折舊值，即平均折舊值。函數(shù)語法：SLN(cost,salvage,life)參數(shù)說明：?cost：資產(chǎn)原值。?salvage：資產(chǎn)在折舊期末的價值，即資產(chǎn)殘值。?life：折舊期限，有時也指使用壽命。所有參數(shù)值必須為正數(shù)，否則返回值為“#NUM”。任務(wù)操作2.平均年限法任務(wù)操作雙倍余額遞減法是加速折舊法的一種，其假設(shè)固定資產(chǎn)的服務(wù)潛力在前期消耗較大，在后期消耗較少，為此在使用前期多提折舊，后期少提折舊，從而相對加速折舊。雙倍余額遞減法是指在不考慮固定資產(chǎn)預(yù)計(jì)殘值的情況下，將每期固定資產(chǎn)的期初賬面凈值乘以一個固定不變的百分率，計(jì)算折舊額的一種加速折舊的方法。雙倍余額遞減法的計(jì)算公式如下：3.雙倍余額遞減法

年折舊額＝固定資產(chǎn)期初折余價值×年折舊率

【提示】在Excel中可以用DDB函數(shù)來計(jì)算。DDB函數(shù)用雙倍余額遞減法或其他指定方法，返回指定期間內(nèi)某項(xiàng)固定資產(chǎn)的折舊值。函數(shù)語法：DDB(Cost,Salvage,Life,Period,[Factor])參數(shù)說明：?Cost：資產(chǎn)原值。?Salvage：固定資產(chǎn)使用年限終了時的估計(jì)殘值(有時也稱為資產(chǎn)殘值)。?Life：資產(chǎn)的折舊期數(shù)(有時也稱為資產(chǎn)的使用壽命)。?Period：要計(jì)算折舊的時期。Period必須使用與Life相同的單位。?Factor：余額遞減速率。如果省略影響因素，則假定其為2(雙倍余額遞減法)。以上參數(shù)都必須是正數(shù)。任務(wù)操作3.雙倍余額遞減法任務(wù)操作(1)打開固定資產(chǎn)管理表格，切換到“固定資產(chǎn)折舊”工作表，選中K12單元格，選擇“公式”選項(xiàng)卡，執(zhí)行“函數(shù)庫”組中的“插入函數(shù)”命令，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表中選擇函數(shù)類型“財(cái)務(wù)”，找到并選擇“DDB”，單擊“確定”按鈕，如圖4-19所示。3.雙倍余額遞減法圖4-19選擇公式任務(wù)操作(2)在打開的“函數(shù)參數(shù)”對話框中設(shè)置對應(yīng)的參數(shù)。在“Cost”文本框中輸入“G12”(可以手動輸入，也可以直接選中G12單元格)，在“Salvage”文本框中輸入“I12”，在“Life”文本框中輸入“F12*12”，在“Period”文本框中輸入“M12”，單擊“確定”按鈕，得出表格數(shù)據(jù)，如圖4-20所示。3.雙倍余額遞減法圖4-20輸入數(shù)據(jù)任務(wù)操作(3)將鼠標(biāo)指針放在K12單元格右下角，當(dāng)鼠標(biāo)指針呈實(shí)心十字時往下拖，填充所有表格，生成數(shù)據(jù)，如圖4-21所示。3.雙倍余額遞減法圖4-21填充數(shù)據(jù)任務(wù)操作年限總和法又稱折舊年限積數(shù)法或級數(shù)遞減法，是將固定資產(chǎn)的原值減去殘值后的凈額乘以一個逐年遞減的分?jǐn)?shù)計(jì)算確定固定資產(chǎn)折舊額的一種方法。逐年遞減分?jǐn)?shù)的分子代表固定資產(chǎn)尚可使用的年數(shù)，分母代表使用年數(shù)的逐年數(shù)字總和。假定使用年限為n年，分母即為1+2+3+…+n=n(n+1)÷2，其折舊的計(jì)算公式如下：4.年限總和法

【提示】在Excel中可以用SYD函數(shù)來計(jì)算。SYD函數(shù)返回某項(xiàng)資產(chǎn)按年限總和法計(jì)算的指定期間的折舊值。函數(shù)語法：SYD(Cost,Salvage,Life,Period)參數(shù)說明：?Cost：資產(chǎn)原值。?Salvage：固定資產(chǎn)使用年限終了時的估計(jì)殘值(有時也稱為資產(chǎn)殘值)。?Life：資產(chǎn)的折舊期數(shù)(有時也稱為資產(chǎn)的使用壽命)。?Period：要計(jì)算折舊的時期。Period必須使用與Life相同的單位。任務(wù)操作4.年限總