Excel的高效秘訣!超好用的公式VLOOKUP,不同表單也能快速整合


(編按:作者熱愛歷史,這次Excel教學以曹操為主角,帶領大家學會最重要的Excel功能「VLOOKUP」。)

曹操自知自家的軍事力量仍遠遠不如袁紹集團,且以小搏大本非易事,所以他決定設計一個「業務獎金激勵機制」來激勵手下的大將們,以吸引更多人才上門。這個激勵機制會將大將的業績分級,按照級距發放不同的獎金比例。

1. 製作業績獎金發放參考表

為求之後計算方便,曹操先把整個衛國股份有限公司的業績獎金辦法輸入到Excel表單,做為未來發放獎金的參考。

經理人

2. 運用VLOOKUP函數,比對出每位大將應得的獎金比例

除了獎金發放辦法,曹操也將武將的銷售訂單和金額輸入Excel,再運用VLOOKUP函數幫忙把第四季的銷貨統計表的獎金比例自動計算出來。

經理人

在獎金比例那一欄(F10)開始,插入函數VLOOKUP。

經理人

這時候會跳出新視窗,讓你填寫四個欄位,以組成公式。

經理人

公式的說明如下:
Lookup_value —叫Excel在之後指定的資料裡,比對的資料,可能是數值或文字。在此輸入D10,是指讓Excel查找符合「25000」的資料。

Table_value —要Excel查找資料的範圍,也就是告訴Excel要在哪裡比對資料。在此輸入「E3:F7」,表示請Excel在最小業績和獎金比例這兩欄中查詢資料。

Col_index_num —在Table_value找到對應的資料之後,要回傳第幾欄的訊息。在此輸入2,表示將業績資訊拿去比對E3:F7後,要回傳指定範圍的第二欄資訊,也就是獎金比例。

Range_lookup —輸入TRUE或是省略不寫,表示要查找Table_value首欄中的近似值,如果找不到精確匹配值,則回傳小於lookup_value的指定欄位資料;FALSE表示要找出完全符合的值。

經理人

最後的公式寫為 =VLOOKUP(D10, $E$3:$F$7,2,TRUE) ,意思是請Excel在業績獎金發放參考表(E3:F7)中,找尋業務代表賺得的營收(D欄)可以獲得的獎金比例(指定查找範圍的第二欄)。因為25000(D10)小於50001,最接近的值就是0,回傳的獎金比例為4%。

3. 公式自動填滿

確認公式無誤後,點選儲存格F10右下角的圓點,直接下拉。

經理人

公式就會自動填滿下面的儲存格,將其他武將的獎金比例計算出來。

經理人

4. 計算獎金

在G10儲存格打入=D10*F10,這時候獎金就會自動計算出來,和剛剛依樣下拉整個儲存格就會將所有武將的獎金一併計算出來!

經理人

延伸課程 / 「資深經理人教你,超強Excel數據力」一張數字圖表,成功說服老闆和客戶!

延伸閱讀 /

1. 絕對用得到!6個Excel小訣竅讓報表更專業,還有14個超棒快速鍵

2. Excel聰明理財兩大招!連結網頁資料,直接匯入股票數據、分析趨勢

3. 2個Excel小技巧,幫你整理投資數據、管理資金!


Steve 蘇書平

前台灣微軟資深業務應用經理

曾於台灣微軟管理雲端IoT物聯網業務,具18年以上資訊產業工作經歷,範疇涵蓋研發、行銷、專案管理、客服等部門主管。同為先行智庫執行長、為你而讀共同推動者、台大TEC創新創業中心業師、台大管理碩士學分班創新創業教師。

延伸閱讀