新中職學生系統新生錄入行政區劃碼查錯小助手

全國中等職業學校學生管理信息系統（簡稱為新中職學生系統）是2014年更新后的學生信息管理系統，新生錄入模板（V1.2版本號）也是2014年9月24日更新的。其中“批量自動生成”功能非常實用，可以根據學生的身份證號自動生成：性別、出生日期、行政區劃碼等，給錄入教師帶來了很多方便。但是，凡是參加過這次新生學籍錄入工作的老師，對學籍信息上傳后出現的行政區劃碼錯誤都會印象深刻。這是由于行政區劃碼是不斷調整變化的（地市縣合并或撤消導致行政區劃碼也改變或撤消），而身份證號是唯一不變的，所以根據身份證前六位數自動生成的行政區劃碼就有一些是錯誤的。當我們把采集完信息的新生錄入模板上傳后，數據庫會根據字典里的行政區劃碼去檢驗上傳信息里的區劃碼，如果有字典里不存在的區劃碼出現，就會被標識為錯誤，從而被駁回，要求更改后再重新上傳。這個錯誤問題非常普遍、集中，錄入教師自己很難查找出錯誤的行政區劃碼，所以只能是上傳數據發現錯誤后下載再改正，因此大大延長了數據上傳的時間。為了把這個問題解決在數據上傳之前，筆者制作了區劃碼查錯小助手，有了這個小助手，不僅自己可以輕松發現錯誤代碼，還可以根據區劃碼自動生成戶口所在地，輕松減少錄入工作量。

下面介紹這個小助手的兩種制作方法（兩種制作方法適合不同性質的學校使用，同時也適合不同階段查錯時使用）。

方法一：新建一個工作薄（使用2010版以下的電子表格），建立兩個工作表：“檢查”、“字典”。在“字典”工作表里，完成如下操作：

1.在第一、二行的“CD”列下方設置一個命令按鈕（方法：點擊“視圖”菜單下“工具欄”中的“控件工具箱”，打開“控件工具箱”后，點擊工具箱左邊第一個 “設計模式”圖標，再點擊左數第六個“命令按鈕”圖標，在C列和D列下方拖曳鼠標畫出“命令按鈕”）。右擊“命令按鈕”選擇“屬性”，在“屬性”窗口修改“Caption”右側內容為“獲取行政區劃碼”。

2.雙擊“獲取行政區劃碼”命令按鈕，在打開的代碼窗口中，輸入以下代碼（加粗代碼不用輸入）：

Private Sub CommandButton1_Click（）

Dim m， n， i， j， rows_zdsj， rows_xssj， rows_a As Integer

Dim Msg_hk， name_str As String

Dim find_qh As Boolean

Msg_hk = "沒有找到戶籍所在地的人員有：" & Chr（13）

Msg_hk = Msg_hk & "行 ? ? ?姓名 ? ? ? ? ?身份證號碼 ? " & Chr（13）

Range（"A3：J10000"）.Delete

MsgBox "選擇含有字典的學生數據模板文件"， vbInformation， "提醒"

Dim tmpFileName As String， FileNumber As Integer

Dim myWorkbook， heWorkBook As Workbook， tmpFileList， tmpFileIndex As Long

tmpFileList = Application.GetOpenFilename（"Data File（*.xls），*.xls"， ， "選擇文件"， ， MultiSelect：=False）

If VarType（tmpFileList） = vbBoolean Then

Exit Sub

Else

Application.ScreenUpdating = False

Application.StatusBar = "數據處理中，請稍等..."

Application.DisplayAlerts = False

Set myWorkbook = Workbooks（1）

Set heWorkBook = Workbooks.Open（tmpFileList， 0， vbReadOnly）

rows_zdsj = heWorkBook.Worksheets（"字典"）.[A65536].End（xlUp）.Row

rows_xssj = heWorkBook.Worksheets（"學生基礎信息"）.[A65536].End（xlUp）.Row

heWorkBook.Worksheets（"字典"）.Range（"A1：A" & rows_zdsj）.Copy myWorkbook.Worksheets（"字典"）.Range（"A3"）

heWorkBook.Worksheets（"學生基礎信息"）.Range（"A3：E" & rows_xssj）.Copy myWorkbook.Worksheets（"檢查"）.Range（"A3"）

heWorkBook.Close

Columns（"A：C"）.Select

Selection.NumberFormatLocal = "@"

Range（"A3"）.Select

Columns（"A：A"）.ColumnWidth = 48

Columns（"B：B"）.ColumnWidth = 40

Columns（"C：C"）.ColumnWidth = 13

Range（"B3"） = "行政區域名稱"

Range（"C3"） = "行政區劃碼"

Range（"A3：C3"）.Interior.ColorIndex = 37

j = 4

name_str1 = ""

Do While j <= [A65536].End（xlUp）.Row

name_d = InStr（Range（"A" & j）， "（"）

name_str2 = Range（"A" & j）

Range（"B" & j） = Left（name_str2， name_d - 1）

name_str1 = Right（Range（"A" & j）， 13）

Range（"C" & j） = Left（name_str1， 12）

j = j + 1

Loop

i = 37

name_str = ""

Do While i <= Worksheets（"字典"）.[A65536].End（xlUp）.Row

If Right（Range（"C" & i）， 10） = "0000000000" Then

name_str = Range（"B" & i）

Else

If Right（Range（"C" & i）， 8） <> "00000000" Then

Range（"B" & i） = name_str & Range（"B" & i）

End If

End If

i = i + 1

Loop

Range（"A3"）.Select

Worksheets（"檢查"）.Activate

With Worksheets（"檢查"）

.Range（"F3"） = "戶口所在地"

.Range（"A3：F3"）.Interior.ColorIndex = 35

.Columns（"D：D"）.ColumnWidth = 12

.Columns（"E：E"）.ColumnWidth = 30

.Columns（"F：F"）.ColumnWidth = 40

rows_a = .[A65536].End（xlUp）.Row

For m = 4 To rows_a

find_qh = False

n = 2

Do While Not （find_qh） And n <= Worksheets（"字典"）.[A65536].End（xlUp）.Row

If Left（.Range（"E" & m）， 6） & "000000" = Worksheets（"字典"）.Range（"C" & n） Then

find_qh = True

.Range（"F" & m） = Worksheets（"字典"）.Range（"B" & n）

Else

n = n + 1

End If

If find_qh = True Then GoTo line1

Loop

If Not （find_qh） Then

Msg_hk = Msg_hk & m & " ? ?" & .Range（"A" & m） & " ? " & .Range（"E" & m） & Chr（13）

.Range（"A" & m & "：E" & m）.Interior.ColorIndex = 33

End If

line1：

Next m

End With

MsgBox Msg_hk

End If

Application.StatusBar = False

Application.DisplayAlerts = True

End Sub

點擊工具箱第一個“退出設計模式”圖標。完成“字典”的代碼錄制。

3. 點擊“獲取行政區劃碼”命令按鈕，即可一鍵完成行政區劃碼的檢查與戶口所在地信息的錄入（前提是學生的姓名、身份證號信息已全部錄入，并已點擊“批量自動生成”按鈕生成行政區劃碼）。

執行結果說明：如果姓名等字段以藍色背景顯示，同時“戶口所在地”為空，說明該生自動生成的行政區劃碼是錯誤的。可根據該生學籍檔案信息錄入“戶口所在地”，最后把全部的戶口所在地信息重新復制到數據模板中，同時到“字典”中查詢錯誤信息對應的正確行政區劃碼進行數據模板中區劃碼的修改。

此方法適合錄入身份證信息后使用（尤其適合縣級校或初次錄入基本信息時使用）。因為它不僅可以檢查出錯誤的行政區劃碼，還可以減少我們錄入戶口所在地時的文本信息錄入量。

方法二：新建一個工作薄（使用2010版以下的電子表格），建立兩個工作表：“檢查”、“字典”

一、“字典”工作表的建立

1. 在第一、二行C列和D列下方設置一個命令按鈕（方法同上）。設置“屬性”窗口中“Caption”右側內容為“獲取行政區劃碼”。

2. 雙擊“獲取行政區劃碼”命令按鈕，在打開的代碼窗口中，輸入以下代碼（加粗代碼不用輸入）：

Private Sub CommandButton1_Click（）

Dim i， j， rows_zdsj， rows_xssj， rows_a As Integer

Dim str， str1， str2， name_str As String

Dim find_qh As Boolean

MsgBox "選擇含有字典的學生數據模板文件"， vbInformation， "提醒"

Dim tmpFileName As String， FileNumber As Integer， c As Range

Dim myWorkbook， heWorkBook As Workbook， tmpFileList， tmpFileIndex As Long

tmpFileList = Application.GetOpenFilename（"Data File（*.xls），*.xls"， ， "選擇文件"， ， MultiSelect：=False）

If VarType（tmpFileList） = vbBoolean Then

Exit Sub

Else

Application.ScreenUpdating = False

Application.StatusBar = "數據處理中，請稍等..."

Application.DisplayAlerts = False

Set myWorkbook = Workbooks（1）

Set heWorkBook = Workbooks.Open（tmpFileList， 0， vbReadOnly）

rows_zdsj = heWorkBook.Worksheets（"字典"）.[A65536].End（xlUp）.Row

heWorkBook.Worksheets（"字典"）.Range（"A1：A" & rows_zdsj）.Copy myWorkbook.Worksheets（"字典"）.Range（"A3"）

heWorkBook.Close

Columns（"A：C"）.Select

Selection.NumberFormatLocal = "@"

Range（"A3"）.Select

Columns（"A：A"）.ColumnWidth = 48

Columns（"B：B"）.ColumnWidth = 40

Columns（"C：C"）.ColumnWidth = 13

Range（"B3"） = "行政區域名稱"

Range（"C3"） = "行政區劃碼"

Range（"A3：C3"）.Interior.ColorIndex = 37

j = 4

name_str1 = ""

Do While j <= [A65536].End（xlUp）.Row

name_d = InStr（Range（"A" & j）， "（"）

name_str2 = Range（"A" & j）

Range（"B" & j） = Left（name_str2， name_d - 1）

name_str1 = Right（Range（"A" & j）， 13）

Range（"C" & j） = Left（name_str1， 12）

j = j + 1

Loop

i = 37

name_str = ""

Do While i <= Worksheets（"字典"）.[A65536].End（xlUp）.Row

If Right（Range（"C" & i）， 10） = "0000000000" Then

name_str = Range（"B" & i）

Else

If Right（Range（"C" & i）， 8） <> "00000000" Then

Range（"B" & i） = name_str & Range（"B" & i）

End If

End If

i = i + 1

Loop

Range（"A3"）.Select

End If

Application.StatusBar = False

Application.DisplayAlerts = True

End Sub

點擊工具箱第一個“退出設計模式”圖標。完成“字典”的代碼錄制。

3. 點擊“獲取行政區劃碼”命令按鈕，在“字典”工作表中創建行政區劃碼查詢信息（此命令執行一次即可）。

二、“檢查”工作表的建立

1. 在第一、二行的C列和D列下方創建一個命令按鈕（方法同上），并設置屬性中Caption內容為“開始檢查”。

2. 雙擊“開始檢查”命令按鈕，輸入以下代碼：

Dim m， n， rows_adsj， rows_xssj， rows_a As Integer

Dim str， str1， str2， name_str As String

Dim find_qh As Boolean

Range（"A3：J10000"）.Delete

MsgBox "選擇已錄完信息的學生數據模板文件"， vbInformation， "提醒"

Dim tmpFileName As String， FileNumber As Integer， c As Range

Dim myWorkbook， heWorkBook As Workbook， tmpFileList， tmpFileIndex As Long

tmpFileList = Application.GetOpenFilename（"Data File（*.xls），*.xls"， ， "選擇文件"， ， MultiSelect：=False）

If VarType（tmpFileList） = vbBoolean Then

Exit Sub

Else

Application.ScreenUpdating = False

Application.StatusBar = "數據處理中，請稍等..."

Application.DisplayAlerts = False

Set myWorkbook = Workbooks（1）

Set heWorkBook = Workbooks.Open（tmpFileList， 0， vbReadOnly）

rows_xssj = heWorkBook.Worksheets（"學生基礎信息"）.[A65536].End（xlUp）.Row

heWorkBook.Worksheets（"學生基礎信息"）.Range（"A3：E" & rows_xssj）.Copy myWorkbook.Worksheets（"檢查"）.Range（"A3"）

heWorkBook.Worksheets（"學生基礎信息"）.Range（"R3：R" & rows_xssj）.Copy myWorkbook.Worksheets（"檢查"）.Range（"F3"）

heWorkBook.Close

Range（"A3"）.Select

Worksheets（"檢查"）.Activate

With Worksheets（"檢查"）

.Range（"F3"） = "行政區劃碼 "

.Range（"A3：F3"）.Interior.ColorIndex = 35

.Columns（"D：D"）.ColumnWidth = 12

.Columns（"E：E"）.ColumnWidth = 30

.Columns（"F：F"）.ColumnWidth = 30

rows_a = .[A65536].End（xlUp）.Row

For m = 4 To rows_a

find_qh = False

n = 2

Do While Not （find_qh） And n <= Worksheets（"字典"）.[A65536].End（xlUp）.Row

If Range（"F" & m） = Worksheets（"字典"）.Range（"C" & n） Then

find_qh = True

Else

n = n + 1

End If

If find_qh=True Then Goto line1

Loop

If Not （find_qh） Then

Range（"G" & m） = "錯誤"

End If

Line1：

Next m

End With

End If

Application.StatusBar = False

Application.DisplayAlerts = True

End Sub

點擊工具箱第一個“退出設計模式”圖標。完成“檢查”的代碼錄制。

3. 點擊“開始檢查”命令按鈕，選擇數據模板文件（該模板中已錄完新生身份證信息），即可檢查出錯誤的行政區劃碼，對出現“錯誤”的行政區劃碼進行修改后即可上傳。

4. 多次點擊“開始檢查”命令按鈕，即可完成對多個新生模板信息的區劃碼檢查。

執行結果說明：錯誤的行政區劃碼以藍色背景顯示其姓名等信息，同時“行政區劃碼”字段后方顯示“錯誤”兩字。

此方法尤其適合省（地市州）校對所屬各縣市校上報的數據進行二次檢查使用，當然它也適合錄完信息后對行政區劃碼的第二次查錯使用。

兩種查錯方法的區別：方法一是根據身份證號來檢查區劃碼的錯誤，方法二是對已錄入的區劃碼來檢查錯誤。各位老師可根據自己的需要來選擇使用哪種方法查錯。建議實際使用時，在錄入新生的姓名和身份證號后，可用方法一來檢查區劃碼的錯誤與戶口所在地的輸入。在錄完全部信息后，用方法二再次重查一下是否有錯誤的區劃碼。兩次查過后，相信不會再有錯誤的區劃碼。