采用Excel制作數(shù)據(jù)分段統(tǒng)計(jì)表的研究

李曉明

摘要：EXCEL表處理軟件是美國微軟公司研制的辦公自動化軟件OFFICE 中的重要成員，它能夠方便地制作出各種電子表格，可以使用公式和函數(shù)對數(shù)據(jù)進(jìn)行復(fù)雜的運(yùn)算。在EXCEL中不必進(jìn)行編程就能對工作表中的數(shù)據(jù)進(jìn)行檢索、分類、排序、篩選等操作，利用系統(tǒng)提供的函數(shù)可完成各種數(shù)據(jù)的分析。該文研究了運(yùn)用多種方法對數(shù)據(jù)進(jìn)行分段統(tǒng)計(jì)，并對使用這些方法制作數(shù)據(jù)統(tǒng)計(jì)表的各個(gè)步驟進(jìn)行介紹，以期和大家分享數(shù)據(jù)分段統(tǒng)計(jì)表制作技巧。

關(guān)鍵詞：統(tǒng)計(jì)表；數(shù)據(jù)；操作；區(qū)域；函數(shù)；透視表

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2014）23-5572-08

為實(shí)際需要，常常要把工農(nóng)業(yè)生產(chǎn)、科學(xué)技術(shù)與日常工作中所得到的相互關(guān)聯(lián)的數(shù)據(jù)，按照一定的要求進(jìn)行整理、歸類、并且按照一定的順序把數(shù)據(jù)排列起來，制成表格，成為集中而有序地體現(xiàn)統(tǒng)計(jì)資料的表格，便于人們用來分析問題和研究問題?，F(xiàn)實(shí)生活中存在各種表格，如學(xué)生登記表中年齡段，考試成績表中分?jǐn)?shù)段，工資表，物價(jià)表、銷售情況表等，需要我們進(jìn)行整理并使之進(jìn)一步表格化，形成統(tǒng)計(jì)表。

實(shí)驗(yàn)使用數(shù)據(jù)：

實(shí)驗(yàn)要求：

1） 將“英語”成績進(jìn)行分為“60以下”、“60-69”、“70-79”、“80-89”和“90-100”各檔，并統(tǒng)計(jì)其中人數(shù)。

2） 將“總分”按“180以下”、“180-239”、“240-269”和“270及以上”分檔，并統(tǒng)計(jì)各檔人數(shù)。

1 實(shí)驗(yàn)一的操作

1.1 用IF函數(shù)+分類匯總

1） 語法結(jié)構(gòu)

IF（條件，結(jié)果1，結(jié)果2）。

2） 功能

對滿足條件的數(shù)據(jù)進(jìn)行處理，條件滿足則輸出結(jié)果1，不滿足則輸出結(jié)果2?？梢允÷越Y(jié)果1或結(jié)果2，但不能同時(shí)省略。

3） 操作

⑵選擇數(shù)據(jù)-分類匯總的方法

統(tǒng)計(jì)某個(gè)區(qū)域中滿足多重條件的單元格個(gè)數(shù)。

1.3 使用數(shù)據(jù)透視表

數(shù)據(jù)透視表是microsoft excel表格提供的一個(gè)便捷的數(shù)據(jù)分析工具。它能夠較快的將所需數(shù)據(jù)呈現(xiàn)在表格或者圖形中。數(shù)據(jù)透視的作用，是根據(jù)條件對大量數(shù)據(jù)進(jìn)行：求和、計(jì)數(shù)、平均值、最大值、最小值、乘積、數(shù)值計(jì)算、標(biāo)準(zhǔn)偏差、方差等計(jì)算類型的計(jì)算。

1） 選擇“插入”功能區(qū)的“數(shù)據(jù)透視表”

2） 在隨之打開的“創(chuàng)建數(shù)據(jù)透視表”窗口，選擇數(shù)據(jù)表中“英語”所在區(qū)域。再選擇數(shù)據(jù)透視表所放地點(diǎn)（單元格）。

3） 將“英語”字段分別拖到“行標(biāo)簽”區(qū)域，將“數(shù)值”區(qū)域，然后把字段值設(shè)置為“計(jì)數(shù)”

1.4 選用FREQUENCY函數(shù)

1） 語法

FREQUENCY（data_array，bins_array）

Data_array 是一個(gè)數(shù)組或?qū)σ唤M數(shù)值的引用，您要為它計(jì)算頻率。如果 data_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 將返回一個(gè)零數(shù)組。

Bins_array 是一個(gè)區(qū)間數(shù)組或?qū)^(qū)間的引用，該區(qū)間用于對 data_array 中的數(shù)值進(jìn)行分組。如果 bins_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回的值與 data_array 中的元素個(gè)數(shù)相等。

2） 功能

計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個(gè)垂直數(shù)組。例如，使用函數(shù) FREQUENCY可以在分?jǐn)?shù)區(qū)域內(nèi)計(jì)算測驗(yàn)分?jǐn)?shù)的個(gè)數(shù)。

3） 操作步驟

①設(shè)置分段點(diǎn)：在I20：I23分別輸入60、70、80和90

②選中統(tǒng)計(jì)數(shù)據(jù)存放區(qū)域J19：J23

⑤同時(shí)按下Shift+Alt+Enter鍵

⑥結(jié)果如圖：

4） FREQUENCY函數(shù)操作要點(diǎn)

①分段值的輸入必須在垂直區(qū)域；

②在插入函數(shù)前必須先選定輸出區(qū)域；

③函數(shù)參數(shù)設(shè)置完后必須按Shift+Alt+Enter鍵才會得到輸出結(jié)果。

5） 問題的思考

FREQUENCY是一個(gè)頻段函數(shù)，我們需要了解它在頻段劃分時(shí)，把臨界點(diǎn)的等于包含在哪里？當(dāng)成績出現(xiàn)臨界值時(shí)，我們可以看出，F(xiàn)REQUENCY函數(shù)的統(tǒng)計(jì)說明如下圖：

經(jīng)過實(shí)驗(yàn)一操作中四種方法的比較，IF函數(shù)+分類匯總屬早期沒有COUNTIF函數(shù)時(shí)一種較為繁瑣的統(tǒng)計(jì)方法；相對來說，F(xiàn)REQUENCY函數(shù)是一種最為快捷的分段統(tǒng)計(jì)方法。但是，如果不掌握操作要點(diǎn)，也許就無法得到輸出結(jié)果；不注意準(zhǔn)確設(shè)置臨界點(diǎn)，將會產(chǎn)生統(tǒng)計(jì)表的數(shù)據(jù)誤差。

2 實(shí)驗(yàn)二操作

2.1 選用FREQUENCY函數(shù)

①根據(jù)實(shí)驗(yàn)一結(jié)果，在I2：I4區(qū)域分別輸入“179”、“239”和“269”；

②選中K1：K4區(qū)域；

③點(diǎn)?x插入FREQUENCY函數(shù)；

④選中數(shù)據(jù)區(qū)域和分段數(shù)組；

⑤同時(shí)按Shift+Alt+Enter；

完成分段統(tǒng)計(jì)表的制作。

2.2 使用COUNTIFS函數(shù)

③完成統(tǒng)計(jì)表制作。

說明：使用COUNTIFS函數(shù)明顯要比COUNTIF函數(shù)簡捷。

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

實(shí)驗(yàn)一中使用數(shù)據(jù)透視表進(jìn)行分段統(tǒng)計(jì)，有個(gè)“組合”設(shè)置窗口，它只能設(shè)置進(jìn)行等步長的統(tǒng)計(jì)表。那么，對于本實(shí)驗(yàn)中不等步長（60，30，30），因此需要進(jìn)行分組后制作統(tǒng)計(jì)表。

1） 選中“總分”列數(shù)據(jù)；

2） 插入數(shù)據(jù)透視表

1） 將字段“總分”拖到行標(biāo)簽

2） 選中小于240的數(shù)據(jù)單元格，鼠標(biāo)擊右鍵→選“創(chuàng)建組”，生成“數(shù)據(jù)組1”

3） 選中大于等于240的數(shù)據(jù)單元格，鼠標(biāo)擊右鍵→選“創(chuàng)建組”，生成“數(shù)據(jù)組2”

4） 將字段“總分”拖到“數(shù)值”區(qū)域，并設(shè)為“計(jì)數(shù)”

5） 鼠標(biāo)雙擊“數(shù)據(jù)組1”右邊空白區(qū)域（I3）

7） 分別將“總分”字段拖到“行標(biāo)簽”和“數(shù)值”區(qū)域

11） 分別將“總分”字段拖到“行標(biāo)簽”和“數(shù)值”區(qū)域

12） 按照11的步驟方法，設(shè)置分組數(shù)據(jù)的值（初始值、終止值和步長）

注意：該方法是在數(shù)據(jù)透視表基礎(chǔ)上，利用“分組”才能實(shí)現(xiàn)分段統(tǒng)計(jì)效果。

3 總結(jié)

通過以上兩個(gè)實(shí)驗(yàn)可以看出，在對數(shù)據(jù)進(jìn)行分段統(tǒng)計(jì)制作統(tǒng)計(jì)表時(shí)，采用FREQUENCY函數(shù)是最快捷的；采用COUNTIFS函數(shù)時(shí)，必須注意條件的邏輯不能出錯(cuò)；使用數(shù)據(jù)透視表進(jìn)行分段統(tǒng)計(jì)，雖然讓人感覺有點(diǎn)繁瑣，但是不需要設(shè)置判斷條件，特別是數(shù)據(jù)透視表的分組統(tǒng)計(jì)，可以無需知道分組后數(shù)據(jù)有多大的量，不容易出錯(cuò)。

通過分別采用多種方法制作數(shù)據(jù)統(tǒng)計(jì)表的研究，有助于我們在實(shí)際操作中，選擇其中最熟練、得心應(yīng)手的方法來實(shí)現(xiàn)。也可以選用其中兩種以上的方法分別制作統(tǒng)計(jì)表，以驗(yàn)證統(tǒng)計(jì)表制作結(jié)果的準(zhǔn)確性。在數(shù)據(jù)量極大的情況下，互相驗(yàn)證是一個(gè)非常好的方法。

參考文獻(xiàn)：

[1] 杜茂康.Excel與數(shù)據(jù)處理[M].北京：電子工業(yè)出版社， 2014.

[2] Excel Home.Excel 2010數(shù)據(jù)透視表應(yīng)用大全[M].北京：人民郵電出版社，2013.

[3] 楊小麗.Excel數(shù)據(jù)處理大全[M].北京：中國鐵道出版社，2013.