CH3 (p51) - 運用Excel輕鬆處理會計帳務及製作財務報表

第三章 利用 Excel 編製財務報表

3-1 會計常用的 EXCEL函數之 功能及應用

3-2 會計項目表 EXCEL 的編製

3-3 日記簿 EXCEL 的編製

3-4 分類帳 EXCEL 的編製

3-5 單月(累計)試算表 EXCEL 的編製

3-6 單月(累計)損益表 EXCEL 的編製

3-7 部門別(產品別)損益表 EXCEL 的編製

3-8 資產負債表 EXCEL 的編製

3-9 傳票 EXCEL 的編製

3-10 驗證

3-1 會計常用的 EXCEL 函數之功能及應用

3-1、會計常用的 EXCEL函數之功能及應用

CONCATENATE(串連、連接)

=CONCATENATE(text1，text2……) =CONCATENATE(儲存格範圍，儲存格範圍……)

3-1、會計常用的 EXCEL函數之功能及應用

LEFT

功能：抓取連續的文字(從左邊第一個字開始) =LEFT(儲存格範圍，字數)

3-1、會計常用的 EXCEL函數之功能及應用

IF

=IF(項目是 True，則執行該項目，相反則執行其他項目)

3-1、會計常用的 EXCEL函數之功能及應用

VLOOKUP

功能：從上而下尋找資料，並帶出相關的記錄。 =VLOOKUP(想要查閱的值, 想要查閱值的範圍, 範圍中包含傳回值的欄號, 完全符合或大約符合 (以 0/FALSE 或 1/TRUE 指示))

3-1、會計常用的 EXCEL函數之功能及應用

ISNA

本課重點

(1) CONCATENATE ：串連、連接

(2) LEFT：由左抓取連續的文字

(3) IF：項目是TRUE，則執行該項目， 相反則執行其他項目

本課重點

(4) VLOOKUP：從左轉往右尋找資料， 並帶出相關的記錄

(5) ISNA：如果回傳的值是N/A，則顯示為0

3-2 會計項目表 EXCEL 的編製

3-2、會計項目表 EXCEL 的編製

會計項目來自於財政部臺北國稅局110年度營利事業所得稅申報書表格式 首頁 > 分稅導覽 > 營所稅 > 「110年度營利事業所得稅申報書格式(含租減、關係人交易及跨國企業集團成員揭露資料、其他申報書表、國別報告、集團主檔報告封面、合併、機關或團體、清算)」

本課重點

(1) 本課程使用之會計項目表以國稅局營利事業 所得稅申報書之損益表及資產負債表為範本。

(2) 每一年會有新修正，可於每年確認是否修改。

(3) 在用EXCEL編製財報時，會提醒大家如何 修改或增減。

3-3、日記簿 EXCEL 的編製

STEP1: 輸入公司名稱: ______股份有限公司

STEP2: 定義「公司名稱」

3-3、日記簿 EXCEL 的編製

STEP3： F5利用函數 CONCATENATE 將D5及E5合併。 於F5欄位輸入=CONCATENATE(D5,E5)

3-3、日記簿 EXCEL 的編製

STEP4: G欄建立「產品別」下拉選單;請先定義「產品別」

3-3、日記簿 EXCEL 的編製

3-3、日記簿 EXCEL 的編製

STEP5： 將G9:G16分類為B產品; 將G30:G35分類為A產品; 其餘分類為總公司

3-3、日記簿 EXCEL 的編製

STEP6： H欄建立「項目代號」下拉選單; 請先定義「項目代號」

3-3、日記簿 EXCEL 的編製

STEP6： H欄建立「項目代號」下拉選單; 請先定義「項目代號」

3-3、日記簿 EXCEL 的編製

STEP7: 建立「項目名稱」的查表功能; 請先定義「會計項目」 (別忘了往下複製)

3-3、日記簿 EXCEL 的編製

STEP8: 於I5欄位輸入 =IF(H5="","",VLOOKUP(H5,會計項目,2,FALSE))

3-3、日記簿 EXCEL 的編製

3-3、日記簿 EXCEL 的編製

STEP9:檢視借貸是否平衡 於M3欄位輸入=IF(SUM(K:K)=SUM(L:L),"借貸平衡","借貸不平衡")

3-3、日記簿 EXCEL 的編製

EXCEL編製回顧

(1) 定義「公司名稱」

(2) 用CONCATENATE合併傳票號碼及序號為序碼

(3) 製作產品別及項目代號之下拉式選單

EXCEL編製回顧

(4) 利用VLOOKUP，只要輸入項目代號， 即可找出項目名稱

(5) 驗證借方金額及貸方金額是否平衡

3-4 分類帳 Excel 的編製

3-4、分類帳 EXCEL 的編製

STEP1: 插入-樞紐分析表-選取範圍

3-4、分類帳 EXCEL 的編製

STEP2: 分類帳是以「項目代號」為篩選

3-4、分類帳 EXCEL 的編製

STEP3: 將各列展開 分析-選項-顯示- 古典樞紐分析表版面配置

3-4、分類帳 EXCEL 的編製

STEP3: 將各列展開 分析-選項-顯示- 古典樞紐分析表版面配置

3-4、分類帳 EXCEL 的編製

STEP4: 將工作表重新命名為 「分類帳」

EXCEL編製回顧

(1) 利用樞紐分析表製作分類帳

(2) 以項目代號為篩選，例如選項目代號1111， 即可知現金之增減變化，了解現金從何而 來，花到哪裡去

(3) 老闆可快速了解各資產之增減狀況

(4) 分類帳亦是國稅局查帳時必要提供之查帳 書表之一

3-5 單月（累計）試算表 Excel 的編製

3-5、單月(累計)試算表的編製

STEP1: 插入-樞紐分析表-選取範圍

3-5、單月(累計)試算表的編製

STEP2: 試算表是以「月」、 「產品別」為篩選

3-5、單月(累計)試算表的編製

STEP2: 試算表是以「月」、 「產品別」為篩選

3-5、單月(累計)試算表的編製

STEP3: 新增借方總額、貸方總額 借方總額 「=IF(借方金額>貸方金額,借方金額-貸方金額,0)」 貸方總額 「=IF(貸方金額>借方金額,貸方金額-借方金額,0)」

3-5、單月(累計)試算表的編製

STEP4: 複製另一個試算表，各命名為「單月試算表」 「單月產品別試算表」 「累計試算表」

3-5、單月(累計)試算表的編製

STEP4: 複製另一個試算表，各命名為「單月試算表」 「單月產品別試算表」 「累計試算表」

3-5、單月(累計)試算表的編製

STEP5: 定義 「單月試算表」 「單月產品別試算表」 「累計試算表」

3-5、單月(累計)試算表的編製

STEP5: 定義 「單月試算表」 「單月產品別試算表」 「累計試算表」

EXCEL編製回顧

(1) 利用樞紐分析表製作試算表

(2) 試算表可以「月」、「季」、「年」 可了解各期間之損益狀況

(3) 快速知道各項目之餘額

3-6 單月累計損益表 Excel 的編製

3-6、單月損益表 EXCEL 的編製

STEP1: A1欄位輸入 =公司名稱

STEP2: A3欄位=單月試算表!B1&"月損益表"

STEP3: C6欄位輸入=IF(ISNA(VLOOKUP(B6,單月試算表,4,FALSE)),0,IF(VLOOKUP(B6,單月試算表,4,FALSE)>0,VLOOKUP(B6, 單月試算表,4,FALSE),VLOOKUP(B6,單月試算表,5,FALSE)))

3-6、單月損益表 EXCEL 的編製

=單月試算表!B1&"月損益表"

3-6、單月損益表 EXCEL 的編製

STEP4:D9欄位輸入 =C6-C7-C8

STEP5:D10欄位輸入 =C10

STEP6:D11欄位輸入 =D9-D10

STEP7:D36欄位輸入 =SUM(C13:C35)

STEP8:D37欄位輸入 =D11-D36

STEP10:D55欄位輸入 =SUM(C49:C54)

STEP9:D47欄位輸入 =SUM(C39:C46)

STEP11:D56欄位輸入 =D37+D47- D55

STEP12:D57欄位輸入 =C57

STEP13:D58欄位輸入 =D56-D57

3-6、單月損益表 EXCEL 的編製

3-6、單月損益表 EXCEL 的編製

3-6、單月損益表 EXCEL 的編製

3-6、累計損益表 EXCEL 的編製

STEP1: A1欄位輸入 =公司名稱

STEP2: C6欄位輸入=IF(ISNA(VLOOKUP(B6,累計試算 表,4,FALSE)),0,IF(VLOOKUP(B6,累計試算表,4, FALSE)>0,VLOOKUP(B6,累計試算表,4,FALSE), VLOOKUP(B6,累計試算表,5,FALSE)))

STEP3: D58欄位稅後淨利定義「本期損益」

3-6、累計損益表 EXCEL 的編製

STEP4: D9欄位輸入 =C6-C7-C8

STEP5: D10欄位輸入 =C10

STEP6: D11欄位輸入 =D9-D10

STEP7: D36欄位輸入 =SUM(C13:C35)

STEP8: D37欄位輸入 =D11-D36

STEP9: D47欄位輸入 =SUM(C39:C46)

STEP10: D55欄位輸入 =SUM(C49:C54)

STEP11: D56欄位輸入 =D37+D47- D55

STEP12: D57欄位輸入 =C57

STEP13: D58欄位輸入 =D56-D57

3-6、累計損益表 EXCEL 的編製

EXCEL編製回顧

(1) 損益表之格式以國稅局營利事業所得稅申 報書之項目為範本，亦可增減

(2) 單月(累計)損益表以單月(累計)試算表之 金額為依據，只要在單月(累計)試算表設 定後，則單月(累計)損益表即可快速產出

3-7 單月部門別(產品別)損益表 Excel 的編製

3-7、單月部門別(產品別)損益表 EXCEL 的編製

3-7、單月部門別(產品別)損益表 EXCEL 的編製

STEP1: A1欄位輸入 =公司名稱

STEP2: A3欄位輸入=單月產品別試算表!B1&"月"&單月產品 別試算表!B2&"損益表"

STEP3: C6欄位輸入=IF(ISNA(VLOOKUP(B6,單月產品別試算 表,4,FALSE)),0,IF(VLOOKUP(B6,單月產品別試算表,4, FALSE)>0,VLOOKUP(B6,單月產品別試算表,4,FALSE), VLOOKUP(B6,單月產品別試算表,5,FALSE)))

3-7、單月部門別(產品別)損益表 EXCEL 的編製

=單月產品別試算表!B1&"月"&單月產品別試算表!B2&"損益表"

B產品

3-7、單月部門別(產品別)損益表 EXCEL 的編製

STEP4:D9欄位輸入 =C6-C7-C8

STEP5:D10欄位輸入 =C10

STEP6:D11欄位輸入 =D9-D10

STEP7:D36欄位輸入 =SUM(C13:C35)

STEP8:D37欄位輸入 =D11-D36

STEP9:D47欄位輸入 =SUM(C39:C46)

STEP10:D55欄位輸入 =SUM(C49:C54)

STEP11:D56欄位輸入 =D37+D47- D55

STEP12:D57欄位輸入 =C57

STEP13:D58欄位輸入 =D56-D57

3-7、部門別(產品別)損益表 EXCEL 的編製

EXCEL編製回顧

(1) 以單月產品別試算表之金額為依據，只要在單月產 品別試算表設定後，則單月產品別損益表即可快速 產出

(2) 產品別之設定亦可以客戶別、區域別、業務別等， 可看出該客戶、該區城及各業務人員對公司獲利之 貢獻度

3-8 資產負債表 Excel 的編製

3-8、資產負債表 EXCEL 的編製

3-8、資產負債表 EXCEL 的編製

STEP1: A1欄位輸入 =公司名稱

STEP2: D5欄位輸入(資產) =IF(ISNA(VLOOKUP(C5,累計試算表,4,FALSE)), 0,IF(VLOOKUP(C5,累計試算表,4,FALSE)>0,VLOOKUP(C5,累計試 算表,4,FALSE),-VLOOKUP(C5,累計試算表,5,FALSE)))

STEP3: D91欄位輸入(負債及權益) =IF(ISNA(VLOOKUP(C91,累計試算表，4,FALSE)),0,IF(VLOOKUP(C91,累計試算表,4,FALSE)>0,-VLOOKUP(C91,累計試算表,4,FALSE),VLOOKUP(C91,累計試算表，5,FALSE)))

3-8、資產負債表 EXCEL 的編製

STEP4: D143欄位輸入 =本期損益

3-8、資產負債表 EXCEL 的編製

STEP5: E89欄位輸入 =SUM(D5:D88)

STEP6: E130欄位輸入 =SUM(D91:D129)

STEP7: E153欄位輸入 =SUM(D131:D152)

STEP8: E154欄位輸入 =E130+E153

3-8、資產負債表 EXCEL 的編製

STEP9: 定義「資產總額」、「負債總額」、「權益總額」

3-8、資產負債表 EXCEL 的編製

STEP10: 檢視借貸是否平衡，在G3欄位輸入 =IF(資產總額=負債總額+權益總額,"平衡","不平衡")

EXCEL編製回顧

(1) 資產負債表之格式以國稅局營利事業所得稅申報書之 項目為範本，亦可增減

(2) 資產負債表以累計試算表之金額為依據，只要在累計 試算表設定後，則資產負債表即可快速產出

3-9 傳票 Excel 的編製

3-9、傳票 EXCEL 的編製

STEP1: F3欄位輸入傳票號碼

3-9、傳票 EXCEL 的編製

STEP2: 傳票日期係取傳票號碼左邊算 起7碼B3欄位輸入 =LEFT(F3,7)

3-9、傳票 EXCEL 的編製

STEP3: 定義「傳票」

3-9、傳票 EXCEL 的編製

STEP4: 於以下欄位輸入A5欄位輸入 =IF(ISNA(VLOOKUP (CONCATENATE($F$3,1),傳票,1,FALSE)),"", CONCATENATE($F$3,1)) CONCATENATE($F$3,1)，此代表傳票號碼+序號 A5中CONCATENATE($F$3,1)，代表11101010011 A6中CONCATENATE($F$3,2)，代表11101010012 A7中CONCATENATE($F$3,3)，代表11101010013 A8中CONCATENATE($F$3,4)，代表11101010014

3-9、傳票 EXCEL 的編製

STEP4: 於以下欄位輸入A5欄位輸入 =IF(ISNA(VLOOKUP (CONCATENATE($F$3,1),傳票,1,FALSE)),"", CONCATENATE($F$3,1)) A9中CONCATENATE($F$3,5)，代表11101010015 A10中CONCATENATE($F$3,6)，代表11101010016 A11中CONCATENATE($F$3,7)，代表11101010017 A12中CONCATENATE($F$3,8)，代表11101010018 A13中CONCATENATE($F$3,9)，代表11101010019

3-9、傳票 EXCEL 的編製

STEP5: 於以下欄位輸入 B5欄位(產品別)輸入 =IF(ISNA(VLOOKUP($A5,傳 票,2,FALSE)),"",VLOOKUP($A5,傳票,2,FALSE)) C5欄位(項目代號)輸入 =IF(ISNA(VLOOKUP($A5,傳 票,3,FALSE)),"",VLOOKUP($A5,傳票,3,FALSE)) D5欄位(項目名稱)輸入 =IF(ISNA(VLOOKUP($A5,傳 票,4,FALSE)),"",VLOOKUP($A5,傳票,4,FALSE)) E5欄位(摘要)輸入 =IF(ISNA(VLOOKUP($A5,傳票,5, FALSE)),"",VLOOKUP($A5,傳票,5,FALSE))

3-9、傳票 EXCEL 的編製

STEP5: 於以下欄位輸入 F5欄位(借方金額)輸入 =IF(ISNA(VLOOKUP($A5,傳 票,6,FALSE)),"",VLOOKUP($A5,傳票,6,FALSE)) G5欄位(貸方金額)輸入 =IF(ISNA(VLOOKUP($A5,傳 票,7,FALSE)),"",VLOOKUP($A5,傳票,7,FALSE))

3-9、傳票 EXCEL 的編製

3-9、傳票 EXCEL 的編製

STEP6: 隱藏「零」值 檔案->選項->進階

3-9、傳票 EXCEL 的編製

EXCEL編製回顧

(1) 傳票的功能，將每一筆分錄，後面可黏貼原始憑證， 可供日後備查，亦可用電子傳票及憑證。

(2) 傳票上只有傳票號碼可供輸入，其餘欄位已設好公 式。

3-10 驗證

3-10、驗證

日記簿驗證 借方金額 = 貸方金額

3-10、驗證

資產負債表驗證 資產 = 負債 + 權益

EXCEL編製回顧

(1) 在日常日記簿之登載尤其重要，因為所以報表皆 由日記簿之內容產出。

(2) 借貸平衡驗證，每一筆分錄必定要借貸平衡才可 以再往下記錄，若有借貸不平衡，必須先找出不 平衡之原因。

(3) 日記簿完成後，可檢視資產負債表是否資產 = 負債+權益。

3-11 第二年開帳操作

同學：一個 EXCEL 表裡面，可不可以把多年的帳 累積在一起？ 老師：可以，但 EXCEL的運算會越來越慢 。 為避免 這個問題發生或是打不開 EXCEL，建議每一 個年度做個區分 。

如何在每一個年度做區分？ 當我們完成111年度，要開始做第二年的開帳

問題 ：我們在111 年度編了損益表和資產負債表， 哪一張表會陪著公司往下走？ 解釋：損益表在111年度一結束就會被結清，第二 年度會有新的損益表。資產負債表會陪著 公司往下走。

資產總額 = 負債總額 + 權益總額