徐莅

摘要：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].上海：上海科学技术出版社，2009.