excel公式lookup和vlookup（穿越时间Excel升级之路连载12）

跟我一起，穿越时间！

在上一期的连载中，我们通过分类汇总、数据透视、Rank函数、SUMPRODUCT函数等方式统计了每个人的销售排名，没有阅读的可以点击头像或链接跳转：

穿越时间Excel升级之路连载9:MATCH函数及SUMPRODUCT不重复值计数

穿越时间Excel升级之路连载10：Index函数Offset函数提取不重复值

穿越时间Excel升级之路连载11：分类汇总 数据透视 SUMPRODUCT排名

统计排名是为了什么呢？当然是为了更直观地展示每个人的销售情况并确定荣誉，那么今天的内容就是让每个人的销售状态更直观，同时确定每个人的荣誉。涉及应用条件格式、PERCENTRANK.INC函数百分比排位、Vlookup函数近似查询自动分级等内容。

下面让我们走到Excel升级之路连载12：百分比排位及Vlookup函数近似查询

一、通过条件格式让排名信息更直观

我们可以先来看看下面这张对比图片，图片的上半部分就是连载11中的结果，无论是销售额还是排名，都只是数字，整个表格显得光秃秃空荡荡的；图片的下半部分则在单元格中加入了代表数据大小的长条、代表名次高低的彩色圆点，瞬间就有了高端大气上档次的感觉，这是怎么实现的呢?

1、条件格式——数据条

虽然上面的效果看起来类似Excel中的图表——条形图，但这并不是通过插入图表实现的，当然也肯定不是手工绘制的，实现他们的方法都是——条件格式。

在前面处理销售人员重复的身份证号时（连载9：MATCH 函数及SUMPRODUCT不重复值计数），我们曾用条件格式突出显示重复的身份证号，因此相信你已经非常清楚条件格式按钮的位置了。

除了突出显示重复值，条件格式中还有非常多高效的功能可以让Excel表格更加直观美观，例如“数据条”、“色阶”、“图标集”。今天我们用到的两项为“数据条”和“图标集”。

默认情况下，“数据条”的长度就代表单元格中数值的大小，数值越大，数据条越长。“数据条”提供两种填充方案：渐变填充和实心填充，因此只需应用一下，即可为销售额添加效果。

通过点击“其他规则”，我们可以自由地调整不同的效果。

注意，无论我们调整单元格尺寸还是调整数据大小，“数据条”都可以实现自动更新。

2、条件格式——图标集

至于每个人排名前面的彩色圆点，我们是通过条件格式中的图标集来实现的。

“图标集”中的图标分为“方向类”、“形状类”、“标记类”、“等级类”，可以根据需要来进行选择。

首先，我们确定一套规则：

排名前10%（不含）为星耀黑灯，授予“胜天至尊”荣誉；

排名10%-50%（不含）为华丽红灯，授予“一代宗师”荣誉；

排名50%-90%（不含）为灿烂黄灯，授予“江湖豪侠”荣誉；

排名90%以后的为萌新绿灯，授予“门派新秀”荣誉。

要实现这样的效果，必须通过“其他规则”来自己设置。

首先选择“图标样式”为四色交通灯，默认图标顺序为“绿、黄、红、黑”；

然后在下方调整规则，输入我们定义的分隔数值90%、50%、10%，然后确定。

这时效果就添加好了：

二、百分比排位

第一部分中我们已经确定了一套排名的规则及荣誉称号，但怎么把荣誉称号填进去呢？

1、分析：要想将荣誉称号填进去，就需要知道每个人的排名所处的位置是前百分之几，那么这要怎么计算？

我们继续向下看。

2、计算某人排名所处的百分比排位，有两种规则。

规则一：

某人排名所处的百分比排位=（比此排名小的数据个数）/（总数据个数-1）

通过下面两个公式都可以计算：

=COUNTIF($AI$2:$AI$9,"<"&AI2)/(COUNT($AI$2:$AI$9)-1)

=SUMPRODUCT(($AI$2:$AI$9<AI2)*1)/(COUNT($AI$2:$AI$9)-1)

Excel中提供了直接的函数：

Percentrank函数和Percentrank.inc函数，字面意思就是百分比排名，百分比值的范围为 0 到 1（含 0 和 1）。

Percentrank函数PERCENTRANK(array,x,[significance])

Percentrank.inc函数PERCENTRANK.INC(array,x,[significance])

其中array代表数值数组或者数据区域，x代表要知道其排位的值，Significance是用来调整结果的小数位数的，默认显示3位。

这两个函数的计算结果都是一样的，之所以存在两个，是因为Percentrank函数可以保持和更早版本的Excel兼容。

所以我们使用的公式为：

=PERCENTRANK($AI$2:$AI$9,AI2)

=PERCENTRANK.INC($AI$2:$AI$9,AI2)

上图中可以看到依据这种规则计算出来的结果，举个例子，“琼英”的销售额最高，排名为1，那么她的百分比排位就是前0%，“周婉悦”的排名为2，那么她的百分比排位就是前14.2%

规则二：

某人排名所处的百分比排位=（比此排名小的数据个数 1）/（总数据个数 1）

这种规则下计算的百分比排位范围是0到1，不包含0和1

Excel中提供了名为Percentrank.exc的函数

PERCENTRANK.EXC(array,x,[significance])

我们可以验证一下，通过具体的规则和Percentrank.exc函数的计算结果都是一样的。

=(COUNTIF($AI$2:$AI$9,"<"&AI2) 1)/(COUNT($AI$2:$AI$9) 1)

=(SUMPRODUCT(($AI$2:$AI$9<AI2)*1) 1)/(COUNT($AI$2:$AI$9) 1)

=PERCENTRANK.EXC($AI$2:$AI$9,AI2)

下文中，我们使用的都是规则一中的百分比排位。

三、Vlookup函数近似查询自动分级

我们调整一下百分比排位的显示方式，这样就比较直观了。

接下来，我们根据上面确定的规则，把每个人都荣誉奖项填写到单元格里。

注意，不是手动填写！那怎么操作？

1、Vloolup函数

我们需要借助Vlookup函数来完成。

先复习一下Vlookup函数的语法

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Vlookup(查询值，查询区域，要返回的目标值在查询区域中的列号，[匹配方式])

相信很多人用得比较多的是精确匹配查询，即最后一个参数为FALSE，如果找到了，那么vlookup就返回找到的结果（当查询区域第一列中有两个或更多值与查询值匹配时，使用第一个找到的值）；如果找不到则返回错误值#N/A

如果最后一个参数省略或者设为“TRUE”，那么Vlookup执行的则是查找精确匹配值或近似匹配值，且要求查询区域的第一列必须升序排列。

注意：是查找精确匹配值或近似匹配值，即如果找到精确匹配值，那就返回精确匹配值；如果找不到精确匹配值，则返回小于查找值的最大值。

利用Vlookup近似匹配的特性，我们就可以实现对不同分段区域自动填写不同的值。

2、Vloolup近似查询公式

=VLOOKUP(AJ2,{0,"胜天至尊";0.1,"一代宗师";0.5,"江湖豪侠";0.9,"门派新秀"},2,TRUE)

解释一下：

{0,"胜天至尊";0.1,"一代宗师";0.5,"江湖豪侠";0.9,"门派新秀"}作为vlookup函数的第二个参数，即查询区域，它是一个常量数组，当然也可以使用单元格引用。

只是需要注意的是，vlookup函数近似匹配返回小于查找值的最大值，因此，我们只需要规则范围的下限值作为分界，即0、0.1、0.5、0.9，而且是升序排列！

这样在一个单元格中输入公式，然后拖动填充即可完成所有人员的荣誉填写。

例如“兰香凝”百分比排位42.8%，处于前10%-50%，应为“一代宗师”；vlookup查询{0,"胜天至尊";0.1,"一代宗师";0.5,"江湖豪侠";0.9,"门派新秀"}，找小于等于42.8%的最大值为0.1，近似匹配的结果就是“一代宗师”。

这里的用法就是Vlookup函数近似查询实现的自动分级，如果我们换一种方法，通过if函数四层嵌套来判断，然后拖动填充也是可以的。

=IF(AJ2<0.1,"胜天至尊",IF(AJ2<0.5,"一代宗师",IF(AJ2<0.9,"江湖豪侠",IF(AJ2<=1,"门派新秀",""))))

或者：

=IF(AJ2>=0.9,"门派新秀",IF(AJ2>=0.5,"江湖豪侠",IF(AJ2>=0.1,"一代宗师",IF(AJ2>=0,"胜天至尊",""))))

Excel中允许嵌套最多64个不同的IF函数，但是IF函数嵌套过多时公式会难以阅读，目前在最新版本的Excel中已经有了IFS函数，相比多重IF函数嵌套，IFS函数要更加简洁一些。

如果上面的都理解了，我们可以采取同样的方法，根据销售额或者百分位排名，为不同人员赋予不同的佣金比率，这些本文就不再详述了。

以上就是连载12的全部内容，如果对数据背景不清楚或有不理解的可以先看前面的连载打牢基础。

点击头像或链接跳转：

穿越时间Excel升级之路连载9:MATCH函数及SUMPRODUCT不重复值计数

穿越时间Excel升级之路连载10：Index函数Offset函数提取不重复值

穿越时间Excel升级之路连载11：分类汇总 数据透视 SUMPRODUCT排名

更多精彩，敬请关注，投币赞赏，感谢支持。

（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）

,