巧用ＥＸＣＥＬ函數建新生學籍

葉建陽

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

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

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

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

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

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

Text1,text2,...為1到30個將要合并成單個文本項的文本項。這些文本項可以為文本字符串、數字或對單個單元格的引用。

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

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

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

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

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

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

語法:MID(text,start_num,num_chars)

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

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

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

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

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

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

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

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

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

IF函數巧用可成就性別快速輸入。

IF函數執行真假值判斷,根據邏輯計算的真假值,返回不同結果。

語法:IF(logical_test,value_if_true,value_if_false)

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

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

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

函數IF可以嵌套七層,用value_if_false及value_if_true參數可以構造復雜的檢測條件。

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

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

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

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

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