Excel在會計與財務管理中的應用項目一現金日記賬(1)了解現金日記賬的手工記賬方法；(2)掌握Excel中表對象和數據透視表的基本使用方法；(3)掌握SUBTOTAL和IF等函數的使用方法；(4)能夠熟練繪制表格及使用函數和公式；(5)學會應用Excel解決實際案例中遇到的現金日記賬問題。學習目標日記賬包括現金日記賬和銀行日記賬，由出納人員（資金會計）按照業務流程發生的時間順序逐筆登記，逐日反映庫存現金或者銀行賬戶存款的收支余情況。由于現金日記賬和銀行日記賬的記賬方式完全相同，只是體現的資金類型不同，因此本項目僅以現金日記賬為例介紹通過Excel設計日記賬的兩種方法，一種是通過表對象和數據透視表完成設計，另一種是通過函數和公式的設定完成現金日記賬的功能。通過本項目的學習，學習者可以了解利用Excel進行財務設計的思路和方法。情景引入簡易現金日記賬1賬頁式現金日記賬2目錄CONTENTS任務一簡易現金日記賬知識準備Excel本身不是一種結構嚴謹的數據庫，只是它的格式和數據庫中的數據表太像了，以至于用戶常常期待可以通過Excel實現某些只有數據庫才具有的功能。Excel中進行數據的計算有兩種基本的思路，一種是使用數據透視表，另一種是使用函數。數據透視表是Excel中功能最為強大的工具，具有分析數據快速、簡單的特點，尤其對于大數據量的分析計算，效率很高。本任務的內容就是按照數據透視表的思路對現金日記賬的余額進行計算。數據透視表是利用Excel進行會計電算化設計的常用方法。任務目標（1）設計并完成福源公司2022年1月和2月現金日記賬；（2）借方發生額、貸方發生額和余額數據必須保留兩位小數，并按照“會計專用”格式顯示；（3）按日計算每日余額，按月分析每月的收支情況。任務資料福源公司在2022年1月和2月發生了如下和現金有關的業務：(1)1月1日轉入上年余額2200元。(2)1月3日支付差旅費320元。(3)1月5日支付12月電費550元。(4)1月5日提現金25000元。(5)1月6日支付招待費3500元。(6)1月6日支付差旅費2400元。(7)1月6日支付手續費35元。(8)1月14日支付辦公用品費6400元。(9)1月15日購買打印機1500元。(10)2月2日支付辦公室電話費650元。(11)2月2日出售廢舊包裝物320元。(12)2月2日支付1月電費3320元。(13)2月3日支付差旅費2500元。(14)2月7日支付綠化苗木款4000元。(15)2月7日支付招待費2200元。任務操作（1）打開Excel，將工作簿保存為“簡易日記賬.xlsx”，雙擊Sheet1工作表標簽，將Sheet1重命名為“現金日記賬”。（2）在A1:H1單元格區域內依次輸入年、月、日、編號、內容摘要、借方發生額、貸方發生額以及余額8個字段，如圖1-1所示。1.數據輸入設計圖1-1

標題行任務操作（3）選中A:D列并右擊，在彈出的快捷菜單中選擇“設置單元格格式”命令，在打開的“設置單元格格式”對話框中選擇“數字”標簽。在“分類”列表框中選擇類型為“數值”，選擇“小數位數”為0，如圖1-2所示。單擊“確定”按鈕，完成A列到D列的年、月、日以及編號的數據類型設置。1.數據輸入設計圖1-2設置年月日以及編號的數據類型任務操作（4）選中E列并右擊，在彈出的快捷菜單中選擇“設置單元格格式”命令，如圖1-3所示。從打開的“設置單元格格式”對話框中選擇“數字”標簽，在“分類”列表框中選擇類型為“文本”，完成對E列內容摘要的數據類型的設置。1.數據輸入設計圖1-3選擇“設置單元格格式”命令任務操作（5）選中F:H列并右擊，在彈出的快捷菜單中選擇“設置單元格格式”命令，如圖1-3所示。從打開的“設置單元格格式”對話框中選擇“數字”標簽，在“分類”列表框中選擇類型為“會計專用”，“小數位數”設置為2，“貨幣符號”設置為無，完成對F列到H列借貸方發生額以及余額的數據類型設置。1.數據輸入設計任務操作（1）選中D2單元格，在編輯欄內輸入公式“=ROW()-1”，完成“編號”字段的設置。【提示】：編號和行號的關系就是當前記錄所在的行號減去1，計算當前記錄所在行在Excel中使用ROW函數。（2）選中H2單元格，在編輯欄內輸入公式“=F2”，完成第1條記錄余額的計算。（3）從A2單元格開始，輸入第1條記錄的其余部分。輸入的第1條記錄如圖1-4所示。2.創建公式圖1-4第1條記錄任務操作（4）選中A3單元格，在編輯欄內輸入公式“=2022”，接著輸入第2條記錄，其中D3單元格中輸入公式“=ROW()-1”，H3單元格中輸入公式“=H2+F3-G3”。輸入的第2條記錄如圖1-5所示。2.創建公式圖1-5第2條記錄任務操作（5）選中A1單元格，選擇“插入”選項卡，執行“表格”組中的“表格”命令，如圖1-6所示。打開如圖1-7所示的“創建表”對話框，選中“表包含標題”復選框，單擊“確定”按鈕，將單元格區域轉化為表。2.創建公式圖1-6“表格”命令圖1-7“創建表”對話框任務操作（6）選中A1單元格，選擇“表設計”選項卡，在“表格樣式選項”組中選擇“白色，表樣式淺色15”，如圖1-8所示。2.創建公式圖1-8“表格樣式”組任務操作（7）選擇“表設計”選項卡，在“屬性”組中將表名稱由默認的“表1”更改為“現金日記賬”，完成表格名稱的設置，如圖1-9所示。2.創建公式圖1-9設置表對象名稱任務操作（8）繼續輸入剩余的記錄，最終結果如圖1-10所示。2.創建公式圖1-10輸入完成后的現金日記賬表格任務操作（1）選擇“現金日記賬”工作表的A1單元格，選擇“插入”選項卡，執行“表格”組中的“數據透視表”命令，如圖1-11所示，打開“創建數據透視表”對話框。3.創建公式圖1-11“數據透視表”命令任務操作（2）選中“選擇一個表或區域”單選按鈕，已經可以自動識別要進行數據透視的區域。如果未能識別，則在“表/區域”后輸入“現金日記賬”。選擇放置數據透視表的位置為“新工作表”，如圖1-12所示，完成數據透視表數據源的設置。3.創建公式圖1-12設置數據透視表數據源任務操作（3）按照上述步驟打開如圖1-13所示的“數據透視表字段”對話框，將“月”字段和“日”字段拖動到“行”中，將“借方發生額”和“貸方發生額”字段拖動到“值”中，完成要顯示的字段的設置。3.創建公式圖1-13“數據透視表字段”對話框任務操作（4）單擊“數據透視表字段”對話框的“值”內的“求和項:借方發生額”字段，選擇“值字段設置”命令，打開如圖1-14所示的“值字段設置”對話框。單擊“值字段設置”對話框中的“數字格式”按鈕，打開“設置單元格格式”對話框，選擇“數字”標簽，在“分類”列表框中選擇類型為“會計專用”，“小數位數”設置為2，“貨幣符號”設置為無，單擊“確定”按鈕，完成字段計算類型和數字的設置。3.創建公式圖1-14“值字段設置”對話框任務操作（5）單擊“數據透視表字段”對話框的“值”內的“求和項:貸方發生額”字段，選擇“值字段設置”命令，打開“值字段設置”對話框。單擊“值字段設置”對話框中的“數字格式”按鈕，打開“設置單元格格式”對話框，選擇“數字”標簽，在“分類”列表框中選擇類型為“會計專用”，“小數位數”設置為2，“貨幣符號”設置為無，單擊“確定”按鈕，完成字段計算類型和數字的設置。3.創建公式任務操作（6）選中數據透視表中任意一個單元格，選擇“分析”選項卡，執行“計算”組中的“字段、項目和集”下的“計算字段”命令(如圖1-15所示)，打開如圖1-16所示的“插入計算字段”對話框。3.創建公式圖1-15“字段、項目和集”下的“計算字段”命令圖1-16“插入計算字段”對話框（7）將“名稱”文本框中的“字段1”改為“余額總計”。（8）將“公式”文本框中的“0”刪除，選擇“字段”列表框中的“借方發生額”，單擊“插入字段”按鈕，再輸入減號，然后選擇“字段”列表框中的“貸方發生額”，單擊“插入字段”按鈕。單擊“確定”按鈕，完成插入計算字段的操作。任務操作3.創建公式任務操作（9）最終結果如圖1-17所示。3.創建公式圖1-17按月和日分類匯總的數據透視表任務二賬頁式現金日記賬知識準備COUNTIF函數的功能是統計在指定范圍內符合統計條件的單元格的個數。COUNTIF函數包括Range和Criteria兩個參數，完整的格式為COUNTIF(Range,Criteria)。Range:表示參與統計的單元格區域。Criteria：表示統計的條件。1.COUNTIF函數知識準備SUBTOTAL函數對選定區域進行分類匯總，其完整的格式為SUBTOTAL(Function_num,Ref1,Ref2,…)。Function_num：本示例參數的值為9，表示分類求和。Ref1,Ref2,…：表示分類計算的區域引用。在本示例中引用的是F6:F11單元格區域，如果在第6行和第11行之間插入空行，則該公式所引用的單元格區域會自動發生變化。2.SUBTOTAL函數任務目標（1）完成賬頁式現金日記賬頁面設計；（2）在賬頁式現金日記賬中創建公式；（3）完成福源公司在2022年1月1日后發生的現金有關的業務。任務資料福源公司在2022年1月1日后發生了如下和現金有關的業務：(1)1月1日轉入上年余額2200元。(2)1月3日支付差旅費320元。(3)1月5日支付12月電費550元。(4)1月6日支付招待費3500元。(5)1月6日支付差旅費2400元。(6)1月6日支付手續費35元。(7)1月7日提現金25000元。(8)1月14日支付辦公用品費用6400元。任務資料(9)1月15日購買打印機1500元。(10)2月2日支付辦公室電話費650元。(11)2月2日支付1月電費3320元。(12)2月3日出售廢舊包裝物320元。(13)2月3日支付差旅費2500元。(14)2月7日支付綠化苗木款4000元。(15)2月7日支付招待費2200元。福源公司在2022年1月1日后發生了如下和現金有關的業務：任務操作（1）打開Excel，將文件保存為“賬頁式現金日記賬.xlsx”，選中Sheet1，重命名為“現金日記賬”。（2）在工作表內輸入如圖1-18所示的現金日記賬界面，完成界面的設置，如圖1-18所示。1.繪制界面圖1-18完成后的界面任務操作(3)選中F、G和I列并右擊，從彈出的快捷菜單中選擇“設置單元格格式”命令，如圖1-19所示。1.繪制界面圖1-19選擇“設置單元格格式”命令任務操作（4）打開“設置單元格格式”對話框，選擇“數字”標簽，在“分類”列表框中選擇類型為“會計專用”，“小數位數”設置為2，“貨幣符號”設置為無，單擊“確定”按鈕，完成對指定列的數字格式的設置，如圖1-20所示。1.繪制界面圖1-20“設置單元格格式”對話框任務操作（1）選中I5單元格，在編輯欄內輸入公式“=F5”；選中H5單元格，輸入“借”字，完成現金初始余額的計算。（2）選中B14單元格，在編輯欄內輸入“=COUNTIF()”，將光標移至括號內，單擊“插入函數”按鈕，如圖1-21所示。2.創建公式圖1-21單擊“插入函數”按鈕任務操作打開如圖1-22所示的“函數參數”對話框，在Range參數后輸入“$E$6:E15”，在Criteria參數后輸入“本月合計”，確定本月月份。2.創建公式圖1-22COUNTIF函數任務操作【提示】這一步是利用COUNTIF函數自動計算月份，計算月份的原理就在于統計指定的單元格范圍內“本月合計”單元格出現的次數。假定現金日記賬是從1月份開始記賬，那么很顯然“本月合計”在1月底時的出現次數為1，就表示1月份；在2月份底時的出現次數為2，就表示2月份。依此類推，通過“本月合計”出現的次數就可以知道正在匯總的月份為幾月。本例中使用“$E$6”，用絕對引用的方式則是表示今后統計時，總是將指定的E6單元格作為初始位置開始統計。2.創建公式任務操作

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

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

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

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

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

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

輸入完成的第1張憑證(1)新建工作表，將工作表重命名為“憑證庫”，建立“憑證庫”工作表。(2)從A1單元格位置開始輸入id、憑證id、摘要、科目代碼、總賬科目、明細科目、現金流量類型、借金額、貸金額、類型代碼、年、月、日、憑證號、筆號、附件數、性質和審核等字段，完成憑證庫所需字段的輸入。(3)選中A1單元格，選擇“插入”選項卡，執行“表格”組內的“表格”命令，將指定的單元格區域轉化為組。選擇“設計”選項卡，將“屬性”組內的表對象名稱更改為“pzk”，完成“憑證庫”工作表的設計。任務操作3.導入憑證庫(4)選中B2單元格，在B2單元格內輸入字母“a”，完成首行數據的輸入。打開“憑證輸入”工作表，在憑證界面中輸入第一張憑證，如圖2-19所示。當I列沒有錯誤提示，并且借貸方沒有紅色顯示時，說明該憑證在結構上是正確的，就可以通過錄制一個宏將數據導入憑證庫工作表中。任務操作3.導入憑證庫圖2-19

輸入完成的第一張憑證【提示】使用Excel導入數據的應用技巧：如果向一張空表中傳入數據，那么為了不發生錯誤，至少要有一行數據。首次導入時，由于表格中沒有數據，因此要為某個字段添加數據，表示該表中現在已經存在一行數據；如果表對象中已經有數據，就無須這一行，在導入第一張憑證后，該行數據將會被手動刪除。任務操作3.導入憑證庫輸入內容前的憑證庫如圖2-20所示。任務操作3.導入憑證庫圖2-20

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

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

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

“選擇性粘貼”對話框圖2-24

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

向憑證庫中導入第一條記錄(1)選擇“憑證輸入”工作表，選擇“開發工具”選項卡，執行“代碼”組內的“錄制宏”命令，打開“錄制宏”對話框，將宏名稱更改為“獲取最新憑單號”，單擊“確定”按鈕，開始錄制宏。(2)選中C2單元格，在編輯欄內輸入公式“=MAX(IF(pzk[年]&″-″&pzk[月]=YEAR(E2)&″-″&MONTH(E2),pzk[憑證號]),0)+1”，按Ctrl+Shift+Enter鍵，獲取憑證庫中指定月份最新的憑單號。(3)選擇“開發工具”選項卡，執行“代碼組”中的“停止錄制”命令，完成宏的錄制。(4)在“獲取最新憑單號”按鈕上右擊，在彈出的快捷菜單中選擇“指定宏”命令，打開“指定宏”對話框，選擇“獲取最新憑單號”選項，單擊“確定”按鈕，完成指定宏的設置。任務操作5.錄制宏(1)選中“憑證庫”工作表，選中第2行，右擊，在彈出的快捷菜單中選擇“刪除”命令，刪除第1行記錄。(2)選中A2單元格，在編輯欄內輸入“=[@年]&″-″&[@月]&″-″&[@憑證號]&″-″&[@筆號]”，按Enter鍵確認，完成id字段的設置。(3)選中J2單元格，在編輯欄內輸入公式“=IFERROR(VLOOKUP([@現金流量類型],xjll,2,FAL