Excel在統計整理中的應用

季凌云

DOI：10.19392/j.cnki.16717341.201720191

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

關鍵詞：統計整理；統計函數；分配數列；統計圖表

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

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

1 編制分配數列

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

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

1.1 使用Countif函數編制品質數列和單項式數列

Excel函數庫中的統計函數Countif（）適合編制品質數列和單項式數列，它主要用來計算單元格區域中滿足給定條件的單元格的個數。

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

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

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

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

1.2 編制組距式數列

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

實例2：某班50名學生“統計學原理”課程期末考試成績如圖4所示，現在要求編制變量數列，并對該數列做簡單分析。

1.2.1 使用Frequency函數編制組距式數列

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

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

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

最后，使用求和函數Sum（）計算合計人數，通過創建公式計算出各組人數在總人數中所占的比重。計算結果如下：

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

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

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

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

2 繪制統計圖

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

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

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

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

參考文獻：

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

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