範例評量---損益表 製作

評量一。

ＰＳ：累計盈虧 與 折扣 要注意　儲存格位址的鎖定方式～

評量二。

201成績計算表

01．「國文」、「數學」、「英文」工作表：

Ａ．「日常紙筆」欄（Ｉ７～Ｉ１６）：
　　使用ＳＵＭ與ＭＩＮ函數，計算四次高分成績之平均。

Ｂ．「日常考查」欄（Ｌ７～Ｌ１６）：

使用ＡＶＥＲＡＧＥ函數，計算二次小考之平均。

Ｃ．「學期成績」欄（Ｐ７～Ｐ１６）：
　　ＳＵＭＰＲＯＤＵＣＴ函數，計算各成績與所佔比例之和。

ＡＮＳ：

選擇ð國文、數學、英文等三張工作表（利用工作群組來做計算節省作業時間）

þＡ…選擇Ｉ７～Ｉ１６è資料編輯列上輸入（如圖）

þＢ…選擇Ｌ７～Ｌ１６è資料編輯列上輸入（如圖）

þＣ…選擇Ｐ７～Ｐ１６è資料編輯列上輸入（如圖）

ＰＳ：在工作群組（國．英．數）任一工作表上，按右鍵ð取消工作表群組設定

02．「成績統計」工作表：

Ａ．　將「國文」、「數學」、「英文」工作表之學期成績（Ｐ７～Ｐ１６）資料，

　　以貼上連結的方式置入Ｃ３～Ｃ１２、Ｄ３～Ｄ１２、Ｅ３～Ｅ１２。

Ｂ．「總分」欄（Ｆ３～Ｆ１２）：

　　使用ＳＵＭ計算每科學期成績之總和。

Ｃ．「名次」欄（Ｇ３～Ｇ１２）：

　　依據「總分」成績使用ＲＡＮＫ．ＡＶＧ函數填入每人之排名。

ＡＮＳ：

þＡ…選擇ð國文工作表之學期成績（Ｐ７～Ｐ１６）資料，

按下複製　鈕ð在國文工作表Ｃ３點一下…按貼上6貼上連結（如圖所示）

（照此步驟完成數學．英文工作表）

þＢ…選擇Ｆ３～Ｆ１２，輸入函數=SUM(C3:E3)或自動加總鈕

þＣ…選擇Ｇ３～Ｇ１２è料編輯列上輸入=RANK.AVG(F3,$F$3:$F$12,0)

完成參考結果如圖所示

104在職訓練班學生選課資料內容

01、「學員基本資料」工作表
●將學號搬移到Ａ欄。
●「年齡」欄位：利用DATEDIF、TODAY函數計算年齡，格式為「X歲」。

ANS：
●●選取學號欄位…按 剪下" è直接在A欄按右鍵 ó 插入剪下的儲存格 即可。
●●選擇Ｄ２到Ｄ１２０，資料編輯列上輸入並自訂格式（如圖所示）

02、插入新工作表，名稱為「修課資料」。
Ａ、將「學員基本資料」工作表中「上課期別」及「班別」二欄搬移至Ｃ、Ｄ欄。
Ｂ、將「學員基本資料」工作表中「學號」、「姓名」複製到Ａ、Ｂ欄。


ANS：
●
在插入工作表 的圖示中，按2下更改工作表名稱為 修課資料。
●點學員本資料表ó選Ｊ和Ｋ欄ó按剪下"è點修課資料表ó指標放在Ｃ１按ENTER鍵。

●點學員本資料表ó選Ａ和Ｂ欄ó按複製è點修課資料表 指標放在Ａ１按ENTER鍵。


03、「修課資料」工作表繪製框線：
Ａ、Ａ１～Ｄ１２０外框線為次粗實線。
Ｂ、設定Ｂ２～Ｂ１２０範圍的儲存格與「學員基本資料」工作表的年齡欄位色彩相同。

ANS：
●選取Ａ１～Ｄ１２０ó選　／常用／儲存格／格式‚‚‚…儲存格格式‚‚‚‚‚（如圖所示）

●選取Ｂ１～Ｂ１２０ó　選　／常用／字型…填滿色彩　為學員資料表中Ｄ２的色彩

04、「開課期別」工作表的「結束日期」欄位：
依據「開課班別」工作表內的課程時數，每次上課時間為３小時，每週上課一次，以此計算課程結束的日期。

ＡＮＳ：

０１、選Ｄ２到Ｄ２８，資料編輯列上輸入

=C2+(VLOOKUP(B2,開課班別!$A$2:$C$15,3,0)/3-1)*7

完成參考結果如下：

103個人每日早晚的血壓紀錄表

01、欄寬、列高：A欄20、B~E欄為15、1~61列的列高為25。

ANS：

●選取A欄（如圖）ó執行／常用／儲存格／格式　è　欄寬　（輸入２０）

●在資料編輯列上輸入１：６１（按ＥＮＴＥＲ）（如圖）

ó執行／常用／儲存格／格式　è　列高　（輸入２５）

 02、「日期」欄位：
指定日期格式為「西元年－美式月份縮寫－日（美式星期縮寫）」，

例如2010/11/1的格式為 2010-Nov-01(Mon)。

ANS：

●指標放在A2（按住ctrl + shift +方向鍵↓）ó選取A2到A61

ó執行／常用／數值　è　（如圖設定）

 03、「收縮壓」欄位：設定格式化條件，條件依序如下：
Ａ、格式化條件一：當數值＞１３９時，顯示淺紅色填滿與深紅色文字。
Ｂ、格式化條件二：指定圖示集中的三箭頭（彩色）格式，

當＞＝１４０時，為上升箭頭，

＞＝１２０且＜１４０時，為平行箭頭；

其他為下降箭頭。

ANS：

●指標放在Ｃ2（按住ctrl + shift +方向鍵↓）ó選取Ｃ2到Ｃ61

ó執行／常用／樣式／設定格式化條件　è　管理規則（如圖設定）

è　點　新增規則（如圖設定）

è點 格式鈕 設定填滿與文字色彩

●再按 新增規則 (如圖)ó 

  



è點 格式樣式選取 圖示集(如圖設定)

最後記得調整一下規則的先後順序（如圖所示）

04、「舒張壓」欄位：設定格式化條件，套用「資料橫條／漸層填滿／紫色資料橫條」格式

ANS：

●指標放在Ｄ2（按住ctrl + shift +方向鍵↓）ó選取Ｄ2到Ｄ61

ó執行／常用／樣式／設定格式化條件（如圖設定）

05、「心跳」欄位：設定格式化條件，套用色階的「綠-黃色階」色階格式。

ANS：

●指標放在E2（按住ctrl + shift +方向鍵↓）ó選取E2到E61

ó執行／常用／樣式／設定格式化條件（如圖設定）

完成參考結果如下

102學期成績計算表

01、使用Average函數計算「第一次定期考查」、「第二次定期考查」、「第三次定期考查」、「日常考查」的「學期成績」(四次平均)，且以Round函數取至整數位。

ANS：

選取 H2 到 H31，資料編輯列上輸入

按CTRL+ENTER即可填滿並複製公式。

02、「趨勢圖」(I2~I31)欄位：

●依據「第一次定期考查」、「第二次定期考查」、「第三次定期考查」、「日常考查」的「學期成績」欄位資料顯示直條圖類型的走勢群組。

●套用「走勢圖樣式彩色#6」。

●標示高點與最終點。

ANS：

●選取 I2到 I31，／插入／走勢圖／直條圖

●點選　走勢圖工具／設計／樣式改為「走勢圖樣式彩色#6」

●勾選高點與最後點

03、設定「成績總表」工作表之「學期成績」欄的格式條件：

●條件1：三符號無框圖示集，分數<50顯示X，>=50顯示！，>=60顯示ü。

●條件2：儲存格值的條件設定：成績大於等於60分，字體顏色為藍色。成績小於60分，字體顏色為紅色。

ANS：

●選取 Ｈ2到 Ｈ31，／常用／樣式／設定格式化條件－－＞點選　管理規則
　點…新增規則…　圖案樣式　選擇　三符號　無框，並更改　值、類型（如圖）

●點…新增規則…設定如圖所示（按下確定鈕）再重複設定成績小於６０分的設定

最後一定要利用上下鈕去調整格式化條件的順序～切記！！

04、插入SmartArt發散星形圖，變更色彩為「彩色-輔色」、SmartArt樣式為「光澤」，位置為A1~G17。

ANS：點選「成績計算流程」工作表

／插入／圖例／SmartArt（如圖點選　發散星形圖）

並設定色彩、樣式、移動位置到 A1~G17(如圖)

05、SmartArt發散星形圖內容設定：

●依序輸入「學期成績」、「第一次定期考查」、「第二次定期考查」、「第三次定期考查」、「日常考查」，字型大小為11、微軟正黑體。

(提示：文字呈現的方式，請參閱參考結果圖)

●改變「日常考查」及其右側箭頭物件的圖案樣式為「鮮明效果-橙色，輔色6」。

●「學期成績」文字樣式為「填滿-紅色，輔色2，霧面質感浮凸」，並改變文字填滿色彩為深藍色，改變字型大小為16。

ANS：如圖

記得換行按SHIFT+ENTER

點選物件框更改字型與大小(如圖)

選取「日常考查」及其右側箭頭物件的圖案…／Smart Art工具／格式／圖案樣式改為「鮮明效果-橙色，輔色6」（如圖）

點選「學期成績」…／Smart Art工具／格式／文字藝術師樣式改為「填滿-紅色，輔色2，霧面質感浮凸」；文字填滿　點選深藍色。

101教師聯誼會年度收支表

01、將第一列之標題文字「教師聯誼會年度收支表」設定自A1至G1合併儲存格、文字水平置中對齊。

   ANS：選取A1到G1按跨欄置中鈕(如圖所示)

02、在G4~G8範圍以SUM函數計算每一項目的合計金額

    

    ANS：選取G4到G8按下   自動加總鈕

03、在G9~F9範圍計算每一季的餘絀(注意公式中需包含SUM函數)

    

    ANS：選取G9到F9在資料編輯列上輸入

04、將所有「合計」及「餘絀」的數字顯示格式設定為數值類別中的「1,234」紅色格式

    

    ANS：選取 G4到G8、按住CTRL不放 再選取 C9到F9，點選

05、儲存格設定：

        A. 「餘絀」： A9 至 B9 設定合併儲存格，且文字水平置中對齊。

        B.  G9 儲存格輸入全形 "－" 符號，且文字水平置中對齊。

  

    ANS：

       A、選取 A9 至 B9 按 跨欄置中鈕

       B、點選 G9 的儲存格輸入鍵盤上的 減號(記得用全形喔~)，再按一下對齊方式的水平置中

參考結果如下圖所示