Excel在統(tǒng)計整理中的應用

季凌云

DOI：10.19392/j.cnki.16717341.201720191

摘要：Excel函數(shù)庫包含了種類眾多的函數(shù)，在實際工作中，適當選取某些函數(shù)能顯著降低工作量，提高工作效率，減少工作誤差。本文以在統(tǒng)計整理工作中遇到的幾個實際問題為例，詳細闡述了Excel中Countif函數(shù)和Frequency函數(shù)在統(tǒng)計整理中的應用。

關鍵詞：統(tǒng)計整理；統(tǒng)計函數(shù)；分配數(shù)列；統(tǒng)計圖表

Excel是MicrosoftOffice軟件的重要組件之一，它主要是以表格形式實現(xiàn)對數(shù)據(jù)進行處理、統(tǒng)計分析、繪制圖表等功能，因其界面簡明、功能全面、操作方便等特點而被廣泛應用于統(tǒng)計、會計、金融、管理等領域。Excel函數(shù)庫包含了統(tǒng)計、財務、文本、邏輯等種類眾多的函數(shù)，在實際工作中，適當?shù)剡x取某些函數(shù)能極大地提高工作效率，同時能有效地降低因人為因素而導致的誤差。本文以在統(tǒng)計整理工作中遇到的幾個實際問題為例，詳細闡述Excel在統(tǒng)計整理中的應用。

一個完整的統(tǒng)計工作包括四個步驟：統(tǒng)計設計、統(tǒng)計調(diào)查、統(tǒng)計整理和統(tǒng)計分析。本文是在統(tǒng)計調(diào)查的基礎上，利用Excel對統(tǒng)計資料進行整理，并對其進行簡單地分析。

1 編制分配數(shù)列

分配數(shù)列是將總體按某一標志進行分組，并按一定順序排列出每組的總體單位數(shù)，用其反映出總體單位數(shù)在各組中的分布狀況的數(shù)列。按照分組標志性質(zhì)不同，分配數(shù)列可以分為品質(zhì)數(shù)列和變量數(shù)列，變量數(shù)列按照各組表現(xiàn)形式不同又可以分為單項式數(shù)列和組距式數(shù)列。

品質(zhì)數(shù)列是按品質(zhì)標志分組形成的分配數(shù)列，用來觀察總體單位中不同屬性的單位的分布情況，如人口按民族、性別等分組形成的數(shù)列就是品質(zhì)數(shù)列；變量數(shù)列是按數(shù)量標志分組形成的分配數(shù)列，用來觀察總體中不同變量值在各組的分布情況；單項式數(shù)列是指每組中的分組只用一個數(shù)值表示的變量數(shù)列，即一個變量值就代表一組，如家庭按照家庭人口數(shù)進行的分組；組距式數(shù)列是指數(shù)列中每一組由兩個變量值所確定的一個數(shù)值范圍來表示的變量數(shù)列，如學生成績按照分數(shù)進行分組。

1.1 使用Countif函數(shù)編制品質(zhì)數(shù)列和單項式數(shù)列

Excel函數(shù)庫中的統(tǒng)計函數(shù)Countif（）適合編制品質(zhì)數(shù)列和單項式數(shù)列，它主要用來計算單元格區(qū)域中滿足給定條件的單元格的個數(shù)。

格式：Countif（Range，Criteria），其中range表示區(qū)域，即要計算單元格數(shù)目的區(qū)域，criteria表示條件，即單元格需要滿足的條件。

實例1：某車間20名工人某日加工的零件個數(shù)見圖1，現(xiàn)要求編制一個變量數(shù)列，反應工人加工零件的分布情況。

首先，單擊存放第一組結(jié)果的單元格，如圖2中I3，單擊“插入函數(shù)”按鈕，在“選擇類別”框中找到“統(tǒng)計”，然后選擇函數(shù)“Countif”，在“Range”框中選擇所有數(shù)據(jù)所在的單元格區(qū)域“A1：E4”，且在行號和列號前加上$符號，表示絕對引用，即“$A$1：$E$4”，然后在“Criteria”框中，選擇“7”所在的單元格H3，單擊“確定”按鈕，就得到了第一組結(jié)果3。

然后，選擇數(shù)值3所在的單元格I3，按住填充柄向下拖拽至I7，這樣就自動計算出加工零件個數(shù)分別為8、9、10、11的工人人數(shù)。至于“合計”項和“比重”項，使用Sum（）函數(shù)和創(chuàng)建公式的方法即可算出。計算結(jié)果如圖3所示：

1.2 編制組距式數(shù)列

Excel函數(shù)庫中的統(tǒng)計函數(shù)Frequency（）適合編制組距式數(shù)列。此外，還可以利用“數(shù)據(jù)分析”中“直方圖”工具來完成分組、計算人數(shù)、比重、繪制直方圖等操作。

實例2：某班50名學生“統(tǒng)計學原理”課程期末考試成績?nèi)鐖D4所示，現(xiàn)在要求編制變量數(shù)列，并對該數(shù)列做簡單分析。

1.2.1 使用Frequency函數(shù)編制組距式數(shù)列

格式：Frequency（Data_array，Bins_array），其中Data_array表示需要計算單元格數(shù)目的區(qū)域，Bins_array表示數(shù)據(jù)接收區(qū)。

根據(jù)上述資料，首先應確定每一組的上限值，上限值是編制組距式數(shù)列的關鍵。上限值的確定其實就確定了每組的組距和組限。結(jié)合該問題的實際意義，確定各組上限值分別為59、69、79、89、100，即將這50個成績分值分別歸入0～59，60～69，70～79，80～89，90～100這5組中，將上限值輸入到L2：L6中。

然后，選取結(jié)果存放的單元格區(qū)域M2：M6，單擊“插入函數(shù)”按鈕，在“選擇類別”框中找到“統(tǒng)計”，選擇函數(shù)“Frequency”，在“Data_array”框中選擇所有數(shù)據(jù)所在的單元格區(qū)域A1：J5，在“Bins_array”框中，選擇所有上限值所在的區(qū)域L2：L6，按住Ctrl+Shift+Enter組合鍵，即可獲得各組相應的人數(shù)，見圖5所示：

最后，使用求和函數(shù)Sum（）計算合計人數(shù)，通過創(chuàng)建公式計算出各組人數(shù)在總?cè)藬?shù)中所占的比重。計算結(jié)果如下：

1.2.2 使用數(shù)據(jù)分析編制組距式數(shù)列（以Excel2007為例說明）

如果在“數(shù)據(jù)”選項卡中沒有“分析”這一組，首先要加載“分析工具庫”。加載的方法是：單擊“快速訪問工具欄”中的“自定義快速訪問工具欄”按鈕，選擇“其他命令”，打開“Excel選項”對話框，在左邊“加載項”中選擇“分析工具庫”，然后選擇“分析工具庫”，單擊下方的“轉(zhuǎn)到”按鈕，打開“加載宏”對話框，選擇“分析工具庫”，單擊“確定”，這時在“數(shù)據(jù)”選項卡中就出現(xiàn)了“分析”組和“數(shù)據(jù)分析”按鈕。

然后就可以用數(shù)據(jù)分析工具來編制組距式數(shù)列。選擇“數(shù)據(jù)”選項卡中的“分析”組，選擇“數(shù)據(jù)分析”按鈕，打開“數(shù)據(jù)分析”對話框，從中選擇“直方圖”，單擊“確定”，這時就打開了“直方圖”對話框，見圖7所示：

在“輸入”中的“輸入?yún)^(qū)域”選擇所有分值所在的區(qū)域，在“接收區(qū)域”選擇所有上限值所在區(qū)域；在“輸出選項”中，選擇“新工作表組”，同時勾選“柏拉圖”、“累計百分率”和“圖表輸出”。其中“柏拉圖”表示按降序排列所做的向上累計頻率，“累計百分率”表示在直方圖上添加累計頻率折線，輸入結(jié)果見圖8所示。

2 繪制統(tǒng)計圖

Excel中的統(tǒng)計圖有多種，如柱形圖、折線圖、條形圖、餅圖、面積圖、散點圖等，下面以為實例2繪制條形圖為例來說明統(tǒng)計圖的繪制方法。

首先，選擇單元格區(qū)域L1：N6，然后單擊“插入”選項卡，選擇“圖表”組中的“柱形圖”按鈕，選擇“二維柱形圖”，在當前工作表中就插入了一張二維柱形圖。

從該表可以看出，分數(shù)在70到90之間的學生人數(shù)占總?cè)藬?shù)的大部分，較接近正態(tài)分布，這說明此次試卷的難度相對學生的知識掌握程度合理，考試成績比較理想。

綜上所述，本文以兩個實際問題為例，重點闡述了Excel在統(tǒng)計整理中的應用，即編制分配數(shù)列和繪制統(tǒng)計圖表，其實，在后續(xù)的統(tǒng)計分析階段，Excel函數(shù)還有更加廣泛的應用。我們只要充分熟悉并掌握Excel函數(shù)的使用條件，結(jié)合具體問題靈活運用，就能大大提升工作效率并能保證統(tǒng)計結(jié)果的準確性。同時，本文中的兩個問題還可以為高職類《計算機應用基礎》課程教學提供兩個比較實用的案例，啟發(fā)學生在學習中舉一反三，學以致用。

參考文獻：

[1]范慧敏，王斌.統(tǒng)計學原理[M].清華大學出版社，2013.

[2]趙俊峰.Excel中函數(shù)的使用技巧與實例[J].太原城市職業(yè)技術學院學報，2010（7）：126127.