計概Ⅲ Excel 上機實習綜合作業(實習二、實習三)

實習二：

Ⓞ 注意： (1)請先下載自己雲端硬碟中的：t21-xx-excel.xlsx

                  (2)上機實習後，記得再上傳回自己雲端硬碟

                (3)全部完成時，再上傳至 Google  Classroom 

實習主題：Excel資料類別的設定、自定格式的設定、相對位址或絕對位址、
                  公式與函數的設定與使用

實習步驟：

      (0) 選取工作表：成績單，設定公式時注意使用相對位址或絕對位址，

           使用填滿控點複製公式時，選"填滿但不填入格式"

(1)於儲存格F4利用SUM()函數計算總分，
再用填滿控點將公式複製於F5:F24

(F4的公式=SUM(C4:E4)

(2)於儲存格G4利用SUMPRODUCT()函數計算加權總分,
再用填滿控點將公式複製於G5:G24

(G4的公式=SUMPRODUCT($C$2:$E$2,C4:E4))

(3)於儲存格H4計算平均(平均=加權總分/學分數),
並利用ROUND()函數取至小數點第1位，再用填滿控點將公式複製於H5:H24

(H4的公式==ROUND(G4/SUM($C$2:$E$2),1))

(4)於儲存格I4利用RANK()函數，依加權總分高低計算名次,
再用填滿控點將公式複製於I5:I24

(I4的公式=RANK(G4,$G$4:$G$24))

(5)請利用「設定格式化條件」將加權總分及平均的最高的前3名，
以藍色、粗體顯示；平均分數不及格，以紅色顯示

(6)請利用「自定格式設定」，使平均分數齊小數點，無小數點者不顯示0

(7)於儲存格F25利用COUNTIF()函數，計算國文成績不及格的人數
(F25的公式==COUNTIF(C4:C24,"<60"))

(8)於儲存格F26利用COUNT()函數，計算國文科應考總人數
(F26的公式=COUNT(C4:C24))

(9)插入27列，於儲存格F27利用AVERAGE()函數，計算國文成績平均分數，並利用ROUND()函數取至小數點第1位

(F27的公式=ROUND(AVERAGE(C4:C24),1))

(10)於儲存格F28利用MAX()函數，找出國文成績的最高分

(F28的公式=MAX(C4:C24))

(11)於儲存格F29利用MIN()函數，找出國文成績的最低分

選取F25:F28，再用填滿控點將公式複製於G25:I28

(F29的公式=MIX(C4:C24))

(12)於儲存格P5利用VLOOKUP()函數於成績表中，找出座號是儲存格P4的國文成績
  (F29的公式=VLOOKUP($P$4,$A$4:$H$24,3))

(13)用填滿控點將公式複製於P6:P8，分別找出座號是儲存格P4的英文、數學、平均成績

(14)於儲存格P9利用SUMIF()函數，計算修得學分

(P9的公式=SUMIF(P5:P7,">=60",L5:L7))

(15)於儲存格P10利用IF()函數判斷平均分數並顯示評語
(平均分數大於等於80為優良；80~60為中等；60以下為再加強)

(P10的公式=IF(P8>=80,"優良",IF(P8>=60,"中等","再加強")))

 

實習三：

實習主題：SUMIF( )、IF( )、MID( )、TODAY( )、DATEDIF( )
                  函數的設定與使用

實習步驟：

(1) 選取工作表：SUMIF

(2) 於儲存格F12利用SUMIF( )函數，計算「直排輪」的銷售總額

( F12的公式=SUMIF(A3:A10,"直排輪",F3:F10) )

(3) 選取工作表：DATEDIF

(4) 於儲存格E2利用TODAY( )函數，顯示今天的日期

( 不需引數 )

(5) 於儲存格D4利用 IF( )、MID( )函數，
     擷取儲存格C4的身分證字號第2碼判斷性別( 1為男生，2為女生 )

( D4的公式=IF(MID(C4,2,1)="1","男","女") )
     再用填滿控點將公式複製於D5:D8

(6) 於儲存格F4利用DATEDIF( )函數來計算生日與目並日期的年份差，
     以計算出年齡

( F4的公式=DATEDIF(E4,$E$2,"Y") )
     再用填滿控點將公式複製於F5:F8