使用(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à)下(xγ♠αià)用(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%級；5★®↑☆0%-60%為(wèi)50%級；60%-70%為(wèi)6$¶δ0%級；70%-80%為(wèi)70%級；80%'₽ ↑-90%為(wèi)80%級；90%-100♦σ %為(wèi)90%級；100%-110%為↑₩ €(wèi)100%級；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í)第二↑ ☆•參數(shù)就(jiù)作(zuò)為(wèi)查找區(¥←Ω©qū)域和(hé)返回區(qū)域。

第一(yī)參數(shù)和(hé)第二參數(s£<hù)的(de)數(shù)據必須按升序排列，否則函數(shù)Lo¶€π•okup不(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<=C3≤ <y時(shí)，返回x。比如(rú)凍幹一(yī)車(c∏¶×αhē)間(jiān)的(de)完成率為(wèi)88±>%，通(tōng)過x<=88%<φ$​✔y可(kě)以看(kàn)到(dào)8₹≈≤0%是(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ín✔δ→<g)分(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ì)件(jiàn)數(s &$≠hù)大(dà)于等于2小(xiǎo)于4時(shí)，質量分(‍±₩fēn)級為(wèi)2；當質量事(shì)件(jiàn)數(shù)大(d×✔à)于等于4小(xiǎo)于6時(shíφ&)，質量分(fēn)級為(wèi)3；當質量事(shì)件(j→♦ià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ě)：=L β←OOKUP(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)Vlookup函數(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)簡單？