EXCEL新增功能應用

1、 第 一 章 excel 2002 新增功能應用1-11-1excel 2002 excel 2002 工作表索引彩色標籤使用說明工作表索引彩色標籤使用說明1-21-2excel 2002 excel 2002 新增功能新增功能- - 智慧標籤智慧標籤 1 儲存格複製時的智慧標籤： 2 插入選項時的智慧標籤：1-31-3excel 2002 excel 2002 新增功能新增功能 - - 試算表函數語法提示試算表函數語法提示1-41-4excel 2002 excel 2002 新增功能新增功能 表格繪製功能表格繪製功能1-51-5excel 2002 excel 2002 新增功能新增功能

2、新增新增webweb查詢功能查詢功能1-61-6excel 2002 excel 2002 新增功能新增功能 監看式瀏覽功能監看式瀏覽功能第二章 excel 2002 應收帳款 實務管理及分析2-1 應收帳款實作 結構分析/ 設計/ 統計應收帳款管理結構分為三個部份來處理： ( a ) 應收帳款統計工作表： ( b ) 應收帳款帳齡分析工作表： ( c ) 帳齡分佈圖表工作表： 2-2 應收帳款統計 實作設計 / 統計 / 分析應收帳款統計主執行機制工作表 2-3 應收帳款統計 欄位/公式交叉設計 在應收帳款統計工作表上，將選定資料庫範圍內所有欄位分為三類：( 1 ) 必須輸入資料欄位：包含交

3、易日期、購買產品名稱、銷 售數量、產品單價、實際收款日期等欄位都要屬於要使 用者輸入資料的欄位。( 2 ) 選項欄位：利用線上選擇方式來處理儲存格，包括客戶編號、 是否含稅、結帳方式、付款方式、對帳年月等欄 位。 ( 3 ) 直接顯示欄位：不須輸入，excel 2002 會依儲存格的公式或函數 自動顯示各類資料。 2-4 利用excel 2002強大的應用函數 來設計應收帳款的顯示/分析欄位 在應收帳款管理上我們希望登錄者在登錄客戶編號後，客戶名稱及vip折扣會一併帶出。要做到此點，我們要使用 excel 內建函數 vlookup ( ) 來處理。現在我們先選擇 c7 儲存格 (顯示客戶名稱的

4、第一個儲存格)，輸入下列公式：=if(trim(=if(trim(客戶編號客戶編號)=,vlookup()=,vlookup(客戶編號客戶編號, ,客戶客戶,2,false),2,false) 我們利用上述公式中的 vlookup(客戶編號,客戶, 2,false) 函數傳回客戶名稱，其運作方式為同筆資料錄的客戶編號 ( c6 儲存格 ) 內資料在客戶名稱項目中的客戶編號欄搜尋，找到相同的客戶編號後將對應的第2 個欄位 (客戶名稱) 的資料傳回。不過在利用 vlookup 函數搜尋 前，我們先利用 trim () 函數將客戶編號欄位資料內的空白清除，在利用 if( )函數判斷客戶編號儲存格內是

5、否已有登錄資料，若未輸入客戶編號資料則傳回空白，有資料則執行搜尋處理。 2-5 應收帳款-金額處理欄位 設計/統計/分析 在前面說到我們在設計應收帳款機制時，希望銷售實價 ( 銷售實價 = 銷售數量 x 產品單價 x vip折扣 )、未稅金額、稅金及小計金額等欄位是運用其他相關欄位組合成的運算公式計算而得，如此才符合帳款金額自動處理。 首先選擇 h7 銷售實價第一個儲存格，輸入 =銷售數量 * 產品單價 * vip折扣 的計算公式 (如下圖所示)。 銷售實價 = 銷售數量 x 產品單價 x vip折扣2-6 應收帳款-應收帳款帳齡分析 分析報表設計2-7 應收帳款-應收帳款帳齡 分析交叉分析圖

6、表設計第三章 excel 2002 進銷存帳務管理3-1進貨憑證設計 / 單品成本計算分析 日月星辰貿易股份有限公司決定做到在每一次進口貨品時，要求營業部門都開立一份進貨憑證留存。但在該份進貨憑證上，要求包含的所有資訊分列如下：(進貨憑證 需包含兩部份：單頭 (header) 及明細 (detail-item) 兩部份 ) 單頭最重要要包含下列五資訊項目： 1 進貨使用原幣金額的當時匯率 2 本次進貨的報關費用 3 本次進貨的關稅費用 4 本次進貨的運費支出 5 卸貨港口 其中 2、3、4 項屬於因進貨而產生的額外費用金額。 明細必須包含下列所有項目： 1 進貨貨品名稱 2 進貨數量 3 進貨

7、原幣單價 4 原幣小計金額 ( 原幣小計金額 = 進貨數量 x進貨原幣單價 ) 5 台幣小計金額 ( 台幣小計金額 = 原幣小計金額 x 使用原幣金額的當時匯率 ) 6 單品進貨成本3-2 進貨憑證前置設計作業3-3 庫存貨品管理 / 統計 / 分析 在實務設計上，我們利用四個工作表來處理庫存所有作業： 庫存統計分析報表：庫存控管主工作表 為庫存控管主機制工作表，記錄庫存管理上所有需要的資訊欄位。 庫存項目設定工作表：基本項目定義工作表 各倉庫存統計：以立體圖表顯示方式來分析統計各倉庫存成本金額 變化情形 庫存樞紐分析統計：利用樞紐分析來分析庫存資料3-4 3-4 庫存統計庫存統計期末庫存成本

8、統計分析期末庫存成本統計分析3-5 3-5 庫存交叉分析統計庫存交叉分析統計 庫存樞紐分析設計庫存樞紐分析設計庫存統計樞紐分析報表 3-6 3-6 銷貨憑證建立銷貨憑證建立 / / 統計統計 / / 分析設計分析設計 在進銷存作業裡面，銷貨可說是企業營業收入最重要的來源，也可以說有了銷貨作業，就產生了應收帳款！因此銷貨作業的管理、統計、及分析就成為各企業日常作業中最重要的工作之一。 第 四 章 會計帳務與 資產負債、損益表實務分析4-1會計帳務管理- 管理架構分析及定義 在設計上將整個要處理的會計帳務管理架構分為五個主工作表來處理： ( a ) 會計科目的建立及維護作業： ( b ) 日計帳簿

9、的建立及維護作業： ( c ) 試算表的建立及試算分析： ( d ) 損益表的建立及統計分析： ( e ) 資產負債表的建立及統計分析： 4-2會計帳務管理-會計科目登錄工作表設計實作 會計科目登錄管理工作表在設計上主要目的為建立會計科目維護及查詢列印作業。在一般會計作業上會計科目都屬於8大會計類別： 資產 負債 業主權益 營業收入 營業成本 營業費用 營業外收入 營業外費用等8類會計類別4-3會計帳務管理- 日記簿登錄工作表設計實作 各個企業在發生任何一筆交易行為 (銷售、進貨、退貨 )時，必須依據該筆交易的內容及性質來開立傳票。而每一張傳票在經過開立後，必須詳實以流水帳方式登錄在日計帳簿上

10、。作為後續會計帳務處理的依據及資料來源。 4-4會計帳務管理- 分類明細帳查詢設計實作 會計日常作業中除了日記帳簿以外，另一最常用到的就是分類明細帳了，在電腦會計實務處理方式上來說，日記帳是以流水帳方式登錄處理，而分類明細帳則是以會計科目為主要分類項目。4-5會計帳務管理試算表查詢設計實作 電腦化會計作業上，試算的觀念是指借方貸方金額平衡來檢視帳務登錄時的作業是否有錯誤發生的可能！也就是說會計電腦化作業上強調的試算表作業，簡單的說就在檢核借貸是否平衡！ 利用excel 2002 的樞紐分析表來對日記帳簿的流水帳資料做統計分析，利用樞紐分析表來設計試算表。4-6會計帳務管理- 損益表實作設計4-

11、7會計帳務管理- 損益類別分析比較圖表設計實作4-8會計帳務管理- 資產負債表設計實作4-9會計帳務管理- 資產負債統計圖表設計實作 第五章 固定資產管理分析與實作固定資產管理 企業固定資產折舊的提列金額對公司的財務狀況及經營成果具有深遠的影響，因此較有規模的公司對於資產的折舊計算及管理皆十分重視！且會定期進行資產的盤點來驗證帳款記錄資料。 固定資產管理固定資產管理5-1excel 2002 內建折舊處理函數介紹 【 1 】. db 函數： 【 2 】. ddb 函數： 【 3 】. sln 函數： 【 4 】. syd 函數： 【 5 】. vdb 函數： 5-2固定資產管理統計- 資產報表

12、登錄管理設計【 1 】.財產登錄管理主工作表： 【 2 】.資產統計分析工作表： 【 3 】.資產樞紐分析工作表： 【 4 】.帳面價值分析工作表： 【 5 】.累計折舊分析工作表： 固定資產管理5-3固定資產管理財產資料管理設計分析 財產資料管工作表主要用途為記錄公司所有購置財產的資料及計算分析折舊攤提金額和帳面價值。主架構欄位包含12個重點資料欄位： a 購置日期： b 財產類型： c 財產編號： d 財產名稱： e 單位： f 數量： g 初始購置成本： h 使用年限： i 殘值： j 本期攤提： k 累計折舊： l 帳面價值：5-4固定資產管理-資產統計分析設計5-5固定資產管理-資產

13、樞紐分析 /累計折舊 /帳面價值圖表繪製設計5-6固定資產管理- 在網際上瀏覽資產統計分析資訊 當設計完成全部固定資產管理作業，已經可以處理所有財產的管理作業，但設計好的資產統計分析圖表只能在excel 中查看！而日月星辰公司決策人員希望將資產的統計分析資料能在網際上也可查看，如此公司決策及管理人員不論人在何處，都可即時掌握最新的資訊。 因此現在就來設計如何將資產統計分析工作表轉成 web 網頁供使用者查詢處理！由於 excel 2002 擁有強大的轉換功能而且操作上十分簡單！固定資產管理資產統計分析試算表在瀏覽器中顯示 第 六 章 收入與費用管理 一個公司企業營運的好壞， 從該公司的收入及費

14、用就可看的一清二楚 ，也就是說從收入及費用的角度上看，就可知道該公司目前是否賺錢或賠錢 。所以在任一公司企業內部對即時性的收入及費用支出情況，必定控管得十 分嚴謹，所謂知己知彼，百戰百勝！收入與費用的即時統計及 分析是企業知己最重要的手段。 收入與費用可說是企業營運資金流動的最重要的兩個方向，因 此在做公司資金流通分析時，一定是收入與費用同時一起處理分析 。 收入及費用管理6-1 收入費用統計分析收入費用統計分析/ /交叉分析報表交叉分析報表 結構設計分析結構設計分析 ( 1 ) 收入費用統計分析 工作表 ( 2 ) 收入費用交叉分析 工作表 6-2 收入費用統計分析報表收入費用統計分析報表實

15、作設計分析實作設計分析6-3 收入費用彙總交叉統計分析收入費用彙總交叉統計分析 月平均收入費用分析設計月平均收入費用分析設計 收入費用統計分析作業中，還有一項十分重要的分析資訊要包含在交叉分析報表中，那就是月平均收入及月平均費用分析。唯有分析月平均收入及費用才可直接看出企業營運走勢！因此在分析收入及費用時，就一定要分析月平均值。日月星辰企業有限公司希望在分析月平均值時，不僅能看到月平均收入及月平均費用資訊外，更要能夠看到月平均盈虧金額以及平均每月費用佔收入比率的統計分析資料。 第七章 excel2002 人事管理 及薪資管理應用 人事資料及薪資資料管理對於企業來說不但涉及公司內部組織管理及營運

16、成本負擔控制。對於公司內部組織管理而言，人事資料的建立、審核、年資處理、升遷建議 等等以及製作員工薪資表單，以及員工薪資銀行轉帳作業 等，都是企業內部組織管理的工作。而對於公司營運成本負擔控制而言，員工薪資亦屬於公司營運的成本！因此在控管營運成本時，員工薪資是一定要加以管理及控制。因此利用電腦化來管理人事薪資資料已是企業內財務會計及管理人員十分重要的一項工作任務！ 人事薪資管理7-1 人事管理及薪資統計管理- 架構分析 a 員工基本資料管理工作表 b 員工薪資清冊工作表 c 員工薪資分析工作表 d 員工薪資報表工作表 e 員工銀行轉帳工作表 7-2 人事管理及薪資統計管理- 員工資料維護管理分

17、析 在設計員工資料維護工作表時，有兩點我們必須特殊處理：【1】輸入員工編號時，需將員工編號中帶有應文字母 的部份自動改為大寫字母。【2】在輸入員工編號後，系統會將本日日期自動傳入 到到期日期及加保日期儲存格上。7-3 人事管理及薪資統計管理-員工薪資清冊管理分析7-4 人事管理及薪資統計管理- 員工薪資報表管理分析 員工薪資建立完成後，我們常常需要檢查及核對是否正確，若在員工薪資清冊上直接檢查當然是可以 ，但當薪資資料多達上百筆或數百筆時，在員工薪資清冊直接檢查就不是那麼容易了，由於員工薪資清冊上一頁上有十幾筆的員工薪資資料，一筆一筆的核對不但容易對錯，更容易漏對！而最好的核對方式是一頁上只有

18、一筆員工薪資資料，如此不但容易核對且不易出錯。 7-5人事管理及薪資統計管理-員工薪資分析圖表設計7-6 人事管理及薪資統計管理-員工銀行轉帳設計 在人事薪資管理系統中，我們還規劃了一個主功能：員工薪資銀行轉帳作業，現在一般的企業在做員工薪資的發放時，都已使用銀行轉帳的方式，如此不僅方便而且公司內也不必存放太多現金！ 因此在規劃上，我們設計了一個員工薪資銀行轉帳資料報表(存放在員工銀行轉帳工作表上)來做員工薪資轉檔的報表建立作業。7-7 人事管理及薪資統計管理-員工基本資料匯出 在一般企業內，員工人事資料可說是企業內部最重要的資料之一，因此不單是財務會計部門會使用到員工資料，其他部門同樣會使用

19、到員工資料，而且是在不同的檔案系統或應用系統中使用！ excel 2002 是ms_office xp 家族的一份子，而officexp包含的所有應用軟體 (excel 2002、access 2002、word 2002 等) 都擁有強大的資料匯出及匯入功能。本節中我們將學習如何將excel 2002工作表的資料匯出到 access 資料庫中供其他應用軟體系統使用。第八章 excel 2002 收付票據管理設計實作8-1應收應付票據帳齡 (票齡)分析管理檔案結構分析 ( 1 ) 說明：利用excel 2002強大的組織流程圖來說 明帳齡 (票齡) 分析管理報表的結構。 在本工作表中讀者會學會

20、如何利用excel 2002提供的資料庫圖表功能來建立專業 的流程圖表。 ( 2 ) 應收票據帳齡分析工作表 ( 3 ) 應付票據帳齡分析工作表 ( 4 ) 應收票據帳齡分析圖表工作表 8-2說明組織結構圖實作/ 設計 組織圖 循環圖表 星形圖表 金字塔(三角)圖表 卞式圖表 靶心圖表8-3應收票據帳齡(票齡)統計分析實作設計8-4應付票據帳齡(票齡)統計分析實作設計8-5應收票據帳齡(票齡)圖表統計分析 實作設計8-6應付票據帳齡(票齡)圖表統計分析 實作設計 第九章 excel 2002 銀行帳戶管理實作設計 企業日常營運過程，首重資金的流通、調度、及進出管理。企業資金的運用及各類交易，都

21、要經由銀行帳戶來處理。因此，在企業體內銀行帳戶資金的運用及調度管理，則是企業內財務會計人員最重要的職責。 由於企業內資金在運用上有不同的功能及用途，通常都不會全部由同一個銀行帳戶來處理，往往會有數個銀行帳戶同時往來處理。所以企業內財務人員對於各銀行帳戶內存提收支記錄以及目前餘額的多寡變化都必須隨時的掌控及瞭解，如此對企業資金的運用及調度，才能全盤的處理及監控，如此才能維持企業的營運正常。9-1銀行帳戶管理結構分析及預視( 1 ) 銀行帳戶管理存提款記錄工作表 ( 2 ) 帳戶樞紐分析統計工作表 ( 3 ) 各月份存提款分析比較圖表及 各帳戶餘額分析比較圖表 各月份存提款分析比較圖表： 立體直條

22、圖即時分析各月份存提款比較 各帳戶餘額分析比較圖表 ： 立體直條圖即時分析各帳戶餘額變化圖9-2銀行帳戶管理存提款記錄工作表 實作設計9-3帳戶樞紐分析統計分析工作表 實作設計9-4帳戶餘額統計圖表分析實作設計9-5月份存提記錄統計圖表分析 實作設計第十章 excel 2002 客戶管理 / 市調資訊分析管理 客戶資訊的建檔、維護、查詢、列印、統計、分析都是任一企業公司中最重要的業務資料，客戶資料也是各企業中最常處理及使用的資料。而一般企業內的客戶資料大都建立在資料庫 (如：ms-access、sql-server 等資料庫) 中，使用客戶資訊管理系統來對客戶資料做新增、修改、查詢、列印、及標

23、籤管理 等作業！而在對客戶資料做統計分析作業時，大部份的企業又希望直接利用excel2002 來完成這項任務。 本章中我們將利用excel 2002 的匯入外部資料功能從資料庫中( ms-access ) 將客戶主檔資料直接匯入到excel2002中執行統計分析的作業。同時我們將利用excel2002多功能的樞紐分析表來對匯入的客戶主檔做分類的統計分析作業。10-1匯入客戶主檔資料實作設計10-2依地區別/消費日期別做 客戶消費樞紐分析 實務設定10-3依客戶類別做類別消費金額圖表分析 利用 pi 型圖分析 在做客戶消費能力分析時，一般一定要做客戶類別的大分類的消費金額分析，由類別的消費金額各

24、佔全部消費金額的比率，我們可以分析出客戶群的消費能力及公司未來針對客戶群的營運方向的制定。依據此項重點，我們現在就來以客戶類別做消費能力分析。在設計上我們將使用圖表分析方式設計，且使用pi型圖( 圓形比率圖 ) 來分析客戶各類別的消費能力。10-4excel 2002 市場調查設定實務分析 日月星辰企業公司最近與國外廠商洽談合作方案，希望代理兩項國外新產品，兩項產品分別是冷凍現烤熱狗及新品牌的汽水飲料。因此日月星辰決策人員希望企劃部門對這兩項新產品做一次簡單式的電話問卷調查，調查的內容只需有四項，分別為： a 是否喜歡吃熱狗 b 是否喜歡喝汽水 c 喜歡熱狗的口味 d 喜歡汽水的口味 除了四項

25、問題以外，必須記錄受訪者的性別、年齡、居住區域、以及職業，由於考量到受訪者的隱私權及回答意願！因此該數項記錄必須以區間性資料來登錄，如：年齡以十歲為單位選項，項目數及名稱設計如下： 20歲以下 (包含20歲) 21 30 歲 31 40 歲 41 50 歲 51 60 歲 60歲以上 問卷調查資料建立後，日月星辰企業決策者希望利用這些調查資料能分析及觀察下列統計分析資料： ( 1 ) 受訪者依地區別分佈統計 ( 2 ) 受訪者依性別別分佈統計 ( 3 ) 依喜歡/ 不喜歡產品的人數統計分析10-5市場調查電話問卷實務設計 市場調查電話問卷由於在設計上要使用輸入即得到結果！通常在設計上能做到當資

26、料一輸入即可得到經過統計或分析的結果，這種處理機制幾乎都是要使用程式來處理才做得到！因此在這種情形下我們必須使用 excel 2002 的巨集及vba 程式設計功能來設計即時統計的部份。 第十一章 excel 2002 營業統計樞紐分析管理 日月星辰百貨公司要做一份依季別及區域別的年度營業銷售交叉分析報表，在該份營業樞紐分析報表上不但要有各區域各季別的銷售金額統計以外，該公司要求做到下列數點： 1 季別區分為：第一季、第二季、第三季及第四季 等四個季別。且各季別要依不同區域別區分。 2 區域別區分為：北部、中部、南部、東部等四個 區域。 3 各季別要做到區域銷售金額合計及計算區域平均 銷售金額

27、。 4 各區域別要做到季別合計及季別平均銷售。 報表座標方位值設計要求如下： 季別：以橫座標方式表現 區域別：以縱座標方式表示11-1營業樞紐分析交叉統計分析報表- 結構設計 日月星辰百貨公司要求的這份營業樞紐分析報表可說是十分的複雜，同時要求在規劃上必須將該營業分析報表區分為三大部份來處理，因此我們定義各部分在實務設計上的作法： ( 1 ) 各季別 / 各區域銷售金額登錄區。 包含季別的總和統計及平均統計以及區域別的總和統計及平均統計 。在工作表上定義a1:h8儲存格來設計此部份的統計管理。 ( 2 ) 各季別交叉統計的高標設定顯示區域。 交叉統計分式依：第一季各區域 / 第二季各區域 第三

28、季各區域 / 第四季各區域 各季別分別統計 在工作表上定義b10:e13儲存格來設計此部份的高標統計管理。 在工作表上定義f10:g13儲存格來設計此部份的高標比率運算。 在工作表上定義h10:h13儲存格來處理顯示優等的狀況。 ( 3 ) 營業分析統計圖表因為要以小範圍顯示報表，因此我們將使用立體 直條圖來表現營業高標統計。11-2各季別/各區域銷售金額登錄區 欄位規劃設計：11-3各季別交叉統計的高標設定顯示區域 欄位規劃設計：11-4營業分析統計圖表設計第十二章 excel 2002 銷售帳務系統規劃實作 辦公室自動化 (oa) 作業可說是一種資訊協同處理作業，在整個作業設計或作業架構分

29、析上，自動化所影響的範疇是非常的廣闊，也就是說如果就辦公室自動化的純粹軟體應用設計層面而言，要將一個企業體內的辦公室自動化做到上軌道，所需要的軟體系統並不是單一 excel 2002 所能做到的！一般企業都是使用適合該公司自動化的套裝或半套裝應用系統 (包含進銷存管理、應收應付管理、票據管理、以及總帳管理、固定資產管理 等等) 來達到自動化的目的。 而另一種方式就是找尋適當的軟體設計公司設計符合自己企業所需要的整合軟體應用系統。 不論採用上面所說的那一種方法執行自動化作業，對於企業而言都必須付出不低的成本費用，對於大多數的中小企業而言，這都是一筆非常高的成本支出！也就是說有許多企業他們無法負擔上述兩種執行辦公室自動化方法所需要的成本支出！ 然而這些企業也不是說負擔不起這種成本支出，就放棄辦公室自動化的作業！因為在現在這種競爭如此激烈的環境中，若不使用自動化來增強企業核心的競爭力！改善企業的內部效率！降低企業的人事成本。那這種企業必無法在這麼激烈的競爭環境中存活。12-1銷售資料管理程式設計 實作分析 首先教導讀者如何利用excel 2002 設計銷售資訊管理系統 ，因為銷售管理統計分析是企業營運收入中最重視的作業。所以現在我們就來利用excel 2002設計一套實用的銷售登錄分析管理系統。在設計上，我們將銷售登錄分析管理系統分為五個主工作表來設計：