今日内容:Excel中的交叉区间匹配

在日常的工作中,经常会遇到一类问题,是区间的匹配。如90%以上为100分,70-90为80分,70分以下为0.这类简单的区间匹配或者条件判断的问题可以使用IF来解决,再高级一点的可以使用LOOKUP或者VLOOKUP函数来实现。

但是今天需要说的三个关于区间匹配的例子,不同于上面的简单的例子,还是交叉的匹配多个交叉条件在一起的。下面我们一起来探讨这三种常见于绩效考核中的例子。

1 十字交叉区间匹配

如图所示,某企业的一个考核的方案中有如下的一个考核规则:某岗位双向考核两个指标,一个是到课的比率;另外是根据带班的人数多少确定对应的绩效奖金基数。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(1)

根据以上的规则,计算下面的几个人的绩效奖金基数。

首先将上面的规则转换一下,做为一个辅助区域。即:

excel if函数区间怎么用(Excel中十字交叉区间匹配)(2)

然后在I3单元格中输入以下公式,向下填充至I11单元格。

=INDEX($B$3:$E$7,MATCH($G3,$A$3:$A$7,1),MATCH($H3,$B$2:$E$2,1))

结果如图所示:

excel if函数区间怎么用(Excel中十字交叉区间匹配)(3)

注意:上述公式主要使用INDEX MATCH的组合策略,最重要的一个部分为MATCH的第三个参数是1,实现模糊匹配的作用。

2 多列交叉区间匹配

如图所示,规则为:每个年级对应的上课率与对应的系数如下。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(4)

根据以上的规则,计算下面的年级与上课率对应的系数。

同样地先将上面的表格稍做转化。如下图所示。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(5)

然后在J2单元格中输入以下公式,向下填充至J6单元格。

=LOOKUP(I2,OFFSET(A$2:A$7,0,MATCH($H2,$A$1:$E$1,0)-1),$F$2:$F$7)

同样地,这个问题也可以使用第一个例子中所讲的INDEX MATCH的套路组合来解决。在J2单元格中输入以下公式,向下填充至J6单元格。

=INDEX($F$2:$F$7,MATCH(I2,OFFSET(A$2:A$7,0,MATCH(H2,$A$1:$E$1,0)-1)))

结果如下图所示:

excel if函数区间怎么用(Excel中十字交叉区间匹配)(6)

3 多列多行十字交叉区间匹配

如图所示,规则为:每个年级对应的上课率与人数区间确定奖金基数。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(7)

根据以上的规则,计算下面的几个人的绩效奖金基数。

同样地先将上面的表格稍做转化。如下图所示。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(8)

然后在D12单元格中输入以下公式,向下填充至D20单元格。

=INDEX($D$4:$G$8,MATCH($B12,OFFSET(A$4:A$8,0,MATCH(A12,$A$3:$C$3,0)-1),1),MATCH($C12,$D$3:$G$3,1))

结果如图所示。

excel if函数区间怎么用(Excel中十字交叉区间匹配)(9)

上面的三个例子是经常在日常工作中会出现的一些例子或规则,使用好公式,能够事半功倍。

,