1、臺灣微軟公司 軟體顧問洪士吉 編撰Microsoft Office Excel 在中小企業(yè)營運上的運用臺灣微軟公司 軟體顧問Microsoft Office Ex地圖式全球績效報告在中小企業(yè)營運實務(wù)運作上，銷售績效分析係十分重要與實用的應(yīng)用系統(tǒng)。在Excel中有許多方法與技巧售績效分析系統(tǒng)來建置銷售績效分析系統(tǒng)。前依單元我們使用控制工具箱工具列與函數(shù)連結(jié)，來建立績效分析。本單元則使用世界地圖物件來作為操作介面，並運用不同的連結(jié)技巧與較簡捷的樞紐分析資料來完成與先前單元截然不同的彙總報表及圖形分析。地圖式全球績效報告在中小企業(yè)營運實務(wù)運作上，銷售績效分析係十物件的佈置及命名Range(區(qū)域名稱)

2、.Formula = Mid(Application.Caller, 5, 99)2.為區(qū)隔與辨識全球地圖中之各地理區(qū)域，我們使用多個文字方塊重疊貼附於地圖中，並將其圖樣設(shè)定為透明屬性。3.為了巨整體設(shè)計之需要將全球地圖中之文字方塊分別命名為map_北美、map_南美、map_北美、map_英國、map_歐洲、map_俄羅斯、map_大陸、map_日本、map_亞太平洋、map_澳洲。並將設(shè)定區(qū)域巨集程式指定與上列各個文字方塊。1.在代表全球績效分析的主控畫面中，插入全球地圖。物件的佈置及命名Range(區(qū)域名稱).Formula 製作與連結(jié)彙總報表2.使用了Index函數(shù)對應(yīng)至彙總分析中績效

3、彙總報告所內(nèi)含的明細資料之各數(shù)值。1.使用資料樞紐分析表及圖報表指令將績效資料工作表內(nèi)之績效資料製作績效彙總報告。製作與連結(jié)彙總報表2.使用了Index函數(shù)對應(yīng)至彙總分析中績運用函數(shù)製作資料參照效應(yīng)INDEX函數(shù)可對儲存格範圍或陣列，傳回就指定的欄序號及列序號所交叉決定的元素內(nèi)容值。本範例所使用的INDEX函數(shù)語法如下：INDEX(Array,row_num,column_num)其引數(shù)內(nèi)容如下：Array：為儲存格範圍或陣列。Row_num：用以指定所要傳回的元素是位於儲存格範圍或陣列裡的第幾列。Column_num：用以指定所要傳回的元素是位於儲存格範圍或陣列裡的第幾欄。 在Row_num

4、及Column_num引數(shù)中，儲存格範圍或陣列倘若只包含單一的橫列或直欄時，則所對應(yīng)的 row_num 或 column_num 可省略。例如：INDEX(1,2;3,4,2,2) 等於 4又例如儲存格 B5:B6 分別內(nèi)含文字日本和美國，而 C5:C6 內(nèi)含文字 主機板和半導體，則：INDEX(B5:C6,2,2) 等於 半導體INDEX(B5:C6,1,2) 等於 美國1. INDEX函數(shù)運用函數(shù)製作資料參照效應(yīng)INDEX函數(shù)可對儲存格範圍或陣列，運用函數(shù)製作資料參照效應(yīng)MATCH函數(shù)係根據(jù)指定的比對方式，傳回一陣列儲存格範圍或陣列中與搜尋值相符合之相對位置。MATCH函數(shù)語法如下：MAT

5、CH(lookup_value,lookup_array,match_type)其引數(shù)內(nèi)容如下：Lookup_value：為所要在儲存格範圍或陣列中尋找的值。Lookup_array：為儲存格範圍或陣列，其內(nèi)含有被比對值的資料。Match_type ：是個數(shù)字，其值有三種可能：-1、0 或 1。用以指定 Microsoft Excel 如何從 lookup_array 裡尋找 lookup_value。如果 match_type 是 1，則 MATCH函數(shù)會找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次序排列：.，-2，-1，0，1，2，.A-Z，F(xiàn)A

6、LSE，TRUE。如果 match_type 是 0，則 MATCH函數(shù)會找第一個完全等於 lookup_value 的比較值。Lookup_array 可以依任意次序排列。如果 match_type 是 -1，則 MATCH函數(shù)會找到等於或大於 lookup_value 的值中的最小值。Lookup_array 必須以遞減次序排列。如果 match_type 引數(shù)被省略，則假設(shè)其值為 1。例如在下列範圍中：MATCH(39000,B2:B8,1) 等於 3MATCH(38000,B2:B8,0) 等於 22. MATCH函數(shù)運用函數(shù)製作資料參照效應(yīng)MATCH函數(shù)係根據(jù)指定的比對方式，貼附連結(jié)

7、圖表2.使用貼上圖片連結(jié)功能將區(qū)域別的績效報表以圖片方式連接到主控畫面中。1.完成了對應(yīng)報表工作表中代表彙集資訊的各個儲存格後，我們可使用繪圖精靈，將對應(yīng)報表工作表內(nèi)B2:G6儲存格之資料，轉(zhuǎn)換成立體長條圖及立體圓形圖，並將之貼附於代表主控畫面的全球績效分析工作表中。貼附連結(jié)圖表2.使用貼上圖片連結(jié)功能將區(qū)域別的績效報表以結(jié)語(地圖式全球績效報告)在本模式中我們使用物件重疊的方式來作為地圖式的主控畫面，再使用Application.caller語法讓巨集程式取得使用者所選取的物件區(qū)域。並藉由Excel 樞鈕分析表所產(chǎn)生的彙總報表，配合工作表中的Match 及Index 函數(shù)來取得對應(yīng)區(qū)域的相關(guān)

8、資料。由此可知，一個設(shè)計完善的Microsoft Excel 應(yīng)用系統(tǒng)，往往是由Microsoft Excel 內(nèi)建的功能表功能、工作表函數(shù)及巨集程式所共同完成。結(jié)語(地圖式全球績效報告)在本模式中我們使用物件重疊的方式來營業(yè)績效圖表彙編營業(yè)績效為企業(yè)管理當局最為關(guān)心的課題。管理者經(jīng)常以區(qū)域別及產(chǎn)品別來比較實際業(yè)績與前期業(yè)績或以預算業(yè)績作為實際業(yè)績的比較基準。藉由此種比較分析，管理者可判斷營業(yè)績效，從而作出適當?shù)慕?jīng)營決策。本單元所設(shè)計的營業(yè)績效分析模式，將各類業(yè)績資料以資料庫型態(tài)列示。在主控畫面上，則使用控制工具箱工具貼附選項按鈕及下拉清單物件到工作表內(nèi)，作為切換及操控工具。此一營業(yè)績效分析模

9、式，乍看之下，宛如以巨集程式在進行操控作業(yè)，然而，事實上其內(nèi)容完全以Excel 內(nèi)建功能完成，並未使用任何巨集程式。營業(yè)績效圖表彙編營業(yè)績效為企業(yè)管理當局最為關(guān)心的課題。管理者資料的佈置與匯總建立業(yè)績資料記錄 設(shè)定資料庫準則範圍 資料的佈置與匯總建立業(yè)績資料記錄 建立績效比較表績效比較表 相關(guān)的儲存格內(nèi)容 建立績效比較表績效比較表 相關(guān)的儲存格內(nèi)容 佈置清單方塊物件設(shè)定清單方塊屬性 繪製清單方塊 佈置清單方塊物件設(shè)定清單方塊屬性 繪製清單方塊 佈置選項按鈕物件設(shè)定選項按鈕屬性 繪製選項按鈕佈置選項按鈕物件設(shè)定選項按鈕屬性 繪製選項按鈕增添選項按鈕物件群組 增添選項按鈕設(shè)定選項按鈕物件屬性增添選

10、項按鈕物件群組 增添選項按鈕設(shè)定選項按鈕物件屬性製作連結(jié)圖表 (一)使用繪圖精靈在工作表內(nèi)貼附長條及區(qū)域分析圖製作連結(jié)圖表 (一)使用繪圖精靈在工作表內(nèi)貼附長條及區(qū)域分析製作連結(jié)圖表(二) 將圖表連結(jié)至營業(yè)績效分析工作表內(nèi) (1).選擇功能表中的編輯/複製指令。(2).將儲存格游標移動到營業(yè)績效分析工作表中。(3).按下Shift 鍵不放，並選擇 編輯/貼上圖片連結(jié)指令。製作連結(jié)圖表(二) 將圖表連結(jié)至營業(yè)績效分析工作表內(nèi) (製作連結(jié)圖表(三) 修改連結(jié)圖片後之公式(1).將圖片的算式更改成： =ChangeChart(2). ChangeChart為已使用功能表插入/名稱/定義指令所定義之公

11、式，其內(nèi)容為： =IF(chartselection=1,BarChart,ArearChart)製作連結(jié)圖表(三) 修改連結(jié)圖片後之公式(1).將圖片的算式結(jié)語(營業(yè)績效圖表彙編)本營業(yè)績效分析模式，運用表單工具內(nèi)的選項按鈕及下拉清單物件來製作操控介面，並以此些物件的儲存格連結(jié)特性及資料庫統(tǒng)計函數(shù)，來變化績效報表的切換顯示。此外，範圍命名為Excel最常被引用的功能。然而，Excel的大部份使用者，均以工作表中的儲存格位作為命名對像，鮮少以公式作為命名對像。在本營業(yè)績效分析模式內(nèi)設(shè)定了公式命名，並指定該公式予連結(jié)的圖表。此一圖表藉由工作表內(nèi)選項按鈕的設(shè)定值，及 IF函數(shù)的判斷，動態(tài)地變化出使

12、用者所要圖表類型。本範例充份應(yīng)用Microsoft Excel本身內(nèi)建的特性功能，而非依賴巨集程式的巧妙構(gòu)思。結(jié)語(營業(yè)績效圖表彙編)本營業(yè)績效分析模式，運用表單工具內(nèi)的銷售預算檢討報告企業(yè)的銷售預算乃是將企業(yè)銷售活動轉(zhuǎn)變成貨幣性數(shù)字的過程與成果。然而企業(yè)在深具不確定性因素的環(huán)境內(nèi)經(jīng)營，因此企業(yè)銷售預算的實際執(zhí)行結(jié)果與事前的規(guī)劃必定有所差異 。因此，企業(yè)必須進行銷售預算檢討分析，從而就環(huán)境的變化將銷售活動加以修正與調(diào)整。Excel 為電子試算表軟體而非美工軟體，因此，在操作介面上，若要形成較為美觀的效果，則須以使用格式化技巧、運用繪圖工具列或以匯入圖片方式來完成。此外，在此，我門展示了以資料庫

13、記錄為基礎(chǔ)，運用表單物件及巨集程式來操控及更新該資料庫的樞紐分析彙總報表及圖表分析銷售預算檢討報告企業(yè)的銷售預算乃是將企業(yè)銷售活動轉(zhuǎn)變成貨幣性人事薪資管理人事薪資管理涉及企業(yè)內(nèi)部組織及外部機構(gòu)。對企業(yè)內(nèi)部組織而言，不但要編製員工薪資表外，尚且要辦理銀行薪資轉(zhuǎn)帳作業(yè)，並編製薪資統(tǒng)計表供決策者參考。至於對外部機構(gòu)而言，管理者必須統(tǒng)計與薪資有關(guān)的代扣稅款、應(yīng)付健保費及應(yīng)付勞保費，並向有關(guān)單位辦理申報作業(yè)。傳統(tǒng)上，倘若以手工方式來進行人事薪資管理，不僅耗時費力，且極易產(chǎn)生錯誤。而藉由電腦化的人事薪資管理，則可快速有效地，提昇企業(yè)組織的經(jīng)營效率。本單元所設(shè)計的人事薪資管理模式主要在於以簡捷快速的方式來登

14、錄人事薪資料紀錄，分類查詢其內(nèi)容，彙總薪資統(tǒng)計並以圖表比較分析之。人事薪資管理人事薪資管理涉及企業(yè)內(nèi)部組織及外部機構(gòu)。對企業(yè)內(nèi)設(shè)計人事薪資資料表以下拉清單登錄欄位以VLOOKUP函數(shù)自動顯示相關(guān)欄位資料以K6儲存格內(nèi)代表第一筆資料記錄的代扣所得稅之公式而言：=VLOOKUP(薪資總額,薪資所得扣繳表,扶養(yǎng)人數(shù)+2)設(shè)計人事薪資資料表以下拉清單登錄欄位以K6儲存格內(nèi)代表第一筆查詢?nèi)耸滦劫Y資料自動篩選自訂篩選 查詢?nèi)耸滦劫Y資料自動篩選人事薪資分組小計對於人事薪資資料紀錄，我們可以資料/小計指令，就某些欄位，加以分組並統(tǒng)計其它欄位的摘要資訊(加總小計、最大值、最小值、平均值等)。 人事薪資分組小計對

15、於人事薪資資料紀錄，我們可以資料/小計彙總?cè)耸滦劫Y統(tǒng)計資料小計雖然可使用分組方式來統(tǒng)計各組的摘要資訊。然而並未能以行列交叉方式來列示人事薪資彙總統(tǒng)計。要建立交叉列表式的彙總統(tǒng)計，必須使用Excel樞紐分析表及圖報表的功能。在人事薪資資料庫內(nèi)選擇資料/樞紐分析表及圖報表指令，並於樞紐分析表精靈中，將樞紐分析表的欄位結(jié)構(gòu)設(shè)定如下： 彙總?cè)耸滦劫Y統(tǒng)計資料小計雖然可使用分組方式來統(tǒng)計各組的摘要資繪製人事薪資圖表當帳戶統(tǒng)計表完成後，我們可使用繪圖精靈將薪資統(tǒng)計表、薪資撥款銀行統(tǒng)計表及雇主負擔費用統(tǒng)計表之資料轉(zhuǎn)換成圖表。繪製人事薪資圖表當帳戶統(tǒng)計表完成後，我們可使用繪圖精靈將薪列印個人薪資表有些企業(yè)組織，

16、於薪資發(fā)放日前，對每位員工寄送個人薪資發(fā)放明細表，讓員工了解每月薪資內(nèi)容。為方便列印所有員工的薪資發(fā)放明細表，我們在工作表中建立了一張個人薪資發(fā)放明細表，並貼附了一個按鈕物件，指定其相關(guān)巨集程式。使用者僅需按下該按鈕即可列印出有人事資料庫內(nèi)所有員工的個人薪資發(fā)放明細表。 ：Private Sub CommandButton1_Click() Dim c As Object 凍結(jié)螢幕更新，以避免螢幕晃動 Application.ScreenUpdating = False Sheets(人事薪資料庫).Select Sheets(人事薪資料庫).Range(B6:B6006).Select Se

17、t c = ActiveCell 判斷目前儲存格位是否為空白，若為空白則代表資料為最後一列， 而停止列印 Do Until IsEmpty(c.Value) Range(EmpID).Value = c.Value 往下移動一列 Set c = c.Offset(1, 0) 列印個人薪資發(fā)放明細表 Sheets(個人薪資列印).PrintOut Copies:=1 Loop Sheets(個人薪資列印).SelectEnd Sub列印個人薪資表有些企業(yè)組織，於薪資發(fā)放日前，對每位員工寄送結(jié)語(設(shè)計人事薪資資料表)人事薪資為企業(yè)機構(gòu)極為普遍與重要的事務(wù)管理，本人事薪資模式綜合運用了 Excel

18、資料分析、統(tǒng)計彙總與圖表繪製的功能。在人事薪資資料的登錄作業(yè)中，使用了資料驗證方式，以下拉清單選項方式來登錄人事薪資資料庫中隸屬單位、部門別、職位等欄位資料，並以VLOOKUP傳回代扣款項各表中相對應(yīng)的代扣所得稅、代扣健保費、代扣勞保費、雇主負擔健保費與雇主負擔勞保費等欄位。在查詢?nèi)耸滦劫Y資料庫方面，本模式使用自動篩選功能來進行查詢作業(yè)。並運用資料排序與資料小計功能來獲取分類彙總的資訊。至於人事薪資統(tǒng)計方面，我們則使用樞紐分析表來彙集人事薪資資料，從而編製了薪資統(tǒng)計表、.薪資撥款銀行統(tǒng)計表及雇主負擔費用統(tǒng)計表。並據(jù)此繪製了各分公司應(yīng)付薪資比較圖、各分公司代扣費用比較圖、雇主負擔費用比較圖等圖表

19、。此外，為方便列印所有員工的薪資發(fā)放明細表，本模式以巨集程式來完成列印所有員工的個人薪資發(fā)放明細表。結(jié)語(設(shè)計人事薪資資料表)人事薪資為企業(yè)機構(gòu)極為普遍與重要的銀行帳戶與應(yīng)收帳款管理應(yīng)收帳款帳為企業(yè)現(xiàn)金來源的源頭，因此企業(yè)對應(yīng)收帳款帳皆投入甚嚴密的控管注意。應(yīng)收帳款帳齡分析表經(jīng)常為企業(yè)控管應(yīng)收帳款的重要控管工具。藉由帳齡分析表，企業(yè)可暸解各筆應(yīng)收帳款帳的品質(zhì)及回收狀況，對超過一定日期的帳齡，投入更為密切的催收程序與注意。應(yīng)收帳款收款活動，皆經(jīng)由銀行帳戶來完成。而一般企業(yè)依營業(yè)之需要，往往會開立多個銀行帳戶，並就各銀行帳戶進行補同性質(zhì)的收支交易。企業(yè)人員對於各銀行帳戶的收支紀錄及餘額變化，須作全

20、盤的了解與掌控，方能有利於企業(yè)現(xiàn)金流量的規(guī)劃作業(yè)及投資與融資活動的進行。本專題所設(shè)計的銀行帳戶管理模式主要在登錄各銀行帳戶的收支交易紀錄，分類查詢其內(nèi)容，彙總分析收支內(nèi)容並以圖表比較分析之。銀行帳戶與應(yīng)收帳款管理應(yīng)收帳款帳為企業(yè)現(xiàn)金來源的源頭，因此企帳齡分析表在帳齡分析表中的H3儲存格內(nèi)輸入編製帳齡分析表時的日期，並於B、C、D三欄中輸入相關(guān)的帳款資料，則E、F、G、H各欄自動會反映出各筆記錄所相對應(yīng)的帳齡金額，並於第18及第19列中，統(tǒng)計出彙總資訊。至於帳齡分配圖圖表，也會根據(jù)各類帳齡資料以立體圓形圖顯現(xiàn)帳齡分配狀況。帳齡分析表在帳齡分析表中的H3儲存格內(nèi)輸入編製帳齡分析表時的函數(shù)與公式辨識

21、帳齡區(qū)間的公式帳齡分析表的標頭函數(shù)代表報表標頭的 B1顯示了帳款資料的起訖日期：=帳齡日期從 & TEXT(MIN(C:C),ee/mm/dd) &到 & TEXT(MAX(C:C),ee/mm/dd)函數(shù)與公式辨識帳齡區(qū)間的公式帳齡分析表的標頭函數(shù)代表報表標頭繪製帳齡金額分配圖完成帳齡分析工作表的設(shè)計後，以圖表精靈繪製帳齡金額分配圖：繪製帳齡金額分配圖完成帳齡分析工作表的設(shè)計後，以圖表精靈設(shè)計銀行帳戶資料清單建立資料清單設(shè)定儲存格函數(shù) 在代表第一筆資料記錄的銀行名稱欄位中輸入下列公式：= IF(ISNA(VLOOKUP(銀行帳號,開戶銀行資料區(qū),2,FALSE), , (VLOOKUP(銀行

22、帳號,開戶銀行資料區(qū),2,FALSE)以資料/驗證指令設(shè)定常用摘要 設(shè)計銀行帳戶資料清單建立資料清單設(shè)定儲存格函數(shù) 在代表第一筆日期表頭的設(shè)計E3 =Now()E4=VLOOKUP(WEEKDAY(E3,2),一週名稱,2)WEEKDAY函數(shù)的語法：WEEKDAY(serial_number,return_type)日期表頭的設(shè)計E3 =Now()銀行帳戶資料之分類、查詢與彙總 自動篩選與進階篩選樞紐分析表 及圖報表銀行帳戶資料之分類、查詢與彙總 自動篩選與進階篩選收支及餘額比較圖 收支及餘額比較圖 結(jié)語(銀行帳戶與應(yīng)收帳款管理)本專題的帳齡分析模式掌握了Excel以數(shù)字型態(tài)來表現(xiàn)日期的特性，

23、對帳款日期的間隔以IF與AND函數(shù)作成分類計算。在報表標題上則運用MAX、MIN來取得日期欄位的起訖日期，並以TEXT函數(shù)轉(zhuǎn)換成文字型態(tài)的日期字串。最後，根據(jù)帳齡分析表繪製帳齡金額分配圖。在資料庫中登錄銀行帳戶的收支紀錄，可使用VLOOKUP函數(shù)或以下拉清單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於銀行帳戶的特性，使用了VLOOKUP函數(shù)來參照對應(yīng)的銀行帳戶名稱，然而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。在帳戶統(tǒng)計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使用自訂欄位方式添加了餘額計算欄位。最後則根據(jù)帳戶統(tǒng)計表繪製了收入、支出及等餘額立體長條圖。在資料庫中

24、登錄銀行帳戶的收支紀錄，可使用VLOOKUP函數(shù)或以下拉清單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於銀行帳戶的特性，使用了VLOOKUP函數(shù)來參照對應(yīng)的銀行帳戶名稱，然而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。至於帳戶統(tǒng)計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使用自訂欄位方式添加了餘額計算欄位。結(jié)語(銀行帳戶與應(yīng)收帳款管理)本專題的帳齡分析模式掌握了Ex報表的合併與分析企業(yè)機構(gòu)的各式報表，往往按組織層級、客戶別、地區(qū)別或產(chǎn)品別來區(qū)隔。然而，就總體的角度觀之，管理者更注重各子報表的合併彙編資訊。各子報表的合併彙編作業(yè)，在以往的手工處理過程，不僅煩鎖耗時，而且極易出錯。然而藉由Excel 2003內(nèi)建的各項功能可以使用簡易便捷的方式，快速有效地完成各式報表的彙編作業(yè)。本專題介紹三種合併報表模式，並以圖表分析合併後的