excel如何進行信息統計

使用Excel 管理人事信息，具有無須編程、簡便易行的特點。為了儘可能減少數據錄入的工作量，下面利用Excel 函數實現數據統計的自動化。

1.性別輸入根據現行的居民身份證號碼編碼規定，正在使用的18 位的身份證編碼。它的第17 位為性別(奇數為男，偶數為女)，第18 位為效驗位。而早期使用的是15 位的身份證編碼，它的第15 位是性別(奇數為男，偶數為女)。

(1)函數分解

LEN 函數返回文本字符串中的字符數。 語法：LEN(text) Text 是要查找其長度的文本。空格將作為字符進行計數。

MOD 函數返回兩數相除的餘數。結果的正負號與除數相同。 語法：MOD(number,divisor) Number 為被除數;Divisor為除數。 MID 函數返回文本字符串中從指定位置開始的特定數目的字符，該數目由用户指定。 語法：MID(text,start_num,num_chars) Text 為包含要提取字符的文本字符串;Start_num 為文本中要提取的第一個字符的位置。文本中第一個字符的start_num 為1 ，以此類推;Num_chars指定希望MID 從文本中返回字符的個數。

(2)實例分析

為了適應上述情況，必須設計一個能夠適應兩種身份編碼的性別計算公式，在D2 單元格中輸入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回車後即可

在單元格獲得該職工的性別，而後只要把公式複製到D3、D4等單元格，即可得到其他職工的性別。 為了便於大家瞭解上述公式的設計思路，下面簡單介紹一下它的工作原理：該公式由三個IF 函數構成，其中“IF(MOD(MID(C2,15,1),2)=1,"男","女")”和“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作為第一個函數的參數。公式中“LEN(C2)=15”是一個邏輯判斷語句，LEN 函數提取C2 等單元格中的字符長度，如果該字符的長度等於15，

則執行參數中的第一個IF 函數，否則就執行第二個IF 函數。在參數“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。

MID 函數從C2 的指定位置(第15 位)提取1 個字符，而MOD 函數將該字符與2 相除，獲取兩者的餘數。如果兩者能夠除盡，説明提取出來的字符是0(否則就是1)。邏輯條件“MOD(MID(C2,15,1),2)=1”不成立，這時就會在D2 單元格中填入“女”，反之則會填入“男”。 如果LEN 函數提取的C2 等單元格中的字符長度不等於15， 則會執行第2個IF函數。除了MID 函數從C2 的指定位置(第17 位，即倒數第2 位)提取1 個字符以外，其他運算過程

與上面的介紹相同。

　　2.出生日期輸入

(1)函數分解

CONCATENATE 函數將幾個文本字符串合併為一個文本字符串。 語法：CONCATENATE(text1,text2,...) Text1,text2,...為1～30 個要合併成單個文本項的文本項。文本項可以為文本字符串、數字或對單個單元格的引用。

(2)實例分析

與上面的思路相同，我們可以在E2 單元格中輸入公式“=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年

",MID(C2,9,2),"月",MID(C2,11,2),"日"),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作為邏輯判斷語句使用，它可以判斷身份證號碼是15 位的還是18 位的，從而調用相應的計算語句。 對15 位的身份證號碼來説，左起第7 至12 個字符表示出生年、月、日，此時可以使用MID 函數從身份證號碼的特定位置，分別提取出生年、月、日。然後用CONCATENATE 函數將提取出來的文字合併起來，就能得到對應的出生年月日。公式中“19”是針對早期身份證號碼中存在2000 年問題設計的，它可以在計算出來的出生年份前加上“19”。對“18”位的身份證號碼的計算思路相同，只是它不存在2000 年問題，公式中不用給計算出來的出生年份前加上“19”。 注意：CONCATENATE 函數和MID 函數的操作對象均為文本，所以存放身份證號碼的單元格必須事先設為文本格式，然後再輸入身份證號。

　　3.職工信息查詢

Excel 提供的“記錄單”功能可以查詢記錄，如果要查詢人事管理工作表中的某條記錄，然後把它打印出來，必須採用下面介紹的方法。

(1)函數分解

INDEX 函數返回數據清單或數組中的元素值，此元素由行序號和列序號的索引值給定。 INDEX 函數有兩種語法形式：數組和引用。數組形式通常返回數值或數值數組，引用形式通常返回引用。當函數INDEX 的第一個參數為數組常數時，使用數組形式。 語法1(數組形式)：INDEX(array,row_num,column_num) Array 為單元格區域或數組常量。如果數組只包含一行或一列，則相對應的參數row_num 或column_num為可選。如果數組有多行和多列，但只使用row_num 或c olumn_num，函數INDEX 返回數組中的整行或整列，且返回值也為數組;Row_num 為數組中某行的行序號，函數從該行返回數值。如果省略row_num， 則必須有column_num;Column_num 為數組中某列的

列序號，函數從該列返回數值。如果省略column_num，則必須有row_num。 語法2(引用形式)：INDEX(reference,row_num,column_num,area_num) Reference 表示對一個或多個單元格區域的引用。如果為引用輸入一個不連續的區域，必須用括號括起來。如果引用中的每個區域只包含一行或一列，則相應的參數row_num 或

column_num 分別為可選項;Row_num 引用中某行的行序號，函數從該行返回一個引用;Column_num引用中某列的列序號，函數從該列返回一個引用;Area_num 選擇引用中的一個區域，並返回該區域中row_num 和column_num 的交叉區域。選中或輸入的第一個區域序號為1，第二個為2，以此類推。如果省略area_num，函數INDEX 使用區域1。 MATCH 函數返回在指定方式下與指定數值匹配的數組中元素的相應位置。 語法：MATCH(lookup_value,lookup_array,match_type) Lookup_value 為需要在數據表中查找的數值;Lookup_value 為需要在Look_array 中查找的數值;Match_type 為數字-1、0或1 。

(2)實例分析

如果上面的人事管理工作表放在Sheet1 中，為了防止因查詢操作而破壞它(必要時可以添加只讀保護)，我們可以打開另外一個空白工作表Sheet2，把上一個數據清單中的列標記複製到第一行。假如你要以“身份證號碼”作為查詢關鍵字，就要在C2 單元格中輸入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的參數“ SC S5”引用公式所在工作表中的C5 單元格(也可以選用其他單元格)，執行查詢時要在其中輸入查詢關鍵字，也就是待查詢記錄中的身份證號碼。參數“Sheet1!C2:C600”設定INDEX 函數的查詢範圍，引用的是數

據清單C 列的所有單元格。MATCH函數中的參數“0”指定它查找“Sheet1! SC S2: SC S600”區域中等於 SC S5的第一個值，並且引用的區域“Sheet1! SC S2: SC S600,0”可以按任意順序排列。上面的公式執行數據查詢操作時，首先由MATCH 函數在“Sheet1! SC S2: SC S600” 區域搜索，找到“ SC S5” 單元格中的數據在引用區域中的位置(自上而下第幾個單元格)，從而得知待查詢數據在引用區域中的第幾行。 接下來INDEX 函數根據MATCH 函數給出的行號，返回“Sheet1!C2:C600”區域中對應行數單元格中的數據。假設其中待查詢的“身份證號碼”是“3234567896”，它位於“Sheet1! SC S2: SC S600”區域的第三行，MATCH函數就會返回“3”。接着INDEX 函數返回“Sheet1!C2:C600”區域中行數是“3”的數據，也就是“3234567896”。 然後，我們將光標放到C2 單元格的填充柄上，當十字光標出現以後向右拖動，從而把C2 中的公式複製到D2、E2 等單元格(然後再向左拖動，以便把公式複製到B2、A2單元格)，這樣就可以獲得與該身份證號對應的性別、籍貫等數據。 注意：公式複製到D2、E2等單元格以後，INDEX函數引用的區域就會發生變化，由C2:C600 變成D2 :D600、E2:E600等等。但是MATCH 函數返回的(相對)行號仍然由查詢關鍵字給出，此後INDEX 函數就會根據MATCH 函數返回的行號從引用區域中找到數據。 在Sheet2 工作表中進行查詢時只要在查詢輸入單元格中輸入關鍵字，回車後即可在工作表的C2 單元格內看到查詢出來的身份證號碼。如果輸入的身份證號碼關鍵字不存在或輸入錯誤，則單元格內會顯示“#N/A”字樣。

　　4.職工性別統計

(1)函數分解

COUNTIF 函數計算區域中滿足給定條件的單元格的個數。語法：COUNTIF(range,criteria) Range 為需要計算其中滿足條件的單元格數目的單元格區域;Criteria為確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。

(2)實例分析

假設上面使用的人事管理工作表中有599 條記錄，統計職工中男性和女性人數的方法是：選中單元格D601(或其他用不上的空白單元格)，統計男性職工人數可以在其中輸入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接着選中單元格D602，在其中輸入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回車後即可得到“男399 人”、“女200 人”。

上式中D2:D600 是對“性別”列數據區域的引用，實際使用時必須根據數據個數進行修改。“男”或“女”則是條件判斷語句，用來判斷區域中符合條件的數據然後進行統計。“&” 則是字符連接符，可以在統計結果的前後加上“男”、“人”字樣，使其更具有可讀性。

　　5.年齡統計

在人事管理工作中，統計分佈在各個年齡段中的職工人數也是一項經常性工作。假設上面介紹的工作表的E2:E600 單元格存放職工的'工齡，我們要以5 年為一段分別統計年齡小於20 歲、20 至25 歲之間，一直到55 至60 歲之間的年齡段人數，可以採用下面的操作方法。

(1)函數分解

FREQUENCY 函數以一列垂直數組返回某個區域中數據的頻率分佈。 語法：FREQUENCY(data_array,bins_array)

Data_array 為一數組或對一組數值的引用，用來計算頻率。如果data_array 中不包含任何數值，函數FREQUENCY 返

回零數組;Bins_array為間隔的數組或對間隔的引用，該間隔用於對data_array 中的數值進行分組。如果bins_array

中不包含任何數值，函數FREQUENCY 返回data_array 中元素的個數。

(2)實例分析

首先在工作表中找到空白的I 列(或其他列)，自I2 單元格開始依次輸入20、25、30 、35、40...60， 分別表示統計年齡小於20、20 至25 之間、25 至30 之間等的人數。然後在該列旁邊選中相同個數的單元格，例如J2：J10 準備存放各年齡段的統計結果。然後在編輯欄輸入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 組合鍵即可在選中單元格中看到計算結果。其中位於J2 單元格中的結果表示年齡小於20 歲的職工人數，J3單元格中的數值表示年齡在20 至25 之間的職工人數等。

　　6.名次值統計

在工資統計和成績統計等場合，往往需要知道某一名次(如工資總額第二、第三)的員工的工資是多少。這種統計的操

作方法如下。

(1)函數分解

LARGE 函數返回數據集中第K 個最大值。使用此函數可以根據相對標準來選擇數值。 語法：LARGE(array,k)

Array 為需要從中選擇第K 個最大值的數組或數據區域; K 為返回值在數組或數據單元格區域中的位置(從大到小排)。SMALL 函數返回數據集中第K 個最小值。使用此函數可以返回數據集中特定位置上的數值。法：SMALL(array,k) Array 為需要找到第K 個最小值的數組或數字型數據區域;K為返回的數據在數組或數據區域裏的位置(從小到大)。

(2)實例分析

假設C2:C688 區域存放着員工的工資，首先在D 列選取空白單元格D3，在其中輸入公式“=LARGE(C2:C688,D2)”。其中D2 作為輸入名次變量的單元格，如果你在其中輸入3，公式就可以返回C2:C688 區域中第三大的數值。

如果我們把上述公式修改為“=SMALL(C2:C688,D1)”，然後在D1 單元格中輸入6，就可以獲得C2:C688 區域倒數第六(小)的數值。 為方便起見，你可以給C2:C688 區域定義一個名稱“職工工資”。此後可以把上述公式修改為“=LARGE(職工工資,D2)”或“=SMALL(職工工資,D1)”。

　　7.位次閾值統計

與上例相似，在工資統計和成績統計等場合，需要知道排名達到總體的前1/3 的工資總額或分數(稱為“閾值”)是多

少。這種統計的操作方法如下：

(1)函數分解

PERCENTILE 函數返回區域中數值的第K 個百分點的值。可以使用此函數來建立接受閾值。 語法：PERCENTILE(array,k) Array 為定義相對位置的數組或數據區域;K為0 到1 之間的百分點值，包含0和1。

(2)實例分析

假設C2:C200 區域存放着學生的考試成績，首先在D列選取空白單元格D3，在其中輸入公式 “=PERCENTILE(C2:C200,D2)”。其中D2 作為輸入百分點變量的單元格，如果你在其中輸入0.33，公式就可以返回名次達到前1/3 所需要的成績。