使用(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)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ū)域），其中第三參數(sγ™hù)可(kě)以省略，省略時(shí)第二參數(shù)就(jλλiù)作(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₹₹λ<=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%的(d ↔• e)最大(dà)值。那(nà)麽按照(zhào)lookup函數(sδ$♥hù)查找規則應該返回80%，這(zhè)樣就(jiù)完成了(→βle)各車(chē)間(jiān)完成率的(de)分(fēn)級。&l÷¶♦∏t; 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ì)件(j Ωiàn)數(shù)小(xiǎo)于2時(s≤♥® hí)，質量分(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,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òn☆♣‌g)Vlookup函數(shù)，在A3↓☆&®-J7區(qū)域內(nèi)查找F3單元格的(de)值在第幾行(xín ×€γg)，再使用(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)簡單？