使用Excel 管理人事資訊，具有無須編程、簡便易行的特點。假設有一個人事管理工作表，它的A1、B1、C1、D1 、E1、F1、G1 和H1 單格分別輸入“序號”、“姓名”、“身份證號碼”、“性別”、“出生年月”等。自第2 行開始依次輸入職工的人事資訊。為了盡可能減少資料錄入的工作量，下面利用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 所需要的成績。
留言列表
