1、用 Excel 函數自動錄入性別與生日摘要：在我們的日常生活中， 經常遇到輸入某些人的基本信息。 比如： 在學校的招生工作中，要錄入學生的“性別”與“出生年月”等。使 用 Excel 函數， 通過身份證編碼能夠地自動錄入。 能夠做到簡便、快 速、且不易出錯。關鍵字： 函數；公式；身份證編碼作者簡介：一、明確概念：1、什么是函數？Excel 函數即是預先定義，執行計算、分析等處理數據任務的特 殊 公 式 。 以 常 用 的 求 和 函 數 SUM 為 例 ， 它 的 語 法 是“SUM(numberl,number2,)”。其中“SUM稱為函數名稱，一個函數只有唯一的一個名稱， 它決定了函數的功

2、能和用途。 函數名稱 后緊跟左括號， 接著是用逗號分隔的稱為參數的內容， 最后用一個右 括號表示函數結束。參數是函數中最復雜的組成部分， 它規定了函數的運算對象、 順 序或結構等。 使得用戶可以對某個單元格或區域進行處理， 如分析存 款利息、確定成績名次、計算三角函數值等。2、什么是公式？函數與公式既有區別又互相聯系。 如果說前者是 Excel 預先定義 好的特殊公式，后者就 是由用戶自行設計對工作表進行計算和處理的 計算式 。以公式“二SUM(E1:H1)*A1+26為例，它要以等號“二”開始，其 內部可以包括函數、引用、運算符和常量。上式中的“SUM(E1:H1)”是函數，“A1”則是對單

3、元格A1的引用(使用其中存儲的數據)，“26” 則是常量，“ *”和“ +”則是算術運算符 (另外還有比較運算符、文 本運算符和引用運算符 )。如果函數要以公式的形式出現， 它必須有兩個組成部分， 一個是 函數名稱前面的等號，另一個則是函數本身。二、函數分析在用 Excel 函數自動錄入性別與生日中，用到的函數有：1、LEN函數返回文本字符串中的字符數。語法 : LEN(text)Text是要查找其長度的文本。空格將作為字符進行計數。例如:AE1公式結果2二LENI love w homeland2、M0函數返回兩數相除的余數。結果的正負號與除數相同語法:MOD(number,divisor)

4、Number為被除數Qivisor為除數。例如:AB公式結果2二HOD 2)! i3、MID函數返回文本字符串中從指定位置開始的特定數目的字 符，該數目由用戶指定。語法:MID(text,start_num,num_chars)Text為包含要提取字符的文本字符串;Start_num為文本中要提 取的第一個字符的位置。文本中第一個字符的 start_num為1，以此 類推;Num_chars指定希望MID從文本中返回字符的個數。例如：A| B1公式結果2 二HID1 4)19754、DATE函數返回年月日。語法：DATE(year,month,day)。

5、例如：ABC1-2008：丿 B日2門a113公式：二 dateXanbnc"4結果：2008-S-85、IF函數執行真假值判斷，根據對指定條件進行邏輯評價的真假 而返回不同的結果。語法：IF(logical-test,value-if-true,value-if-false)若Logical-test表達式成立，返回value-if-true，否則返回value-if-false。例如ALBJ1公式：1二IF (MOD 化 2)=2, TRUE, FALSE) I2結杲：FALSE'在某些情況下，您可能需要將某函數作為另一函數的參數使用。例如，上面的公式使用了嵌套的 M0函

6、數。公式可包含多達7級的嵌 套函數。當函數B在函數A中用作參數時，函數B則為第二級函數。例如M0函數是第二級函數。因為它是IF函數的參數。在 MODE數 中嵌套的函數為第三級函數，以此類推。下面，我們將會應用到嵌套 函數。三、應用根據現行的居民身份證號碼編碼規定，正在使用的18位的身份證編碼。它的第17位為性別（奇數為男，偶數為女），第18位為效驗位。而早期使用的是15位的身份證編碼，它的第15位是性別（奇數為男，偶數為女)。為了適應上述情況，必須設計一個能夠適應兩種 身份編碼的性別與出生年月的計算公式1、性別:二IF（LENE"二15JF （M0D（MID（EN 15）2）二 男二

7、"女 IF0lQD（IID（E2a 17,1）, 2）=1,廣女冷）AECDE學號nrs名1性別岀生日期身份證號2200700011鄭美娜LAJf320324198904261860320070002:荷昊！3320325198805191990420070003邁適2036區520070004:劉彥軍ir320324198802146953620070005丄.張健1f 3203248705072597 120070006一王翔寶iif 320325870623691820070007李政阻L.丫交亦躬i §36606更口弱9120070008丄趙陽SL1一=320325

8、1989090135721020070009.陳廳娟I13203258706142361120070010.何偉娜)1*3203241988061768491220070011；叢征丫5320323860809127在上圖的E列依次輸入身份證號，要求在C列輸出學生的性別。 在 C2 單元格中輸入如下公式：“=IF(LEN(E2)=15,IF(MOD(MID(E2,15,1),2)=1," 男"，” 女"),IF(MOD(MID(E2,17,1),2)=1," 男","女")”。回車后即可在單元 格獲得該學生的性別，而后只要把

9、公式復制(將插入點放在C2單元格的句柄處，按住鼠標左鍵不松手向下拖動)到 C3 C4等單元格， 即可得到其他學生的性別。為了便于大家了解上述公式的設計思路， 下面簡單介紹一下它的工作原理：該公式由三個IF 函數構成，其中“ IF(MOD(MID(E2,15,1),2)=1," 男 "," 女 ") ” 和 “ IF(MOD(MID(E2,17,1),2)=1," 男"," 女") ”作為第一個函數的參數。 公式中“ LEN(E2)=15是一個邏輯判斷語句，LEN函數提取E2等單 元格中的字符長度，如果該字符的長度等

10、于15，則執行參數中的第一個 IF 函數，否則就執行第二個 IF 函數。在參數“ IF(MOD(MID(E2,15,1),2)=1," 男","女")”中。MID 函 數從E2的指定位置(第15位)提取1個字符，而MODI數將該字符與 2 相除，獲取兩者的余數。如果兩者能夠除盡，說明提取出來的字符 是 0(否則就是 1)。邏輯條件“ MOD(MID(E2,15,1),2)=1 ”不成立，這 時就會在C2單元格中填入“女”，反之則會填入“男”。如果LEN函數提取的E2等單元格中的字符長度不等于15,則會執 行第2個IF函數。除了 MID函數從E2的指定位置

11、(第17位，即倒數 第 2位)提取 1 個字符以外,其他運算過程與上面的介紹相同。2、出生年月：6=IF(LBNCE2)=15, DATEClSOIDCE 7, 2), lllDCE?, 92)ID(E2,11, 2), DATE (HIDCE2, 7, 4), HID(E2,11, 2),110(£2,13, 2)ABCDE1學號II姓名II性別II岀生日期r身份證號220070001 【鄭美娜；女19S9J-26320324198904261860320070002:何昊:男T320325198805191990420070003 1白麗剋女32032519900S22362S5

12、26670004閔彥輕臭320324193802146953620076665 I 艇=男f 320324870507259720070006 1至翱宇$男r 320325870623691826670007 季政眄男L . . . J320325199002082535926670008俺聒群i男3203251989090135721020070009 陳晩鵝女:3203258706142361120070010 何希鯛女3203241988061768491220076611 :叢征:男r 320323860809127快速輸入學生的出生年月與上面我們介紹性別的快速輸入的思路相同。 在D2

13、單元格 中輸入 如下公式：“ =IF(LEN(E2)=15,DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2),DATE(MID(E2,7 ,4),MID(E2,11,2),MID(E2,13,2)”。回車后即可在單元格獲得該學生的出生年月，而后只要把公式復制到D3 D4等單元格，即可得到其他學生的生日。D2與C2單元格中都使用了嵌套函數。其中 “ DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2)” 和“ DATE(MID(E2,7,4),MID(E2,11,2),MID(E2,13,2)” 作為第一

14、個函數的參數。公式中“ LEN(E2)=15”是一個邏輯判斷語句，LEN函數 提取E2等單元格中的字符長度，如果該字符的長度等于 15,貝卩執 行參數中的第一個DATEi數，否則就執行第二個 DATEi數。在參數“ DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2) ” 中。在19&MID(E2,7,2)中，19與MID函數從E2的第7位開始提取的 2個字符進行文本連接運算，得到的字符串作為 DATE函數的第一個 參數。DATE!數將返回日期。如果LEN函數提取的E2等單元格中的字符長度不等于 15,則會 執行第2個DATE!數。上面已介紹過，不再贅述。通過身份證編碼自動錄入的性別與生日后，表格如下:1學號姓名1性別I岀生日期1身份證號2 J20070001:鄭美娜女19891-26：320324198904261860320070002：何昊19SS-5-19203251988051919964200700031990-8-2232032519906822362852007000419S8-2-143203241988021469536 J20070005丄一張健19S7-5-7:3203248705072