2016年10月9日 星期日

用 vlookup 合併 (join) 試算表

用 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 即可。

沒有留言:

張貼留言