2015年1月17日 星期六

Excel-根據等第評比進步與否(MATCH,CHOOSE)

在 Excel 中有一個人員最近二個月的成績等第表,如何利用這個成績等第表來評比是否進步呢?(參考下圖)

其中假設甲、乙、丙、丁分別對照分數 4、3、2、1。

評比要判斷出:進步、持平、退步

【公式設計】

儲存格D2:=CHOOSE(MATCH(C2,{"丁","丙","乙","甲"},0)-MATCH(B2,
{"丁","丙","乙","甲"},0)+4,"退步","退步","退步","持平","進步","進步","進步")

複製儲存格D2,貼至儲存格D2:D20。

為了說明方便,先使用輔助欄位來說明,參考下圖:

儲存格D2:=MATCH(B2,{"丁","丙","乙","甲"},0)

利用 MATCH 函數在常數陣列({"丁","丙","乙","甲"})中找尋儲存格B2位於第幾個,巧妙的得到結果:丁傳回 1、丙傳回 2、乙傳回 3、甲傳回 4,符合定義的分數。

同理:

儲存格E2:=MATCH(C2,{"丁","丙","乙","甲"},0)

儲存格F2:=CHOOSE(E2-D2+4,"退步","退步","退步","持平","進步","進步","進步")

這是個很趣的公式寫法,因為沒有使用 IF 來判斷,而是使用 CHOOSE 函數來判斷。

E2-D2+4:因為E2-D2可能的結果為 -3, -2, -1, 0, 1, 2, 3,其中 -3, -2, -1 為退步,0 為持平,1, 2, 3 為進步。將結果+4,即可套用至 CHOOSE 函數來根據這個結果求得退步/持平/進步的其中一個結果。

複製儲存格F2,貼至儲存格F2:F20。

這個公式的用法雖然有趣,但是如果等第的分類很多的話,公式也會變得很長。

沒有留言:

張貼留言

好康東東