Excel 函數(shù)操作題你會(huì)嗎？

Excel 函數(shù)操作題你會(huì)嗎？

小李是北京某政法學(xué)院教務(wù)處的工作人員，法律系提交了2012級(jí)四個(gè)法律專業(yè)教學(xué)班的期末成績單，為更好地掌握各個(gè)教學(xué)班學(xué)習(xí)的整體情況，教務(wù)處領(lǐng)導(dǎo)要求她制作成績分析表，供學(xué)院領(lǐng)導(dǎo)掌握宏觀情況。請(qǐng)根據(jù)考生文件夾下的“Excel素材.xlsx”文檔，幫助小李完成2012級(jí)法律專業(yè)學(xué)生期末成績分析表的制作。具體要求如下：

1. 在考生文件夾下，將“Excel素材.xlsx”文件另存為“Excel.xlsx”(“.xlsx”為擴(kuò)展名)，后續(xù)操作均基于此文件，否則不得分。

2. 在“2012級(jí)法律”工作表最右側(cè)依次插入“總分”、“平均分”、“年級(jí)排名”列;將工作表的第一行根據(jù)表格實(shí)際情況合并居中為一個(gè)單元格，并設(shè)置合適的字體、字號(hào)，使其成為該工作表的標(biāo)題。對(duì)班級(jí)成績區(qū)域套用帶標(biāo)題行的“表樣式中等深淺15”的表格格式。設(shè)置所有列的對(duì)齊方式為居中，其中排名為整數(shù)，其他成績的數(shù)值保留1位小數(shù)。

3. 在“2012級(jí)法律”工作表中，利用公式分別計(jì)算“總分”、“平均分”、“年級(jí)排名”列的值。對(duì)學(xué)生成績不及格(小于60)的單元格套用格式突出顯示為“黃色(標(biāo)準(zhǔn)色)填充色紅色(標(biāo)準(zhǔn)色)文本”。

4. 在“2012級(jí)法律”工作表中，利用公式、根據(jù)學(xué)生的學(xué)號(hào)、將其班級(jí)的名稱填入“班級(jí)”列，規(guī)則為：學(xué)號(hào)的第三位為-碼、第四位代表班級(jí)序號(hào)，即01為“法律一班”，02為“法律二班”，03為“法律三班”，04為“法律四班”。

5. 根據(jù)“2012級(jí)法律”工作表，創(chuàng)建一個(gè)數(shù)據(jù)透視表，放置于表名為“班級(jí)平均分”的新工作表中，工作表標(biāo)簽顏色設(shè)置為紅色。要求數(shù)據(jù)透視表中按照英語、體育、計(jì)算機(jī)、近代史、法制史、刑法、民法、法律英語、立法法的順序統(tǒng)計(jì)各班各科成績的平均分，其中行標(biāo)簽為班級(jí)。為數(shù)據(jù)透視表格內(nèi)容套用帶標(biāo)題行的“數(shù)據(jù)透視表樣式中等深淺15”的表格格式，所有列的對(duì)齊方式設(shè)為居中，成績的數(shù)值保留1位小數(shù)。

6. 在“班級(jí)平均分”工作表中，針對(duì)各課程的班級(jí)平均分創(chuàng)建二維的簇狀柱形圖，其中水平簇標(biāo)簽為班級(jí)，圖例項(xiàng)為課程名稱，并將圖表放置在表格下方的A10:H30區(qū)域中。

對(duì) 應(yīng) 步 驟

1、復(fù)制Excel素材，右擊重命名為Excel即可。

2、在右側(cè)三列依次輸入：總分、平均分、年級(jí)排名;選中A1：O1→單擊合并后居中

為標(biāo)題換個(gè)字體，增大字號(hào)

選中A2：O102區(qū)域，套用表格樣式→選中等深淺樣式15

勾選表包含標(biāo)題，確定

所有列居中對(duì)齊

選中各科成績區(qū)域右擊設(shè)置單元格格式→數(shù)值→保留1位小數(shù)

選中排名區(qū)域右擊設(shè)置單元格格式→數(shù)值→保留0位小數(shù)

3、總分等于各科成績總和，直接用sum函數(shù)求和即可;sum(數(shù)據(jù))

輸入=sum() 然后在括號(hào)里面選取這些數(shù)據(jù)即可。

同理求平均分用average函數(shù)。average(數(shù)據(jù))

求排名要用rank函數(shù)，rank函數(shù)參數(shù)(排名的數(shù)據(jù)，排名的區(qū)域)

寫函數(shù)根據(jù)參數(shù)套用即可,根據(jù)總分進(jìn)行排名，排名的數(shù)據(jù)為M3單元格，排名的區(qū)域?yàn)榭偡诌@一列

即=RANK(M3,M3:M102) (提示：因?yàn)槲覀兲子昧吮砀駱邮剑跃惋@示成了總分這個(gè)公式名稱，這些名稱不需要自己輸入，做題的時(shí)候直接選擇M3單元格，和M3:M102就可以了)

(rank函數(shù)不會(huì)請(qǐng)看鏈接：RANK函數(shù) )

選中各科成績區(qū)域即D3:L102→條件格式→突出顯示單元格規(guī)則→小于

輸入60→單擊旁邊的下拉三角→自定義格式為黃色填充，紅色文本

4、本題用if函數(shù)判斷即可，if函數(shù)(判斷的條件，滿足條件的值，不滿足條件的值)

思路為：如果學(xué)號(hào)=01，那么返回“法律一班”，如果學(xué)號(hào)=02，那么返回“法律二班”，如果學(xué)號(hào)=03，那么返回“法律三班”，剩下的都返回法律四班，即不滿足條件的值。

好，現(xiàn)在我們要做的就是把學(xué)號(hào)第3位和第4位提取出來，根據(jù)上面的思路判斷即可。

提取函數(shù)用mid(提取的數(shù)據(jù)，提取的位置，提取幾位)

(不會(huì)提取的看鏈接：Excel|提取函數(shù))

即：從學(xué)號(hào)中提取，第3位開始提取，提取兩位=mid(B3,3,2)

好，學(xué)號(hào)提取完成，可以按照思路進(jìn)行判斷了。

5、選擇表中的所有數(shù)據(jù)(除了標(biāo)題行)→插入→數(shù)據(jù)透視表→位置選擇新工作表→確定

重命名這個(gè)新工作表為“班級(jí)平均分”，并切右擊工作表標(biāo)簽設(shè)置為紅色

把班級(jí)拖動(dòng)到行，各科拖動(dòng)到值。

選擇數(shù)據(jù)透視表的標(biāo)題項(xiàng)→右擊→值字段設(shè)置→值匯總方式為平均值，把每一個(gè)科目都改為求平均值。

平均值改好后如圖：

選擇數(shù)據(jù)透視表→數(shù)據(jù)透視表工具選項(xiàng)卡→設(shè)計(jì)→中等深淺15

(提示：設(shè)置數(shù)據(jù)透視表樣式，千萬不要套用表格樣式)

選擇數(shù)據(jù)透視表整體→居中對(duì)齊→垂直和水平都要居中，否則會(huì)扣分

再選中數(shù)值區(qū)域→設(shè)置單元格格式→保留1位小數(shù)

6、選擇數(shù)據(jù)透視表區(qū)域(不要選擇總計(jì)一行)→插入→二維簇狀柱形圖

然后將圖表拉大，放置于A10:H30區(qū)域中即可

最后保存就可以啦，跟著練習(xí)哦

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。