Excel可以清晰的呈現(xiàn)出表格數(shù)據(jù)的內(nèi)容結(jié)構(gòu)，

讓你對(duì)學(xué)生的情況可以做到一目了然。

巧用技巧公式可以為我們省了不少的事和時(shí)間，

得到又快、又準(zhǔn)、又精確的結(jié)果。

1

求和

=SUM(C2：E2)橫向求和

=SUM(C2：C10) 縱向求和

這是對(duì)C2到E2這一區(qū)域進(jìn)行求和

2

平均數(shù)

=AVERAGE(C2：G2)

對(duì)C2到G2區(qū)域進(jìn)行求和

3

排名

=RANK(H2，H$2:H$11)

對(duì)10名學(xué)生的成績(jī)進(jìn)行排名

4

最高分

=MAX（H2：H11）

求H2到H11區(qū)域（10名同學(xué)）的最高分

5

最低分

=MIN（H2：H11）

求H2到H11區(qū)域（10名同學(xué)）的最低分

6

分?jǐn)?shù)段人數(shù)統(tǒng)計(jì)

1、 以“六年級(jí)學(xué)生語(yǔ)文成績(jī)表”為例，分?jǐn)?shù)段設(shè)置方法一，我們?cè)贒列統(tǒng)計(jì)人數(shù)。

2、 在“D3”單元格輸入公式

“=COUNTIF(B3:B32,">=0")-COUNTIF(B3:B32,">=60")”，

此公式中“B3:B32”對(duì)應(yīng)的是所有的成績(jī)。

輸入完成后在鍵盤(pán)上直接點(diǎn)擊“Enter”

即可完成對(duì)分?jǐn)?shù)段“0-59.9”人數(shù)的統(tǒng)計(jì)。

3、 同理，我們依次在D4、D5、D6、D7內(nèi)輸入

=COUNTIF(B3:B32,">=60")-COUNTIF(B3:B32,">=70")、=COUNTIF(B3:B32,">=70")-COUNTIF(B3:B32,">=80")、=COUNTIF(B3:B32,">=80")-COUNTIF(B3:B32,">=90")、=COUNTIF(B3:B32,">=90")，

每次輸入完成后直接在鍵盤(pán)上點(diǎn)擊“Enter”即可完成對(duì)所有分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)。

在前面兩期教程中為大家講解了LAGER函數(shù)的基礎(chǔ)用法。那么今天龔老師就來(lái)個(gè)升級(jí)版的排名函數(shù)應(yīng)用案例講解。

之前我們講解的是如何按排名提取成績(jī)。今天跟大家分享一下如何按照排名提取相對(duì)應(yīng)的學(xué)員姓名。

舉例：

如上表是，我們的學(xué)員成績(jī)信息表。我們現(xiàn)在需要快速?gòu)谋碇刑崛〕銮叭麑W(xué)員姓名。

函數(shù)公式

=LOOKUP(1,0/(LARGE($C$2:$C$13,ROW(D1))=C2:C13),B2:B13)

函數(shù)公式解釋?zhuān)?/p>

ROW(D1)：返回D1所在行的行號(hào)。

LARGE($C$2:$C$13,ROW(D1):返回ROW(D1)對(duì)應(yīng)的排名結(jié)果。

LOOKUP(1,0/(LARGE($C$2:$C$13,ROW(D1))=C2:C13),B2:B13)用lookup函數(shù)查找學(xué)員成績(jī)所對(duì)應(yīng)的學(xué)員姓名。

是不是又學(xué)到技巧了？記得分享哦！

下面介紹如下幾種函數(shù)及VBA的方法：

首先設(shè)一個(gè)前提：假設(shè)當(dāng)平均成績(jī)小于60時(shí)為不及格，大于等于60并且小于70時(shí)為及格，大于等于70并且小于90時(shí)為一般，大于等于90時(shí)為優(yōu)秀。

方法一：利用if函數(shù)的方法：在B2單元格輸入公式：=IF(A2<60,"不及格",IF(A2>=90,"優(yōu)秀",IF(AND(A2>=60,A2<70),"及格","一般")))，下拉填充即可。

方法二：利用lookup函數(shù)的方法：在B2單元格輸入公式：=LOOKUP(A2,{0,60,70,90;"不及格","及格","一般","優(yōu)秀"})，下拉填充即可。

方法三：利用函數(shù)index結(jié)合函數(shù)match的方法：在B2單元格輸入公式：=INDEX({"不及格";"及格";"一般";"優(yōu)秀"},MATCH(A2,{0;60;70;90}))，下拉填充即可。

方法四：利用函數(shù)text的方法：在B2單元格輸入公式：=TEXT(0&A2-60,"[<10]及格;[<30]一般;優(yōu)秀;不及格")，下拉填充即可。

簡(jiǎn)要介紹下TEXT函數(shù)的用法：TEXT函數(shù)的條件格式公式樣式為：text(數(shù)據(jù)源，"[條件1]顯示格式；[條件2]顯示格式；否則顯示格式；文本顯示格式")，本例中的0&的意思為：當(dāng)>=0時(shí)返回本身，負(fù)數(shù)返回文本。

方法五：利用函數(shù)choose結(jié)合函數(shù)sum的方法：在B2單元格輸入公式：=CHOOSE(SUM(1*(A2>={0,60,70,90})),"不及格","及格","一般","優(yōu)秀")，下拉填充即可。

方法六：利用VBA自定義函數(shù)的方法：按ALT+F11打開(kāi)VBE編輯器，在工程窗口下的Microsoft Excel對(duì)象右鍵-插入-模塊，新建一個(gè)模塊1。粘貼如下代碼：

Option ExplicitFunction grade(r As Range)If r < 60 Then grade = "不及格"ElseIf r >= 60 And r < 70 Then grade = "及格"ElseIf r >= 70 And r < 90 Then grade = "一般"Else grade = "優(yōu)秀"End IfEnd Function

接著在B2單元格輸入公式：=grade(A2)，下拉填充即可。

方法七：利用VBA自動(dòng)填充的方法：按ALT+F11打開(kāi)VBE編輯器，雙擊工程窗口下的sheet1（sheet1）項(xiàng)，在右邊的代碼窗口粘貼如下代碼：

Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 1 And Target.Row > 1 Then '限定僅當(dāng)A列第二個(gè)單元格即A2開(kāi)始的單元格內(nèi)容變化時(shí)觸發(fā)，此處請(qǐng)根據(jù)實(shí)際需要更改 If IsNumeric(Target.Value) Then  If Target.Value < 60 Then   Target.Offset(0, 1) = "不及格"  ElseIf Target.Value >= 60 And Target.Value < 70 Then   Target.Offset(0, 1) = "及格"  ElseIf Target.Value >= 70 And Target.Value < 90 Then   Target.Offset(0, 1) = "一般"  Else   Target.Offset(0, 1) = "優(yōu)秀"  End If  Else   MsgBox "輸入類(lèi)型不合法，請(qǐng)輸入數(shù)字！"   Target.ClearContents   Target.Offset(0, 1).ClearContents  End IfEnd IfEnd Sub

當(dāng)在A列A2開(kāi)始的單元格區(qū)域輸入內(nèi)容變化時(shí)，會(huì)自動(dòng)在相鄰單元格填入平均成績(jī)向?qū)?yīng)的成績(jī)等級(jí)；當(dāng)輸入的內(nèi)容為非數(shù)字時(shí)，會(huì)彈出提示框。

（本文內(nèi)容由百度知道網(wǎng)友zuhi貢獻(xiàn)）

　　方法一、打開(kāi)Excel表格，光標(biāo)定位到做為排序依據(jù)的成績(jī)列（zf），點(diǎn)擊常用工具欄中的“降序排序”按鈕。就可以讓整個(gè)表依“zf”列排序。（不能選中這一列再排序，這樣做只能這一列排序，而其它列不動(dòng)）

　　方法二、

　　1、點(diǎn)擊菜單欄“數(shù)據(jù)”－“排序”

　　2、在彈出來(lái)的“排序?qū)υ捒颉?，選擇排序的“主要關(guān)鍵字”－“zf”，排序方式“升序”，“有標(biāo)題行”，確定即可。

（本文內(nèi)容由百度知道網(wǎng)友sjz13jl貢獻(xiàn)）

方法較多，舉例如下，

 =COUNTIFS($B$1:$B$6,">=80",$B$1:$B$6,"<=90")=COUNTIF($B$1:$B$6,">=80")-COUNTIF($B$1:$B$6,">90")=SUM(IF(($B$1:$B$6>=80)*($B$1:$B$6<90),1,0))=FREQUENCY($B$1:$B$6,90.0001)-FREQUENCY($B$1:$B$6,79.9999)VBA代碼Sub tst()Dim cel As RangeDim i% For Each cel In Range("b1:b10")  If cel.Value >= 80 And cel.Value <= 90 Then i = i + 1 Next MsgBox iEnd Sub

（本文內(nèi)容由百度知道網(wǎng)友zhuying0511貢獻(xiàn)）

獲取方法：

二、給我發(fā)私信“成績(jī)分析”

三、我回復(fù)下載鏈接給您。

施老師：

Excel用處非常大，我們用EXCEL可以快速的對(duì)數(shù)據(jù)進(jìn)行排名，我們經(jīng)常在制作一些成績(jī)單或者銷(xiāo)售統(tǒng)計(jì)分析表的時(shí)候，都希望將其進(jìn)行排名，然后可以快速的看到各自的排名次序。今天我們就講解利用Excel中的排名函數(shù)對(duì)數(shù)據(jù)快速的進(jìn)行排名！

下面是我們的成績(jī)單，這里我希望通過(guò)計(jì)算所有人的總分來(lái)在排名中顯示出排名的次序！

使用函數(shù)：【=RANK(F2,$F$2:$F$11)】

直接定位到G2單元格，然后輸入上列函數(shù)下拉。

函數(shù)說(shuō)明

RANK函數(shù)：是求出數(shù)值在指定區(qū)域中排名函數(shù)。

F2,$F$2:$F$11：在F2單元格，區(qū)域是F2到F11這塊區(qū)域。

=RANK(F2,$F$2:$F$11)：合起來(lái)就是在求出F2單元格中的數(shù)據(jù)在F2到F11這塊區(qū)域的數(shù)據(jù)中的排名情況。

在統(tǒng)計(jì)考試或者競(jìng)賽的成績(jī)時(shí)，經(jīng)常會(huì)根據(jù)參加考試人員所取得的分?jǐn)?shù)把最終成績(jī)?cè)u(píng)價(jià)成優(yōu)或者良等幾個(gè)層次，這就需要統(tǒng)計(jì)成績(jī)的人員根據(jù)分?jǐn)?shù)給每個(gè)人的成績(jī)標(biāo)上優(yōu)、良等標(biāo)簽。如果參加考試的人員較多，那么完全通過(guò)人工來(lái)判斷成績(jī)的優(yōu)良并且再打出文字就是一個(gè)比較繁瑣的過(guò)程。強(qiáng)大的Excel可以幫助我們完成這項(xiàng)工作，只需要在Excel中列出函數(shù)公式，然后拖動(dòng)一下鼠標(biāo)或者選擇性粘貼公式，就可以完成所有考試人員的成績(jī)統(tǒng)計(jì)，還可以自動(dòng)打出優(yōu)或者良等文字。下面以Excel2007為例介紹一下如何操作，以供參考。

●我們可以通過(guò)Excel中的“IF”函數(shù)達(dá)到上述目的。例如要根據(jù)下圖表格中的成績(jī)，把分?jǐn)?shù)范圍在80-89的成績(jī)?cè)u(píng)價(jià)為“良”，把分?jǐn)?shù)范圍在90-100的成績(jī)?cè)u(píng)價(jià)為“優(yōu)”。第一步先在成績(jī)?cè)u(píng)價(jià)列最上方的單元格中點(diǎn)擊鼠標(biāo)，選中該單元格。

●選中單元格后，在Excel編輯欄中輸入“=if”，輸入后Excel2007之后的版本會(huì)根據(jù)輸入的字符彈出供選擇的函數(shù)列表，這時(shí)可以用鼠標(biāo)雙擊函數(shù)列表中的“IF”。

●雙擊“IF”后，Excel會(huì)在IF后自動(dòng)添加一個(gè)括號(hào)，還會(huì)在下方顯示函數(shù)的語(yǔ)法說(shuō)明，說(shuō)明都是英文，在這里直接解釋有點(diǎn)抽象。我會(huì)在下述的實(shí)例中對(duì)IF函數(shù)的語(yǔ)法進(jìn)行說(shuō)明。

●此時(shí)我們可以點(diǎn)擊成績(jī)?cè)u(píng)價(jià)單元格左側(cè)對(duì)應(yīng)的分?jǐn)?shù)單元格，本例中為“C3”單元格。

●點(diǎn)擊后，Excel會(huì)自動(dòng)把單元格的名稱輸入到編輯欄中，這樣可以不用通過(guò)鍵盤(pán)輸入單元格名稱，比較簡(jiǎn)便。

●接下來(lái)再根據(jù)要求把IF函數(shù)的參數(shù)補(bǔ)充完整，在地址欄中輸入完整的函數(shù)公式“=IF(C3>=90,"優(yōu)","良")”其中“C3>=90”是判斷條件，即判斷C3單元格中的值是否大于等于90，"優(yōu)"為符合判斷條件時(shí)顯示的字符，"良"為不符合判斷條件時(shí)顯示的字符。即如果C3單元格中的成績(jī)大于等于90，則成績(jī)?cè)u(píng)價(jià)單元格中就顯示字符"優(yōu)"，否則顯示字符“良”。其中>=還可以更換成其他計(jì)算運(yùn)算符，如< 小于、> 大于、= 等于或 <>不等于等。

●完成函數(shù)的輸入后，可以點(diǎn)擊編輯欄左側(cè)的對(duì)號(hào)或者按鍵盤(pán)的回車(chē)鍵。

●點(diǎn)擊對(duì)號(hào)或者按鍵盤(pán)的回車(chē)鍵后，成績(jī)?cè)u(píng)價(jià)單元格中就會(huì)自動(dòng)根據(jù)IF函數(shù)的內(nèi)容顯示出成績(jī)的評(píng)價(jià)結(jié)果。

●之后就可以通過(guò)拖動(dòng)單元格右下方的填充柄在其他單元格中自動(dòng)完成IF函數(shù)的輸入。

●如果表格較長(zhǎng)，用填充柄填充也比較麻煩，還可以采用選擇性粘貼公式的方法自動(dòng)填充函數(shù)。方法是：先復(fù)制已輸入函數(shù)的單元格。然后用按鍵盤(pán)Shift鍵等方式選擇要填充的單元格范圍后，在所選單元格范圍上點(diǎn)擊鼠標(biāo)右鍵，再點(diǎn)擊右鍵菜單中的“選擇性粘貼”。

●然后點(diǎn)選“選擇性粘貼”對(duì)話框中的“公式”選項(xiàng)，再點(diǎn)擊“確定”按鈕，即可完成所選范圍中全部單元格的填充。

●上面介紹的方法只能自動(dòng)列出優(yōu)和良兩個(gè)評(píng)價(jià)結(jié)果，如果想要列出更多的評(píng)價(jià)結(jié)果怎么辦呢？我們可以在IF函數(shù)中再嵌套一個(gè)IF函數(shù)來(lái)達(dá)到這個(gè)目的。還是以上表為例，除了優(yōu)和良外再增加一個(gè)“特”的評(píng)價(jià)標(biāo)準(zhǔn)。要把的分?jǐn)?shù)范圍在80-89的成績(jī)?cè)u(píng)價(jià)為“良”，把分?jǐn)?shù)范圍在90-94的成績(jī)?cè)u(píng)價(jià)為“優(yōu)”，把分?jǐn)?shù)范圍在95-100的成績(jī)?cè)u(píng)價(jià)為“特”。則可把函數(shù)公式修改為“IF(C3>=95,"特",(IF(C3>90,"優(yōu)","良")))”。意思是把不符合判斷條件“>=95”的數(shù)值，再用“>90”的條件判斷一次，從而可以自動(dòng)列出“優(yōu)”、“良”、“特”三個(gè)判斷結(jié)果。

●如下圖：修改公式后，用上述方法自動(dòng)填充函數(shù)到其他單元格，就會(huì)列出“優(yōu)”、“良”、“特”三個(gè)判斷結(jié)果。

把函數(shù)中的>=更換成< 小于、> 大于、= 等于或 <>不等于等計(jì)算運(yùn)算符還可以達(dá)到其他的目的，讀者可以參考本例在實(shí)際應(yīng)用中進(jìn)行更改。

一份學(xué)生成績(jī)單，需要分別統(tǒng)計(jì)出60分以下、60-69、70-79、80-89、90-100各階段成績(jī)的學(xué)生數(shù)量。下面分別介紹4種方法來(lái)統(tǒng)計(jì)符合要求的學(xué)生數(shù)量。

1、利用FREQUENCY函數(shù)

FREQUENCY函數(shù)為：計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率（個(gè)數(shù)）。函數(shù)語(yǔ)法為：

FREQUENCY(data_array, bins_array)：

Data_array 必需。 要對(duì)其頻率進(jìn)行計(jì)數(shù)的一組數(shù)值或?qū)@組數(shù)值的引用。 如果 data_array 中不包含任何數(shù)值，則 FREQUENCY 返回一個(gè)零數(shù)組。Bins_array 必需。 要將 data_array 中的值插入到的間隔數(shù)組或?qū)﹂g隔的引用。 如果 bins_array 中不包含任何數(shù)值，則 FREQUENCY 返回 data_array 中的元素個(gè)數(shù)。使用FREQUENCY函數(shù)難點(diǎn)為函數(shù)返回的是一個(gè)數(shù)組，所以它必須以數(shù)組公式的形式輸入。C2:C26 為學(xué)生成績(jī)，E2：E6為成績(jī)分?jǐn)?shù)區(qū)間分割點(diǎn)，對(duì)應(yīng)的分別在F2：F6統(tǒng)計(jì)數(shù)量。

1）選中F2：F6單元格區(qū)域（由于是數(shù)組函數(shù)，必須選中F2：F6單元格區(qū)域），選定后在F2輸入公式：

=FREQUENCY(C2:C26,E2:E6)。

2）公式輸入完后同時(shí)按Shift+Ctrl+Enter鍵，會(huì)在F2:F6中分別返回滿足條件的成績(jī)個(gè)數(shù)。

2、使用COUNTIFS函數(shù)

COUNTIFS函數(shù)為：統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量（個(gè)數(shù)）。函數(shù)語(yǔ)法為：

=COUNTIF(要檢查哪些區(qū)域? 要查找哪些內(nèi)容?)

這里將學(xué)生成績(jī)按成績(jī)范圍各自計(jì)數(shù)，E2:F6為成績(jī)范圍，在G2輸入公式：

=COUNTIFS($C$2:$C$26,">="&E2,$C$2:$C$26,"<="&F2)，然后向下拖動(dòng)復(fù)制到G6即可。注意C2:C26為絕對(duì)引用，按F4加"$"符號(hào)。

3、利用數(shù)據(jù)透視表

數(shù)據(jù)透視表處理大量數(shù)據(jù)時(shí)比較常用的方法，而且比較直觀。

4、利用分析工具庫(kù)

首先加載分析工具庫(kù)。打開(kāi)Excel選項(xiàng)，點(diǎn)擊加載項(xiàng)，在管理下拉列表選擇excel加載項(xiàng)，點(diǎn)擊轉(zhuǎn)到，勾選分析工具庫(kù)。在excel數(shù)據(jù)工具欄下會(huì)加載數(shù)據(jù)分析選項(xiàng)。

1）點(diǎn)擊工具欄數(shù)據(jù)—數(shù)據(jù)分析，在數(shù)據(jù)分析對(duì)話框中選擇直方圖，確定。

2）在直方圖對(duì)話框輸入?yún)^(qū)域和接收區(qū)域分別輸入數(shù)據(jù)區(qū)域，在輸出區(qū)域選擇一個(gè)空單元格，下面柏拉圖、累積百分率、直方圖3個(gè)選項(xiàng)任選一個(gè)，我這里選直方圖，確定。

一：計(jì)算總分

1、打開(kāi)需要編輯的excel示例文檔。已知20個(gè)同學(xué)的7門(mén)功課的成績(jī)，現(xiàn)在需要在I列中計(jì)算出每個(gè)同學(xué)的總分。

2、在I1單元格中輸入函數(shù)=sum（B1：H1）按下enter鍵即在I1單元格中計(jì)算出第一位同學(xué)的總分將光標(biāo)移動(dòng)到I1單元格右下角鼠標(biāo)呈十字架狀下來(lái)列表，即完成所有同學(xué)總分的計(jì)算。

二：計(jì)算平均分

這里，向大家介紹兩種常用的也是比較簡(jiǎn)單的計(jì)算平均分的法子~

1.將光標(biāo)移動(dòng)到J2單元格，輸入函數(shù)=I2/count(B2:H2)按Enter鍵然后將鼠標(biāo)下拉方法同步驟2得到的結(jié)果。

2.將光標(biāo)移動(dòng)到J2單元格。輸入函數(shù)=average（B2：H2）按下Enter鍵然后將鼠標(biāo)下拉方法步驟同上得到的結(jié)果，這里顯示的結(jié)果和方法1是一樣的。

三：去除一個(gè)最高值和一個(gè)最低值再計(jì)算平均分

將光標(biāo)移動(dòng)到K2單元格中，輸入函數(shù)=TRIMMEAN(B2:H2,2/COUNT(B2:H2))。按下Enter鍵然后將鼠標(biāo)下拉方法步驟同上得到的結(jié)果

四：計(jì)算及格人數(shù)

將光標(biāo)移動(dòng)到B22單元格，如下圖示。輸入函數(shù)=COUNTIF(B2:B21,">=60")。按下Enter鍵，得到第一門(mén)功課及格的人數(shù)為10.同理，將鼠標(biāo)移動(dòng)到B22單元格右下角呈十字形狀，向右邊拖動(dòng)。得到的最后結(jié)果

五：計(jì)算及格率

將光標(biāo)移動(dòng)到B23單元格，輸入函數(shù)=B22/COUNT(B2:B21)。（這個(gè)函數(shù)的原理：及格率=及格人數(shù)/參考總?cè)藬?shù)）

按下Enter鍵，得到第一門(mén)功課的及格率。

選中B23：H23,單擊鼠標(biāo)右鍵，選擇設(shè)置單元格格式為百分?jǐn)?shù)，小數(shù)點(diǎn)后保持2位數(shù)。點(diǎn)擊確定。

（本文內(nèi)容由百度知道網(wǎng)友a(bǔ)pplejackli貢獻(xiàn)）