Excel在財(cái)務(wù)會(huì)計(jì)中的應(yīng)用——憑證電子教案項(xiàng)目一Excel在賬務(wù)處理中的應(yīng)用——憑證項(xiàng)目介紹本任務(wù)主要內(nèi)容為建賬、建立會(huì)計(jì)憑證表。項(xiàng)目目標(biāo)1.知識(shí)目標(biāo)了解Excel2010應(yīng)用環(huán)境下的電算化處理流程熟悉Excel2010的基礎(chǔ)理論掌握Excel2010公式的使用方法掌握SUM函數(shù)、IF函數(shù)、SUMIF函數(shù)、VLOOKUP函數(shù)和LEN函數(shù)等的使用方法2.能力目標(biāo)學(xué)會(huì)使用Excel建立會(huì)計(jì)科目表、總賬科目表、末級(jí)科目表學(xué)會(huì)使用Excel錄入期初科目余額表學(xué)會(huì)使用Excel生成會(huì)計(jì)憑證表3.素質(zhì)目標(biāo)培養(yǎng)學(xué)生誠(chéng)信務(wù)實(shí)、品德為先的職業(yè)素養(yǎng)培養(yǎng)學(xué)生分析問題、解決問題的職業(yè)能力培養(yǎng)學(xué)生不斷探索、銳意進(jìn)取的工匠精神培養(yǎng)學(xué)生認(rèn)真負(fù)責(zé)、嚴(yán)謹(jǐn)細(xì)致的工作作風(fēng)項(xiàng)目重難點(diǎn)1.重點(diǎn)建立會(huì)計(jì)科目表、建立會(huì)計(jì)憑證表的操作方法。2.難點(diǎn)會(huì)計(jì)憑證表模板的編制。項(xiàng)目?jī)?nèi)容任務(wù)1-1：建賬任務(wù)1-2：建立會(huì)計(jì)憑證表相關(guān)知識(shí)任務(wù)1建賬任務(wù)1-1創(chuàng)建會(huì)計(jì)科目表在Excel2010中，會(huì)計(jì)科目表的數(shù)據(jù)輸入可以采用兩種方式：一是直接在相應(yīng)的單元格中輸入；另一種是通過“記錄單”命令進(jìn)行錄入。通常采用第一種方式。任務(wù)實(shí)施：操作步驟如下：（1）在D盤建立“東方公司”文件夾，在該文件夾下新建名為“總賬及報(bào)表2201.xlsx”的工作簿。（2）打開“總賬及報(bào)表2201.xlsx”工作簿，雙擊工作表標(biāo)簽Sheet1，重命名為“會(huì)計(jì)科目表”。（3）單擊行號(hào)1，選中1行后右擊，在彈出的快捷菜單中單擊“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對(duì)話框。選中“字體”選項(xiàng)卡，設(shè)置字體為“宋體”，字號(hào)為“12”，字形為“加粗”。（4）單擊列號(hào)A，選中A列，打開“設(shè)置單元格格式”對(duì)話框，選擇“數(shù)字”選項(xiàng)卡下的“文本”。（5）選定A1:C1單元格區(qū)域，打開“設(shè)置單元格格式”對(duì)話框，選中“對(duì)齊”選項(xiàng)卡，選擇“水平對(duì)齊”下拉列表中的“居中”，同樣選擇“垂直對(duì)齊”下拉列表中的“居中”。（6）在A1、B1、C1單元格內(nèi)分別輸入“科目編碼”“總賬科目”“明細(xì)科目”。選擇A1:C1單元格區(qū)域，打開“設(shè)置單元格格式”對(duì)話框，選中“填充”選項(xiàng)卡，設(shè)置背景為黃色。（7）根據(jù)表1-1，輸入東方公司2022年1月初的會(huì)計(jì)科目表。（8）選擇A:C列，將光標(biāo)放到A:C列中的任意兩列間，當(dāng)光標(biāo)變?yōu)樽笥壹^時(shí)，雙擊左右箭頭，調(diào)整A:C列至合適的列寬。（9）選擇A1:C127區(qū)域，單擊功能區(qū)中的“開始”—“字體”—“邊框”下拉菜單下的，設(shè)置網(wǎng)格線。任務(wù)1-2建立總賬科目表在Excel2010中，可通過對(duì)任務(wù)1.1建立的會(huì)計(jì)科目表進(jìn)行篩選得到總賬科目表。任務(wù)實(shí)施：操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，選擇“會(huì)計(jì)科目表”工作表數(shù)據(jù)清單內(nèi)的任一單元格，單擊功能區(qū)中的“數(shù)據(jù)”—“排序和篩選”—“篩選”命令。單擊字段名“科目編碼”右側(cè)的下拉三角按鈕，選擇“文本篩選”下的“自定義篩選”命令。打開“自定義自動(dòng)篩選方式”對(duì)話框。（2）在“自定義自動(dòng)篩選方式”對(duì)話框中，“科目編碼”下的文本框內(nèi)選擇“等于”，右邊文本框內(nèi)輸入文本“????”。單擊確定按鈕，即可篩選出科目編碼長(zhǎng)度是4的總賬科目。（3）選擇“A1:B127”單元格區(qū)域，按組合鍵“Ctrl+C”復(fù)制，打開Sheet2工作表，選擇A1單元格，按組合鍵“Ctrl+V”進(jìn)行粘貼。（4）選擇A:B列，將光標(biāo)放到A:B兩列之間，當(dāng)光標(biāo)變?yōu)樽笥壹^時(shí)雙擊，調(diào)整A、B列至合適的列寬。將Sheet2工作表重命名為“總賬科目表”（5）選擇工作表“會(huì)計(jì)科目表”，單擊功能區(qū)中的“數(shù)據(jù)”—“排序和篩選”—“篩選”命令，取消對(duì)科目編碼的“自動(dòng)篩選”。任務(wù)1-3建立末級(jí)會(huì)計(jì)科目表一、建立末級(jí)會(huì)計(jì)科目表在電算化環(huán)境下，填制記賬憑證需要用到末級(jí)會(huì)計(jì)科目，非末級(jí)會(huì)計(jì)科目不允許有發(fā)生額。因此在Excel電算化下，有必要?jiǎng)h除非末級(jí)科目，建立有效的末級(jí)會(huì)計(jì)科目表。此時(shí)，需財(cái)務(wù)人員判斷會(huì)計(jì)科目表中哪些是末級(jí)會(huì)計(jì)科目，也即是最明細(xì)的會(huì)計(jì)科目。根據(jù)非末級(jí)科目以及末級(jí)會(huì)計(jì)科目的含義，先進(jìn)行人工判斷，看看每個(gè)科目后面是不是有其從屬的子科目，然后逐個(gè)刪除即可。如在會(huì)計(jì)科目表科目編碼為“1002”的科目，由于它有從屬的下一級(jí)子科目——對(duì)應(yīng)科目編碼為“100201”以及“100202”的科目，所以它是非末級(jí)會(huì)計(jì)科目，需要?jiǎng)h除。任務(wù)實(shí)施：操作步驟如下：（1）打開“總賬及報(bào)表.xlsx”的工作簿中，復(fù)制“會(huì)計(jì)科目表！A:C”列到“sheet3！A:C”列。（2）選擇工作表中判斷為非末級(jí)的會(huì)計(jì)科目所在行，調(diào)整鼠標(biāo)至當(dāng)前行的最左方，當(dāng)鼠標(biāo)變作時(shí)，右鍵單擊，在彈出的快捷菜單中，選擇刪除即可。（3）同理，可刪除其他非末級(jí)科目所在行，即得到末級(jí)科目表。最后將工作表sheet3重命名為“末級(jí)科目表”。二、定義名稱Excel允許將單元格或單元格區(qū)域定義為名稱，即用一個(gè)名稱代替單元格或單元格區(qū)域，在需要引用單元格時(shí)，直接使用其名稱即可。使用名稱可以使公式的意義更加明確，也便于理解。在此需定義范圍名稱“末級(jí)科目編碼”和“末級(jí)科目表”。1.定義名稱“末級(jí)科目編碼”操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，選擇“末級(jí)科目表!A2:A107”區(qū)域，單擊功能區(qū)中的“公式”—“定義的名稱”—“名稱管理器”按鈕，打開“名稱管理器”對(duì)話框。（2）單擊“新建”按鈕，打開“新建名稱”對(duì)話框。在“名稱”文本框中輸入單元格區(qū)域的名稱“末級(jí)科目編碼”，在“引用位置”右側(cè)已默認(rèn)為“=末級(jí)會(huì)計(jì)科目表!$A$2:$A$107”。。圖1-27“新建名稱”對(duì)話框（3）單擊“確定”按鈕，返回“名稱管理器”對(duì)話框，在列表中顯示了新建的名稱，最后單擊“關(guān)閉”按鈕即可。2.定義名稱“末級(jí)科目表”定義區(qū)域“末級(jí)科目表!$A$1:$C$107”為名稱“末級(jí)科目表”，操作方法同上。任務(wù)1-5期初余額錄入與財(cái)務(wù)軟件的初次使用相似，在用Excel建立電算化賬套時(shí)，應(yīng)將達(dá)到試算平衡的期初科目余額表錄入計(jì)算機(jī)。若企業(yè)是在年初建賬，則輸入的期初余額就是年初余額。若企業(yè)是在年中建賬，則應(yīng)輸入建賬月份的期初余額和從年初到建賬月初的累計(jì)借貸方發(fā)生額，系統(tǒng)會(huì)自動(dòng)根據(jù)定義好的公式倒算出年初余額。東方公司的Excel賬套是從2019年4月份建立的，屬于年中建賬。為了簡(jiǎn)化工作量作，本賬套沒有涉及各科目1—3月的累計(jì)發(fā)生額。任務(wù)實(shí)施：東方公司的Excel賬套是從2022年1月份建立的，屬于年初建賬。操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，單擊“插入工作表”按鈕，將新建工作表重命名為“期初科目余額表”。（2）復(fù)制“會(huì)計(jì)科目表!A:C”到“期初科目余額表!A:C”。（3）選擇D1、E1單元格，分別輸入“期初借方余額”和“期初貸方余額”。（4）用格式刷設(shè)置D1、E1格式與A1相同。（5）選擇D:E列，右擊，打開設(shè)置單元格格式對(duì)話框，單擊“數(shù)字”選項(xiàng)卡，選擇分類下的“會(huì)計(jì)專用”，小數(shù)位數(shù)2，貨幣符號(hào)“無”。單擊“字體”選項(xiàng)卡，選擇字體為“宋體”，字號(hào)為“12”。（6）選擇D2單元格，單擊功能區(qū)中的“視圖”—“窗口”—“凍結(jié)窗格”按鈕，在彈出的下拉菜單中，選擇“凍結(jié)拆分窗格”命令，如圖1-40所示。即可將窗口從D2單元格左上方的交點(diǎn)處進(jìn)行凍結(jié)，其左邊的列和上邊的行將被鎖定。（7）根據(jù)表1-1錄入東方公司2022年1月期初科目余額。（8）選擇A128:C128單元格，設(shè)置其格式為“跨列居中”。在合并后的單元格內(nèi)輸入文本“合計(jì)”。（9）選擇D128單元格，單擊功能區(qū)“公式”—“函數(shù)庫(kù)”—“插入函數(shù)”按鈕（或直接單擊編輯欄中的“插入函數(shù)”按鈕），打開“插入函數(shù)”窗口，在“選擇類別”右側(cè)的下拉菜單中選擇“數(shù)學(xué)與三角函數(shù)”。（10）選擇“SUMIF”函數(shù)，單擊“確定”按鈕。（11）打開SUMIF函數(shù)的“函數(shù)參數(shù)”窗口。在Range參數(shù)內(nèi)，輸入A:A，在Criteria參數(shù)內(nèi)，輸入"????"。注：這里（"????"）用的是通配符，通配符有星號(hào)（*）和問號(hào)（？）兩種。星號(hào)（*）表示0個(gè)或任意多個(gè)字符，問號(hào)（？）只能表示一個(gè)任意字符。在本例中（"????"）表示長(zhǎng)度為4的文本，即長(zhǎng)度為4的總賬科目編碼。在Sum_range參數(shù)內(nèi)，輸入D:D。最后單擊“確定”按鈕。完成如下公式的輸入：D128=SUMIF(A:A,"????",D:D)計(jì)算科目編碼長(zhǎng)度為4的總賬科目期初借方余額合計(jì)數(shù)。（12）同理輸入公式E128=SUMIF(A:A,"????",E:E)，計(jì)算科目編碼長(zhǎng)度為4的總賬科目期初貸方余額合計(jì)。檢查D128與E128是否相等，即可判斷期初余額錄入是否達(dá)到試算平衡。（13）選擇區(qū)域A1:E128，設(shè)置格式為所有框線。（14）定義區(qū)域“期初科目余額表!A1:E127”為名稱“期初科目余額表”。任務(wù)1-2：建立會(huì)計(jì)憑證表任務(wù)1-2-1建立會(huì)計(jì)憑證表模板各類手工會(huì)計(jì)記賬憑證構(gòu)成雖然各有不同，但都包含下列共同項(xiàng)目：類別編號(hào)、憑證日期、附件、摘要、總賬科目、明細(xì)科目、借方金額、貸方金額、制單人、審核人、記賬人等。在電算會(huì)計(jì)中，為了簡(jiǎn)化輸入會(huì)計(jì)科目的工作量，錄入記賬憑證時(shí)一般只輸入相關(guān)的科目編碼，系統(tǒng)自動(dòng)切換出對(duì)應(yīng)的總賬科目和明細(xì)科目。結(jié)合手工與電算會(huì)計(jì)的特點(diǎn)，李悅設(shè)計(jì)基于Excel的會(huì)計(jì)憑證表模板包含的項(xiàng)目如下圖，即以手工記賬憑證共同項(xiàng)目為基礎(chǔ)，增設(shè)“科目編碼”一列。任務(wù)實(shí)施：一、定義憑證模板格式操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，單擊“插入工作表”按鈕，將新建工作表重命名為“憑證模板”。（2）選擇“A1:L1”單元格，分別輸入“類別編號(hào)”“憑證日期”“附件”“摘要”“科目編碼”“總賬科目”“明細(xì)科目”“借方金額”“貸方金額”“制單人”“審核人”“記賬人”，設(shè)置其字體為宋體、12號(hào)、加粗。選中“對(duì)齊”選項(xiàng)卡，選擇“水平對(duì)齊”下拉列表中的“居中”，同樣選擇“垂直對(duì)齊”下拉列表中的“居中”。（3）選擇“A1:L1”單元格，單擊功能區(qū)中的“開始”—“字體”—“填充顏色”下拉按鈕下的黃色按鈕，設(shè)置單元格的底色。（4）選擇“A1:L3”單元格，單擊功能區(qū)中的“開始”—“字體”—“邊框”下拉按鈕下的所有框線按鈕，設(shè)置“A1:L3”單元格的邊框。二、定義單元格的有效性操作步驟如下：1）設(shè)置“憑證日期”的有效性。選擇B2單元格，單擊功能區(qū)中的“數(shù)據(jù)”—“數(shù)據(jù)工具”—“數(shù)據(jù)有效性”按鈕，打開“數(shù)據(jù)有效性”對(duì)話框。在“設(shè)置”選項(xiàng)卡的“允許”下拉菜單中選擇“日期”，“數(shù)據(jù)”下拉菜單中選擇“介于”，開始日期輸入“2022-1-1”，結(jié)束日期輸入“2022-1-31”。選擇“輸入信息”選項(xiàng)卡，選中“選定單元格時(shí)顯示輸入信息”復(fù)選框，在“輸入信息”輸入框內(nèi)輸入“請(qǐng)輸入日期，格式為YYYY-DD-MM!”。最后單擊“確定”按鈕。（2）設(shè)置“附件”的有效性。選擇C2單元格，打開“數(shù)據(jù)有效性”對(duì)話框。在“設(shè)置”選項(xiàng)卡的“允許”下拉菜單中選擇“整數(shù)”，“數(shù)據(jù)”下拉菜單中選擇“介于”，最小值輸入“0”，最大值輸入“100”。選擇“輸入信息”選項(xiàng)卡，選中“選定單元格時(shí)顯示輸入信息”復(fù)選框，“輸入信息”輸入框內(nèi)輸入“請(qǐng)輸入0到100的整數(shù)!”。最后單擊“確定”按鈕。（3）設(shè)置“摘要”的有效性。選擇D2單元格，打開“數(shù)據(jù)有效性”對(duì)話框。在“設(shè)置”選項(xiàng)卡的“允許”下拉菜單中選擇“文本長(zhǎng)度”，“數(shù)據(jù)”下拉菜單中選擇“介于”，最小值輸入“1”，最大值輸入“50”。選擇“輸入信息”選項(xiàng)卡，選中“選定單元格時(shí)顯示輸入信息”復(fù)選框，“輸入信息”輸入框內(nèi)輸入“請(qǐng)輸入1到50個(gè)字!”。最后單擊“確定”按鈕。（4）設(shè)置“科目編碼”的有效性。首先設(shè)置E列為文本，然后選擇E2單元格，打開“數(shù)據(jù)有效性”對(duì)話框。在“設(shè)置”選項(xiàng)卡的“允許”下拉菜單中選擇“序列”，單擊“來源”輸入框中輸入“=末級(jí)科目編碼”。選擇“忽略空值”和“提供下拉菜單”復(fù)選框，如。選擇“輸入信息”選項(xiàng)卡，選中“選定單元格時(shí)顯示輸入信息”復(fù)選框，“輸入信息”輸入框內(nèi)輸入“請(qǐng)輸入末級(jí)科目編碼!。單擊“確定”按鈕。三、定義自動(dòng)顯示會(huì)計(jì)科目公式操作步驟如下：1.定義總賬科目自動(dòng)顯示公式方法一在總賬科目所在的F2單元格輸入公式。F2=VLOOKUP(E2,末級(jí)科目表,2,0)公式含義：在范圍名稱“末級(jí)科目表”的首列查找“E2”單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“末級(jí)科目表”第2列交叉點(diǎn)的值，即該科目編碼對(duì)應(yīng)的總賬科目。這時(shí)在單元格F2內(nèi)會(huì)出現(xiàn)“#N/A”錯(cuò)誤。怎樣進(jìn)行改進(jìn)呢？這就用到了方法二。方法二在總賬科目所在的F2單元格輸入公式。F2=IF(E2="","",VLOOKUP(E2,末級(jí)科目表,2,0))公式含義：首先判斷E2單元格內(nèi)是不是空文本，如果是，則返回空文本，否則，在范圍名稱“末級(jí)科目表”查找“E2”單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“末級(jí)科目表”的第2列交叉點(diǎn)的值，即科目編碼對(duì)應(yīng)的總賬科目。公式經(jīng)過調(diào)整，不再出現(xiàn)“#N/A”錯(cuò)誤。2.定義明細(xì)科目自動(dòng)顯示公式在明細(xì)科目所在的G2單元格輸入公式。G2=IF(LEN(E2)<=4,"",VLOOKUP(E2,末級(jí)科目表,3,0))公式含義：首先判斷E2單元格的長(zhǎng)度是不是小于等于4，如果是，則沒有明細(xì)科目，返回空文本“""”，否則在范圍名稱“末級(jí)科目表”的首列查找“E2”單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“末級(jí)科目表”第3列交叉點(diǎn)的值，即科目編碼對(duì)應(yīng)的明細(xì)科目。四、設(shè)置“借方金額”和“貸方金額”的格式選擇H:J列，單擊鼠標(biāo)右鍵，選擇“設(shè)置單元格格式”命令，打開“數(shù)字”選項(xiàng)卡，選擇“分類”列表框中的“會(huì)計(jì)專用”，小數(shù)位數(shù)為“2”，貨幣符號(hào)為“無”。最后，選擇A2:L2單元格，光標(biāo)放到選擇區(qū)域的右下角變?yōu)閷?shí)心“十”字，拖拉復(fù)制到下一行。五、設(shè)置借貸不平衡自動(dòng)提示“借貸記賬法”的記賬規(guī)則是“有借必有貸，借貸必相等”，在Excel中，可以利用這個(gè)