實驗4-8Excel2010的綜合應用

1、.實驗 4-9 Excel 2010的綜合應用【實驗目的】1) 掌握 Excel 2010 的各個知識點?！鞠嚓P(guān)知識】【實驗內(nèi)容】1) 成績分析小李是北京某政法學院教務處的工作人員，法律系提交了2012 級 4 個法律專業(yè)教學班的期末成績單， 為更好地掌握各個教學班學習的整體情況，教務處領(lǐng)導要求她制作成績分析表，供學院領(lǐng)導掌握宏觀情況。請根據(jù)已有數(shù)據(jù)，幫助小李完成2012 級法律專業(yè)學生期末成績分析表的制作。具體要求如下：打開“年級期末成績分析.xlsx”文件。在“ 2012 級法律”工作表最右側(cè)依次插入“總分”、“平均分”、“年級排名”列；將工作表的第一行合并為一個單元格，并設置黑體、16

2、磅，使其成為工作表的標題。對班級成績區(qū)套用帶標題行的“表樣式中等深淺15”的表格格式。設置所有列的對齊方式為居中，其中排名為整數(shù)，其他成績的數(shù)值保留1 位小數(shù)。在“ 2012 級法律”工作表中，利用公式分別計算“總分”、“平均分”、“年級排名”列的值。對學生成績不及格（小于60）的單元格套用格式突出顯示為“黃色（標準色）填充色紅色（標準色）文本”。提示：“黃色（標準色）填充色紅色（標準色）文本”默認的設置格式中沒有，需要進行自定義。表格工具設計樣式條件格式突出顯示單元格規(guī)則小于，在“小于”對話框設置“自定義格式”，在“填充”選項卡中設置背景色為黃色（標準色），在“字體”選項卡中設置字體顏色為紅

3、色（標準色）。 在“ 2012 級法律”工作表中，利用公式根據(jù)學生的學號將其班級的名稱填入“班級”列，規(guī)則為：學號的第三位為專業(yè)代碼、第四位為班級序號，即：01法律一班02法律二班03法律三班04法律四班提示：使用MID+IF 函數(shù)。根據(jù)“ 2012 級法律”工作表，創(chuàng)建一個數(shù)據(jù)透視表，放置于表名為“班級平均分”的新工作表中， 工作表的標簽顏色設置為紅色。要求數(shù)據(jù)透視表中按照英語、體育、計算機、近代史、法制史、刑法、民法、法律英語、立法法的順序統(tǒng)計各班各科成績的平均分，其中行標簽為班級。為數(shù)據(jù)透視表格內(nèi)容套用帶標題行的“數(shù)據(jù)透視表樣式中等深淺15”的表格格式，所有列的對齊方式設為居中，成績的數(shù)

4、值保留1 位小數(shù)。1 / 5.提示：工作表標簽設置為紅色：右鍵點擊工作表標簽選擇“工作表標簽顏色”。在“班級平均分”工作表中，針對各課程的班級平均分創(chuàng)建二維的簇狀柱形圖，其中水平簇標簽為班級，圖例項為課程名稱，并將圖表放置在表格下方的A10： H30 區(qū)域中。保存工作簿。2) 計算工資小李是東方公司的會計， 利用自己所學的辦公軟件進行記賬管理，為節(jié)省時間， 同時又確保記賬的準確性，她使用Excel 編制了 2014 年 3 月員工工資表。請你根據(jù)下列要求幫助小李對該工資表進行整理和分析。打開“東方公司2014 年 3 月員工工資表 .xlsx”。通過合并單元格，將表名 “東方公司 2014 年

5、 3 月員工工資表” 放于整個表的上端，居中，并調(diào)整字體為黑體、14。在“序號”列分別填入1 到 15，將其數(shù)據(jù)格式設置為數(shù)值，保留0 位小數(shù)，居中。將“基礎(chǔ)工資” （含）往右各列設置為會計專用格式、保留2 位小數(shù)、無貨幣符號。調(diào)整表格各列寬度，使得各列內(nèi)容能夠顯示，并設置居中對齊。設置紙張大小為A4、橫向，整個工作表需調(diào)整在1 個打印頁內(nèi)。參考“工資薪金所得稅利率”工作表信息，利用IF 函數(shù)計算“應交個人所得稅”列（提示：應交個人所得稅=應納稅所得額 * 對應稅率 -對應速算扣除數(shù)） 。利用公式計算“實發(fā)工資列” ，公式為：實發(fā)工資=應付工資合計 -扣除社保 -應交個人所得稅。復制工作表“

6、2014 年 3 月”，將副本放置到原表的右側(cè)，并命名為“分類匯總”。在“分類匯總”工作表中通過分類匯總功能求出各部門“應付工資合計”、“實發(fā)工資”的和，每組數(shù)據(jù)不分頁。=IF(K3<=1500,K3*sl!$B$2-sl!$C$2,IF(K3<=4500,K3*sl!$B$3-sl!$C$3,IF (K3<=9000,K3*sl!$B$4-sl!$C$4,IF(K3<=35000,K3*sl!$B$5-sl!$C$5,IF(K 3<=55000,K3*sl!$B$6-sl!$C$6,IF(K3<=80000,K3*sl!$B$7-sl!$C$7,K3*sl

7、 !$B$8-sl!$C$8)3)考分統(tǒng)計（本題涉及函數(shù)操作較為復雜，可選做）：濱海市對重點中學組織了一場物理統(tǒng)考， 并生成了所有考生和每一個題目的得分。 市教委要求小羅教師根據(jù)已有數(shù)據(jù)， 統(tǒng)計分析各學校及班級的考試情況。 請根據(jù)已有數(shù)據(jù)， 統(tǒng)計分析各學校及班級的考試情況，幫助小羅完成此項工作。具體要求如下：打開“濱海市2015 年春高二物理統(tǒng)考情況分析.xlsx”文件。11 利用“成績單” 、“小分統(tǒng)計” 、“分值表”工作表中的數(shù)據(jù)，完成“按班級匯總”和“按學校匯總”工作表中相應空白列的數(shù)值計算。具體提示如下：i.“考試學生數(shù)”列必須利用公式計算，“平均分”列由“成績單”工作表數(shù)據(jù)計算得出。

8、ii.“分值表” 工作表中給出了本次考試各題的類型及分值（備注： 本次考試一共50 小題，其中 140 為客觀題， 4150 為主觀題）。iii.“小分統(tǒng)計” 工作表中包含了各班級每一道小題的平均分，通過其可以計算出各班級的“客觀題平均分”和“主觀題平均分”（備注：由于系統(tǒng)生成每題平均分時已經(jīng)進行了四舍五入操作，因此通過其計算“客觀題平均分”和“主觀題平均分” 之和時， 可能與根據(jù) “成績單” 工作表的計算結(jié)果存在一定誤差）。iv. 利用公式計算 “按學校匯總” 工作表中的 “客觀題平均分” 和“主觀題平均分” 。計算方法為： 每個學校的所有班級相應平均分乘以對應班級人數(shù)，相加后再除以該校的總

9、考生數(shù)。2 / 5.v. 計算“按學校匯總” 工作表中的每題得分率， 即：每個學校所有學生在該題上的得分之和除以該?？偪忌鷶?shù)，再除以該題的分值。vi. 所有工作表中“考試學生數(shù)” 、“最高分”、“最低分”顯示為整數(shù)；各類平均分顯示為數(shù)值格式， 并保留 2 位小數(shù)； 各題得分率顯示為百分比數(shù)據(jù)格式， 并保留 2 位小數(shù)。12 新建“按學校匯總 2”工作表，將“按學校匯總”工作表中所有單元格數(shù)值轉(zhuǎn)置復制到新工作表中。提示：選擇性粘貼（粘貼：數(shù)值；運算：轉(zhuǎn)置）13將“按學校匯總2”工作表中的內(nèi)容套用表格樣式為“表樣式中等深淺12”；將得分率低于80%的單元格標記為“淺紅填充色深紅色文本”格式，將介于

10、80%和 90%之間的單元格標記為“黃填充色深黃色文本”格式。提示：開始樣式功能區(qū)套用表格樣式和條件格式14保存 “濱海市2015 年春高二物理統(tǒng)考情況分析.xlsx ” 文件。提示：“按班級匯總”工作表中“考試學生數(shù)”列使用函數(shù)COUNTIFS舉例：“按班級匯總” 工作表中C2 單元格的計算條件為“學?！?為“濱海市第一中學”且“班號”為“1”的“考試學生數(shù)” ，C2 單元格輸入公式：=COUNTIFS(成績單 !$A$2:$A$950, 按班級匯總 !$A2, 成績單 !$B$2:$B$950, 按班級匯總 !$B2)“最高分”使用函數(shù)MAX+IF舉例：“按班級匯總”工作表中D2 單元格的

11、計算條件為“學?！睘椤盀I海市第一中學”且“班號”為“1”的“最高分” ，D2 單元格輸入公式：=MAX(IF(成績單 !$A$2:$A$950= 按班級匯總 !$A10)*( 成績單 !$B$2:$B$950=按班級匯總!$B10), 成績單 !$D$2:$D$950,0)最后按 Ctrl+Shift+Enter組合鍵確定。說明：(成績單 !$A$2:$A$950= 按班級匯總 !$A2)*( 成績單 !$B$2:$B$950=按班級匯總 !$B2)相當于對 “學?！?為“濱海市第一中學” 且“班號”為“ 1”條件的判定，成立結(jié)果為“ 1”，否則為“ 0”。成績單 !$D$2:D$950) 則

12、“學?！?為“濱海市第一中學”且“班號”為“ 1”的學生保留原成績，其余學生成績計算結(jié)果為 0。在以上成績中找最大值，可以得到要求的結(jié)果。最后一個乘法涉及到數(shù)組相乘，需要用 Ctrl+Shift+Enter組合鍵確認?！白畹头帧笔褂煤瘮?shù)MIN+IF舉例：“按班級匯總” 工作表中E2 單元格的計算條件為“學校” 為“濱海市第一中學”且“班號”為“1”的“最低分” ，E2 單元格輸入公式：=MIN(IF(成績單 !$A$2:$A$950= 按班級匯總 !$A10)*( 成績單 !$B$2:$B$950=按班級匯總!$B10), 成績單 !$D$2:$D$950,100)最后按 Ctrl+Shift

13、+Enter組合鍵確定?！捌骄帧笔褂煤瘮?shù)AVERAGEIFSF2 單元格輸入公式：3 / 5.=AVERAGEIFS(成績單 !$D$2:$D$950, 成績單 !$A$2:$A$950, 按班級匯總 !$A2,成績單!$B$2:$B$950, 按班級匯總 !$B2)“主觀題平均分”和“客觀題平均分”使用函數(shù)SUMG2 單元格輸入公式：=SUM(小分統(tǒng)計 !$C2:$AP2)H2 單元格輸入公式：=SUM(小分統(tǒng)計 !$AQ2:$AZ2)注意：公式中哪些要使用絕對地址，哪些要使用相對地址。“考試學生數(shù)” 、“最高分” 、“最低分” 顯示為整數(shù)； 各類平均分顯示為數(shù)值格式，并保留 2 位小數(shù)。

14、提示：“按學校匯總”工作表中“考試學生數(shù)”列使用函數(shù)COUNTIFS舉例：“按班級匯總” 工作表中 B2 單元格的計算條件為 “學校” 為“濱海市第一中學”的“考試學生數(shù)” ，B2 單元格輸入公式：=COUNTIFS(成績單 !$A$2:$A$950,A2)“最高分”使用函數(shù)MAX+IFC2 單元格輸入公式：=MAX(IF(成績單 !$A$2:$A$950= 按學校匯總 !$A2),成績單 !$D$2:$D$950,0)按 Ctrl+Shift+Enter組合鍵確定?！白畹头帧笔褂煤瘮?shù) MIN+IFD2 單元格輸入公式：=MIN(IF(成績單 !$A$2:$A$950= 按學校匯總 !$A2)

15、, 成績單 !$D$2:$D$950,100)按 Ctrl+Shift+Enter組合鍵確定?！捌骄帧笔褂煤瘮?shù) AVERAGEIFSE2 單元格輸入公式：=AVERAGEIFS(成績單 !$D$2:$D$950, 成績單 !$A$2:$A$950, 按學校匯總 !$A2)“主觀題平均分”和“客觀題平均分”使用函數(shù)SUM+IFF2 單元格輸入公式：=SUM(IF(按班級匯總 !$A$2:$A$33=按學校匯總 !$A2,按班級匯總 !$C$2:$C$33* 按班級匯總!G$2:G$33,0)/$B2按 Ctrl+Shift+Enter組合鍵確定。G2 單元格輸入公式：=SUM(IF(按班級匯總 !$A$2:$A$33=按學校匯總 !$A2,按班級匯總 !$C$2:$C$33* 按班級匯總!H$2:H$33,0)/$B2按 Ctrl+Shift+Enter組合鍵確定?！啊?1】得分率 ”使用函數(shù) SUM+IFH2 單元格輸入公式：=SUM(IF(小分統(tǒng)計 !$A$2:$A$33= 按學校匯總 !$A2,小分統(tǒng)計 !C$2:C$33*按班級匯總!$C$2:$C$33,0)/$B2/ 分值表 !B$3按 Ctrl+Shif