在平時(shí)的學(xué)?？荚囍?，“用Excel制作成績單”是一項(xiàng)非常常見的應(yīng)用。下面就對成績單制作過程中所涉及到的幾個(gè)常見的Excel知識點(diǎn)逐一分析。

快速預(yù)覽效果

目錄

1. 求各科總分

2. 求年級名次

3. 求班級名次

4. 求各班各科平均分

5. 查詢各班前3名

6. 按班級拆分工作表

一、求各科總分

• 知識點(diǎn)：SUM函數(shù)

• 難度：1

在I2單元格輸入公式=SUM(E2:H2)，按Enter確定，然后雙擊向下填充

二、求年級名次

• 知識點(diǎn)：RANK函數(shù)

• 難度：1

在J2單元格輸入公式=RANK(I2,$I$2:$I$275)，按Enter確定，然后雙擊向下填充（數(shù)據(jù)一共有275行）。注意RANK函數(shù)的第二個(gè)參數(shù)必須采用絕對引用的方式，即$I$2:$I$275

三、求班級名次

• 知識點(diǎn)：SUMPRODUCT函數(shù)

• 難度：5

在求班級名次之前我們先來看看SUMPRODUCT函數(shù)的基本用法。

比如下面一張銷售統(tǒng)計(jì)表，如何計(jì)算銷售總額？可能我們想到的方法是先計(jì)算每一個(gè)品類的銷售額，然后利用SUM函數(shù)計(jì)算銷售總額。

但其實(shí)我們有更簡單的方法，那就是SUMPRODUCT函數(shù)，它能夠用一個(gè)公式實(shí)現(xiàn)上述同樣的計(jì)算過程，并得到同樣的結(jié)果。

通過上述兩個(gè)函數(shù)的對比，相信大家應(yīng)該能夠明白SUMPRODUCT函數(shù)的工作原理了，那下面就回到正題上，看看在成績單中如何利用它計(jì)算班級名次。

我們在K2單元格輸入公式=SUMPRODUCT((C2=$C$2:$C$275)*(I2<$I$2:$I$275))+1，然后雙擊向下填充即可。

公式中的(C2=$C$2:$C$275)是用于判斷C2單元格是否等于C2至C75范圍的值，其目的是為了篩選出C列所有和C2單元格一致的數(shù)據(jù)。這個(gè)等式會返回一個(gè)數(shù)組，大家可以選中這部分公式之后按F9鍵顯示結(jié)果。

操作步驟如下圖所示：

這個(gè)數(shù)組開頭為{TRUE;FALSE;FALSE;TRUE;TRUE....}

同理，公式中的(I2<$I$2:$I$275)也會返回一個(gè)數(shù)組，其目的在于判斷I2的總分是否小于所有的總分，按F9鍵顯示為{FALSE;FALSE;TRUE;TRUE;FALSE...}

然后TRUE和FALSE在參與計(jì)算的時(shí)候就會轉(zhuǎn)化為1和0，也即{TRUE;FALSE;FALSE;TRUE;TRUE....}x{FALSE;FALSE;TRUE;TRUE;FALSE...}={1,0,0,1,1...}x{0,0,1,1,0...}={0,0,0,1,0...}

而SUMPRODUCT({0,0,0,1,0...})=1

最后在SUMPRODUCT后面加的一個(gè)1，是為了修正結(jié)果，得到第一個(gè)學(xué)生正確的班級名次2.

需要注意的是，這個(gè)SUMPRODUCT函數(shù)的計(jì)算過程類似數(shù)組公式，計(jì)算量很大，單單是計(jì)算第一個(gè)學(xué)生的班級名次，就出現(xiàn)了275x275這么大的計(jì)算量，如果算完所有學(xué)生的班級名次，Excel在后臺默默地做大量的運(yùn)算工作。電腦配置比較差的朋友，可能會出現(xiàn)電腦輕微的卡頓。

四、求各班各科平均分

• 知識點(diǎn)：AVERAGEIF函數(shù)

• 難度：3

說到求平均值，有點(diǎn)Excel基礎(chǔ)的朋友都知道有一個(gè)AVERAGE函數(shù)可用，其實(shí)AVERAGEIF函數(shù)其實(shí)就比AVERAGE函數(shù)僅僅多了一個(gè)判斷而已，所以并沒有太大的難度。

求各班各科平均分的難度，主要在于單元格的“混合引用”上。

我們只需在N2單元格輸入公式=AVERAGEIF($C$2:$C$275,$M3,E$2:E$275)，然后向右、再向下拖動填充即可。

需要注意的是公式中的第2和第3個(gè)參數(shù)的引用方式，之所以要用$M3這樣的引用方式，是為了要鎖定M列，以保證在向右拖動填充的過程中，第2個(gè)參數(shù)始終都是對應(yīng)的班級這一列。同理，E$2:E$275這樣的引用方式鎖定行，是為了保證在向下拖動的過程中，始終計(jì)算的是第2行至第275行的分?jǐn)?shù)。

完整的操作過程如下所示（切換引用方式可以用F4鍵）：

五、查詢各班前3名

• 知識點(diǎn)：INDEX+MATCH函數(shù)組合、數(shù)組公式

• 難度：5

說到數(shù)據(jù)查詢，可能大家都能想到VLOOKUP函數(shù)，和INDEX+MATCH函數(shù)組合，一般來說INDEX+MATCH函數(shù)組合更好用些，因?yàn)樗挥每紤]數(shù)據(jù)列的前后順序，非常的靈活。

這里的“查詢各班前3名”，就要用到上面提到的函數(shù)組合。

在進(jìn)行正式的查詢之前，我們先來看看一個(gè)小例子，用于解釋INDEX+MATCH的工作原理。還是以這個(gè)成績表為例，我們要查找“高昌健”同學(xué)的總分，就可以用這個(gè)公式來完成：=INDEX(I2:I275,MATCH(M19,B2:B275,0))

這個(gè)公式很好理解，我們應(yīng)該由內(nèi)而外來看，首先用MATCH函數(shù)在B2:B275精確查找M19對應(yīng)的內(nèi)容，返回對應(yīng)的索引位置（8），然后INDEX函數(shù)在I2:I275范圍的第8個(gè)位置查找數(shù)據(jù)，即總分407.5

下面回歸正題。我們在查詢“各班總分前3名”之前，還有一個(gè)難點(diǎn)沒有解決，就是如何進(jìn)行多條件查詢，即要用“班級”和“名次”這兩個(gè)字段來查找對應(yīng)的“姓名”。

這里簡單的做法是建立一個(gè)輔助列，將“班級”和“名次”這兩個(gè)字段連接起來，作為一個(gè)字段，這樣就變成了單條件查詢。

然后在O12單元格輸入如下公式，并向右、向下拖動填充。

=INDEX($C$2:$C$275,MATCH($N12&O$11,$A$2:$A$275,0))

這里同樣要注意MATCH函數(shù)中的混合引用方式，其原理與“求各班各科平均分”類似，可以對比著看看。

這樣就完成了各班總分前3名的查詢，然而，輔助列的出現(xiàn)還是不太美觀，有沒有一種方法可以不用輔助列呢？當(dāng)然有，那就是借用數(shù)組公式，將“班級”和“名次”這兩個(gè)字段連接成一個(gè)新數(shù)組，作為MATCH函數(shù)的第2個(gè)參數(shù)。具體公式如下（注意表結(jié)構(gòu)的前后變化）：

=INDEX($B$2:$B$275,MATCH($M12&N$11,$C$2:$C$275&$K$2:$K$275,0))

可能大家也都注意到了上圖中的公式最外面有一對大括號{}，這是數(shù)組公式的特點(diǎn)，在N12單元格輸入上述公式之后需要按CTRL+SHIFT+ENTER三鍵確定輸入，而不是ENTER一個(gè)鍵。

公式中的$C$2:$C$275&$K$2:$K$275和之前的輔助列返回的結(jié)果，除了第一行標(biāo)題之外，完全一樣。大家可以選中這部分公式，按F9鍵查看結(jié)果，如下圖所示。

六、按班級拆分工作表

• 知識點(diǎn)：插件的使用

• 難度：1

拆分工作表的方法很多，常用的方法是插件和VBA，前者一鍵完成，最為便捷，后者需要有一定的VBA基礎(chǔ)，但也相對不難。考慮到辦公的效率，這里僅介紹借助插件進(jìn)行工作表的拆分。

有很多插件都可以完成工作表的拆分任務(wù)，這里以“易用寶”為例，這個(gè)插件可以在ExcelHome官網(wǎng)免費(fèi)下載。

安裝完插件之后，在【易用寶】菜單找到【工作表管理】命令按鈕，選擇【拆分工作表】，拆分區(qū)域選擇A1:K275，主拆分字段選擇第3列（班級），將所有的【可選拆分項(xiàng)】都添加到右側(cè)的【待拆分項(xiàng)】，直接點(diǎn)擊【分拆】按鈕即可，其它選項(xiàng)保持默認(rèn)。

下面拆分之后的結(jié)果。

七、總結(jié)

在這個(gè)成績單的制作過程中，涉及到了簡單的函數(shù)應(yīng)用，比如SUM、RANK，以及稍微復(fù)雜的函數(shù)應(yīng)用，比如AVERAGEIF、INDEX+MATCH，在函數(shù)應(yīng)用過程中需要重點(diǎn)把握的是單元格或區(qū)域的引用方式，其中以混合引用最不好理解，大家在以后的運(yùn)用中要多加體會。

案例中還涉及到“數(shù)組公式”這樣的稍微高階的Excel知識，如果覺得理解有難度，大家可以先查閱相關(guān)資料，理解數(shù)組公式的特點(diǎn)和使用方式，然后再來研究這個(gè)案例。

最后，還介紹了Excel插件的使用，這里僅僅只是演示了Excel插件的一個(gè)小小的功能，而很多插件的功能都十分強(qiáng)大，可以極大地提高辦公效率。除了“易用寶”插件之外，這里再給大家推薦幾個(gè)插件：KUTOOLS、方方格子工具箱、Excel必備工具箱、慧辦公。