使用(yòng)公式解決數據分(fēn)級匹配及二維查詢問題

近日，小編和(hé)一位在藥企做(zuò)項目管理(lǐ)的(de)朋友聚了(le)聚，言談中感受到她濃濃的(de)焦慮，原來(lái)是到季度末了(le)，一大(dà)堆繁雜(zá)的(de)統計報表要做(zuò)。

按耐不住助人(rén)爲樂(yuè)的(de)強烈愛(ài)好（我怎會輕易透露隻有面對(duì)妹子的(de)時(shí)候才這(zhè)麽熱(rè)心）。小編自告奮勇要幫她優化(huà)下(xià)用(yòng)到的(de)各種電子表格，盡量做(zuò)到能自動計算(suàn)的(de)就不人(rén)爲處理(lǐ)。

她也(yě)不客氣，什(shén)麽設備使用(yòng)率、成本核算(suàn)、生産進度、績效考核……發我一堆。

其實都不怎麽複雜(zá)，大(dà)多(duō)數表格經過稍稍提示她自己也(yě)就搞定了(le)。其中一個(gè)績效考核表格，因爲涉及到二維表格的(de)分(fēn)檔匹配，稍微複雜(zá)點，在這(zhè)分(fēn)享一下(xià)。

她的(de)考核标準是這(zhè)樣的(de)：

其中”質量事件分(fēn)級”依據質量事件發生次數确定：0-1次爲1級；2-3次爲2級；4-5次爲3級；6次及以上爲4級。

“生産任務完成率分(fēn)級”的(de)标準是：低于50%爲0%級；50%-60%爲50%級；60%-70%爲60%級；70%-80%爲70%級；80%-90%爲80%級；90%-100%爲90%級；100%-110%爲100%級；110%-120%爲110%級；大(dà)于120%爲120%級。

考核數據是這(zhè)樣記錄的(de)：

要人(rén)工将”任務完成率”和(hé)”質量事件數”匹配爲不同的(de)等級，然後根據兩個(gè)維度的(de)等級确定獎金數，不但麻煩而且難免出錯。

下(xià)邊是小編優化(huà)後的(de)表格，”完成率分(fēn)級”、”質量分(fēn)級”以及”績效獎金”都是自動計算(suàn)出來(lái)的(de)。

下(xià)邊看看是怎麽實現的(de)。

1.将任務完成率分(fēn)級

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

公式講解：

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

第一參數和(hé)第二參數的(de)數據必須按升序排列，否則函數Lookup不能返回正确的(de)結果，文本不區(qū)分(fēn)大(dà)小寫。

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

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

本例中函數公式可(kě)以理(lǐ)解爲X<=C3<y時(shí)，返回x。比如凍幹一車間的(de)完成率爲88%，通(tōng)過x<=88%<y可(kě)以看到80%是小于等于88%的(de)最大(dà)值。那麽按照(zhào)lookup函數查找規則應該返回80%，這(zhè)樣就完成了(le)各車間完成率的(de)分(fēn)級。< p=””>

2.将工作質量進行分(fēn)級

和(hé)第一步一樣，也(yě)是使用(yòng)Lookup函數。

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

和(hé)第一步不同，這(zhè)裏使用(yòng)了(le)第三參數：當質量事件數小于2時(shí)，質量分(fēn)級爲1；當質量事件數大(dà)于等于2小于4時(shí)，質量分(fēn)級爲2；當質量事件數大(dà)于等于4小于6時(shí)，質量分(fēn)級爲3；當質量事件數大(dà)于等于6時(shí)，質量分(fēn)級爲4；

如果分(fēn)級想要以字母表示，如分(fēn)爲A、B、C、D四級。公式稍微更改即可(kě)：=LOOKUP(E3,{0,2,4,6},{“A”,”B”,”C”,”D”})。文本和(hé)數字的(de)區(qū)别在于文本需要加雙引号。

3.進行二維匹配

在G3單元格輸入公式=VLOOKUP(F3,績效獎金計算(suàn)标準!A$3:J$7,MATCH(D3,績效獎金計算(suàn)标準!$A$3:$J$3,0),0)，然後雙擊向下(xià)填充公式。

公式講解：

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

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

整個(gè)公式的(de)含義：使用(yòng)Vlookup函數，在A3-J7區(qū)域内查找F3單元格的(de)值在第幾行，再使用(yòng)Match函數在A3-J3區(qū)域内查找D3單元格值在第幾列，根據查找到的(de)行号和(hé)列号即可(kě)匹配到對(duì)應的(de)績效獎金數。

是不是很簡單？