基于Excel2007函數(shù)的員工銷售業(yè)績查詢及提成計(jì)算操作

徐蒞

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

關(guān)鍵詞：查詢；函數(shù)；數(shù)量；銷售產(chǎn)品；數(shù)據(jù)表；任務(wù)；提成

Microsoft Office系列辦公軟件以其強(qiáng)大的功能和優(yōu)良的性能成為人們辦公軟件的首選，而其中的Excel更是電子表格領(lǐng)域的權(quán)威，很方便地對(duì)數(shù)據(jù)、公式、函數(shù)和圖像進(jìn)行處理，被廣泛地應(yīng)用于文秘、經(jīng)濟(jì)、管理、統(tǒng)計(jì)、財(cái)會(huì)、審計(jì)、金融、工程、數(shù)據(jù)處理及相關(guān)行業(yè)等多個(gè)領(lǐng)域。

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

統(tǒng)計(jì)某公司員工某年某月的銷售情況，現(xiàn)希望查詢，當(dāng)輸入員工姓名和銷售產(chǎn)品名稱時(shí)，可以自動(dòng)查詢員工銷售該產(chǎn)品的數(shù)量，根據(jù)產(chǎn)品規(guī)定銷售底限判斷是否完成了任務(wù)，最后根據(jù)銷售額確定其銷售每種產(chǎn)品應(yīng)得的提成，并計(jì)算出總的獎(jiǎng)金提成。

設(shè)表1：打開Excel，首先要有所有員工及銷售各種產(chǎn)品數(shù)量的數(shù)據(jù)統(tǒng)計(jì)表，數(shù)據(jù)統(tǒng)計(jì)表：所有員工姓名為列，銷售不同的產(chǎn)品為行，內(nèi)容為銷售各種產(chǎn)品名數(shù)量的二維表。

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

這里需要準(zhǔn)備3張數(shù)據(jù)表，表1，員工銷售情況數(shù)據(jù)統(tǒng)計(jì)表；表2：規(guī)定銷售各個(gè)產(chǎn)品數(shù)量的任務(wù)底限數(shù)據(jù)表；表3：確定銷售提成的表。這3張表當(dāng)列出查找條件和已有數(shù)據(jù)來查找某員工或所有員工及銷售某產(chǎn)品的數(shù)量和其他信息。

2 查找某員工銷售某產(chǎn)品的數(shù)量

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

注：Array：表1區(qū)域，即單元格區(qū)域，這里指所有員工及銷售各種產(chǎn)品數(shù)量的區(qū)域。

Lookup_value1： 獲取某員工姓名

Lookup_value2：獲取該員工所銷售的產(chǎn)品名

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

Lookup_array2：所有產(chǎn)品名的區(qū)域，這里是行

在此公式中，應(yīng)用了INDEX函數(shù)和MATCH函數(shù)。要獲取輸入的員工姓名和銷售產(chǎn)品所對(duì)應(yīng)的銷售數(shù)量，首先使用MATCH函數(shù)返回員工姓名 （Lookup_value1）在Lookup_array1區(qū)域中所在的行號(hào)，以及產(chǎn)品名（Lookup_value2）在區(qū)域Lookup_array2中所在的列標(biāo)，然后再利用INDEX函數(shù)返回單元格區(qū)域（Array）中該行號(hào)與列標(biāo)交叉處單元格的值。

3 判斷某員工銷售某產(chǎn)品的數(shù)量是否完成任務(wù)

設(shè)表2：在表1中，即在所有員工及銷售各種產(chǎn)品數(shù)量的數(shù)據(jù)二維表內(nèi)增加“規(guī)定銷售產(chǎn)品數(shù)量的任務(wù)底限”一行，針對(duì)每一產(chǎn)品應(yīng)完成的銷售最低任務(wù)值。

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

Array：表2區(qū)域，即單元格區(qū)域，這里指所有員工及銷售各種產(chǎn)品數(shù)量及規(guī)定銷售產(chǎn)品數(shù)量的任務(wù)底限的區(qū)域。

注：Row_num：行號(hào)，即規(guī)定銷售各產(chǎn)品數(shù)量的任務(wù)底限數(shù)量

Lookup_value2：獲取該員工所銷售的產(chǎn)品名

Lookup_array2：所有產(chǎn)品名的區(qū)域，這里是行

Lookup_value3：某員工銷售某產(chǎn)品的數(shù)量

判斷某員工銷售某產(chǎn)品的數(shù)量是否完成任務(wù)，即判斷銷售數(shù)量中的結(jié)果是否超過了規(guī)定任務(wù)底限中的值，因此首先使用MATCH函數(shù)獲取產(chǎn)品所在的列號(hào)，即產(chǎn)品所在的列號(hào)，再使用IF函數(shù)來進(jìn)行判斷，使用CHOOSE函數(shù)返回值，若超過了規(guī)定底限，則返回“完成”，否則返回“未完成”。

4 計(jì)算某員工銷售某產(chǎn)品的應(yīng)得提成

設(shè)表3：確定銷售提成的表。如設(shè)提成比列為第3列。當(dāng)提成為3%，銷售下限的數(shù)量，上限的數(shù)量；當(dāng)提成為5%，銷售下限的數(shù)量，上限的數(shù)量；當(dāng)提成為8%，銷售下限的數(shù)量，上限的數(shù)量；當(dāng)提成為10%，銷售下限的數(shù)量，上限的數(shù)量；

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

注：Lookup_value3：某員工銷售某產(chǎn)品的數(shù)量

Table_array：數(shù)據(jù)表3：即銷售提成的表

計(jì)算某員工銷售某產(chǎn)品的應(yīng)得提成，即比較銷售數(shù)量與銷售提成中的上下限，使用VLOOKUP函數(shù)進(jìn)行豎直查找，返回Table_array區(qū)域中第3列的值，即提成的數(shù)字。如當(dāng)某員工銷售某產(chǎn)品的數(shù)量超過提成為10%的數(shù)量，進(jìn)行查找后得出應(yīng)得提成為10%，即0.1。

5 計(jì)算某員工銷售所有產(chǎn)品應(yīng)得的獎(jiǎng)金提成

設(shè)第一行第一列為第一個(gè)員工，同行第二列為第一個(gè)員工銷售的第一個(gè)產(chǎn)品；同行第三列為第一個(gè)員工銷售的第二個(gè)產(chǎn)品；同行第四列為第一個(gè)員工銷售的第三個(gè)產(chǎn)品……；在單元格中插入公式“=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鍵后得到計(jì)算結(jié)果。

注：Table_array：數(shù)據(jù)表3：即銷售提成的表

要計(jì)算某員工的獎(jiǎng)金提成，將每種產(chǎn)品的銷售數(shù)量與提成比例相乘可得到銷售該產(chǎn)品的獎(jiǎng)金提成，再將各個(gè)產(chǎn)品的銷售提成相加就是該員工的獎(jiǎng)金提成總和。

6 計(jì)算其他員工銷售產(chǎn)品應(yīng)得的獎(jiǎng)金提成

利用自動(dòng)填充功能，當(dāng)按下Enter鍵后得到計(jì)算結(jié)果，然后拖動(dòng)此單元格，使其自動(dòng)填充其他員工銷售產(chǎn)品應(yīng)得的計(jì)算獎(jiǎng)金提成的單元格中。

現(xiàn)在完成了使用引用和查詢函數(shù)計(jì)算員工獎(jiǎng)金評(píng)定的操作。此時(shí)只需輸入條件后，即可查找某員工或所有員工及銷售某產(chǎn)品的數(shù)量及獎(jiǎng)金提成情況和其他信息。

上述所用函數(shù)可以在數(shù)據(jù)清單或數(shù)據(jù)表中查找特定的數(shù)據(jù)，要達(dá)到準(zhǔn)確輸出，就要查詢準(zhǔn)確，數(shù)據(jù)準(zhǔn)確，函數(shù)參數(shù)引用準(zhǔn)確。

說明：

INDEX函數(shù)：得到指定的內(nèi)容

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

數(shù)組形式：返回指定單元格或單元格數(shù)組的元素值，此元素由行序號(hào)和列序號(hào)的索引值給定。當(dāng)函數(shù)INDEX的第一個(gè)參數(shù)為數(shù)組常量時(shí)，使用數(shù)組形式。

格式與參數(shù)：INDEX（Array，Row_ num，Column_num）

MATCH函數(shù)：數(shù)組中查找值

用途：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。

格式與參數(shù)：MATCH（Lookup_value，Lookup_array，Match_type）

CHOOSE函數(shù)：列值中查找值

用途：使用Index—num返回?cái)?shù)值參數(shù)列表中的數(shù)值。使用CHOOSE可以根據(jù)索引號(hào)從最多254個(gè)數(shù)值中選擇一個(gè)。

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

VLOOKUP函數(shù)：豎直查找

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

格式與參數(shù)：VLOOKUP（Lookup_value，Table_array，Col_index_num，Range_lookup）

參考文獻(xiàn)：

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

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

[3]Excel Home. Excel 2010函數(shù)與公式實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2014.

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

[5]吳新瑛.EXCEL函數(shù)實(shí)例[M].上海：上?？茖W(xué)技術(shù)出版社，2009.