Excel张财会应用技巧探素课件

1、Excel 於財務會計的應用第1页，共181页。三個範例收入與費用管理應收帳款管理及分析庫存帳務管理C. W. Cheng2第2页，共181页。收入與費用管理第3页，共181页。兩個工作表收入費用與統計分析工作表交叉統計資料交叉分析圖表收入費用交叉分析工作表顯示分析圖表C. W. Cheng4第4页，共181页。收入費用與統計分析工作表C. W. Cheng5第5页，共181页。收入費用統計分析工作表目標一依據月份及收入費用項目得到統計狀況直接顯示該月的盈虧目標二以圖表顯示每月的收入與費用C. W. Cheng6第6页，共181页。目標一:依據月份及收入費用作統計利用手繪格線設計工作表外觀設定

2、各個欄位資料型態C. W. Cheng7第7页，共181页。手繪格線Alt + Enter換行使用手繪格線C. W. Cheng8第8页，共181页。設定儲存格格式C. W. Cheng9第9页，共181页。計算儲存格的值(1)選擇工具列的插入函數圖示C. W. Cheng10第10页，共181页。計算儲存格的值(2)選擇SUM函數C. W. Cheng11第11页，共181页。計算儲存格的值(3)點選這裡,選取要加總的範圍C. W. Cheng12第12页，共181页。計算儲存格的值(4)C. W. Cheng13第13页，共181页。計算儲存格的值(5)C. W. Cheng14第14页，

3、共181页。練習請完成費用小計與金額小計費用小計=營業費用+其他費用各項金額小計=各項金額16月的總額C. W. Cheng15第15页，共181页。目標一:直接顯示該月的盈虧盈虧金額=收入小計費用小計直接輸入計算式Ex: C13=C7-C11C. W. Cheng16第16页，共181页。目標二:圖表顯示每月的收入及費用圖表區背景牆C. W. Cheng17第17页，共181页。步驟一:插入圖表選擇插入圖表選擇圖表類型C. W. Cheng18第18页，共181页。步驟二:選擇資料來源(1)點選這裡以選取資料範圍C. W. Cheng19第19页，共181页。選擇資料來源(2)先選取收入小計

4、部分，按住Ctrl鍵不放,再選取費用小計2C. W. Cheng20第20页，共181页。更改數列名稱C. W. Cheng21第21页，共181页。選擇X軸的類別名稱C. W. Cheng22第22页，共181页。將圖表插入目前的工作表C. W. Cheng23第23页，共181页。步驟三:選擇選擇圖表區與背景牆格式(1)C. W. Cheng24第24页，共181页。選擇選擇圖表區與背景牆格式(2)C. W. Cheng25第25页，共181页。收入費用交叉分析工作表C. W. Cheng26第26页，共181页。欄寬自動調整C. W. Cheng27第27页，共181页。用AVERAGE

5、()函數計算平均值AVERAGE(, ,),是1到30個數字計算所有輸入數字的平均值(算術平均數)C. W. Cheng28第28页，共181页。計算平均值(1)使用功能表的插入函數C. W. Cheng29第29页，共181页。計算平均值(2)資料來源範圍選擇鈕C. W. Cheng30第30页，共181页。計算平均值(3)選取要計算的工作表與資料範圍C. W. Cheng31第31页，共181页。計算平均值(4)返回函數設定視窗預覽結果C. W. Cheng32第32页，共181页。平均每月盈虧金額平均每月盈虧金額= 平均每月收入-平均每月費用= B1-B2C. W. Cheng33第33

6、页，共181页。平均每月費用占收入比率(1)計算公式:若收入不為0, 則比率=平均費用/平均收入若收入為 0, 則比率=0IF(B10, B2/B1, 0)IF(B1=0,0,B2/B1)C. W. Cheng34第34页，共181页。平均每月費用占收入比率(2)C. W. Cheng35第35页，共181页。設定儲存格圖樣選一個顏色C. W. Cheng36第36页，共181页。設定儲存格外框同時選取外框和內線C. W. Cheng37第37页，共181页。選擇儲存格格式C. W. Cheng38第38页，共181页。練習插入平均每月收入與費用的圖表C. W. Cheng39第39页，共18

7、1页。應收帳款管理及分析第40页，共181页。應收帳款處理流程應收帳款產生登錄該筆應收帳款製財務帳冊(包含: 銷售項目,銷售數量,銷售金額,發票開立與否,發票開立日期,預定收款日期,收款方式)定時追蹤是否已到預定收款日期?執行收款,收到款項後,財務人員在帳冊加注已收到帳款,並註明日期轉會計作業C. W. Cheng41第41页，共181页。三個工作表將應收帳款管理結構分為三個部份處理應收帳款統計工作表應收帳款帳齡分析工作表帳齡分布圖表工作表輔助工作表資料項目定義工作表C. W. Cheng42第42页，共181页。應收帳款統計工作表C. W. Cheng43第43页，共181页。應收帳款統計工

8、作表目標一熟悉儲存格格式的應用目標二登錄客戶編號時，相關欄位內容自動顯示目標三自動算出銷售實價目標四利用應收帳款統計的資料，完成應收帳款帳齡分析與帳齡統計圖表C. W. Cheng44第44页，共181页。目標一:熟悉儲存格格式的應用對各個欄位資料的特性設定儲存格格式交易日期:日期銷售數量:數值產品單價:貨幣實際收款日期:日期C. W. Cheng45第45页，共181页。設定儲存格格式C. W. Cheng46第46页，共181页。目標二：自動顯示相關欄位內容建立儲存格名稱單一行或列儲存格名稱多行或列儲存格名稱運用選項欄位以線上查詢與選擇的功能免除重複輸入C. W. Cheng47第47页，

9、共181页。建立儲存格名稱(1)選取要命名的儲存格C. W. Cheng48第48页，共181页。建立儲存格名稱(2)C. W. Cheng49第49页，共181页。建立儲存格名稱(3)C. W. Cheng50第50页，共181页。建立儲存格名稱(4)在應收帳款統計工作表建立下列名稱設定客戶編號銷售數量產品單價VIP折扣銷售實價是否含稅未稅金額稅金結帳方式C. W. Cheng51第51页，共181页。建立儲存格名稱(5)選取要命名的儲存格C. W. Cheng52第52页，共181页。建立儲存格名稱(6)C. W. Cheng53第53页，共181页。建立儲存格名稱(7)C. W. Che

10、ng54第54页，共181页。建立儲存格名稱(8)再資料項目定義工作表建立下列名稱設定結帳處理方式付款方式結帳年結帳月稅別客戶C. W. Cheng55第55页，共181页。建立選項欄位(1)提供選擇的方式讓使用者選取需要的數值或資料值，屬於選項欄位。 C. W. Cheng56第56页，共181页。建立選項欄位(2)給稅別欄位一個名稱C. W. Cheng57第57页，共181页。建立選項欄位(3)以稅別為基礎,建立參考的清單C. W. Cheng58第58页，共181页。建立選項欄位(4)標題極為稅別C. W. Cheng59第59页，共181页。建立選項欄位(5)選擇要採用選取式欄位的儲

11、存格選擇儲存格 I7:I20C. W. Cheng60第60页，共181页。建立選項欄位(6)建立資料驗證：資料驗證C. W. Cheng61第61页，共181页。建立選項欄位(7)資料來源為清單C. W. Cheng62第62页，共181页。建立選項欄位(8)C. W. Cheng63第63页，共181页。建立選項欄位(9)可在欄位旁發現下拉式箭頭C. W. Cheng64第64页，共181页。練習請建立結帳方式以及付款方式的選項欄位提示先在資料項目定義工作表中找到對應的清單名稱C. W. Cheng65第65页，共181页。自動顯示相關欄位內容(1)輸入客戶編號時，自動顯示客戶名稱以及VI

12、P折扣。 C. W. Cheng66第66页，共181页。自動顯示相關欄位內容(2)利用三個應用函數:IF, TRIM, VLOOKUPIF(TRIM(客戶編號)=“”,”,VLOOKUP(客戶編號,客戶,2,FALSE)C. W. Cheng67第67页，共181页。自動顯示相關欄位內容(3)VLOOKUP(客戶編號,客戶, 2 ,FALSE)第二個欄位C. W. Cheng68第68页，共181页。自動顯示相關欄位內容(3)IF(判斷條件,條件成立的值,條件不成立的值)IF(TRIM(客戶編號)=“ ”, “ ”, VLOOKUP(客戶編號,客戶,2,FALSE)C. W. Cheng69

13、第69页，共181页。練習輸入產品名稱時,希望自動顯示產品單價提示先給資料項目定義工作表中的產品名稱與單價一個共同的名稱定義使用IF, TRIM, VLOOKUP設計公式C. W. Cheng70第70页，共181页。目標三:自動計算儲存格的值C. W. Cheng71第71页，共181页。自動計算(1)銷售實價=銷售數量產品單價VIP折扣若銷售實價已含稅未稅金額=銷售實價/(1+0.05)稅金=未稅金額0.05 (5%的營業稅率)若銷售實價未含稅未稅金額=銷售實價稅金=銷售實價0.05C. W. Cheng72第72页，共181页。自動計算(2)銷售實價=銷售數量產品單價VIP折扣C. W.

14、 Cheng73第73页，共181页。自動計算(3)銷售實價=銷售數量產品單價VIP折扣C. W. Cheng74第74页，共181页。自動計算(4)若銷售實價已含稅,未稅金額=銷售實價/(1+0.05)若銷售實價未含稅,未稅金額=銷售實價C. W. Cheng75第75页，共181页。練習請完成下列各項若銷售實價已含稅,稅金=未稅金額0.05 若銷售實價未含稅,稅金=銷售實價0.05小計=未稅金額+稅金收款日期=交易日期+結帳方式的日數C. W. Cheng76第76页，共181页。目標四:帳齡分析與圖表帳齡分析先以手動方式產生帳齡分析資料圖表設計用圖表精靈製作圓形圖C. W. Cheng7

15、7第77页，共181页。帳齡分析(1)C. W. Cheng78第78页，共181页。圖表設計(1)C. W. Cheng79第79页，共181页。圖表設計(2)選取工具列的圖表精靈圖示C. W. Cheng80第80页，共181页。圖表設計(3)1. 在圖表類型中選擇圓形圖2. 副圖表類型中選擇立體圓形圖C. W. Cheng81第81页，共181页。圖表設計(4)3.按這裡選擇資料範圍 C9:F9C. W. Cheng82第82页，共181页。圖表設計(5)C. W. Cheng83第83页，共181页。圖表設計(6)C. W. Cheng84第84页，共181页。練習請製作各個客戶總應收

16、帳款的分布比率請以立體圓形圖表示C. W. Cheng85第85页，共181页。庫存帳務管理第86页，共181页。目的對資料進行排序、篩選、小計對庫存資料進行樞紐分析C. W. Cheng87第87页，共181页。四個工作表庫存統計管理主要機制庫存統計分析報表工作表各倉庫的庫存統計各倉庫存統計工作表庫存樞紐分析庫存樞紐分析工作表輔助工作表資料項目工作表C. W. Cheng88第88页，共181页。庫存統計分析報表工作表C. W. Cheng89第89页，共181页。庫存統計分析報表工作表各欄位資料輸入倉庫別：利用線上清單選擇庫房別：利用線上清單選擇貨品編號：輸入貨品名稱：自動搜尋傳入期初存貨

17、、本期進貨、本期退貨、本期銷貨：輸入本期期末庫存：自動統計本期單價金額：自動搜尋傳入期末庫存成本金額：運算公式計算C. W. Cheng90第90页，共181页。利用線上清單選擇先對庫存項目設定工作表作插入名稱C. W. Cheng91第91页，共181页。建立儲存格名稱C. W. Cheng92第92页，共181页。建立選項資料欄位(1)建立資料驗證：資料驗證C. W. Cheng93第93页，共181页。建立選項資料欄位(2)資料來源為清單C. W. Cheng94第94页，共181页。建立選項資料欄位(3)前面加“=”C. W. Cheng95第95页，共181页。建立選項資料欄位(4)

18、用選擇的方式選取資料內容C. W. Cheng96第96页，共181页。自動搜尋貨品名稱(1)使用函數IF, TRIM, VLOOKUPIF(TRIM(貨品編號)=“ ”,” ”, VLOOKUP(貨品編號,產品,2,FALSE)C. W. Cheng97第97页，共181页。自動搜尋貨品名稱(2)C. W. Cheng98第98页，共181页。本期期末庫存本期期末庫存=期初存貨+本期存貨-本期退貨-本期銷貨C. W. Cheng99第99页，共181页。成本單價金額C. W. Cheng100第100页，共181页。期末庫存成本金額C. W. Cheng101第101页，共181页。篩選資料

19、藉由查詢與分類來過濾所需的資料選取儲存格資料篩選可依據查詢項目萃取出所需的資料項目可以做多重篩選在篩選過的資料中進行更進一步的篩選可自定篩選條件C. W. Cheng102第102页，共181页。篩選資料(1)選取A4儲存格資料篩選C. W. Cheng103第103页，共181页。篩選資料(2)只想看台北總倉的資料C. W. Cheng104第104页，共181页。篩選資料(3)只會出現台北總倉的資料C. W. Cheng105第105页，共181页。篩選資料(4)進行第二次篩選C. W. Cheng106第106页，共181页。篩選資料(5)同時滿足台北總倉與第一庫房兩項條件最後得到的結果

20、C. W. Cheng107第107页，共181页。自訂篩選條件(1)C. W. Cheng108第108页，共181页。自訂篩選條件(2)條件運算區塊篩選資料輸入區塊C. W. Cheng109第109页，共181页。自訂篩選條件(3)從下拉式選單中挑選C. W. Cheng110第110页，共181页。自訂篩選條件(4)同時篩選出台北總倉和基隆總倉的資料C. W. Cheng111第111页，共181页。排序可將資料有順序地列出使用工具列的排序鍵選擇功能表資料排序C. W. Cheng112第112页，共181页。排序(1)選擇依據哪些欄位進行排序選擇排序的方式排序範圍內的第一列是否為標題

21、列C. W. Cheng113第113页，共181页。排序(2)依據倉庫別和庫房別遞增排序C. W. Cheng114第114页，共181页。排序結果(3)C. W. Cheng115第115页，共181页。小計小計自工作表資料自動作加總、平均等計算使用者可選擇顯示或隱藏小計的明細資料列當明細資料改變時，小計的值也會自動改變C. W. Cheng116第116页，共181页。庫存成本小計(1)利用小計加總各欄位的數值資料C. W. Cheng117第117页，共181页。庫存成本小計(2)對每個倉庫做小計使用加總進行小計同時處理兩項小計每一組資料可分別顯示C. W. Cheng118第118页

22、，共181页。庫存成本小計(3)C. W. Cheng119第119页，共181页。庫存成本小計(4)此工作表中只顯示合計欄位的資料C. W. Cheng120第120页，共181页。庫存成本分析圖C. W. Cheng121第121页，共181页。庫存成本分析圖(1)C. W. Cheng122第122页，共181页。庫存成本分析圖(2)選取期末庫存成本金額C. W. Cheng123第123页，共181页。庫存成本分析圖(3)更改數列名稱C. W. Cheng124第124页，共181页。庫存成本分析圖(4)C. W. Cheng125第125页，共181页。庫存樞紐分析設計進行樞紐分析不

23、將小計資料一起統計,請先關閉小計功能C. W. Cheng126第126页，共181页。庫存樞紐分析(1)C. W. Cheng127第127页，共181页。庫存樞紐分析(2)使用整個庫存統計資料範圍樞紐分析結果放在 庫存樞紐分析統計工作表指定儲存格C. W. Cheng128第128页，共181页。庫存樞紐分析(3)設計編輯區樞紐分析表欄位選擇區樞紐分析表工具列C. W. Cheng129第129页，共181页。庫存樞紐分析(4)新增倉庫別頁面統計設定1234C. W. Cheng130第130页，共181页。庫存樞紐分析(5)依序將欄位清單中的庫房別新增至欄區域貨品編號新增至列區域本期期末

24、庫存新增至資料區域期末庫存成本金額新增至資料區域C. W. Cheng131第131页，共181页。庫存樞紐分析(6)C. W. Cheng132第132页，共181页。庫存樞紐分析(7)挑選台北總倉的庫存分析資料1變換倉庫別的統計方式2C. W. Cheng133第133页，共181页。庫存樞紐分析(8)C. W. Cheng134第134页，共181页。分割視窗方便對照資料與欄位名稱分割工作表凍結窗格C. W. Cheng135第135页，共181页。分割工作表(1)先選A5儲存格由功能表選擇視窗分割C. W. Cheng136第136页，共181页。分割工作表(2)分割線C. W. Ch

25、eng137第137页，共181页。凍結窗格(1)C. W. Cheng138第138页，共181页。凍結窗格(2)這一區的儲存格不會隨捲軸而上下移動C. W. Cheng139第139页，共181页。儲蓄存款的利潤分析第140页，共181页。年金終值與年金現值年金終值年金現值C. W. Cheng141第141页，共181页。(1) 單筆定存的複利計算計算期滿時本金與利率的總和本金固定利率C. W. Cheng142第142页，共181页。計算利息月利率=年利率/12個月當月利息=本金*月利率次月的本金=上月的本金與利息小計次月的本金C. W. Cheng143第143页，共181页。第二個

26、月的單月利息與存款小計向下拖曳填滿第四列C. W. Cheng144第144页，共181页。填滿十二個月的資料期滿領回金額C. W. Cheng145第145页，共181页。(2) 定期定額儲蓄複利計算每月初固定存入一萬元,連續12個月期滿領回金額C. W. Cheng146第146页，共181页。計算利息月利率=年利率/12個月=1.713%/12當月利息=本金*月利率次月本金=10,000+上月本金與利息小計C. W. Cheng147第147页，共181页。第二個月的當月利息與存款小計向下拖曳填滿第四列C. W. Cheng148第148页，共181页。填滿十二個月的資料12個月後可拿回

27、的總金額C. W. Cheng149第149页，共181页。(3) 使用FV函數計算未來值計算投資的未來值FV(Rate, Nper, Pmt, Pv, Type)Rate: 每一期存款利率 (月利率)Nper: 期數Pmt: 分期存款的金額Pv: 現值,現在已存的金額 (預設為0)Type:給付款項的時間是期初或期末1: 期初存款,期初付款0: 期末存款,期末付款C. W. Cheng150第150页，共181页。應用FV計算固定利率定存利潤用FV公式計算定存利潤選擇公式存款請用負號C. W. Cheng151第151页，共181页。選擇FV函數C. W. Cheng152第152页，共18

28、1页。填寫參數每一期利率(月利率)期數每期存入金額現值(目前已存金額)期初存款C. W. Cheng153第153页，共181页。(4) 目標搜尋希望兩年後有100萬存款已知道定存的利率每個月該存多少錢才能達到目標?C. W. Cheng154第154页，共181页。善加利用FV的工作表想知道的金額兩年,請改為24期目標金額設為100萬C. W. Cheng155第155页，共181页。工具目標搜尋C. W. Cheng156第156页，共181页。目標搜索對話方塊C. W. Cheng157第157页，共181页。算出每月需存入的金額每月需存入40,928元,兩年後才有100萬的存款C. W

29、. Cheng158第158页，共181页。範例二:機動利率定存的利潤分析將10萬定存一年採用機動利率計算屆時可領回多少錢?C. W. Cheng159第159页，共181页。(1)單筆定存機動利率計算以現在的機動利率值為參考單筆存款金額C. W. Cheng160第160页，共181页。計算複利本金*(機動利率/12)1本金+當月利息23次月本金=上月存款小計C. W. Cheng161第161页，共181页。填滿12個月的資料一年後領回的金額C. W. Cheng162第162页，共181页。(2)利用函數計算FVSCHEDULE(Principal, Schedule)Principal

30、: 單筆存款金額總值Schedule:特定的月利率範圍C. W. Cheng163第163页，共181页。建立資料這些都是年利率,公式計算需使用月利率C. W. Cheng164第164页，共181页。先算出月利率轉換為月利率運用FVSCHEDULE()C. W. Cheng165第165页，共181页。選擇FVSCHEDULE()函數選財務函數C. W. Cheng166第166页，共181页。工具增益集若Excel中無法使用FVSCHEDULE(),請點選“分析工具”C. W. Cheng167第167页，共181页。輸入參數機動月利率的範圍本金C. W. Cheng168第168页，共1

31、81页。計算出定存利潤10萬元定存一年後可領回的金額C. W. Cheng169第169页，共181页。PMT投資或貸款的每期付款額PMT(rate,nper,pv,fv,type)Rate 各期的利率Nper 期數Pv 未來各期年金現值的總和Fv 年金終值。如省略，會自動假定為 0Type 為 0或省略(期末) 或 1 (期初) Ex. 貸100萬, 年利率1.45%, 七年內還清, 每月要還=PMT(1.45%/12, 7*12, 1000000) =-$12,526.33C. W. Cheng170第170页，共181页。年金現值 ExampleEx. 貸100萬, 年利率10%, 一年

32、要還多少?=PMT(10%,10,-1000000)=162,745.39C. W. Cheng171第171页，共181页。PMT Example=Pmt(7.5%/12, 2*12, 5000, 0, 1)貸5千, 年利率7.5%, 2年還清, 每月初應付=Pmt(6%/52, 4*52, 8000, 0, 0)貸8千, 年利率6%, 4年還清, 每週末應付=Pmt(5%, 10*1, 6000, 0, 0)貸6千, 年利率5%, 10年還清, 每年末應付=Pmt(8%/12, 3*12, 5000, 1000, 0)貸5千, 年利率8%, 3年只還4千, 每月末應付C. W. Cheng

33、172第172页，共181页。PMT 貸款與存款PMT(rate,nper,pv,fv,type)每月存6102, 10年後有100萬=PMT(6%/12,10*12,0,1000000)=6102=Fv(6%/12,10*12,6102)=999,991.77貸款100萬, 每月要還11102=PMT(6%/12,10*12,1000000)=11102C. W. Cheng173第173页，共181页。FV年金終值FV(rate,nper,pmt,pv,type)Rate 各期的利率Nper 期數Pmt 各期所應給付或所能取得的固定金額Pv現值或一系列未來付款的目前總額Type 為 0或省略(期末) 或 1 (期初)Ex. 一年存10萬, 年利率10%, 十年後有多少?=FV(10%, 10, 100000, , 0)C. W. Cheng174第174页，共181页。年金終值 ExampleEx. 一年存10萬, 年利率10%, 十年後有多少?=FV(10%, 10, 100000, , 0)C. W.