excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)

高考今天结束,预祝考生们都能取得理想的成绩~

今天继续分享实用干货,涉及到透视表分组、Vlookup函数模糊匹配

工作当中经常遇到需要将数据分组的情况,我们来看两个经典案例。

案例需求

案例一,如何统计出每个分数区间的人数?

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(1)

思路是先判断每个分数属于哪个区间,再统计每个区间的人数,如果用IF函数多层嵌套的话,区间太多,公式太长,容易出错,并不推荐。

案例二,如何确定每位员工的绩效提成点?

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(2)

思路同上,先判断每位员工的业绩属于提成规则表中的哪个区间,再返回对应的提成点。

这类判断数据分组的需求,我们首先想到的是IF函数:

=IF(业绩>90000,12%,IF(业绩>8000,11%,IF(业绩>7000,10%,IF(....))))

案例二的问题,至少得嵌套6个IF函数(累死人),繁琐且容易出错,在区间多的情况下,基本不考虑IF函数

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(3)

今天我们介绍两个快速简单的方法,实现数据分组的问题。

数据透视表

数据透视表的功能不可谓不强大,那么数据透视表如何实现分组?

这里我们以案例1为基础。

首先选中数据源,插入数据透视表,将字段“分数”拖动到行区域,字段“姓名”拖动到值区域

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(4)

这样,就可以统计数,每个分数,有多少个学生。

接着右键分组列,选择【组合】,Excel会默认给出数字范围的最小/最大值;

这里我们根据需求调整最大最小值与步长步长10,表示分数每隔10分为一组

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(5)

点击【确定】之后,数据分组完成。

从这里可以看出,数据透视表比较适合等步长数据的分组也就是说透视表并不适合非等步长的分组

比如将成绩区间分到60-80,80-90,90-100,第一个分数区间60-80的步长是20,后面两个分数区间的步长是10,前后步长不一致,透视表无法实现。

不等步长的问题,我们可以利用第二种方法实现。

Vlookup函数模糊匹配

Vlookup函数在Excel中的地位,堪比数据透视表,数据匹配神器。

但在日常工作中,我们基本只用到它的精准匹配模式,也就是最后一个参数是FALSE(0);

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(6)

它还有种模式,模糊匹配,最后一个参数是TRUE(1)。

我们以案例二为演示基础。

首先利用快速填充CTRL E,提取出提成规则表中的数字,记得放在列“提成点”的前面。

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(7)

接着,在C2单元格内输入公式:

=VLOOKUP(B2,E:G,3,1)

公式下拉,完成提成点的匹配。

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(8)

这是什么原理呢?

vlookup函数的第四个参数等于TRUE(0)的时候,表示模糊匹配

当在查找B2单元格(14000)的时候,Vlookup会从匹配区域的首列,也就是E列,从上到下查找当查找到第一个比它大的值时,停止查找,返回停止查找的上一个值

先查找14000,在E列从上到下查找,当找到30000的时候,30000大于14000,停止查找,返回30000上面的数字10000,结合vlookup函数参数三(等于3),返回匹配区域的第三列,所以函数最终返回G2单元格,3%,返回正确结果。

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(9)

前提是一定要对匹配区域的首列进行升序

也就是说E列需要升序操作,我们来看个反例。

下图中E列处于无序状态,正常状态下赵云的业绩是34000,返回的提成点应该是5%,但最终结果却是3%。

这是因为模糊匹配从上向下查找,查找到50000的时候,50000大于34000,返回50000上面的一个值10000,再结合参数三,返回3%。

excel数据透视表怎么设置条件(数据透视表和Vlookup函数模糊匹配了解一下)(10)

所以,一定要记得将E列升序操作,Vlookup函数模糊匹配才会返回正确的结果!

小结

Excel数据透视表分组 Vlookup函数模糊匹配,分享给大家,希望对你有所帮助。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页