MicrosoftOfficeExcel在中小企业营运上的运用(44)课件.ppt

1、Microsoft Office Excel 在中小企業營運上的運用在中小企業營運上的運用来自来自 中国最大的资料库下载中国最大的资料库下载地圖式全球績效報告地圖式全球績效報告在中小企業營運實務運作上，銷售績效分析係十分重要與實用的應用系統。在Excel中有許多方法與技巧售績效分析系統來建置銷售績效分析系統。前依單元我們使用控制工具箱工具列與函數連結，來建立績效分析。本單元則使用世界地圖物件來作為操作介面，並運用不同的連結技巧與較簡捷的樞紐分析資料來完成與先前單元截然不同的彙總報表及圖形分析。来自来自 中国最大的资料库下载中国最大的资料库下载物件的佈置及命名物件的佈置及命名Range(區域名稱

2、).Formula=Mid(Application.Caller,5,99)2.2.為區隔與辨識全球地圖中之各地理區域，我們使用多個文字方塊重疊貼附於地圖中，並將其圖樣設定為透明屬性。3.3.為了巨整體設計之需要將全球地圖中之文字方塊分別命名為map_北美、map_南美、map_北美、map_英國、map_歐洲、map_俄羅斯、map_大陸、map_日本、map_亞太平洋、map_澳洲。並將設定區域巨集程式指定與上列各個文字方塊。1.在代表全球績效分析的主控畫面中，插入全球地圖。来自来自 中国最大的资料库下载中国最大的资料库下载製作與連結彙總報表製作與連結彙總報表2.2.使用了Index函數對

3、應至彙總分析中績效彙總報告所內含的明細資料之各數值。1.使用資料樞紐分析表及圖報表指令將績效資料工作表內之績效資料製作績效彙總報告。来自来自 中国最大的资料库下载中国最大的资料库下载運用函數製作資料參照效應運用函數製作資料參照效應INDEX函數可對儲存格範圍或陣列，傳回就指定的欄序號及列序號所交叉決定的元素內容值。本範例所使用的INDEX函數語法如下：INDEX(Array,row_num,column_num)其引數內容如下：Array：為儲存格範圍或陣列。Row_num：用以指定所要傳回的元素是位於儲存格範圍或陣列裡的第幾列。Column_num：用以指定所要傳回的元素是位於儲存格範圍或陣

4、列裡的第幾欄。在Row_num及Column_num引數中，儲存格範圍或陣列倘若只包含單一的橫列或直欄時，則所對應的 row_num 或 column_num 可省略。例如：INDEX(1,2;3,4,2,2)等於 4又例如儲存格 B5:B6 分別內含文字日本和美國，而 C5:C6 內含文字 主機板和半導體，則：INDEX(B5:C6,2,2)等於 半導體INDEX(B5:C6,1,2)等於 美國1.INDEXINDEX函數函數来自来自 中国最大的资料库下载中国最大的资料库下载運用函數製作資料參照效應運用函數製作資料參照效應MATCH函數係根據指定的比對方式，傳回一陣列儲存格範圍或陣列中與搜尋

5、值相符合之相對位置。MATCH函數語法如下：MATCH(lookup_value,lookup_array,match_type)其引數內容如下：Lookup_value：為所要在儲存格範圍或陣列中尋找的值。Lookup_array：為儲存格範圍或陣列，其內含有被比對值的資料。Match_type ：是個數字，其值有三種可能：-1、0 或 1。用以指定 Microsoft Excel 如何從 lookup_array 裡尋找 lookup_value。如果 match_type 是 1，則 MATCH函數會找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次

6、序排列：.，-2，-1，0，1，2，.A-Z，FALSE，TRUE。如果 match_type 是 0，則 MATCH函數會找第一個完全等於 lookup_value 的比較值。Lookup_array 可以依任意次序排列。如果 match_type 是-1，則 MATCH函數會找到等於或大於 lookup_value 的值中的最小值。Lookup_array 必須以遞減次序排列。如果 match_type 引數被省略，則假設其值為 1。例如在下列範圍中：MATCH(39000,B2:B8,1)等於 3MATCH(38000,B2:B8,0)等於 22.MATCHMATCH函數函數来自来自 中

7、国最大的资料库下载中国最大的资料库下载貼附連結圖表貼附連結圖表2.2.使用貼上圖片連結功能將區域別的績效報表以圖片方式連接到主控畫面中。1.完成了對應報表工作表中代表彙集資訊的各個儲存格後，我們可使用繪圖精靈，將對應報表工作表內B2:G6儲存格之資料，轉換成立體長條圖及立體圓形圖，並將之貼附於代表主控畫面的全球績效分析工作表中。来自来自 中国最大的资料库下载中国最大的资料库下载結語(地圖式全球績效報告)v在本模式中我們使用物件重疊的方式來作為地圖式的主控畫面，再使用Application.caller語法讓巨集程式取得使用者所選取的物件區域。並藉由Excel 樞鈕分析表所產生的彙總報表，配合工

8、作表中的Match 及Index 函數來取得對應區域的相關資料。v由此可知，一個設計完善的Microsoft Excel 應用系統，往往是由Microsoft Excel 內建的功能表功能、工作表函數及巨集程式所共同完成。来自来自 中国最大的资料库下载中国最大的资料库下载營業績效圖表彙編營業績效圖表彙編營業績效為企業管理當局最為關心的課題。管理者經常以區域別及產品別來比較實際業績與前期業績或以預算業績作為實際業績的比較基準。藉由此種比較分析，管理者可判斷營業績效，從而作出適當的經營決策。本單元所設計的營業績效分析模式，將各類業績資料以資料庫型態列示。在主控畫面上，則使用控制工具箱工具貼附選項按

9、鈕及下拉清單物件到工作表內，作為切換及操控工具。此一營業績效分析模式，乍看之下，宛如以巨集程式在進行操控作業，然而，事實上其內容完全以Excel 內建功能完成，並未使用任何巨集程式。来自来自 中国最大的资料库下载中国最大的资料库下载資料的佈置與匯總資料的佈置與匯總v建立業績資料記錄建立業績資料記錄 v設定資料庫準則範圍設定資料庫準則範圍 来自来自 中国最大的资料库下载中国最大的资料库下载建立績效比較表建立績效比較表v績效比較表績效比較表 v相關的儲存格內容相關的儲存格內容 来自来自 中国最大的资料库下载中国最大的资料库下载佈置清單方塊物件佈置清單方塊物件v設定清單方塊屬性設定清單方塊屬性 v繪

10、製清單方塊繪製清單方塊 来自来自 中国最大的资料库下载中国最大的资料库下载佈置選項按鈕物件佈置選項按鈕物件v設定選項按鈕屬性設定選項按鈕屬性 v繪製繪製選項按鈕選項按鈕来自来自 中国最大的资料库下载中国最大的资料库下载增添選項按鈕物件群組增添選項按鈕物件群組 v增添選項按鈕增添選項按鈕v設定選項設定選項按鈕物件按鈕物件屬性屬性来自来自 中国最大的资料库下载中国最大的资料库下载製作連結圖表製作連結圖表 (一一)使用繪圖精靈在工作表內貼附長條及區域分析圖使用繪圖精靈在工作表內貼附長條及區域分析圖来自来自 中国最大的资料库下载中国最大的资料库下载製作連結圖表製作連結圖表(二二)將圖表連結至營業績效分

11、析工作表內將圖表連結至營業績效分析工作表內 (1).選擇功能表中的選擇功能表中的編輯編輯/複製複製指令。指令。(2).將儲存格游標移動到營業績效分析工作表中。將儲存格游標移動到營業績效分析工作表中。(3).按下按下Shift 鍵不放，並選擇鍵不放，並選擇 編輯編輯/貼上圖片連結貼上圖片連結指令。指令。来自来自 中国最大的资料库下载中国最大的资料库下载製作連結圖表製作連結圖表(三三)修改連結圖片後之公式修改連結圖片後之公式(1).將圖片的算式更改成：將圖片的算式更改成：=ChangeChart(2).ChangeChart為已使用功能表為已使用功能表插入插入/名稱名稱/定義定義指令指令所定義之公

12、式，其內容為：所定義之公式，其內容為：=IF(chartselection=1,BarChart,ArearChart)来自来自 中国最大的资料库下载中国最大的资料库下载結語(營業績效圖表彙編)v本營業績效分析模式，運用表單工具內的選項按鈕及下拉清單物件來製作操控介面，並以此些物件的儲存格連結特性及資料庫統計函數，來變化績效報表的切換顯示。v此外，範圍命名為Excel最常被引用的功能。然而，Excel的大部份使用者，均以工作表中的儲存格位作為命名對像，鮮少以公式作為命名對像。在本營業績效分析模式內設定了公式命名，並指定該公式予連結的圖表。此一圖表藉由工作表內選項按鈕的設定值，及 IF函數的判斷

13、，動態地變化出使用者所要圖表類型。v本範例充份應用Microsoft Excel本身內建的特性功能，而非依賴巨集程式的巧妙構思。来自来自 中国最大的资料库下载中国最大的资料库下载銷售預算檢討報告銷售預算檢討報告企業的銷售預算乃是將企業企業的銷售預算乃是將企業銷售銷售活動活動轉變成貨幣性數字的過程與成果。然轉變成貨幣性數字的過程與成果。然而而企業在深具不確定性因素的環境內企業在深具不確定性因素的環境內經營，因此企業銷售預算的實際執行經營，因此企業銷售預算的實際執行結果與事前的規劃必定有所差異結果與事前的規劃必定有所差異。因。因此，企業必須進行此，企業必須進行銷售預算檢討分析銷售預算檢討分析，從而

14、就環境的變化將從而就環境的變化將銷售活動銷售活動加以加以修正與調整。修正與調整。Excel 為電子試算表軟體而非美工軟為電子試算表軟體而非美工軟體，因此，在操作介面上，若要形成體，因此，在操作介面上，若要形成較為美觀的效果，則須以使用格式化較為美觀的效果，則須以使用格式化技巧、運用繪圖工具列或以匯入圖片技巧、運用繪圖工具列或以匯入圖片方式來完成。此外，在此，我門展示方式來完成。此外，在此，我門展示了以資料庫記錄為基礎，運用表單物了以資料庫記錄為基礎，運用表單物件及巨集程式來操控及更新該資料庫件及巨集程式來操控及更新該資料庫的樞紐分析彙總報表及圖表分析的樞紐分析彙總報表及圖表分析来自来自 中国最

15、大的资料库下载中国最大的资料库下载人事薪資管理人事薪資管理人事薪資管理涉及企業內部組織及外部機構。對企業內部組織而言，不但要編製員工薪資表外，尚且要辦理銀行薪資轉帳作業，並編製薪資統計表供決策者參考。至於對外部機構而言，管理者必須統計與薪資有關的代扣稅款、應付健保費及應付勞保費，並向有關單位辦理申報作業。傳統上，倘若以手工方式來進行人事薪資管理，不僅耗時費力，且極易產生錯誤。而藉由電腦化的人事薪資管理，則可快速有效地，提昇企業組織的經營效率。本單元所設計的人事薪資管理模式主要在於以簡捷快速的方式來登錄人事薪資料紀錄，分類查詢其內容，彙總薪資統計並以圖表比較分析之。来自来自 中国最大的资料库下载

16、中国最大的资料库下载設計人事薪資資料表設計人事薪資資料表v以下拉清單登錄欄位以下拉清單登錄欄位v以以VLOOKUP函數自動顯示相關欄位資料函數自動顯示相關欄位資料以以K6儲存格內代表第一筆資料記錄儲存格內代表第一筆資料記錄的代扣所得稅之公式而言：的代扣所得稅之公式而言：=VLOOKUP(薪資總額薪資總額,薪資所得扣薪資所得扣繳表繳表,扶養人數扶養人數+2)来自来自 中国最大的资料库下载中国最大的资料库下载查詢人事薪資資料查詢人事薪資資料v自動篩選自動篩選v自訂篩選自訂篩選 来自来自 中国最大的资料库下载中国最大的资料库下载人事薪資分組小計人事薪資分組小計對於人事薪資資料紀錄，我們可以對於人事薪

17、資資料紀錄，我們可以資料資料/小計小計指令，就某些欄指令，就某些欄位，加以分組並統計其它欄位的摘要資訊位，加以分組並統計其它欄位的摘要資訊(加總小計、最大值、加總小計、最大值、最小值、平均值等最小值、平均值等)。来自来自 中国最大的资料库下载中国最大的资料库下载彙總人事薪資統計彙總人事薪資統計資料小計雖然可使用分組方式來統計各組的摘要資訊。然而並未資料小計雖然可使用分組方式來統計各組的摘要資訊。然而並未能以行列交叉方式來列示人事薪資彙總統計。要建立交叉列表式能以行列交叉方式來列示人事薪資彙總統計。要建立交叉列表式的彙總統計，必須使用的彙總統計，必須使用Excel樞紐分析表樞紐分析表及圖報表及圖

18、報表的功能。的功能。在人事薪資資料庫內選擇在人事薪資資料庫內選擇資料資料/樞紐分析表及圖報表樞紐分析表及圖報表指令，並於指令，並於樞紐分析表精靈中，將樞紐分析表的欄位結構設定如下：樞紐分析表精靈中，將樞紐分析表的欄位結構設定如下：来自来自 中国最大的资料库下载中国最大的资料库下载繪製人事薪資圖表繪製人事薪資圖表當帳戶統計表完成後，我們可使用繪圖精靈將薪資統計表、當帳戶統計表完成後，我們可使用繪圖精靈將薪資統計表、薪資撥款銀行統計表及雇主負擔費用統計表之資料轉換薪資撥款銀行統計表及雇主負擔費用統計表之資料轉換成圖表。成圖表。来自来自 中国最大的资料库下载中国最大的资料库下载列印個人薪資表列印個人

19、薪資表有些企業組織，於薪資發放日前，對每位員工寄送個人薪資發放明細有些企業組織，於薪資發放日前，對每位員工寄送個人薪資發放明細表，讓員工了解每月薪資內容。為方便列印所有員工的薪資發放明細表，讓員工了解每月薪資內容。為方便列印所有員工的薪資發放明細表，我們在工作表中建立了一張個人薪資發放明細表，表，我們在工作表中建立了一張個人薪資發放明細表，並貼附了一並貼附了一個按鈕物件，指定其相關巨集程式。使用者僅需按下該按鈕即可列印出個按鈕物件，指定其相關巨集程式。使用者僅需按下該按鈕即可列印出有人事資料庫內所有員工的個人薪資發放明細表。有人事資料庫內所有員工的個人薪資發放明細表。：Private Sub

20、CommandButton1_Click()Dim c As Object 凍結螢幕更新，以避免螢幕晃動凍結螢幕更新，以避免螢幕晃動 Application.ScreenUpdating=False Sheets(人事薪資料庫人事薪資料庫).Select Sheets(人事薪資料庫人事薪資料庫).Range(B6:B6006).Select Set c=ActiveCell 判斷目前儲存格位是否為空白，若為空白則代表資料為最後一列，判斷目前儲存格位是否為空白，若為空白則代表資料為最後一列，而停止列印而停止列印 Do Until IsEmpty(c.Value)Range(EmpID).Valu

21、e=c.Value 往下移動一列往下移動一列 Set c=c.Offset(1,0)列印個人薪資發放明細表列印個人薪資發放明細表 Sheets(個人薪資列印個人薪資列印).PrintOut Copies:=1 Loop Sheets(個人薪資列印個人薪資列印).SelectEnd Sub来自来自 中国最大的资料库下载中国最大的资料库下载結語結語(設計人事薪資資料表設計人事薪資資料表)v人事薪資為企業機構極為普遍與重要的事務管理，本人事薪資模式綜合運用了 Excel 資料分析、統計彙總與圖表繪製的功能。v在人事薪資資料的登錄作業中，使用了資料驗證方式，以下拉清單選項方式來登錄人事薪資資料庫中隸屬

22、單位、部門別、職位等欄位資料，並以VLOOKUP傳回代扣款項各表中相對應的代扣所得稅、代扣健保費、代扣勞保費、雇主負擔健保費與雇主負擔勞保費等欄位。v在查詢人事薪資資料庫方面，本模式使用自動篩選功能來進行查詢作業。並運用資料排序與資料小計功能來獲取分類彙總的資訊。v至於人事薪資統計方面，我們則使用樞紐分析表來彙集人事薪資資料，從而編製了薪資統計表、.薪資撥款銀行統計表及雇主負擔費用統計表。並據此繪製了各分公司應付薪資比較圖、各分公司代扣費用比較圖、雇主負擔費用比較圖等圖表。此外，為方便列印所有員工的薪資發放明細表，本模式以巨集程式來完成列印所有員工的個人薪資發放明細表。来自来自 中国最大的资料

23、库下载中国最大的资料库下载銀行帳戶與應收帳款管理銀行帳戶與應收帳款管理應收帳款帳為企業現金來源的源頭，因此企業對應收帳款帳皆投入甚嚴密的控管注意。應收帳款帳齡分析表經常為企業控管應收帳款的重要控管工具。藉由帳齡分析表，企業可暸解各筆應收帳款帳的品質及回收狀況，對超過一定日期的帳齡，投入更為密切的催收程序與注意。應收帳款收款活動，皆經由銀行帳戶來完成。而一般企業依營業之需要，往往會開立多個銀行帳戶，並就各銀行帳戶進行補同性質的收支交易。企業人員對於各銀行帳戶的收支紀錄及餘額變化，須作全盤的了解與掌控，方能有利於企業現金流量的規劃作業及投資與融資活動的進行。本專題所設計的銀行帳戶管理模式主要在登錄

24、各銀行帳戶的收支交易紀錄，分類查詢其內容，彙總分析收支內容並以圖表比較分析之。来自来自 中国最大的资料库下载中国最大的资料库下载帳齡分析表帳齡分析表在帳齡分析表中的H3儲存格內輸入編製帳齡分析表時的日期，並於B、C、D三欄中輸入相關的帳款資料，則E、F、G、H各欄自動會反映出各筆記錄所相對應的帳齡金額，並於第18及第19列中，統計出彙總資訊。至於帳齡分配圖圖表，也會根據各類帳齡資料以立體圓形圖顯現帳齡分配狀況。来自来自 中国最大的资料库下载中国最大的资料库下载函數與公式函數與公式v辨識帳齡區間的公式辨識帳齡區間的公式v帳齡分析表的標頭函數帳齡分析表的標頭函數代表報表標頭的 B1顯示了帳款資料的

25、起訖日期：=帳齡日期從&TEXT(MIN(C:C),ee/mm/dd)&到&TEXT(MAX(C:C),ee/mm/dd)来自来自 中国最大的资料库下载中国最大的资料库下载繪製帳齡金額分配圖繪製帳齡金額分配圖完成帳齡分析工作表的設計後，以圖表精靈繪製帳齡金額分配圖：来自来自 中国最大的资料库下载中国最大的资料库下载設計銀行帳戶資料清單設計銀行帳戶資料清單v建立資料清單建立資料清單v設定儲存格函數設定儲存格函數 在代表第一筆資料記錄的銀行名稱欄位中輸入下列公式：在代表第一筆資料記錄的銀行名稱欄位中輸入下列公式：=IF(ISNA(VLOOKUP(銀行帳號銀行帳號,開戶銀行資料區開戶銀行資料區,2,

26、FALSE),(VLOOKUP(銀行帳號銀行帳號,開戶銀行資料區開戶銀行資料區,2,FALSE)v以以 資料資料/驗證驗證 指指令設定常用摘要令設定常用摘要 来自来自 中国最大的资料库下载中国最大的资料库下载日期表頭的設計日期表頭的設計vE3=Now()vE4=VLOOKUP(WEEKDAY(E3,2),一週名稱一週名稱,2)vWEEKDAY函數的語法：函數的語法：WEEKDAY(serial_number,return_type)来自来自 中国最大的资料库下载中国最大的资料库下载銀行帳戶資料之分類、查詢與彙總銀行帳戶資料之分類、查詢與彙總 v自動篩選與進階篩選自動篩選與進階篩選v樞紐分析表樞

27、紐分析表 及圖報表及圖報表来自来自 中国最大的资料库下载中国最大的资料库下载收支及餘額比較圖收支及餘額比較圖 来自来自 中国最大的资料库下载中国最大的资料库下载結語結語(銀行帳戶與應收帳款管理銀行帳戶與應收帳款管理)v本專題的帳齡分析模式掌握了Excel以數字型態來表現日期的特性，對帳款日期的間隔以IF與AND函數作成分類計算。在報表標題上則運用MAX、MIN來取得日期欄位的起訖日期，並以TEXT函數轉換成文字型態的日期字串。最後，根據帳齡分析表繪製帳齡金額分配圖。v在資料庫中登錄銀行帳戶的收支紀錄，可使用VLOOKUP函數或以下拉清單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於

28、銀行帳戶的特性，使用了VLOOKUP函數來參照對應的銀行帳戶名稱，然而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。v在帳戶統計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使用自訂欄位方式添加了餘額計算欄位。最後則根據帳戶統計表繪製了收入、支出及等餘額立體長條圖。v在資料庫中登錄銀行帳戶的收支紀錄，可使用VLOOKUP函數或以下拉清單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於銀行帳戶的特性，使用了VLOOKUP函數來參照對應的銀行帳戶名稱，然而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。v至於帳戶統計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使用自訂

29、欄位方式添加了餘額計算欄位。来自来自 中国最大的资料库下载中国最大的资料库下载報表的合併與分析企業機構的各式報表，往往按組織層級、客戶別、地區別或產品別來區隔。然而，就總體的角度觀之，管理者更注重各子報表的合併彙編資訊。各子報表的合併彙編作業，在以往的手工處理過程，不僅煩鎖耗時，而且極易出錯。然而藉由Excel 2003內建的各項功能可以使用簡易便捷的方式，快速有效地完成各式報表的彙編作業。本專題介紹三種合併報表模式，並以圖表分析合併後的彙總資料。来自来自 中国最大的资料库下载中国最大的资料库下载以陣列方式來合併報表以陣列方式來合併報表所謂陣列係以行列方陣排列的資料元素集合。工作表中的範圍為儲

30、存格的集合，其形狀為行列排列，因此亦為陣列類型。来自来自 中国最大的资料库下载中国最大的资料库下载圖表彙編圖表彙編報表合併完成後，我們可使用繪圖精靈將合併後的彙總報表製作成各式圖表来自来自 中国最大的资料库下载中国最大的资料库下载以合併彙算來合併報表以合併彙算來合併報表當被合併的報表格式互為不同時，就不適合使用陣列方式來合併報表，而必須運用 Excel 的合併彙算或樞紐分析表功能來進行合併報表作業。来自来自 中国最大的资料库下载中国最大的资料库下载運用樞紐分析表來合併範圍資料運用樞紐分析表來合併範圍資料無論各分支單位的報表內各欄與各列標題的名稱是否相同，我們都可以使用Excel樞鈕分析功能來完

31、成合併報表。来自来自 中国最大的资料库下载中国最大的资料库下载結語結語(報表的合併與分析報表的合併與分析)v企業機構為闡述整體性的數值資料，而導致彙編合併報表的需要。在Excel中有多種彙編合併報表的方法，各種方法皆有其適用的時機及特點。當被合併的各個子報表之欄列標題，具有相互雷同的名稱時，則可使用陣列運算來合併報表。然而，當被合併的各個子報表之欄列標題名稱互不相同時，則可使用功能表中的資料/合併彙算或資料/樞紐分析表指令來建立合併報表。v以資料/合併彙算所建立的合併報表為欄與列交織的二維式報表，而以資料/樞紐分析表及圖報表指令所建立的合併報表為欄、列與頁交織的三維式報表，透過頁欄位，使用者可運用下拉方塊，顯現各群組的合併報表。v綜上所述，使用資料/樞紐分析表及圖報表指令來建立樞紐分析表式的合併報表，對於分支機構眾多，或對分支機構與予分類的企業組織，提供功能強大及極具彈性的合併報表。来自来自 中国最大的资料库下载中国最大的资料库下载Q&A来自来自 中国最大的资料库下载中国最大的资料库下载