人事信息數據統計分析課件

Excel在商務中的應用任課教師：李磊Excel在商務中的應用任課教師：李磊第4章人事信息數據統計分析本章要點：4.1人事信息數據表4.2人事數據的條件求和計數4.3用數據透視表和數據透視圖分析員工學歷水平4.4人事數據表的兩表數據核對4.5員工人事信息數據查詢表（員工信息卡片）4.6統計不同年齡段員工信息4.7人力資源月報動態(tài)圖表第4章人事信息數據統計分析本章要點：第4章人事信息數據統計分析第4章人事信息數據統計分析

4.1人事信息數據表

4.1.1創(chuàng)建人事信息數據表

4.1.2利用數據有效性防止工號重復輸入

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格

4.2人事數據的條件求和計數

4.2.1人事數據的單字段單條件求和計數

4.2.2人事數據的單字段多條件求和計數

4.2.3人事數據的多字段多條件求和計數

4.2.4

DSUM數據庫函數的應用

4.3用數據透視表和數據透視圖分析員工學歷水平

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

4.4人事數據表的兩表數據核對

4.4.1利用“條件格式”比照核對兩表格數據

4.4.2利用“數據透視表”比照核對兩表格數據

4.4.3利用VLOOKUP函數比照核對兩表數據

4.5員工人事信息數據查詢表（員工信息卡片）

4.5.1VLOOKUP函數查詢人員信息

4.5.2美化和打印表格（員工信息卡片）

4.6統計不同年齡段員工信息

4.6.1應用COUNTIF函數統計分段信息

4.6.2使用FREQUENCY數組公式法統計分段信息

4.7人力資源月報動態(tài)圖表

4.7.1創(chuàng)建數據源工作表

4.7.2數據分析匯總

4.7.3建立窗體控件

4.7.4對數據自動降序排列

4.7.5定義動態(tài)數據區(qū)域名稱

4.7.6繪制柏拉圖

4.7.7美化圖表區(qū)

第4章人事信息數據統計分析第4章人事信息數據統計分析

4.1人事信息數據表案例背景企業(yè)里由于人員較多且流動變化大，因而人力資源部應及時做好人事數據的整理、匯總分析等工作，并且這些數據常常也是企業(yè)做各項決策的參考依據，因此處理好人事數據的整理工作意義重大。人事信息數據表是企業(yè)進行人事信息管理的基礎和依據，因此，人事信息管理表格一定要科學、準確、詳細，并且有利于查找、利用，這樣才能真正輔助企業(yè)管理者進行人事信息管理工作。

4.1.1創(chuàng)建人事信息數據表

4.1.2利用數據有效性防止工號重復輸入

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格4.1人事信息數據表案例背景4.1人事信息數據表最終效果展示

某某有限公司員工人事信息表序號工號姓名隸屬部門學歷身份證號生日性別計算年齡（-年-月-日）年齡職稱現任職務聯系電話居住地址168胡生產部本科1201051976101200341976-10-12男35年6個月26天35年工程師部長12345678杭州市某區(qū)某路1號214徐生產部?？?201021969080331681969-8-3女42年9個月4天42年助工科員12345679杭州市某區(qū)某路2號355楊生產部碩士1201021947100111701947-10-1男64年7個月6天64年無科員12345680杭州市某區(qū)某路3號4106劉生產部專科1201101959031418291959-3-14女53年1個月24天53年工程師科員12345681杭州市某區(qū)某路4號5107李銷售部本科1201026811151711968-11-15男43年5個月23天43年工程師部長12345682杭州市某區(qū)某路5號6114林銷售部本科1201021982022811641982-2-28女30年2個月10天30年助工科員12345683杭州市某區(qū)某路6號7118童行政部?？?201107505153921975-5-15女36年11個月23天36年助工部長12345684杭州市某區(qū)某路7號869王行政部本科1201051983032530361983-3-25男29年1個月13天29年無科員12345685杭州市某區(qū)某路8號9236李生產部本科1201051983032530361983-3-25男29年1個月13天29年工程師部長12345686杭州市某區(qū)某路9號10237趙生產部?？?201021963012011691963-1-20女49年3個月18天49年工程師科員12345687杭州市某區(qū)某路10號11238劉生產部高中1201017702221511977-2-22男35年2個月16天35年工程師科員12345688杭州市某區(qū)某路11號12239馬生產部高中1201021974050707261974-5-7女38年0個月0天38年工程師科員12345689杭州市某區(qū)某路12號13240胡生產部本科1201011978050207251978-5-2女34年0個月5天34年助工科員12345690杭州市某區(qū)某路13號14241林生產部本科1201051972011933181972-1-19男40年3個月19天40年助工科員12345691杭州市某區(qū)某路14號15242童生產部碩士1201021958122316291958-12-23女53年4個月15天53年助工科員12345692杭州市某區(qū)某路15號4.1人事信息數據表最終效果展示

某某有限公司員工人事信息創(chuàng)建人事信息數據表人事信息數據表包括項目有：姓名、性別、年齡、身份證號（或社會保障號）、學歷、現任職務、聯系電話、E-mail和居住地等有效信息。其中性別、年齡、出生日期等項目均不需要手工錄入，利用函數計算即可。創(chuàng)建人事信息數據表人事信息數據表包括項目有：姓名、性別、年齡身份證號中提取生日、性別等有效信息建立人事信息數據表時，性別和出生日期兩個項目可以不必手工錄入，而是利用函數通過身份證號碼來自動提取即可。身份證號中提取生日、性別等有效信息建立人事信息數據表時，性別分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關的個人信息。15位身份證號碼：第7、8位為出生年份(兩位數)，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數為男，偶數為女。例如:某員工的身份證號碼(15位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年份(四位數)，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數為男，偶數為女，第18位為校驗位。分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年4.1人事信息數據表關鍵技術點（方法1）

要實現本例中的功能，讀者應當掌握以下Excel技術點。DATEIF函數RIGHT函數LEFT函數MID函數LEN函數4.1人事信息數據表關鍵技術點（方法1）

要實現本例中編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2編號姓名身份證號碼出生日期A001陳雙雙342701ABCD1編號姓名身份證號碼出生日期2A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+0*2),"#-00-00")=--TEXT(MID(c2,7,6),"#-00-00")=--TEXT(800913,"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙ABCD1編號姓名身份證號碼出生日期2A001陳雙雙3427011980091388411980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+1*2),"#-00-00")=--TEXT(MID(c2,7,8),"#-00-00")=--TEXT(19800913,"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙=IF(c2="","",IF(MOD(RIGHT(LEFT(c2,17)),2),"男","女"))分析:left(C17,17)：截取單元格C17的前17位數right(left(C17,17))其實省略了一個參數，完整的應該是：right(left(C17,17),1)意思是：截取left()所得的結果的最后一位數。MOD(RIGHT(LEFT(C17,17)),2)是以上面的結果除以2的余數最后的if()函數是用來判斷的，如果余數為有余數則返回“男”，沒有余數則返回“女”。其實該公式被簡化了，完整的應該是：=IF(MOD(RIGHT(LEFT(C17,17),1),2)<>0,"男","女")從身份證中提取性別ABCD1編號姓名身份證號碼出生日期2A001陳雙雙3427018009138841980年09月13日=IF(c2="","",IF(MOD(RIGHT(LERIGHT(LEFT(c2,17))RIGHT(34270119760213857,1)-->7RIGHT(LEFT(c3,17))RIGHT(342701820213857,1)7從身份證中提取性別ABC1編號姓名身份證號碼2A019吳仕3427011976021385783A020孫國成342701820213857RIGHT(LEFT(c2,17))從身份證中提取性別ABCLEFT函數用途：得到字符串左部指定個數的字符。語法形式：LEFT(string,n)參數：string指定要提取子串的字符串。

n指定子串長度返回值String。說明：函數執(zhí)行成功時返回string字符串左邊n個字符，發(fā)生錯誤時返回空字符串（""）。如果任何參數的值為NULL，Left()函數返回NULL。如果n的值大于string字符串的長度，那么Left()函數返回整個string字符串，但并不增加其它字符。LEFT函數用途：得到字符串左部指定個數的字符。LEFT函數實例：如果A1=安徽省蚌埠市固鎮(zhèn)縣楊廟鄉(xiāng)，則公式“=LEFT(A1,FIND("省",A1))”返回安徽省。

DimAnyString,MyStr

AnyString="HelloWorld"'定義字符串。

MyStr=Left(AnyString,1)'返回"H"。

MyStr=Left(AnyString,7)'返回"HelloW"。

MyStr=Left(AnyString,10)'返回"HelloWorl"。LEFT函數實例：4.1人事信息數據表關鍵技術點（方法2）

要實現本例中的功能，讀者應當掌握以下Excel技術點。IF（）函數CHOOSE（）函數MOD（）函數LEN（）函數RIGHT（）函數MID（）函數DATE（）函數4.1人事信息數據表關鍵技術點（方法2）

要實現本例中IF函數的應用IF函數是根據指定的條件來判斷其“真”（TRUE）、“假”（FALSE），從而返回其相對應得內容。語法形式：IF(logical_test,value_if_true,value_if_false)其中：logical_test邏輯判斷表達式value_if_true當判斷條件為邏輯“真”（TRUE）時，顯示該處給定的內容。如忽略，返回TRUE。value_if_false當判斷條件為邏輯“假”（FALSE）時，顯示該處給定的內容。如忽略，返回FALSE。IF(0=1,"男","女")結果為女IF函數的應用IF函數是根據指定的條件來判斷其“真”（TRULEN函數LEN函數用于返回字符串的長度。語法形式：LEN(text)其中，text表示要查找其長度的字符串文本。ABC1員工姓名身份證號碼位數2趙榮378501601517897153王勇3425076508053247164吳小平364501197804058246185陳果342501199010182233819=LEN(B2)LEN函數LEN函數用于返回字符串的長度。ABC1員工姓名身練習1、判斷身份證號碼的長度，顯示不同信息身份證號碼的長度為15位的，顯示“老身份證號長度”身份證號碼的長度為18位的，顯示“新身份證號長度”ABCD1員工姓名身份證號碼位數結果2趙榮37850160151789715老身份證號長度3吳小平36450119780405824618新身份證號長度練習1、判斷身份證號碼的長度，顯示不同信息ABCD1員工姓名MID函數MID函數用于從文本字符串中提取指定位置開始的特定字符。語法格式：

MID（text，start_num，num_chars）text：要提取字符的文本字符串start_num：從文本字符串中要提取第一個字符的開始位置num_chars：提取字符的個數=MID(A3,4,8)ABC1文本字符串提取條件提取結果201062791976從開始位置提取3個字符010301062791976從第4個字符位置提取8個字符62791976MID函數MID函數用于從文本字符串中提取指定位置開始的特定練習獲取身份證號碼中的“年份”數值員工姓名身份證號碼位數結果完整形式趙榮378501601517897從第7位提取2個字符601960年吳小第7位提取4個字符19781978年練習獲取身份證號碼中的“年份”數值員工姓名身份證號碼位數MOD函數MOD函數用于求兩個數值相除后的余數，其結果的正負號與除數相同。語法格式：MOD(number,divisor)Number:指定的被除數數值Diisor:指定的除數數值,并且不能為0值ABC1被除數除數結果2113231543=MOD(A2,B2)=MOD(A3,B3)MOD函數MOD函數用于求兩個數值相除后的余數，其結果的正負練習通過獲取身份證號碼中的“性別”的數值來判斷“性別”15位身份證號碼：第15位代表性別，奇數為男，偶數為女。18位身份證號碼：第17位代表性別，奇數為男，偶數為女。員工姓名身份證號碼位數“性別”數值奇偶性判斷性別趙榮378501601517897從第15位提取1個字符71男吳小第17位提取1個字符40女練習通過獲取身份證號碼中的“性別”的數值來判斷“性別”員工姓RIGHT函數的應用作用：從字符串右端取指定個數字符。語法形式：RIGHT(text,num_chars)其中：Text是包含要提取字符的文本字符串，可以直接輸入含有目標文字的單元格名稱。Num_chars指定希望RIGHT提取的字符數。它

必須大于或等于0。如果num_chars大于文本長度，則RIGHT返回所有文本。如果忽略num_chars，則假定其為1。例：字符串公式要求結果HelloWorld

=RIGHT(A3,5)最后5個字符World寧夏理工學院經濟管理系

=RIGHT(A4,5)最后5個字符經濟管理系RIGHT函數的應用作用：從字符串右端取指定個數字符。字符DATE函數的應用作用：返回代表特定日期的序列號。如果在輸入函數前，單元格的格式為“常規(guī)”，則結果將設為日期格式。

語法形式：DATE(year,month,day)

其中：Year參數year可以為一到四位數字。MicrosoftExcel將根據所使用的日期系統來解釋year參數。默認情況下，MicrosoftExcelforWindows將使用1900日期系統，而MicrosoftExcelforMacintosh將使用1904日期系統。Month代表一年中從1月到12月（一月到十二月）各月的正整數或負整數。

Day代表一月中從1日到31日各天的正整數或負整數。例如A5為2010，B5為3，C5為5DATE(A5,B5,C5)結果：表示轉為日期格式的2010-3-5DATE函數的應用作用：返回代表特定日期的序列號。如果在輸入編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2))結果顯示形式：19XX-XX-XXMID函數：從文本字符串中提取指定位置開始的特定字符MID(C3,7,2),分析：從C3單元格中數值的第7位開始取2位，取得出生日期的年份信息。編號姓名身份證號碼出生日期A001陳雙雙342701=IF(LEN(C4)=15,DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2)),IF(LEN(C4)=18,DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)),""))編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=IF(LEN(C4)=15,編號姓名身份證號碼出生=IF(LEN(C3)=15,IF(MOD(MID(C3,15,1),2)=1,"男","女"),IF(MOD(MID(C3,17,1),2)=1,"男","女"))分析:MID(C3,15,1)從C3單元格中數值的第15位開始取1位（取4）MOD(4,2)返回4除以2的余數，結果為0IF(0=1,"男","女")結果為女從身份證中提取性別編號姓名身份證號碼出生日期HSR1003陳雙雙3427018009138841980年09月13日=IF(LEN(C3)=15,從身份證中提取性別編號應用DATEDIF函數計算員工年齡編制提取員工年齡公式（格式為“多少年多少月多少天”）=DATEDIF(G3,TODAY(),"y")&"年"&DATEDIF(G3,TODAY(),"ym")&"個月"&DATEDIF(G3,TODAY(),"md")&"天"編制提取員工年齡公式（格式為“多少年”）=DATEDIF(G3,TODAY(),"y")&"年"應用DATEDIF函數計算員工年齡編制提取員工年齡公式（格式補充：CHOOSE函數的應用作用：從參數列表中選擇并返回一個值。函數CHOOSE可以使用index_num返回數值參數清單中的數值。使用函數CHOOSE可以基于索引號返回多達29個待選數值中的任一數值。語法形式：CHOOSE(index_num,value1,value2,...)其中：Index_num用以指明待選參數序號的參數值。Index_num必須為1到29之間的數字、或者是包含數字1到29的公式或單元格引用。Value1,value2,...為1到29個數值參數，函數CHOOSE基于index_num，從中選擇一個數值或執(zhí)行相應的操作。參數可以為數字、單元格引用，已定義的名稱、公式、函數或文本。例如公式“=CHOOSE(2，”電腦“，”愛好者“)返回“愛好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”與公式“=SUM(A1:A30)”等價(因為CHOOSE(3，A10，A20，A30)返回A30)。補充：CHOOSE函數的應用作用：從參數列表中選擇并返回一=IF(LEN(C4)=15,DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2)),IF(LEN(C4)=18,DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)),""))編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=IF(LEN(C4)=15,編號姓名身份證號碼出生=IF(C4="","",CHOOSE(MOD(IF(LEN(C4)=15,RIGHT(C4,1),IF(LEN(C4)=18,MID(C4,17,1),"")),2)+1,"女","男"))分析:MID(C3,17,1)從C4單元格中數值的第15位開始取1位（取4）RIGHT(C4,1)取C4單元格中數值的1位（取4）

IF(1,"女","男")結果為女從身份證中提取性別編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日=IF(C4="","",從身份證中提取性別編補充：CONCATENATE函數CONCATENATE函數用于將多個文本字符串合并為一個文本字符串。語法格式：

CONCATENATE（text1，text12text13，…）其中，text1，text12text13，…表示將要合并成單個文本項的文本項（這些文本項可以為文本字符串、數字或對單個單元格的引用）。ABC1姓名姓名2趙榮趙榮3王勇王勇4吳小平吳小平5陳果陳果=CONCATENATE(A5,B5)補充：CONCATENATE函數CONCATENATE函數用4.2人事數據的條件求和計數案例背景

Excel的條件求和計數在工作中的應用很廣泛。人力資源管理者需要經常對員工信息進行統計分析，比如需要獲知人事數據表中學歷為本科的員工人數有多少，又比如查詢某個年齡段的員工人數等，這些都可以通過活用函數條件求和計數加以解決，進而提高工作的效率。

4.2.1人事數據的單字段單條件求和計數

4.2.2人事數據的單字段多條件求和計數

4.2.3人事數據的多字段多條件求和計數

4.2.4

DSUM數據庫函數的應用

4.2人事數據的條件求和計數案例背景4.2人事數據的條件求和計數最終效果展示

序號姓名隸屬部門學歷性別年齡獎金1胡生產部本科男304002徐生產部?？婆?72003楊生產部碩士男592004劉生產部?？婆?82005李銷售部本科男382006林銷售部本科女252007童行政部?？婆?22008王行政部本科男244009李生產部本科男2440010趙生產部專科女4440011劉生產部博士男3040012馬生產部博士女3340013胡生產部本科女2940014林生產部本科男3530015童生產部碩士女483004.2人事數據的條件求和計數最終效果展示

序號姓名隸屬部門4.2人事數據的條件求和計數關鍵技術點

要實現本例中的功能，讀者應當掌握以下Excel技術點。COUNTIF函數SUMPRODUCT函數DSUM函數4.2人事數據的條件求和計數關鍵技術點

要實現本例中的人事數據的單字段單條件求和計數例：統計所有本科學歷人數例：統計年齡大于等于40歲人數方法：使用COUNTIF函數統計數據人事數據的單字段單條件求和計數例：統計所有本科學歷人數COUNTIF函數用于計算單元格區(qū)域中滿足給定條件的單元格的個數。語法形式：COUNTIF（range,criteria)COUNTIF（數據區(qū)域,條件表達式)COUNTIF函數用于計算單元格區(qū)域中滿足給定條件的單元格的COUNTIF函數舉例ABC1姓名性別職務2劉勇男經理3李南男職員4陳雙雙女經理5葉小來男職員6林佳女經理7彭力男主管8范琳琳女職員9易呈亮男經理10黃海燕女職員11男性員工人數512女性員工人數413年齡在30歲以下的人數3=COUNTIF(B2:B10,"男")=COUNTIF(B2:B10,"女")COUNTIF函數舉例ABC1姓名性別職務2劉勇男經理3李南人事數據的單字段多條件求和計數例：統計年齡在30歲至40歲的人數方法1：=COUNTIF(F2:F18,">=30")-COUNTIF(F2:F18,">40")方法2：=SUM((F2:F18>=30)*(F2:F18<=40))方法3：=SUMPRODUCT((F2:F18>=30)*(F2:F18<=40))人事數據的單字段多條件求和計數例：統計年齡在30歲至40歲的SUM函數舉例ABC1姓名性別年齡2劉勇男183李南男264陳雙雙女255葉小來男31=SUM((C2:C5>=20)*(C2:C5<=30))20歲至30歲(0,1,1,1)*(1,1,1,0)(0,1,1,0)=SUM(0,1,1,0)例：統計年齡在20歲至30歲的人數SUM函數舉例ABC1姓名性別年齡2劉勇男183李南男264SUMPRODUCT函數作用：計算工作表內多列中對應值相乘之后的和，即乘積之和。其語法為：SUMPRODUCT(array1,array2,array3,…)其中，Array1,array2,array3,…為2到30個數組，其相應元素需要進行相乘并求和。數組參數必須具有相同的維數，否則，函數SUMPRODUCT將返回錯誤值#VALUE!。該函數將非數值型的數組元素作為0處理。SUMPRODUCT函數作用：計算工作表內多列中對應值相乘之人事數據的多字段多條件求和計數例：統計性別為男性且年齡在30歲以上的人數方法：

=SUMPRODUCT(($E$2:$E$18=I11)*($F$2:$F$18>I12))人事數據的多字段多條件求和計數例：統計性別為男性且年齡在30DSUM數據庫函數的應用例：統計生產部學歷為本科的員工的獎金合計方法：=DSUM(A1:G18,I19,I15:J16)DSUM數據庫函數的應用例：統計生產部學歷為本科的員工的獎金DSUM函數DSUM函數，按條件求和語法形式:DSUM(database,field,criteria)其中：Database是區(qū)域field是列數criteria是條件DSUM函數DSUM函數，按條件求和DSUM函數例如：ABC1產品名稱產品單價（單位：元）總銷售提成2A111003B100104C200305D20406E45507F888089條件產品單價（單位：元）結果10>50120單價大于50元的產品的銷售提成總額=DSUM(A1:C7,3,B9:B10)DSUM函數例如：ABC1產品名稱產品單價（單位：元）總銷4.3用數據透視表和數據透視圖分析員工學歷水平案例背景

Excel的圖表分析功能十分強大，其中的數據透視圖更是聚集了強大的智能。本案例以人事基礎數據圖為例，介紹如何在紛雜的數據中提取所需要的數據，同時制作合乎需求的數據透視表以及數據透視圖。

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

4.3用數據透視表和數據透視圖分析員工學歷水平案例背景4.3用數據透視表和數據透視圖分析員工學歷水平最終效果展示員工學歷透視表員工學歷透視圖

計數項:學歷學歷

隸屬部門本科博士碩士?？瓶傆嬌a部622313銷售部22行政部112總計9224174.3用數據透視表和數據透視圖分析員工學歷水平最終效果展示4.3用數據透視表和數據透視圖分析員工學歷水平關鍵技術點

要實現本例中的功能，讀者應當掌握以下Excel技術點。數據透視表的應用數據透視圖的應用4.3用數據透視表和數據透視圖分析員工學歷水平關鍵技術點

4.3.1編制員工學歷透視表案例背景

Excel的圖表分析功能十分強大，其中的數據透視圖更是聚集了強大的智能。本案例以人事基礎數據圖為例，介紹如何在紛雜的數據中提取所需要的數據，同時制作合乎需求的數據透視表以及數據透視圖。

4.3.2制作員工學歷透視圖

4.3.1編制員工學歷透視表案例背景4.3.2制作員工學歷透視圖案例背景

Excel的圖表分析功能十分強大，其中的數據透視圖更是聚集了強大的智能。本案例以人事基礎數據圖為例，介紹如何在紛雜的數據中提取所需要的數據，同時制作合乎需求的數據透視表以及數據透視圖。

4.3.2制作員工學歷透視圖案例背景4.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表1、數據透視表：一種交互的、交叉制表的Excel報表，用于對多種來源（包括Excel的外部數據）的數據（如數據庫記錄）進行匯總和分析。正源于其交互式動態(tài)匯總報表的特性，數據透視表即可快速合并和比較大量數據，也可以創(chuàng)建頻率分布和多個不同數據維的交叉制表。也就是說，創(chuàng)建一個數據透視表后，可以旋轉其行和列以看到源數據的不同匯總結果，而且可以顯示不同頁面的篩選數據，即根據需要顯示區(qū)域中的明細數據。

4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表2、數據透視表的作用:能夠改變數據表的行、列布局;能夠快速匯總大量數據;能夠基于原數據表創(chuàng)建數據分組，并對分組進行匯總統計。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表包括：分頁字段、數據項、行字段、列字段、項目等元素數據項列字段頁字段行字段4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表解釋:行：拖放到行中的數據字段中的每個數據項將占據透視表的一行。列：拖放到列中的數據字段中的每個數據項將占據透視表的一列。行和列確定一個二維表格.頁：拖放在頁中的字段，Excel將按該字段的數據項對透視表進行分頁。數據：進行計數或匯總的字段名稱。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數據透視表的建立點擊菜單命令“數據→數據透視表和數據透視圖”，打開“數據透視表和數據透視圖向導”對話框。第一步，如下圖所示，選擇“MicrosoftExcel數據列表或數據庫”及下面的“數據透視表”單選項。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數據透視表的建立第二步，如下圖所示，在“選定區(qū)域”輸入全部數據所在的單元格區(qū)域，或者點擊輸入框右側的“壓縮對話”按鈕，在工作表中用鼠標選定數據區(qū)域。第三步，在對話框中選定“新建工作表”單選項，以便將創(chuàng)建的數據透視表放到一個新的工作表中，再點擊“完成”按鈕，如下圖所示。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數據透視表的建立這樣，就可以建立一個空的數據透視表，并同時顯示“數據透視表”工具欄和“數據透視表字段列表”對話框，如圖5所示。

4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表4、利用數據透視表得到需要的結果

（1）統計所有學生各學科成績的匯總數據（2）查看所有學生“新聞學”成績的平均值”,平均值保留兩位小數位數（3）查看男生“新聞學”成績的平均值”,平均值保留兩位小數位數（4）查看“工商1010101班男生的“新聞學”成績的平均值”,平均值保留兩位小數位數（5）將第（2）題“行”與“列”字段互換，并設置“自動套用格式”樣式4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表5、修改數據透視表顯示或隱藏行、列中的數據項在行或列字段的下拉列表條中選擇或取消復選標志。重新組織數據透視表行、列置換：鼠標移至行字段上按住拖動到列字段上在字段內移動個別項：鼠標按在要移動字段邊框線上拖動到指定位置放開。在原有行列字段上增加或減少字段增加：單擊數據透視表,在數據透視表工具欄上將所需字段拖入行或列的字段處.減少：選擇數據透視表行或列的字段名，拖到表外4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表6、修改數據透視表的概要函數

概要函數：即數據透視表中使用的透視函數。如：求和、計數、平均、百分比、最大值、標準偏差等。操作方法：選擇數據透視表中數據字段；在對象菜單或數據透視表工具欄中選擇[字段設置]；在數據透視表對話框的匯總方式列表中選擇所需函數。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平例：數據列表是以列表形式存在的數據表格，它是一維的表格。而在實際工作中，用戶的數據往往是以二維表格的形式存在的，如下圖所示：純牛奶乳酸飲料酸牛奶奶茶奶片大林店93165784837金井店12925224935179和平路店5167382114258新陽路店260738220554解放路店73358390113285延安路店39257248330186四橋店3601995230139古尖店48142165136105東單店5320233246664.3用數據透視表和數據透視圖分析員工學歷水平例：純牛奶乳4.3用數據透視表和數據透視圖分析員工學歷水平對于這樣的數據表，用戶無法以它為數據源創(chuàng)建理想的數據透視表。只有把二維的數據表格轉換為如下圖所示的數據列表，才能成為數據透視表的數據源。能作為數據透視表數據源的一維數據列表店鋪商品銷量大林店純牛奶93大林店乳酸飲料165大林店酸牛奶78大林店奶茶48大林店奶片37金井店純牛奶129金井店乳酸飲料252金井店酸牛奶249金井店奶茶35金井店奶片179和平路店純牛奶5和平路店乳酸飲料167（1）各店鋪商品銷量求和統計（2）各店鋪商品中銷量最大的商品銷量4.3用數據透視表和數據透視圖分析員工學歷水平對于這樣的數4.3用數據透視表和數據透視圖分析員工學歷水平4.3.1編制員工學歷透視表7、刪除數據透視表的方法利用數據透視表工具欄的“數據透視表”按鈕→“選定”→“整張表格”，再利用“編輯”→“清除”→“全部”4.3用數據透視表和數據透視圖分析員工學歷水平4.3.14.3用數據透視表和數據透視圖分析員工學歷水平4.3.2制作員工學歷透視圖

數據透視表雖然具有可以準確計算和分析數據的優(yōu)點，但是通常數據透視表的數據源較大，數據量非常多，數據排列也相當復雜，這時就可以利用數據透視圖來更加直觀地分析數據。數據透視圖以圖形形式表示數據表中的數據，和數據透視表一樣，可以更改數據透視圖的布局和顯示的數據。與數據透視表相比，數據透視圖展示數據之間關系的方式更加可視化、更易于理解。數據透視圖是由系列、分類軸、坐標軸、數據標志、頁字段、數據字段、頁和分類字段等元素組成。兩種創(chuàng)建方式：直接創(chuàng)建數據透視圖和利用數據透視表創(chuàng)建數據透視圖建立數據透視圖表的方法單擊數據透視表中的非空單元格，選擇“插入”菜單的圖表命令，或單擊常用工具欄中的圖表工具，Excel會自動在當前工作簿中插入一個新的數據透視圖。4.3用數據透視表和數據透視圖分析員工學歷水平4.3.24.4人事數據表的兩表數據核對案例背景在實際工作中人力資源部經常需要核對某些數據，除了核對工資外，壞需要核對員工身份證號碼、職工姓名、銀行賬號等信息。這些工作很單調且麻煩。若被核對的字符串簡單，直接進行人工處理還是可以的；一旦字符很多，且排列順序不一，這是人工查找起來就顯得比較麻煩。

借助Excel提供的條件格式或者數據透視表功能，復雜的比對難題就可以迎刃而解。此外人力資源工作者利用相關函數，同樣也能完成該項工作。

4.4.1利用“條件格式”比照核對兩表格數據

4.4.2利用“數據透視表”比照核對兩表格數據

4.4.3利用VLOOKUP函數比照核對兩表數據4.4人事數據表的兩表數據核對案例背景4.4人事數據表的兩表數據核對最終效果展示使用條件格式對比使用VLOOKUP函數對比使用數據透視表對比

4.4人事數據表的兩表數據核對最終效果展示4.4人事數據表的兩表數據核對關鍵技術點

要實現本例中的功能，讀者應當掌握以下Excel技術點。條件格式的應用數據透視表的應用VLOOKUP函數的應用NOT函數OR函數4.4人事數據表的兩表數據核對關鍵技術點

要實現本例中4.4人事數據表的兩表數據核對4.4.1利用“條件格式”比照核對兩表格數據將D列中的身份證號碼與H列的身份證號碼進行比較，若不一致則顯示為黃色底紋。方法：設置條件格式，設置公式為=NOT(OR(D2=H$2:H$9))ABCDEFGH1工號隸屬部門姓名身份證號碼工號隸屬部門姓名身份證號碼225生產部張三33020219760428123388技術部Sun120302198806051611314生產部李四31030219810101012325生產部張三330202197604281233456生產部王五33090219771208213230行政部Apple3303021976042812335109生產部趙六21030219760428121114生產部李四310302198101110123676銷售部Lemon32030219631111021756生產部王五33090219771208213230行政部Apple3303021976042812337行政部Gary3303021986033001357行政部Gary33030219860330013576銷售部Lemon32030219631111021788技術部Sun120302198805061611109生產部趙六2103021967042812114.4人事數據表的兩表數據核對4.4.1利用“條件格式”O(jiān)R（）函數作用：OR函數指在其參數組中，任何一個參數邏輯值為TRUE，即返回TRUE。它與AND函數的區(qū)別在于，AND函數要求所有函數邏輯值均為真，結果方為真。而OR函數僅需其中任何一個為真即可為真。例如：對員工的考核成績進行綜合評定。本例使用OR函數來判斷一組考評數據中是否有一個大于“60”，如果有，該員工就具備晉級的資格，否則取消資格。=IF(OR(C2>60,D2>60),"晉級資格","取消資格")，按下回車鍵，即可判斷C2、D2單元格中的值，只要其中有一項大于60，利用IF函數顯示“晉級資格”，如果兩項都小于60，利用IF函數顯示“取消資格”。OR（）函數作用：OR函數指在其參數組中，任何一個參數邏輯值NOT（）函數作用：NOT函數用于對參數值求反。當要確保一個值不等于某一特定值時，可以使用NOT函數。簡言之，就是當參數值為TRUE時，NOT函數返回的結果恰與之相反，結果為FALSE.語法：NOT（logical）公式：=NOT（FALSE）表示對FALSE求反（TRUE）例如：NOT(2+2=4)，由于2+2的結果的確為4，該參數結果為TRUE，由于是NOT函數，因此返回函數結果與之相反，為FALSE。NOT（）函數作用：NOT函數用于對參數值求反。當要確保一個4.4人事數據表的兩表數據核對4.4.2利用“數據透視表”比照核對兩表格數據步驟：1、將“數據表一”和“數據表二”中的內容復制至“合并表”中2、將“姓名”和“身份證號”字段拖至“行字段”4.4人事數據表的兩表數據核對4.4.2利用“數據透視表4.4人事數據表的兩表數據核對4.4.3利用VLOOKUP函數比照核對兩表數據步驟：步驟:1、從“原始表”中查找數據=IF(ISERROR(VLOOKUP(A2,原始表!$A$1:$B$27,2,0)),"",

VLOOKUP(A2,原始表!$A$1:$B$27,2,0))2、判斷兩個表格中的數據是否一致=IF(B2<>D2,"有誤","")4.4人事數據表的兩表數據核對4.4.3利用VLOOKU4.5員工人事信息數據查詢表案例背景在人事信息基礎數據表的基礎上可以實現每人一張員工信息卡片的制作，并且可以自定義相關的信息隨時打印。

在實際工作中為了保證信息及時、準確，應注意一下兩點。

1、人事信息填寫完整，不缺頁、漏頁。2、人事信息應根據人員增減和信息變化隨時進行動態(tài)調整。

4.5.1VLOOKUP函數查詢人員信息

4.5.2美化和打印表格（員工信息卡片）

4.5員工人事信息數據查詢表案例背景4.5員工人事信息數據查詢表最終效果展示

員工信息卡片所屬部門生產部工號106姓名劉身份證號別女職稱工程師現任職務科員聯系電話12345681聯系地址杭州市某區(qū)某路4號4.5員工人事信息數據查詢表最終效果展示員工信息卡片所屬部4.5員工人事信息數據查詢表關鍵技術點

要實現本例中的功能，讀者應當掌握以下Excel技術點。VLOOKUP函數的應用4.5員工人事信息數據查詢表關鍵技術點

要實現本例中的4.5員工人事信息數據查詢表4.5.1VLOOKUP函數查詢人員信息=IF(ISERROR(VLOOKUP(B3,人事數據表!$B$3:$D$33,2,0)),"",VLOOKUP(B3,人事數據表!$B$3:$D$33,2,0))4.5.2美化和打印表格（員工信息卡片）4.5員工人事信息數據查詢表4.5.1VLOOKUP函數4.6統計不同年齡段員工信息案例背景人力資源部在人事信息統計的過程中經常需要按照不同的年齡段分組職工，從而獲知不同年齡段職工的人數情況。當員工人數眾多時，人力資源工作者借助Excel里的相關函數就能夠精確、輕松地完成該項工作。

4.6.1應用COUNTIF函數統計分段信息

4.6.2使用FREQUENCY數組公式法統計分段4.6統計不同年齡段員工信息案例背景4.6統計不同年齡段員工信息最終效果展示

4.6統計不同年齡段員工信息最終效果展示4.6統計不同年齡段員工信息關鍵技術點

要實現本例中的功能，讀者應當掌握以下Excel技術點。COUNTIF函數的應用FREQUENCY數組公式的應用4.6統計不同年齡段員工信息關鍵技術點

要實現本例中的4.6統計不同年齡段員工信息4.6.1應用COUNTIF函數統計分段信息=COUNTIF($F$3:$F$17,H5)-SUM(I$4:I4)或=COUNTIF($F$3:$F$17,K5)-COUNTIF($F$3:$F$17,H4)4.6.2使用FREQUENCY數組公式法統計分段=FREQUENCY(F3:F17,H13:H16)按ctrl+shift+enter組合鍵確認4.6統計不同年齡段員工信息4.6.1應用COUNTIFExcel在商務中的應用任課教師：李磊Excel在商務中的應用任課教師：李磊第4章人事信息數據統計分析本章要點：4.1人事信息數據表4.2人事數據的條件求和計數4.3用數據透視表和數據透視圖分析員工學歷水平4.4人事數據表的兩表數據核對4.5員工人事信息數據查詢表（員工信息卡片）4.6統計不同年齡段員工信息4.7人力資源月報動態(tài)圖表第4章人事信息數據統計分析本章要點：第4章人事信息數據統計分析第4章人事信息數據統計分析

4.1人事信息數據表

4.1.1創(chuàng)建人事信息數據表

4.1.2利用數據有效性防止工號重復輸入

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格

4.2人事數據的條件求和計數

4.2.1人事數據的單字段單條件求和計數

4.2.2人事數據的單字段多條件求和計數

4.2.3人事數據的多字段多條件求和計數

4.2.4

DSUM數據庫函數的應用

4.3用數據透視表和數據透視圖分析員工學歷水平

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

4.4人事數據表的兩表數據核對

4.4.1利用“條件格式”比照核對兩表格數據

4.4.2利用“數據透視表”比照核對兩表格數據

4.4.3利用VLOOKUP函數比照核對兩表數據

4.5員工人事信息數據查詢表（員工信息卡片）

4.5.1VLOOKUP函數查詢人員信息

4.5.2美化和打印表格（員工信息卡片）

4.6統計不同年齡段員工信息

4.6.1應用COUNTIF函數統計分段信息

4.6.2使用FREQUENCY數組公式法統計分段信息

4.7人力資源月報動態(tài)圖表

4.7.1創(chuàng)建數據源工作表

4.7.2數據分析匯總

4.7.3建立窗體控件

4.7.4對數據自動降序排列

4.7.5定義動態(tài)數據區(qū)域名稱

4.7.6繪制柏拉圖

4.7.7美化圖表區(qū)

第4章人事信息數據統計分析第4章人事信息數據統計分析

4.1人事信息數據表案例背景企業(yè)里由于人員較多且流動變化大，因而人力資源部應及時做好人事數據的整理、匯總分析等工作，并且這些數據常常也是企業(yè)做各項決策的參考依據，因此處理好人事數據的整理工作意義重大。人事信息數據表是企業(yè)進行人事信息管理的基礎和依據，因此，人事信息管理表格一定要科學、準確、詳細，并且有利于查找、利用，這樣才能真正輔助企業(yè)管理者進行人事信息管理工作。

4.1.1創(chuàng)建人事信息數據表

4.1.2利用數據有效性防止工號重復輸入

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格4.1人事信息數據表案例背景4.1人事信息數據表最終效果展示

某某有限公司員工人事信息表序號工號姓名隸屬部門學歷身份證號生日性別計算年齡（-年-月-日）年齡職稱現任職務聯系電話居住地址168胡生產部本科1201051976101200341976-10-12男35年6個月26天35年工程師部長12345678杭州市某區(qū)某路1號214徐生產部?？?201021969080331681969-8-3女42年9個月4天42年助工科員12345679杭州市某區(qū)某路2號355楊生產部碩士1201021947100111701947-10-1男64年7個月6天64年無科員12345680杭州市某區(qū)某路3號4106劉生產部?？?201101959031418291959-3-14女53年1個月24天53年工程師科員12345681杭州市某區(qū)某路4號5107李銷售部本科1201026811151711968-11-15男43年5個月23天43年工程師部長12345682杭州市某區(qū)某路5號6114林銷售部本科1201021982022811641982-2-28女30年2個月10天30年助工科員12345683杭州市某區(qū)某路6號7118童行政部?？?201107505153921975-5-15女36年11個月23天36年助工部長12345684杭州市某區(qū)某路7號869王行政部本科1201051983032530361983-3-25男29年1個月13天29年無科員12345685杭州市某區(qū)某路8號9236李生產部本科1201051983032530361983-3-25男29年1個月13天29年工程師部長12345686杭州市某區(qū)某路9號10237趙生產部?？?201021963012011691963-1-20女49年3個月18天49年工程師科員12345687杭州市某區(qū)某路10號11238劉生產部高中1201017702221511977-2-22男35年2個月16天35年工程師科員12345688杭州市某區(qū)某路11號12239馬生產部高中1201021974050707261974-5-7女38年0個月0天38年工程師科員12345689杭州市某區(qū)某路12號13240胡生產部本科1201011978050207251978-5-2女34年0個月5天34年助工科員12345690杭州市某區(qū)某路13號14241林生產部本科1201051972011933181972-1-19男40年3個月19天40年助工科員12345691杭州市某區(qū)某路14號15242童生產部碩士1201021958122316291958-12-23女53年4個月15天53年助工科員12345692杭州市某區(qū)某路15號4.1人事信息數據表最終效果展示

某某有限公司員工人事信息創(chuàng)建人事信息數據表人事信息數據表包括項目有：姓名、性別、年齡、身份證號（或社會保障號）、學歷、現任職務、聯系電話、E-mail和居住地等有效信息。其中性別、年齡、出生日期等項目均不需要手工錄入，利用函數計算即可。創(chuàng)建人事信息數據表人事信息數據表包括項目有：姓名、性別、年齡身份證號中提取生日、性別等有效信息建立人事信息數據表時，性別和出生日期兩個項目可以不必手工錄入，而是利用函數通過身份證號碼來自動提取即可。身份證號中提取生日、性別等有效信息建立人事信息數據表時，性別分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關的個人信息。15位身份證號碼：第7、8位為出生年份(兩位數)，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數為男，偶數為女。例如:某員工的身份證號碼(15位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年份(四位數)，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數為男，偶數為女，第18位為校驗位。分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年4.1人事信息數據表關鍵技術點（方法1）

要實現本例中的功能，讀者應當掌握以下Excel技術點。DATEIF函數RIGHT函數LEFT函數MID函數LEN函數4.1人事信息數據表關鍵技術點（方法1）

要實現本例中編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2編號姓名身份證號碼出生日期A001陳雙雙342701ABCD1編號姓名身份證號碼出生日期2A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+0*2),"#-00-00")=--TEXT(MID(c2,7,6),"#-00-00")=--TEXT(800913,"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙ABCD1編號姓名身份證號碼出生日期2A001陳雙雙3427011980091388411980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+1*2),"#-00-00")=--TEXT(MID(c2,7,8),"#-00-00")=--TEXT(19800913,"#-00-00")結果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數值型結果

2、函數公式中的“*”表示IF函數P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙=IF(c2="","",IF(MOD(RIGHT(LEFT(c2,17)),2),"男","女"))分析:left(C17,17)：截取單元格C17的前17位數right(left(C17,17))其實省略了一個參數，完整的應該是：right(left(C17,17),1)意思是：截取left()所得的結果的最后一位數。MOD(RIGHT(LEFT(C17,17)),2)是以上面的結果除以2的余數最后的if()函數是用來判斷的，如果余數為有余數則返回“男”，沒有余數則返回“女