【前言】
对于经常处理数据的我们来说,有一种让我们最头疼的情况叫做“不规范数据源”,估计大家都会或多或少的遇到过吧。作者有一个朋友,前几天就找到作者,帮忙给看看下面的这个数据该如何处理。
数据源如下:
(脱敏数据,姓名为三国人物名替代)
需求是:按照人名提取对应的基本工资。效果如下:
根据在B14单元格的输入姓名,自动得到对应的基本工资。
【正文】
首先说,这个数据源还是比较“规范”的,可以看出每个基本工资等级对应的所有人员。但是这样的数据对于统计或者索引来说,却是“大大增加了难度”,下面作者就给大家介绍一个好用的嵌套函数,来解决这个问题。
INDEX+SMALL+IF的“万金油”
在C14单元格输入函数:
{=INDEX($G$2:$G$9,SMALL(IF($B$2:$F$9=B14,ROW($1:$8),99^9),1))}
函数解析:
No.1
给满足条件的数据标记“跟踪序号”
通过IF函数,如果B2:F9单元格区域中的值,等于B14单元格的值,那么返回ROW(1:8)的行号,否则返回99^9;我们可以通过“公式求值”的功能,来预看一下得到了什么内容。
大家可以看出,满足条件的就会返回这个值在数据区域中的第几行,其他不满足条件的就返回了91351724…..一串数字;
No.2
使用SMALL得到我们需要的序号
在得到了这样一组数列之后,使用SMALL函数,引用这个数列中最小的值,就是上图中的“3”了,它的意义就在于,我们要的对应“基本工资”在数据范围内的第3行;
No.3
引用吧,我的INDEX
此时的INDEX函数就好理解了,索引区域G2:G9单元格区域中的第3个值;
No.4
注意数组函数的录入方式
按CTRL+SHIFT+ENTER,组合键结束函数的录入。这个就是“三键录入”了,是数组函数的结束动作,按了三键后,原函数就会被一对【{}】括起来,很多同学数组函数没有成功,就是因为最后的三键没有操作对,或者手动输入的花括号。
函数点评
“万金油”是众多EXCEL使用者共同认可的一个称呼,对于很多“多维引用”、“多条件引用”、“不规则区域引用”等情况时,它都起到了很大的作用,所以大家也就约定俗成的给这种函数嵌套起了“万金油”的称呼。应用还是比较广泛的,所以建议大家一定要学会哟。
作者:我是世杰,财务excel深度玩家,坚持每天分享财务excel干货,微信公众号:24财务excel
推荐阅读