1 張
工作表
20 列
資料列
72 個
內建公式
完整內容預覽
| 學生成績單 | |||||||||||
| 學生姓名 | 學號 | 班級 | 國文 | 英文 | 數學 | 自然 | 社會 | 加權平均 | 及格率 | 班級排名 | |
| 王小明 | S001 | 高一甲 | 85 | 78 | 92 | 88 | 75 | =AVERAGE(D3:H3) | =COUNTIF(D3:H3,">=60")/5 | =RANK(I3,I3:I17,0) | |
| 李大華 | S002 | 高一甲 | 70 | 65 | 80 | 72 | 68 | =AVERAGE(D4:H4) | =COUNTIF(D4:H4,">=60")/5 | =RANK(I4,I3:I17,0) | |
| 張小麗 | S003 | 高一甲 | 90 | 85 | 95 | 90 | 88 | =AVERAGE(D5:H5) | =COUNTIF(D5:H5,">=60")/5 | =RANK(I5,I3:I17,0) | |
| 陳志明 | S004 | 高一甲 | 60 | 55 | 70 | 65 | 58 | =AVERAGE(D6:H6) | =COUNTIF(D6:H6,">=60")/5 | =RANK(I6,I3:I17,0) | |
| 林美玲 | S005 | 高一甲 | 75 | 80 | 68 | 70 | 82 | =AVERAGE(D7:H7) | =COUNTIF(D7:H7,">=60")/5 | =RANK(I7,I3:I17,0) | |
| 黃俊傑 | S006 | 高一甲 | 80 | 70 | 75 | 85 | 78 | =AVERAGE(D8:H8) | =COUNTIF(D8:H8,">=60")/5 | =RANK(I8,I3:I17,0) | |
| 吳佩珊 | S007 | 高一甲 | 65 | 72 | 60 | 58 | 70 | =AVERAGE(D9:H9) | =COUNTIF(D9:H9,">=60")/5 | =RANK(I9,I3:I17,0) | |
| 許家豪 | S008 | 高一甲 | 92 | 88 | 85 | 90 | 80 | =AVERAGE(D10:H10) | =COUNTIF(D10:H10,">=60")/5 | =RANK(I10,I3:I17,0) | |
| 高雅婷 | S009 | 高一甲 | 78 | 75 | 70 | 68 | 72 | =AVERAGE(D11:H11) | =COUNTIF(D11:H11,">=60")/5 | =RANK(I11,I3:I17,0) | |
| 周子軒 | S010 | 高一甲 | 50 | 60 | 55 | 62 | 58 | =AVERAGE(D12:H12) | =COUNTIF(D12:H12,">=60")/5 | =RANK(I12,I3:I17,0) | |
| 鄭怡君 | S011 | 高一甲 | 88 | 90 | 82 | 78 | 92 | =AVERAGE(D13:H13) | =COUNTIF(D13:H13,">=60")/5 | =RANK(I13,I3:I17,0) | |
| 謝承翰 | S012 | 高一甲 | 72 | 68 | 75 | 70 | 65 | =AVERAGE(D14:H14) | =COUNTIF(D14:H14,">=60")/5 | =RANK(I14,I3:I17,0) | |
| 郭子涵 | S013 | 高一甲 | 68 | 70 | 62 | 75 | 60 | =AVERAGE(D15:H15) | =COUNTIF(D15:H15,">=60")/5 | =RANK(I15,I3:I17,0) | |
| 曾柏凱 | S014 | 高一甲 | 55 | 60 | 50 | 65 | 52 | =AVERAGE(D16:H16) | =COUNTIF(D16:H16,">=60")/5 | =RANK(I16,I3:I17,0) | |
| 劉子晴 | S015 | 高一甲 | 82 | 78 | 85 | 80 | 75 | =AVERAGE(D17:H17) | =COUNTIF(D17:H17,">=60")/5 | =RANK(I17,I3:I17,0) | |
| 班級平均 | =AVERAGE(D3:D17) | =AVERAGE(E3:E17) | =AVERAGE(F3:F17) | =AVERAGE(G3:G17) | =AVERAGE(H3:H17) | =AVERAGE(I3:I17) | =AVERAGE(J3:J17) | ||||
| 班級及格率 | =COUNTIF(D3:D17,">=60")/COUNT(D3:D17) | =COUNTIF(E3:E17,">=60")/COUNT(E3:E17) | =COUNTIF(F3:F17,">=60")/COUNT(F3:F17) | =COUNTIF(G3:G17,">=60")/COUNT(G3:G17) | =COUNTIF(H3:H17,">=60")/COUNT(H3:H17) |
內建公式，自動計算
這份模板已寫好 72 個公式，您只需要填入原始數字，合計、小計與百分比都會自動更新。 線上編輯器會即時運算，下載後在 Excel 或 Google 試算表同樣有效。
- J4
=AVERAGE(D3:H3) - K4
=COUNTIF(D3:H3,">=60")/5 - L4
=RANK(I3,I3:I17,0) - M4
=IF(I3>=90,"優等",IF(I3>=80,"甲等",IF(I3>=70,"乙等",IF(I3>=60,"丙等","丁等")))) - J5
=AVERAGE(D4:H4)
怎麼使用這份模板
- 1. 線上編輯：點上方「線上編輯」直接在瀏覽器修改，不需安裝任何軟體，公式即時計算。
- 2. 存到我的專案：登入後可把編輯結果存到雲端，下次接著改；也能產生唯讀分享連結給同事或客戶看。
- 3. 下載 .xlsx：需要離線使用時下載，公式與格式完整保留，可用 Excel、Google 試算表、Numbers 開啟。