使用公式解决数据分级匹配及二维查询问题

武汉点风科技

近日，小编和一位在药企做项目管理专员的朋友聚了聚，言谈中感受到她浓浓的焦虑，原来是到季度末了，一大堆繁杂的统计报表要做。

按耐不住助人为乐的强烈爱好（我怎会轻易透露只有面对妹子的时候才这么热心~~）。小编自告奋勇要帮她优化下用到的各种电子表格，尽量做到能自动计算的就不人为处理。

她也不客气，什么设备使用率、成本核算、生产进度、绩效考核……发我一堆。

其实都不怎么复杂，大多数表格经过稍稍提示她自己也就搞定了。其中一个绩效考核表格，因为涉及到二维表格的分档匹配，稍微复杂点，在这分享一下。

她的考核标准是这样的：

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg

其中“质量事件分级”依据质量事件发生次数确定：0-1次为1级；2-3次为2级；4-5次为3级；6次及以上为4级。

“生产任务完成率分级”的标准是：低于50%为0%级；50%-60%为50%级；60%-70%为60%级；70%-80%为70%级；80%-90%为80%级；90%-100%为90%级；100%-110%为100%级；110%-120%为110%级；大于120%为120%级。

考核数据是这样记录的：

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png

要人工将“任务完成率”和“质量事件数”匹配为不同的等级，然后根据两个维度的等级确定奖金数，不但麻烦而且难免出错。

下边是小编优化后的表格，“完成率分级”、“质量分级”以及“绩效奖金”都是自动计算出来的。

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image005.jpg

下边看看是怎么实现的。

第一步：将任务完成率分级

在D3单元格输入公式=LOOKUP(C3,{0,0.5,0.6,0.7,0.8,0.9,1,1.1,1.2})，然后双击向下填充公式。

Lookup用法讲解：

Lookup（查找值，查找区域，返回区域），其中第三参数可以省略，省略时第二参数就作为查找区域和返回区域。

第一参数和第二参数的数据必须按升序排列，否则函数Lookup不能返回正确的结果，文本不区分大小写。

如果在查找区域中找不到查找值，则查找第二参数中小于等于查找值的最大数值。

如果查找值小于第二参数中的最小值，函数Lookup返回错误值#N/A。

本例中函数公式可以理解为X<=C3<Y时，返回X。比如冻干一车间的完成率为88%，通过X<=88%<Y可以看到80%是小于等于88%的最大值。那么按照Lookup函数查找规则应该返回80%，这样就完成了各车间完成率的分级。

第二步：将工作质量进行分级

和第一步一样，也是使用Lookup函数。

在F3单元格输入公式=LOOKUP(E3,{0,2,4,6},{1,2,3,4})，然后双击向下填充公式。

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image009.jpg

和第一步不同，这里使用了第三参数：当质量事件数小于2时，质量分级为1；当质量事件数大于等于2小于4时，质量分级为2；当质量事件数大于等于4小于6时，质量分级为3；当质量事件数大于等于6时，质量分级为4；

如果分级想要以字母表示，如分为A、B、C、D四级。公式稍微更改即可：=LOOKUP(E3,{0,2,4,6},{"A","B","C","D"})。文本和数字的区别在于文本需要加双引号。

第三步：进行二维匹配

在G3单元格输入公式=VLOOKUP(F3,绩效奖金计算标准!A$3:J$7,MATCH(D3,绩效奖金计算标准!$A$3J$3,0),0)，然后双击向下填充公式。

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image011.jpg

公式讲解：

Vlookup（查找值，查找区域，返回第几列，0）。

Match（查找值，查找区域，0），match函数的查找区域只能是单行单列。

整个公式的含义：使用Vlookup函数，在A3-J7区域内查找F3单元格的值在第几行，再使用Match函数在A3-J3区域内查找D3单元格值在第几列，根据查找到的行号和列号即可匹配到对应的绩效奖金数。

是不是很简单？

话说回来，冻干一车间第二季度奖金22万，蛮可观呢！

转自微信公众号：实验室信息化与自动化

hhxx2018

对你这个比较感兴趣，能说说你的质量事件范围吗或者如何定义质量事件。如何进行质量分级。

hhxx2018

质量分级、质量事件，你们是如何界定的，能否分享？

武汉点风科技

hhxx2018 发表于 2018-9-27 09:39
质量分级、质量事件，你们是如何界定的，能否分享？

你好，这一般都是公司内部制定的，每个公司有所不同，参考意义不大呀。