《Excel在財(cái)務(wù)會(huì)計(jì)中的應(yīng)用》（第3版） 教案 項(xiàng)目二 賬簿教案

Excel在財(cái)務(wù)會(huì)計(jì)中的應(yīng)用——賬簿電子教案項(xiàng)目二Excel在賬務(wù)處理中的應(yīng)用——賬簿項(xiàng)目介紹本任務(wù)主要內(nèi)容為建立日記賬、生成科目匯總表、編制科目余額表。項(xiàng)目目標(biāo)1.知識(shí)目標(biāo)熟悉手工會(huì)計(jì)賬簿的種類及記賬方法掌握Excel2010數(shù)據(jù)篩選的種類及方法掌握Excel2010數(shù)據(jù)透視表熟練掌握Excel2010中公式的使用方法熟練掌握Excel2010中IF、VLOOKUP、IFERROR、ABS等函數(shù)的使用方法2.能力目標(biāo)學(xué)會(huì)使用Excel建立庫存現(xiàn)金日記賬、銀行存款日記賬學(xué)會(huì)使用Excel建立總賬科目匯總表學(xué)會(huì)使用Excel建立明細(xì)科目匯總表學(xué)會(huì)使用Excel建立總賬科目余額表學(xué)會(huì)使用Excel建立明細(xì)科目余額表3.素質(zhì)目標(biāo)培養(yǎng)學(xué)生嚴(yán)謹(jǐn)細(xì)致、一絲不茍的工作作風(fēng)培養(yǎng)學(xué)生誠實(shí)守信、不做假賬的職業(yè)精神培養(yǎng)學(xué)生分析問題、解決問題的動(dòng)手能力培養(yǎng)學(xué)生勤于思考、永攀高峰的進(jìn)取精神項(xiàng)目重難點(diǎn)1.重點(diǎn)掌握建立總賬科目匯總表、總賬科目余額表、明細(xì)科目余額表的操作方法。2.難點(diǎn)銀行余額調(diào)節(jié)表的編制。項(xiàng)目內(nèi)容任務(wù)2-1建立日記賬任務(wù)2-2生成科目匯總表任務(wù)2-3編制科目余額表相關(guān)知識(shí)任務(wù)2-1建立日記賬任務(wù)2-1-1創(chuàng)建會(huì)計(jì)科目表為了加強(qiáng)貨幣資金的管理，企業(yè)都設(shè)置庫存現(xiàn)金日記賬和銀行存款日記賬，并由出納人員負(fù)責(zé)登記和保管。在本任務(wù)中，只要保證會(huì)計(jì)憑證表的正確性和完整性，即可通過Excel的“自動(dòng)篩選”功能輕松地篩選出庫存現(xiàn)金日記賬和銀行存款日記賬。任務(wù)2-1-1生成庫存現(xiàn)金日記賬任務(wù)實(shí)施：操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，單擊“插入工作表”按鈕，增加一新工作表。雙擊新建工作表標(biāo)簽，將工作表重命名為“庫存現(xiàn)金日記賬”。（2）打開“會(huì)計(jì)憑證表”工作表，選擇數(shù)據(jù)清單內(nèi)的任一單元格。（3）單擊功能區(qū)中的“數(shù)據(jù)”—“排序和篩選”—“篩選”命令，在工作表的每一個(gè)表頭字段的右側(cè)出現(xiàn)一個(gè)下拉箭頭，即打開“自動(dòng)篩選”窗口。（4）單擊“科目編碼”列右側(cè)下拉三角按鈕，打開下拉菜單，取消“全選”復(fù)選框，再只選擇“1001”復(fù)選框，單擊“確定”按鈕。（5）選擇篩選后的“庫存現(xiàn)金日記賬”1:37行，單擊右鍵，選擇“復(fù)制”命令。（6）選擇“庫存現(xiàn)金日記賬”工作表A1單元格，單擊右鍵，選擇執(zhí)行“粘貼”命令。刪除J：L列，適當(dāng)調(diào)整列寬，即可篩選出“庫存現(xiàn)金日記賬”。任務(wù)2-1-2生成銀行存款日記賬任務(wù)實(shí)施：操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，單擊“插入工作表”按鈕，新建名為“銀行存款日記賬—工行”的新工作表；再單擊“插入工作表”按鈕，新建名為“銀行存款日記賬—建行”的新工作表。（2）切換到“會(huì)計(jì)憑證表”工作表，單擊“科目編碼”列右側(cè)下拉三角按鈕，取消“全選”復(fù)選框，再只選擇“100201”復(fù)選框，單擊“確定”按鈕。（3）將篩選出的工作表復(fù)制到“銀行存款日記賬—工行”內(nèi)，刪除J:N列，適當(dāng)調(diào)整列寬，即可篩選出“銀行存款日記賬—工行”。（4）同理，篩選出“銀行存款日記賬—建行”。（5）切換到“會(huì)計(jì)憑證表”工作表，再單擊一次功能區(qū)中的“數(shù)據(jù)”—“排序和篩選”—“篩選”命令，取消“自動(dòng)篩選”窗口。任務(wù)2-2生成科目匯總科目匯總表亦稱“記賬憑證匯總表”，它是定期對(duì)全部記賬憑證進(jìn)行匯總，按各個(gè)賬戶列示其借方發(fā)生額和貸方發(fā)生額的一種匯總憑證。手工編制科目匯總表的工作強(qiáng)度大，既費(fèi)時(shí)費(fèi)力又容易出錯(cuò)，特別是對(duì)業(yè)務(wù)量較多的企業(yè)。而在Excel中，可以運(yùn)用數(shù)據(jù)透視表功能輕松、快捷、準(zhǔn)確地生成科目匯總表。任務(wù)2-2-1生成末級(jí)科目匯總表任務(wù)實(shí)施：操作步驟如下：（1）選擇“會(huì)計(jì)憑證表”工作表數(shù)據(jù)清單內(nèi)任一單元格，單擊功能區(qū)中的“插入”—“表格”—“數(shù)據(jù)透視表”右側(cè)的下拉菜單，選擇“數(shù)據(jù)透視表”命令。（2）彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，設(shè)置選擇區(qū)域?yàn)椤皶?huì)計(jì)憑證表!$A$1:$L$66”，放置位置為“新工作表”，單擊“確定”按鈕。（3）Excel2010自動(dòng)在新建的工作表上建立了一張空的數(shù)據(jù)透視表。（4）單擊功能區(qū)中的“數(shù)據(jù)透視表工具”—“設(shè)計(jì)”—“數(shù)據(jù)透視表樣式”—“數(shù)據(jù)透視表樣式淺色16”圖標(biāo)。（5）在“數(shù)據(jù)透視表字段列表”中，選擇字段“科目編碼”（或?qū)⒆侄巍翱颇烤幋a”拖動(dòng)到“行標(biāo)簽”區(qū)域中）。（6）在“數(shù)據(jù)透視表字段列表”中，選擇字段“總賬科目”（或?qū)⒆侄巍翱傎~科目”拖動(dòng)到“行標(biāo)簽”區(qū)域中）8。（7）在“數(shù)據(jù)透視表字段列表”中，選擇字段“明細(xì)科目”（或?qū)⒆侄巍懊骷?xì)科目”拖動(dòng)到“行標(biāo)簽”區(qū)域中）。（8）在“數(shù)據(jù)透視表字段列表”中，將字段“借方金額”拖動(dòng)到“數(shù)值”區(qū)域中。（9）在“數(shù)據(jù)透視表字段列表”中，將字段“貸方金額”拖動(dòng)到“數(shù)值”區(qū)域中。（10）單擊“∑數(shù)值”區(qū)域下的“計(jì)數(shù)項(xiàng):借方金額”，在彈出的快捷菜單中選擇“值字段設(shè)置（N）...”，打開“值字段設(shè)置”對(duì)話框。（11）將“匯總方式”選項(xiàng)卡下的計(jì)算類型由“計(jì)數(shù)”改為“求和”。單擊左下角的“數(shù)字格式”按鈕，打開“設(shè)置單元格格式”對(duì)話框。在“分類”下的文本框內(nèi)選擇“會(huì)計(jì)專用”，右側(cè)小數(shù)位數(shù)為“2”，貨幣符號(hào)為“無”。。最后單擊兩次“確定”按鈕退出。（12）同理，在“數(shù)據(jù)透視表字段列表”中，對(duì)“數(shù)值”下的“計(jì)數(shù)項(xiàng):貸方金額”進(jìn)行相應(yīng)格式的設(shè)置。（13）單擊數(shù)據(jù)透視表內(nèi)任一單元格，單擊功能區(qū)中的“數(shù)據(jù)透視表工具”—“設(shè)計(jì)”—“布局”—“報(bào)表布局”命令，在下拉菜單中選擇“以表格形式顯示”。（14）單擊數(shù)據(jù)透視表內(nèi)任一單元格，單擊功能區(qū)中的“數(shù)據(jù)透視表工具”—“設(shè)計(jì)”—“布局”—“分類匯總”命令，在下拉菜單中選擇“不顯示分類匯總”。（15）單擊數(shù)據(jù)透視表內(nèi)任一單元格，單擊功能區(qū)中的“數(shù)據(jù)透視表工具”—“選項(xiàng)”—“顯示/隱藏”—“+/-按鈕”命令，去掉分級(jí)顯示。（16）將此工作表重命名為“末級(jí)科目匯總表”，并移到“銀行存款日記賬—建行”工作表后面。（17）定義“末級(jí)科目匯總表!A4:E40”區(qū)域?yàn)榉秶Q“末級(jí)科目匯總表”。任務(wù)2-2-2生成總賬科目匯總表在手工會(huì)計(jì)核算中，企業(yè)一般根據(jù)總賬科目匯總表登記總賬。在Excel2010中，生成總賬科目匯總表的方法與末級(jí)科目匯總表類似，也是以會(huì)計(jì)憑證表為數(shù)據(jù)源，根據(jù)數(shù)據(jù)透視表自動(dòng)生成。任務(wù)實(shí)施：一、添加按“科目編碼”對(duì)“總賬科目”進(jìn)行排序的新序列操作步驟如下：（1）單擊“文件”菜單下的“選項(xiàng)”命令，打開“Excel選項(xiàng)”對(duì)話框，如圖2—37所示。在“高級(jí)”選項(xiàng)卡下，選擇“編輯自定義列表（O）...”命令。打開“自定義序列”對(duì)話框。（2）單擊“導(dǎo)入”按鈕左側(cè)的文本框，選擇輸入“總賬科目表！$B$2:$B$52”，單擊“導(dǎo)入”按鈕，單擊“確定”按鈕退出。二、重新生成數(shù)據(jù)透視表操作步驟如下：（1）選擇“會(huì)計(jì)憑證表”工作表數(shù)據(jù)清單內(nèi)任一單元格，單擊功能區(qū)中的“插入”—“表格”—“數(shù)據(jù)透視表”右側(cè)的下拉菜單，選擇“數(shù)據(jù)透視表”命令。（2）彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框。設(shè)置選擇區(qū)域?yàn)椤皶?huì)計(jì)憑證表!$A$1:$I$66”，放置位置為“新工作表”，單擊“確定”按鈕。（3）Excel2010自動(dòng)在新建的工作表上建立了一張空的數(shù)據(jù)透視表。（4）在“數(shù)據(jù)透視表字段列表”中，選擇字段“總賬科目”（或?qū)⒆侄巍翱傎~科目”拖動(dòng)到“行標(biāo)簽”區(qū)域中）。（5）在“數(shù)據(jù)透視表字段列表”中，將字段“借方金額”和“貸方金額”拖動(dòng)到“數(shù)值”區(qū)域中。（6）在“數(shù)據(jù)透視表字段列表”中，設(shè)置“數(shù)值”下的“計(jì)數(shù)項(xiàng):借方金額”的“匯總方式”為“求和”，“數(shù)字格式”為“會(huì)計(jì)專用”，右側(cè)小數(shù)位數(shù)為“2”，貨幣符號(hào)為“無”。（7）同理，對(duì)“數(shù)值”下的“計(jì)數(shù)項(xiàng):貸方金額”進(jìn)行相應(yīng)的格式設(shè)置。（8）單擊數(shù)據(jù)透視表內(nèi)任一單元格，單擊功能區(qū)中的“數(shù)據(jù)透視表工具”—“設(shè)計(jì)”—“布局”—“報(bào)表布局”命令，在下拉菜單中選擇“以表格形式顯示”。（9）最后得到正確的總賬科目匯總表。（10）將當(dāng)前工作表定義為“總賬科目匯總表”，移動(dòng)到“末級(jí)科目匯總表”之后。（11）定義“總賬科目匯總表!A3:C25”區(qū)域?yàn)榉秶Q“總賬科目匯總表”。任務(wù)2-3：編制科目余額表在會(huì)計(jì)實(shí)務(wù)中，資產(chǎn)負(fù)債表的取數(shù)不但需要總賬科目余額，還涉及相關(guān)明細(xì)科目余額。因此，要編制正確的資產(chǎn)負(fù)債表，不僅需要編制“總賬科目余額表”，還要編制“末級(jí)科目余額表”。任務(wù)2-3-1編制末級(jí)科目余額表“末級(jí)科目余額表”是在“期初科目余額表”和“末級(jí)科目匯總表”的基礎(chǔ)上編制的，其內(nèi)容包括期初借方余額、期初貸方余額、本期借方發(fā)生額、本期貸方發(fā)生額、期末借方余額、期末貸方余額六部分。任務(wù)實(shí)施：一、定義格式操作步驟如下：（1）打開“總賬及報(bào)表2201.xlsx”工作簿，單擊“插入工作表”按鈕，增加名為“末級(jí)科目余額表”的新工作表。（2）復(fù)制“末級(jí)科目表!A1:C107”到“末級(jí)科目余額表!A1:C107”。（3）打開“末級(jí)科目余額表”工作表，分別在D1:I1中輸入“期初借方余額”、“期初貸方余額”、“本期借方發(fā)生額”、“本期貸方發(fā)生額”、“期末借方余額”、“期末貸方余額”。用格式刷把D1:I1刷成和A1相同的格式。（4）選擇D:I列，單元格格式設(shè)置“數(shù)字”選項(xiàng)卡下的“會(huì)計(jì)專用”，小數(shù)位數(shù)為“2”，貨幣符號(hào)為“無”；設(shè)置“字體”選項(xiàng)卡下的“宋體”，字號(hào)為“12”。（5）選擇A1:I107，設(shè)置邊框?yàn)樗锌蚓€。二、定義期初余額公式操作步驟如下：1.定義“期初借方余額”的公式選擇D2單元格，下列公式：D2=VLOOKUP（A2,期初科目余額表,4,0）公式含義：在范圍名稱“期初科目余額表”的首列查找A2單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“期初科目余額表”第4列交叉點(diǎn)的值，即查找到末級(jí)科目編碼對(duì)應(yīng)的“期初借方余額”。2.定義“期初貸方余額”的公式選擇E2單元格，同理設(shè)置公式：E2=VLOOKUP（A2,期初科目余額表,5,0）最后，用填充柄把D2:E2的公式向下復(fù)制到D107:E107。三、定義本期發(fā)生額公式操作步驟如下：方法一：1.定義“本期借方發(fā)生額”的公式選擇F2單元格，輸入下列公式：F2=VLOOKUP（A2,末級(jí)科目匯總表,4,0）公式含義：在范圍名稱“末級(jí)科目匯總表”的首列查找A2單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“末級(jí)科目匯總表”第4列交叉點(diǎn)的值，即查找到末級(jí)科目編碼對(duì)應(yīng)的“本期借方發(fā)生額”。2.定義“本期貸方發(fā)生額”的公式選擇G2單元格，同理輸入下列公式：G2=VLOOKUP（A2,末級(jí)科目匯總表,5,0）此公式的含義為：在范圍名稱“末級(jí)科目匯總表”的首列查找A2單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“末級(jí)科目匯總表”第5列交叉點(diǎn)的值，即查找到末級(jí)科目編碼對(duì)應(yīng)的“本期貸方發(fā)生額”。用填充柄把F2:G2的公式向下復(fù)制到F107:G107。這時(shí)，會(huì)出現(xiàn)“#N/A”錯(cuò)誤。原因分析：當(dāng)有的末級(jí)科目本期沒有發(fā)生額時(shí)，即找不到相關(guān)的值，也即發(fā)生“#N/A”錯(cuò)誤。如何改進(jìn)呢?方法二：1.定義“本期借方發(fā)生額”的公式（1）選擇F2單元格，輸入下列公式：F2=IFERROR(VLOOKUP(A2,末級(jí)科目匯總表,4,0),0)公式含義：首先判斷“VLOOKUP（A2,末級(jí)科目匯總表,4,0）”是否出現(xiàn)“#N/A”錯(cuò)誤，如果是，則說明該科目本期沒有借方發(fā)生額，即定義其值為“0”，否則，取VLOOKUP（A2,末級(jí)科目匯總表,4,0）的值。公式經(jīng)過調(diào)整，不會(huì)再出現(xiàn)“#N/A”錯(cuò)誤。2.定義“本期貸方發(fā)生額”的公式與定義“本期借方發(fā)生額”的公式的操作方法類似，最終定義“本期貸方發(fā)生額”的公式為：G2=IFERROR(VLOOKUP(A2,末級(jí)科目匯總表,5,0),0)公式含義：首先判斷“VLOOKUP（A2,末級(jí)科目匯總表,5,0）”是否出現(xiàn)“#N/A”錯(cuò)誤，如果是，則說明該科目本期沒有貸方發(fā)生額，即定義其值為“0”，否則，取VLOOKUP（A2,末級(jí)科目匯總表,5,0）的值。最后，用填充柄把F2:G2的公式向下復(fù)制到F107:G107。四、定義期末余額公式操作步驟如下：1.定義“期末借方余額”的公式選擇H2單元格，輸入下列公式：H2=IF（D2-E2+F2-G2>0,D2-E2+F2-G2,0)公式含義：首先判斷“D2-E2+F2-G2”的值是否大于“0”，如果是，說明是借方余額，則取“D2-E2+F2-G2”的值，否則，取值為“0”。2.定義“期末貸方余額”的公式選擇I2單元格，定義“期末貸方余額”公式：I2=IF(D2-E2+F2-G2<0,ABS(D2-E2+F2-G2),0)公式含義：首先判斷“D2-E2+F2-G2”的值是否小于“0”，如果是，說明是貸方余額，則取“D2-E2+F2-G2”的絕對(duì)值，否則，取值為“0”。最后，用填充柄把H2:I2的公式向下復(fù)制到H107:I107。五、試算平衡檢查要達(dá)到末級(jí)科目余額表的試算平衡，需要滿足下列要求：（1）全部末級(jí)科目的期初借方余額合計(jì)等于期初貸方余額合計(jì)，即D108=E108；（2）全部末級(jí)科目的本期借方發(fā)生額合計(jì)等于本期貸方發(fā)生額合計(jì)，即F108=G108；（3）全部末級(jí)科目的期末借方余額合計(jì)等于期末貸方余額合計(jì)，即H108=I108。操作步驟如下：（1）選擇A108:C108單元格，設(shè)置其格式為跨列居中。在合并后的單元格內(nèi)輸入文本“合計(jì)”。（2）選擇D108單元格，單擊功能區(qū)中的“開始”—“編輯”下的求和按鈕，設(shè)置公式為“=SUM(D2:D107)”，向右復(fù)制到I108。（3）最后，設(shè)置邊框?yàn)樗锌蚓€。任務(wù)2-3-2編制總賬科目余額表“總賬科目余額表”是資產(chǎn)負(fù)債表和利潤表重要的取數(shù)來源，它是在“期初科目余額表”和“總賬科目匯總表”的基礎(chǔ)上編制的。與“末級(jí)科目余額表”類似，其內(nèi)容也包括期初借方余額、期初貸方余額、本期借方發(fā)生額、本期貸方發(fā)生額、期末借方余額、期末貸方余額六部分。任務(wù)實(shí)施：一、定義格式操作步驟如下：（1)單擊“末級(jí)科目余額表”后面的“插入工作表”按鈕增加一新工作表，雙擊新建的工作表標(biāo)簽，將工作表重命名為“總賬科目余額表”。（2）復(fù)制區(qū)域“總賬科目表!A:B”到區(qū)域“總賬科目余額表!A:B”。（3)分別在C1:H1中輸入“期初借方余額”、“期初貸方余額”、“本期借方發(fā)生額”、“本期貸方發(fā)生額”、“期末借方余額”、“期末貸方余額”。用格式刷把C1:H1刷成和A1相同的格式。（4)選擇C:H列，單元格格式設(shè)置“數(shù)字”選項(xiàng)卡下的“會(huì)計(jì)專用”，小數(shù)位數(shù)為“2”，貨幣符號(hào)為“無”；設(shè)置“字體”選項(xiàng)卡下的“宋體”，字號(hào)為“12”。（5)選擇A1:H52，設(shè)置邊框?yàn)樗锌蚓€。二、定義期初余額公式操作步驟如下：選擇C2單元格，輸入下列公式：C2=VLOOKUP（A2,期初科目余額表,4,0)公式含義：在范圍名稱“期初科目余額表”的首列查找A2單元格內(nèi)的科目編碼，找到后返回該行與范圍名稱“期初科目余額表”第4列交叉點(diǎn)的值，即查找到總賬科目編碼對(duì)應(yīng)的“期初借方余額”。（4)同理定義“期初貸方余額”的公式：D2=VLOOKUP（A2,期初科目余額表,5,0)（5)用填充柄把C2:D2的公式向下復(fù)制。三、定義本期發(fā)生額公式（1)定義“總賬科目余額表”的“本期借方發(fā)生額”公式為：選擇E2單元格，輸入下列公式：E2=IF(ISNA(VLOOKUP(B2,總賬科目匯總表,2,0)),0,VLOOKUP(B2,總賬科目匯總表,2,0))公式含義：首先判斷“VLOOKUP（B2,總賬科目匯總表,2,0)”是否出現(xiàn)“#N/A”錯(cuò)誤，如果是，則說明該科目本期沒有借方發(fā)生額，即定義其值為“0”，否則，取“VLOOKUP（B2,總賬科目匯總表,2,0)”的值。其中“VLOOKUP（B3，總賬科目匯總表，2，0)”表示在范圍名稱“總賬科目匯總表”的首列查找“B2”單元格內(nèi)的“總賬科目”，找到后返回該行與范圍名稱“總賬科目匯總表”第2列交叉點(diǎn)的值，即查找到“總賬科目”對(duì)應(yīng)的“本期借方發(fā)生額”。（2)定義“總賬科目余額表”的“本期貸方發(fā)生額”公式為：選擇F2單元格，輸入下列公式：F2=IF(ISNA(VLOOKUP(B2,總賬科目匯總表,3,0)),0,VLOOKUP(B2,總賬科目匯總