Excel页财会应用技巧.ppt

C. W. Cheng Excel 於財務會計的應用 三個範例 收入與費用管理 應收帳款管理及分析 庫存帳務管理 收入與費用管理 兩個工作表 收入費用與統計分析工作表 交叉統計資料 交叉分析圖表 收入費用交叉分析工作表 顯示分析圖表 收入費用與統計分析工作表 收入費用統計分析工作表 目標一 依據月份及收入費用項目得到統計狀況 直接顯示該月的盈虧 目標二 以圖表顯示每月的收入與費用 目標一:依據月份及收入費用作統計 利用手繪格線設計工作表外觀 設定各個欄位資料型態 手繪格線 設定儲存格格式 計算儲存格的值(1) 選擇工具列的「插入函數」圖示 計算儲存格的值(2) 計算儲存格的值(3) 計算儲存格的值(4) 計算儲存格的值(5) 練習 請完成「費用小計」與「金額小計」 費用小計=營業費用+其他費用 各項金額小計=各項金額1~6月的總額 目標一:直接顯示該月的盈虧 盈虧金額=收入小計－費用小計 直接輸入計算式 Ex: C13=C7-C11 目標二:圖表顯示每月的收入及費用 步驟一:插入圖表 選擇插入圖表 選擇圖表類型 步驟二:選擇資料來源(1) 選擇資料來源(2) 更改數列名稱 選擇X軸的類別名稱 將圖表插入目前的工作表 步驟三:選擇選擇圖表區與背景牆格式(1) 選擇選擇圖表區與背景牆格式(2) 收入費用交叉分析工作表 欄寬自動調整 用AVERAGE()函數計算平均值 AVERAGE(Num1, Num2,…) Num1,Num2,…是1到30個數字 計算所有輸入數字的平均值(算術平均數) 計算平均值(1) 使用功能表的「插入」－「函數」 計算平均值(2) 計算平均值(3) 計算平均值(4) 平均每月盈虧金額 平均每月盈虧金額 = 平均每月收入-平均每月費用 = B1-B2 平均每月費用占收入比率(1) 計算公式: 若「收入」不為0, 則比率=平均費用/平均收入 若「收入」為 0, 則比率=0 IF(B10, B2/B1, 0) IF(B1=0,0,B2/B1) 平均每月費用占收入比率(2) 設定儲存格圖樣 設定儲存格外框 選擇儲存格格式 練習 插入平均每月收入與費用的圖表 應收帳款管理及分析 應收帳款處理流程 三個工作表 將應收帳款管理結構分為三個部份處理 「應收帳款統計」工作表 「應收帳款帳齡分析」工作表 「帳齡分布圖表」工作表 輔助工作表 「資料項目定義」工作表 「應收帳款統計」工作表 應收帳款統計工作表 目標一 熟悉儲存格格式的應用 目標二 登錄「客戶編號」時，相關欄位內容自動顯示 目標三 自動算出「銷售實價」 目標四 利用「應收帳款統計」的資料，完成「應收帳款帳齡分析」與「帳齡統計圖表」 目標一:熟悉儲存格格式的應用 對各個欄位資料的特性設定儲存格格式 交易日期:日期 銷售數量:數值 產品單價:貨幣 實際收款日期:日期 … 設定儲存格格式 目標二：自動顯示相關欄位內容 建立儲存格名稱 單一行或列儲存格名稱 多行或列儲存格名稱 運用選項欄位 以線上查詢與選擇的功能免除重複輸入 建立儲存格名稱(1) 建立儲存格名稱(2) 建立儲存格名稱(3) 建立儲存格名稱(4) 在「應收帳款統計」工作表建立下列名稱設定 客戶編號 銷售數量 產品單價 VIP折扣 銷售實價 是否含稅 未稅金額 稅金 結帳方式 建立儲存格名稱(5) 建立儲存格名稱(6) 建立儲存格名稱(7) 建立儲存格名稱(8) 再「資料項目定義」工作表建立下列名稱設定 結帳處理方式 付款方式 結帳年 結帳月 稅別 客戶 建立選項欄位(1) 提供選擇的方式讓使用者選取需要的數值或資料值，屬於「選項欄位」。 建立選項欄位(2) 給「稅別」欄位一個名稱 建立選項欄位(3) 以「稅別」為基礎,建立參考的「清單」 建立選項欄位(4) 建立選項欄位(5) 選擇要採用選取式欄位的儲存格 選擇儲存格 I7:I20 建立選項欄位(6) 建立資料驗證：「資料」－「驗證」 建立選項欄位(7) 建立選項欄位(8) 建立選項欄位(9) 可在欄位旁發現下拉式箭頭 練習 請建立「結帳方式」以及「付款方式」的選項欄位 提示 先在「資料項目定義」工作表中找到對應的清單名稱 自動顯示相關欄位內容(1) 輸入「客戶編號」時，自動顯示「客戶名稱」以及「VIP折扣」。 自動顯示相關欄位內容(2) 利用三個應用函數:IF, TRIM, VLOOKUP IF(TRIM(客戶編號)=“”,””, VLOOKUP(客戶編號,客戶,2,FALSE)) 自動顯示相關欄位內容(3) VLOOKUP(客戶編號,客戶, 2 ,FALSE) 自動顯示相關欄位內容(3) IF(判斷條件,條件成立的值,條件不成立的值) 練習 輸入產品名稱時,希望自動顯示產品單價 提示 先給「資料項目定義」工作表中的產品名稱與單價一個共同的