編按：今天的文章是一篇概述性的內(nèi)容，針對一張成績表，講解一系列的函數(shù)操作。雖然都不難，但是我們可以從中學(xué)到許多思路，希望大家能從中“溫故知新”吧！

（模擬數(shù)據(jù)源，這班學(xué)生偏科的厲害）

數(shù)據(jù)源很常見。

需求也很“簡單且樸實(shí)”——要做全套的數(shù)據(jù)統(tǒng)計(jì)。

?

STEP 1 排名

在G2單元格輸入函數(shù)=RANK(F2,$F$2:$F$13)，下拉填充至G13單元格。

實(shí)際上RANK函數(shù)是有三個(gè)參數(shù)，第三個(gè)參數(shù)是“升序排序、降序排序”

如果我們不填寫，則默認(rèn)為0降序排序，需要的話，也可以在函數(shù)中輸入1進(jìn)行升序排序。

?

STEP 2 中國式排名

上面的排名明顯有一個(gè)問題，有兩個(gè)第一名，卻沒有第二名，所以我們要調(diào)整同分?jǐn)?shù)不占名次的問題。

在G2單元格輸入函數(shù):

{=SUM(IF($F$2:$F$13>F2,1/COUNTIF($F$2:$F$13,$F$2:$F$13)))+1}，下拉填充至G13單元格。

這是全篇最不好理解的一個(gè)函數(shù)，使用數(shù)組函數(shù)，判斷數(shù)據(jù)列表中有多少是大于F2單元格值的，如果大于則用1/COUNTIF的技巧，標(biāo)記序號，如果有重復(fù)的，則如下圖標(biāo)記為0.5，最后再用SUM將這些數(shù)值相加既得F2數(shù)值的排序。

STEP 3 不重復(fù)排序

中國式排名的內(nèi)涵，就是如果有同分情況，則比例第N名，下一個(gè)分?jǐn)?shù)則為N+1名次。但是這樣的話，還是會有重復(fù)排名，那我們再來一個(gè)“不重復(fù)排序”的函數(shù)吧。

在單元格H2輸入函數(shù)=RANK(F2,$F$2:$F$13)+COUNTIF($F$2:F2,F2)-1，下拉填充至H13單元格。

?

STEP 4 動態(tài)按總分排列成績單

STEP3的作用就是這樣了，可以讓我們動態(tài)的按照H列的排序引出排序明細(xì)。這里用了經(jīng)典的VLOOKUP函數(shù)的反向查詢。

在J2單元格輸入函數(shù)=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$B$2:$B$13),2,0)

在K2單元格輸入函數(shù)=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$F$2:$F$13),2,0)

?

STEP 5 按照J(rèn)、K列做一個(gè)圖表

選中J1:K13單元格區(qū)域，按ALT+F1快捷鍵，就可以快速的創(chuàng)建一個(gè)總分排名的圖表，再加上數(shù)據(jù)標(biāo)簽，齊活兒！

?

STEP 6 各科平均分

很直白的函數(shù)，在單元格C15輸入函數(shù)=AVERAGE(C2:C13)，右拉填充至F15單元格即可。

?

STEP 7 及格率和優(yōu)秀率

假定及格線是60及以上，優(yōu)秀率是85及以上。

在單元格C16輸入函數(shù){=SUM((C2:C13>=60)*1)/COUNT(C2:C13)}，右拉填充至E16單元格

在單元格C17輸入函數(shù){=SUM((C2:C13>=85)*1)/COUNT(C2:C13)}，右拉填充至E17單元格

這里我們利用邏輯值求和技巧，再除以總體樣本數(shù)。純粹的TRUE或者FALSE是不能被SUM求和的，但是可以進(jìn)行四則運(yùn)算，所以用了“*1”之后就可以轉(zhuǎn)化成1或者0，再用SUM就OK了。

?

STEP 8 前N名、后N名

在單元格C19輸入函數(shù)=LARGE(C$2:C$13,ROW(C1))，利用ROW函數(shù)得到第N大的序號，配合相對飲用固定行號，下拉再右拉填充至E21單元格，就得到了相對于各科的前三甲。

與LARGE相對應(yīng)的函數(shù)是SMALL函數(shù)，引用數(shù)據(jù)列表中的第N小的值，在C22單元格輸入函數(shù)=SMALL(C$2:C$13,ROW(C1))，原理同上。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

學(xué)習(xí)交流請加微信：hclhclsc進(jìn)微信學(xué)習(xí)群。

相關(guān)推薦：

如何計(jì)算兩個(gè)日期間的工作日天數(shù)？超實(shí)用的5類日期函數(shù)來了！

超級經(jīng)典的8個(gè)函數(shù)組合，解決70%工作中的函數(shù)難題

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者E圖表述；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。