5.5 提取教師個人信息

案例背景
    在2.3節(jié)中曾涉及到組織天津市中小學教師信息技術考核的問題，當時各區(qū)縣報上來的匯總表的信息也不盡相同，但是都有參加考試的教師姓名和身份證號這兩項信息，我們就根據教師的身份證號碼，提取出該教師的出生年月日，性別和原戶籍所在地，由此統(tǒng)計出全市參加“中小學教師信息技術考核”的整體情況。
    如何從身份證號碼提取出生年月日、性別和戶籍所在區(qū)呢？我國的公民身份證號碼是按照《公民身份證號碼》國家標準編制的，身份證號碼由18位數字組成：前6位為行政區(qū)劃分代碼，第7位至14位為出生日期碼，第15位至17位為順序碼，第18位為校驗碼。其中，男性的順序碼設為奇數，女性順序碼設為偶數。根據這些規(guī)則使用Excel函數設計公式就可以實現從身份證號碼中提取上述信息了。鑒于可視界面區(qū)域的限制，我們選擇部分參加“天津市中小學教師信息技術考核”的教師信息作為本案例樣本原始信息。
關鍵技術點
    要實現本案例中的功能，學員應該掌握以下EXCEL技術點。
    ●基礎知識
    ●函數應用 IF函數，MOD函數，MID函數，LEFT函數， VLOOKUP函數
    ●函數應用 ISODD函數，ISEVEN函數，DATE函數
最終效果展示

5.5.1創(chuàng)建教師個人信息表
Step1新建工作簿，重命名工作表
單擊文件“保持”，將工作簿命名為”教師個人信息.xls”。將“Sheet1”工作表重命名為“信息表”，將“Sheet2”工作表重命名為“對照表”。

Step2輸入教師個人原始信息
①單擊 “信息表”工作表標簽，在單元格區(qū)域A1：G1，輸入字段名稱：“序號”，“姓名”，“身份證號碼”，“性別”，“出生年月日”，“省/直轄市/自治區(qū)”和“區(qū)/縣”。

②在單元格區(qū)域B2:C27輸入教師姓名和身份證號碼，在單元格A1輸入“1”，單元格A2輸入“2”，選中單元格區(qū)域：A1：A2，雙擊單元格A2右下角的填充柄即可完成A3：A27單元格區(qū)域的自然數字的填充。

Step 3用不同的嵌套函數提取“性別”
①選中單元格D2，在編輯欄輸入以下公式，然后按鍵確認得到第1名教師的“性別“。
=IF(MOD(MID(C2,17,1),2)=1,"男","女")

②選中單元格D2，向下拖曳其右下角的填充柄至單元格D11，松開鼠標完成第2至第10個教師的性別提取。

③選中單元格D12，在編輯欄輸入以下公式，然后按鍵確認得到第11名教師的“性別“。
“=IF(ISODD(MID(C12,17,1)),"男","女")”

④選中單元格D12，向下拖曳其右下角的填充柄至單元格D21，松開鼠標完成第12至第20個教師的性別提取。

⑤選中單元格D22，在編輯欄輸入以下公式，然后按鍵確認得到第21名教師的“性別“。
“=IF(ISEVEN(MID(C22,17,1)),"女","男")”

⑥選中單元格D22，向下拖曳其右下角的填充柄至單元格D27，松開鼠標完成剩余教師的性別提取。

Step 4提取“出生年月日“
①選中單元格E2，在編輯欄輸入以下公式，然后按鍵確認即可得到第1名教師的“出生年月日”。
“=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))

②雙擊單元格E2右下角的填充柄即可完成其余教師的出生年月日”的提取工作。

Step 4建立全國省份地區(qū)代碼索引對照表
①切換到“對照表”工作表，在單元格區(qū)域A1:A4輸入：“身份證前兩位”，“省/直轄市/自治區(qū)”，“身份證前六位”和“區(qū)/縣”。
②在A2:D2308輸入全國省份地區(qū)代碼索引對照表，如圖12所示

Step5提取教師原戶籍所在“省/直轄市/自治區(qū)”
①切換到“信息表”工作表，選中單元格F2，在編輯欄輸入以下公式，然后按鍵確認即可得到第1個教師的原戶籍所在“省/直轄市/自治區(qū)”。
“=VLOOKUP(LEFT(C2,2), 對照表!$A$2:$B$35,2,0) “

②雙擊單元格F2右下角的填充柄即可完成其余教師的原戶籍所在“省/直轄市/自治區(qū)”的提取工作。

Step6提取教師原戶籍所在“區(qū)/縣“
①選中單元格G2，在編輯欄輸入以下公式，然后按鍵確認即可得到第1個教師的原戶籍所在“區(qū)/縣“。
“= VLOOKUP(LEFT(C2,6),對照表!$C$2:$D$10000,2,0) “

②雙擊單元格G2右下角的填充柄即可完成其余教師的原戶籍所在“區(qū)/縣”的提取工作。

5.5.2美化修飾“教師個人信息表”
為表格設置邊框，為字段名區(qū)域，教師原始信息區(qū)域和提取信息區(qū)域，分別設置不同顏色的底紋，以示區(qū)別。

至此教師個人信息提取工作全部完成。

關鍵知識點講解：
1. ISODD函數
函數名稱：ISODD
主要功能：如果參數 number 為奇數，返回 TRUE，否則返回 FALSE。
    如果該函數不可用，并且返回錯誤值 #NAME?，請安裝并加載“分析工具庫”加載宏。
    操作方法
    1.在“工具”菜單上，單擊“加載宏”。
    2.在“可用加載宏”列表中，選中“分析工具庫”框，再單擊“確定”。
    3.如果必要，請遵循安裝程序中的指示。
使用格式：ISODD(number)
參數說明：
Number 待測試的數值。如果參數值不是整數，則截尾取整。
函數說明
如果參數 number 不是數值型，函數 ISODD 返回錯誤值 #VALUE!。
應用示例

2. ISEVEN函數
函數名稱：ISEVEN
主要功能：如果參數 number 為偶數，返回 TRUE，否則返回 FALSE。
    如果該函數不可用，并返回錯誤值 #NAME?，請安裝并加載“分析工具庫”加載宏。
    操作方法
    1.在“工具”菜單上，單擊“加載宏”。
    2.在“可用加載宏”列表中，選中“分析工具庫”框，再單擊“確定”。
    3.如果必要，請遵循安裝程序中的指示。
使用格式：ISEVEN(number)
參數說明：
Number    待測試的數值。如果參數值不是整數，則截尾取整。
函數說明
如果參數 number 為非數值型，函數 ISEVEN 返回錯誤值 #VALUE!。
應用示例

3.DATE函數
函數名稱：DATE
主要功能：返回代表特定日期的序列號。如果在輸入函數前，單元格格式為“常規(guī)”，則結果將設為日期格式。
使用格式：DATE(year,month,day)
參數說明：
Year    參數 year 可以為一到四位數字。Microsoft Excel 將根據所使用的日期系統(tǒng)來解釋 year 參數。默認情況下，Microsoft Excel for Windows 將使用 1900 日期系統(tǒng)，而 Microsoft Excel for Macintosh 將使用 1904 日期系統(tǒng)。
    對于 1900 年日期系統(tǒng)
    ●如果 year 位于 0（零）到 1899（包含）之間，則 Excel 會將該值加上 1900，再計算年份。例如：DATE(108,1,2) 將返回 2008 年 1 月 2 日 (1900+108)。
    ●如果 year 位于 1900 到 9999（包含）之間，則 Excel 將使用該數值作為年份。例如：DATE(2008,1,2) 將返回 2008 年 1 月 2 日。
    ●如果 year 小于 0 或大于等于 10000，則 Excel 將返回錯誤值 #NUM!。
    對于 1904 年日期系統(tǒng)
    ●如果 year 位于 4 到 1899（含）之間，則 Excel 會將該值加上 1900，再計算年份。例如：DATE(108,1,2) 將返回 2008 年 1 月 2 日 (1900+108)。
    ●如果 year 位于 1904 到 9999（含）之間，則 Excel 將使用該數值作為年份。例如：DATE(2008,1,2) 將返回 2008 年 1 月 2 日。
    ●如果 year 小于 4 或大于等于 10000，或者位于 1900 到 1903（含）之間，則 Excel 將返回錯誤值 #NUM!。
    Month    代表每年中月份的數字。如果所輸入的月份大于 12，將從指定年份的一月份開始往上加算。例如：DATE(2008,14,2) 返回代表 2009 年 2 月 2 日的序列號。
    Day    代表在該月份中第幾天的數字。如果 day 大于該月份的最大天數，則將從指定月份的第一天開始往上累加。例如，DATE(2008,1,35) 返回代表 2008 年 2 月 4 日的序列號。
函數說明
    ●Microsoft Excel 可將日期存儲為可用于計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1 而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一個默認日期系統(tǒng)。
    ●函數 DATE 在年、月、日為變量的公式中非常有用。
應用示例

注釋    若要將數字轉換為日期形式，請選中單元格，在“格式”菜單上單擊“單元格”，再單擊“數字”選項卡，然后單擊“分類”框中的“日期”，在單擊“分類“框中的任意一種您想要的日期形式即可將數字轉換為日期形式。
案例公式解析：
    Step 3之①的單元格D2中的公式為     “=IF(MOD(MID(C2,17,1),2)=1,"男","女")”
    其中MID(C2,17,1)=2，公式可化簡為     “=IF(MOD(2,2)=1,"男","女")”
    MOD(2,2)=1返回FALSE所以公式繼續(xù)化簡為     “=IF(FALSE,"男","女")”
    最后單元格D2中的公式返回“女”。
    Step 3之③的單元格D12中的公式為     “=IF(ISODD(MID(C12,17,1)),"男","女")”
    其中MID(C12,17,1)=2，公式可化簡為    =IF(ISODD(2),"男","女")”
    公式可化簡為    =IF(ISODD(2),"男","女")”
    因為2不是奇數，所以ISODD(2)返回FALSE，公式繼續(xù)化簡為    =IF(FALSE,"男","女")”
    最后單元格D12中的公式返回“女”。
    Step 3之⑤的單元格D22中的公式為     “=IF(ISEVEN(MID(C22,17,1)),"女","男")”
    其中MID(C12,17,1)=6，公式可化簡為     “=IF(ISEVEN(6),"女","男")”
    因為6是偶數，ISEVEN(6) 返回TRUE，公式繼續(xù)化簡為     “=IF(TRUE,"女","男")”
    最后單元格D22中的公式返回“女”。
    Step 4之①的單元格E2中的公式為     “=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))
    因為MID(C2,7,4)=1980，MID(C2,11,2)=09,MID(C2,13,2)=19
    公式可化簡為     “=DATE(1980,09,19)
    最后單元格E2中的公式返回第一個教師的出生年月日：“1980-9-19”
    Step 5之①的單元格F2中的公式為
     “= VLOOKUP(LEFT(C2,2),對照表!A2:B35,2,0) “
    其中公式LEFT(C2,2)=12，公式可化簡為
     “=VLOOKUP(12,對照表!A2:B35,2,0) ”
    在“對照表”中的單元格區(qū)域A2:B35中是省/直轄市/自治區(qū)索引表，而“12”對應著“天津市”所以最后單元格F2中的公式返回“天津市”。
    Step 6之①的單元格G2中的公式為
    = VLOOKUP(LEFT(C2,6),對照表!$C$2:$D$10000,2,0)
    其中LEFT(C2,6)=120221，公式可化簡為
    = VLOOKUP(120221,對照表!$C$2:$D$2308,2,0)
    在“對照表”中的單元格區(qū)域C2:D2308中是區(qū)/縣索引表，而“122021”對應著“寧河縣”所以最后單元格G2中的公式返回“寧河縣”。