葉建陽

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

一、首位帶零的長學號生成方法:

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

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

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

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

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

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

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

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

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

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

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

語法:MID(text,start_num,num_chars)

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

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

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

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

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

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

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

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

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

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

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

語法:IF(logical_test,value_if_true,value_if_false)

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

Value_if_true:logical_test為TRUE時返回的值。例如,如果本參數(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ù)為文本字符串“女”而且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ù)可以構造復雜的檢測條件。

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

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

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

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

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