Excel函數(shù)開放教育學(xué)籍管理論文

1、Excel函數(shù)開放教育學(xué)籍管理論文一、引言 南京電大開放教育學(xué)籍管理工作涉及開放教育學(xué)生的入學(xué)注冊管理、學(xué)生基本信息管理、學(xué)生學(xué)籍異動管理、學(xué)生課程注冊管理、學(xué)生畢業(yè)審核管理和學(xué)位審核等工作，如何做好平均每學(xué)期三萬多在籍學(xué)生的各項學(xué)籍管理工作，以及學(xué)籍管理工作中涉及到的各項數(shù)據(jù)處理、統(tǒng)計和分析工作，除了熟練掌握開放教育教務(wù)管理系統(tǒng)外，掌握相關(guān)的數(shù)據(jù)表格管理軟件是非常有必要的，Microsoftoffice的電子表格處理軟件Excel就是一套優(yōu)秀的數(shù)據(jù)處理軟件。通過Excel工具配合開放教育教務(wù)管理系統(tǒng)的使用，必將極大地提高開放教育學(xué)籍管理工作效率，取得良好效果。 二、Excel工具及常用函數(shù)介

2、紹 Excel是微軟公司的辦公軟件Microsoftoffice的組件之一，也是微軟辦公套裝軟件的一個重要組成部分。它可以進行各種數(shù)據(jù)的處理、統(tǒng)計分析和輔助決策操作，廣泛地應(yīng)用于管理、統(tǒng)計、財經(jīng)、金融等眾多領(lǐng)域。Excel中的函數(shù)是一些預(yù)定義的公式，它們使用一些參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算。用戶可以直接使用它們對某個區(qū)域內(nèi)的數(shù)值進行一系列運算，如分析和處理日期值和時間值、確定單元格中的數(shù)據(jù)類型、計算平均值和運算文本數(shù)據(jù)等。Excel函數(shù)一共有11類，分別是數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、工程函數(shù)、財務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計函數(shù)、文本函數(shù)以及用戶自

3、定義函數(shù)。下面，筆者就南京電大開放教育學(xué)籍管理中經(jīng)常使用的Excel函數(shù)作簡要介紹：1.MID函數(shù)功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。格式：MID(text,start_num,num_chars)參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目。舉例：若A1單位格中內(nèi)容為“開放教育學(xué)籍管理”，從中取出“學(xué)籍”可以在B1單元格編輯公式“=MID(A1,5,2)”，確認后B1單元格顯出“學(xué)籍”。2.LEN函數(shù)功能：統(tǒng)計文本字符串中字符數(shù)目。格式：LEN(text)參加數(shù)說明：text表示要統(tǒng)計的文本字符串。舉例：

4、若A1單位格中內(nèi)容為“開放教育學(xué)籍管理”，要統(tǒng)計A1單元格中字符的數(shù)目，可以在B1單元格編輯公式“=len(A1)”，確認后B1單元格顯示出統(tǒng)計結(jié)果“8”。LEN函數(shù)統(tǒng)計時，無論參數(shù)中是全角字符，還是半角字符，每個字符均計為“1”；與之相對應(yīng)的一個函數(shù)LENB，在統(tǒng)計時半角字符計為“1”，全角字符計為“2”。3.IF函數(shù)功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。格式：IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返

5、回“TRUE”；Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如果忽略返回“FALSE”。舉例：A1單元格為學(xué)生的年齡，在B1單元格中輸入公式：=IF(A1=35,”中年組”,”青年組”)，確認以后，如果A1單元格中的數(shù)值大于或等于35，則C29單元格顯示“中年組”字樣，反之顯示“青年組”。4.DATEDIF函數(shù)功能：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。格式：DATEDIF(start_date,end_date,unit)參數(shù)說明：Start_date為一個日期，它代表時間段內(nèi)的第一個日期或起始日期。End_date為一個日期，它代表時間段內(nèi)的最后一個日期

6、或結(jié)束日期。Unit為所需信息的返回類型。Unit參數(shù)中”Y”返回時間段中的整年數(shù)，Unit參數(shù)中”M”時間段中的整月數(shù)，Unit參數(shù)中”D”時間段中的天數(shù)。5.VLOOKUP函數(shù)功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)參數(shù)說明：Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當(dāng)Col_index_n

7、um為2時,返回table_array第2列中的數(shù)值，為3時，返回第3列的值）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯誤值#N/A。 三、Excel函數(shù)在開放教育學(xué)籍管理中的應(yīng)用 （一）數(shù)據(jù)截取 在南京電大開放教育學(xué)籍管理過程中，經(jīng)常要根據(jù)中央電大下發(fā)的數(shù)據(jù)，提取學(xué)生所在的省級電大名稱、學(xué)生的類別和年級等。例如：在畢業(yè)審核反饋文件中的畢業(yè)學(xué)生統(tǒng)考未通過名單（詳見圖，注：考慮到學(xué)生信息的隱私性，圖中所涉及的數(shù)據(jù)均為隨機編制數(shù)據(jù)

8、）。圖1.學(xué)生基本信息表截圖1.從數(shù)據(jù)表中篩選出南京電大的學(xué)生由于數(shù)據(jù)表中沒有省級電大名稱字段，此時，我們可以利用MID函數(shù)從學(xué)號字段中截取出省級電大代碼，然后再根據(jù)省級電大代碼篩選出滿足條件的記錄即可。具體步驟：第一步，在D1單元格輸入標題省校電大名稱；第二步，在D2單元格編輯公式“=MID(A2,6，3)”后確認，即從學(xué)號字段中第六個字符開始取三個字符即省級電大代碼；第三步使用自動填充功能引用公式，得出所有學(xué)生的省校代碼；第四步，使用EXCEL篩選出滿足條件為“321”的記錄，南京電大的省級代碼為321，即篩選出南京電大的學(xué)生記錄（如圖2）。圖2.學(xué)生基本信息表中XH字段含“321”信息截

9、圖2.從數(shù)據(jù)表中篩選出學(xué)生的年級同理，我們可以利用MID函數(shù)從學(xué)號字段中截取出學(xué)生的年級，具體步驟：第一步，在D1單元格輸入標題年級；第二步，在D2單元格編輯公式“=MID(A2,1,4)”后確認，即從學(xué)號字段中取出前四個字符即為年級代碼；第三步使用自動填充功能引用公式，得出所有學(xué)生的年級（功能相似，此處不作圖示）。3.從數(shù)據(jù)表中篩選出學(xué)生的類別同理，我們還可以利用前面的MID函數(shù)實現(xiàn),從學(xué)號字段中截取出學(xué)生的類別代碼，“1”為開放本科學(xué)生，“7”為開放專科學(xué)生，具體步驟：第一步，在D1單元格輸入標題學(xué)生類別；第二步，在D2單元格編輯公式“=MID(A2,5,1)”后確認，即從學(xué)號字段中取出學(xué)

10、生類別代碼；第三步使用自動填充功能引用公式，得出所有學(xué)生的類別代碼（功能相似，此處不作圖示）。 （二）數(shù)據(jù)計算 南京電大開放教育學(xué)籍科每學(xué)期會對開放教育在籍和畢業(yè)學(xué)生信息進行分類統(tǒng)計，如按照學(xué)生的性別、專業(yè)、籍貫、民族、政治面貌等，此類信息可以從數(shù)據(jù)庫中直接提取分類匯總統(tǒng)計結(jié)果。而有些數(shù)據(jù)則需要通過對系統(tǒng)中的數(shù)據(jù)進行計算，才能得到相關(guān)的統(tǒng)計結(jié)果，例如學(xué)生的年齡，我們可以從學(xué)生的身份證號碼字段提取出相關(guān)數(shù)據(jù)計算學(xué)生的年齡，在提取身份證號碼中出生日期數(shù)據(jù)時要注意區(qū)分身份證號碼15位和18位不同的取值，可以通過LEN函數(shù)來判斷身份證號碼的位數(shù)，使用IF函數(shù)做判斷。如果身份證號碼為18位，通過MID函

11、數(shù)從第7位開始取4位作為出生年份，否則用MID函數(shù)從第7位開始取2位作為出生年份。最后用返回當(dāng)前日期函數(shù)TODAY函數(shù)進行運算，即用當(dāng)前系統(tǒng)的日期跟學(xué)生的出生日期做比較，得到學(xué)生的年齡。具體步驟：第一步，在D1單元格輸入標題學(xué)生年齡；第二步，在D2單元格編輯公式“=IF(LEN(C2)=18,DATEDIF(MID(C2,7,4)&”-”&MID(C2,11,2)&”-”&MID(C2,13,2),TODAY(),”Y”),DATEDIF(“19”&MID(C2,7,2)&”-”&MID(C2,9,2)&”-”&MID(C2,11,2),TODA-Y(),”Y”)”后確認，即求得學(xué)生的年齡；第

12、三步使用自動填充功能引用公式，得出所有學(xué)生的年齡（如圖3）。圖3.學(xué)生基本信息表 （三）數(shù)據(jù)比較及引用 在南京電大開放教育學(xué)籍管理過程中，經(jīng)常會對多張數(shù)據(jù)表進行比較，或者引用其他數(shù)據(jù)表中的數(shù)據(jù)，例如前面示例圖一數(shù)據(jù)和圖4數(shù)據(jù)做比較，圖4數(shù)據(jù)見下圖：圖4.學(xué)生所學(xué)專業(yè)信息表截圖圖1為EXCEL中Sheet1工作表的內(nèi)容，圖4為EXCEL中Sheet2工作表的內(nèi)容，現(xiàn)在需要在Sheet1工作表中增加學(xué)生的ZYMC（專業(yè)名稱）字段，如果通過復(fù)制、粘貼來完成，不僅費時費力，而且容易出錯。我們可以借助于VLOOKUP函數(shù)，通過學(xué)號來對兩張工作表的數(shù)據(jù)做比較，并從Sheet2工作表中讀出相同學(xué)號的專業(yè)名稱字段。具體步驟：第一步，在D1單元格輸入標題ZYMC(專業(yè)名稱)；第二步，在D2單元格編輯公式“=VLOOKUP(A2,Sheet2!A1:C14,3,0)”后確認，即從Sheet2工作表中取出與A2單元格相同學(xué)號的專業(yè)名稱；第三步使用自動填充功能引用公式，得出所有學(xué)生的專業(yè)名稱（如圖5）。圖5.學(xué)生基本信息表增加專業(yè)名稱字段截圖以上Exce