EXCEL新增功能應用.ppt

第一章EXCEL2002新增功能應用 1 1 Excel2002工作表索引彩色標籤使用說明 1 2 Excel2002新增功能 智慧標籤 1 儲存格複製時的智慧標籤 2 插入選項時的智慧標籤 1 3 Excel2002新增功能 試算表函數語法提示 1 4 Excel2002新增功能 表格繪製功能 1 5 Excel2002新增功能 新增Web查詢功能 1 6 Excel2002新增功能 監看式瀏覽功能 第二章EXCEL2002應收帳款實務管理及分析 2 1應收帳款實作 結構分析 設計 統計 應收帳款管理結構分為三個部份來處理 A 應收帳款統計 工作表 B 應收帳款帳齡分析 工作表 C 帳齡分佈圖表 工作表 2 2應收帳款統計 實作設計 統計 分析 應收帳款統計 主執行機制工作表 2 3應收帳款統計 欄位 公式交叉設計 在 應收帳款統計 工作表上 將選定資料庫範圍內所有欄位分為三類 1 必須輸入資料欄位 包含 交易日期 購買產品名稱 銷售數量 產品單價 實際收款日期 等欄位都要屬於要使用者輸入資料的欄位 2 選項欄位 利用線上選擇方式來處理儲存格 包括 客戶編號 是否含稅 結帳方式 付款方式 對帳年月 等欄位 3 直接顯示欄位 不須輸入 EXCEL2002會依儲存格的公式或函數自動顯示各類資料 2 4利用EXCEL2002強大的應用函數 來設計應收帳款的顯示 分析欄位 在應收帳款管理上我們希望登錄者在登錄 客戶編號 後 客戶名稱 及 VIP折扣 會一併帶出 要做到此點 我們要使用EXCEL內建函數VLOOKUP 來處理 現在我們先選擇C7儲存格 顯示客戶名稱的第一個儲存格 輸入下列公式 IF TRIM 客戶編號 VLOOKUP 客戶編號 客戶 2 FALSE 我們利用上述公式中的VLOOKUP 客戶編號 客戶 2 FALSE 函數傳回客戶名稱 其運作方式為同筆資料錄的 客戶編號 C6儲存格 內資料在 客戶 名稱項目中的客戶編號欄搜尋 找到相同的客戶編號後將對應的第2個欄位 客戶名稱 的資料傳回 不過在利用VLOOKUP函數搜尋前 我們先利用TRIM 函數將客戶編號欄位資料內的空白清除 在利用IF 函數判斷 客戶編號 儲存格內是否已有登錄資料 若未輸入客戶編號資料則傳回空白 有資料則執行搜尋處理 2 5應收帳款 金額處理欄位設計 統計 分析 在前面說到我們在設計應收帳款機制時 希望 銷售實價 銷售實價 銷售數量x產品單價xVIP折扣 未稅金額 稅金 及 小計金額 等欄位是運用其他相關欄位組合成的運算公式計算而得 如此才符合帳款金額自動處理 首先選擇H7銷售實價第一個儲存格 輸入 銷售數量 產品單價 VIP折扣的計算公式 如下圖所示 銷售實價 銷售數量x產品單價xVIP折扣 2 6應收帳款 應收帳款帳齡分析分析報表設計 2 7應收帳款 應收帳款帳齡分析交叉分析圖表設計 第三章EXCEL2002進銷存帳務管理 3 1進貨憑證設計 單品成本計算分析 日月星辰貿易股份有限公司決定做到在每一次進口貨品時 要求營業部門都開立一份進貨憑證留存 但在該份進貨憑證上 要求包含的所有資訊分列如下 進貨憑證需包含兩部份 單頭 Header 及明細 Detail Item 兩部份 單頭最重要要包含下列五資訊項目 1進貨使用原幣金額的當時匯率2本次進貨的報關費用3本次進貨的關稅費用4本次進貨的運費支出5卸貨港口其中2 3 4項屬於因進貨而產生的額外費用金額 明細必須包含下列所有項目 1進貨貨品名稱2進貨數量3進貨原幣單價4原幣小計金額 原幣小計金額 進貨數量x進貨原幣單價 5台幣小計金額 台幣小計金額 原幣小計金額x使用原幣金額的當時匯率 6單品進貨成本 3 2進貨憑證前置設計作業 3 3庫存貨品管理 統計 分析 在實務設計上 我們利用四個工作表來處理庫存所有作業 庫存統計分析報表 庫存控管主工作表為庫存控管主機制工作表 記錄庫存管理上所有需要的資訊欄位 庫存項目設定工作表 基本項目定義工作表 各倉庫存統計 以立體圖表顯示方式來分析統計各倉庫存成本金額變化情形 庫存樞紐分析統計 利用樞紐分析來分析庫存資料 3 4庫存統計 期末庫存成本統計分析 3 5庫存交叉分析統計 庫存樞紐分析設計 庫存統計樞紐分析報表 3 6銷貨憑證建立 統計 分析設計 在進銷存作業裡面 銷貨 可說是企業營業收入最重要的來源 也可以說有了銷貨作業 就產生了 應收帳款 因此 銷貨 作業的管理 統計 及分析就成為各企業日常作業中最重要的工作之一 第四章會計帳務與資產負債 損益表實務分析 4 1 會計帳務管理 管理架構分析及定義 在設計上將整個要處理的會計帳務管理架構分為五個主工作表來處理 A 會計科目的建立及維護作業 B 日計帳簿的建立及維護作業 C 試算表的建立及試算分析 D 損益表的建立及統計分析 E 資產負債表的建立及統計分析 4 2 會計帳務管理 會計科目登錄工作表設計實作 會計科目登錄管理工作表在設計上主要目的為建立會計科目維護及查詢列印作業 在一般會計作業上會計科目都屬於8大會計類別 資產負債業主權益營業收入營業成本營業費用營業外收入營業外費用等8類會計類別 4 3 會計帳務管理 日記簿登錄工作表設計實作 各個企業在發生任何一筆交易行為 銷售 進貨 退貨 時 必須依據該筆交易的內容及性質來開立傳票 而每一張傳票在經過開立後 必須詳實以流水帳方式登錄在日計帳簿上 作為後續會計帳務處理的依據及資料來源 4 4 會計帳務管理 分類明細帳查詢設計實作 會計日常作業中除了日記帳簿以外 另一最常用到的就是分類明細帳了 在電腦會計實務處理方式上來說 日記帳是以流水帳方式登錄處理 而分類明細帳則是以會計科目為主要分類項目 4 5 會計帳務管理試算表查詢設計實作 電腦化會計作業上 試算的觀念是指借方貸方金額平衡來檢視帳務登錄時的作業是否有錯誤發生的可能 也就是說會計電腦化作業上強調的 試算表 作業 簡單的說就在檢核借貸是否平衡 利用Excel2002的樞紐分析表來對日記帳簿的流水帳資料做統計分析 利用樞紐分析表來設計試算表 4 6 會計帳務管理 損益表實作設計 4 7 會計帳務管理 損益類別分析比較圖表設計實作 4 8 會計帳務管理 資產負債表設計實作 4 9 會計帳務管理 資產負債統計圖表設計實作 第五章固定資產管理分析與實作 固定資產管理 企業固定資產折舊的提列金額對公司的財務狀況及經營成果具有深遠的影響 因此較有規模的公司對於資產的折舊計算及管理皆十分重視 且會定期進行資產的盤點來驗證帳款記錄資料 固定資產管理 固定資產管理 5 1 EXCEL2002內建折舊處理函數介紹 1 DB函數 2 DDB函數 3 SLN函數 4 SYD函數 5 VDB函數 5 2 固定資產管理統計 資產報表登錄管理設計 1 財產登錄管理 主工作表 2 資產統計分析 工作表 3 資產樞紐分析 工作表 4 帳面價值分析 工作表 5 累計折舊分析 工作表 固定資產管理 5 3 固定資產管理 財產資料管理設計分析 財產資料管工作表 主要用途為記錄公司所有購置財產的資料及計算分析折舊攤提金額和帳面價值 主架構欄位包含12個重點資料欄位 A購置日期 B財產類型 C財產編號 D財產名稱 E單位 F數量 G初始購置成本 H使用年限 I殘值 J本期攤提 K累計折舊 L帳面價值 5 4 固定資產管理 資產統計分析設計 5 5 固定資產管理 資產樞紐分析 累計折舊 帳面價值圖表繪製設計 5 6 固定資產管理 在網際上瀏覽資產統計分析資訊 當設計完成全部固定資產管理作業 已經可以處理所有財產的管理作業 但設計好的資產統計分析圖表只能在Excel中查看 而日月星辰公司決策人員希望將資產的統計分析資料能在網際上也可查看 如此公司決策及管理人員不論人在何處 都可即時掌握最新的資訊 因此現在就來設計如何將資產統計分析工作表轉成Web網頁供使用者查詢處理 由於Excel2002擁有強大的轉換功能而且操作上十分簡單 固定資產管理 資產統計分析試算表在瀏覽器中顯示 第六章收入與費用管理 一個公司企業營運的好壞 從該公司的收入及費用就可看的一清二楚 也就是說從收入及費用的角度上看 就可知道該公司目前是否賺錢或賠錢 所以在任一公司企業內部對即時性的收入及費用支出情況 必定控管得十分嚴謹 所謂 知己知彼 百戰百勝 收入 與 費用 的即時統計及分析是企業 知己 最重要的手段 收入 與 費用 可說是企業營運資金流動的最重要的兩個方向 因此在做公司資金流通分析時 一定是 收入 與 費用 同時一起處理分析 收入及費用管理 6 1收入費用統計分析 交叉分析報表 結構設計分析 1 收入費用統計分析工作表 2 收入費用交叉分析工作表 6 2收入費用統計分析報表 實作設計分析 6 3收入費用彙總交叉統計分析 月平均收入費用分析設計 收入費用統計分析作業中 還有一項十分重要的分析資訊要包含在交叉分析報表中 那就是 月平均收入 及 月平均費用 分析 唯有分析月平均收入及費用才可直接看出企業營運走勢 因此在分析收入及費用時 就一定要分析 月平均值 日月星辰企業有限公司希望在分析 月平均值 時 不僅能看到 月平均收入 及 月平均費用 資訊外 更要能夠看到 月平均盈虧金額 以及 平均每月費用佔收入比率 的統計分析資料 第七章EXCEL2002人事管理及薪資管理應用 人事資料及薪資資料管理對於企業來說不但涉及公司內部組織管理及營運成本負擔控制 對於公司內部組織管理而言 人事資料的建立 審核 年資處理 升遷建議 等等以及製作員工薪資表單 以及員工薪資銀行轉帳作業 等 都是企業內部組織管理的工作 而對於公司營運成本負擔控制而言 員工薪資亦屬於公司營運的成本 因此在控管營運成本時 員工薪資是一定要加以管理及控制 因此利用電腦化來管理人事薪資資料已是企業內財務會計及管理人員十分重要的一項工作任務 人事薪資管理 7 1人事管理及薪資統計管理 架構分析 A 員工基本資料管理 工作表 B 員工薪資清冊 工作表 C 員工薪資分析 工作表 D 員工薪資報表 工作表 E 員工銀行轉帳 工作表 7 2人事管理及薪資統計管理 員工資料維護管理分析 在設計員工資料維護工作表時 有兩點我們必須特殊處理 1 輸入員工編號時 需將員工編號中帶有應文字母的部份自動改為大寫字母 2 在輸入員工編號後 系統會將本日日期自動傳入到 到期日期 及 加保日期 儲存格上 7 3人事管理及薪資統計管理 員工薪資清冊管理分析 7 4人事管理及薪資統計管理 員工薪資報表管理分析 員工薪資建立完成後 我們常常需要檢查及核對是否正確 若在員工薪資清冊上直接檢查當然是可以 但當薪資資料多達上百筆或數百筆時 在員工薪資清冊直接檢查就不是那麼容易了 由於員工薪資清冊上一頁上有十幾筆的員工薪資資料 一筆一筆的核對不但容易對錯 更容易漏對 而最好的核對方式是一頁上只有一筆員工薪資資料 如此不但容易核對且不易出錯 7 5人事管理及薪資統計管理 員工薪資分析圖表設計 7 6人事管理及薪資統計管理 員工銀行轉帳設計 在人事薪資管理系統中 我們還規劃了一個主功能 員工薪資銀行轉帳 作業 現在一般的企業在做員工薪資的發放時 都已使用銀行轉帳的方式 如此不僅方便而且公司內也不必存放太多現金 因此在規劃上 我們設計了一個員工薪資銀行轉帳資料報表 存放在 員工銀行轉帳 工作表上 來做員工薪資轉檔的報表建立作業 7 7人事管理及薪資統計管理 員工基本資料匯出 在一般企業內 員工人事資料可說是企業內部最重要的資料之一 因此不單是財務會計部門會使用到員工資料 其他部門同樣會使用到員工資料 而且是在不同的檔案系統或應用系統中使用 Excel2002是MS OfficeXP家族的一份子 而OfficeXP包含的所有應用軟體 Excel2002 Access2002 Word2002 等 都擁有強大的資料匯出及匯入功能 本節中我們將學習如何將Excel2002工作表的資料匯出到Access資料庫中供其他應用軟體系統使用 第八章EXCEL2002收付票據管理設計實作 8 1 應收應付票據帳齡 票齡 分析管理 檔案結構分析 1 說明 利用EXCEL2002強大的組織流程圖來說明帳齡 票齡 分析管理報表的結構 在本工作表中讀者會學會如何利用Excel2002提供的資料庫圖表功能來建立專業的流程圖表 2 應收票據帳齡分析工作表 3 應付票據帳齡分析工作表 4 應收票據帳齡分析圖表工作表 8 2 說明 組織結構圖實作 設計 組織圖循環圖表星形圖表 金字塔 三角 圖表卞式圖表靶心圖表 8 3應收票據帳齡 票齡 統計分析 實作設計 8 4 應付票據帳齡 票齡 統計分析 實作設計 8 5 應收票據帳齡 票齡 圖表統計分析 實作設計 8 6 應付票據帳齡 票齡 圖表統計分析 實作設計 第九章EXCEL2002銀行帳戶管理實作設計 企業日常營運過程 首重資金的流通 調度 及進出管理 企業資金的運用及各類交易 都要經由銀行帳戶來處理 因此 在企業體內銀行帳戶資金的運用及調度管理 則是企業內財務會計人員最重要的職責 由於企業內資金在運用上有不同的功能及用途 通常都不會全部由同一個銀行帳戶來處理 往往會有數個銀行帳戶同時往來處理 所以企業內財務人員對於各銀行帳戶內存提收支記錄以及目前餘額的多寡變化都必須隨時的掌控及瞭解 如此對企業資金的運用及調度 才能全盤的處理及監控 如此才能維持企業的營運正常 9 1 銀行帳戶管理 結構分析及預視 1 銀行帳戶管理 存提款記錄工作表 2 帳戶樞紐分析統計 工作表 3 各月份存提款分析比較圖表 及 各帳戶餘額分析比較圖表 各月份存提款分析比較圖表 立體直條圖即時分析各月份存提款比較 各帳戶餘額分析比較圖表 立體直條圖即時分析各帳戶餘額變化圖 9 2 銀行帳戶管理 存提款記錄工作表 實作設計 9 3 帳戶樞紐分析統計 分析工作表 實作設計 9 4 帳戶餘額統計圖表 分析 實作設計 9 5 月份存提記錄統計圖表 分析 實作設計 第十章EXCEL2002客戶管理 市調資訊分析管理 客戶資訊的建檔 維護 查詢 列印 統計 分析都是任一企業公司中最重要的業務資料 客戶資料也是各企業中最常處理及使用的資料 而一般企業內的客戶資料大都建立在資料庫 如 MS Access SQL Server 等資料庫 中 使用客戶資訊管理系統來對客戶資料做新增 修改 查詢 列印 及標籤管理 等作業 而在對客戶資料做統計分析作業時 大部份的企業又希望直接利用EXCEL2002來完成這項任務 本章中我們將利用EXCEL2002的 匯入外部資料 功能從資料庫中 MS Access 將客戶主檔資料直接匯入到EXCEL2002中執行統計分析的作業 同時我們將利用EXCEL2002多功能的樞紐分析表來對匯入的客戶主檔做分類的統計分析作業 10 1 匯入客戶主檔資料 實作設計 10 2 依地區別 消費日期別做客戶消費樞紐分析 實務設定 10 3 依客戶類別做類別消費金額圖表分析 利用PI型圖分析 在做客戶消費能力分析時 一般一定要做客戶類別的大分類的消費金額分析 由類別的消費金額各佔全部消費金額的比率 我們可以分析出客戶群的消費能力及公司未來針對客戶群的營運方向的制定 依據此項重點 我們現在就來以客戶類別做消費能力分析 在設計上我們將使用圖表分析方式設計 且使用PI型圖 圓形比率圖 來分析客戶各類別的消費能力 10 4 EXCEL2002市場調查設定實務分析 日月星辰企業公司最近與國外廠商洽談合作方案 希望代理兩項國外新產品 兩項產品分別是冷凍現烤熱狗及新品牌的汽水飲料 因此日月星辰決策人員希望企劃部門對這兩項新產品做一次簡單式的電話問卷調查 調查的內容只需有四項 分別為 A是否喜歡吃熱狗B是否喜歡喝汽水C喜歡熱狗的口味D喜歡汽水的口味除了四項問題以外 必須記錄受訪者的性別 年齡 居住區域 以及職業 由於考量到受訪者的隱私權及回答意願 因此該數項記錄必須以區間性資料來登錄 如 年齡以十歲為單位選項 項目數及名稱設計如下 20歲以下 包含20歲 21 30歲31 40歲41 50歲51 60歲60歲以上問卷調查資料建立後 日月星辰企業決策者希望利用這些調查資料能分析及觀察下列統計分析資料 1 受訪者依地區別分佈統計 2 受訪者依性別別分佈統計 3 依喜歡 不喜歡產品的人數統計分析 10 5 市場調查電話問卷實務設計 市場調查電話問卷 由於在設計上要使用輸入即得到結果 通常在設計上能做到當資料一輸入即可得到經過統計或分析的結果 這種處理機制幾乎都是要使用程式來處理才做得到 因此在這種情形下我們必須使用Excel2002的巨集及VBA程式設計功能來設計即時統計的部份 第十一章EXCEL2002營業統計樞紐分析管理 日月星辰百貨公司要做一份依季別及區域別的年度營業銷售交叉分析報表 在該份營業樞紐分析報表上不但要有各區域各季別的銷售金額統計以外 該公司要求做到下列數點 1 季別區分為 第一季 第二季 第三季及第四季等四個季別 且各季別要依不同區域別區分 2 區域別區分為 北部 中部 南部 東部等四個區域 3 各季別要做到區域銷售金額合計及計算區域平均銷售金額 4 各區域別要做到季別合計及季別平均銷售 報表座標方位值設計要求如下 季別 以橫座標方式表現區域別 以縱座標方式表示 11 1 營業樞紐分析交叉統計分析報表 結構設計 日月星辰百貨公司要求的這份營業樞紐分析報表可說是十分的複雜 同時要求在規劃上必須將該營業分析報表區分為三大部份來處理 因此我們定義各部分在實務設計上的作法 1 各季別 各區域銷售金額登錄區 包含季別的總和統計及平均統計以及區域別的總和統計及平均統計 在工作表上定義A1 H8儲存格來設計此部份的統計管理 2 各季別交叉統計的高標設定顯示區域 交叉統計分式依 第一季各區域 第二季各區域第三季各區域 第四季各區域各季別分別統計在工作表上定義B10 E13儲存格來設計此部份的高標統計管理 在工作表上定義F10 G13儲存格來設計此部份的高標比率運算 在工作表上定義H10 H13儲存格來處理顯示 優等 的狀況 3 營業分析統計圖表因為要以小範圍顯示報表 因此我們將使用立體直條圖來表現營業高標統計 11 2 各季別 各區域銷售金額登錄區 欄位規劃設計 11 3 各季別交叉統計的高標設定顯示區域 欄位規劃設計 11 4 營業分析統計圖表設計 第十二章EXCEL2002銷售帳務系統規劃實作 辦公室自動化 OA 作業可說是一種資訊協同處理作業 在整個作業設計或作業架構分析上 自動化所影響的範疇是非常的廣闊 也就是說如果就辦公室自動化的純粹軟體應用設計層面而言 要將一個企業體內的辦公室自動化做到上軌道 所需要的軟體系統並不是單一EXCEL2002所能做到的 一般企業都是使用適合該公司自動化的套裝或半套裝應用系統 包含進銷存管理 應收應付管理 票據管理 以及總帳管理 固定資產管理 等等 來達到自動化的目的 而另一種方式就是找尋適當的軟體設計公司設計符合自己企業所需要的整合軟體應用系統 不論採用上面所說的那一種方法執行自動化作業 對於企業而言都必須付出不低的成本費用 對於大多數的中小企業而言 這都是一筆非常高的成本支出 也就是說有許多企業他們無法負擔上述兩種執行辦公室自動化方法所需要的成本支出 然而這些企業也不是說負擔不起這種成本支出 就放棄辦公室自動化的作業 因為在現在這種競爭如此激烈的環境中 若不使用自動化來增強企業核心的競爭力 改善企業的內部效率 降低企業的人事成本 那這種企業必無法在這麼激烈的競爭環境中存活 12 1 銷售資料管理程式設計 實作分析 首先教導讀者如何利用EXCEL2002設計銷售資訊管理系統 因為銷售管理統計分析是企業營運收入中最重視的作業 所以現在我們就來利用EXCEL2002設計一套實用的銷售登錄分析管理系統 在設計上 我們將銷售登錄分析管理系統分為五個主工作表來設計 1客戶建檔維護主工作表 該工作表用途為建立客戶資料 並執行客戶人數的加總及統計 在此工作表上 我們希望模擬一般的應