基于Excel2007函數的員工銷售業績查詢及提成計算操作

徐蒞

摘要：Microsoft Office Excel是一個功能強大的辦公及業務處理工具，內含10類共300余個函數。通過這些函數的使用，可有效地進行數據處理和統計。在實際工作中，常常有多元的數據處理需求，例如在成千上萬條記錄中查找特定的信息——此時查找和引用相應的函數就能快速準確地達到預期效果。

關鍵詞：查詢；函數；數量；銷售產品；數據表；任務；提成

Microsoft Office系列辦公軟件以其強大的功能和優良的性能成為人們辦公軟件的首選，而其中的Excel更是電子表格領域的權威，很方便地對數據、公式、函數和圖像進行處理，被廣泛地應用于文秘、經濟、管理、統計、財會、審計、金融、工程、數據處理及相關行業等多個領域。

如在實際工作中常常需要在非常多的記錄中查找特定的信息，這是非常麻煩的事情，這時要涉及到各種查找和引用函數。查找和引用函數可在數據清單或數據表中查找特定的數值，或者查找某一個單元格的引用。如果熟練掌握，可靈活地應用到實際工作和生活中。

統計某公司員工某年某月的銷售情況，現希望查詢，當輸入員工姓名和銷售產品名稱時，可以自動查詢員工銷售該產品的數量，根據產品規定銷售底限判斷是否完成了任務，最后根據銷售額確定其銷售每種產品應得的提成，并計算出總的獎金提成。

設表1：打開Excel，首先要有所有員工及銷售各種產品數量的數據統計表，數據統計表：所有員工姓名為列，銷售不同的產品為行，內容為銷售各種產品名數量的二維表。

1 首先根據要求，將規定底限、需要查找的條件及提成輸入到數據表中

這里需要準備3張數據表，表1，員工銷售情況數據統計表；表2：規定銷售各個產品數量的任務底限數據表；表3：確定銷售提成的表。這3張表當列出查找條件和已有數據來查找某員工或所有員工及銷售某產品的數量和其他信息。

2 查找某員工銷售某產品的數量

在單元格中插入公式“=INDEX（Array，MATCH（Lookup_value1， Lookup_array1，0），MATCH（Lookup_value2，Lookup_array2，0））”，按下Enter鍵后得到計算結果。

注：Array：表1區域，即單元格區域，這里指所有員工及銷售各種產品數量的區域。

Lookup_value1： 獲取某員工姓名

Lookup_value2：獲取該員工所銷售的產品名

Lookup_array1：所有員工姓名的區域，這里是列

Lookup_array2：所有產品名的區域，這里是行

在此公式中，應用了INDEX函數和MATCH函數。要獲取輸入的員工姓名和銷售產品所對應的銷售數量，首先使用MATCH函數返回員工姓名 （Lookup_value1）在Lookup_array1區域中所在的行號，以及產品名（Lookup_value2）在區域Lookup_array2中所在的列標，然后再利用INDEX函數返回單元格區域（Array）中該行號與列標交叉處單元格的值。

3 判斷某員工銷售某產品的數量是否完成任務

設表2：在表1中，即在所有員工及銷售各種產品數量的數據二維表內增加“規定銷售產品數量的任務底限”一行，針對每一產品應完成的銷售最低任務值。

在單元格中插入公式 “=CHOOSE（IF（INDEX（Array，Row_num，MATCH（Lookup_value2，Lookup_array2，0））>Lookup_value3，1，2），”未完成”，”完成”）”，按下Enter鍵后得到計算結果。

Array：表2區域，即單元格區域，這里指所有員工及銷售各種產品數量及規定銷售產品數量的任務底限的區域。

注：Row_num：行號，即規定銷售各產品數量的任務底限數量

Lookup_value2：獲取該員工所銷售的產品名

Lookup_array2：所有產品名的區域，這里是行

Lookup_value3：某員工銷售某產品的數量

判斷某員工銷售某產品的數量是否完成任務，即判斷銷售數量中的結果是否超過了規定任務底限中的值，因此首先使用MATCH函數獲取產品所在的列號，即產品所在的列號，再使用IF函數來進行判斷，使用CHOOSE函數返回值，若超過了規定底限，則返回“完成”，否則返回“未完成”。

4 計算某員工銷售某產品的應得提成

設表3：確定銷售提成的表。如設提成比列為第3列。當提成為3%，銷售下限的數量，上限的數量；當提成為5%，銷售下限的數量，上限的數量；當提成為8%，銷售下限的數量，上限的數量；當提成為10%，銷售下限的數量，上限的數量；

在單元格中插入公式“=VLOOKUP（Lookup_value3，Table_array，3）”，按下Enter鍵后得到計算結果。

注：Lookup_value3：某員工銷售某產品的數量

Table_array：數據表3：即銷售提成的表

計算某員工銷售某產品的應得提成，即比較銷售數量與銷售提成中的上下限，使用VLOOKUP函數進行豎直查找，返回Table_array區域中第3列的值，即提成的數字。如當某員工銷售某產品的數量超過提成為10%的數量，進行查找后得出應得提成為10%，即0.1。

5 計算某員工銷售所有產品應得的獎金提成

設第一行第一列為第一個員工，同行第二列為第一個員工銷售的第一個產品；同行第三列為第一個員工銷售的第二個產品；同行第四列為第一個員工銷售的第三個產品……；在單元格中插入公式“=B1*VLOOKUP（B1，Table_array，3）+C1*VLOOKUP（C1，Table_array，3）+D1*VLOOKUP（D1， Table_array，3）+E1*VLOOKUP（E1， Table_array，3）+……”，

按下Enter鍵后得到計算結果。

注：Table_array：數據表3：即銷售提成的表

要計算某員工的獎金提成，將每種產品的銷售數量與提成比例相乘可得到銷售該產品的獎金提成，再將各個產品的銷售提成相加就是該員工的獎金提成總和。

6 計算其他員工銷售產品應得的獎金提成

利用自動填充功能，當按下Enter鍵后得到計算結果，然后拖動此單元格，使其自動填充其他員工銷售產品應得的計算獎金提成的單元格中。

現在完成了使用引用和查詢函數計算員工獎金評定的操作。此時只需輸入條件后，即可查找某員工或所有員工及銷售某產品的數量及獎金提成情況和其他信息。

上述所用函數可以在數據清單或數據表中查找特定的數據，要達到準確輸出，就要查詢準確，數據準確，函數參數引用準確。

說明：

INDEX函數：得到指定的內容

用途：返回表或區域中的值或值的引用。函數INDEX有兩種形式：數組形式和引用形式。

數組形式：返回指定單元格或單元格數組的元素值，此元素由行序號和列序號的索引值給定。當函數INDEX的第一個參數為數組常量時，使用數組形式。

格式與參數：INDEX（Array，Row_ num，Column_num）

MATCH函數：數組中查找值

用途：返回在指定方式下與指定數值匹配的數組中元素的相應位置。

格式與參數：MATCH（Lookup_value，Lookup_array，Match_type）

CHOOSE函數：列值中查找值

用途：使用Index—num返回數值參數列表中的數值。使用CHOOSE可以根據索引號從最多254個數值中選擇一個。

格式與參數：CHOOSE（lndex- num，Valuel，Value2，…）

VLOOKUP函數：豎直查找

用途：在表格或數值的首列查找指定的數值，并在表格或數組中指定行的同一列中返回一個數值。

格式與參數：VLOOKUP（Lookup_value，Table_array，Col_index_num，Range_lookup）

參考文獻：

[1]陳錫盧，楊明輝.Excel效率手冊[M].北京：清華大學出版社，2014.

[2]Excel Home編著.Excel2007應用大全[M].北京：人民郵電出版社，2012.

[3]Excel Home. Excel 2010函數與公式實戰技巧精粹[M].北京：人民郵電出版社，2014.

[4]伍昊.你早該這么玩Excel[M].北京：北京大學出版社，2011.

[5]吳新瑛.EXCEL函數實例[M].上海：上?？茖W技術出版社，2009.