[紀錄] How to use Google Spreadsheet Formula

自從Google docs出現後，就越來越少使用Excel記錄資料了。

上個學期當了 TA，要記錄學生的小考作業成績還有總成績計算。
所以就研究了一下如何在spreadsheet上利用一些formula做簡單的成績計算。

記錄了一些除了SUM()以外，我所運用到的一些功能，希望能幫助到人。 XD


[計算不同分數區間的總人數]

有時會不想給學生看到其他學生的成績，但又想讓學生知道自己這次的表現落在哪個程度。
於是就需要分數區間表，可以讓學生知道自己這次表現的程度好壞。

這裡主要使用的是COUNTIF(範圍, 條件)：在所指定的範圍內，計算符合條件的總個數。

因此：

計算 90-100 (E11欄位)：COUNTIF(B2:B19, ">=90")
計算 80-89   (E10欄位)：COUNTIF(B2:B19, ">=80") - E11  // 即>=80的總人數 - >=90的總人數
計算 70-79   (E9欄位)：  COUNTIF(B2:B19, ">=70") - E10 - E11  // 即>=70的總人數 - >=80的總人數(即E10+E11)

依此類推，計算 60-69 的總人數可寫成 COUNTIF(B2:B19, ">=60") - E9 - E10 - E11
但是當要計算 0-9的總人數時，後面要減的欄位就太多了，因此可以把公式改寫成COUNTIF(B2:B19) - SUM(E3:E11)


[扣掉最低兩次成績]

通常我們都使用 MIN() 找到最低的那次成績，但是如果我想要扣掉的是每位學生的最低兩次成績呢?
當學生有6X多人時，一個個手算也太累了，而且還可能計算錯誤。

// 這裡假設學生考了11次小考，小考部份成績佔總成績的45%，於是我們想扣掉兩次最低分成績，只取9次，而每次小考都佔5%，共計45%。

這裡使用的是SMALL(範圍, 排序Index)：在所指定的範圍內，排序後找出其Index的值。
// 其中Index是指由小排到大後，依排序結果後的Index，因為最小的值其Index即為1，第二小的Index即為2

所以我們要扣掉最低分的兩次成績，公式可寫成：SUM(B2:L2) - MIN(B2:L2) - SMALL(B2:L2, 2)


[加分條件判斷]

有時候想對學生給予額外的bonus，就需要對所有學生作條件判斷，符合條件的就給予bonus分數。

// 這裡假設當學生全勤的時候，就額外加5分。

這裡使用了 IF(條件, 符合條件的值, 不符合條件的值)：此欄位的值會依據條件符合與否，給予不同結果

因此我們可把公式寫成： IF(SUM(B2:G2) = 6, 5, 0) // 6次點名都到，給5分，否則給0分。

[讀取跨分頁的值]

在做總成績的計算時，會想在目前的分頁上讀取其他分頁的值再做計算。

// 這裡假設我們已有三個分頁，各別計算作業(45%)、小考(45%)和點名(10%)的總成績。我們又開了一個分頁做總成績的加總，這時就需要讀取其他分頁的成績。

若想使用其他分頁的值就可以使用 '分頁名稱'!欄位名稱：此欄位的值會依據你填入的名稱抓取某分頁的某欄位值。
最後的總成績部分，也可以做一些特別處理。

例如：我想將加總後的總成績四捨五入，而且若超過100分地想直接以100分計算，於是公式就寫成：
=IF(ROUND(SUM(B2:E2))>100, 100, ROUND(SUM(B2:E2)))
// ROUND(值)：會將值做四捨五入。並利用IF() 去filter掉超過100的值。



另外，還有一些可能用到的小技巧，在這裡也順便說明一下

[設定欄寬]

原本預設的欄寬有120，當欄位很多時就必須要拉很長。
通常使用者都會直接用滑鼠拖曳縮小欄寬，但是有多個欄位時，每次拖曳的寬度有可能不同而造成畫面不整潔。

將要調整欄寬的欄位一次選取起來，並按下滑鼠右鍵，就會出現上方圖片的選單。
選取Resize columns，就會出現下方的小視窗

你就可以重新設定你要的欄寬或者使用Fit to data，使其配合資料自動設定欄寬。


[依據狀態設定格式]

對一個TA來說，應該會想用到低於60分就設為紅色字體的功能

選取要做狀態判斷的欄位，然後選取Format -> Conditional formating，就會看到下方的小視窗。

可以在這裡設定自己需要的rule，例如我設定的就是低於(Less than)60，字體顏色為紅色。
還可以利用+Add another rule，增加更多的設定規則。



以上就是學到的一些Google docs小技巧，雖然都很簡單但沒用過的人不免還是要花費一番時間，分享給大家嚕。