第三章 樞紐-業務績效與業績獎金 學習重點 -資料篩選與排序 -小計 -樞紐分析表與樞紐分析圖的繪製 -HLOOKUP()函數的運用 -使用IF條件判斷式 -變更百分比的顯示 -註解

章節大綱 3-1 製作產品銷售排行榜 3-2 建立樞紐分析表 3-3 樞紐分析圖的製作 3-4 計算業務人員的銷售獎金 備註：可依進度點選小節

3-1 製作產品銷售排行榜 篩選資料 Excel 中就有一種篩選功能，可以讓工作表只顯示指定條件的資料，隱藏其餘非指定條件資料。 以下範例將利用此功能篩選出這個月份，業務人員編號「A0906」所有的業務狀況。請開啟範例檔「ch03-01.xlsx」。 1

3-1 製作產品銷售排行榜 篩選資料 2 3

3-1 製作產品銷售排行榜 進階篩選選項 按下篩選下拉鈕後，會依照儲存格數值格式不同出現「文字篩選」或「數字篩選」指令，在此則可設定更為進階的篩選設定。例如執行「前10項」指令，將會產生「自動篩選前10項」對話視窗，如下圖：

3-1 製作產品銷售排行榜 進階篩選選項 如執行「數字篩選/自訂篩選」或「文字篩選/自訂篩選」指令，則會出現自訂自動篩選對話視窗，讓使用者自行訂定條件。如下圖：

3-1 製作產品銷售排行榜 業績表資料排序 工作表中都有設定欄位名稱，當開始進行排序後，如果遇到欄位中有相同數值時，該如何判定先後分出高下呢？ 開啟範例檔「ch03-02.xlsx」 1

3-1 製作產品銷售排行榜 業績表資料排序 2 3

3-1 製作產品銷售排行榜 業績表資料排序 如果在篩選狀態下進行排序工作，篩選鈕則會顯示 表示排序方式 4 5

3-1 製作產品銷售排行榜 小計功能 1 2

3-1 製作產品銷售排行榜 小計功能 3

3-1 製作產品銷售排行榜 「小計」對話視窗 取代目前小計 每組資料分頁 摘要置於小計資料下方 選此項，就會覆蓋之前的小計結果。 則會將每一組小計以分頁的方式列印出來。 摘要置於小計資料下方 若勾選此項，則會將小計列及總計列置於每一組小計的下方。

3-1 製作產品銷售排行榜 運用大綱功能查看業績表小計結果

3-1 製作產品銷售排行榜 運用大綱功能查看業績表小計結果 大綱鈕：使用者會看到不同編號的符號，此符號乃依照小計的欄位來做不同層次區別。數字編號越小，顯示資料最精簡，數字越大資料顯示越多。 顯示鈕 ：按下此鈕，會將隱藏的資料顯示出來。 隱藏鈕 ：按下此鈕，會將顯示的資料隱藏起來。

3-1 製作產品銷售排行榜 使用大綱功能 按下大綱鈕「2」 1 2

3-1 製作產品銷售排行榜 製作銷售排行 讓公司藉此排行來決定下一個月的產品製造量，銷售好的產品下個月將增產，而銷售不好的產品則進行減產。 開啟範例檔「ch03-03.xlsx」 1

3-1 製作產品銷售排行榜 製作銷售排行 2 3

3-1 製作產品銷售排行榜 製作銷售排行 4 5

3-2 建立樞紐分析表 認識樞紐分析表 就是依照使用者的需求而製作的互動式資料表。 樞紐分析表是由四種元件組成，分別為欄、列、值及報表篩選。

3-2 建立樞紐分析表 認識樞紐分析表 欄與列 通常為使用者用來查詢資料的主要根據。 值 「值」乃由欄與列交叉產生的儲存格內容，即樞紐分析表中顯示資料的欄位。 報表篩選 「報表篩選」並非樞紐分析表必要的組成元件，假如設定此項，可自由設定想要查看的區域或範圍。

3-2 建立樞紐分析表 樞紐分析表的建立

3-2 建立樞紐分析表 樞紐分析表設定視窗進行設定 1.選此項 自動選取 資料來源 資料範圍 2.選此項將樞紐分析表建立於新的工作表中 表建立的 位置 3.按此鈕

3-2 建立樞紐分析表 樞紐分析表資料來源 選取表格或範圍 使用外部資料來源 設定目前活頁簿工作表中的資料清單範圍為資料來源。 設定資料來源為Excel外部的檔案或資料庫，如SQL Server、Access等等的資料檔案。

3-2 建立樞紐分析表 版面配置 樞紐分析 表功能區 資料來源的 欄位名稱 這裡將會顯 以滑鼠拖曳 示右邊拖曳 各個欄位名 的情形 稱至四個不 同的組成元 件中

3-2 建立樞紐分析表 版面配置 因為要製作各個地區的產品銷售統計表，所以接下來，將「產品代號」欄位名稱拖曳至「列」組成元件欄位中，並將「銷售地區」欄位名稱拖曳至「欄」組成元件欄位，最後再將「總金額」移至「資料」組成元件欄位即可。 1

3-2 建立樞紐分析表 版面配置 2 3

3-2 建立樞紐分析表 顯示各個地區的銷售平均值 樞紐分析表還可依照選取不同的欄為順序來變更顯示結果，如果主管要查看各個區域銷售的「最大值」狀況時，就可以利用欄位的更動，來轉變樞紐分析表的顯示。請開啟範例檔「ch03-05.xlsx」。 1

3-2 建立樞紐分析表 顯示各個地區的銷售平均值 2 3

3-3 樞紐分析圖的製作 快速建立樞紐分析圖 1 2

3-3 樞紐分析圖的製作 快速建立樞紐分析圖 3 4

3-3 樞紐分析圖的製作 快速建立樞紐分析圖 如果已經建立好樞紐分析表，那麼可以以下列的方式快速建立樞紐分析圖：

3-3 樞紐分析圖的製作 編輯樞紐分析圖 1

3-3 樞紐分析圖的製作 編輯樞紐分析圖 2 3

3-3 樞紐分析圖的製作 移動圖表位置 如果覺得樞紐分析圖與樞紐分析表放置於同一個工作表上顯的有些紛亂，那麼也可以將樞紐分析圖複製或移動到新增的工作表中。 1

3-3 樞紐分析圖的製作 移動圖表位置 2 3

3-3 樞紐分析圖的製作 變換樞紐分析圖表類型 1

3-3 樞紐分析圖的製作 變換樞紐分析圖表類型 2 3

3-4 計算業務人員的銷售獎金 HLOOKUP()函數說明 HLOOKUP()函數中的「H」即代表「水平」的意思，所以HLOOKUP()函數是在一工作表中的第一列中尋找含有某「特定值」的欄位，傳回同一欄中某一指定儲存格的值。 語法：HLOOKUP(Lookup_value, Table_array, Row_index_num, Range_lookup ) 引數名稱 說明 Lookup_value 搜尋資料的條件依據 Table_array 搜尋資料範圍 Row_index_num 指定傳回範圍中符合條件的那一列 Range_lookup 此為邏輯值。若設為True或省略，則會找出部分符合的值；若設為False，會找出完全符合的值

3-4 計算業務人員的銷售獎金 建立獎金百分比 首先使用HLOOKUP()函數來參照基本銷售業績獎金標準表，來查出每個業務人員此月可發放的獎金百分比。 請開啟範例檔「ch03-08.xlsx」 1

3-4 計算業務人員的銷售獎金 建立獎金百分比 2 3

3-4 計算業務人員的銷售獎金 建立獎金百分比 4 5

3-4 計算業務人員的銷售獎金 建立獎金百分比 6 7

3-4 計算業務人員的銷售獎金 變換百分比的顯示方式 上述範例中，使用HLOOKUP()函數參照傳回的獎金百分比，因為還未設定儲存格的格式，所以並不是以百分比方式顯示，因此讓我們來變換百分比的顯示方式。

3-4 計算業務人員的銷售獎金 變換百分比的顯示方式 2 3

3-4 計算業務人員的銷售獎金 建立累積業績資料 每個月的累積業績會隨著是否領取過累積獎金及每月的業績銷售資料而變動，所以在每個月算出業績獎金後，就必須把累積業績資料先存放在「累積銷售業績」工作表中，好讓下一個月有所依據。 1

3-4 計算業務人員的銷售獎金 建立累積業績資料 2 3

3-4 計算業務人員的銷售獎金 建立累積業績資料 4 5

3-4 計算業務人員的銷售獎金 建立累積業績資料 接下來，只要複製E3儲存格公式內容至E11儲存格之中，即可得到如下圖的成果： 6

3-4 計算業務人員的銷售獎金 計算累積獎金 IF函數 語法：IF (Logical_test, Value_if_true, Value_if_false ) 說明：以下為函數中的引數說明： 引數名稱 說明 Logical_test 此為判斷式。用來判斷測試條件是否成立 Value_if_true 此為條件成立時，所執行的程序 Value_if_false 此為條件不成立時，所執行的程序

3-4 計算業務人員的銷售獎金 使用IF( )函數計算累積獎金 1 2

3-4 計算業務人員的銷售獎金 使用IF( )函數計算累積獎金 3 4

3-4 計算業務人員的銷售獎金 使用IF( )函數計算累積獎金 5

3-4 計算業務人員的銷售獎金 總業績獎金的計算 「總業績獎金」是以「銷售業績」乘以「獎金百分比」，然後再加上「累積獎金」金額。所以用「990001」業務人員來說：計算公式為「總業績獎金=（C3*D3）+F3」。請開啟範例檔「ch03-11.xlsx」。

3-4 計算業務人員的銷售獎金 加上註解 1 2

3-4 計算業務人員的銷售獎金 加上註解 3 當滑鼠移至此儲存格上時，就會顯示出註內容

3-4 計算業務人員的銷售獎金 註解的各項功能 註解的顯示與隱藏 修改註解內容 刪除註解 如果想將註解一直顯示在儲存格旁，就可在此儲存格按下滑鼠右鍵並執行「顯示/隱藏註解」指令即可。如果想把註解隱藏起來，只要再執行一次此指令就可以了！ 修改註解內容 點選儲存格，按下滑鼠右鍵並執行「編輯註解」，註解圖文框就會出現插入點，只要直接編輯文字，在編輯完註解之後，只要任意點選一儲存格即可儲存。 刪除註解 當使用者想要移除註解時，只要選取欲刪除註解的儲存格並按下滑鼠右鍵，執行「刪除註解」指令即可。

本章結束 Q&A討論時間