用 vlookup 合併 (join) 試算表

身為寶可夢班級的上課老師， 要算成績很辛苦的~~ 同學們常常出去比賽， 可是不見得每個人都會參加每一場比賽； 有時會場還會不小心收集到其他班插花同學的成績。 每次收集到新成績時， 要把它跟現有的成績冊整合， 都是一件很費工的事。 本文介紹偷懶的方法。

[事實上本文要介紹的是如何用 vlookup() 函數來實作兩個 tables 的 join 運算 (資料庫的概念)， 不只適用於合併成績冊。 本文以 LibreOffice 實作； 若要用於 google sheet 或 MS excel， 只需要略改語法。]

假設你已有一個 (ods 或 xls 格式的) 「成績總表」 內含寶可夢們的學號、 姓名、 許多次的成績。 另有一個新的 .csv 檔， 內含某次小考的新成績及寶可夢們的學號； 但並不是每位同學都有考到這次小考。 我們的目標是要把新成績貼到原先的成績總表裡面去。

有一種方法是對新成績的 .csv 檔採用學號排序， 然後用工人智慧 (人眼+手工) 找出缺考的同學， 小心地一段段剪貼。 或是， 排序完之後即可在命令列下採用 join 指令合併， 再剪貼回成績總表。 以前我一直都採用 join 指令這個方法， 比較不怕粗心弄錯， 但每次都要做頭腦體操， 還是有點辛苦。

最近才突然想到： vlookup 根本就是為這類問題而設計的試算表函數! 用 vlookup 做， 連「排序新成績」 那一步也可以省下來了。 (呃.. 也許這是常識.. 可是我沒認真學過試算表， 也不太滿意搜尋到的中文教學文， 所以還是分享一下好了。)

1. 在成績總表最左側新增兩個空白欄。 假設現在學號欄變成 C 欄。
2. 開一個新分頁 (圖中青色框框部分) 命名為 new 好了。 順便把原先的主分頁改命名為 main。
3. 在 new 分頁裡， 把新的 csv 內容貼到第 B、C 兩欄。 假設 B 欄是學號， C 欄是這次的新成績。
4. 點選 new 分頁的 A1 (或 A10 或 A 欄任何一格皆可)。
5. 填入以下算式： =VLOOKUP(B1,main.C$1:D$99,2,FALSE()) 然後把這個算式向下拖滿整欄到資料結束為止。 意思是： 拿我右邊那格到 main 分頁的 C$1 到 D$99 表格中去查詢， 查到的話就把 (從查詢表格範圍的最左邊開始數起) 第 2 欄的內容貼上； 沒查到的話就填 #N/A。 這一步非必須； 只是要確認新成績都屬於本班同學。 例如圖中 s36 明明不是本班同學， 卻跑來插花出現在新成績當中。 應該去了解一下是剛轉班進來還是怎樣、 是否要把成績轉給他們班的老師。
6. 回到 main 主分頁， 點選 A1。
7. 填入以下算式： =VLOOKUP(C1,new.B$1:C$99,2,FALSE()) 然後把這個算式向下拖滿整欄到資料結束為止。 同樣地， 這意思是： 拿我右邊那格到 new 分頁的 B$1 到 C$99 表格中去查詢， 並填上第二欄的值。 現在誰缺考就很清楚了。
8. 在 B1 那一格填入： =IF(ISNA(A1),0,A1)。 向下拉， 填滿整欄， 也就是把缺考的同學通通算零分!
9. 複製 B1 這一欄， 然後在成績冊右方新的空白欄 「paste special」 (以特別方式貼上)， 只貼數值就好。 新成績合併成功!

請參考範例檔 grade.ods 。

完成之後， A、 B 兩欄還有 new 分頁都不要刪掉。 如果嫌礙眼， 大不了把 A、 B 兩欄藏起來就好。 保留這兩欄及 new 分頁， 以後再有新的成績進來， 只需要重複 3、 9 兩步即可， 不必再每次頭腦體操， 大腦就可以休息了， 耶! (咦?)

ps. 如果是 google sheet 或 excel， 只要把 "main." 改成 "main!"、 把 "new." 改成 "new!"、 FALSE() 改成 FALSE 即可。