使用(yòng)公式解決數(shù)據分(÷×≈<fēn)級匹配及二維查詢問(wèn)題

近(jìn)日(rì)，小(xiǎo)編和(h ¶↓é)一(yī)位在藥企做(zuò)項目管理(lǐ)的(de)朋(péng)友(‍↕∏yǒu)聚了(le)聚，言談中感受到(dào)她(tā)濃濃的(✔♦♦<de)焦慮，原來(lái)是(shì)到(dào)季度末了(le)，>♣‍一(yī)大(dà)堆繁雜(zá)的(de)統計(jì)報(bào)表要(yà₽‍δφo)做(zuò)。

按耐不(bù)住助人(rén)為(wèi)樂(yuè)的(♥σ÷de)強烈愛(ài)好(hǎo)（我怎會(huì)輕易透露隻有(yǒu)≠✘α面對(duì)妹(mèi)子(zǐ)的(de)時(shí)候才這(zhèπ≈γ♦)麽熱(rè)心）。小(xiǎo)編自(zì)告奮勇要 ↕÷₽(yào)幫她(tā)優化(huà)下(xià)用(yòng)到(dàβ×₹o)的(de)各種電(diàn)子(zǐ)表格π✔★，盡量做(zuò)到(dào)能(néng)自(zì)動計(jì)算(suàn)的(de)就(jiù)不 →ε×(bù)人(rén)為(wèi)處理(l≈§<ǐ)。

她(tā)也(yě)不(bù)客氣，什(shé​♣"≠n)麽設備使用(yòng)率、成本核算(suàn)、生(sh‌'ēng)産進度、績效考核……發我一(yī)堆。

其實都(dōu)不(bù)怎麽複雜(zá)，大(dà)多(du<±ō)數(shù)表格經過稍稍提示她(tā)自(zì©σ★∑)己也(yě)就(jiù)搞定了(le)。其中一(yī)個(gè)績效考核表≥£∑¥格，因為(wèi)涉及到(dào)二維表格的(♣‍de)分(fēn)檔匹配，稍微(wēi)複雜(zá)點，在這(zhè)₩‌←分(fēn)享一(yī)下(xià)。

她(tā)的(de)考核标準是(shì)這(zhè)樣的(de₩£♦∞)：

其中”質量事(shì)件(jiàn)分(fēn)級”依據質↔±量事(shì)件(jiàn)發生(shēng)次數(shù)确定∞"：0-1次為(wèi)1級；2-3次為(wèiδ "γ)2級；4-5次為(wèi)3級；6次及以上(shàng)為(™₹<wèi)4級。

“生(shēng)産任務完成率分(fēn)級”的(de)标準是(shì)♦αγφ：低(dī)于50%為(wèi)0%級；50%-60%€±∞為(wèi)50%級；60%-70%為(wèi)60%級；70<>$%-80%為(wèi)70%級；80%-90%為(wèi)80%級；90%-∞✔₽ 100%為(wèi)90%級；100%-110%為(wèi)1↓'∞¶00%級；110%-120%為(wèi)110%級；大(dà)于120%為(w​♥èi)120%級。

考核數(shù)據是(shì)這(zhè)樣記錄≤"♥α的(de)：

要(yào)人(rén)工(gōng)将”任務完成率”和(hé)”質量事(βδshì)件(jiàn)數(shù)”匹配為(wèi)σ>不(bù)同的(de)等級，然後根據兩個(gè)維度的(de)等級∑ 确定獎金(jīn)數(shù)，不(bù)但(dàn)麻煩而且難免出錯(☆∏cuò)。

下(xià)邊是(shì)小(xiǎo)編優化(hu↓‌à)後的(de)表格，”完成率分(fēn)級”、"•∏”質量分(fēn)級”以及”績效獎金(jīn)”都(dōu)是(®≤shì)自(zì)動計(jì)算(suàn)出來( λ&​lái)的(de)。

下(xià)邊看(kàn)看(kàn)是(shì)怎麽實現(xiàn)的≠​(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ū)域），其中↓∏ε∞第三參數(shù)可(kě)以省略，省略時(shí)第二參數(s•&↑&hù)就(jiù)作(zuò)為(wèi↓')查找區(qū)域和(hé)返回區(qū)域。

第一(yī)參數(shù)和(hé)第二參數(shù)的(de)數(shù)據₹εε必須按升序排列，否則函數(shù)Lookup不(bù)能(néng™±λ)返回正确的(de)結果，文(wén)本不(bù)區(qū)分(fēn)大₩≤<•(dà)小(xiǎo)寫。

如(rú)果在查找區(qū)域中找不(bù)到(dàoΩ ∑)查找值，則查找第二參數(shù)中小(xiǎo)于等于查找值的(de)最大∏‍↔(dà)數(shù)值。

如(rú)果查找值小(xiǎo)于第二參↕↓數(shù)中的(de)最小(xiǎo)值，函數δ$↔(shù)Lookup返回錯(cuò)誤值#N/A。

本例中函數(shù)公式可(kě)以理(lǐ)解為(wèi)X<=C‍÷3<y時(shí)，返回x。比如(rú)凍幹一λ™λ≈(yī)車(chē)間(jiān)的(de)完成率為(wèi)8γδ§ 8%，通(tōng)過x<=88%<y可(↓ε kě)以看(kàn)到(dào)80%是(shì)小(xi$©ǎo)于等于88%的(de)最大(dà)值。那(nà ∞)麽按照(zhào)lookup函數(shù)查找規則應該返回80%，這(α≤←≤zhè)樣就(jiù)完成了(le)各車(chē)間(jiāδ •n)完成率的(de)分(fēn)級。< p=””>

2.将工(gōng)作(zuò)質量進行(xíng)分(f•× ēn)級

和(hé)第一(yī)步一(yī)樣，也(yě)是(shì)使用(yòng)>™€Lookup函數(shù)。

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

和(hé)第一(yī)步不(bù)同，這∏∑★(zhè)裡(lǐ)使用(yòng)了(le)第三參數(shù)：當$§ ↑質量事(shì)件(jiàn)數(shù)小(xiǎo)于2時( ≠♠shí)，質量分(fēn)級為(wèi)1；當質量事(shì)件(j✔Ω✘♣iàn)數(shù)大(dà)于等于2小(xi≤γ±"ǎo)于4時(shí)，質量分(fēn)級為(wèi)2；當質量¶∞事(shì)件(jiàn)數(shù)大(dà)于等于4小(x ≤ iǎo)于6時(shí)，質量分(fēn)級為(wèi)3；當質量事(sh×∞✔ì)件(jiàn)數(shù)大(dà)于等于6✘₩≈§時(shí)，質量分(fēn)級為(wèi)4룩™；

如(rú)果分(fēn)級想要(yào)以✘"¶字母表示，如(rú)分(fēn)為(wèi)A、B、α>'©C、D四級。公式稍微(wēi)更改即可(♥♣kě)：=LOOKUP(E3,{0,2,4,6},{“A”,”B”₩₽$&,”C”,”D”})。文(wén)本和(hé)數(shù)字的(deπ® )區(qū)别在于文(wén)本需要(yào)加雙↓‌λ×引号。

3.進行(xíng)二維匹配

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

公式講解：

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

Match（查找值，查找區(qū)域，0），match函數(shù)的(de)查找區(q♠☆ū)域隻能(néng)是(shì)單行("δ‌↑xíng)單列。

整個(gè)公式的(de)含義：使用(yòng)Vl★€♦♦ookup函數(shù)，在A3-J7區(qū)域φ∞✘內(nèi)查找F3單元格的(de)值在♦®↔₹第幾行(xíng)，再使用(yòng)Match函數ε↔≠φ(shù)在A3-J3區(qū)域內(nè✘≈i)查找D3單元格值在第幾列，根據查找到(d®±$¶ào)的(de)行(xíng)号和(hé)列号即可(kě)匹配到(dà¥‌♠♥o)對(duì)應的(de)績效獎金(jīn)數(shù)。

是(shì)不(bù)是(shì)很(hěn)簡單？