《Excel在會計與財務(wù)管理中的應(yīng)用》 教案 項目三 會計賬簿

Excel在會計與財務(wù)管理中的應(yīng)用課程教案課程名稱：Excel在會計與財務(wù)管理中的應(yīng)用課程號：課程學分：2參考學時：32課程性質(zhì)：專業(yè)必修課適用專業(yè)：經(jīng)管類專業(yè)上課時間：授課教師：負責人：審核單位：年月日-1-一、課程基本信息課程名稱Excel在會計與財務(wù)管理中的應(yīng)用課程號課程性質(zhì)專業(yè)必修課開課部門課程負責人課程團隊授課學期學分/學時2/32授課語言漢語課程簡介Excel是使用極為廣泛的電子表格軟件，其以操作簡便、功能強大而著稱，非常適合缺乏計算機編程語言知識和對數(shù)據(jù)處理要求不高的財務(wù)管理人員使用。本書以Excel2010為基礎(chǔ)，通過實例詳細介紹了Excel在會計電算化中的應(yīng)用，并應(yīng)用Excel對企業(yè)運營中的會計流程和財務(wù)管理問題進行成因分析。本書應(yīng)用實例典型，內(nèi)容豐富，充分利用宏、MicrosofQuery組件、數(shù)據(jù)透視表組件等完成會計電算化的設(shè)計。書中各項目詳細介紹了實例的操作步驟，學習者只需按照書中介紹的步驟一步步地實際操作，就能完全掌握本書的內(nèi)容。授課班級

二、課程授課教案項目三講課主題會計賬簿學時6學時教學目標(1)了解MicrosoftQuery的使用方法；(2)了解數(shù)據(jù)驗證的方法；(3)掌握總分類賬的結(jié)構(gòu)；(4)掌握科目余額表的結(jié)構(gòu)和MicrosoftQuery提取數(shù)據(jù)的方法；(5)掌握明細分類賬的設(shè)計方法；(6)學會應(yīng)用Excel解決實際案例中遇到的會計賬簿問題。任務(wù)目標(1)制作科目匯總表；(2)完成科目匯總表數(shù)據(jù)。(3)制作科目余額表；(4)導入科目代碼；(5)完成科目余額。(6)制作總分類賬；(7)完成總分類賬數(shù)據(jù)。教學設(shè)計授課提綱及重難點分析教學方法及課程思政設(shè)計教學時間任務(wù)一科目匯總表知識準備Excel中有一個MicrosoftQuery組件，該組件的作用相當于數(shù)據(jù)庫的查詢分析器，能夠通過該組件從數(shù)據(jù)庫中提取數(shù)據(jù)并顯示到工作表中。雖然Excel并不是真正意義上的數(shù)據(jù)庫，但是MicrosoftQuery組件依舊能夠從指定的工作簿(包括當前正在使用的工作簿)中提取數(shù)據(jù)。如果把“科目匯總表”工作表當作數(shù)據(jù)表看待，就可以解決會計科目的來源問題；如果把“憑證庫”工作表當作數(shù)據(jù)表看待，就可以解決金額來源的問題。任務(wù)資料科目匯總表包括如下內(nèi)容：(1)日期信息。(2)科目代碼。(3)科目名稱。(4)指定月借方發(fā)生額。(5)指定月貸方發(fā)生額。任務(wù)操作1.科目匯總表界面(1)在“賬務(wù)處理.xlsm”中新建一張工作表，將其重命名為“科目匯總表”。(2)選中A1:D1單元格區(qū)域，選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，將指定的內(nèi)容合并居中，然后在A1單元格中輸入“=B2&″年″&D2&″月科目匯總表″”，完成表頭標題的設(shè)置。(3)從A2單元格開始輸入如圖3-1所示的內(nèi)容，完成科目匯總表界面的設(shè)置。2.科目匯總表數(shù)據(jù)(1)選中A4單元格，選擇“數(shù)據(jù)”選項卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-2所示的“選擇數(shù)據(jù)源”對話框。(2)在“選擇數(shù)據(jù)源”對話框中選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-3所示的“選擇工作簿”對話框。(3)在“驅(qū)動器”中選擇D盤，在“目錄”中選擇“data正文”，數(shù)據(jù)庫的名稱指定為“賬務(wù)處理.xlsm”。(4)單擊“下一頁”按鈕，完成要顯示的列的選擇，打開“查詢向?qū)?篩選數(shù)據(jù)”對話框，如圖3-5所示。(5)單擊“下一頁”按鈕，跳過篩選過程，打開如圖3-6所示的“查詢向?qū)?排序順序”對話框，在該步驟中不需要設(shè)置任何內(nèi)容。(6)單擊“下一頁”按鈕，打開如圖3-7所示的“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”單選按鈕，單擊“完成”按鈕。(7)在MicrosoftQuery窗口中選擇“科目代碼”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-9所示的“編輯列”對話框，將“字段”下的內(nèi)容更改為“Left(科目代碼,4)”，單擊“確定”按鈕，完成列的設(shè)置。(8)在MicrosoftQuery窗口中選擇“借金額”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-10所示的“編輯列”對話框，在“字段”下拉列表中選擇“借金額”，在“總計”下拉列表中選擇“求和”，單擊“確定”按鈕，完成“借金額”字段的求和。(9)在MicrosoftQuery窗口中選擇“貸金額”列中任意一個數(shù)據(jù)，選擇“記錄”→“編輯列”命令，打開如圖3-11所示的“編輯列”對話框，在“字段”下拉列表中選擇“貸金額”，在“總計”下拉列表中選擇“求和”，單擊“確定”按鈕，完成“貸金額”字段的求和。(10)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-12所示的“添加條件”對話框。51min任務(wù)二科目余額表知識準備科目余額表是記錄本期所有會計科目的發(fā)生額和余額的表格，它反映了某一會計期間相關(guān)會計科目的期初余額、本期發(fā)生額和期末余額。與科目匯總表類似，科目余額表反映的是各個科目的余額，但是各個月份的會計科目并不是固定的，部分明細科目會隨著企業(yè)業(yè)務(wù)的發(fā)展而逐漸加入其中，因此直接使用公式引用科目代碼表并不是一個很好的選擇。任務(wù)一已經(jīng)介紹了一種導入變動數(shù)據(jù)的方法，即利用MicrosoftQuery組件。用戶通過MicrosoftQuery組件將“科目代碼表”中的科目導入工作表后，只需要單擊刷新，則不論什么樣的會計科目總能出現(xiàn)在工作表中，并且這些數(shù)據(jù)和科目代碼表中的數(shù)據(jù)完全一致。同樣，科目余額表也會遇到余額的處理問題?？颇坑囝~表中涉及的科目余額計算包括期初余額、本期發(fā)生額和期末余額。本任務(wù)中，企業(yè)從年初開始建賬，因此期初余額就是年初數(shù)，本期發(fā)生額是指定期限中的累計借方和累計貸方的發(fā)生額，期末余額則是根據(jù)期初余額和本期發(fā)生額計算的結(jié)果。任務(wù)資料科目余額表需要實現(xiàn)的功能如下：(1)期初各個會計科目的借方和貸方余額。(2)當期各會計科目的借方和貸方發(fā)生額。(3)計算本期期末各個會計科目的借方和貸方余額。任務(wù)操作1.科目余額表界面(1)新建工作表，將工作表重命名為“科目余額表”。(2)選中E1:G1單元格區(qū)域，選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，設(shè)置字體為“仿宋_GB2312”，字號為24，并設(shè)置雙下畫線，完成字體字號的設(shè)置。(3)選中E1單元格，在編輯欄內(nèi)輸入公式“=B2&″年″&G2&″月科目余額表″”，完成科目余額表標題的設(shè)置。(4)從第2行開始輸入如圖3-20所示的科目余額表中的各項。2.導入科目代碼(1)選擇“數(shù)據(jù)”選項卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開“選擇數(shù)據(jù)源”對話框。(2)選擇“數(shù)據(jù)庫類型”為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-21所示的“選擇工作簿”對話框。(3)選擇工作簿所在的文件路徑，單擊“確定”按鈕，打開如圖3-22所示的“查詢向?qū)?選擇列”對話框，單擊“科目代碼表”前的“+”按鈕，展開該表所包含的字段，選中要顯示的列為科目代碼、科目名稱、性質(zhì)和年初數(shù)。(4)單擊“下一頁”按鈕，完成要顯示的列的選擇。(5)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-23所示的“添加條件”對話框，選擇“字段”為“是否明細”，“指定值”為“y”，單擊“添加”按鈕，完成條件設(shè)置，單擊“關(guān)閉”按鈕。(6)在MicrosoftQuery窗口中選擇“文件”→“將數(shù)據(jù)返回MicrosoftExcel”命令，打開如圖3-24所示的“導入數(shù)據(jù)”對話框，將“數(shù)據(jù)的放置位置”設(shè)置為科目余額表的A5單元格，完成數(shù)據(jù)導入操作。(7)選中A5單元格，選擇“設(shè)計”選項卡，在“表樣式”組中選擇一種樣式。選中D5單元格，右擊，在彈出的快捷菜單中選擇“插入”→“在右側(cè)插入表列”命令，插入一個新列。使用同樣的操作方法插入其他5列，完成表主體結(jié)構(gòu)的設(shè)置。(8)選中A5單元格，選擇“數(shù)據(jù)”選項卡，執(zhí)行“排序和篩選”組中的“篩選”命令，退出篩選狀態(tài)。(9)選中A5單元格，選擇“設(shè)計”選項卡，將“屬性”組內(nèi)的表名稱更改為“kmye”，完成表名稱的設(shè)置。最終完成的科目余額表如圖3-25所示。3.科目余額(1)選中E6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=1,[@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)-SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)),0)”，完成期初余額的計算。(2)選中F6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=-1,ABS([@性質(zhì)]*[@年初數(shù)]+(SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2)-SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],″<″&$G$2))),0)”，完成期初貸方余額的計算。(3)選中G6單元格，在編輯欄內(nèi)輸入公式“=SUMIFS(pzk[借金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月借方發(fā)生額的計算。(4)選中H6單元格，在編輯欄內(nèi)輸入公式“=SUMIFS(pzk[貸金額],pzk[科目代碼],[@科目代碼],pzk[月],$G$2)”，完成本月貸方發(fā)生額的計算。(5)選中I6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=1,[@列1]+[@列3]-[@列4],0)”，完成借方余額的計算。(6)選中J6單元格，在編輯欄內(nèi)輸入公式“=IF([@性質(zhì)]=-1,[@列2]+[@列4]-[@列3],0)”，完成貸方余額的計算。(7)選中C6:J79單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對話框，選擇數(shù)值類型為“會計專用”，不顯示貨幣符號類型。(8)選中A5單元格，選擇“設(shè)計”選項卡，執(zhí)行“表格樣式”組內(nèi)的“匯總行”命令，如圖3-26所示，完成匯總行的添加。(9)在“匯總”行中選中列5所在的列，從下拉列表中選擇匯總方式為“求和”，如圖3-27所示，完成期末借方余額的匯總。(10)用同樣的方法，將匯總行中列1、列2、列3和列4字段所在的列匯總方式設(shè)置為“求和”。(11)在“匯總”行中選中年初數(shù)所在的列，在編輯欄內(nèi)輸入公式“=SUM([性質(zhì)]*[年初數(shù)])”，同時按Shift+Ctrl+Enter鍵，完成數(shù)組公式的輸入。(12)選中第5行，即字段名稱所在的行，右擊，在彈出的快捷菜單中選擇“隱藏”命令，隱藏該行。(13)選中C列和D列，右擊，在彈出的快捷菜單中選擇“隱藏”命令，隱藏指定的列。(14)選中A6單元格，選擇“設(shè)計”選項卡，執(zhí)行“外部表數(shù)據(jù)”組中的“屬性”命令，打開“外部數(shù)據(jù)屬性”對話框，如圖3-28所示。在“數(shù)據(jù)格式和布局”下取消選中“調(diào)整列寬”復選框，單擊“確定”按鈕，完成外部數(shù)據(jù)屬性的設(shè)置。4.獲取最新數(shù)據(jù)(1)選中“科目余額表”，選擇“開發(fā)工具”選項卡，選擇“控件”組中“插入”下“表單控件”中的“按鈕控件”，在J1單元格內(nèi)拖出一個命令按鈕，將命令按鈕內(nèi)的文字更改為“刷新”，如圖3-30所示，完成命令按鈕的設(shè)置。(2)選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組中的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新科目”，如圖3-31所示，單擊“確定”按鈕，開始錄制宏。(3)選中A6單元格，右擊，在彈出的快捷菜單中選擇“刷新”命令。選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組中的“停止錄制”命令，完成宏的錄制。(4)在“刷新”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開“指定宏”對話框，選擇“刷新科目”項，單擊“確定”按鈕，完成指定宏的設(shè)置。51min任務(wù)三總分類賬知識準備總分類賬就是我們通常所稱的總賬，它是根據(jù)總分類科目開設(shè)賬戶，用來登記全部經(jīng)濟業(yè)務(wù)，進行總分類核算，提供總括核算資料的分類賬簿?？偡诸愘~提供了編制會計報表的主要依據(jù)，是所有單位都必須設(shè)立的賬簿?？偡诸愘~全面且總括地反映了業(yè)務(wù)單位的財務(wù)收支和經(jīng)濟活動情況。總分類賬的結(jié)構(gòu)本身并不復雜，使用Excel進行設(shè)計時，只需要更改日期信息和會計科目，就能夠得到期初余額、當期的借方發(fā)生額和貸方發(fā)生額以及期末余額，但其并不反映具體的業(yè)務(wù)信息。日期信息是由用戶手工更改的，會計科目信息可以通過數(shù)據(jù)驗證的方式交由用戶選擇而不是直接輸入，從而避免發(fā)生錯誤，各種余額和發(fā)生額則都是通過函數(shù)從憑證庫中提取的。任務(wù)資料要求根據(jù)已經(jīng)完成的科目余額表中的期初數(shù)和憑證庫中的憑證數(shù)據(jù)編制總分類賬。任務(wù)操作1.總分類賬界面(1)新建一張工作表，將工作表名稱更改為“總分類賬”，選中D1:E1單元格區(qū)域，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，“字體”為“仿宋_GB2312”，在D1單元格內(nèi)輸入“總分類賬”，并設(shè)置雙下畫線，完成總分類賬標題的設(shè)置。(2)從A2單元格開始輸入如圖3-34所示的表格內(nèi)容。(3)選中C3單元格，選擇“數(shù)據(jù)”選項卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗證”命令，打開如圖3-35所示的“數(shù)據(jù)驗證”對話框，在“允許”下拉列表中選擇“序列”，在“來源”文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)驗證的設(shè)置。(4)選中F3單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=4,VLOOKUP($C$3,kmdm,2,FALSE),(VLOOKUP(LEFT($C$3,4)+0,kmdm,2,FALSE))&″-″&VLOOKUP($C$3,kmdm,2,FALSE))”，完成會計科目名稱的設(shè)置。2.總分類賬數(shù)據(jù)(1)選中C2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!B2”；選中F2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!G2”；選中A4單元格，在編輯欄內(nèi)輸入公式“=C2&″年″”，完成年份和月份信息的設(shè)置。(2)選中A6單元格，在編輯欄內(nèi)輸入公式“=$F$2”，按Enter鍵確認；將A6單元格的內(nèi)容向下拖曳到A7和A8單元格內(nèi)，完成月份信息的填制。(3)在B6單元格內(nèi)輸入數(shù)值1；選中B7單元格，在編輯欄內(nèi)輸入公式“=DAY(DATE($C$2,$F$2+1,0))”，計算指定月份的天數(shù)；選中B8單元格，在編輯欄內(nèi)輸入公式“=B7”，完成日信息的填制。(4)在C6單元格內(nèi)輸入“期初余額”，在C7單元格內(nèi)輸入“本月發(fā)生額”，在C8單元格內(nèi)輸入“本月合計”，完成摘要內(nèi)容的填制。(5)選中D6單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列1],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列1],0)))”，同時按Ctrl+Shift+Enter鍵，完成借金額期初數(shù)的設(shè)置。(6)選中E6單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列2],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列2],0)))”，同時按Ctrl+Shift+Enter鍵，完成貸金額期初數(shù)的設(shè)置。(7)選中F6單元格，在編輯欄內(nèi)輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質(zhì)])>0,″借″,″貸″)”，完成借貸方向的設(shè)置。(8)選中G6單元格，在編輯欄內(nèi)輸入公式“=IF(F6=″借″,D6-E6,E6-D6)”，完成期初余額的設(shè)置。(9)選中D7單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列3],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列3],0)))”，同時按Ctrl+Shift+Enter鍵，完成指定月借方發(fā)生額的設(shè)置。(10)選中E7單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=6,SUMIFS(kmye[列4],kmye[科目代碼],C3),SUM(IF(LEFT(kmye[科目代碼],4)+0=LEFT($C$3,4)+0,kmye[列4],0)))”，同時按Ctrl+Shift+Enter鍵，完成指定月貸方發(fā)生額的設(shè)置。3.優(yōu)化顯示(1)選中G7:G8單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開如圖3-41所示的“設(shè)置單元格格式”對話框。選擇“數(shù)字”選項卡，在“分類”列表框中選擇“自定義”，在類型中輸入“#,##0.00;#,##0.00”，單擊“確定”按鈕，完成數(shù)值顯示格式的設(shè)置。(2)選中整張工作表，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，選擇“保護”選項卡，選中“鎖定”復選框，單擊“確定”按鈕，完成全部單元格的鎖定操作。(3)選中C3單元格，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，選擇“保護”選項卡，取消選中“鎖定”復選框，單擊“確定”按鈕，完成可編輯單元格的指定操作。(4)選擇“開始”選項卡，執(zhí)行“單元格”組內(nèi)“格式”下的“保護工作表”命令，打開“保護工作表”對話框，設(shè)置一個合適的密碼，完成工作表的保護操作。51min任務(wù)四明細分類賬知識準備用戶從總分類賬中得到的僅僅是某個會計科目在指定期間內(nèi)總的發(fā)生額，但是該科目到底發(fā)生了什么業(yè)務(wù)并不明確，這就要求使用明細分類賬來顯示具體的業(yè)務(wù)信息。明細分類賬是根據(jù)明細分類賬戶進行分類登記的賬簿，是根據(jù)單位開展經(jīng)濟管理的需要對經(jīng)濟業(yè)務(wù)的詳細內(nèi)容進行的核算，是對總分類賬進行的補充反映。從技術(shù)角度來說，創(chuàng)建明細分類賬需要用戶指定的信息如下：(1)會計科目：用戶查詢的是哪個明細分類賬戶的信息。(2)日期信息：用戶查詢的是哪個月份的信息。(3)期初余額：明細分類賬期初余額信息，有了期初余額和本月發(fā)生信息，就可以計算期末的科目余額。會計科目是由用戶指定的，月份信息可以由用戶在科目余額表中指定，期初余額數(shù)據(jù)通過公式從科目余額表中獲得。具體的業(yè)務(wù)信息數(shù)據(jù)來自“憑證庫”工作表，為此需要通過MicrosoftQuery組件創(chuàng)建查詢來獲得相關(guān)的數(shù)據(jù)。期末余額是通過期初余額和本期借貸方發(fā)生額獲得的。任務(wù)資料明細分類賬要實現(xiàn)如下幾個功能：(1)列出指定月份和科目代碼的期初數(shù)。(2)列出本期指定會計科目的發(fā)生記錄，并將這些記錄填到表中。(3)計算本期期末的借方和貸方余額。任務(wù)操作1.明細分類賬界面(1)新建一張工作表，將工作表名稱更改為“明細分類賬”。選中A1:H1單元格區(qū)域，將該單元格區(qū)域合并并居中，設(shè)置“字號”為24號，在編輯欄內(nèi)輸入公式“=F3&″明細分類賬″”，并設(shè)置雙下畫線，完成明細分類賬標題的設(shè)置。(2)從A2單元格開始輸入如圖3-43所示的內(nèi)容。(3)選中C3單元格，選擇“數(shù)據(jù)”選項卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗證”命令，打開如圖3-44所示的“數(shù)據(jù)驗證”對話框，在“允許”下拉列表中選擇“序列”，在“來源”文本框內(nèi)輸入“=dm”，單擊“確定”按鈕，完成數(shù)據(jù)有效性的設(shè)置。(4)選中F3單元格，在編輯欄內(nèi)輸入公式“=IF(LEN(C3)=4,VLOOKUP($C$3,kmdm,2,FALSE),(VLOOKUP(LEFT($C$3,4)+0,kmdm,2,FALSE))&″-″&VLOOKUP($C$3,kmdm,2,FALSE))”，完成會計科目名稱的設(shè)置。(5)選中F2單元格，在編輯欄內(nèi)輸入公式“=科目余額表!G2”；選中A6單元格，在編輯欄內(nèi)輸入公式“=F2”，完成月份的設(shè)置。(6)在B6單元格中輸入數(shù)值1，在D6單元格內(nèi)輸入“期初余額”。(7)選中E6單元格，在編輯欄內(nèi)輸入公式“==SUMIF(kmye[科目代碼],$C$3,kmye[列1])”，完成期初借方金額的設(shè)置。(8)選中F6單元格，在編輯欄內(nèi)輸入公式“=SUMIF(kmye[科目代碼],$C$3,kmye[列2])”，完成期初貸方金額的設(shè)置。(9)選中G6單元格，在編輯欄內(nèi)輸入公式“=IF(SUMIF(kmdm[科目代碼],$C$3,kmdm[性質(zhì)])>0,″借″,″貸″)”，完成借貸方向的設(shè)置。(10)選中H6單元格，在編輯欄內(nèi)輸入公式“=IF(G6=″借″,E6-F6,F6-E6)”，完成期初余額的設(shè)置。2.導入業(yè)務(wù)信息(1)選擇“數(shù)據(jù)”選項卡，執(zhí)行“獲取外部數(shù)據(jù)”組中“自其他來源”中的“來自MicrosoftQuery”命令，打開如圖3-45所示的“選擇數(shù)據(jù)源”對話框。(2)在“選擇數(shù)據(jù)源”對話框中選擇數(shù)據(jù)庫類型為“ExcelFiles*”，單擊“確定”按鈕，打開如圖3-46所示的“選擇工作簿”對話框。(3)選擇本工作簿所在的路徑，單擊“確定”按鈕，打開如圖3-47所示的“查詢向?qū)?選擇列”對話框，單擊“可用的表和列”中“憑證庫”前的“+”按鈕，展開該表所包含的列，選中要顯示的列為月、日、憑證號、摘要、借金額和貸金額。(4)單擊“下一頁”按鈕，完成要顯示的列的選擇，進入“查詢向?qū)?篩選數(shù)據(jù)”對話框，直接單擊“下一頁”按鈕，跳過篩選過程，打開如圖3-48所示的“查詢向?qū)?排序順序”對話框，“主要關(guān)鍵字”設(shè)置為“憑證號”，完成排序操作。(5)單擊“下一頁”按鈕，打開“查詢向?qū)?完成”對話框，在“請確定下一步的動作”中選中“在MicrosoftQuery中查看數(shù)據(jù)或編輯查詢”單選按鈕，單擊“完成”按鈕，完成查詢向?qū)У牟僮?，進入MicrosoftQuery窗口。(6)在MicrosoftQuery窗口中選擇“條件”→“添加條件”命令，打開如圖3-49所示的“添加條件”對話框，選擇“字段”為“月”，“指定值”為“[yue]”，完成第一個參數(shù)條件的設(shè)置。(7)單擊“添加”按鈕，打開如圖3-50所示的“輸入?yún)?shù)值”對話框，此時不需要設(shè)置任何參數(shù)，單擊“確定”按鈕，完成第一個參數(shù)的設(shè)置。(8)在“添加條件”對話框中選擇“字段”為“科目代碼”，“指定值”為“[kmdm]”，如圖3-51所示，完成第二個參數(shù)條件的設(shè)置。單擊“添加”按鈕，打開“輸入?yún)?shù)值”對話框，此時不需要設(shè)置任何參數(shù)，單擊“確定”按鈕，完成第二個參數(shù)的設(shè)置。3.添加匯總行(1)選中A8單元格，選擇“設(shè)計”選項卡，執(zhí)行“表樣式選項”組中的“匯總行”命令，完成添加匯總行的操作。(2)刪除“月”字段所在列中的“匯總”字樣，在匯總行中，在“摘要”字段所在的單元格內(nèi)輸入文字“本月合計”。(3)在“匯總”行中選中“借金額”所在的列，在下拉列表中選擇匯總方式為“求和”，如圖3-55所示，完成本月借方發(fā)生額的計算。(4)在“匯總”行中選中“貸金額”所在的列，在下拉列表中選擇匯總方式為“求和”，完成本月貸方發(fā)生額的計算。(5)在“匯總”行中選中“列1”所在的列，在編輯欄中輸入公式“=G6”，完成借貸方向的設(shè)置。(6)在“匯總”行中選中“列2”所在的列，在編輯欄內(nèi)輸入公式“=IF($G$6=″借″,$H$6+flz[[#匯總],[借金額]]-flz[[#匯總],[貸金額]],H6+flz[[#匯總],[貸金額]]-flz[[#匯總],[借金額]])”，完成期末余額的計算。4.獲取最新數(shù)據(jù)(1)選中“明細分類賬”工作表，選擇“開發(fā)工具”選項卡，執(zhí)行“控件”組中“插入”下的“表單控件”命令，選擇“按鈕控件”，在H2:H3單元格區(qū)域內(nèi)拖曳一個命令按鈕，如圖3-59所示，將命令按鈕內(nèi)的文字更改為“數(shù)據(jù)刷新”，完成命令按鈕的放置。(2)選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組中的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“刷新明細分類賬”,單擊“確定”按鈕，開始錄制宏。(3)選中A8單元格，選擇“數(shù)據(jù)”選項卡，執(zhí)行“連接”組中的“全部刷新”命令，完成數(shù)據(jù)刷新操作。(4)選擇“開發(fā)工具”選項卡，執(zhí)行“代碼”組中的“停止錄制”命令，完成宏的錄制。(5)在“刷新數(shù)據(jù)”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開“指定宏”對話框，選擇“刷新明細分類賬”項，單擊“確定”按鈕，完成為按鈕指定宏的操作。51min任務(wù)五設(shè)計導航頁面知識準備用戶為自己設(shè)計的軟件添加了諸多功能后，可能會遇到一個問題，即如何快速找到自己所需的功能。這在一般的軟件中是通過添加菜單、導航欄或者添加鏈接來解決的。由于本書設(shè)計的財務(wù)管理系統(tǒng)并沒有采用編程方式，因此無法自定義菜單和選項卡。用戶要達到相同的目的，可以在工作簿中添加一個導航頁面，利用工作表鏈接的方法快速到達指定頁面?？颇繀R總表、科目余額表、總分類賬和明細分類賬都涉及指定月份的功能，而科目余額表又為總分類賬和明細分類賬提供了期初余額，這就要求上述表格中的數(shù)據(jù)要保持一致性。因此，可以在導航頁面上設(shè)置月份信息，而其他表格的月份信息都和導航頁面中指定的月份信息一致，從而保證不至于因為月份不同步而發(fā)生期初余額引用數(shù)據(jù)上的錯誤。任務(wù)資料利用導航頁面完成在各個頁面之間的跳轉(zhuǎn)。任務(wù)操作1.導航頁面(1)創(chuàng)建一張新的工作表，將工作表的名稱更改為“首頁”。(2)選擇“文件”→“選項”命令，打開“Excel選項”對話框，選擇“高級”選項卡，在“此工作表的顯示選項”中取消選中“顯示行和列標題”和“顯示網(wǎng)格線”復選框。(3)選擇“插入”選項卡，執(zhí)行“插圖”組中的“聯(lián)機圖片”命令，打開如圖3-61所示的“聯(lián)機圖片”對話框，在搜索范圍內(nèi)輸入“計算機”，按Enter鍵確認后，選擇一張合適的圖片，單擊“插入”按鈕，完成圖片的插入操作。(4)將圖片放置在合適的位置，調(diào)整到合適的大