2014年1月18日

[教學]讓 Google 試算表製作的問卷試題能自動計分

A+
2015.7.8 公告:本文的方法在新版 Google Drive 將會失效,請改用「讓 Google 問卷(表單)能自動計分 V2」。

Google 試算表的威力十分強大,許多我們在電腦前無法監控、執行的事務,都能交給他全天候 24hr 不間斷執行,尤其跟 Blogger 結合後更顯得無所不能,可參考這篇「利用 Google 問卷(試算表)當小型資料庫」。能凸顯其威力的其中一個因素是,內建函數能做到的事就已經很廣泛(而且內建函數還不斷在擴充中),而做不到的事,還可自行另外寫程式(script)來實現。

此次「Blogger中文社群週年慶聖誕活動」,猜謎的部分利用試算表儲存問卷回答資料後,需要一個能全自動核對答案、並統計分數的系統。這對 Google 試算表而言,其實不是什麼難事,但可惜並沒有 Google 到任何中文網頁,來說明函數或是程式碼(script)要如何寫。

因此研究了一下這功能要如何實現,請依「二、製作能自動計分的試算表」的步驟進行,以後就能輕易地讓問卷試題自動計分了。

(圖片出處: lex100.com)


一、製作問卷試題


如果不熟悉如何製作問卷的步驟,可參考這篇「Google文件 2013新版問卷製作」;而製作問卷可能遇到的問題,可參考這篇 +Ken Lo 的技巧與心得「Google 問卷相關操作」。

以下是「Blogger 中文社群週年慶猜謎活動」的問卷,可內嵌到網頁:



其試算表的內容大致如這個網址:


為了能自動計分,WFU 必須搶先第一個回答問卷填入正確答案,讓資料存入試算表,供系統有比對答案的基準(該活動答案的說明可參考「Blogger中文社群週年慶__猜謎問題解答」)



二、製作能自動計分的試算表


1. 完成問卷後,進入試算表的畫面,假設如同「一、製作問卷試題」,已經完成了正確答案的填寫,就像下圖第一位填問卷者 Wayne Fu──



首先需注意第一題的位置,存放正確答案的儲存格是由 "F2" 開始,請記下這個位置;另外 "B 欄" 用來記錄答題者暱稱,這一點之後會用到。


2. 接著我們新增一個工作表,如下圖(按左下角的 "+" 即可)──



  • 新增的第二個工作表假設取名為 "計分",如上圖紅框;而原本儲存回答資料的工作表,假設名稱為 "WFU_exam",請務必記住這個自訂的工作表名稱。
  • 接著 A 欄要保留給答題者名稱,請空著
  • 從儲存格 B1 開始,利用滑鼠拖曳的方式,題目有幾題、數字就拉到多少(拉不成功只好手動打上 1~12),例如本次活動有 12 題,將從 B1~M1 來標示題號
  • 最後 N 欄註明 "總分" 即可。


3. 將游標移到 A1 儲存格,我們要將答題者名稱複製過來──



在 A1 貼上以下字串:

=query(WFU_exam!B:B)
  • 這個 query 函數的意思為,以後無論有多少人答題,都能將 "WFU_exam" 這個試算表 B 欄的所有資料複製過來,我們不必再手動複製,非常地方便。
  • "WFU_exam" 字串請置換為自訂的試算表名稱。
  • 如果原工作表(WFU_exam)的答題者名稱不在 B 欄,請自行置換對應的英文字母欄位。

2014.5.30 補充:將 A 欄的資料型態改為 "純文字",可避免儲存格只輸入數字時,會讓整欄資料錯亂。


4. 接著將游標移到 B2 欄位,我們要將進行改考卷的動作──



在 B2 貼上以下字串:

=ArrayFormula( if(ISBLANK(WFU_exam! F2:F)=false, if(WFU_exam! F2:F = WFU_exam! F$2, 10, 0), ) )
  • 這段函數一貼上之後,馬上可看到第 1 題所有人的分數都被統計出來了(答對得 10 分,答錯 0 分),不必再使用滑鼠拖曳來複製。
  • 所有藍色字串 F 代表「第 1 題從原工作表(WFU_exam)的 F 欄開始」,所有藍色字串 F 請改為自己第 1 題的起始欄位。
  • 紅色字串 100,請改為自訂的分數。

這段函數的意思不容易簡單解釋,大致的意思為「從儲存格 F2 開始檢查 → 如果 F 欄該儲存格有資料 → 一律跟 F2 比對資料 → 如果資料相同則得到 10 分 → 如果資料不同則得到 0 分」


5. 這個步驟沿用上個圖,請注意圖中的紅圈,滑鼠移到這個有藍色小正方形的位置,將他往右邊拖曳,直到最後一題為止,就能將步驟 4 的函數,從第 1 題複製到最後一題了。


6. 下面是滑鼠將藍色小正方形拉到第 12 題的狀況,我們可以發現,所有人每一題的答題分數,瞬間全部顯示出來了──




7. 最後一個步驟,將每個人的分數加總。滑鼠移動到總分那一欄、如下圖 N2 儲存格──



在 N2 貼上以下字串:

=MMULT( INDIRECT("B2:M" & count(B:B)), TRANSPOSE( ARRAYFORMULA( COLUMN( INDIRECT("B2:M" & count(B:B)))^0)))
  • 這段函數一貼上之後,馬上可看到所有人的分數被自動加總完畢。
  • 兩個紅色字母 M 代表最後一題的欄位,請改為自訂的最後一題欄位字母。
  • 所有藍色字母 B 代表第 1 題的欄位,可改為自訂的第 1 題欄位字母。

這段函數的意思比上一段函數更像天書,其原始出處為這個網頁「How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows」,原作者能找出這個方法真的是天才,因為一般的解法會使用 SUM 函數加總,但可惜 SUM 無法做到全自動執行的效果。原作者有用英文稍微解釋一下含意,不過用中文 WFU 也不會表達(跟陣列有關),有興趣可看原網頁並參考 Google 官方函數說明。

但是我的版本跟原作還是有一些差別,因為原作的版本只能處理固定範圍,意思就是只能一次性的處理,有額外的答題者時得手動操作、少於預定的答題者時畫面會不美觀。

現在 WFU 修改後的函數,不必設定初始答題人數,有再多的額外答題者,系統依然能全自動加總。想測試是否如 WFU 以上所言,可進入前面提供的問卷表單隨意填寫:



然後再到以下的試算表 → "計分" 工作表查看,



就能看到你的考卷是否已經被立即改好、並打上分數了!



三、小結


本文的函數內容很不好懂,不過有很強大的優點:

1. 當答題者數量無法確定的情況下,一般的函數無法處理這樣的情形,只能等所有人答題完畢後進行一次性的處理。每次有額外的答題者時,必須另外手動操作以複製儲存格的函數,才能進行計分動作。

2. 使用本文的函數後,不必假定答題者人數、或設定儲存格範圍,系統隨時都能全自動運算統計

這篇文章看起來不短,然而其實只需要貼上幾個函數、改一下參數就能完成。存成範本的話,以後使用的速度將更快,相信是一個很好的 "自動計分" 解決方案。


Google試算表 相關文章:

9 則留言:

  1. 如果複選題的部分,我需要"錯一個答案得6分,錯兩個得4分....以此類推",那要怎麼寫函數啊?

    回覆刪除
  2. <5245604386296503644>(以上內容請勿刪除,從括號之後開始留言)不好意思,目前比較沒閒暇解答這類問題,如果有寫程式的需求,可參閱 http://www.wfublog.com/2013/06/service-suggest-cooperation.html 用表單來聯繫 → 將依「5. 其他類諮詢」處理。

    回覆刪除
  3. <890479365943771530>(以上內容請勿刪除,從括號之後開始留言)好的我了解 謝謝

    回覆刪除
  4. 感謝站長分享 這真是小弟的救星!!

    回覆刪除
  5. 您好:
    我在總分的地方輸入公式,但是出現錯誤#VALUE! (MMULT函數第一參數)需要數字值,但()是空白,無法強迫成為數字,該怎麼處理呢? 萬分感謝!!!

    回覆刪除
  6. <1763124730879921005>(以上內容請勿刪除,從括號之後開始留言)請見開頭公告,改用 V2 試試看

    回覆刪除
  7. <4251376828260588548>(以上內容請勿刪除,從括號之後開始留言)
    謝謝你的回答~但是如果不是選擇題,是簡答題的話,還可以使用V2嗎?

    回覆刪除
  8. 請問,如果是很簡單的加減乘除,要怎麼設定自動加總?
    因為我已經設定好公式,可是有客人填單,表單仍無法自動加總,只能手動?

    回覆刪除
    回覆
    1. 請見開頭公告,改用 V2 試試看

      刪除

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
提問請附網址、詳細描述狀況,如提供的資訊不足,則無法回覆。
◎ 請在相關文章留言,與文章無關的主題請至「Blogger 中文論壇」。
◎ 若詢問 CSS 、非官方範本問題、或貴站為商業網站 ,請參考「本站諮詢頁面」→「1. 諮詢服務」
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 若發現留言不見了,通常是因為 "複製貼上" 的內容常被系統判定為垃圾留言,請不用擔心,我會定期將留言恢復。
◎ 本站「已關閉自刪留言功能」。