Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用 課件 汪政杰 項(xiàng)目1-6 現(xiàn)金日記賬- 往來(lái)款項(xiàng)管理

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

標(biāo)題行任務(wù)操作（3）選中A:D列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在打開(kāi)的“設(shè)置單元格格式”對(duì)話框中選擇“數(shù)字”標(biāo)簽。在“分類(lèi)”列表框中選擇類(lèi)型為“數(shù)值”，選擇“小數(shù)位數(shù)”為0，如圖1-2所示。單擊“確定”按鈕，完成A列到D列的年、月、日以及編號(hào)的數(shù)據(jù)類(lèi)型設(shè)置。1.數(shù)據(jù)輸入設(shè)計(jì)圖1-2設(shè)置年月日以及編號(hào)的數(shù)據(jù)類(lèi)型任務(wù)操作（4）選中E列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，如圖1-3所示。從打開(kāi)的“設(shè)置單元格格式”對(duì)話框中選擇“數(shù)字”標(biāo)簽，在“分類(lèi)”列表框中選擇類(lèi)型為“文本”，完成對(duì)E列內(nèi)容摘要的數(shù)據(jù)類(lèi)型的設(shè)置。1.數(shù)據(jù)輸入設(shè)計(jì)圖1-3選擇“設(shè)置單元格格式”命令任務(wù)操作（5）選中F:H列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，如圖1-3所示。從打開(kāi)的“設(shè)置單元格格式”對(duì)話框中選擇“數(shù)字”標(biāo)簽，在“分類(lèi)”列表框中選擇類(lèi)型為“會(huì)計(jì)專(zhuān)用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號(hào)”設(shè)置為無(wú)，完成對(duì)F列到H列借貸方發(fā)生額以及余額的數(shù)據(jù)類(lèi)型設(shè)置。1.數(shù)據(jù)輸入設(shè)計(jì)任務(wù)操作（1）選中D2單元格，在編輯欄內(nèi)輸入公式“=ROW()-1”，完成“編號(hào)”字段的設(shè)置。【提示】：編號(hào)和行號(hào)的關(guān)系就是當(dāng)前記錄所在的行號(hào)減去1，計(jì)算當(dāng)前記錄所在行在Excel中使用ROW函數(shù)。（2）選中H2單元格，在編輯欄內(nèi)輸入公式“=F2”，完成第1條記錄余額的計(jì)算。（3）從A2單元格開(kāi)始，輸入第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單元格，選擇“插入”選項(xiàng)卡，執(zhí)行“表格”組中的“表格”命令，如圖1-6所示。打開(kāi)如圖1-7所示的“創(chuàng)建表”對(duì)話框，選中“表包含標(biāo)題”復(fù)選框，單擊“確定”按鈕，將單元格區(qū)域轉(zhuǎn)化為表。2.創(chuàng)建公式圖1-6“表格”命令圖1-7“創(chuàng)建表”對(duì)話框任務(wù)操作（6）選中A1單元格，選擇“表設(shè)計(jì)”選項(xiàng)卡，在“表格樣式選項(xiàng)”組中選擇“白色，表樣式淺色15”，如圖1-8所示。2.創(chuàng)建公式圖1-8“表格樣式”組任務(wù)操作（7）選擇“表設(shè)計(jì)”選項(xiàng)卡，在“屬性”組中將表名稱(chēng)由默認(rèn)的“表1”更改為“現(xiàn)金日記賬”，完成表格名稱(chēng)的設(shè)置，如圖1-9所示。2.創(chuàng)建公式圖1-9設(shè)置表對(duì)象名稱(chēng)任務(wù)操作（8）繼續(xù)輸入剩余的記錄，最終結(jié)果如圖1-10所示。2.創(chuàng)建公式圖1-10輸入完成后的現(xiàn)金日記賬表格任務(wù)操作（1）選擇“現(xiàn)金日記賬”工作表的A1單元格，選擇“插入”選項(xiàng)卡，執(zhí)行“表格”組中的“數(shù)據(jù)透視表”命令，如圖1-11所示，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框。3.創(chuàng)建公式圖1-11“數(shù)據(jù)透視表”命令任務(wù)操作（2）選中“選擇一個(gè)表或區(qū)域”單選按鈕，已經(jīng)可以自動(dòng)識(shí)別要進(jìn)行數(shù)據(jù)透視的區(qū)域。如果未能識(shí)別，則在“表/區(qū)域”后輸入“現(xiàn)金日記賬”。選擇放置數(shù)據(jù)透視表的位置為“新工作表”，如圖1-12所示，完成數(shù)據(jù)透視表數(shù)據(jù)源的設(shè)置。3.創(chuàng)建公式圖1-12設(shè)置數(shù)據(jù)透視表數(shù)據(jù)源任務(wù)操作（3）按照上述步驟打開(kāi)如圖1-13所示的“數(shù)據(jù)透視表字段”對(duì)話框，將“月”字段和“日”字段拖動(dòng)到“行”中，將“借方發(fā)生額”和“貸方發(fā)生額”字段拖動(dòng)到“值”中，完成要顯示的字段的設(shè)置。3.創(chuàng)建公式圖1-13“數(shù)據(jù)透視表字段”對(duì)話框任務(wù)操作（4）單擊“數(shù)據(jù)透視表字段”對(duì)話框的“值”內(nèi)的“求和項(xiàng):借方發(fā)生額”字段，選擇“值字段設(shè)置”命令，打開(kāi)如圖1-14所示的“值字段設(shè)置”對(duì)話框。單擊“值字段設(shè)置”對(duì)話框中的“數(shù)字格式”按鈕，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，在“分類(lèi)”列表框中選擇類(lèi)型為“會(huì)計(jì)專(zhuān)用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號(hào)”設(shè)置為無(wú)，單擊“確定”按鈕，完成字段計(jì)算類(lèi)型和數(shù)字的設(shè)置。3.創(chuàng)建公式圖1-14“值字段設(shè)置”對(duì)話框任務(wù)操作（5）單擊“數(shù)據(jù)透視表字段”對(duì)話框的“值”內(nèi)的“求和項(xiàng):貸方發(fā)生額”字段，選擇“值字段設(shè)置”命令，打開(kāi)“值字段設(shè)置”對(duì)話框。單擊“值字段設(shè)置”對(duì)話框中的“數(shù)字格式”按鈕，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，在“分類(lèi)”列表框中選擇類(lèi)型為“會(huì)計(jì)專(zhuān)用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號(hào)”設(shè)置為無(wú)，單擊“確定”按鈕，完成字段計(jì)算類(lèi)型和數(shù)字的設(shè)置。3.創(chuàng)建公式任務(wù)操作（6）選中數(shù)據(jù)透視表中任意一個(gè)單元格，選擇“分析”選項(xiàng)卡，執(zhí)行“計(jì)算”組中的“字段、項(xiàng)目和集”下的“計(jì)算字段”命令(如圖1-15所示)，打開(kāi)如圖1-16所示的“插入計(jì)算字段”對(duì)話框。3.創(chuàng)建公式圖1-15“字段、項(xiàng)目和集”下的“計(jì)算字段”命令圖1-16“插入計(jì)算字段”對(duì)話框（7）將“名稱(chēng)”文本框中的“字段1”改為“余額總計(jì)”。（8）將“公式”文本框中的“0”刪除，選擇“字段”列表框中的“借方發(fā)生額”，單擊“插入字段”按鈕，再輸入減號(hào)，然后選擇“字段”列表框中的“貸方發(fā)生額”，單擊“插入字段”按鈕。單擊“確定”按鈕，完成插入計(jì)算字段的操作。任務(wù)操作3.創(chuàng)建公式任務(wù)操作（9）最終結(jié)果如圖1-17所示。3.創(chuàng)建公式圖1-17按月和日分類(lèi)匯總的數(shù)據(jù)透視表任務(wù)二賬頁(yè)式現(xiàn)金日記賬知識(shí)準(zhǔn)備COUNTIF函數(shù)的功能是統(tǒng)計(jì)在指定范圍內(nèi)符合統(tǒng)計(jì)條件的單元格的個(gè)數(shù)。COUNTIF函數(shù)包括Range和Criteria兩個(gè)參數(shù)，完整的格式為COUNTIF(Range,Criteria)。Range:表示參與統(tǒng)計(jì)的單元格區(qū)域。Criteria：表示統(tǒng)計(jì)的條件。1.COUNTIF函數(shù)知識(shí)準(zhǔn)備SUBTOTAL函數(shù)對(duì)選定區(qū)域進(jìn)行分類(lèi)匯總，其完整的格式為SUBTOTAL(Function_num,Ref1,Ref2,…)。Function_num：本示例參數(shù)的值為9，表示分類(lèi)求和。Ref1,Ref2,…：表示分類(lèi)計(jì)算的區(qū)域引用。在本示例中引用的是F6:F11單元格區(qū)域，如果在第6行和第11行之間插入空行，則該公式所引用的單元格區(qū)域會(huì)自動(dòng)發(fā)生變化。2.SUBTOTAL函數(shù)任務(wù)目標(biāo)（1）完成賬頁(yè)式現(xiàn)金日記賬頁(yè)面設(shè)計(jì)；（2）在賬頁(yè)式現(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日購(gòu)買(mǎi)打印機(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）打開(kāi)Excel，將文件保存為“賬頁(yè)式現(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）打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，在“分類(lèi)”列表框中選擇類(lèi)型為“會(huì)計(jì)專(zhuān)用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號(hào)”設(shè)置為無(wú)，單擊“確定”按鈕，完成對(duì)指定列的數(shù)字格式的設(shè)置，如圖1-20所示。1.繪制界面圖1-20“設(shè)置單元格格式”對(duì)話框任務(wù)操作（1）選中I5單元格，在編輯欄內(nèi)輸入公式“=F5”；選中H5單元格，輸入“借”字，完成現(xiàn)金初始余額的計(jì)算。（2）選中B14單元格，在編輯欄內(nèi)輸入“=COUNTIF()”，將光標(biāo)移至括號(hào)內(nèi)，單擊“插入函數(shù)”按鈕，如圖1-21所示。2.創(chuàng)建公式圖1-21單擊“插入函數(shù)”按鈕任務(wù)操作打開(kāi)如圖1-22所示的“函數(shù)參數(shù)”對(duì)話框，在Range參數(shù)后輸入“$E$6:E15”，在Criteria參數(shù)后輸入“本月合計(jì)”，確定本月月份。2.創(chuàng)建公式圖1-22COUNTIF函數(shù)任務(wù)操作【提示】這一步是利用COUNTIF函數(shù)自動(dòng)計(jì)算月份，計(jì)算月份的原理就在于統(tǒng)計(jì)指定的單元格范圍內(nèi)“本月合計(jì)”單元格出現(xiàn)的次數(shù)。假定現(xiàn)金日記賬是從1月份開(kāi)始記賬，那么很顯然“本月合計(jì)”在1月底時(shí)的出現(xiàn)次數(shù)為1，就表示1月份；在2月份底時(shí)的出現(xiàn)次數(shù)為2，就表示2月份。依此類(lèi)推，通過(guò)“本月合計(jì)”出現(xiàn)的次數(shù)就可以知道正在匯總的月份為幾月。本例中使用“$E$6”，用絕對(duì)引用的方式則是表示今后統(tǒng)計(jì)時(shí)，總是將指定的E6單元格作為初始位置開(kāi)始統(tǒng)計(jì)。2.創(chuàng)建公式任務(wù)操作

（3）選中B15單元格，在編輯欄內(nèi)輸入公式“=B14”，確定“本年累計(jì)”行的月份設(shè)置。（4）選中F14單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號(hào)內(nèi)，單擊“插入函數(shù)”按鈕，打開(kāi)如圖1-23所示的“函數(shù)參數(shù)”對(duì)話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“F6:F13”，完成本月借方發(fā)生總額的計(jì)算。2.創(chuàng)建公式圖1-23SUBTOTAL函數(shù)任務(wù)操作【提示】SUBTOTAL函數(shù)的作用是在指定范圍內(nèi)進(jìn)行分類(lèi)求和計(jì)算。雖然是進(jìn)行求和計(jì)算，但是此處不能使用Sum函數(shù)進(jìn)行匯總，原因是Sum函數(shù)計(jì)算時(shí)會(huì)將以前各月的借貸方累計(jì)發(fā)生額都加入計(jì)算范圍內(nèi)，從而對(duì)最終結(jié)果產(chǎn)生干擾，而SUBTOTAL則不會(huì)這樣。SUBTOTAL函數(shù)有一個(gè)特點(diǎn)是所引用的單元格區(qū)域中如果包含其他的SUBTOTAL函數(shù)，那么所有包含SUBTOTAL函數(shù)的單元格不會(huì)被計(jì)算在內(nèi)，所以可以通過(guò)SUBTOTAL函數(shù)避免重復(fù)計(jì)算。2.創(chuàng)建公式任務(wù)操作（5）選中G14單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號(hào)內(nèi)，單擊“插入函數(shù)”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“G6:G13”，完成本月貸方發(fā)生總額的計(jì)算。（6）選中F15單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號(hào)內(nèi)，單擊“插入函數(shù)”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“$F$6:F13”，完成本年借方累計(jì)發(fā)生額的計(jì)算。2.創(chuàng)建公式任務(wù)操作（7）選中G15單元格，在編輯欄內(nèi)輸入“=SUBTOTAL()”，將光標(biāo)移至括號(hào)內(nèi)，單擊“插入函數(shù)”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框，在Function_num參數(shù)后輸入數(shù)值9，在Ref1參數(shù)后輸入單元格區(qū)域“$G$6:G13”，完成本年貸方累計(jì)發(fā)生額的計(jì)算。（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)”，完成余額的計(jì)算。2.創(chuàng)建公式任務(wù)操作（1）選中第11行并右擊，在彈出的快捷菜單中選擇“插入”命令，完成插入行的操作。（2）將數(shù)據(jù)填寫(xiě)到現(xiàn)金日記賬1月份的對(duì)應(yīng)位置，1月份填報(bào)完成后的樣式如圖1-24所示3.持續(xù)記賬設(shè)計(jì)圖1-241月份的現(xiàn)金日記賬任務(wù)操作（3）選中第6～15行，選擇“開(kāi)始”選項(xiàng)卡，執(zhí)行“剪貼板”中的“復(fù)制”命令，完成1月份數(shù)據(jù)區(qū)域的復(fù)制。（4）選中A16單元格并右擊，在彈出的快捷菜單中選擇“粘貼”命令，將1月份的數(shù)據(jù)全部粘貼過(guò)來(lái)。（5）選中第16～23行，按Delete鍵，刪除復(fù)制過(guò)來(lái)的1月份的數(shù)據(jù)。3.持續(xù)記賬設(shè)計(jì)任務(wù)操作（6）將2月份數(shù)據(jù)填寫(xiě)到現(xiàn)金2月份的對(duì)應(yīng)位置，2月份填報(bào)完成后的樣式如圖1-25所示。3.持續(xù)記賬設(shè)計(jì)圖1-25輸入2月份的記錄本項(xiàng)目主要介紹了簡(jiǎn)易現(xiàn)金日記賬和賬頁(yè)式現(xiàn)金日記賬的制作方法。簡(jiǎn)易現(xiàn)金日記賬外觀簡(jiǎn)陋，顯示余額的方式也不直觀，和用戶(hù)平時(shí)使用的現(xiàn)金日記賬的外觀相去甚遠(yuǎn)，不太符合普通用戶(hù)的習(xí)慣。理想的現(xiàn)金日記賬應(yīng)當(dāng)有一個(gè)和用戶(hù)平時(shí)的紙質(zhì)賬頁(yè)類(lèi)似的外觀，而且要能夠?qū)崿F(xiàn)以下功能：（1）輸入的數(shù)據(jù)能夠完整反映業(yè)務(wù)情況。（2）實(shí)時(shí)反映每筆業(yè)務(wù)完成后的現(xiàn)金余額。（3）按月計(jì)算本月的借貸方累計(jì)發(fā)生額、本年的借貸方累計(jì)發(fā)生額以及余額。項(xiàng)目小結(jié)福興公司在2022年1月和2月發(fā)生了如下和現(xiàn)金有關(guān)的業(yè)務(wù)，根據(jù)下列內(nèi)容編制簡(jiǎn)易現(xiàn)金日記賬和賬頁(yè)式現(xiàn)金日記賬。項(xiàng)目練習(xí)2022年1月1日轉(zhuǎn)入上年余額2500元。2022年1月3日支付辦公室電話費(fèi)210元。2022年1月5日支付12月電費(fèi)878元。2022年1月7日?qǐng)?bào)銷(xiāo)經(jīng)理差旅費(fèi)500元。2022年1月6日提現(xiàn)金130000元。2022年1月6日預(yù)付員工差旅費(fèi)3600元。2022年1月6日支付工資120000元。2022年1月14日支付辦公用品費(fèi)用3752元。福興公司在2022年1月和2月發(fā)生了如下和現(xiàn)金有關(guān)的業(yè)務(wù)，根據(jù)下列內(nèi)容編制簡(jiǎn)易現(xiàn)金日記賬和賬頁(yè)式現(xiàn)金日記賬。項(xiàng)目練習(xí)2022年1月15日訂閱報(bào)刊2788元。2022年2月2日提現(xiàn)金5000元。2022年2月2日出售廢舊電腦500元。2022年2月3日支付辦公室電話費(fèi)255元。2022年2月3日支付1月電費(fèi)900元。2022年2月7日提取現(xiàn)金125000元。2022年2月7日支付員工工資130000元。謝謝觀看Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用項(xiàng)目二會(huì)計(jì)憑證（1）了解科目代碼表和現(xiàn)金流量類(lèi)型的設(shè)置方法和用途；（2）了解記賬憑證和后臺(tái)數(shù)據(jù)表之間的聯(lián)系；（3）掌握表對(duì)象的使用方法，了解錄制宏的過(guò)程；（4）學(xué)會(huì)應(yīng)用Excel解決實(shí)際案例中遇到的會(huì)計(jì)憑證問(wèn)題。學(xué)習(xí)目標(biāo)會(huì)計(jì)憑證是會(huì)計(jì)登記入賬的數(shù)據(jù)來(lái)源，從外觀上來(lái)看，會(huì)計(jì)憑證是一種比較復(fù)雜的表格。Excel就是設(shè)計(jì)表格的能手，它兼顧了數(shù)據(jù)庫(kù)設(shè)計(jì)的方法，因此非常適合業(yè)務(wù)復(fù)雜程度不高的單位。用Excel編制會(huì)計(jì)憑證的難點(diǎn)在于要將不規(guī)則表格中的數(shù)據(jù)轉(zhuǎn)換成表對(duì)象中的數(shù)據(jù)，以便日后進(jìn)一步進(jìn)行賬務(wù)處理。情景引入科目代碼表1憑證輸入2目錄CONTENTS任務(wù)一科目代碼表知識(shí)準(zhǔn)備對(duì)于現(xiàn)金流量的類(lèi)型名稱(chēng)會(huì)計(jì)上是有明確規(guī)定的，而現(xiàn)金流量類(lèi)型的代碼則可以由用戶(hù)自定義。本任務(wù)中單獨(dú)設(shè)置一張表來(lái)放置現(xiàn)金流量相關(guān)的項(xiàng)目?，F(xiàn)金流量的具體內(nèi)容包括：(1)銷(xiāo)售商品、提供勞務(wù)收到的現(xiàn)金。(2)收到的稅費(fèi)返還。(3)收到其他與經(jīng)營(yíng)活動(dòng)有關(guān)的現(xiàn)金。(4)購(gòu)買(mǎi)商品、接受勞務(wù)支付的現(xiàn)金。(5)支付給職工以及為職工支付的現(xiàn)金。(6)支付的各項(xiàng)稅費(fèi)。(7)支付其他與經(jīng)營(yíng)活動(dòng)有關(guān)的現(xiàn)金。(8)收回投資收到的現(xiàn)金。知識(shí)準(zhǔn)備(9)取得投資收益收到的現(xiàn)金。(10)處置固定資產(chǎn)、無(wú)形資產(chǎn)和其他長(zhǎng)期資產(chǎn)收回的現(xiàn)金凈額。(11)處置子公司及其他營(yíng)業(yè)單位收到的現(xiàn)金凈額。(12)收到其他與投資活動(dòng)有關(guān)的現(xiàn)金。(13)購(gòu)建固定資產(chǎn)、無(wú)形資產(chǎn)和其他長(zhǎng)期資產(chǎn)支付的現(xiàn)金。(14)投資支付的現(xiàn)金。(15)取得子公司及其他營(yíng)業(yè)單位支付的現(xiàn)金凈額。(16)支付其他與投資活動(dòng)有關(guān)的現(xiàn)金。知識(shí)準(zhǔn)備(17)吸收投資收到的現(xiàn)金。(18)取得借款收到的現(xiàn)金。(19)收到其他與籌資活動(dòng)有關(guān)的現(xiàn)金。(20)償還債務(wù)支付的現(xiàn)金。(21)分配股利、利潤(rùn)或償付利息支付的現(xiàn)金。(22)支付其他與籌資活動(dòng)有關(guān)的現(xiàn)金。科目代碼表和現(xiàn)金流量類(lèi)型是編制記賬憑證的基礎(chǔ)。任務(wù)目標(biāo)(1)設(shè)置科目代碼表，并為各個(gè)科目設(shè)置年初數(shù)；(2)設(shè)置現(xiàn)金流量表的類(lèi)型及代碼。任務(wù)資料福源公司是一家商品流通企業(yè)，公司為一般納稅人。在2021年底，各個(gè)會(huì)計(jì)科目的余額如表2-1所示（表2-1見(jiàn)教材17-18頁(yè)）。要求：(1)根據(jù)表2-1中的內(nèi)容設(shè)置科目代碼表，并為各個(gè)科目設(shè)置年初數(shù)。(2)設(shè)置現(xiàn)金流量表的類(lèi)型及代碼。任務(wù)操作(1)打開(kāi)Excel，將工作表保存為“賬務(wù)處理.xlsm”，如圖2-1所示。選中Sheet1工作表，將Sheet1工作表重命名為“科目代碼表”。1.科目代碼表圖2-1

保存為啟用宏的工作簿格式【提示】因?yàn)樯院髸?huì)涉及“宏”的應(yīng)用，所以文件必須保存為.xlsm格式。任務(wù)操作(2)選中A1單元格，在A1:E1單元格區(qū)域內(nèi)依次輸入科目代碼、科目名稱(chēng)、性質(zhì)、是否明細(xì)和年初數(shù)等字段。(3)選中A列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，選擇“數(shù)字格式”為“數(shù)值”，“小數(shù)位數(shù)”設(shè)置為0，完成A列數(shù)據(jù)格式的設(shè)置。(4)選中E列并右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，選擇“數(shù)字格式”為“會(huì)計(jì)專(zhuān)用”，“貨幣符號(hào)”設(shè)置為無(wú)。1.科目代碼表任務(wù)操作(5)選中A1單元格，選擇“插入”選項(xiàng)卡，執(zhí)行“表格”組內(nèi)的“表格”命令，打開(kāi)如圖2-2所示的“創(chuàng)建表”對(duì)話框，選中“表包含標(biāo)題”復(fù)選框，將指定的單元格區(qū)域轉(zhuǎn)化為表對(duì)象。1.科目代碼表圖2-2“創(chuàng)建表”對(duì)話框任務(wù)操作(6)單擊A1單元格，選擇“表設(shè)計(jì)”選項(xiàng)卡，將“屬性”組內(nèi)的默認(rèn)表名稱(chēng)更改為“kmdm”，完成表對(duì)象名稱(chēng)的設(shè)置，如圖2-3所示。1.科目代碼表圖2-3設(shè)置表對(duì)象名稱(chēng)(7)選中A1單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，在“表樣式”組中選擇“表樣式淺色9”，完成表樣式的選擇。任務(wù)操作(1)從A2單元格開(kāi)始輸入科目代碼“1001”，在B2單元格內(nèi)輸入科目名稱(chēng)“庫(kù)存現(xiàn)金”。(2)在C2單元格內(nèi)輸入數(shù)值“1”，完成現(xiàn)金科目的性質(zhì)設(shè)定。(3)在D2單元格內(nèi)輸入“y”，完成是否明細(xì)科目的設(shè)置。【提示】科目分為明細(xì)科目和非明細(xì)科目，明細(xì)科目用“y”表示，非明細(xì)科目用“n”表示。只有明細(xì)科目才用于會(huì)計(jì)記賬。2.各科目年初數(shù)任務(wù)操作(4)在E2單元格內(nèi)輸入數(shù)值“3630”，完成現(xiàn)金年初數(shù)的設(shè)定。2.各科目年初數(shù)【提示】不論余額產(chǎn)生的方向是借方還是貸方，年初數(shù)都用正數(shù)表示。余額的方向是通過(guò)性質(zhì)字段來(lái)確定的。任務(wù)操作(5)從A3單元格開(kāi)始，繼續(xù)輸入各個(gè)會(huì)計(jì)科目的代碼、名稱(chēng)、性質(zhì)、年初數(shù)和是否明細(xì)等字段內(nèi)容。最終完成的科目代碼表如圖2-4所示。2.各科目年初數(shù)圖2-4科目代碼表任務(wù)操作(6)選中第1列，選擇“公式”選項(xiàng)卡，執(zhí)行“定義的名稱(chēng)”組內(nèi)的“定義名稱(chēng)”命令，打開(kāi)如圖2-5所示的“新建名稱(chēng)”對(duì)話框，在“名稱(chēng)”文本框中輸入“dm”，在“引用位置”文本框中輸入“=kmdm[科目代碼表]”，完成第1列名稱(chēng)的設(shè)置。2.各科目年初數(shù)圖2-5“新建名稱(chēng)”對(duì)話框任務(wù)操作(1)新建一張工作表，將工作表重命名為“參數(shù)”,完成參數(shù)表的設(shè)置。(2)在A1:B1單元格區(qū)域內(nèi)輸入“內(nèi)容”和“類(lèi)型代碼”。(3)選中A1單元格，選擇“插入”選項(xiàng)卡，執(zhí)行“表格”組內(nèi)的“表格”命令，打開(kāi)如圖2-6所示的“創(chuàng)建表”對(duì)話框，單擊“確定”按鈕，創(chuàng)建一個(gè)表對(duì)象。3.現(xiàn)金流量類(lèi)型圖2-6“創(chuàng)建表”對(duì)話框任務(wù)操作3.現(xiàn)金流量類(lèi)型(4)選中A1單元格，選擇“設(shè)計(jì)”選項(xiàng)卡，在“屬性”組內(nèi)將表名稱(chēng)更改為“xjll”，完成表對(duì)象樣式和名稱(chēng)的設(shè)置。(5)從A2單元格開(kāi)始輸入實(shí)驗(yàn)原理中介紹的各種現(xiàn)金流量項(xiàng)目?jī)?nèi)容和代碼，最終結(jié)果如圖2-7所示。任務(wù)操作3.現(xiàn)金流量類(lèi)型圖2-7現(xiàn)金流量類(lèi)型及代碼【提示】現(xiàn)金流量表中的代碼僅僅是為了引用方便，是由用戶(hù)自定義的。和科目表不同，現(xiàn)金流量類(lèi)型的表中需要將內(nèi)容放在第一列，代碼放在第二列。任務(wù)操作3.現(xiàn)金流量類(lèi)型(6)選擇“公式”選項(xiàng)卡，執(zhí)行“定義的名稱(chēng)”組內(nèi)的“定義名稱(chēng)”命令，打開(kāi)如圖2-8所示的“新建名稱(chēng)”對(duì)話框，在“名稱(chēng)”文本框中輸入“現(xiàn)金流量類(lèi)型”，在“引用位置”文本框中輸入“=xjll[內(nèi)容]”，單擊“確定”按鈕，完成新建名稱(chēng)的設(shè)置。圖2-8“新建名稱(chēng)”對(duì)話框任務(wù)二憑證輸入知識(shí)準(zhǔn)備LEFT函數(shù)的作用是返回指定字符串的左數(shù)前幾個(gè)字符。LEFT函數(shù)的返回結(jié)果是文本類(lèi)型的字符串。LEFT函數(shù)首先從C4單元格的左側(cè)找4個(gè)字符，如代碼為“100201”，那么1002就是其總賬科目，代碼長(zhǎng)度為4。1.LEFT函數(shù)知識(shí)準(zhǔn)備VLOOKUP函數(shù)的作用是在指定區(qū)域的第一列中查找第一個(gè)符合要求的值。本任務(wù)要查找的區(qū)域是表對(duì)象kmdm，查找的值是“LEFT(C5,4)+0”。本任務(wù)中，LEFT函數(shù)之所以在返回之后還要加0，是因?yàn)長(zhǎng)EFT函數(shù)返回的是文本類(lèi)型的字符串，而在科目代碼表中，字段“科目代碼”的數(shù)據(jù)格式是“數(shù)值”，二者的數(shù)值類(lèi)型不一樣，因此比較時(shí)自然就不可能相等，變通的方法是為字符串類(lèi)型的數(shù)字加上0，這樣就可以將字符類(lèi)型的數(shù)字強(qiáng)制轉(zhuǎn)換為數(shù)值。2.VLOOKUP函數(shù)知識(shí)準(zhǔn)備3.IFERROR函數(shù)IFERROR函數(shù)的作用是判斷接下來(lái)計(jì)算的值是否正確，如果值是正確的，就用正確的值表示，否則用指定的值顯示。在本任務(wù)中，顯示不正確就用空白表示。任務(wù)目標(biāo)(1)完成宏錄制；(2)完成憑證的輸入。任務(wù)資料在2022年1月，福源公司發(fā)生了如下業(yè)務(wù)：(1)購(gòu)買(mǎi)編號(hào)為JP34的商品33000元，購(gòu)買(mǎi)編號(hào)MT5的商品21000元，購(gòu)買(mǎi)編號(hào)為KK2的商品8000元，增值稅稅率為13%，以工行支付貨款。(2)向海尚公司銷(xiāo)售JP32和JP33兩種商品，收入為175000元，其中工行收到了貨款13475元，其余部分稍后支付。JP32成本為6000元，JP33成本為5000元。(3)工行收到歐麗公司歸還的貨款88000元。(4)歸還一通公司貨款250000元，貨款已經(jīng)通過(guò)工行支付。(5)銷(xiāo)售JP35、MT5和KK3三種商品共計(jì)152800元，工行已經(jīng)收到全部貨款。JP35的成本為61000元，MT5的成本為28000元，KK2的成本為3350元。任務(wù)資料(6)現(xiàn)金支付銷(xiāo)售部門(mén)費(fèi)用98元。(7)工行支付招待費(fèi)5532元。(8)向金鑫公司銷(xiāo)售商品MT4共計(jì)254000元，未收到貨款。MT4商品成本為189100元。(9)銷(xiāo)售部門(mén)購(gòu)買(mǎi)辦公用品2200元，以工行支付。(10)銷(xiāo)售JP34商品157200元，貨款已經(jīng)通過(guò)工行收訖，JP34商品的成本為72500元。(11)以工行支付稅款，上月應(yīng)交增值稅銷(xiāo)項(xiàng)稅71382元，不考慮增值稅外的其他稅收。(12)購(gòu)買(mǎi)商品MT4共計(jì)487000元，JP32共計(jì)45000元，JP34共計(jì)102000元，貨款未支付，其中從合力賒購(gòu)547940元，從興樂(lè)公司賒購(gòu)168480元。任務(wù)資料(13)工行支付前期計(jì)入其他應(yīng)付款的水費(fèi)6022元。(14)購(gòu)買(mǎi)空調(diào)5臺(tái)，共計(jì)35000元，以工行支付。(15)方樂(lè)出差暫領(lǐng)現(xiàn)金3500元。(16)從工行提取175000元支付職工工資，其中銷(xiāo)售部門(mén)95000元，管理部門(mén)80000元，同時(shí)按10%計(jì)提福利費(fèi)用。(17)工行收到新通訊公司歸還的欠款100000元。(18)出售商品KK3和KK4共計(jì)135000元，其中KK3成本為32000元，KK4成本為28000元，貨款已經(jīng)通過(guò)工行收訖。(19)向海尚公司銷(xiāo)售商品JP35共計(jì)182000元，成本為110000元，貨款未收訖。任務(wù)資料(20)向一通公司和興樂(lè)公司賒購(gòu)商品MT5共計(jì)205000元，KK4共計(jì)25000元，其中向一通企業(yè)購(gòu)買(mǎi)商品140000元，向樂(lè)星公司購(gòu)買(mǎi)商品65000元。(21)以工行支付一通企業(yè)商品款150000元。(22)以工行支付網(wǎng)絡(luò)費(fèi)1200元。(23)以工行支付勞保用品費(fèi)用8000元。(1)單擊Excel左上角的“文件”按鈕，選擇“選項(xiàng)”命令，打開(kāi)“Excel選項(xiàng)”對(duì)話框，如圖2-9所示。任務(wù)操作1.憑證界面圖2-9“Excel選項(xiàng)”對(duì)話框(2)在左側(cè)選擇“自定義功能區(qū)”，在右側(cè)選中“自定義功能區(qū)”下“主選項(xiàng)卡”中的“開(kāi)發(fā)工具”復(fù)選框，完成“開(kāi)發(fā)工具”的選擇。通過(guò)上述操作，選項(xiàng)卡中就能顯示“開(kāi)發(fā)工具”選項(xiàng)卡，如圖2-10所示。任務(wù)操作1.憑證界面圖2-10“開(kāi)發(fā)工具”選項(xiàng)卡(3)新建工作表，將工作表重命名為“憑證輸入”。(4)在B1單元格中輸入“記賬憑證”，選中B1:F1單元格區(qū)域，選擇“開(kāi)始”選項(xiàng)卡，執(zhí)行“對(duì)齊方式”組中的“合并后居中”命令，如圖2-11所示。將“字體”設(shè)置為“仿宋_GB2312”，“字號(hào)”大小為“20”，并將B1:F1單元格區(qū)域設(shè)置為雙下畫(huà)線。任務(wù)操作1.憑證界面圖2-11“合并后居中”命令(5)在B2單元格內(nèi)輸入“憑證號(hào)”，在D2單元格內(nèi)輸入“日期”，在G2單元格內(nèi)輸入“附件數(shù):”。(6)選中E2單元格，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”選項(xiàng)卡，設(shè)置為“日期”分類(lèi)中的“2012/3/14”類(lèi)型，完成日期格式的設(shè)置。任務(wù)操作1.憑證界面(7)從A3單元格開(kāi)始，在工作表中輸入如圖2-12所示的內(nèi)容。任務(wù)操作1.憑證界面圖2-12憑證界面(8)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，選擇“控件”組中“插入”下“表單控件”中的“按鈕”控件，如圖2-13所示。在“憑證輸入”工作表的H2單元格偏右位置添加一個(gè)按鈕，直接關(guān)閉“指定宏”對(duì)話框，在按鈕上右擊，在彈出的快捷菜單中選擇“編輯文字”命令，將按鈕的名稱(chēng)改為“添加記錄”，完成按鈕的設(shè)置。任務(wù)操作1.憑證界面圖2-13選擇“表單控件”中的“按鈕”控件(9)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，選擇“控件”組中“插入”下“表單控件”中的“按鈕”控件，在“添加記錄”按鈕后添加一個(gè)命令按鈕，并將按鈕上的標(biāo)題更改為“獲取最新憑單號(hào)”，完成按鈕的設(shè)置。任務(wù)操作1.憑證界面(10)選中A5單元格，在編輯欄內(nèi)輸入公式“=IF(B5<>″″,YEAR($E$2)&″-″&IF(MONTH($E$2)<10,″0″&MONTH($E$2))&″-″&IF(DAY($E$2)<10,″0″&DAY($E$2))&″-″&IF($C$2<10,″00″&$C$2,IF($C$2<100,″0″&$C$2,$C$2))&″-″&IF(ROW()-4<10,″0″&ROW()-4,ROW()-4)&″-″&IF($H$2<10,″0″&$H$2,$H$2),″″)”，完成憑證id的設(shè)計(jì)。A5單元格中的公式比較復(fù)雜，其含義為如果B5單元格中有數(shù)據(jù)，即用戶(hù)開(kāi)始添加摘要信息時(shí)，就給用戶(hù)一個(gè)憑證id，id的內(nèi)容需要根據(jù)下述函數(shù)和公式來(lái)提取。任務(wù)操作1.憑證界面【提示】憑證id相當(dāng)于一張憑證某筆記錄的身份證，具有唯一性，該值是通過(guò)公式根據(jù)用戶(hù)輸入憑證的日期和憑證號(hào)以及筆號(hào)自動(dòng)形成的。憑證id的作用是通過(guò)公式分解該id號(hào)，以獲得憑證日期、憑證號(hào)以及其他相關(guān)信息。憑證id由年-月-日-憑證號(hào)-筆號(hào)-附件數(shù)構(gòu)成，其中年以4位數(shù)表示；月、日、筆號(hào)和附件數(shù)都以2位數(shù)表示，當(dāng)位數(shù)不足2位數(shù)時(shí)用0補(bǔ)充完整；憑證號(hào)用3位數(shù)表示，憑證號(hào)不足3位數(shù)的用0補(bǔ)充完整。當(dāng)憑證中有摘要時(shí)，會(huì)顯示憑證id的內(nèi)容，否則不顯示id的具體內(nèi)容。任務(wù)操作1.憑證界面(11)選中C5:C18單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)工具”組內(nèi)“數(shù)據(jù)驗(yàn)證”下的“數(shù)據(jù)”命令，打開(kāi)如圖2-14所示的“數(shù)據(jù)驗(yàn)證”對(duì)話框。在“設(shè)置”選項(xiàng)卡下，將“驗(yàn)證條件”中的“允許”設(shè)置為“序列”，“來(lái)源”設(shè)置為“=dm”，選擇“出錯(cuò)警告”選項(xiàng)卡，在“標(biāo)題”下輸入“科目代碼錯(cuò)誤”，在“錯(cuò)誤信息”下輸入“輸入的科目代碼不存在”，單擊“確定”按鈕，完成數(shù)據(jù)驗(yàn)證的設(shè)置。任務(wù)操作1.憑證界面任務(wù)操作1.憑證界面圖2-14

“數(shù)據(jù)驗(yàn)證”對(duì)話框(12)選中D5單元格，在編輯欄內(nèi)輸入公式“=IFERROR(VLOOKUP(LEFT(C5,4)+0,kmdm,2,FALSE),″″)”，完成總賬科目的設(shè)置。(13)選中E5單元格，在編輯欄內(nèi)輸入“=IFERROR(IF(LEN(C5)=6,VLOOKUP(C5,kmdm,2,FALSE),″″),″″)”，完成明細(xì)科目的設(shè)置。(14)選中I5單元格，在編輯欄內(nèi)輸入公式“=IF(OR(COUNTBLANK(G5:H5)+COUNTBLANK(B5)=3,COUNTBLANK(G5:H5)+COUNTBLANK(B5)=0),″″,″錯(cuò)誤″)”，完成錯(cuò)誤提示的設(shè)置。任務(wù)操作1.憑證界面(15)選中F5:F18單元格區(qū)域，選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“數(shù)據(jù)工具”組內(nèi)的“數(shù)據(jù)驗(yàn)證”命令，打開(kāi)如圖2-15所示的“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“設(shè)置”選項(xiàng)卡下的“允許”中選擇“序列”，“來(lái)源”設(shè)置為“=現(xiàn)金流量類(lèi)型”，單擊“確定”按鈕，完成數(shù)據(jù)驗(yàn)證的設(shè)置。任務(wù)操作1.憑證界面圖2-15

驗(yàn)證現(xiàn)金流量信息(16)選中G5:G18單元格區(qū)域，選擇“開(kāi)始”選項(xiàng)卡，執(zhí)行“樣式”組內(nèi)“條件格式”下的“新建規(guī)則”命令，打開(kāi)“新建格式規(guī)則”對(duì)話框，在“選擇規(guī)則類(lèi)型”中選擇“使用公式確定要設(shè)置格式的單元格”命令，打開(kāi)如圖2-16所示的“編輯規(guī)則說(shuō)明”，在“為符合此公式的值設(shè)置格式”文本框內(nèi)輸入“=SUM($H$5:$H$18)<>SUM($G$5:$G$18)”，單擊“格式”按鈕，選擇“填充”選項(xiàng)卡，選擇紅色為填充顏色，單擊“確定”按鈕，完成條件格式的設(shè)置。任務(wù)操作1.憑證界面任務(wù)操作1.憑證界面圖2-16

設(shè)置條件格式(17)選中G5:H18單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”標(biāo)簽，將其類(lèi)型設(shè)置為“會(huì)計(jì)專(zhuān)用”，“小數(shù)位數(shù)”設(shè)置為2，“貨幣符號(hào)”設(shè)置為“無(wú)”，完成指定單元格區(qū)域的格式設(shè)置。(18)選中A5單元格，將鼠標(biāo)指針移動(dòng)到A5單元格的右下角，向下拖曳填充公式到A18單元格區(qū)域，完成憑證id的設(shè)置。選中D5:E5單元格區(qū)域，將鼠標(biāo)指針移動(dòng)到單元格區(qū)域的右下角，向下拖曳填充公式至D18:E18單元格區(qū)域，完成總賬科目和明細(xì)科目的設(shè)置。(19)選中I5單元格，將鼠標(biāo)指針移動(dòng)到I5單元格的右下角，向下拖曳填充公式到I18單元格區(qū)域，完成憑證錯(cuò)誤提示的設(shè)置。任務(wù)操作1.憑證界面(1)選中C2單元格，輸入憑證號(hào)，完成憑證號(hào)的設(shè)置。(2)選中E2單元格，輸入“2022-1-3”，完成憑證日期的輸入。(3)選中H2單元格，輸入“1”，表示該筆憑證的附件數(shù)。任務(wù)操作2.輸入數(shù)據(jù)(4)在B5單元格內(nèi)輸入摘要“購(gòu)買(mǎi)商品”，在C5單元格內(nèi)輸入科目代碼“140503”，在G5單元格內(nèi)輸入金額33000，在H5單元格內(nèi)輸入數(shù)值0，完成第1號(hào)憑證的第1筆記錄輸入，第1筆記錄完成后的效果如圖2-17所示。任務(wù)操作2.輸入數(shù)據(jù)圖2-17

輸入第1筆記錄(5)在B6單元格內(nèi)輸入摘要“購(gòu)買(mǎi)商品”，在C6單元格內(nèi)輸入科目代碼“140507”，在G6單元格內(nèi)輸入金額21000，在H6單元格內(nèi)輸入數(shù)值0，完成該憑證的第2筆記錄輸入。(6)在B7單元格內(nèi)輸入摘要“購(gòu)買(mǎi)商品”，在C7單元格內(nèi)輸入科目代碼“140508”，在G7單元格內(nèi)輸入金額8000，在H7單元格內(nèi)輸入數(shù)值0，完成該憑證的第3筆記錄輸入。(7)在B8單元格內(nèi)輸入摘要“購(gòu)買(mǎi)商品”，在C8單元格內(nèi)輸入科目代碼“222101”，在G8單元格內(nèi)輸入金額8060，在H8單元格內(nèi)輸入數(shù)值0，完成該憑證的第4筆記錄輸入。任務(wù)操作2.輸入數(shù)據(jù)(8)在B9單元格內(nèi)輸入摘要“購(gòu)買(mǎi)商品”，在C9單元格內(nèi)輸入科目代碼“100201”，在F9單元格內(nèi)選擇“現(xiàn)金流量類(lèi)型”為“購(gòu)買(mǎi)商品、接受勞務(wù)支付的現(xiàn)金”，在G9單元格內(nèi)輸入0，在H9單元格內(nèi)輸入數(shù)值70060，完成該憑證的第5筆記錄。在憑證界面中完成輸入后的憑證如圖2-18所示。任務(wù)操作2.輸入數(shù)據(jù)圖2-18

輸入完成的第1張憑證(1)新建工作表，將工作表重命名為“憑證庫(kù)”，建立“憑證庫(kù)”工作表。(2)從A1單元格位置開(kāi)始輸入id、憑證id、摘要、科目代碼、總賬科目、明細(xì)科目、現(xiàn)金流量類(lèi)型、借金額、貸金額、類(lèi)型代碼、年、月、日、憑證號(hào)、筆號(hào)、附件數(shù)、性質(zhì)和審核等字段，完成憑證庫(kù)所需字段的輸入。(3)選中A1單元格，選擇“插入”選項(xiàng)卡，執(zhí)行“表格”組內(nèi)的“表格”命令，將指定的單元格區(qū)域轉(zhuǎn)化為組。選擇“設(shè)計(jì)”選項(xiàng)卡，將“屬性”組內(nèi)的表對(duì)象名稱(chēng)更改為“pzk”，完成“憑證庫(kù)”工作表的設(shè)計(jì)。任務(wù)操作3.導(dǎo)入憑證庫(kù)(4)選中B2單元格，在B2單元格內(nèi)輸入字母“a”，完成首行數(shù)據(jù)的輸入。打開(kāi)“憑證輸入”工作表，在憑證界面中輸入第一張憑證，如圖2-19所示。當(dāng)I列沒(méi)有錯(cuò)誤提示，并且借貸方?jīng)]有紅色顯示時(shí)，說(shuō)明該憑證在結(jié)構(gòu)上是正確的，就可以通過(guò)錄制一個(gè)宏將數(shù)據(jù)導(dǎo)入憑證庫(kù)工作表中。任務(wù)操作3.導(dǎo)入憑證庫(kù)圖2-19

輸入完成的第一張憑證【提示】使用Excel導(dǎo)入數(shù)據(jù)的應(yīng)用技巧：如果向一張空表中傳入數(shù)據(jù)，那么為了不發(fā)生錯(cuò)誤，至少要有一行數(shù)據(jù)。首次導(dǎo)入時(shí)，由于表格中沒(méi)有數(shù)據(jù)，因此要為某個(gè)字段添加數(shù)據(jù)，表示該表中現(xiàn)在已經(jīng)存在一行數(shù)據(jù)；如果表對(duì)象中已經(jīng)有數(shù)據(jù)，就無(wú)須這一行，在導(dǎo)入第一張憑證后，該行數(shù)據(jù)將會(huì)被手動(dòng)刪除。任務(wù)操作3.導(dǎo)入憑證庫(kù)輸入內(nèi)容前的憑證庫(kù)如圖2-20所示。任務(wù)操作3.導(dǎo)入憑證庫(kù)圖2-20

輸入內(nèi)容前的憑證庫(kù)(1)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“錄制宏”命令，打開(kāi)如圖2-21所示的“錄制宏”對(duì)話框，將宏名稱(chēng)更改為“添加新記錄”，單擊“確定”按鈕，開(kāi)始錄制宏。任務(wù)操作4.錄制宏圖2-21

“錄制宏”對(duì)話框【提示】在如圖2-21所示的對(duì)話框中通常指定的內(nèi)容包括宏名，如果把錄制的宏看作一部電影，那么宏名就是這部電影的名稱(chēng)，用戶(hù)只需要取一個(gè)比較貼切的名稱(chēng)即可。如果用戶(hù)只是錄制宏，而不準(zhǔn)備在今后的使用中修改代碼，那么用中文取一個(gè)合適的名稱(chēng)是最優(yōu)方案。注意：“快捷鍵”不要和系統(tǒng)中已經(jīng)存在的快捷鍵重合，否則在按下快捷鍵時(shí)會(huì)引起混亂。如果使用按鈕綁定一個(gè)宏，那么快捷鍵并不是必需的。本實(shí)例沒(méi)有使用快捷鍵。“保存在”的位置一般只對(duì)當(dāng)前工作簿有用。任務(wù)操作4.錄制宏(2)選中H5單元格，選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“使用相對(duì)引用”命令，進(jìn)入相對(duì)模式下錄制。(3)選中H5單元格，同時(shí)按Shift+Ctrl+向下方向鍵，再同時(shí)按Shift+Ctrl+向左方向鍵，按3次向左方向鍵，再按Ctrl+C鍵，完成要復(fù)制內(nèi)容的選擇復(fù)制操作。任務(wù)操作4.錄制宏【提示】H5單元格所在的行是第5行，也是憑證記錄的開(kāi)始行。由于每筆憑證的筆數(shù)是不固定的，因此無(wú)法通過(guò)選擇某個(gè)單元格區(qū)域然后復(fù)制到憑證庫(kù)中的方法來(lái)完成。其解決方法是使用Shift+Ctrl+向下方向鍵到達(dá)最后一行包含數(shù)字的行。因?yàn)镠列是貸方金額所在的列，所以只要有記錄，就一定會(huì)有貸方的金額，即使是借方發(fā)生額，貸方的金額也會(huì)是0。同理，Shift+Ctrl+向左方向鍵將會(huì)到達(dá)最后一列包含數(shù)值的列，由于F列的內(nèi)容是現(xiàn)金流量類(lèi)型，而現(xiàn)金流量并不是每筆憑證都必須填，因此按一次Shift+Ctrl+向左方向鍵并不能到達(dá)ID字段，其解決方法是多按幾次該快捷鍵。由于A列是最左側(cè)一列，因此用戶(hù)不論按多少次Shift+Ctrl+向左方向鍵都不能逾越A列的位置。任務(wù)操作4.錄制宏上述操作的結(jié)果實(shí)際上就是選中了單元格記錄中有記錄的區(qū)域，如圖2-22所示。任務(wù)操作4.錄制宏圖2-22

選中的包含數(shù)據(jù)的區(qū)域(4)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，取消選中“代碼”組中的“使用相對(duì)引用”命令，進(jìn)入絕對(duì)引用模式。(5)選擇“憑證庫(kù)”工作表，選中B2單元格，選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“使用相對(duì)引用”命令，同時(shí)按Ctrl+向下方向鍵，再單獨(dú)按一次向下方向鍵，在空白單元格內(nèi)右擊，在彈出的快捷菜單中選擇“選擇性粘貼”命令，打開(kāi)如圖2-23所示的“選擇性粘貼”對(duì)話框，選中“數(shù)值”單選按鈕，單擊“確定”按鈕，完成數(shù)據(jù)的粘貼操作。(6)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，取消選中“代碼”組中的“使用相對(duì)引用”命令，進(jìn)入絕對(duì)引用模式。任務(wù)操作4.錄制宏(7)選中B1單元格，雙擊，退出復(fù)制模式；選中C1單元格，選擇“憑證輸入”工作表，選中B5:C18單元格區(qū)域，按Delete鍵，刪除憑證摘要和科目代碼；選中F5:H18單元格區(qū)域，按Delete鍵，刪除憑證的借方金額和貸方金額，完成已經(jīng)導(dǎo)入憑證的數(shù)據(jù)的刪除。(8)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組中的“停止錄制”命令，完成宏的錄制。(9)在“添加記錄”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開(kāi)如圖2-24所示的“指定宏”對(duì)話框，選擇“添加新記錄”選項(xiàng)，單擊“確定”按鈕，完成指定宏的設(shè)置。任務(wù)操作4.錄制宏任務(wù)操作4.錄制宏圖2-23

“選擇性粘貼”對(duì)話框圖2-24

“指定宏”對(duì)話框通過(guò)上述步驟，在“憑證庫(kù)”工作表中就可以顯示剛才輸入的憑證內(nèi)容，如圖2-25所示。任務(wù)操作4.錄制宏圖2-25

向憑證庫(kù)中導(dǎo)入第一條記錄(1)選擇“憑證輸入”工作表，選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼”組內(nèi)的“錄制宏”命令，打開(kāi)“錄制宏”對(duì)話框，將宏名稱(chēng)更改為“獲取最新憑單號(hào)”，單擊“確定”按鈕，開(kāi)始錄制宏。(2)選中C2單元格，在編輯欄內(nèi)輸入公式“=MAX(IF(pzk[年]&″-″&pzk[月]=YEAR(E2)&″-″&MONTH(E2),pzk[憑證號(hào)]),0)+1”，按Ctrl+Shift+Enter鍵，獲取憑證庫(kù)中指定月份最新的憑單號(hào)。(3)選擇“開(kāi)發(fā)工具”選項(xiàng)卡，執(zhí)行“代碼組”中的“停止錄制”命令，完成宏的錄制。(4)在“獲取最新憑單號(hào)”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開(kāi)“指定宏”對(duì)話框，選擇“獲取最新憑單號(hào)”選項(xiàng)，單擊“確定”按鈕，完成指定宏的設(shè)置。任務(wù)操作5.錄制宏(1)選中“憑證庫(kù)”工作表，選中第2行，右擊，在彈出的快捷菜單中選擇“刪除”命令，刪除第1行記錄。(2)選中A2單元格，在編輯欄內(nèi)輸入“=[@年]&″-″&[@月]&″-″&[@憑證號(hào)]&″-″&[@筆號(hào)]”，按Enter鍵確認(rèn)，完成id字段的設(shè)置。(3)選中J2單元格，在編輯欄內(nèi)輸入公式“=IFERROR(VLOOKUP([@現(xiàn)金流量類(lèi)型],xjll,2,FALSE),″″)”，完成現(xiàn)金流量類(lèi)型代碼的設(shè)置。(4)選中K2單元格，在編輯欄內(nèi)輸入公式“=LEFT([@憑證id],4)+0”，完成“年”字段的設(shè)置。(5)選中L2單元格，在編輯欄內(nèi)輸入公式“=MID([@憑證id],6,2)+0”，完成“月”字段的設(shè)置。任務(wù)操作6.其他字段數(shù)據(jù)(6)選中M2單元格，在編輯欄內(nèi)輸入公式“=MID([@憑證id],9,2)+0”，完成“日”字段的設(shè)置。(7)選中N2單元格，在編輯欄內(nèi)輸入公式“=MID([@憑證id],12,3)+0”，完成“憑證號(hào)”字段的設(shè)置。(8)選中O2單元格，在編輯欄內(nèi)輸入公式“=MID([@憑證id],16,2)+0”，完成“筆號(hào)”字段的設(shè)置。(9)選中P2單元格，在編輯欄內(nèi)輸入公式“=RIGHT([@憑證id],2)+0”，完成“附件數(shù)”字段的設(shè)置。(10)選中Q2單元格，在編輯欄內(nèi)輸入公式“=VLOOKUP([@科目代碼],kmdm,3,FALSE)”，完成會(huì)計(jì)科目性質(zhì)的顯示。(11)選中A2單元格，選擇“視圖”選項(xiàng)卡，執(zhí)行“窗口”組中“凍結(jié)窗口”下的“凍結(jié)拆分窗口”命令，完成窗口凍結(jié)。任務(wù)操作6.其他字段數(shù)據(jù)(1)選中“憑證輸入”工作表，輸入日期和附件數(shù)，單擊“獲取最新憑單號(hào)”按鈕，設(shè)置本張憑單的憑單號(hào)。(2)選中B5單元格，輸入摘要“銷(xiāo)售商品”。在C5單元格內(nèi)輸入科目代碼100201，在F5單元格內(nèi)選擇“銷(xiāo)售商品、提供勞務(wù)收到的現(xiàn)金”，在G5單元格內(nèi)輸入數(shù)值13475，在H5單元格內(nèi)輸入數(shù)值0。(3)選中B6單元格，輸入摘要“銷(xiāo)售商品”。在C6單元格內(nèi)輸入科目代碼112202，在G6單元格內(nèi)輸入數(shù)值63000，在H6單元格內(nèi)輸入數(shù)值0。(4)選中B7單元格，輸入摘要“銷(xiāo)售商品”。在C7單元格內(nèi)輸入科目代碼6402,在G7單元格內(nèi)輸入數(shù)值0，在H7單元格內(nèi)輸入數(shù)值17500。任務(wù)操作7.后續(xù)憑證輸入(5)選中B8單元格，輸入摘要“銷(xiāo)售商品”。在C8單元格內(nèi)輸入科目代碼222102,在G8單元格內(nèi)輸入數(shù)值0，在H8單元格內(nèi)輸入數(shù)值2275。最終生成的憑證如圖2-26所示。任務(wù)操作7.后續(xù)憑證輸入圖2-26

憑證內(nèi)容(6)憑證輸入檢查無(wú)誤后，單擊“添加記錄”按鈕，即可將記錄添加到“憑證庫(kù)”中，完成將憑證內(nèi)容導(dǎo)入“憑證庫(kù)”中。打開(kāi)“憑證庫(kù)”工作表，可以看到輸入的憑證數(shù)據(jù)，如圖2-27所示。任務(wù)操作7.后續(xù)憑證輸入圖2-27

憑證庫(kù)中新增的記錄下面列出了本任務(wù)中涉及的各筆業(yè)務(wù)的會(huì)計(jì)分錄。第1筆業(yè)務(wù)：借：庫(kù)存商品-JP3433000庫(kù)存商品-MT521000庫(kù)存商品-KK28000應(yīng)交稅費(fèi)-應(yīng)交增值稅(進(jìn)項(xiàng))8060貸：銀行存款-工行70060第2筆業(yè)務(wù)：借：銀行存款-工行13475應(yīng)收賬款-海尚公司6300貸：主營(yíng)業(yè)務(wù)收入17500

應(yīng)交稅費(fèi)-應(yīng)交增值稅(銷(xiāo)項(xiàng))2275借：主營(yíng)業(yè)務(wù)成本11000貸：庫(kù)存商品-JP326000庫(kù)存商品-JP335000任務(wù)操作8.會(huì)計(jì)分錄參考第3筆業(yè)務(wù)：借：銀行存款-工行88000貸：應(yīng)收賬款-歐麗公司88000第4筆業(yè)務(wù)：借：應(yīng)付賬款-一通公司250000貸：銀行存款-工行250000任務(wù)操作8.會(huì)計(jì)分錄參考（其余業(yè)務(wù)見(jiàn)教材35~37頁(yè)）第5筆業(yè)務(wù)：借：銀行存款-工行172664貸：主營(yíng)業(yè)務(wù)收入152800應(yīng)交稅費(fèi)-應(yīng)交增值稅(銷(xiāo)項(xiàng))

19864借：主營(yíng)業(yè)務(wù)成本92350貸：庫(kù)存商品-JP3561000庫(kù)存商品-MT528000庫(kù)存商品-KK23350本項(xiàng)目主要介紹了福源公司設(shè)置科目代碼、各科目年初數(shù)和設(shè)置現(xiàn)金流量類(lèi)型，介紹了記賬憑證和后臺(tái)數(shù)據(jù)表之間的聯(lián)系。通過(guò)了解表對(duì)象的使用方法，了解錄制宏的過(guò)程，學(xué)習(xí)者可完成會(huì)計(jì)憑證的制作和使用。項(xiàng)目小結(jié)1.福興公司在2021年年底各個(gè)會(huì)計(jì)科目的余額如表2-2所示（完整表格查看教材38頁(yè)）。項(xiàng)目練習(xí)2.2022年1月，金邦公司發(fā)生的業(yè)務(wù)如下：項(xiàng)目練習(xí)(1)購(gòu)買(mǎi)編號(hào)為P03的商品25740元，購(gòu)買(mǎi)編號(hào)為P02的商品28080元，購(gòu)買(mǎi)編號(hào)為P07的商品10530元，以工行支付貨款。(2)向興華公司銷(xiāo)售P01和P02兩種商品，收入為21060元，其中工行收到了貨款10000元，其余部分稍后支付。P01成本為3900元，P02成本為8100元。(3)工行收到佳佳公司歸還的貨款140000元。(4)歸還勝瑞科技公司貨款300000元，貨款已經(jīng)通過(guò)工行支付。（完整內(nèi)容查看教材39頁(yè)）謝謝觀看Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用項(xiàng)目三會(huì)計(jì)賬簿(1)了解MicrosoftQuery的使用方法；(2)了解數(shù)據(jù)驗(yàn)證的方法；(3)掌握總分類(lèi)賬的結(jié)構(gòu)；(4)掌握科目余額表的結(jié)構(gòu)和MicrosoftQuery提取數(shù)據(jù)的方法；(5)掌握明細(xì)分類(lèi)賬的設(shè)計(jì)方法；(6)學(xué)會(huì)應(yīng)用Excel解決實(shí)際案例中遇到的會(huì)計(jì)賬簿問(wèn)題。學(xué)習(xí)目標(biāo)憑證庫(kù)相當(dāng)于整個(gè)賬務(wù)處理系統(tǒng)的數(shù)據(jù)中心，它為賬簿和報(bào)表提供了所需的數(shù)據(jù)。從Excel應(yīng)用的角度來(lái)看，賬簿和報(bào)表只是從不同的分析角度來(lái)看待憑證庫(kù)數(shù)據(jù)。本項(xiàng)目介紹了如何創(chuàng)建科目匯總表、科目余額表、總分類(lèi)賬、明細(xì)分類(lèi)賬以及導(dǎo)航頁(yè)面的設(shè)計(jì)等內(nèi)容。情景引入目錄CONTENTS科目匯總表1科目余額表2總分類(lèi)賬3明細(xì)分類(lèi)賬4設(shè)計(jì)導(dǎo)航頁(yè)面5任務(wù)一科目匯總表知識(shí)準(zhǔn)備Excel中有一個(gè)MicrosoftQuery組件，該組件的作用相當(dāng)于數(shù)據(jù)庫(kù)的查詢(xún)分析器，能夠通過(guò)該組件從數(shù)據(jù)庫(kù)中提取數(shù)據(jù)并顯示到工作表中。雖然Excel并不是真正意義上的數(shù)據(jù)庫(kù)，但是MicrosoftQuery組件依舊能夠從指定的工作簿(包括當(dāng)前正在使用的工作簿)中提取數(shù)據(jù)。如果把“科目匯總表”工作表當(dāng)作數(shù)據(jù)表看待，就可以解決會(huì)計(jì)科目的來(lái)源問(wèn)題；如果把“憑證庫(kù)”工作表當(dāng)作數(shù)據(jù)表看待，就可以解決金額來(lái)源的問(wèn)題。任務(wù)目標(biāo)(1)制作科目匯總表；(2)完成科目匯總表數(shù)據(jù)。任務(wù)資料2022年1月科目匯總表如表3-1所示?？颇繀R總表包括如下內(nèi)容：(1)日期信息。(2)科目代碼。(3)科目名稱(chēng)。(4)指定月借方發(fā)生額。(5)指定月貸方發(fā)生額。任務(wù)操作(1)在“賬務(wù)處理.xlsm”中新建一張工作表，將其重命名為“科目匯總表”。(2)選中A1:D1單元格區(qū)域，選擇“開(kāi)始”選項(xiàng)卡，執(zhí)行“對(duì)齊方式”組中的“合并后居中”命令，將指定的內(nèi)容合并居中，然后在A1單元格中輸入“=B2&″年″&D2&″月科目匯總表″”，完成表頭標(biāo)題的設(shè)置。(3)從A2單元格開(kāi)始輸入如圖3-1所示的內(nèi)容，完成科目匯總表界面的設(shè)置。1.科目匯總表界面圖3-1

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

1月份科目匯總表任務(wù)操作當(dāng)“匯總”行的“借方發(fā)生額”和“貸方發(fā)生額”兩個(gè)數(shù)據(jù)一致時(shí)，說(shuō)明所有的憑證借貸方金額沒(méi)有發(fā)生輸入錯(cuò)誤，從而達(dá)到試算平衡。當(dāng)要查看其他月份時(shí)，只要修改D2單元格中的值即可。例如，當(dāng)月份修改為2時(shí)，由于2月份并沒(méi)有輸入憑證，因此顯示的結(jié)果如圖3-19所示。2.科目匯總表數(shù)據(jù)圖3-192月份科目匯總表任務(wù)二科目余額表知識(shí)準(zhǔn)備科目余額表是記錄本期所有會(huì)計(jì)科目的發(fā)生額和余額的表格，它反映了某一會(huì)計(jì)期間相關(guān)會(huì)計(jì)科目的期初余額、本期發(fā)生額和期末余額。與科目匯總表類(lèi)似，科目余額表反映的是各個(gè)科目的余額，但是各個(gè)月份的會(huì)計(jì)科目并不是固定的，部分明細(xì)科目會(huì)隨著企業(yè)業(yè)務(wù)的發(fā)展而逐漸加入其中，因此直接使用公式引用科目代碼表并不是一個(gè)很好的選擇。任務(wù)一已經(jīng)介紹了一種導(dǎo)入變動(dòng)數(shù)據(jù)的方法，即利用MicrosoftQuery組件。知識(shí)準(zhǔn)備用戶(hù)通過(guò)MicrosoftQuery組件將“科目代碼表”中的科目導(dǎo)入工作表后，只需要單擊刷新，則不論什么樣的會(huì)計(jì)科目總能出現(xiàn)在工作表中，并且這些數(shù)據(jù)和科目代碼表中的數(shù)據(jù)完全一致。同樣，科目余額表也會(huì)遇到余額的處理問(wèn)題。科目余額表中涉及的科目余額計(jì)算包括期初余額、本期發(fā)生額和期末余額。本任務(wù)中，企業(yè)從年初開(kāi)始建賬，因此期初余額就是年初數(shù)，本期發(fā)生額是指定期限中的累計(jì)借方和累計(jì)貸方的發(fā)生額，期末余額則是根據(jù)期初余額和本期發(fā)生額計(jì)算的結(jié)果。任務(wù)目標(biāo)(1)制作科目余額表；(2)導(dǎo)入科目代碼；(3)完成科目余額。任務(wù)資料科目余額表需要實(shí)現(xiàn)的功能如下：(1)期初各個(gè)會(huì)計(jì)科目的借方和貸方余額。(2)當(dāng)期各會(huì)計(jì)科目的借方和貸方發(fā)生額。(3)計(jì)算本期期末各個(gè)會(huì)計(jì)科目的借方和貸方余額。任務(wù)操作(1)新建工作表，將工作表重命名為“科目余額表”。(2)選中E1:G1單元格區(qū)域，選擇“開(kāi)始”選項(xiàng)卡，執(zhí)行“對(duì)齊方式”組中的“合并后居中”命令，設(shè)置字體為“仿宋_GB2312”，字號(hào)為24，并設(shè)置雙下畫(huà)線，完成字體字號(hào)的設(shè)置。(3)選中E1單元格，在編輯欄內(nèi)輸入公式“=B2&″年″&G2&″月科目余額表″”，完成科目余額表標(biāo)題的設(shè)置。(4)從第2行開(kāi)始輸入如圖3-20所示的科目余額表中的各項(xiàng)。1.科目余額表界面圖3-20科目余額表表頭部分任務(wù)操作(1)選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來(lái)源”中的“來(lái)自MicrosoftQuery”命令，打開(kāi)“選擇數(shù)據(jù)源”對(duì)話框。(2)選擇“數(shù)據(jù)庫(kù)類(lèi)型”為“ExcelFiles*”，單擊“確定”按鈕，打開(kāi)如圖3-21所示的“選擇工作簿”對(duì)話框。2.導(dǎo)入科目代碼圖3-21“選擇工作簿”對(duì)話框任務(wù)操作(3)選擇工作簿所在的文件路徑，單擊“確定”按鈕，打開(kāi)如圖3-22所示的“查詢(xún)向?qū)?選擇列”對(duì)話框，單擊“科目代碼表”前的“+”按鈕，展開(kāi)該表所包含的字段，選中要顯示的列為科目代碼、科目名稱(chēng)、性質(zhì)和年初數(shù)。2.導(dǎo)入科目代碼圖3-22“查詢(xún)向?qū)?選擇列”對(duì)話框任務(wù)操作(4)單擊“下一頁(yè)”按鈕，完成要顯示的列的選擇。單擊“下一頁(yè)”按鈕，打開(kāi)“查詢(xún)向?qū)?篩選數(shù)據(jù)”對(duì)話框，單擊“下一頁(yè)”按鈕，跳過(guò)篩選過(guò)程，打開(kāi)“查詢(xún)向?qū)?排序順序”對(duì)話框，在該步驟中不需要設(shè)置任何內(nèi)容。單擊“下一頁(yè)”按鈕，打開(kāi)“查詢(xún)向?qū)?完成”對(duì)話框，在“請(qǐng)確定下一步的動(dòng)作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢(xún)”單選按鈕，單擊“完成”按鈕，完成查詢(xún)向?qū)У牟僮?，進(jìn)入MicrosoftQuery窗口。2.導(dǎo)入科目代碼任務(wù)操作(5)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開(kāi)如圖3-23所示的“添加條件”對(duì)話框，選擇“字段”為“是否明細(xì)”，“指定值”為“y”，單擊“添加”按鈕，完成條件設(shè)置，單擊“關(guān)閉”按鈕。2.導(dǎo)入科目代碼圖3-23“添加條件”對(duì)話框任務(wù)操作(6)在MicrosoftQuery窗口中選擇“文件”→“將數(shù)據(jù)返回MicrosoftExcel”命令，打開(kāi)如圖3-24所示的“導(dǎo)入數(shù)據(jù)”對(duì)話框，將“數(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è)插入表列”命令，插入一個(gè)新列。使用同樣的操作方法插入其他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)的表名稱(chēng)更改為“kmye”，完成表名稱(chēng)的設(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單