巧用ＥＸＣＥＬ函數(shù)建新生學(xué)籍

葉建陽

新生入校后,學(xué)校的一項(xiàng)重要工作就是建立新生學(xué)籍。隨著學(xué)生學(xué)籍的數(shù)字化、電子化、網(wǎng)絡(luò)化、信息化,對我們的學(xué)生學(xué)籍管理提出了更高要求。大多數(shù)學(xué)校建立學(xué)生電子學(xué)籍是在電子表格下完成的,即便使用數(shù)據(jù)庫,多數(shù)也是先建立電子表格,然后導(dǎo)入數(shù)據(jù)庫。因?yàn)殡娮颖砀窬哂胁僮骱啽阒庇^,易于打印校對,不需要定義字段等優(yōu)點(diǎn),且普及和應(yīng)用遠(yuǎn)遠(yuǎn)勝于數(shù)據(jù)庫。但輸入的信息多數(shù)情況下是簡單重復(fù)且有規(guī)律性的,為了避免重復(fù)無意義的勞動(dòng),在實(shí)際工作中,輸入方法的科學(xué)與否,直接影響輸入的成效。電子表格中巧用函數(shù),是解決這一問題最直接、最有效的方法。

一、首位帶零的長學(xué)號生成方法:

學(xué)校為學(xué)生建立學(xué)籍往往要編輯學(xué)號。例如:某校的學(xué)號編輯規(guī)律是錄取年份兩位數(shù)、學(xué)校代碼五位數(shù)、院系代碼一位數(shù)、學(xué)歷層次兩位數(shù)、專業(yè)代碼兩位數(shù)、班級一位數(shù)、順序號兩位數(shù),共計(jì)十五位數(shù)。例如:07年錄取的某專業(yè)新生,編輯的學(xué)號為“071698042034012”。其中,07是錄取年份,16980是學(xué)校代碼,4是院系代碼,20是學(xué)歷層次編碼,34是專業(yè)編碼,0表示是單班,12是學(xué)生的順序號。

在電子表格中生成學(xué)號時(shí),學(xué)籍人員往往將輸入學(xué)號一列定義為“文本”,否則不能保留首位零,然后一一輸入,效率極低;如果使用“常規(guī)”或“數(shù)值(小數(shù)位數(shù)定義為零)”,因?qū)W號編碼太長,當(dāng)超過十一位時(shí),就自動(dòng)采用科學(xué)記數(shù)法,不能正常顯示學(xué)號編碼,且首位“零”不能保留。

而事實(shí)上只要合理利用文本合并函數(shù)“CONCATENATE”,就可輕松解決這一問題。

語法:CONCATENATE (text1,text2,...)

Text1,text2,...為1到30個(gè)將要合并成單個(gè)文本項(xiàng)的文本項(xiàng)。這些文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹€(gè)單元格的引用。

在實(shí)際操作中,text1、text2、……分別指電子表格中的某個(gè)單元格中的字符串,常用單元格名稱代替。

假定:在A列A3單元格以下生成新生學(xué)號。先在C列C3、C4分別輸入“34001”、“34002”,選中此二格,利用填充柄向下生成所需順序號數(shù)值。然后選擇B列,定義B列為“常規(guī)”,再選中B3格,在英文輸入法狀態(tài)下,輸入“=concatenate(‘07169804203,C3)”,敲回車鍵,即可在B3單元格產(chǎn)生十五位學(xué)號。然后用填充柄在B列向下拖拽可產(chǎn)生所需學(xué)號,快速簡便,效率極高。生成的學(xué)號會因刪除B、C兩列無用數(shù)值而產(chǎn)生錯(cuò)誤,所以在B列生成十五位學(xué)號后,必須全部選中,單擊鼠標(biāo)右鍵,在彈出的快捷菜單中選擇“復(fù)制”,再選中A3單元格,單擊鼠標(biāo)右鍵,在彈出的快捷菜單中選擇“選擇性粘貼”,在彈出的“選擇性粘貼”對話框中,選擇“數(shù)值”,單擊“確定”按鍵,即可將B列產(chǎn)生的學(xué)號全部復(fù)制到A列。然后全部刪除B、C列的數(shù)值。

二、巧用身份證號生成出生年月:

建立學(xué)生學(xué)籍時(shí),往往既要輸入出生年月也要輸入身份證號,輸入耗時(shí)且易出錯(cuò),有時(shí)往往出現(xiàn)身份證號與出生年月不一致的問題,給校對帶來很大的麻煩。

在實(shí)際輸入中,只要輸入正確的身份證號,經(jīng)校對無誤后,可用MID函數(shù)自動(dòng)生成出生年月。

MID 返回文本字符串中從指定位置開始的特定數(shù)目的字符,該數(shù)目由用戶指定。

語法:MID(text,start_num,num_chars)

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 start_num為1,以此類推。

Num_chars 指定希望MID從文本中返回字符的個(gè)數(shù)。

學(xué)生的身份證號全部為18位,幾乎沒有15位,前六為省、市、縣區(qū)代碼,中間8位為出生年月日,后4位為該生編碼。而我們要用的就是中間這8位數(shù)值。

我們可用MID函數(shù)從身份證號中巧用此8位出生年月,而不用一一輸入,即保證了數(shù)值的準(zhǔn)確性、與身份證號的一致性,且簡單快捷。

假定,已在F列F3單元格以下輸入了學(xué)生的身份證號,現(xiàn)要在D列D3以下輸入學(xué)生的出生年月,只要選中D3單元格,在其中輸入“=MID(F3,7,8)”,意為從F3單元格的字符串中從7位起向后共選取8位數(shù)值(含第7位)返回其值在D3單元格。然后敲回車鍵,即可在D3單元格產(chǎn)生8位出生年月數(shù)值。余下的工作就是用填充柄向下填充了。

也可用下式生成日期格式出生年月,且可在身份證號單元格為空時(shí),不返回錯(cuò)誤信息,而返回空值。

=IF(F3=“”,“”,MID(F3,7,4)&-MID(F3,9,2)&-MID(F3,11,2))

不論哪一種輸入法,輸入男、女性別,都是多次敲擊鍵盤的重復(fù)錄入。有沒有只需敲擊一次鍵盤即可輸入性別的方法呢?

IF函數(shù)巧用可成就性別快速輸入。

IF函數(shù)執(zhí)行真假值判斷,根據(jù)邏輯計(jì)算的真假值,返回不同結(jié)果。

語法:IF(logical_test,value_if_true,value_if_false)

Logical_test:表示計(jì)算結(jié)果為 TRUE 或FALSE的任意值或表達(dá)式。例如,A10=100就是一個(gè)邏輯表達(dá)式,如果單元格A10中的值等于100,表達(dá)式即為TRUE,否則為FALSE。本參數(shù)可使用任何比較運(yùn)算符。

Value_if_true:logical_test為TRUE時(shí)返回的值。例如,如果本參數(shù)為文本字符串“男”而且logical_test參數(shù)值為 TRUE,則IF函數(shù)將顯示文本“男”。如果logical_test為TRUE而value_if_true為空,則本參數(shù)返回0(零)。如果要顯示TRUE,則請為本參數(shù)使用邏輯值TRUE。Value_if_true也可以是其他公式。

Value_if_false:logical_test為 FALSE 時(shí)返回的值。例如,如果本參數(shù)為文本字符串“女”而且logical_test參數(shù)值為 FALSE,則IF函數(shù)將顯示文本“女”。如果 logical_test為FALSE 且忽略了Value_if_false(即value_if_true后沒有逗號),則會返回邏輯值FALSE。如果logical_test 為FALSE且Value_if_false為空(即 value_if_true后有逗號,并緊跟著右括號),則本參數(shù)返回0(零)。Value_if_false也可以是其他公式。

函數(shù)IF可以嵌套七層,用value_if_false及value_if_true參數(shù)可以構(gòu)造復(fù)雜的檢測條件。

假定:在B列B3開始輸入姓名,C列C3開始輸入性別。先在D列D3單元格輸入“=IF(C3=0,“男”,“女”)”,然后用填充柄拖拽至輸入完成的最后一行,會在D列看到性別全部為“男”。在B列輸入姓名完成后,當(dāng)該生性別為“男”時(shí)在C列輸入“0”或不輸入任何內(nèi)容,當(dāng)為該生性別“女”時(shí),可在C列輸入除“0”以外的任何一字符或數(shù)字,D列對應(yīng)的性別會立即變?yōu)椤芭?連輸入法都不用切換。輸入完成后,只要將D列性別“復(fù)制”并以“選擇性粘貼”、“數(shù)值”粘貼在C列即告完成。

身份證號第十七位為單數(shù)表示“男”性,為偶數(shù)時(shí)表示“女性”。只要使用MID函數(shù)從身份證號中提取第17位數(shù),再使用求余函數(shù)MOD對提取值進(jìn)行求余運(yùn)算,當(dāng)余數(shù)為“1”時(shí),說明是單數(shù),返回函數(shù)值“男”,否則返回函數(shù)值“女”。假設(shè)從F3單元格及以下錄入了身份證號,可用下列式在公式所在單元格自動(dòng)產(chǎn)生性別。

=IF(F3=“”,“”,IF(MOD(MID(F3,17,1),2)=1,“男”,“女”)

Excel中函數(shù)功能極其強(qiáng)大,應(yīng)用得當(dāng)可以起到事半功倍的作用,是電子表格處理事務(wù)的強(qiáng)有力助手。函數(shù)應(yīng)用的能力強(qiáng)弱,將直接影響到電子表格的應(yīng)用效率。電子表格不僅僅是學(xué)生學(xué)籍電子管理、學(xué)生成績管理的手段,同時(shí)也為我們提供了強(qiáng)大的開發(fā)應(yīng)用價(jià)值,我們應(yīng)在實(shí)踐中不斷學(xué)習(xí)和提高應(yīng)用能力,發(fā)揮好這一工具的作用。

作者單位:延安職業(yè)技術(shù)學(xué)院(學(xué)士、副教授)