近日，小編和一位在藥企做項目管理的朋友聚了聚，言談中感受到她濃濃的焦慮，原來是到季度末了，一大堆繁雜的統(tǒng)計報表要做。

按耐不住助人為樂的強烈愛好（我怎會輕易透露只有面對妹子的時候才這么熱心）。小編自告奮勇要幫她優(yōu)化下用到的各種電子表格，盡量做到能自動計算的就不人為處理。

她也不客氣，什么設(shè)備使用率、成本核算、生產(chǎn)進度、績效考核……發(fā)我一堆。

其實都不怎么復(fù)雜，大多數(shù)表格經(jīng)過稍稍提示她自己也就搞定了。其中一個績效考核表格，因為涉及到二維表格的分檔匹配，稍微復(fù)雜點，在這分享一下。

她的考核標(biāo)準(zhǔn)是這樣的：

其中”質(zhì)量事件分級”依據(jù)質(zhì)量事件發(fā)生次數(shù)確定：0-1次為1級；2-3次為2級；4-5次為3級；6次及以上為4級。

“生產(chǎn)任務(wù)完成率分級”的標(biāo)準(zhǔn)是：低于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%級。

考核數(shù)據(jù)是這樣記錄的：

要人工將”任務(wù)完成率”和”質(zhì)量事件數(shù)”匹配為不同的等級，然后根據(jù)兩個維度的等級確定獎金數(shù)，不但麻煩而且難免出錯。

下邊是小編優(yōu)化后的表格，”完成率分級”、”質(zhì)量分級”以及”績效獎金”都是自動計算出來的。

下邊看看是怎么實現(xiàn)的。

1.將任務(wù)完成率分級

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

公式講解：

Lookup（查找值，查找區(qū)域，返回區(qū)域），其中第三參數(shù)可以省略，省略時第二參數(shù)就作為查找區(qū)域和返回區(qū)域。

第一參數(shù)和第二參數(shù)的數(shù)據(jù)必須按升序排列，否則函數(shù)Lookup不能返回正確的結(jié)果，文本不區(qū)分大小寫。

如果在查找區(qū)域中找不到查找值，則查找第二參數(shù)中小于等于查找值的最大數(shù)值。

如果查找值小于第二參數(shù)中的最小值，函數(shù)Lookup返回錯誤值#N/A。

本例中函數(shù)公式可以理解為X<=C3<y時，返回x。比如凍干一車間的完成率為88%，通過x<=88%<y可以看到80%是小于等于88%的最大值。那么按照lookup函數(shù)查找規(guī)則應(yīng)該返回80%，這樣就完成了各車間完成率的分級。< p=””>

2.將工作質(zhì)量進行分級

和第一步一樣，也是使用Lookup函數(shù)。

在F3單元格輸入公式=LOOKUP(E3,{0,2,4,6},{1,2,3,4})，然后雙擊向下填充公式。

和第一步不同，這里使用了第三參數(shù)：當(dāng)質(zhì)量事件數(shù)小于2時，質(zhì)量分級為1；當(dāng)質(zhì)量事件數(shù)大于等于2小于4時，質(zhì)量分級為2；當(dāng)質(zhì)量事件數(shù)大于等于4小于6時，質(zhì)量分級為3；當(dāng)質(zhì)量事件數(shù)大于等于6時，質(zhì)量分級為4；

如果分級想要以字母表示，如分為A、B、C、D四級。公式稍微更改即可：=LOOKUP(E3,{0,2,4,6},{“A”,”B”,”C”,”D”})。文本和數(shù)字的區(qū)別在于文本需要加雙引號。

3.進行二維匹配

在G3單元格輸入公式=VLOOKUP(F3,績效獎金計算標(biāo)準(zhǔn)!A$3:J$7,MATCH(D3,績效獎金計算標(biāo)準(zhǔn)!$A$3:$J$3,0),0)，然后雙擊向下填充公式。

公式講解：

Vlookup（查找值，查找區(qū)域，返回第幾列，0）。

Match（查找值，查找區(qū)域，0），match函數(shù)的查找區(qū)域只能是單行單列。

整個公式的含義：使用Vlookup函數(shù)，在A3-J7區(qū)域內(nèi)查找F3單元格的值在第幾行，再使用Match函數(shù)在A3-J3區(qū)域內(nèi)查找D3單元格值在第幾列，根據(jù)查找到的行號和列號即可匹配到對應(yīng)的績效獎金數(shù)。

是不是很簡單？