第 8 章 計算業績獎金.

Presentation on theme: "第 8 章 計算業績獎金."— Presentation transcript:

1 第 8 章 計算業績獎金

2 本章學習提要 輸入日期與更改日期顯示格式 使用 HLOOKUP 函數與 LOOKUP 函數進行查表
使用 TODAY 函數計算年資並搭配 ROUND 函數做四捨五入 用篩選、排序、設定格式化的條件功能分析資料 為儲存格加上說明註解

3 本章學習提要 精明能幹的業務員是一家公司不可或缺的重要角色, 儘管公司生產了品質優良的產品, 若缺乏業務員將產品的特色與優點推廣給眾多客戶, 那麼再優良的產品也只能關在公司的倉庫中涼快！ 為了激勵業務人員的士氣, 公司通常會訂定一套業績獎金發放標準, 以鼓舞表現傑出的業務員。

4 本章學習提要 發放業績獎金的方式有很多種, 譬如從業績金額當中提撥固定的百分比當作獎金、或者規定每達到一個業績水準, 就可領取對應額度的獎金, 另外也有論件計酬的方式, 也就是每成交一筆, 就固定可得到某一數目的獎金…。

5 本章學習提要 不同的行業類別, 所制訂的業績獎金發放標準也不盡相同, 本章將探討兩種業績獎金發放的案例：一種是「依銷售業績分段核算獎金」、一種是「依業績表現分二階段計算獎金」, 看完這兩個案例之後, 相信就能幫助您將這些技巧運用到實際的情況中了。

6 本章學習提要

7 本章學習提要

8 8-1 依銷售業績分段核算獎金 企業為了有效激勵業務員衝刺業績, 時常會採取高業績伴隨高比例獎金的制度, 也就是說業績愈高, 就可以獲得愈高比例的獎金。不過也因為如此, 業績獎金的計算工作也變得繁瑣多變。

9 依銷售業績分段核算獎金 本節要為您介紹的是依照銷售業績高低, 分段給予不同比例獎金的計算技巧。現在, 請您開啟範例檔案 Ch08-01, 我們已經將宏瞻公司的獎金發放規則輸入到獎金標準工作表當中：

10 依銷售業績分段核算獎金 在本例中, 業績獎金採用分段計算的方式：當銷售業績在 10 萬元以下, 只可獲得業績的 10% 做為獎金；若是業績介於 10 ~ 15 萬之間, 則 10 萬以下的部分可獲得 10% 的獎金, 超過 10 萬未滿 15 萬的部分則可得到 12% 的獎金, 依此類推…。

11 依銷售業績分段核算獎金 舉例來說, 假設甲業務員的業績是 220,000 元, 那麼他可獲得的獎金就是：

12 計算累進差額 從剛才示範的例子各位會發現, 分段累計獎金的計算公式有點複雜。為了簡化獎金的計算工作, 我們採用「累進差額」來設計獎金的公式：也就是直接將業績金額乘上應得的最高比例, 然後再減去其中多算的部份, 就可得到實得的獎金, 而這個 "多算的部份" 就是所謂的「累進差額」：

13 計算累進差額 以上例 220,000 的銷售業績來看, 將 220,000 乘上第三段的獎金比例 15%, 再減去前面兩段多算的累進差額 6,500 (稍後說明計算公式), 同樣可以得到 26,500 的獎金。因此在繼續之前, 我們先來說明如何計算各階段獎金的累進差額。

14 計算累進差額 請各位參考 Ch08-01 獎金標準工作表中 B6：F6 儲存格中的公式：

15 計算累進差額 當銷售業績達到第一段的獎金比例時, 並不會產生累進差額, 所以其累進差額為 0。其餘各段的累進差額則可用以下公式來計算, 我們以第二段累進差額來說明：

16 計算累進差額 當銷售業績達到第二段獎金比例時, 若直接將銷售業績乘上 12%, 則其中屬於第一段的部份 (也就是 100,000 以下的部份) 會多算 2% (12% - 10%), 若以圖形來表達, 則下頁圖中的 也就是 100,000 * 2% = 2,000。由於第二段之前所累計累進差額為 0, 所以第二段累進差額實為 0 + 2,000 = 2,000。

17 計算累進差額 當銷售業績達到第三段獎金比例時, 則原屬第二段的部份 (150,000 以下) 會多算 3% (15%-12%), 圖中的 也就是 150,000 * 3 % = 4,500, 再加上之前累計的累進差額 2,000, 所以結果為 2, ,500 = 6,500。第四段、第五段的累進差額也都是依照相同的方式推算出來的。

18 計算累進差額

19 查詢獎金比例 請切換到 Ch08-01 的獎金計算工作表, 宏瞻公司每個業務員的銷售業績資料已經輸入到 B 欄當中, 現在我們要參照獎金標準工作表, 將對應的獎金比例填入獎金計算工作表的 C 欄。在此, 我們要使用到一個水平查表函數：HLOOKUP。

20 HLOOKUP 函數的用 HLOOKUP 函數可讓我們在表格的第一列中尋找含有某個值的欄位, 然後再傳回同一欄中某列儲存格的值。HLOOKUP 的格式為：

21 HLOOKUP 函數的用 Lookup_value：就是搜尋範圍第一列中所要搜尋的值, 可以是數字、位址或文字。
Table_array：指定要搜尋的資料範圍, 也可以是一個定義好的儲存格範圍名稱。 Row_index_num：是一個數值, 表示要傳回第幾列的值。

22 HLOOKUP 函數的用 Range_lookup：為一個邏輯值, 可指定尋找完全相符或部份相符的值。當此值為TRUE 或省略的時候, 會傳回部份相符的值, 也就是若找不到完全相符的值, 會傳回僅次於 Lookup_value 的值。 而當此值為 FALSE 時, 則表示要尋找完全相符的值, 若找不到, 就會傳回錯誤值 #N/A。另外, 當Range_lookup 值為 TRUE 時, Table_array 第一列中的數值必須按照遞增排列, 這樣搜尋結果才會正確。

23 HLOOKUP 函數的用 以下面的例子來說, 我們要查詢檸檬的數量, 可在儲存格 B7 輸入公式 "=HLOOKUP(B6, B1：G4, 4, FALSE)", 由於 B6 的值為 "檸檬", 在 B1：G4 範圍中找到 "檸檬" 後, 在 "檸檬" 所屬的那一欄中, 第 4 列的值為 "170", 故 B7 公式的運算結果為 "170 " ：

24 HLOOKUP 函數的用

25 查詢獎金比例 了解 HLOOKUP 函數的用法之後, 我們回到獎金計算工作表, 開始依據 B 欄的銷售業績數字來查詢應得的獎金比例。我們以在 C3 儲存格求出第一位業務員的獎金比例來說明, 請在 C3 儲存格中輸入公式：

26 查詢獎金比例

27 查詢獎金比例 由於每個業務員獎金比例求算方式都相同, 因此可如下操作, 將 C3 的公式複製到 C4：C30 當中：

28 查詢獎金比例 接著, 您可以按下常用頁次數值區的百分比樣式鈕 , 讓 C3：C30 的數值改以百分比樣式呈現。

29 查詢累進差額 累進差額的查詢方式與獎金比例相同, 一樣是使用 HLOOKUP 函數到獎金標準工作表中進行搜尋。不過, 為了讓公式看起來更易懂, 我們要在公式中使用名稱。

30 定義儲存格範圍名稱 請切換到獎金標準工作表。B4：F6 是我們進行查表的儲存格範圍, 現在我們來為它取個好懂、容易記住的名稱吧！請選取 B4：F6, 然後按一下名稱方塊, 輸入 "查表範圍" 做為它的名稱, 完成後請按下 鍵：

31 定義儲存格範圍名稱

32 建立累進差額查詢公式 接著, 請切換至獎金計算工作表, 選取 D3 儲存格開始輸入查詢累進差額的公式：

33 建立累進差額查詢公式

34 建立累進差額查詢公式 拉曳 D3 儲存格的填滿控點至 D30, 則每個人的獎金累進差額就通通查出來了：

35 計算業績獎金 知道各業務員可得的獎金比例及累進差額之後, 計算業績獎金就不是一件難事了。我們先在 E3 儲存格輸入第一位業務員 "陳艾齡" 的業績獎金計算公式：銷售業績 × 獎金比例 － 累進差額 (=B3*C3-D3), 然後再將 E3 的公式複製到 E4：E30, 便可算出全部業務員的業績獎金：

36 計算業績獎金

37 計算業績獎金 完成這張工作表之後, 各位可將 B 欄 (銷售業績) 的值全部歸零, 然後另存一份含有公式及獎金標準的空白檔案, 未來每個月 (或固定的週期) 只要將業務員的銷售業績填入獎金計算工作表的 B 欄當中, 就可自動將獎金比例、累進差額、業績獎金通通算出來, 相當省事。 而如果業績獎金發放標準有所變動, 也只要到獎金標準工作表中修改數值, 便可按照新標準重新計算個員的業績獎金了。

38 改變獎金計算方式 由於銷售業績直接關係到個人的業績獎金, 所以業務員都會賣力的衝刺業績。但有時候業務員難免會有投機或鬆懈的心理, 譬如說, 這個月的業績特別好, 抽了高比例的獎金, 到了下個月, 業務員就鬆懈下來；或者業務員會蓄意將業績集中在某個月, 以衝高業績、得到較高的獎金比例…。

39 改變獎金計算方式 為了避免發生諸如此類的情況, 宏瞻公司決定依照實際狀況來變動部份遊戲規則, 將獎金的計算方式改成以 2 個月的平均銷售業績來做計算。例如 2 月份的獎金就是根據 1、2 月平均業績來計算、3 月份獎金則根據 2、3 月平均業績來計算…依此類推, 以督促業務員持續努力衝刺業績。

40 改變獎金計算方式 現在, 計算規則改變了, 我們的工作表當然也要有所更動。其實並不難, 只要在獎金計算工作表當中加上兩欄, 分別存放 "上月" 和 "本月" 業績, 然後在原來的銷售業績欄算出這兩欄的平均之後, 再依照平均業績來查詢可得的獎金比例, 就可算出業務員的獎金了！ 請您開啟範例檔案 Ch08-02, 然後跟著下面的說明來做修改：

41 改變獎金計算方式 選取獎金計算工作表的 B、C 兩欄, 接著在常用頁次儲存格區按下插入鈕右側箭頭選擇『插入工作表欄』命令：

42 改變獎金計算方式 分別在插入的兩欄中輸入 "上月" 及 "本月" 業績, 然後將 D 欄改成 "兩月平均", 並建立公式計算 B、C 兩欄的平均 (如在 D3 儲存格中輸入 "=(B3+C3)/2"), 如此就可按照新的規則算出每個人應得的獎金囉：

43 改變獎金計算方式 假如在計算業績的時候, 想讓 "上月" 與 "本月" 的業績比重為 3：7 (也就是以本月表現為重, 但參考上個月的表現), 則我們可將 D3 的公式改成 "=B3 * C3 * 0.7", 再複製到 D4：D30。

44 改變獎金計算方式 等到下個月要來計算獎金的時候, 只要將 C 欄的 "本月" 業績複製到 B 欄的 "上月" 業績, 然後在 C 欄輸入新月份的業績數據, 就又可輕鬆完成獎金的計算工作了。您可開啟範例檔案 Ch08-03 來查看成果。

45 8-2 依業績表現分二階段計算獎金 本節的範例要來計算推廣健身中心會員的業務員獎金, 此外還要運用計算出來的結果做進一步的分析工作, 譬如找出哪些業務員具有潛力, 值得公司好好栽培等等…。

46 依業績表現分二階段計算獎金 請開啟範例檔案 Ch08-04, 業績標準工作表存放招募會員業績獎金的發放標準, 一共分成兩階段來計算業績獎金：第一階段採取論件計酬的方式, 也就是說只要成功推廣一人成為終身會員, 可得 500 元獎金、推廣一人成為 5 年期會員則得 100 元獎金；之後再按照第一階段所得到的獎金金額來核發第二階段的累進獎金。 另外, 計算獎金工作表則已建立好各區業務員的各項資料, 待會兒我們便要在此完成業績獎金的計算工作：

47 依業績表現分二階段計算獎金

48 依業績表現分二階段計算獎金

49 輸入到職日 計算獎金工作表的 C 欄要用來存放業務員的到職日, 我們先來看看如何在工作表中輸入日期資料。當您在儲存格中輸入日期 (或時間) 資料時, 必須以 Excel 能接受的格式輸入才會當作是日期 (或時間), 否則會被當成文字資料。底下列舉 Excel 可接受的日期輸入格式：

50 輸入到職日

51 輸入到職日 接著請你依照下圖, 將所有業務員的到職日輸入到 C 欄中：

52 直接輸入民國 由於 Excel 預設使用西元年, 若想直接輸入民國年份, 例如 "87/9/10", 不被 Excel 判斷成 1987/9/10, 那麼請在輸入日期的最前面加上 "R" (文字與數字間不能空格), 例如 "R95/5/20", 那麼儲存格會顯示 95/5/20, 但資料編輯列則顯示 2006/5/20。

53 直接輸入民國 請注意, 要直接輸入民國年, 請先確認儲存格格式為通用格式 (在常用頁次的數值區中, 可得知儲存格的格式), 否則 Excel 可能會判斷錯誤。

54 更改日期顯示格式 輸入完成後, 可以依照自己的需要更改日期的顯示格式, 例如要將「西元年」改成「民國年」顯示, 則請選取 C3：C13, 然後到常用頁次數值區按下數值格式右側箭頭選擇『其他數值格式』命令：

55 更改日期顯示格式

56 更改日期顯示格式

57 計算年資 年資的計算就是將目前的日期減去到職日期, 然後將這段期間的天數再除以一年365 天, 即可求出。以計算第一位業務員 "劉珮珊" 的年資為例, 請在 D3 儲存格中輸入公式：

58 計算年資 算出年資後, 再利用 ROUND 函數做四捨五入, 以增加報表的美觀與易讀性：

59 計算年資

60 ROUND 函數的用法 ROUND 函數可按照您指定的位數, 將數字四捨五入。其格式如下：

61 計算第一階段獎金 現在, 我們要開始計算業績獎金囉！首先是將每個業務員招募了幾位終身會員、 幾位 5 年期會員的資料輸入到計算獎金工作表的 E、F 欄當中, 然後就可以來計算第一階段的獎金了。請開啟範例檔案 Ch08-05, 我們已事先輸入好所有業務員招募的 資料：

62 計算第一階段獎金 第一階段獎金必須參照到業績標準工作表中的內容, 我們以計算第一位業務員 "劉珮珊" 為例來說明, 請在 G3 儲存格中輸入公式：

63 計算第一階段獎金 接著, 請將公式中的 D3、D4 改成絕對參照位址 "$D$3"、"$D$4", 然後把公式複製到 G4：G13, 即可完成第一階段獎金的計算工作。

64 計算第二階段獎金 剛才已根據業務員的終身會員、5 年期會員人數算出第一階段的獎金, 接下來, 還要依據第一階段的獎金來加發第二階段的累進獎金。請接續上例, 或開啟範例檔案 Ch08-06：

65 計算第二階段獎金

66 計算第二階段獎金 在計算之前, 我們先來認識一下 LOOKUP 函數, 因為待會兒要用這個函數來幫我們查出每個業務員可得到多少第二階段的獎金。

67 LOOKUP 函數的用法 LOOKUP 函數會在單一欄 (或單一列) 的範圍中尋找指定的搜尋值, 然後傳回另一個單一欄 (或單一列) 範圍中同一個位置的值。LOOKUP 函數的格式如下：

68 LOOKUP 函數的用法 Lookup_value：即為所要尋找的值。Lookup_value 可以是文字、數字、邏輯值等。
Lookup_vector：是單一列或單一欄的儲存格範圍。Lookup_ vector 中的值須以遞增排列, 否則結果會不正確。 Result_vector：是單一列或單一欄的範圍。它的大小要與 Lookup_vector 相同。

69 LOOKUP 函數的用法 以下圖而言, 在儲存格 C2 中輸入公式 "=LOOKUP (B2, A9：A12, B9：B12)" 的計算結果 為 "甲"：

70 LOOKUP 函數的用法

71 計算第二階段獎金 明白 LOOKUP 函數的用法後, 我們開始在範例檔案 Ch08-06 計算獎金工作表中輸入第二階段獎金的計算公式, 請在 H3 輸入如下的公式：

72 計算第二階段獎金

73 計算第二階段獎金 當 LOOKUP 函數無法在查詢範圍中找到完全符合的值時, 會找出最接近但不超過的值。例如：業務員 "劉珮珊" 第一階段的獎金為 13,500, 介於 12,000 ~ 20,000 之間, 因此會採用 12,000 而找出對應 12,000 的第二階段獎金為 8,000。

74 計算第二階段獎金 接著, 請將 H3 的公式複製到 H4：H13 當中：

75 計算第二階段獎金 會發生 "#N/A" 錯誤, 是因為當搜尋值小於搜尋範圍中的最小值, LOOKUP 函數就會傳回錯誤值。例如 "王勝良" 第一階段的獎金為 3,700 元, 少於第二階段最低業績標準 5,000 (即業績標準工作表的 C7 儲存格), 因此傳回 "#N/A" 錯誤訊息。

76 計算第二階段獎金 要避免 "#N/A" 的錯誤訊息, 我們可在第二階段獎金的計算公式中加入 IF 函數來判斷：若低於最低業績標準, 獎金就直接填 0, 不用查表了。現在, 請您將 H3 的公式修如下, 然後再將 H3 的公式拉曳複製到 H13, 就不會出現 "#N/A" 的錯誤訊息了：

77 合計獎金 既然兩個階段的獎金都算出來了, 接著就可以來計算 I 欄的 "獎金合計" 囉！要將第一、二階段的獎金相加, 相信這對你來說應該很容易吧！您可以在 I3 儲存格中輸入公式 "=G3+H3", 然後複製公式至 I13, 整個業績獎金的計算作業就完成了：

78 合計獎金

79 合計獎金 在這份工作表中, 您也可以依各公司敘薪方式做調整, 例如, 增加 "本薪"、"加給" 欄位, 則此工作表不但可以計算業績獎金, 還可以直接將當月應付薪資都計算出來哦！

80 找出超級業務員 業績獎金計算出來就滿足了嗎？其實我們還可藉由這些計算結果做更進一步的 分析、獲得更多寶貴的資訊喔！譬如, 可以找出資歷很淺, 但是業績表現卻很出色的業務員, 日後可多加栽培訓練。 請開啟範例檔案 Ch08-07 的計算獎金工作表, 然後到常用頁次編輯區按下排序與篩選鈕執行『篩選』命令, 現在我們要來找出「年資小於 4 年, 但領到 20,000 元以上獎金」的優秀業務員：

81 找出超級業務員 拉下年資欄的自動篩選鈕, 執行『數字篩選/小於』命令：

82 找出超級業務員 在開啟的自訂自動篩選交談窗中做如右的設定：

83 找出超級業務員

84 找出超級業務員 接著使用相同的方法, 拉下獎金合計欄的自動篩選鈕, 執行『數字篩選/大於』命令, 然後在自訂自動篩選交談窗中將條件設定為「大於 20000」, 我們所要的結果便篩選出來了：

85 找出超級業務員 另外, 您也可以利用篩選功能, 將業務員資料依 "區別" 顯示出來, 或者是篩選出 「終身會員人數大於某數量」的業務員…等等, 就看你的需求來靈活應用囉！ 若要移除工作表中的自動篩選鈕, 只要到常用頁次編輯區中按下排序與篩選鈕, 再次執行 『篩選』命令即可。

86 分區按業績獎金做排序 假設, 現在我們又想將同一地區的業務員資料排在一塊, 並且按照獎金高低由大排到小, 那麼該怎麼做呢？
請您選取清單中的任一個儲存格, 然後在常用頁次編輯區按下排序與篩選鈕執行 『自訂排序』命令, 開啟排序交談窗：

87 分區按業績獎金做排序

88 分區按業績獎金做排序

89 標示出獎金高於平均值的業務員 假設我們想將「獎金合計高於平均值的業務員」標示出來, 怎麼做呢？這個問題可以利用設定格式化的條件功能來處理。接續上例或開啟範例檔案 Ch08-08： 請選取 B3：B13 儲存格, 到常用頁次的樣式區按下設定格式化的條件鈕選擇『新增規則』命令：

90 標示出獎金高於平均值的業務員

91 標示出獎金高於平均值的業務員 在新增格式化規則交談窗中建立本例的規則「獎金合計高於平均值」, 及要套用的格式：

92 標示出獎金高於平均值的業務員

93 標示出獎金高於平均值的業務員

94 標示出獎金高於平均值的業務員 分別按下兩個交談窗的確定鈕, 您就可以從工作表中清楚看出哪幾位業務員合計獎金高於平均值：

95 加入說明註解 若擔心日後忘記剛剛加上粗體、橘色文字的用意, 可以利用註解功能來為儲存格加上說明。請繼續如下練習：

96 加入說明註解 請選取 B2 儲存格, 然後按右鈕選擇『插入註解』命令, 儲存格附近即會出現註解圖文框讓您輸入文字：

97 加入說明註解 輸入註解內容時, 無需按 鍵換 行, Excel 會自動換行。如果覺得註解圖文框 的範圍太狹窄, 也可以直接拉曳控點來調整大小；拉曳邊框則可移動註解的位置。

98 加入說明註解 輸入完畢後, 請用滑鼠點選圖文框以外的地方即可完成, 且註解會自動被隱藏起來。

99 加入說明註解 若要修改註解內容, 可在已加入註解的儲存格上按右鈕執行『編輯註解』命令, 重 新進入註解的編輯狀態進行修改, 修改完畢後, 同樣再點一下圖文框以外的地方即可結束編輯狀態。 假如是要刪除註解, 同樣先選取欲刪除註解的儲存格, 再按右鈕選擇『刪除註解』命令, 就可以將之刪除。範例完成結果可參考範例檔案 Ch08-09。

100 後記 實際生活中計算獎金的方式千變萬化, 不過只要掌握這些技巧, 相信日後您遇到獎金計算的問題時, 應該都能迎刃而解了！
除了本章所舉的兩個範例之外, 在保險業、房屋仲介業…等, 也常會遇到類似的問題, 您可依實際的狀況, 將獎金計算規則與業務員資料建立到工作表中, 然後設計好計算公式、適當的搭配使用函數, 而後續計算問題就通通交給 Excel 來完成吧！