利用rank函數(shù)降序排名！讓我們通過下面的例子學(xué)習(xí)吧！期末考試結(jié)束了，初三(14)班的班主任助理王老師需要對(duì)本班學(xué)生的各科考試成績(jī)進(jìn)行統(tǒng)計(jì)分析，按照下列要求完成該班的成績(jī)統(tǒng)計(jì)工作。
1. 在考生文件夾下，將“Excel素材.xlsx”文件另存為“Excel.xlsx”(“.xlsx”為擴(kuò)展名)，后續(xù)操作均基于此文件，否則不得分。
2. 在工作簿“Excel.xlsx”最左側(cè)插入一個(gè)空白工作表，重命名為“初三學(xué)生檔案”，并將該工作表標(biāo)簽顏色設(shè)為“紫色(標(biāo)準(zhǔn)色)”。
3. 將以制表符分隔的文本文件“學(xué)生檔案.txt”自A1單元格開始導(dǎo)入到工作表“初三學(xué)生檔案”中，注意不得改變?cè)紨?shù)據(jù)的排列順序。將第1列數(shù)據(jù)從左到右依次分成“學(xué)號(hào)”和“姓名”兩列顯示。最后創(chuàng)建一個(gè)名為“檔案”、包含數(shù)據(jù)區(qū)域A1:G56、包含標(biāo)題的表，同時(shí)刪除外部鏈接。
4. 在工作表“初三學(xué)生檔案”中，利用公式及函數(shù)依次輸入每個(gè)學(xué)生的性別“男”或“女”、出生日期“××××年××月××日”和年齡。其中：身份證號(hào)的倒數(shù)第2位用于判斷性別，奇數(shù)為男性，偶數(shù)為女性;身份證號(hào)的第7~14位代表出生年月日;年齡需要按周歲計(jì)算，滿1年才計(jì)1歲。最后適當(dāng)調(diào)整工作表的行高和列寬、對(duì)齊方式等，以方便閱讀。
5. 參考工作表“初三學(xué)生檔案”，在工作表“語(yǔ)文”中輸入與學(xué)號(hào)對(duì)應(yīng)的“姓名”;按照平時(shí)、期中、期末成績(jī)各占30%、30%、40%的比例計(jì)算每個(gè)學(xué)生的“學(xué)期成績(jī)”并填入相應(yīng)單元格中;按成績(jī)由高到低的順序統(tǒng)計(jì)每個(gè)學(xué)生的“學(xué)期成績(jī)”排名并按“第n名”的形式填入“班級(jí)名次”列中;按照下列條件填寫“期末總評(píng)”：
6. 將工作表“語(yǔ)文”的格式全部應(yīng)用到其他科目工作表中，包括行高(各行行高均為22默認(rèn)單位)和列寬(各列列寬均為14默認(rèn)單位)。并按上述4.中的要求依次輸入或統(tǒng)計(jì)其他科目的“姓名”、“學(xué)期成績(jī)”、“班級(jí)名次”和“期末總評(píng)”。
7. 分別將各科的“學(xué)期成績(jī)”引入到工作表“期末總成績(jī)”的相應(yīng)列中，在工作表“期末總成績(jī)”中依次引入姓名、計(jì)算各科的平均分、每個(gè)學(xué)生的總分，并按成績(jī)由高到低的順序統(tǒng)計(jì)每個(gè)學(xué)生的總分排名、并以1、2、3……形式標(biāo)識(shí)名次，最后將所有成績(jī)的數(shù)字格式設(shè)為數(shù)值、保留兩位小數(shù)。
8. 在工作表“期末總成績(jī)”中分別用紅色(標(biāo)準(zhǔn)色)和加粗格式標(biāo)出各科第一名成績(jī)。同時(shí)將前10名的總分成績(jī)用淺藍(lán)色填充。
9. 調(diào)整工作表“期末總成績(jī)”的頁(yè)面布局以便打印：紙張方向?yàn)闄M向，縮減打印輸出使得所有列只占一個(gè)頁(yè)面寬(但不得縮小列寬)，水平居中打印在紙上。
對(duì) 應(yīng) 步 驟
1、打開考生文件夾，復(fù)制Excel素材，粘貼，重命名為Excel。打開這份Excel做題即可。
點(diǎn)擊加號(hào)創(chuàng)建新工作表，右擊重命名為初三學(xué)生檔案。標(biāo)簽顏色改為紫色。并按住鼠標(biāo)左鍵拖動(dòng)到最左邊。
3、數(shù)據(jù)選項(xiàng)卡→自文本→選擇學(xué)生檔案→導(dǎo)入
語(yǔ)言選擇簡(jiǎn)體中文936→下一步
分割符號(hào)選擇Tab鍵→下一步
選中身份證號(hào)碼這一列→改成文本格式→下一步
選擇位置為A1→確定
接下來(lái)對(duì)A列進(jìn)行分列，在身份證號(hào)碼前插入一個(gè)新列
選擇A列一整列→數(shù)據(jù)選項(xiàng)卡→分列
分隔符選擇固定寬度→下一步
光標(biāo)定位學(xué)號(hào)和姓名中間，畫一條線→下一步直到完成
把姓名剪切到B列
選中A1:G56區(qū)域→套用表格樣式→隨便選擇一個(gè)樣式即可
勾選表包含標(biāo)題→確定
點(diǎn)確定后會(huì)彈出此對(duì)話框，選擇是
然后選中表區(qū)域→在名稱框輸入檔案→回車確定
4、下面填寫性別，出生日期，和年齡這三列。
首先身份證號(hào)的倒數(shù)第2位用于判斷性別，奇數(shù)為男性，偶數(shù)為女性。先在身份證號(hào)碼中提取出來(lái)倒數(shù)第二位，用mid函數(shù)提取即可。
mid(提取的數(shù)據(jù)，提取的位置，提取幾位)
再用mod函數(shù)判斷奇偶。mod取余函數(shù)(被除數(shù)，除數(shù))
如果倒數(shù)第二位/2=0，那么就是偶數(shù)，否則就是奇數(shù)
現(xiàn)在用if函數(shù)判斷，如果為1，那么返回男，否則返回女。
現(xiàn)在求出生日期，用mid函數(shù)分別提取第7位-第14位，即年月日的對(duì)應(yīng)數(shù)字，即
年：MID([@身份證號(hào)碼],7,4)
月：MID([@身份證號(hào)碼],11,2)
日：MID([@身份證號(hào)碼],13,2)
然后用date函數(shù)合起來(lái)即可，date函數(shù)參數(shù)(年，月，日)
然后選擇日期的區(qū)域即E2:E56，改成yyyy”年”mm”月”dd”日”的格式，顯示##時(shí)是因?yàn)榱袑挷粔颍瓕掽c(diǎn)即可
現(xiàn)在求出生年齡，求周歲需要用到datedif函數(shù)，即求兩個(gè)日期直接的整年數(shù)。
datedif(開始日期，結(jié)束日期，整年數(shù)用y表示)
開始日期為出生的第一天，結(jié)束日期為今天，今天是指可以隨著時(shí)間變化而變化的今天，即今天顯示為2019/5/15，明天就顯示為2019/5/16。需要用到today函數(shù)，即可以隨著時(shí)間變化而變化的日期。today沒有參數(shù)。即每一天的日期=today()
最后適當(dāng)增加行高和列寬，居中對(duì)齊。
5、輸入姓名需要根據(jù)學(xué)號(hào)，查找對(duì)應(yīng)的姓名。即用vlookup函數(shù)查找
vlookup(要查找的數(shù)據(jù)，查找的范圍，返回的值在范圍的第幾行，0)
根據(jù)題目要求，得出學(xué)期成績(jī)=平時(shí)*30%+期中*30%+期末*40%，即
求排名用rank函數(shù)，rank(排名的數(shù)據(jù)，排名的范圍)
用&鏈接符，鏈接第和名這兩個(gè)字。
期末總評(píng)用if函數(shù)判斷即可。如果成績(jī)>=102,顯示為優(yōu)秀，如果成績(jī)>=84,顯示為良好，如果成績(jī)>=72，顯示為及格，否則顯示為不合格。即：
6、這道題需要做6個(gè)表，我們先點(diǎn)擊格式刷，然后按住ctrl鍵選擇依次6個(gè)表，此時(shí)6個(gè)表會(huì)變成工作組的狀態(tài)，修改一個(gè)，6個(gè)會(huì)同時(shí)變化。
這時(shí)候格式刷刷其中一個(gè)表即可。6個(gè)會(huì)同時(shí)變化。
在選中6個(gè)表的狀態(tài)下，改行高為22，列寬為14
著，還是在選中的狀態(tài)，姓名列輸入一個(gè)等于號(hào)=，然后直接選中語(yǔ)文表中的姓名單元格，直接回車確定
同理選中6個(gè)表，用公式填寫學(xué)期成績(jī)和班級(jí)名次。
最后的期末總評(píng)的題目要求的是，大于90優(yōu)秀，大于75良好，大于60及格，其余不合格。
7、最后的期末成績(jī)表，還是輸入一個(gè)等于號(hào)，然后點(diǎn)擊各表對(duì)應(yīng)的單元格，回車確定就可以引用過來(lái)了
同理其他各科也都是引用對(duì)應(yīng)表的F2單元格。
總分用sum函數(shù)求和即可。
然后選中，單擊單元格右下角自動(dòng)填充
最后求排名，rank函數(shù)(排名的數(shù)據(jù)，排名的范圍)，范圍要進(jìn)行鎖定不能發(fā)生變化。
利用average求平均值，然后向右填充
選中C3：J46單元格→右擊設(shè)置單元格格式→數(shù)值→保留兩位小數(shù)
8、選中語(yǔ)文這一列數(shù)據(jù)→條件格式→項(xiàng)目選取規(guī)則→前10項(xiàng)
把10改為1→ 自定義格式
字體選中紅色，字形選擇加粗
然后格式刷，刷每一科對(duì)應(yīng)的數(shù)據(jù)，一列一列的刷，不能一起刷
選中總分這一列的數(shù)據(jù)，不要選擇最后一行，項(xiàng)目選取規(guī)則，自定義格式為淺藍(lán)色填充
9、頁(yè)面布局→頁(yè)面設(shè)置對(duì)話框啟動(dòng)器→紙張方向→橫向→一頁(yè)高一頁(yè)寬
點(diǎn)擊頁(yè)邊距→水平居中
