利用函数快速实现数组条件引用.doc

_利用函数快速实现数组的条件引用撰稿：张伟/国内销售部 2009年4月14日人们在使用Excel时，大多将它仅仅当作表格来使用。而Excel的核心功能却在于它强大、高效的数据处理功能。为了帮助用户提高数据处理效率，开发商在Excel中提供了包括统计函数、文本函数、财务函数、查找和引用函数、日期和时间函数等12大类常用函数。下面，让我们一起感受一下吧。为了量化员工考核，A公司对员工的计划销售量、计划完成量、误差率进行了规定：误差率100计划销售量实际完成量/ 计划销售量误差率与员工考核挂钩，误差率越小，考核时员工得分越高。评分标准及考核期内的销售数据如图1中的表1、表2所示。图1如何利用函数快速进行计算呢？1、考核表中“误差率”项与“排名”项的计算方法误差率和排名两项比较简单，我们只需在表2的单元格D17、F17中分别输入：=100*ABS(B17-C17)/B17=RANK(F17,F$17:F$23)之后双击单元格右下角的“+”即可（复制公式。也可点住“+”向下拖动鼠标）。ABS函数用于计算绝对值。RANK函数用于返回一个数字在数字列表中的排位。在表达式“F$17:F$23”中，参数“$”表示对行的绝对引用。如果不加“$”的话，在F18中复制公式时，公式将会变成“=RANK(F18,F18:F24)”，以至于结果出错。 2、考核表中“得分”项的计算方法为了方便计算，我们需将表1中数据的表现形式调整成数组形式，具体如图1中的表3（方法一使用）和表4（方法二使用）所示。方法一：利用INDEX函数和MATCH函数进行计算在E17中输入“=INDEX($D$3:$E$13,MATCH(D17,$D$3:$D$13,1)+1,2)”，之后进行公式复制即可。结果如图2中的表2所示。图2下面以E17的计算公式“=INDEX($D$3:$E$13,MATCH(D17,$D$3:$D$13,1)+1,2)”为例进行说明：利用MATCH 函数，可在单元格中搜索指定项，然后返回该项在单元格区域中的相对位置。公式“MATCH(D17,$D$3:$D$13,1)”的作用就是在数组D3：E13中查找小于或等于30.81（D17的值，即销售员李1的误差率）的数（结果为30）的行数（结果为7）。利用INDEX函数，可返回表格或数组中的元素值，此元素由行号和列号的索引值给定。根据MATCH函数对E17的计算结果，公式：=INDEX($D$3:$E$13,MATCH(D17,$D$3:$D$13,1)+1,2)可以简化为：=INDEX($D$3:$E$13,8,2)也就是说，在E17中返回数组D3：E13第8行第2列的值（结果为8）。方法二：利用VLOOKUP函数进行计算在E17中输入“=VLOOKUP(D17,$G$3:$H$12,2)”，之后进行公式复制即可。利用VLOOKUP函数，可以在表格数组的首列垂直查找指定的值，并由此返回表格数组当前行中其他列的值。以E17的公式为例，就是要在数组G3：H12中查找小于或等于30.81（D17的值）的最大值（结果为30），并返回其对应的该数组第2列的值（结果为8）。3、结果验证李1的误差率为30.81，查表1，介于3035之间，得分应为8。利用方法一、方法二的计算结果亦为8，结果一致。利用函数，还可以快速实现文本的合并或拆分，利用身份证号码计算一个人的生日、年龄并自动判断其性别等等。这些今后将会逐一介绍