心理學。 Instagram @curtischanct

邊到都 Excel ：除咗函數 VLOOKUP 來評學生表現，仲有咩其他板斧？

2021 年 3 月 21 日

函數 IF, VLOOKUP, COUNTIF, SWITCH, PRODUCT, 條件化格式, 絕對參照, 名稱定義, Formula builder, 萬用字符 (Wildcard), 運算子(Operator)

上一篇文，我講咗我點樣用 Excel 來幫我搞掂嗰心理學畢業論文一啲部分。作為讀Psycho嘅人，Excel同計數機點都要用。呢次我來我就來重温一啲有用嘅 LOOKUP 函數，以及 highlight 一啲用 Excel 嘅好習慣。呢度用嗰例字講老師想檢查一下學生嘅學習成果。

試諗下有個小學老師批改曬佢嗰班啲考試卷。嗰熱心師老想睇下啲數據用來提升一啲學生嘅成績。

English readers may find the original version on Medium

註：篇文有點長，所以標咗章節同加個下邊呢個圖方便理解～同埋用咗廣東話來寫，台灣嘅讀者可能睇唔明⋯⋯

A1 用 IF 來勾出邊個肥佬同合格

先用社會呢科來執隻公式出來。喺呢部分，我地先試頭嗰5個學生嘅成績。其他學科測驗都可以照住來寫，只要明嗰背後邏輯。呢度我假設大家知咩係自動填充 (Auto-fill) 先。

E2 | =IF(D2>=50,"pass","fail")

在以上個公式度，有三大部分，分別係：

1、睇邊一格，我就打咗 D2>=50 來做嗰準則

2、如果係啱嗰陣，就彈翻咩字翻來。好似要 “pass” 呢隻字，如果 D2 嗰格嘅分等於或超過 50。

3、唔係就彈翻另一隻字翻來。好似要 “fail” 呢隻字，如果 D2 嗰格嘅分唔喺等於或超過 50。

呢度要講翻用一個常數，即係 50，其實唔係幾建議。用 50 只係為咗呢部分易啲說明姐。陣間再解點解。

A2 用條件格式化標明邊啲學生唔得掂

用嗰自動填充 (Auto-fill），我地即刻就知嗰五個學生邊個肥佬。跟住，我地可以做多步，就係用條件格式化 (Conditional formatting) 來加啲顏色方便啲睇。咁嗰設定寫到明其實就，先揀成棟 passSociety ，再喺 Excel 都㩒有 “pass” 字嘅格就將嗰背景變綠色。而 “fail” 都係咁整。

咁明顯標曬，嗰老師就可以睇下點幫佢嗰班。差啲嗰批就加強佢嗰動力、或者比其他獎勵佢地之類咁。較好嗰批則繼續谷佢地，比今次考得更好。

B 用 IF 來派 grade

學校一路都有一個分級系統嘅。嗰學生考到49分或者以下或者級別 F 就係嗰分水嶺。嗰老師唔只齋睇合格，仲想睇清啲成班啲 grade 啲分佈。呢度用咗 IF 來做，雖則都做到，但超混亂。

E2 | =IF(D2>=90,"A",IF(D2>=80,"B",IF(D2>=70,"C",IF(D2>=50,"D","F"))))

IF 只係啱用來比較幾隻情況，以上呢條示範公式好難去搞。同埋咁樣一層溝一層嘅 IF (nested) 好容易會漏括號。遲啲有咩改動都好頭赤。

C 更好解決方案 VLOOKUP

IF 簡直用來係惡夢。好彩仲有其他函數可以比 Excel 去處理自動判斷咩 grade。不過用呢個 VLOOKUP要 set up 啲嘢先。

1) 先整個分級表比公式來參考

一開波，加個新嘅，打曬唔同 grade 嘅線。要注意嘅係，呢個表要細至大咁一行行來排。另外，最好就一個 workbook 就一個目的。

宜家過 90 分叫做 “A”。他日如果改為 “Excellence”，只要喺呢度轉一轉，所有睇佢頭嘅公式都會自動更正埋，Super！

2) 函數 VLOOKUP

好，咁我地將 IF 換成勁抽嘅 VLOOKUP 啦！

E2 | =VLOOKUP(D2,gradeTable!$A$2:$B$6,2,TRUE)
E6 | =VLOOKUP(D6,gradeTable!$A$2:$B$6,2,TRUE)

呢次就有成四個部分，拎 E2 嗰格係 iPad 度做例子：

1、睇邊一格，我就打咗 D2 。

2、嗰分級參考表。用感歎號來將呢板連去參考表嗰板度，但千奇唔好包埋嗰分級參考表嗰標題。

3、揾到嗰界定點，睇翻邊嗰棟來表示嗰 grade

4、決定用近似定完全符合，喺呢個情景就用 TRUE 來表示揾 D2 值最近嗰個界定點。

C2 分級參考表背後嘅概念

如果用自動填充嗰陣，記得要固定嗰分級表嘅範圍。其中一個方法就係透過「絕對儲存格參照」。

去整一大堆相似嘅公式嗰陣，加嗰美金符號 ($) 喺嗰欄或列數度。上邊幅圖就比較咁比錢同唔比錢嘅分別。想再易明嗰關係，可以用「追蹤前導參照」呢個功能，用藍色箭嘴暫時顯示格格之間嘅關係。

呢度亦可以參考置地記者嘅《「Excel 防柒六件事」》嘅「六：用 VLOOKUP 唔用 $ ，何必呢？」。嗰引用數值可以點改都度，只要佢地有嗰參照關係預先定咗。其他 Office 軟件都有類似嘅方法，好似 Word 嘅郵件合併來用翻 Excel 啲數據咁，不過呢度就唔講太多。

定義名稱：比嗰名來代表一些成日用嘅儲存格

方法唔只有一個，仲有其他功能可以處理嗰分級表嘅範圍喺條 VLOOKUP 公式。先揀翻分級表嘅範圍，直接透過跟住落來的方法比嗰名佢 (gradeTable)。下次或者其他經手人都可以好快就 get 到嗰位嘅用途。名稱是一個參照嘅參照。

想用翻呢嗰名，好似 E2v 版本咁，打嗰函數嗰名，直接代咗原本嗰絕對參照就搞掂。日後想改嗰範圍，只要喺「名稱管理員」改就輕鬆得多。另外，上面嗰界面叫做「公式建立器」(Formula Builder)。遇到啲唔啲唔熟嘅函數，㩒下嗰 fx 圖示就可會有啲提示出來。

E2u | =VLOOKUP(D2,gradeTable!$A$2:$B$6,2,TRUE)
E2v | =VLOOKUP(D2,gradeTable,2,TRUE)

重用任何一格

唔止一個大範圍，就算一格都可以賦予一個名比佢。一開頭咪講用 IF 來睇嗰學生合唔合格嘅。我地今次可以將佢改成一個名，scorePassCutOff，放喺另一板到記住。假設下年嗰合格線由 50 改成 60，唯一要做嘅只係儲存格 B2 改動，關事嘅公式會一併 update 埋。

E2 原本 | =IF(D2>=50,"pass","fail")
E2 建議 | =IF(D2>scorePassCutOff,"pass","fail")

今日快嗰 2秒，第二日就慢 2個鐘

D 缺席同其他情況嗰分點計

嗰成績記錄仲有其他嘢，好似有啲考生缺席咗嗰測驗。ABS 用來識別佢地，而學校就用佢平時分打嗰八折。同埋，有啲監考老師仲捉到有人出貓，之後查咗真係作弊就會比零分作為懲罰。

嗰老師亦從校務署拎翻一啲學生的平時分以 CSV 格式記錄的資料。

得到所需的平時分資料，呢度我地用函數 SWITCH 來做特殊情況的分數調整。我地另開一板來寫埋啱先講嘅調整因子。

SWITCH 雖然比之前嘅公式長，但其實好易明嘅。成嗰架構都係先講比 Excel 知睇邊格先，再根據唔同嘅標籤再彈翻相應結果。如果冇符合條件嘅情況，就傳翻預設值。喺下邊，我斬向嗰 F2 格公式做三部分來逐一解釋。

F2 | =SWITCH(E2,"med",PRODUCT(medReasonMarkAdjustHomework,VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)),"cheat",PRODUCT(D6,cheatReasonMarkAdjustTest),D2)
A  | =SWITCH(E2,
B1 |         "cheat",PRODUCT(D6,cheatReasonMarkAdjustTest),
B2 |         "med",PRODUCT(medReasonMarkAdjustHomework,VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)),
C  |         D2)

A 指比 Excel 睇邊格
B 每個相應標籤應該傳回嘅數值
C 如果沒有符合 B 部分嘅條件則甚麼都不做，取回原始 D2 值

部分 B 裏，簡化做兩種情況：

B1| "cheat",PRODUCT(
  |           D6,
  |           cheatReasonMarkAdjustTest
  |           )

如果目標考生附上 “cheat” (B1)，咁佢地嗰最終分數就會，透過函數 PRODUCT 乘以預先定義嘅 cheatReasonMarkAdjustTest (即 0%)。

B2 | "med",PRODUCT(
   |         medReasonMarkAdjustHomework,
   |         VLOOKUP(A2,societyHomeWork.csv!societyHomework,2,FALSE)
   |      )

如果目標考生附上 “med” (B2)，咁佢地嗰最終分數就會，透過函數 PRODUCT 乘以預先定義嘅調整因子 (即 80%)與透過VLOOKUP找出嘅平時分 (即 95 分）。

喺呢度，我用咗 PRODUCT 這個函數。因為公式都要寫比人睇，所以唔用 a*b 星號，而用個可以自己解釋自己做咩嘅公式。咁樣做方便之後其他人都明條式點來。

E 統計下有幾多人合格

終於來到本文最後一節。每個學生考試經調整所得等級計曬出來，咁嗰老師想知道社會科嘅合格率。

1) 用 COUNTIF 與 COUNT

D7h | =(COUNTIF(D2:D6, "*")-COUNTIF(D2:D6,"F"))/COUNT(B2:B6)
D7k | =COUNTIF(D2:D6,"<>F"))/COUNT(B2:B6)

第一行同跟住嗰行嗰結果都喺一樣，都是合格的人 over 全班人數。

先講上邊嗰行 h 先，其中嘅(COUNTIF(D2:D6,”*")同 COUNT(B2:B6) 都係度計翻一班有幾多人。但前面嗰個用一個萬用字符條件（即星號，*) 來計計「任何」列嘅有幾多。

而下邊嗰行 k 就再簡單啲，喺 COUNTIF 用一個運算符不等於符號 <>，只計所有沒有 F ，即 A-E 的格。

兩隻寫法都可以計到嗰喺 D7 格嘅合格率係 0.6。可以喺 Excel 入面數字格式轉成常用的百分比。

總結：方法不只有一個

不同情況都有不同函數來做到同一效果。好似 IF 就啱輕量嘅邏輯比較而 VLOOKUP 就用來應付更複雜嘅情況。多考慮嗰寫法係咪方便後人使用或修訂。

人類讀得明唔等於機器都明

識點用資訊科技喺呢個世界非常重要。好似嗰老師拎到嘅同學平時分 CSV 檔，可以之後用來做其他應用。如果另一個寫字樓同事，比相同嘅數據，但係用「截圖」（雖然係電子檔案）或者「分級」而沒有提供原始分數。呢啲數據都需要嗰老師人手再執執先至可以用喺 Excel 。

睇埋

呢篇係係以前仲讀緊U嗰陣攞係Medium度比人參考（2019年5月20日）～去到宜家2021年文中VLOOKUP可以用XLOOPUP來處理，靈活更多少咁啲限制！

喜欢我的文章吗？
别忘了给点支持与赞赏，让我知道创作的路上有你陪伴。

加载中…