excel如何進(jìn)行信息統(tǒng)計(jì)
使用Excel 管理人事信息，具有無須編程、簡便易行的特點(diǎn)。為了盡可能減少數(shù)據(jù)錄入的工作量，下面利用Excel 函數(shù)實(shí)現(xiàn)數(shù)據(jù)統(tǒng)計(jì)的自動化。
1.性別輸入根據(jù)現(xiàn)行的居民身份證號碼編碼規(guī)定，正在使用的18 位的身份證編碼。它的第17 位為性別(奇數(shù)為男，偶數(shù)為女)，第18 位為效驗(yàn)位。而早期使用的是15 位的身份證編碼，它的第15 位是性別(奇數(shù)為男，偶數(shù)為女)。
(1)函數(shù)分解
LEN 函數(shù)返回文本字符串中的字符數(shù)。 語法：LEN(text) Text 是要查找其長度的文本。空格將作為字符進(jìn)行計(jì)數(shù)。
MOD 函數(shù)返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號與除數(shù)相同。 語法：MOD(number,divisor) Number 為被除數(shù);Divisor為除數(shù)。 MID 函數(shù)返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。 語法：MID(text,start_num,num_chars) Text 為包含要提取字符的文本字符串;Start_num 為文本中要提取的第一個字符的位置。文本中第一個字符的start_num 為1 ，以此類推;Num_chars指定希望MID 從文本中返回字符的個數(shù)。
(2)實(shí)例分析
為了適應(yīng)上述情況，必須設(shè)計(jì)一個能夠適應(yīng)兩種身份編碼的性別計(jì)算公式，在D2 單元格中輸入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回車后即可
在單元格獲得該職工的性別，而后只要把公式復(fù)制到D3、D4等單元格，即可得到其他職工的性別。 為了便于大家了解上述公式的設(shè)計(jì)思路，下面簡單介紹一下它的工作原理：該公式由三個IF 函數(shù)構(gòu)成，其中“IF(MOD(MID(C2,15,1),2)=1,"男","女")”和“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作為第一個函數(shù)的參數(shù)。公式中“LEN(C2)=15”是一個邏輯判斷語句，LEN 函數(shù)提取C2 等單元格中的字符長度，如果該字符的長度等于15，
則執(zhí)行參數(shù)中的第一個IF 函數(shù)，否則就執(zhí)行第二個IF 函數(shù)。在參數(shù)“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。
MID 函數(shù)從C2 的指定位置(第15 位)提取1 個字符，而MOD 函數(shù)將該字符與2 相除，獲取兩者的.余數(shù)。如果兩者能夠除盡，說明提取出來的字符是0(否則就是1)。邏輯條件“MOD(MID(C2,15,1),2)=1”不成立，這時(shí)就會在D2 單元格中填入“女”，反之則會填入“男”。 如果LEN 函數(shù)提取的C2 等單元格中的字符長度不等于15， 則會執(zhí)行第2個IF函數(shù)。除了MID 函數(shù)從C2 的指定位置(第17 位，即倒數(shù)第2 位)提取1 個字符以外，其他運(yùn)算過程
與上面的介紹相同。
2.出生日期輸入
(1)函數(shù)分解
CONCATENATE 函數(shù)將幾個文本字符串合并為一個文本字符串。 語法：CONCATENATE(text1,text2,...) Text1,text2,...為1～30 個要合并成單個文本項(xiàng)的文本項(xiàng)。文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹�單元格的引用。
(2)實(shí)例分析
與上面的思路相同，我們可以在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 位的，從而調(diào)用相應(yīng)的計(jì)算語句。 對15 位的身份證號碼來說，左起第7 至12 個字符表示出生年、月、日，此時(shí)可以使用MID 函數(shù)從身份證號碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE 函數(shù)將提取出來的文字合并起來，就能得到對應(yīng)的出生年月日。公式中“19”是針對早期身份證號碼中存在2000 年問題設(shè)計(jì)的，它可以在計(jì)算出來的出生年份前加上“19”。對“18”位的身份證號碼的計(jì)算思路相同，只是它不存在2000 年問題，公式中不用給計(jì)算出來的出生年份前加上“19”。 注意：CONCATENATE 函數(shù)和MID 函數(shù)的操作對象均為文本，所以存放身份證號碼的單元格必須事先設(shè)為文本格式，然后再輸入身份證號。
3.職工信息查詢
Excel 提供的“記錄單”功能可以查詢記錄，如果要查詢?nèi)耸鹿芾砉ぷ鞅碇械哪硹l記錄，然后把它打印出來，必須采用下面介紹的方法。
(1)函數(shù)分解
INDEX 函數(shù)返回?cái)?shù)據(jù)清單或數(shù)組中的元素值，此元素由行序號和列序號的索引值給定。 INDEX 函數(shù)有兩種語法形式：數(shù)組和引用。數(shù)組形式通常返回?cái)?shù)值或數(shù)值數(shù)組，引用形式通常返回引用。當(dāng)函數(shù)INDEX 的第一個參數(shù)為數(shù)組常數(shù)時(shí)，使用數(shù)組形式。 語法1(數(shù)組形式)：INDEX(array,row_num,column_num) Array 為單元格區(qū)域或數(shù)組常量。如果數(shù)組只包含一行或一列，則相對應(yīng)的參數(shù)row_num 或column_num為可選。如果數(shù)組有多行和多列，但只使用row_num 或c olumn_num，函數(shù)INDEX 返回?cái)?shù)組中的整行或整列，且返回值也為數(shù)組;Row_num 為數(shù)組中某行的行序號，函數(shù)從該行返回?cái)?shù)值。如果省略row_num， 則必須有column_num;Column_num 為數(shù)組中某列的
列序號，函數(shù)從該列返回?cái)?shù)值。如果省略column_num，則必須有row_num。 語法2(引用形式)：INDEX(reference,row_num,column_num,area_num) Reference 表示對一個或多個單元格區(qū)域的引用。如果為引用輸入一個不連續(xù)的區(qū)域，必須用括號括起來。如果引用中的每個區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num 或
column_num 分別為可選項(xiàng);Row_num 引用中某行的行序號，函數(shù)從該行返回一個引用;Column_num引用中某列的列序號，函數(shù)從該列返回一個引用;Area_num 選擇引用中的一個區(qū)域，并返回該區(qū)域中row_num 和column_num 的交叉區(qū)域。選中或輸入的第一個區(qū)域序號為1，第二個為2，以此類推。如果省略area_num，函數(shù)INDEX 使用區(qū)域1。 MATCH 函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。 語法：MATCH(lookup_value,lookup_array,match_type) Lookup_value 為需要在數(shù)據(jù)表中查找的數(shù)值;Lookup_value 為需要在Look_array 中查找的數(shù)值;Match_type 為數(shù)字-1、0或1 。
(2)實(shí)例分析
如果上面的人事管理工作表放在Sheet1 中，為了防止因查詢操作而破壞它(必要時(shí)可以添加只讀保護(hù))，我們可以打開另外一個空白工作表Sheet2，把上一個數(shù)據(jù)清單中的列標(biāo)記復(fù)制到第一行。假如你要以“身份證號碼”作為查詢關(guān)鍵字，就要在C2 單元格中輸入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的參數(shù)“ SC S5”引用公式所在工作表中的C5 單元格(也可以選用其他單元格)，執(zhí)行查詢時(shí)要在其中輸入查詢關(guān)鍵字，也就是待查詢記錄中的身份證號碼。參數(shù)“Sheet1!C2:C600”設(shè)定INDEX 函數(shù)的查詢范圍，引用的是數(shù)
據(jù)清單C 列的所有單元格。MATCH函數(shù)中的參數(shù)“0”指定它查找“Sheet1! SC S2: SC S600”區(qū)域中等于 SC S5的第一個值，并且引用的區(qū)域“Sheet1! SC S2: SC S600,0”可以按任意順序排列。上面的公式執(zhí)行數(shù)據(jù)查詢操作時(shí)，首先由MATCH 函數(shù)在“Sheet1! SC S2: SC S600” 區(qū)域搜索，找到“ SC S5” 單元格中的數(shù)據(jù)在引用區(qū)域中的位置(自上而下第幾個單元格)，從而得知待查詢數(shù)據(jù)在引用區(qū)域中的第幾行。 接下來INDEX 函數(shù)根據(jù)MATCH 函數(shù)給出的行號，返回“Sheet1!C2:C600”區(qū)域中對應(yīng)行數(shù)單元格中的數(shù)據(jù)。假設(shè)其中待查詢的“身份證號碼”是“3234567896”，它位于“Sheet1! SC S2: SC S600”區(qū)域的第三行，MATCH函數(shù)就會返回“3”。接著INDEX 函數(shù)返回“Sheet1!C2:C600”區(qū)域中行數(shù)是“3”的數(shù)據(jù)，也就是“3234567896”。 然后，我們將光標(biāo)放到C2 單元格的填充柄上，當(dāng)十字光標(biāo)出現(xiàn)以后向右拖動，從而把C2 中的公式復(fù)制到D2、E2 等單元格(然后再向左拖動，以便把公式復(fù)制到B2、A2單元格)，這樣就可以獲得與該身份證號對應(yīng)的性別、籍貫等數(shù)據(jù)。 注意：公式復(fù)制到D2、E2等單元格以后，INDEX函數(shù)引用的區(qū)域就會發(fā)生變化，由C2:C600 變成D2 :D600、E2:E600等等。但是MATCH 函數(shù)返回的(相對)行號仍然由查詢關(guān)鍵字給出，此后INDEX 函數(shù)就會根據(jù)MATCH 函數(shù)返回的行號從引用區(qū)域中找到數(shù)據(jù)。 在Sheet2 工作表中進(jìn)行查詢時(shí)只要在查詢輸入單元格中輸入關(guān)鍵字，回車后即可在工作表的C2 單元格內(nèi)看到查詢出來的身份證號碼。如果輸入的身份證號碼關(guān)鍵字不存在或輸入錯誤，則單元格內(nèi)會顯示“#N/A”字樣。
4.職工性別統(tǒng)計(jì)
(1)函數(shù)分解
COUNTIF 函數(shù)計(jì)算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF(range,criteria) Range 為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域;Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。
(2)實(shí)例分析
假設(shè)上面使用的人事管理工作表中有599 條記錄，統(tǒng)計(jì)職工中男性和女性人數(shù)的方法是：選中單元格D601(或其他用不上的空白單元格)，統(tǒng)計(jì)男性職工人數(shù)可以在其中輸入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接著選中單元格D602，在其中輸入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回車后即可得到“男399 人”、“女200 人”。
上式中D2:D600 是對“性別”列數(shù)據(jù)區(qū)域的引用，實(shí)際使用時(shí)必須根據(jù)數(shù)據(jù)個數(shù)進(jìn)行修改。“男”或“女”則是條件判斷語句，用來判斷區(qū)域中符合條件的數(shù)據(jù)然后進(jìn)行統(tǒng)計(jì)。“&” 則是字符連接符，可以在統(tǒng)計(jì)結(jié)果的前后加上“男”、“人”字樣，使其更具有可讀性。
5.年齡統(tǒng)計(jì)
在人事管理工作中，統(tǒng)計(jì)分布在各個年齡段中的職工人數(shù)也是一項(xiàng)經(jīng)常性工作。假設(shè)上面介紹的工作表的E2:E600 單元格存放職工的工齡，我們要以5 年為一段分別統(tǒng)計(jì)年齡小于20 歲、20 至25 歲之間，一直到55 至60 歲之間的年齡段人數(shù)，可以采用下面的操作方法。
(1)函數(shù)分解
FREQUENCY 函數(shù)以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。 語法：FREQUENCY(data_array,bins_array)
Data_array 為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計(jì)算頻率。如果data_array 中不包含任何數(shù)值，函數(shù)FREQUENCY 返
回零數(shù)組;Bins_array為間隔的數(shù)組或?qū)﹂g隔的引用，該間隔用于對data_array 中的數(shù)值進(jìn)行分組。如果bins_array
中不包含任何數(shù)值，函數(shù)FREQUENCY 返回data_array 中元素的個數(shù)。
(2)實(shí)例分析
首先在工作表中找到空白的I 列(或其他列)，自I2 單元格開始依次輸入20、25、30 、35、40...60， 分別表示統(tǒng)計(jì)年齡小于20、20 至25 之間、25 至30 之間等的人數(shù)。然后在該列旁邊選中相同個數(shù)的單元格，例如J2：J10 準(zhǔn)備存放各年齡段的統(tǒng)計(jì)結(jié)果。然后在編輯欄輸入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 組合鍵即可在選中單元格中看到計(jì)算結(jié)果。其中位于J2 單元格中的結(jié)果表示年齡小于20 歲的職工人數(shù)，J3單元格中的數(shù)值表示年齡在20 至25 之間的職工人數(shù)等。
6.名次值統(tǒng)計(jì)
在工資統(tǒng)計(jì)和成績統(tǒng)計(jì)等場合，往往需要知道某一名次(如工資總額第二、第三)的員工的工資是多少。這種統(tǒng)計(jì)的操
作方法如下。
(1)函數(shù)分解
LARGE 函數(shù)返回?cái)?shù)據(jù)集中第K 個最大值。使用此函數(shù)可以根據(jù)相對標(biāo)準(zhǔn)來選擇數(shù)值。 語法：LARGE(array,k)
Array 為需要從中選擇第K 個最大值的數(shù)組或數(shù)據(jù)區(qū)域; K 為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置(從大到小排)。SMALL 函數(shù)返回?cái)?shù)據(jù)集中第K 個最小值。使用此函數(shù)可以返回?cái)?shù)據(jù)集中特定位置上的數(shù)值。法：SMALL(array,k) Array 為需要找到第K 個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域;K為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從小到大)。
(2)實(shí)例分析
假設(shè)C2:C688 區(qū)域存放著員工的工資，首先在D 列選取空白單元格D3，在其中輸入公式“=LARGE(C2:C688,D2)”。其中D2 作為輸入名次變量的單元格，如果你在其中輸入3，公式就可以返回C2:C688 區(qū)域中第三大的數(shù)值。
如果我們把上述公式修改為“=SMALL(C2:C688,D1)”，然后在D1 單元格中輸入6，就可以獲得C2:C688 區(qū)域倒數(shù)第六(小)的數(shù)值。 為方便起見，你可以給C2:C688 區(qū)域定義一個名稱“職工工資”。此后可以把上述公式修改為“=LARGE(職工工資,D2)”或“=SMALL(職工工資,D1)”。
7.位次閾值統(tǒng)計(jì)
與上例相似，在工資統(tǒng)計(jì)和成績統(tǒng)計(jì)等場合，需要知道排名達(dá)到總體的前1/3 的工資總額或分?jǐn)?shù)(稱為“閾值”)是多
少。這種統(tǒng)計(jì)的操作方法如下：
(1)函數(shù)分解
PERCENTILE 函數(shù)返回區(qū)域中數(shù)值的第K 個百分點(diǎn)的值�？梢允褂么撕瘮�(shù)來建立接受閾值。 語法：PERCENTILE(array,k) Array 為定義相對位置的數(shù)組或數(shù)據(jù)區(qū)域;K為0 到1 之間的百分點(diǎn)值，包含0和1。
(2)實(shí)例分析
假設(shè)C2:C200 區(qū)域存放著學(xué)生的考試成績，首先在D列選取空白單元格D3，在其中輸入公式 “=PERCENTILE(C2:C200,D2)”。其中D2 作為輸入百分點(diǎn)變量的單元格，如果你在其中輸入0.33，公式就可以返回名次達(dá)到前1/3 所需要的成績。
【excel如何進(jìn)行信息統(tǒng)計(jì)】相關(guān)文章：