1、EXCEL 電子表格中四個常用函數的用法現在介紹四個常用函數的用法:COUNT(用于計算單元格區域中數字值的個數 、 COUNTA(用于計算單元格區域中非空白單元格的個數 、 COUNTBLANK(用于計算單元格區 域中空白單元格的個數 、 COUNTIF(用于計算符合一定條件的 COUNTBLANK 單元格個數 。 結合例子將具體介紹:如何利用函數 COUNTA 統計本班應考人數 (總人數 、利用函數 COUNT 統計實際參加考試人數、利用函數 COUNTBLANK 統計各科缺考人數、利用函數 COUNTIF 統計各科各分數段的人數。首先,在上期最后形成的表格的最后添加一些字段名 和合并一些

2、單元格,見圖 1。一、 利用函數 COUNTA 統計本班的應考人數 (總人數 因為函數 COUNTA 可以計算出非空單元格的個數,所以我們在利用此函數時,選取本 班學生名字所在單元格區域 (B3B12 作為統計對象, 就可計算出本班的應考人數 (總人數 。1. 選取存放本班總人數的單元格, 此單元格是一個經過合并后的大單元格 (C18G18 ;2.選取函數;單擊菜單 “ 插入 /函數 ” 或工具欄中的函數按鈕 f *,打開 “ 粘貼函數 ” 對話 框,在 “ 函數分類 ” 列表中選擇函數類別 “ 統計 ” ,然后在 “ 函數名 ” 列表中選擇需要的函數 “COUNTA” ,按 “ 確定 ” 按

3、鈕退出 “ 粘貼函數 ” 對話框。3.選取需要統計的單元格區域;在打開的 “ 函數向導 ” 對話框中,選取需要計算的單元 格區域 B3B13, 按下回車鍵以確認選取; “ 函數向導 ” 對話框圖再次出現在屏幕上, 按下 “ 確 定 ” 按鈕,就可以看到計算出來本班的應考人數 (總人數 了。二、利用 COUNT 、 COUNTBLANK 和 COUNTIF 函數分別統計各科參加考試的人數、 統計各科缺考人數、統計各科各分數段的人數我們在輸入成績時,一般情況下,缺考的人相應的科目的單元格為空就可以了,是 0分的都輸入 0。(一 統計語文科的參加考試人數、缺考人數、各分數段的人數。1.用函數 COU

4、NT 統計語文科的參加考試人數。單擊存放參加語文科考試人數的單元 格 C19,然后按照前面的操作步驟,首先在 “ 函數分類 ” 列表中選擇函數類別 “ 統計 ” ,在 “ 函 數名 ” 列表中選擇需要的函數 “COUNT” ;其次按照上面 “ 一、 3” 選取單元格區域的操作方法, 選取需要統計的單元格區域 (C3C12 , 然后回車確認, 單擊 “ 函數向導 ” 對話框 “ 確定 ” 按鈕, 就可以看到計算出來的結果。 2.用函數 COUNTBLANK 統計語文科的缺考人數。單擊存放語文科缺考人數的單元格 C20,然后按照上面的操作方法,在 “ 統計 ” 類別中選取函數 COUNTBLANK

5、 ,并進行需要統 計單元格區域 (C3C12 的選取,直到得出結果。3.用函數 COUNTIF 分別統計出語文科各分數段的人數。(1 統計 90分 (包括 90分 以上的人數 (表中為 “90分以上 ” :單擊存放此統計人數的單 元格 C21,然后選取函數 , 即選取 “ 統計 ” 類別中的函數 “COUNTIF” ,然后單擊 “ 函數向導 ” 對 話框中的 “Ragane” 右側的按鈕,以選取統計單元格的區域 (C3C12 后,回到 “ 函數向導 ” 對 話框中, 再輸入統計的條件:“$#62;=90”, 如圖 2。 單擊 “ 確定 ” 按鈕, 就可以計算出結果了。 (2 統計大于或等于 8

6、0分而小于 90分的人數 (表中為 “8089分 ” :雙擊單元格 C21進 入 編 輯 狀 態 , 可 以 看 到 統 計 90分 以 上 的 分 數 段 的 人 數 的 公 式 如 圖 3所 示 是 : =COUNTIF(C3:C12,$#62;=90 ,要統計本分數段人數,我們只要雙擊 C22,在其中輸入計算公式:=COUNTIF(C3:C12,$#62;=80 -COUNTIF(C3:C12,$#62;=90回車后,即可計算出此分數段的人數。(3用同樣方法, 只要在 C23、 C24、 C25三個單元格中, 分別輸入公式 (可以通過復制粘 貼后 , 修改數字快速完成 :=COUNTIF

7、(C3:C12,$#62;=70 -COUNTIF(C3:C12,$#62;=80=COUNTIF(C3:C12,$#62;=60 -COUNTIF(C3:C12,$#62;=70=COUNTIF(C3:C12,$#60;60輸入完畢后,注意一定要以回車確定,即可分別統計出 “ 大于或等于 70分而小于 80分 ”(表中為 “7079分 ” 、 “ 大于或等于 60分而小于 70分 ”(表中為 “6069分 ” 、 “ 小于 60分 ”(表中為 “ 不及格 ” ,這三個分數段的各自的人數。(二 統計其余各科的參加考試人數、缺考人數、各分數段的人數。如前一期所述, 用復制公式的方法, 可以快速計

8、算出其余各科的有關數據。 以上已經計 算出語文科的應考人數、 缺考人數及各分數段的人數, 選取范圍 (C19C25 , 把鼠標指向剛 才選取的單元格區域的右下方 (即填充句柄 ,待光標變為小黑十字時,按下鼠標左鍵,并向 右拖動,至 G25松開鼠標,各科要統計的結果都出來了。前兩期對班級成績,分別作了總分、平均分、最高分、最低分、應考人數、缺考人數、分數 段等數據統計 , 這些數據可以用來衡量這個班的成績的情況。這一期,將首先介紹用函數 “RANK” 以最快的速度把本班的名次排出來,作為衡量學生個人在本班的學習情況;另外再 介紹用 “MEDIAN” 、 “MODE” 、 “STDEVP” 函數分

9、別統計出各科成績的 “ 中位數 ” 、 “ 眾數 ” 、 “ 標 準差 ” ,以此衡量各科試題的質量 (如試題的難易程度、離散程度等 情況。一、用函數 “RANK” 對總分排名次(一 單元格區域的命名先打開上期制作的表格(如圖 1 , 如果用 “ 總分 ” 來排名次,首先選取所有 “ 總分 ” 數據 的單元格區域 (H3H12 , 然后單擊菜單中的 “ 插入 /名稱 /定義 ” ,在彈出的 “ 定義名稱 ” 對話 框中,在 “ 當前工作簿的名稱 ” 中輸入或修改名稱為 “ 總分 ” ;在 “ 引用位置 ” 欄中顯示的就是剛 才選取的單元格區域 (H3H12 ,當然也可以通過單擊其右側的按鈕重新

10、選取單元格區域。 如果只定義一個名稱,則可按 “ 確定 ” 按鈕退出;如果還要添加其它區域名稱,可單擊 “ 添加 ” 按鈕,待命名完畢后,再按 “ 確定 ” 按鈕結束單元格區域的命名。 在此我們可以比較一下前 兩期用 “ 選取 ” 和現在用 “ 命名 ” 區域的不同方法及用途:利用 “ 選取 ” 確定區域,預選區域不是 固定的,如果需要相對固定的區域,可以利用 “ 命名 ” ,則以后的操作會比較簡便,如果對某 個區域一旦命名, 利用函數的時候, 就可以按以下的方法確定單元格的區域, 無須再去選取 區域了。(二 選取函數確定排名1.在圖 1的 “ 平均分 ” 右邊的單元格(J2中輸入 “ 名次

11、” 。2.單擊選取單元格 J3,再選擇 “ 統計 ” 類的 “RANK” 函數,則在彈出的 “ 粘貼函數 ” 對話 框中,一切設置如圖 2(圖中的 “H3” 是存放第一個學生總分的單元格, “ 總分 ” 則是剛才命名 的單元格區域名稱。此時不能在 “ 粘貼函數 ” 對話框中,單擊圖 2中 “Ref” 右邊的按鈕去選取 單元格區域, 否則后面利用復制方法統計其余各人的名次時, 單元格的區域會發生變化; 利 用命名的單元格區域, 復制時其區域不會發生變化; 如果只看其中一個人的名次, 則可以利 用 “ 選取 ” 的方法 ,單擊 “ 確定 ” 按鈕,即可得出第一個學生的成績排名。然后選取單元格 J3

12、, 拖動其填充句柄至最后一名學生,馬上得出全班的成績排名。而且名次是可以動態變化的, 如果某人的某科成績發生變化, 所有排名也會隨數據的變化而變化。 如果想把名次按從低到 高的順序進行排列, 只要先選取范圍 (J3J12, 然后利用菜單中的 “ 數據 /排序 ” 命令, 對 “ 名次 ” 進行 “ 遞增 ” 排序即可。二、用 “MEDIAN” 、 “MODE” 、 “STDEVP” 函數分別計算各科成績的 “ 中位數 ” 、 “ 眾數 ” 、 “ 標 準差 ”“ 中位數 ” 、 “ 眾數 ” 、 “ 標準差 ” 是三個 “ 統計類 ” 的函數,也是統計學中三個十分常用的概 念,它是分析數據的分

13、布、離散程度等標志的重要依據,下面通過對學生成績的分布情況, 分析每科試題的有關情況。1.計算 “ 語文 ” 的 “ 中位數 ”先選取存放數據的單元格 C26,然后在 “ 統計 ” 類函數中選取函數 “MEDIAN” ,在彈出的 “ 粘貼函數 ” 對話框中,單擊 “Nuber1” 右邊的按鈕,選取需計算的單元格區域 (C3C12 ,然 后單擊 “ 粘貼函數 ” 對話框中的 “ 確定 ” 按鈕,就可計算出語文科的中位數 (在選取的數據中, 中位數是它們的平均數 。2.計算 “ 語文 ” 的 “ 眾數 ” 、 “ 標準差 ”利用 “MODE” 、 “STDEVP” 兩個函數, 按照上述的方法, 即

14、可計算出 “ 語文 ” 的 “ 眾數 ” 、 “ 標 準差 ” 。3.計算其余各科的 “ 中位數 ” 、 “ 眾數 ” 、 “ 標準差 ”按照前兩期介紹的復制方法,相信各位可以熟練地操作,計算出其余各科的 “ 中位數 ” 、 “ 眾數 ” 、 “ 標準差 ” 了,最后結果如圖 3。三、數據分析學會使用 Excel 中的一些常用函數,是為了使我們的工作更科學、更迅速、更輕松。那 么上述對成績統計出來的數據, 如何體現為教學服務呢?下面簡單介紹一下 Excel 中 “ 圖表 ” 的應用,以便我們對各科的試題進行分析:在 Excel 中 “ 圖表 ” 是反映表格數據的直觀表現, 通過圖表可以非常迅速

15、直觀地對數據產 生總體上的認識,這正是統計學中,最常用的對數據分布的表現方式。(一 使用 “ 圖表向導 ” 建立 “ 語文 ” 分數的分布圖表1. 選取圖表類型:可以先選取表格中某個空白的單元格,單擊菜單中的 “ 插入圖表 ” , 在彈出的 “ 圖表向導-步驟 1” 對話框中,選擇一種圖表類型和子圖表類型,如我們選擇 “ 折 線圖 ” 。單擊 “ 確定 ” 按鈕后,進入 “ 圖表向導-步驟 2” 。2.選擇圖表源數據:在彈出的步驟 2對話框中,單擊數據區域右側的按鈕,對話框消 失,按前面介紹過的方法選取數據區域 (b21c25, 回車確定選取后,單擊 “ 下一步 ” 進入 “ 圖 表向導-步驟

16、 3” 。3.設置圖表選項:在彈出的步驟 3對話框中,可以簡單設置如圖 4,單擊 “ 下一步 ” 進 入 “ 圖表向導-步驟 4” 。4. 選擇圖表位置:在彈出的對話框中, 需要選擇生成圖表放置的位置, 此時單擊 “ 完成 ” 按鈕,把圖表嵌在當前的工作表中,圖 5為完成的 “ 語文 ” 分數分布圖表。具體的數據分析留給有興趣的讀者。下期將用一個貨物銷售的例子,說明總計 (SUMIF和分類匯總 (SUBTOTAL兩個函數的 用法。前面介紹過求和函數 (SUM,它能對選取區域內的數據進行按行 (或列 求和,但在實際應用 中, 經常需要進行有條件的數據匯總。 本期介紹的總計函數 (SUMIF, 將

17、為你解決這個問題; 另外介紹的分類匯總函數 (SUBTOTAL, 可以很容易地計算分類匯總。 這兩個函數的作用都 十分強大,利用它可以簡化條件匯總和分類匯總。如圖 1的樣表, 是某百貨公司下屬的四個門市部, 都銷售同樣的商品; 假設其中的四個 售貨員, 輪流在四個門市部工作, 樣表是他們在一段時間內的營業銷售情況, (其中的 “ 金額 ” 可以用公式計算,方法是在 F2中輸入公式 “=D2*E2” 進行計算,然后按照前面講述過的拖 動進行復制的方法,即可求出各門市部各種商品的銷售金額 。下面將舉例說明用總計函數 (SUMIF,分別按門市部、售貨員、某一商品、某一范圍進行有關的匯總;用分類匯總函

18、數 (SUBTOTAL,分別計算某門市部商品數量的平均值、某門市部的商品數量之和、某門市部貨物類別的數目、某門市部最大銷售量和最便宜的單價。一、對固定的單元格區域進行命名上期已介紹過對一定的單元格區域進行命名的方法, 應該值得注意和學會運用, 因為命 名單元格區域對靈活運用函數是十分重要的, 它將為運用函數帶來極大的方便。 這里先按上 期介紹過的方法,利用菜單中的 “ 插入 /名稱 /定義 ” 命令,在 “ 定義名稱 ” 的對話框中,分別添 加對如下區域的命名:把 “A2A17” 命名為 “ 門市部 ” , 把 “B2B17” 命名為 “ 售貨員 ” , 把 “C2 C17” 命名為 “ 類別

19、 ” ,下面的幾項依次命名為 “ 數量 ” 、 “ 單價 ” 、 “ 金額 ” 。二、用總計函數 (SUMIF分別按門市部、售貨員、商品類別、某一條件進行有關的匯總 1. 按門市部進行匯總以 “ 門市部 3” 進行金額的匯總為例:按我們都已熟悉的方法,首先選取存放數據的單元 格,然后選取函數 “SUMIF” ,在彈出的 “ 粘貼函數 ” 對話框中,設置如圖 2,其中的 “Range” 為選取的區域,這個區域 (前面已定義為 “ 門市部 ” 是下面條件判斷的依據、 “Criteria” 為計算 條件,它將判斷選取區域中哪些單元格 (即 “ 門市部 3” 符合計算要求、 “Sum_range” 為

20、求和區 域,這個區域中的數值 (“ 金額 ” 用于真正的求和。2. 按售貨員進行匯總以 “ 劉芳 ” 的銷售數量為例, 與上述方法一樣, 只要在 “ 粘貼函數 ” 對話框圖中, 在 “Range” 項輸入 “ 售貨員 ” ,在 “Criteria” 項中輸入 “ 劉芳 ” ,在 “Sum_range” 項中輸入 “ 數量 ” 。3. 按商品類別進行匯總以 “ 茶葉 ” 的銷售金額為例,只要在 “ 粘貼函數 ” 對話框圖中,在 “Range” 項輸入 “ 類別 ” , 在 “Criteria” 項中輸入 “ 茶葉 ” ,在 “Sum_range” 項中輸入 “ 金額 ” 。4.按一定條件進行匯總

21、如計算除 “ 礦泉水 ” 之外的貨款:在 “ 粘貼函數 ” 對話框圖中,在 “Range” 項輸入 “ 類別 ” , 在 “Criteria” 項中輸入 “$#60;$#62;礦泉水 ” 、在 “Sum_range” 項中輸入 “ 金額 ” 。以上按不同的條件進行了 “ 數量 ” 或 “ 金額 ” 的匯總,如果雙擊存放 “ 門市部 3” 金額的單元格, 其中的計算公式為:“=SUMIF(門市部 , 門市部 3, 金額 ” ,如果需要計算其它門市部的匯總 金額,只須把計算公式利用 “ 復制 ” 和 “ 粘貼 ” 命令,復制至相對應的單元格后,把 “ 門市部 3” 修改為其它門市部即可匯總出其它門

22、市部的金額了。 同樣方法,可完成對各 “ 售貨員 ” 的匯 總、各類商品的匯總。圖 3是以上各項匯總數據的樣表,剩余的各項 (如各門市部、各種商 品的銷售數量 的匯總,大家不妨一試。不難看出,利用函數進行計算時,相對固定的單元 格區域命名后,減少了頻繁選取單元格區域的次數,給我們操作帶來了極大的方便。 三、分類匯總函數 (SUBTOTAL的應用分類匯總函數 (SUBTOTAL不僅僅是一個求和函數, 還能夠對給定區域內的數值進行其 它計算 (見圖 4 ,它的語法結構為:SUBTOTAL(function_num,ref1其中的 “function_num” 是計算類型編號, 為一個 1到 11的

23、數字, 它規定所要進行的計算 類型,圖 4為計算類型編號及具體含義; “ref1” 為進行匯總數據的單元格區域。應用舉例如下:1. 計算 “ 門市部 2” 的 “ 數量 ” 平均值選定函數后, 彈出 “ 粘貼函數 ” 對話框中, 在 “Function_num” 項中輸入計算類型 “1” , 單擊 “Ref1” 右側的按鈕,選取 “ 門市部 2” 對應的 “ 數量 ” 單元格區域 (D6D9 ,如圖 5,按 “ 確定 ” 按鈕完成。2. 計算 “ 門市部 4” 中的數量之和與計算 “ 門市部 2” 的 “ 數量 ” 平均值相似,計算類型 (“Function_num” 為 “9” ,匯總單元格 區域 (“Ref1” 為 “D14:D17” 。3. 計算 “ 門市部 2” 的商品 “ 類別 ” 數目與上例相似的操作,計算類型 (“Function_num” 為 “2” ,匯總單元格區域為 “C6:C9” 。 4. 計算最大的銷售數量計算類型 (“Function_num” 為 “4” ,匯總單元格區域 (“Ref1” 為 “D2:D17” 。5. 計算 “ 門市部 2” 中的最便宜的單價計算類型 (“Function_num” 為 “5” ,匯總單元格區域 (