使用(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è)的(d&•e)強烈愛(à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)5π₹0%級；60%-70%為(wèi)60%級；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é)第二參↓↔"數(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à)數(sαΩεhù)值。

如(rú)果查找值小(xiǎo)于第二參數(shù≤♦♠)中的(de)最小(xiǎo)值，函數(shù)Look ™↔up返回錯(cuò)誤值#N/A。

本例中函數(shù)公式可(kě)以理(lǐ)≈≥§解為(wèi)X<=C3<y時(shí)β±←♠，返回x。比如(rú)凍幹一(yī)車(chē)間(γ$✘₩jiān)的(de)完成率為(wèi)88%，通(≥∏♥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ì)件($δ±jiàn)數(shù)大(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ì)↕​¶∑件(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,M±β±×ATCH(D3,績效獎金(jīn)計(jì)算(suàn)•★ ±标準!$A$3:$J$3,0),0)，然後雙擊向下(xπ§ià)填充公式。

公式講解：

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

Match（查找值，查找區(qū)域，0），match函數(sh★←≈£ù)的(de)查找區(qū)域隻能(néng)是(shì)單行(xíng)單列♣∏¶。

整個(gè)公式的(de)含義：使用(yòng)Vlook☆↑δ¥up函數(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)簡單≥≥¥•？