用Excel函數(shù)自動(dòng)錄入性別與生日

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

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

3、元格A1的引用(使用其中存儲(chǔ)的數(shù)據(jù))，“26” 則是常量，“ *”和“ +”則是算術(shù)運(yùn)算符 (另外還有比較運(yùn)算符、文 本運(yùn)算符和引用運(yùn)算符 )。如果函數(shù)要以公式的形式出現(xiàn)， 它必須有兩個(gè)組成部分， 一個(gè)是 函數(shù)名稱前面的等號(hào)，另一個(gè)則是函數(shù)本身。二、函數(shù)分析在用 Excel 函數(shù)自動(dòng)錄入性別與生日中，用到的函數(shù)有：1、LEN函數(shù)返回文本字符串中的字符數(shù)。語法 : LEN(text)Text是要查找其長度的文本?？崭駥⒆鳛樽址M(jìn)行計(jì)數(shù)。例如:AE1公式結(jié)果2二LENI love w homeland2、M0函數(shù)返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同語法:MOD(number,divisor)

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

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

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

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

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

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

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

11、(第17位，即倒數(shù) 第 2位)提取 1 個(gè)字符以外,其他運(yùn)算過程與上面的介紹相同。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學(xué)號(hào)II姓名II性別II岀生日期r身份證號(hào)220070001 【鄭美娜；女19S9J-26320324198904261860320070002:何昊:男T320325198805191990420070003 1白麗剋女32032519900S22362S5

12、26670004閔彥輕臭320324193802146953620076665 I 艇=男f 320324870507259720070006 1至翱宇$男r 320325870623691826670007 季政眄男L . . . J320325199002082535926670008俺聒群i男3203251989090135721020070009 陳晩鵝女:3203258706142361120070010 何希鯛女3203241988061768491220076611 :叢征:男r 320323860809127快速輸入學(xué)生的出生年月與上面我們介紹性別的快速輸入的思路相同。 在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)”。回車后即可在單元格獲得該學(xué)生的出生年月，而后只要把公式復(fù)制到D3 D4等單元格，即可得到其他學(xué)生的生日。D2與C2單元格中都使用了嵌套函數(shù)。其中 “ 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、個(gè)函數(shù)的參數(shù)。公式中“ LEN(E2)=15”是一個(gè)邏輯判斷語句，LEN函數(shù) 提取E2等單元格中的字符長度，如果該字符的長度等于 15,貝卩執(zhí) 行參數(shù)中的第一個(gè)DATEi數(shù)，否則就執(zhí)行第二個(gè) DATEi數(shù)。在參數(shù)“ DATE(19&MID(E2,7,2),MID(E2,9,2),MID(E2,11,2) ” 中。在19&MID(E2,7,2)中，19與MID函數(shù)從E2的第7位開始提取的 2個(gè)字符進(jìn)行文本連接運(yùn)算，得到的字符串作為 DATE函數(shù)的第一個(gè) 參數(shù)。DATE!數(shù)將返回日期。如果LEN函數(shù)提取的E2等單元格中的字符長度不等于 15,則會(huì) 執(zhí)行第2個(gè)DATE!數(shù)。上面已介紹過，不再贅述。通過身份證編碼自動(dòng)錄入的性別與生日后，表格如下:1學(xué)號(hào)姓名1性別I岀生日期1身份證號(hào)2 J20070001:鄭美娜女19891-26：320324198904261860320070002：何昊19SS-5-19203251988051919964200700031990-8-2232032519906822362852007000419S8-2-143203241988021469536 J20070005丄一張健19S7-5-7:3203248705072