|
本文作者丨趙驕陽(yáng) - Excel 研究院 本文由「秋葉 Excel」原創(chuàng)發(fā)布 表姐碎碎念 幸福的日子時(shí)間總是過(guò)的飛快，轉(zhuǎn)眼間距離 2018 結(jié)束只剩 20 天的時(shí)間了，相信很多人正面臨著年終 KPI 考核評(píng)定，又一波加班即將來(lái)臨。  所以今天我就來(lái)和大家說(shuō)說(shuō)，怎么用 Excel 公式來(lái)高效地處理等級(jí)評(píng)定的問(wèn)題。 這次我們用一個(gè)具體的例子來(lái)講解這個(gè)問(wèn)題—— 首先設(shè)置評(píng)定等級(jí) 小于 60 分不合格； 大于等于 60 小于 70 為合格； 大于等于 70 小于 80 為良好； 大于等于 80 為優(yōu)秀。 也就是說(shuō)，如果我在考核中得了 75 分，那么就會(huì)被評(píng)定為良好。 好了，接下來(lái)我就依次介紹 4 種辦法來(lái)完成等級(jí)評(píng)定，供大家根據(jù)情況自行選用哦~ 1 IF 函數(shù)法 IF 函數(shù)的語(yǔ)法如下： =IF(條件判斷,條件成立返回值,條件不成立返回值) 含義就是，我們先設(shè)置一個(gè)條件，看條件是否成立。 ? 如果條件成立，返回一個(gè)值； ? 如果條件不成立，則返回另一個(gè)值。 根據(jù)上面說(shuō)的評(píng)定規(guī)則，我將判斷邏輯做成下圖的樣子：  再根據(jù)這幅邏輯關(guān)系圖，我在 C3 單元格中寫(xiě)下了多重嵌套的 IF 函數(shù)公式： =IF(B3<60,'不合格',IF(B3<70,'合格',IF(B3<80,'良好','優(yōu)秀')))  而這串公式的具體含義是： 當(dāng) B3<60 時(shí)，返回值為'不合格'，這時(shí)在'不合格'逗號(hào)的右側(cè)默認(rèn)就是>=60 的情況，接下來(lái)只需要滿足<70 的條件即可判斷為'合格'，以此類推，第一個(gè) IF 函數(shù)的第 3 個(gè)參數(shù)就被安排上了第二個(gè) IF 函數(shù)，之后再作判斷……從而形成 IF 函數(shù)的多層嵌套公式。 當(dāng)然，我們也可以換另一個(gè)思路，將得分由大至小判斷，邏輯圖就變成了下面的樣子：  此時(shí) C3 單元格的公式就可以寫(xiě)成： =IF(B3>=80,'優(yōu)秀',IF(B3>=70,'良好',IF(B3>=60,'合格','不合格'))) 這里特別提醒大家注意臨界點(diǎn)的判定，70 分到底是良好還是及格，等于號(hào)加在哪里要想清楚。 2 LOOKUP 函數(shù)法 IF 函數(shù)肯定是大家最熟悉的函數(shù)之一了，但是如果評(píng)定等級(jí)比較多，公式寫(xiě)起來(lái)會(huì)十分冗長(zhǎng)，下面我就來(lái)介紹 LOOKUP 函數(shù)法來(lái)簡(jiǎn)化處理這個(gè)問(wèn)題。 LOOKUP 函數(shù)的語(yǔ)法有兩種，數(shù)組形式和向量形式，我們?cè)诘燃?jí)評(píng)定中使用的是向量形式，語(yǔ)法如下： =LOOKUP(查找值,目標(biāo)向量,返回向量) 看這個(gè)你可能有點(diǎn)懵，我們直接用案例講：  我在 C3 單元格寫(xiě)出了下面的公式，然后拖動(dòng)向下填充。 =LOOKUP(B3,E$3:E$6,F$3:F$6) 你一定注意到了，在分?jǐn)?shù)列表右側(cè)有這樣兩列——  這是我們?cè)谟?LOOKUP 函數(shù)法解決等級(jí)評(píng)定問(wèn)題時(shí)要建立的評(píng)定標(biāo)準(zhǔn)表。 這里采用絕對(duì)引用或混合引用，本例采用混合引用，即只對(duì)行進(jìn)行錨定的方式來(lái)引用這兩組向量，也就是寫(xiě)成E$3:E$6,F$3:F$6 的樣子，否則向下填充后就有可能得不到正確的答案。 最后要提醒大家注意兩點(diǎn)： ? 評(píng)定等級(jí)的標(biāo)準(zhǔn)必須為升序排序，亂序、降序會(huì)得不到正確答案； ? 這里關(guān)于臨界值的判斷是這樣的，當(dāng)查找值大于等于某個(gè)等級(jí)（目標(biāo)向量值）時(shí)，將返回旁邊對(duì)應(yīng)的返回向量值。 3 TEXT 函數(shù)法 剛剛的 LOOKUP 函數(shù)法，寫(xiě)起公式來(lái)非常方便，但評(píng)定標(biāo)準(zhǔn)需要按升序排序，如果此條件無(wú)法滿足，這個(gè)方式就無(wú)法適用。 所以第 3 種方法，我要為大家再介紹一個(gè)函數(shù)——TEXT 函數(shù)。TEXT 函數(shù)是一個(gè)超級(jí)好用的格式化文本函數(shù)，可通過(guò)格式代碼對(duì)數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式。 TEXT 函數(shù)的語(yǔ)法如下： =TEXT(數(shù)值,格式代碼) 具體是這樣的，TEXT 函數(shù)的格式代碼可分為 4 個(gè)條件區(qū)段，各區(qū)段間用半角分號(hào)間隔。這 4 個(gè)區(qū)段的定義為： [條件 1];[條件 2];[不滿足條件 1 和條件 2 的其他部分];[文本] 然后根據(jù)各區(qū)段條件來(lái)判斷，返回相應(yīng)結(jié)果。因此我在C3 單元格寫(xiě)下了下面的公式： =TEXT(0&B3-60,'[<10]合格;[<20]良好;優(yōu)秀;不合格')  這串公式的含義是： ? '0&B3-60'部分就是首先計(jì)算 42-60 得到-18，然后與前面的 0 連接，最終結(jié)果為'0-18'，這時(shí) TEXT 函數(shù)會(huì)把它當(dāng)文本處理，文本落在第 4 個(gè)區(qū)間，返回結(jié)果'不合格'。我們可以看出，分?jǐn)?shù)在 60 分以下。表達(dá)式的結(jié)果都會(huì)是'0-數(shù)字'這種樣式，這種樣式會(huì)被 TEXT 函數(shù)以文本對(duì)待處理； ? 當(dāng) B3-60 小于 10 時(shí) ，公式返回的結(jié)果是'合格'；當(dāng)<20 時(shí)返回'良好'，其他情況得到的計(jì)算結(jié)果就是'優(yōu)秀'。 4 CHOOSE 函數(shù)法 剛剛的 TEXT 函數(shù)法，是通過(guò)自定義格式條件設(shè)置進(jìn)行等級(jí)評(píng)定，適用于 4 個(gè)以內(nèi)的等級(jí)評(píng)定，而 4 個(gè)以上的等級(jí)評(píng)定就不再適用了。 因此最后一種方法，將為大家介紹 CHOOSE 函數(shù)。這個(gè)函數(shù)可以根據(jù)指定的自然數(shù)序號(hào)返回與其對(duì)應(yīng)的數(shù)據(jù)值、區(qū)域引用或嵌套函數(shù)結(jié)果。根據(jù)它的特性，我們可以在某些條件下用CHOOSE 函數(shù)替代 IF 函數(shù)進(jìn)行條件判斷。 CHOOSE 函數(shù)的語(yǔ)法如下： =CHOOSE(索引值,值 1,值 2,值 3,.....) 語(yǔ)法含義是索引值可以是運(yùn)算結(jié)果是數(shù)值的表達(dá)式，或者直接是數(shù)值。當(dāng)索引值為 1 時(shí)，函數(shù)的結(jié)果返回值 1，當(dāng)索引值為 2 時(shí)，函數(shù)的結(jié)果返回值 2……以此類推。 如果沒(méi)看明白不要著急，我們直接看例子——  我在 C3 單元格輸入了下面的公式： =CHOOSE(SUM(--(B3>={0;60;70;80})),'不合格','合格','良好','優(yōu)秀') 這串公式的含義是： ? B3>={0;60;70;80}用來(lái)判斷 B3（這里是 42）是否大于等于考評(píng)值{0;60;70;80}，判斷完將返回邏輯值{TRUE;FALSE;FALSE;FALSE}； ? --(B3>={0;60;70;80})也就是在前面放置兩個(gè)-的目的是將邏輯值轉(zhuǎn)換為數(shù)值，結(jié)果為{1,0,0,0}，再用 SUM 求和，計(jì)算結(jié)果為 1，也就是說(shuō)作為這個(gè) CHOOSE 函數(shù)第一個(gè)參數(shù)的索引值是 1； ? 最后， CHOOSE(1,'不合格','合格','良好','優(yōu)秀')，得到計(jì)算結(jié)果為'不合格'。 ▼▼▼ 以上就是 4 種用于評(píng)定等級(jí)的方法，不知道大家掌握的怎么樣？EXCEL技巧再多，也要熟練運(yùn)用才好，今天的科普就到這里，如果大家有什么疑問(wèn)，歡迎到后臺(tái)進(jìn)行咨詢喲～ |
|
|
