Excel在會計與財務(wù)管理中的應(yīng)用 課件 項目一 現(xiàn)金日記賬

Excel在會計與財務(wù)管理中的應(yīng)用項目一現(xiàn)金日記賬(1)了解現(xiàn)金日記賬的手工記賬方法；(2)掌握Excel中表對象和數(shù)據(jù)透視表的基本使用方法；(3)掌握SUBTOTAL和IF等函數(shù)的使用方法；(4)能夠熟練繪制表格及使用函數(shù)和公式；(5)學(xué)會應(yīng)用Excel解決實際案例中遇到的現(xiàn)金日記賬問題。學(xué)習(xí)目標(biāo)日記賬包括現(xiàn)金日記賬和銀行日記賬，由出納人員（資金會計）按照業(yè)務(wù)流程發(fā)生的時間順序逐筆登記，逐日反映庫存現(xiàn)金或者銀行賬戶存款的收支余情況。由于現(xiàn)金日記賬和銀行日記賬的記賬方式完全相同，只是體現(xiàn)的資金類型不同，因此本項目僅以現(xiàn)金日記賬為例介紹通過Excel設(shè)計日記賬的兩種方法，一種是通過表對象和數(shù)據(jù)透視表完成設(shè)計，另一種是通過函數(shù)和公式的設(shè)定完成現(xiàn)金日記賬的功能。通過本項目的學(xué)習(xí)，學(xué)習(xí)者可以了解利用Excel進(jìn)行財務(wù)設(shè)計的思路和方法。情景引入簡易現(xiàn)金日記賬1賬頁式現(xiàn)金日記賬2目錄CONTENTS任務(wù)一簡易現(xiàn)金日記賬知識準(zhǔn)備Excel本身不是一種結(jié)構(gòu)嚴(yán)謹(jǐn)?shù)臄?shù)據(jù)庫，只是它的格式和數(shù)據(jù)庫中的數(shù)據(jù)表太像了，以至于用戶常常期待可以通過Excel實現(xiàn)某些只有數(shù)據(jù)庫才具有的功能。Excel中進(jìn)行數(shù)據(jù)的計算有兩種基本的思路，一種是使用數(shù)據(jù)透視表，另一種是使用函數(shù)。數(shù)據(jù)透視表是Excel中功能最為強(qiáng)大的工具，具有分析數(shù)據(jù)快速、簡單的特點，尤其對于大數(shù)據(jù)量的分析計算，效率很高。本任務(wù)的內(nèi)容就是按照數(shù)據(jù)透視表的思路對現(xiàn)金日記賬的余額進(jìn)行計算。數(shù)據(jù)透視表是利用Excel進(jìn)行會計電算化設(shè)計的常用方法。任務(wù)目標(biāo)（1）設(shè)計并完成福源公司2022年1月和2月現(xiàn)金日記賬；（2）借方發(fā)生額、貸方發(fā)生額和余額數(shù)據(jù)必須保留兩位小數(shù)，并按照“會計專用”格式顯示；（3）按日計算每日余額，按月分析每月的收支情況。任務(wù)資料福源公司在2022年1月和2月發(fā)生了如下和現(xiàn)金有關(guān)的業(yè)務(wù)：(1)1月1日轉(zhuǎn)入上年余額2200元。(2)1月3日支付差旅費(fèi)320元。(3)1月5日支付12月電費(fèi)550元。(4)1月5日提現(xiàn)金25000元。(5)1月6日支付招待費(fèi)3500元。(6)1月6日支付差旅費(fèi)2400元。(7)1月6日支付手續(xù)費(fèi)35元。(8)1月14日支付辦公用品費(fèi)6400元。(9)1月15日購買打印機(jī)1500元。(10)2月2日支付辦公室電話費(fèi)650元。(11)2月2日出售廢舊包裝物320元。(12)2月2日支付1月電費(fèi)3320元。(13)2月3日支付差旅費(fèi)2500元。(14)2月7日支付綠化苗木款4000元。(15)2月7日支付招待費(fèi)2200元。任務(wù)操作（1）打開Excel，將工作簿保存為“簡易日記賬.xlsx”，雙擊Sheet1工作表標(biāo)簽，將Sheet1重命名為“現(xiàn)金日記賬”。（2）在A1:H1單元格區(qū)域內(nèi)依次輸入年、月、日、編號、內(nèi)容摘要、借方發(fā)生額、貸方發(fā)生額以及余額8個字段，如圖1-1所示。1.數(shù)據(jù)輸入設(shè)計圖1-1

標(biāo)題行任務(wù)操作（3）選中A:D列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在打開的“設(shè)置單元格格式”對話框中選擇“數(shù)字”標(biāo)簽。在“分類”列表框中選擇類型為“數(shù)值”，選擇“小數(shù)位數(shù)”為0，如圖1-2所示。單擊“確定”按鈕，完成A列到D列的年、月、日以及編號的數(shù)據(jù)類型設(shè)置。1.數(shù)據(jù)輸入設(shè)計圖1-2設(shè)置年月日以及編號的數(shù)據(jù)類型任務(wù)操作（4）選中E列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，如圖1-3所示。從打開的“設(shè)置單元格格式”對話框中選擇“數(shù)字”標(biāo)簽，在“分類”列表框中選擇類型為“文本”，完成對E列內(nèi)容摘要的數(shù)據(jù)類型的設(shè)置。1.數(shù)據(jù)輸入設(shè)計圖1-3選擇“設(shè)置單元格格式”命令任務(wù)操作（5）選中F:H列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，如圖1-3所示。從打開的“設(shè)置單元格格式”對話框中選擇“數(shù)字”標(biāo)簽，在“分類”列表框中選擇類型為“會計專用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號”設(shè)置為無，完成對F列到H列借貸方發(fā)生額以及余額的數(shù)據(jù)類型設(shè)置。1.數(shù)據(jù)輸入設(shè)計任務(wù)操作（1）選中D2單元格，在編輯欄內(nèi)輸入公式“=ROW()-1”，完成“編號”字段的設(shè)置?！咎崾尽浚壕幪柡托刑柕年P(guān)系就是當(dāng)前記錄所在的行號減去1，計算當(dāng)前記錄所在行在Excel中使用ROW函數(shù)。（2）選中H2單元格，在編輯欄內(nèi)輸入公式“=F2”，完成第1條記錄余額的計算。（3）從A2單元格開始，輸入第1條記錄的其余部分。輸入的第1條記錄如圖1-4所示。2.創(chuàng)建公式圖1-4第1條記錄任務(wù)操作（4）選中A3單元格，在編輯欄內(nèi)輸入公式“=2022”，接著輸入第2條記錄，其中D3單元格中輸入公式“=ROW()-1”，H3單元格中輸入公式“=H2+F3-G3”。輸入的第2條記錄如圖1-5所示。2.創(chuàng)建公式圖1-5第2條記錄任務(wù)操作（5）選中A1單元格，選擇“插入”選項卡，執(zhí)行“表格”組中的“表格”命令，如圖1-6所示。打開如圖1-7所示的“創(chuàng)建表”對話框，選中“表包含標(biāo)題”復(fù)選框，單擊“確定”按鈕，將單元格區(qū)域轉(zhuǎn)化為表。2.創(chuàng)建公式圖1-6“表格”命令圖1-7“創(chuàng)建表”對話框任務(wù)操作（6）選中A1單元格，選擇“表設(shè)計”選項卡，在“表格樣式選項”組中選擇“白色，表樣式淺色15”，如圖1-8所示。2.創(chuàng)建公式圖1-8“表格樣式”組任務(wù)操作（7）選擇“表設(shè)計”選項卡，在“屬性”組中將表名稱由默認(rèn)的“表1”更改為“現(xiàn)金日記賬”，完成表格名稱的設(shè)置，如圖1-9所示。2.創(chuàng)建公式圖1-9設(shè)置表對象名稱任務(wù)操作（8）繼續(xù)輸入剩余的記錄，最終結(jié)果如圖1-10所示。2.創(chuàng)建公式圖1-10輸入完成后的現(xiàn)金日記賬表格任務(wù)操作（1）選擇“現(xiàn)金日記賬”工作表的A1單元格，選擇“插入”選項卡，執(zhí)行“表格”組中的“數(shù)據(jù)透視表”命令，如圖1-11所示，打開“創(chuàng)建數(shù)據(jù)透視表”對話框。3.創(chuàng)建公式圖1-11“數(shù)據(jù)透視表”命令任務(wù)操作（2）選中“選擇一個表或區(qū)域”單選按鈕，已經(jīng)可以自動識別要進(jìn)行數(shù)據(jù)透視的區(qū)域。如果未能識別，則在“表/區(qū)域”后輸入“現(xiàn)金日記賬”。選擇放置數(shù)據(jù)透視表的位置為“新工作表”，如圖1-12所示，完成數(shù)據(jù)透視表數(shù)據(jù)源的設(shè)置。3.創(chuàng)建公式圖1-12設(shè)置數(shù)據(jù)透視表數(shù)據(jù)源任務(wù)操作（3）按照上述步驟打開如圖1-13所示的“數(shù)據(jù)透視表字段”對話框，將“月”字段和“日”字段拖動到“行”中，將“借方發(fā)生額”和“貸方發(fā)生額”字段拖動到“值”中，完成要顯示的字段的設(shè)置。3.創(chuàng)建公式圖1-13“數(shù)據(jù)透視表字段”對話框任務(wù)操作（4）單擊“數(shù)據(jù)透視表字段”對話框的“值”內(nèi)的“求和項:借方發(fā)生額”字段，選擇“值字段設(shè)置”命令，打開如圖1-14所示的“值字段設(shè)置”對話框。單擊“值字段設(shè)置”對話框中的“數(shù)字格式”按鈕，打開“設(shè)置單元格格式”對話框，選擇“數(shù)字”標(biāo)簽，在“分類”列表框中選擇類型為“會計專用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號”設(shè)置為無，單擊“確定”按鈕，完成字段計算類型和數(shù)字的設(shè)置。3.創(chuàng)建公式圖1-14“值字段設(shè)置”對話框任務(wù)操作（5）單擊“數(shù)據(jù)透視表字段”對話框的“值”內(nèi)的“求和項:貸方發(fā)生額”字段，選擇“值字段設(shè)置”命令，打開“值字段設(shè)置”對話框。單擊“值字段設(shè)置”對話框中的“數(shù)字格式”按鈕，打開“設(shè)置單元格格式”對話框，選擇“數(shù)字”標(biāo)簽，在“分類”列表框中選擇類型為“會計專用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號”設(shè)置為無，單擊“確定”按鈕，完成字段計算類型和數(shù)字的設(shè)置。3.創(chuàng)建公式任務(wù)操作（6）選中數(shù)據(jù)透視表中任意一個單元格，選擇“分析”選項卡，執(zhí)行“計算”組中的“字段、項目和集”下的“計算字段”命令(如圖1-15所示)，打開如圖1-16所示的“插入計算字段”對話框。3.創(chuàng)建公式圖1-15“字段、項目和集”下的“計算字段”命令圖1-16“插入計算字段”對話框（7）將“名稱”文本框中的“字段1”改為“余額總計”。（8）將“公式”文本框中的“0”刪除，選擇“字段”列表框中的“借方發(fā)生額”，單擊“插入字段”按鈕，再輸入減號，然后選擇“字段”列表框中的“貸方發(fā)生額”，單擊“插入字段”按鈕。單擊“確定”按鈕，完成插入計算字段的操作。任務(wù)操作3.創(chuàng)建公式任務(wù)操作（9）最終結(jié)果如圖1-17所示。3.創(chuàng)建公式圖1-17按月和日分類匯總的數(shù)據(jù)透視表任務(wù)二賬頁式現(xiàn)金日記賬知識準(zhǔn)備COUNTIF函數(shù)的功能是統(tǒng)計在指定范圍內(nèi)符合統(tǒng)計條件的單元格的個數(shù)。COUNTIF函數(shù)包括Range和Criteria兩個參數(shù)，完整的格式為COUNTIF(Range,Criteria)。Range:表示參與統(tǒng)計的單元格區(qū)域。Criteria：表示統(tǒng)計的條件。1.COUNTIF函數(shù)知識準(zhǔn)備SUBTOTAL函數(shù)對選定區(qū)域進(jìn)行分類匯總，其完整的格式為SUBTOTAL(Function_num,Ref1,Ref2,…)。Function_num：本示例參數(shù)的值為9，表示分類求和。Ref1,Ref2,…：表示分類計算的區(qū)域引用。在本示例中引用的是F6:F11單元格區(qū)域，如果在第6行和第11行之間插入空行，則該公式所引用的單元格區(qū)域會自動發(fā)生變化。2.SUBTOTAL函數(shù)任務(wù)目標(biāo)（1）完成賬頁式現(xiàn)金日記賬頁面設(shè)計；（2）在賬頁式現(xiàn)金日記賬中創(chuàng)建公式；（3）完成福源公司在2022年1月1日后發(fā)生的現(xiàn)金有關(guān)的業(yè)務(wù)。任務(wù)資料福源公司在2022年1月1日后發(fā)生了如下和現(xiàn)金有關(guān)的業(yè)務(wù)：(1)1月1日轉(zhuǎn)入上年余額2200元。(2)1月3日支付差旅費(fèi)320元。(3)1月5日支付12月電費(fèi)550元。(4)1月6日支付招待費(fèi)3500元。(5)1月6日支付差旅費(fèi)2400元。(6)1月6日支付手續(xù)費(fèi)35元。(7)1月7日提現(xiàn)金25000元。(8)1月14日支付辦公用品費(fèi)用6400元。任務(wù)資料(9)1月15日購買打印機(jī)1500元。(10)2月2日支付辦公室電話費(fèi)650元。(11)2月2日支付1月電費(fèi)3320元。(12)2月3日出售廢舊包裝物320元。(13)2月3日支付差旅費(fèi)2500元。(14)2月7日支付綠化苗木款4000元。(15)2月7日支付招待費(fèi)2200元。福源公司在2022年1月1日后發(fā)生了如下和現(xiàn)金有關(guān)的業(yè)務(wù)：任務(wù)操作（1）打開Excel，將文件保存為“賬頁式現(xiàn)金日記賬.xlsx”，選中Sheet1，重命名為“現(xiàn)金日記賬”。（2）在工作表內(nèi)輸入如圖1-18所示的現(xiàn)金日記賬界面，完成界面的設(shè)置，如圖1-18所示。1.繪制界面圖1-18完成后的界面任務(wù)操作(3)選中F、G和I列并右擊，從彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，如圖1-19所示。1.繪制界面圖1-19選擇“設(shè)置單元格格式”命令任務(wù)操作（4）打開“設(shè)置單元格格式”對話框，選擇“數(shù)字”標(biāo)簽，在“分類”列表框中選擇類型為“會計專用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號”設(shè)置為無，單擊“確定”按鈕，完成對指定列的數(shù)字格式的設(shè)置，如圖1-20所示。1.繪制界面圖1-20“設(shè)置單元格格式”對話框任務(wù)操作（1）選中I5單元格，在編輯欄內(nèi)輸入公式“=F5”；選中H5單元格，輸入“借”字，完成現(xiàn)金初始余額的計算。（2）選中B14單元格，在編輯欄內(nèi)輸入“=COUNTIF()”，將光標(biāo)移至括號內(nèi)，單擊“插入函數(shù)”按鈕，如圖1-21所示。2.創(chuàng)建公式圖1-21單擊“插入函數(shù)”按鈕任務(wù)操作打開如圖1-22所示的“函數(shù)參數(shù)”對話框，在Range參數(shù)后輸入“$E$6:E15”，在Criteria參數(shù)后輸入“本月合計”，確定本月月份。2.創(chuàng)建公式圖1-22COUNTIF函數(shù)任務(wù)操作【提示】這一步是利用COUNTIF函數(shù)自動計算月份，計算月份的原理就在于統(tǒng)計指定的單元格范圍內(nèi)“本月合計”單元格出現(xiàn)的次數(shù)。假定現(xiàn)金日記賬是從1月份開始記賬，那么很顯然“本月合計”在1月底時的出現(xiàn)次數(shù)為1，就表示1月份；在2月份底時的出現(xiàn)次數(shù)為2，就表示2月份。依此類推，通過“本月合計”出現(xiàn)的次數(shù)就可以知道正在匯總的月份為幾月。本例中使用“$E$6”，用絕對引用的方式則是表示今后統(tǒng)計時，總是將指定的E6單元格作為初始位置開始統(tǒng)計。2.創(chuàng)建公式任務(wù)操作

（3）選中B15單元格，在編輯欄內(nèi)輸入公式“=B14”，確定“本年累計”行的月份設(shè)置。（4）選中F14單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號內(nèi)，單擊“插入函數(shù)”按鈕，打開如圖1-23所示的“函數(shù)參數(shù)”對話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“F6:F13”，完成本月借方發(fā)生總額的計算。2.創(chuàng)建公式圖1-23SUBTOTAL函數(shù)任務(wù)操作【提示】SUBTOTAL函數(shù)的作用是在指定范圍內(nèi)進(jìn)行分類求和計算。雖然是進(jìn)行求和計算，但是此處不能使用Sum函數(shù)進(jìn)行匯總，原因是Sum函數(shù)計算時會將以前各月的借貸方累計發(fā)生額都加入計算范圍內(nèi)，從而對最終結(jié)果產(chǎn)生干擾，而SUBTOTAL則不會這樣。SUBTOTAL函數(shù)有一個特點是所引用的單元格區(qū)域中如果包含其他的SUBTOTAL函數(shù)，那么所有包含SUBTOTAL函數(shù)的單元格不會被計算在內(nèi)，所以可以通過SUBTOTAL函數(shù)避免重復(fù)計算。2.創(chuàng)建公式任務(wù)操作（5）選中G14單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號內(nèi)，單擊“插入函數(shù)”按鈕，打開“函數(shù)參數(shù)”對話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“G6:G13”，完成本月貸方發(fā)生總額的計算。（6）選中F15單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號內(nèi)，單擊“插入函數(shù)”按鈕，打開“函數(shù)參數(shù)”對話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“$F$6:F13”，完成本年借方累計發(fā)生額的計算。2.創(chuàng)建公式任務(wù)操作（7）選中G15單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號內(nèi)，單擊“插入函數(shù)”按鈕，打開“函數(shù)參數(shù)”對話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“$G$6:G13”，完成本年貸方累計發(fā)生額的計算。（8）SUBTOTAL(9,$G$6:G13)>0,″借″,IF($I$5+SUBTOTAL(9,$F$6:F13)-SUBTOTAL(9,$G$6:G13)<O,″貸″,″平″))”，完成余額方向的確定。（9）選中I15單元格，輸入“=ABS($I$5+F15-G15)”，完成余額的計算。2.創(chuàng)建公式任務(wù)操作（1）選中第11行并右擊，在彈出的快捷菜單中選擇“插入”命令，完成插入行的操作。（2）將數(shù)據(jù)填寫到現(xiàn)金日記賬1月份的對應(yīng)位置，1月份填報完成后的樣式如圖1-24所示3.持續(xù)記賬設(shè)計圖1-241月份的現(xiàn)金日記賬任務(wù)操作（3）選中第6～15行，選擇“開始”選項卡，執(zhí)行“剪貼板”中的“復(fù)制”命令，完成1月份數(shù)據(jù)區(qū)域的復(fù)制。（4）選中A16單元格并右擊，在彈出的快捷菜單中選擇“粘貼”命令，將1月份的數(shù)據(jù)全部粘貼過來。（5）選中第16～23行，按Delete鍵，刪除復(fù)制過來的1月份的數(shù)據(jù)。3.持續(xù)記賬設(shè)計任務(wù)操作（6）將2月份數(shù)據(jù)填寫到現(xiàn)金2月份的對應(yīng)位置，2月份填報完成后的樣式如圖1-25所示。3.持續(xù)記賬設(shè)計圖1-25輸入2月份的記錄本項目主要介紹了簡易現(xiàn)金日記賬和賬頁式現(xiàn)金日記賬的