Excel在創建職工信息統計表中的應用

[摘要] Microsoft Office中的Excel 是具有強大的數據分析和管理功能的電子表格軟件，是辦公室工作中很常用也很重要的一款軟件。運用Excel函數功能并配合使用一些編輯技巧可以高效、準確地完成數據統計和管理工作。本文介紹了用Excel創建職工信息統計表，并根據職工不同職稱、學歷、年齡分別進行統計的方法和步驟。

[關鍵詞] Excel；函數；統計

[中圖分類號] F232 [文獻標識碼] A [文章編號] 1673 - 0194（2013）05- 0011- 02

各企事業及機關單位經常需要對職工信息進行統計和管理，比如說統計職工人數、性別、年齡、學歷、職稱分布的情況等數據信息。Microsoft Office中的Excel 是具有強大的數據分析和管理功能的電子表格軟件，是辦公室工作中很常用也很重要的一款軟件。運用Excel函數功能并配合使用一些編輯技巧對數據信息進行統計分析，方便快捷，省時省力。現以Excel 2007為例，介紹創建職工信息統計表的方法和步驟。

1 創建職工信息數據表

（1）啟動Excel 2007，創建新的工作簿。

（2）首先合并A1至G1單元格，輸入“職工信息統計表”，然后輸入職工基本信息：姓名、身份證號碼、出生日期、性別、年齡、學歷、職稱。

1）多于15位數字的輸入[1]。身份證號碼多為15位或18位數字，而在單元格中輸入15位以上數字后單元格顯示方式會自動變成科學計數，且15位數后全變成零了，因此在輸入18位身份證號碼前，選定單元格B3，按右鍵彈出快捷菜單，單擊“設置單元格格式→數字→文本”，將單元格格式設置為文本；或者在B3單元格輸入身份證號碼前輸入一個單引號（英文狀態下）“ ' ”，都可以輸入18位的身份證號碼。

2）從身份證號碼提取出生日期、性別、年齡的信息[2]。從身份證號碼中提取出生日期。從B3單元格提取“職工1”的出生日期并在C3單元格錄入，操作步驟：選中單元格C3，輸入函數公式：“=IF（LEN（B3）=15，19MID（B3，7，2）\"年\"，MID（B3，7，4）\"年\"）IF（LEN（B3）=15，MID（B3，9，2）\"月\"，MID（B3，11，2）\"月\"）IF（LEN（B3）=15，MID（B3，11，2）\"日\"，MID（B3，13，2）\"日\"）” 并回車，立刻顯示出生日期為：1978年04月24日。如果只需要出生年月，可以用left函數公式“=left（C3，8）”提取出生日期單元格C3“1978年04月24日”左起8個字符，即“1978年04月”。

從身份證號碼中提取性別。從B3單元格提取“職工1”的性別并在D3單元格錄入，操作步驟：選中D3單元格，輸入函數公式：“=IF（MOD（（IF（LEN（B3）=15，RIGHT（B3），MID（B3，17，1））），2）=0，\"女\"，\"男\"）” 并回車，立刻顯示職工1性別為“男”。

從出生日期中提取年齡。從B3單元格提取“職工1”的年齡并在E3單元格錄入，操作步驟：在E3單元格中輸入函數公式（1）：“=CONCATENATE（DATEDIF（C3，\"2011-08-31\"，\"y\"））”并回車，計算職工1至2011年8月31日的年齡；函數公式（2）“=CONCATENATE（DATEDIF（C3，TODAY（），\"y\"））” 并回車，計算職工1截止至當天的年齡。

3）使用智能填充技巧將其他職工的出生日期、性別、年齡錄入。選中連續的單元格（C3：E3），將光標移到單元格右下角，當光標變成小黑十字時（即填充柄），按著鼠標左鍵向下拖動至E8單元格，將單元格（C3：E3）單元格中的公式連續復制到同列中下列連續的單元格中，即可錄入其他職工的出生日期、性別、年齡。

4）由于出生日期、性別、年齡3列數據都是用公式計算的，計算出來的數據不能直接復制或者移動，否則就會顯示公式出錯，所以我們要將公式算出來的數據轉換為數值。操作步驟：首先選定C3至E3單元格，按右鍵彈出快捷菜單選擇“復制”，單擊開始菜單選擇“粘貼→粘貼值→確定”。E列是年齡，即使簡單地把單元格格式改成“數值”也無濟于事，這些文本型的數字不能作各種排序、求和等函數運算，這時我們需要將它們轉換成數值格式。操作方法：選中需要轉換單元格（E3：E8），單元格旁邊會出現一個智能標記，單擊智能標記，在隨后彈出的下拉列表中選中“轉換為數字”選項，即可快速完成轉換。

2 統計職工信息

2.1 計算職工總數及其中女性人數

用COUNTA函數統計全部職工人數，COUNTA函數功能是返回單元格區域中非空值的單元格個數。操作步驟：選定單元格B11，輸入公式“=COUNTA（A3：A8）”，然后按Enter鍵確認，計算出職工總人數。用Countif函數[3]可以統計女性職工人數情況，COUNTIF函數功能是統計某個單元格區域符合指定條件的單元格數目，操作步驟：選定單元格B12，輸入公式“=COUNTIF（D3：D8，\"女\"）”，按Enter鍵確認，計算出職工中女性人數。

2.2 統計職稱、學歷分布

用COUNTIF函數對職工中不同職稱的職工分別進行統計。計算職稱為正高級的職工人數：選定單元格B13，輸入公式“=COUNTIF（G3：G8，\"正高級\"）”，按Enter鍵確認；計算職稱為副高級的職工人數：選定單元格B14，輸入公式“=COUNTIF（G3：G8，\"副高級\"）”按Enter鍵確認；計算出職稱為中級的職工人數，在單元格B15輸入公式“=COUNTIF（G3：G8，\"中級\"）”；計算職稱為初級的職工人數，在單元格B16輸入公式“=COUNTIF（G3：G8，\"初級\"）”；計算無職稱的職工人數，在單元格B17輸入公式“=COUNTIF（G3：G8，\"無職稱\"）”。

同樣用COUNTIF函數可以完成職工學歷統計。操作步驟：分別選定單元格C11、D11、E11、F11、G11，依次輸入函數公式“=COUNTIF（F3：F8，\"博士\"）”；“=COUNTIF（F3：F8，\"碩士\"）”；“=COUNTIF（F3：F8，\"本科\"）”；“=COUNTIF（F3：F8，\"專科\"）”；“=COUNTIF（F3：F8，\"高中階段及以下\"）”，然后按Enter鍵確認，即可計算出學歷為博士、碩士、本科、專科及高中階段及以下的職工人數。

2.3 雙重條件計數

COUNTIFS函數是Excel 2007新增函數，用于計算某個區域中滿足多重條件的單元格數目。在C12至G12單元格需要計算的職工人數有雙重條件，條件1性別為女性，條件2學歷分別為博士、碩士、本科、專科或高中階段及以下，用COUNTIFS可以滿足。選定單元格C12，輸入函數公式：“=COUNTIFS（D3：D8，\"女\"，F3：F8，\"博士\"）”按Enter鍵確認，計算女性博士生的職工人數；選定D12單元格，輸入函數公式“=COUNTIFS（D3：D8，\"女\"，F3：F8，\"碩士\"）”按Enter鍵確認，計算女性碩士生的職工人數；余下E12至G12單元格以此類推即可。同樣用COUNTIFS函數可以計算條件1：職稱為正高級、副高級、中級、初級或者無職稱；條件2：學歷分別為博士、碩士、本科、專科、高中階段及以下的職工數 。

2.4 統計年齡分布

用Excel怎樣統計出職工各年齡段內的人數分布呢？大多數人使用COUNTIF或COUNTIFS函數，公式要在B20：B27單元格內統計顯示E3：E8內30歲及以下、31～35歲、36～40歲、41～49歲、50～55歲、56～60歲、61歲以上各年齡段內人數分布情況，要輸入7條公式，十分麻煩。其實，Excel已經為我們提供了一個進行頻度分析的frequency數組函數[3]，frequency 函數是以一列垂直數組返回某個區域中數據的頻率分布，用一條數組公式就能輕松地統計出給定的年齡范圍內不同年齡段各分數段的人數分布，C20：C26為各年齡段的分段點。操作步驟如下：選中需要顯示各年齡段人數的單元格區域B20：B27，在編輯欄內輸入函數“=FREQUENCY（E3：E8，C20：C26）”， 然后同時按Ctrl+Shift+Enter組合鍵產生數組公式“{=FREQUENCY（E3：E8，C20：C26）}”，這里要注意“{ }”不能手工鍵入，必須按下“Crtl+Shift+Enter”組合鍵由系統自動產生，即可統計出各年齡段的人數。

使用辦公軟件準確高效地處理各類數據，不僅可以減輕勞動強度和提高工作效率，而且可以激發工作積極性與熱情，使現代科技滲透到日常管理之中。

主要參考文獻

[1]肖文雅，王燕，王涵.Excel 2003中的填充技巧和特殊數據輸入方法[J].中國醫學教育技術，2010，24（4）.

[2]錢秀峰.Excel中函數嵌套功能的實際應用[J].人力資源管理，2010（4）.

[3]趙磊.Excel中進行數據分析的幾個常用函數[J].中小企業管理與科技，2010（18）.