Excel

在財務(wù)中的應(yīng)用

任務(wù)3應(yīng)用Excel登記會計賬簿子任務(wù)3.1建立總分類賬子任務(wù)3.2建立科目匯總表子任務(wù)3.3編制科目余額表應(yīng)用Excel登記會計賬簿任務(wù)3重點和難點1.總分類賬的建立;2.科目匯總表的建立;3.科目余額表的建立。學習目標【知識目標】1.了解會計賬簿的相關(guān)概念;2.掌握利用數(shù)據(jù)透視表功能建立總分類賬、科目匯總表的方法;3.掌握科目余額表的建立方法。【技能目標】1.能夠利用數(shù)據(jù)透視表功能建立總分類賬;2.能夠利用數(shù)據(jù)透視表功能建立科目匯總表;3.能夠利用IF函數(shù)、ISNA函數(shù)以及VLOOKUP函數(shù)編制科目余額表。應(yīng)用Excel登記會計賬簿任務(wù)3任務(wù)概述以會計憑證為依據(jù)設(shè)置和登記會計賬簿是會計賬務(wù)處理工作的中心環(huán)節(jié)。通過本任務(wù)的學習,學生可在已建立的會計科目表、會計憑證表等有關(guān)數(shù)據(jù)的基礎(chǔ)上,掌握利用Excel的數(shù)據(jù)透視表及函數(shù)等功能,建立總分類賬、科目匯總表和科目余額表等賬表。應(yīng)用Excel登記會計賬簿任務(wù)3會計賬簿的含義和作用3.1知識積累會計賬簿是指以會計憑證為依據(jù),在具有專門格式的賬頁中全面、連續(xù)、系統(tǒng)、綜合地記錄經(jīng)濟業(yè)務(wù)的簿籍。會計賬簿在會計核算中具有十分重要的意義,主要體現(xiàn)在以下幾個方面:(1)可以為經(jīng)濟管理提供連續(xù)、全面和系統(tǒng)的會計信息。(2)可以保護財產(chǎn)物資的安全完整。(3)便于企業(yè)單位考核成本、費用和利潤計劃的完成情況。(4)為編制會計報表提供資料。(5)為會計檢查、會計分析提供資料。會計賬簿的分類3.2知識積累按照在經(jīng)濟管理中的用途劃分,會計賬簿可以分為日記賬簿、分類賬簿和備查賬簿三種。3.2.1日記賬簿日記賬簿又稱為序時賬簿,是按照經(jīng)濟業(yè)務(wù)發(fā)生的時間先后順序,逐日逐筆登記經(jīng)濟業(yè)務(wù)的賬簿。按其記錄內(nèi)容的不同,可以將日記賬簿分為普通日記賬和特種日記賬兩種。3.2.2分類賬簿分類賬簿是區(qū)別不同賬戶登記經(jīng)濟業(yè)務(wù)的賬簿。按照提供指標的詳細程度不同,可以將賬戶分為總分類賬簿和明細分類賬簿兩種?？偡诸愘~簿是按一級科目分類,連續(xù)地記錄和反映資金增減、成本和利潤情況的賬簿,它能總括并全面地反映企事業(yè)單位的經(jīng)濟活動情況,是編制會計報表的依據(jù)。一切企業(yè)都應(yīng)設(shè)置總分類賬簿。明細分類賬簿是根據(jù)明細科目開設(shè)的賬簿,它能詳細地反映企業(yè)某項經(jīng)濟活動的具體情況。3.2.3備查賬簿備查賬簿是對某些在日記賬簿和分類賬簿中不能記錄登記或記錄登記不全的經(jīng)濟業(yè)務(wù)進行補充登記的賬簿。企業(yè)根據(jù)自身的情況,可以選擇設(shè)置或不設(shè)置此賬簿。科目匯總表3.3知識積累科目匯總表是指根據(jù)一定期間發(fā)生的所有經(jīng)濟業(yè)務(wù),按照相同的會計科目進行歸類并定期匯總出每個會計科目的借方本期發(fā)生額合計和貸方本期發(fā)生額合計的一種表格。科目匯總表在會計賬務(wù)處理過程中起著承上啟下的作用。一方面,它將一定期間發(fā)生的經(jīng)濟業(yè)務(wù)分門別類地進行匯總;另一方面,它為編制會計報表提供數(shù)據(jù)?？颇繀R總表的基本格式如表3-1所示?？颇看a會計科目借方本期發(fā)生額貸方本期發(fā)生額

合計

科目余額表3.4知識積累科目余額表是指用來記錄本期所有會計科目的發(fā)生額和余額的表格。它是科目匯總表的進一步延伸,能夠反映某一會計期間相關(guān)會計科目的期初余額、本期發(fā)生額和期末余額,為編制會計報表提供更完善的數(shù)據(jù)?？颇坑囝~表的基本格式如表3-2所示?？颇看a會計科目期初余額本期發(fā)生額期末余額借方貸方借方貸方借方貸方

合計

建立總分類賬子任務(wù)3.1任務(wù)要求1.掌握總分類賬的建立過程;2.能夠利用數(shù)據(jù)透視表建立總分類賬;3.能夠查詢總分類賬。建立總分類賬子任務(wù)3.1任務(wù)資料1.在“大連百大公司會計核算”工作簿,以“會計憑證表”工作表為基礎(chǔ)數(shù)據(jù),利用數(shù)據(jù)透視表功能生成如圖3-1所示的總分類賬,并將新的工作表重命名為“總分類賬”。2.在生成的總分類賬中添加“借方余額”和“貸方余額”計算字段,并設(shè)置借方余額公式為“=IF((借方金額-貸方金額)>0,借方金額-貸方金額,0)”,貸方余額公式為“=IF((貸方金額-借方金額)>0,貸方金額-借方金額,0)”,并將借方金額和貸方金額的數(shù)字格式設(shè)置為“會計專用”,小數(shù)位2位,貨幣符號“無”。結(jié)果如圖3-2所示。圖3-1圖3-2建立總分類賬子任務(wù)3.1任務(wù)資料3.在總分類賬上方添加標題“大連百大公司總分類賬”,設(shè)置字號為16磅、加粗,將各科目的匯總行隱藏,使總分類賬更加簡潔,如圖3-3所示。4.篩選“銀行存款”科目的總分類賬。5.自動更新數(shù)據(jù)透視表。圖3-3任務(wù)實施1.在“大連百大公司會計核算”工作簿,以“會計憑證表”工作表為基礎(chǔ)數(shù)據(jù),利用數(shù)據(jù)透視表功能建立總分類賬,并將新的工作表重命名為“總分類賬”。步驟1打開“大連百大公司會計核算”工作簿,選擇“會計憑證表”工作表。步驟2

選定“會計憑證表”工作表中會計分錄的任意一個單元格,選擇“插入—表格”命令組,單擊“數(shù)據(jù)透視表”按鈕,彈出“創(chuàng)建數(shù)據(jù)透視表”對話框。步驟3

在“創(chuàng)建數(shù)據(jù)透視表”對話框,選擇一個表或區(qū)域為“會計憑證表!$A$2:$J$140”,選擇放置數(shù)據(jù)透視表的位置為“新工作表”,如圖3-4所示。建立總分類賬子任務(wù)3.1圖3-4任務(wù)實施步驟4

單擊“確定”按鈕,系統(tǒng)自動創(chuàng)建一個新的工作表,并顯示“數(shù)據(jù)透視表字段”任務(wù)窗格,如圖3-5所示。步驟5

在“數(shù)據(jù)透視表字段”任務(wù)窗格中,將“年”“月”字段拖動到篩選器區(qū)域。步驟6

在“數(shù)據(jù)透視表字段”任務(wù)窗格中,將“科目編碼”“科目名稱”“日”字段拖動到“行”標簽區(qū)域。步驟7

在“數(shù)據(jù)透視表字段”任務(wù)窗格中,將“借方金額”“貸方金額”字段拖動到“值”標簽區(qū)域。操作結(jié)束后,形成如圖3-6所示的工作表。建立總分類賬子任務(wù)3.1圖3-5圖3-6任務(wù)實施步驟8

在“數(shù)據(jù)透視表字段”任務(wù)窗格中,單擊“值”標簽下的“計數(shù)項:借方余額”,在下拉列表中選擇“值字段設(shè)置”,彈出“值字段設(shè)置”對話框。在對話框中,將計算類型設(shè)置為“求和”,如圖3-7所示。步驟9

按照步驟8的操作過程,將“計數(shù)項:貸方金額”的計算類型也設(shè)置為“求和”。生成的數(shù)據(jù)透視表總分類賬如圖3-8所示。建立總分類賬子任務(wù)3.1圖3-7圖3-8任務(wù)實施步驟10

選擇B:C列,右擊鼠標,在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令,在彈出的“設(shè)置單元格格式”對話框中選擇“數(shù)字”選項卡,設(shè)置分類為“會計專用”,小數(shù)位2位,符號為“無”。步驟11

選擇“數(shù)據(jù)透視表工具—設(shè)計—布局”命令組,單擊“報表布局”下拉列表中的“以表格形式顯示”命令,結(jié)果如圖3-9所示。步驟12

選擇總分類賬所在工作表,雙擊工作表標簽名,背景反白后輸入標簽名“總分類賬”,按Enter鍵確定。建立總分類賬子任務(wù)3.1圖3-9任務(wù)實施2.在前面建立的總分類賬基礎(chǔ)上添加“借方余額”和“貸方余額”計算字段,并進行相應(yīng)設(shè)置(詳見任務(wù)資料)。步驟1

在“總分類賬”工作表中,選定數(shù)據(jù)透視表,選擇“數(shù)據(jù)透視表工具—分析—計算”命令組,選擇“字段、項目和集”下拉列表中的“計算字段”命令,打開“插入計算字段”對話框。步驟2

在“插入計算字段”對話框中,在“名稱”文本框輸入“借方余額”,在“公式”文本框中輸入“=IF((借方金額-貸方金額)>0,借方金額-貸方金額,0)”,如圖3-10所示。步驟3

在“插入計算字段”對話框中,單擊“添加”按鈕,在“名稱”文本框輸入“貸方余額”,在“公式”文本框輸入“=IF((貸方金額-借方金額)>0,貸方金額-借方金額,0)”,如圖3-11所示。建立總分類賬子任務(wù)3.1圖3-10圖3-11任務(wù)實施步驟4

單擊“確定”按鈕,在總分類賬的數(shù)據(jù)透視表最后添加“借方余額”和“貸方余額”數(shù)據(jù)列,添加計算字段后的總分類賬如圖3-12所示。步驟5選定F:G列,設(shè)置單元格格式為“會計專用”,小數(shù)位2位,貨幣符號為“無”。步驟5

選定F:G列,設(shè)置單元格格式為“會計專用”,小數(shù)位2位,貨幣符號為“無”。建立總分類賬子任務(wù)3.1圖3-12任務(wù)實施4.篩選“銀行存款”科目的總分類賬。步驟1

在“總分類賬”工作表,單擊會計科目名稱字段列右側(cè)的下拉列表,選中“銀行存款”會計科目。步驟2

單擊“確定”按鈕,結(jié)果如圖3-14所示。5.自動更新數(shù)據(jù)透視表。步驟1

選定“總分類賬”工作表。步驟2

選擇“數(shù)據(jù)透視表工具—分析—數(shù)據(jù)”命令組,選擇“刷新”命令即可。建立總分類賬子任務(wù)3.1圖3-14任務(wù)要求1.掌握科目匯總表的格式;2.能夠利用數(shù)據(jù)透視表建立科目匯總表。建立科目匯總表子任務(wù)3.2任務(wù)資料在“大連百大公司會計核算”工作簿,在總分類賬的基礎(chǔ)上利用數(shù)據(jù)透視表建立科目匯總表。建立科目匯總表子任務(wù)3.2任務(wù)實施步驟1

打開“大連百大公司會計核算”工作簿,選擇“總分類賬”工作表。步驟2

選擇“數(shù)據(jù)透視表工具—分析—數(shù)據(jù)透視表”命令組,選擇“選項”下拉列表中的“顯示報表篩選頁”命令,打開“顯示報表篩選頁”對話框,如圖3-15所示。步驟3

在“顯示報表篩選頁”對話框中,選擇“月”選項,單擊“確定”按鈕,系統(tǒng)自動新建一個名稱為“12”的工作表,在“12”工作表中形成一個與“總分類賬”相同的工作表。建立科目匯總表子任務(wù)3.2圖3-15任務(wù)實施步驟4

在“數(shù)據(jù)透視表字段”任務(wù)窗格中取消“日”“借方余額”“貸方余額”的選擇狀態(tài)。步驟5

選擇“數(shù)據(jù)透視表工具—設(shè)計—布局”命令組,單擊“分類匯總”下拉列表中的“不顯示分類匯總”命令,如圖3-16所示。步驟6

工作表“12”變?yōu)槿鐖D3-17所示的格式,即科目匯總表。步驟7

將工作表“12”重命名為“科目匯總表”即可。建立科目匯總表子任務(wù)3.2圖3-16圖3-17任務(wù)要求1.掌握科目余額表的格式;2.掌握科目余額表的編制原理;2.能夠利用IF函數(shù)、ISNA函數(shù)和VLOOKUP函數(shù)建立科目余額表。編制科目余額表子任務(wù)3.3任務(wù)資料1.在“大連百大公司會計核算”工作簿插入一個空白工作表,并將工作表重命名為“科目余額表”,并按如圖3-18所示的格式設(shè)計科目余額表。設(shè)置“期初余額”“本期發(fā)生額”“期末余額”列(C:H列)的數(shù)字格式為“會計專用”,2位小數(shù),貨幣符號為“無”。2.根據(jù)給定的上期科目余額表的期末余額和科目余額表的編制原理,完成本期科目余額表期初余額的編制。

【小提示】由于科目余額表中的會計科目固定,本期科目余額表的期初余額可從上期科目余額表中的期末余額調(diào)用。直接引用公式為“=[被引用工作簿名稱]被引用工作表名稱!被引用單元格”,如果數(shù)據(jù)在同一個工作簿中,則“被引用工作簿名稱”可以省略。編制科目余額表子任務(wù)3.3圖3-18任務(wù)資料2.根據(jù)給定的上期科目余額表的期末余額和科目余額表的編制原理,完成本期科目余額表期初余額的編制。

【小提示】由于科目余額表中的會計科目固定,本期科目余額表的期初余額可從上期科目余額表中的期末余額調(diào)用。直接引用公式為“=[被引用工作簿名稱]被引用工作表名稱!被引用單元格”,如果數(shù)據(jù)在同一個工作簿中,則“被引用工作簿名稱”可以省略。3.根據(jù)科目匯總表的借方金額和貸方金額,編制科目余額表的本期發(fā)生額。【小提示】科目余額表中本期發(fā)生額需要從本期科目匯總表中調(diào)用。由于每個會計期間發(fā)生的經(jīng)濟業(yè)務(wù)不完全相同,因此根據(jù)科目匯總表自動生成的會計科目并不固定。從本期科目匯總表中調(diào)用數(shù)據(jù)時,不能直接調(diào)用,需要借助IF函數(shù)、ISNA函數(shù)和VLOOKUP函數(shù)間接調(diào)用。編制科目余額表子任務(wù)3.3任務(wù)資料本期借方發(fā)生額公式:=IF(ISNA(VLOOKUP("查找的會計科目",科目匯總表,2,FALSE)),0,VLOOKUP("查找的會計科目",科目匯總表,2,FALSE))。本期貸方發(fā)生額公式:=IF(ISNA(VLOOKUP("查找的會計科目",科目匯總表,3,FALSE)),0,VLOOKUP("查找的會計科目",科目匯總表,3,FALSE))。其中:(1)ISNA函數(shù)用于判斷VLOOKUP函數(shù)的返回值是否為錯誤值"#N/A"(即值不存在)。如果為錯誤值,返回TRUE值,否則返回FALSE值。(2)IF函數(shù)用于判斷ISNA返回值,如果為TRUE,則返回0,否則使用VLOOKUP函數(shù)在科目匯總表中進行查找。4.計算科目余額表的期末余額。

【小提示】科目余額表中所有的會計科目分為資產(chǎn)類、負債類、所有者權(quán)益類、成本類和損益類五類。根據(jù)會計核算規(guī)則,科目余額表期末余額的計算公式為:資產(chǎn)類和成本類會計科目期末余額=期初余額+本期借方發(fā)生額-本期貸方發(fā)生額負債類和所有者權(quán)益類會計科目期末余額=期初余額+本期貸方發(fā)生額-本期借方發(fā)生額損益類會計科目無余額。編制科目余額表子任務(wù)3.3任務(wù)資料1.在“大連百大公司會計核算”工作簿中建立“科目余額表”工作表,并按要求進行設(shè)計。步驟1

打開“大連百大公司會計核算”工作簿,插入一個新的工作表,并命名為“科目余額表”。步驟2

在“科目余額表”工作表中,選定A1:H1單元格區(qū)域,單擊“合并后居中”命令,在A1單元格中輸入“大連百大公司科目余額表”,并設(shè)置為加粗、16磅。步驟3

按圖3-18所示設(shè)計“科目余額表”工作表的格式。步驟4

按圖3-18所示輸入科目代碼和相應(yīng)的會計科目名稱。步驟5

選擇C:H列,設(shè)置單元格格式數(shù)字分類為“會計專用”,小數(shù)位為2,貨幣符號為“無”。步驟6

選擇C57單元格,輸入公式“=SUM(C4:C56)”,按Enter鍵確認公式輸入。步驟7

選擇C57單元格,按住鼠標左鍵拖動填充柄至H57,對D57:H57進行快速填充。至此,科目余額表建立完成。編制科目余額表子任務(wù)3.3任務(wù)資料2.根據(jù)給定的上期科目余額表的期末余額和科目余額表的編制原理,完成本期科目余額表期初余額的編制。步驟1打開“期初余額表”工作簿。步驟2選擇“大連百大公司會計核算”工作簿,選定“科目余額表”工作表中的C4單元格。步驟3在C4單元格輸入“=”,將鼠標移動到“期初余額表”工作簿,單擊“2019年11月”工作表的G4單元格,按Enter鍵,系統(tǒng)自動將“期初余額表”工作簿中的“2019年11月”工作表中的G4單元格數(shù)據(jù)直接調(diào)用到“科目余額表”的C4單元格,如圖3-19所示。步驟4使用步驟3的方法,建立其他會計科目期初余額的鏈接。至此,2019年12月份科目余額表的期初余額編制完成。編制科目余額表子任務(wù)3.3圖3-19任務(wù)資料3.根據(jù)科目匯總表的借方金額和貸方金額,編制科目余額表的本期發(fā)生額。步驟1

打開“科目余額表”工作表,選定E4單位格。步驟2

單擊編輯欄上的“插入函數(shù)”按鈕,在打開的“插入函數(shù)”對話框中選擇邏輯函數(shù)中的IF函數(shù),打開“函數(shù)參數(shù)”對話框。步驟3

將光標定位于IF函數(shù)的Logical_test文本框,如圖3-20所示,單擊左上角的下拉列表框中的ISNA函數(shù),彈出ISNA函數(shù)參數(shù)對話框,如圖3-21所示。編制科目余額表子任務(wù)3.3圖3-21圖3-20任務(wù)資料步驟4將光標定位在ISNA函數(shù)的Value文本框,按步驟3操作單擊下拉列表框中的VLOOKUP函數(shù),打開VLOOKUP函數(shù)參數(shù)對話框。步驟5在VLOOKUP函數(shù)參數(shù)對話框的Lookup_value文本框輸入要查找的會計科目“"庫存現(xiàn)金"”,在Table_array文本框中輸入“科目匯總表!B5:D38”(也可以直接選取工作表區(qū)域),在Col_index_num文本框中輸入“2”,在Range_lookup文本框中輸入“FALSE”,如圖3-22所示。步驟6單擊編輯欄上的IF函數(shù),返回IF函數(shù)參數(shù)對話框,如圖3-23所示。編制科目余額表子任務(wù)3.3圖3-23圖3-22任務(wù)資料步驟7

在IF函數(shù)的Value_if_true文本框中輸入“0”,將光標定位于IF函數(shù)的Value_if_false文本框,再插入VLOOKUP函數(shù),打開VLOOKUP函數(shù)參數(shù)對話框。步驟8

重復步驟5的操作,單擊“確定”按鈕,則在E4單元格顯示本月“庫存現(xiàn)金”科目的借方發(fā)生額,如圖3-24所示。步驟9

使用以上方法,將其他會計科目的借方本期發(fā)生額和貸方本期發(fā)生額與科目匯總表建立動態(tài)鏈接。

【小提示】：在設(shè)計本期發(fā)生額公式時,不得使用快速填充功能,因為每次查找的會計科目名稱不同,而且會計科目類別也不同,有些會計科目沒有發(fā)生額。編制科目余額表子任務(wù)3.3圖3-24任務(wù)資料4.計算科目余額表的期末余額。步驟1

選擇“科目余額表”工作表,選定G4單元格。步驟2

在G4單元格輸入公式“=C4+E4-F4”,表示“資產(chǎn)類和成本類會計科目的期末余額=期初借方金額+借方本期發(fā)生額-貸方本期發(fā)生額”,如圖3-25所示,按Enter鍵完成輸入,在G4單元格顯示計算結(jié)果。步驟3

選定G4單元格,右擊鼠標,在彈出的快捷菜單中選擇“復制”命令。編制科目余額表子任務(wù)3.3圖3-25任務(wù)資料步驟4

選定其他資產(chǎn)類和成本類會計科目的借方期末余額的單元格區(qū)域,在