利用Excel完成學生信息查詢的設計

陳佩民+郭慶成

摘 要 學生信息管理是學校學籍管理的重要工作，尤其是中等職業學校更要對學生從入學到畢業的信息進行管理，雖然目前能找到許多免費版的學籍管理軟件，但這些軟件在使用中總是會存在一些不便之處。怎樣使用現有的辦公軟件，挖掘軟件的功能來完成我們的工作，是值得我們深入研究的問題。

關鍵詞 信息管理；數據源；信息查詢

中圖分類號：TP317 文獻標識碼：A 文章編號：1671-7597（2014）03-0065-02

近年來，隨著學校辦學規模的不斷擴大，學生數量增加，學生的各種信息量也不斷增長。為了做好學生信息管理工作，實行學生學籍信息的計算機管理是非常必要的。雖然目前能找到許多免費版的學籍管理軟件，但每個學校都有自己的管理特點，這些軟件在使用中總是會存在一些不便之處。自己動手，用Excel建立適于學校實際需求的學籍信息管理系統是一個不錯的選擇。這里主要探討使用Excel的函數，實現學生信息的查詢的方法。

1 完成學生信息數據庫

根據需求分析結合實際情況，建立“學生信息查詢”工作簿文件。

1）新建Excel工作簿，名稱為“學生信息查詢”，將“Sheet1”更名為“數據源”，主要字段有“學號”、“姓名”、“班別”、“性別”、“身份證號”、“出生年月”、“民族”、“詳細通信地址”、“照片”等。

圖1

2）從身份證號碼中自動輸出男女性別，我們知道18位的身份證男女性別看身份證號的第17位數字，如果第17位是奇數，那么此人為男，如果第17位是偶數，那么此人為女，15位的身份證是看最后一位，在EXCEL中，我們經常遇到從身份證號直接判斷出男女性別，并將“男”、“女”自動輸出到特定的EXCEL格子中。這里我們就需要用到幾個函數的混合使用，分別是MID取數函數、MOD求作商后的余數函數、IF條件函數、LEN長度函數。在C2中輸入“IF（MOD（IF（LEN（E2）=15，MID（A1，15，1），MID（E2，17，1）），2）=1，"男"，"女"）”往下填充。

3）出生日期從身份證中生成，在F2中輸入“=MID（E2，7，4）&"年"&MID（E2，11，2）&"月"&MID（E2，13，2）&"日"”，往下填充。

4）照片一列為照片存放地址，將相片存放在F：＼11文件夾中，以姓名命名，格式為JPG，在I2中輸入公式“="F：＼11＼"&B2&".jpg"”，往下填充。為查詢顯示相片做準備。

5）除學號字段數字格式為“常規”外，其余均為“文本”。

2 根據“數據源”建立“信息查詢”工作表

1）將Sheet2改名為“信息查詢”，標題輸入“學生信息查詢”。

2）在單元格A2、A3、A4、A5、A6、C2、C3、C4中分別輸入“姓名”、“班別”、“地址”、“學制”、“地址”、“性別”，字體、字號如圖2。

3）插入藝術字“請輸入查找的學號”，字體字號如圖2。

4）在B2單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，2）”，同樣方法依次完成B3、B4、B5等單元格的引用。

圖2

3 相片顯示的實現

1）在D3單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，9）”，顯示相片位置。

2）單擊菜單“視圖”→“工具欄”→“繪圖”，打開繪圖工具欄，插入一個矩形，稍微設置一下，調整大小如圖。

3）右擊“信息查詢”工作表標簽，出現的快捷菜單中選擇“查看代碼”，復制下列到代碼框中：

Private Sub Worksheet_Calculate（）

ActiveSheet.Shapes（1）.Select

Selection.ShapeRange.Fill.UserPicture Range（D3"）.Text

End Sub

4 添加“數值調節鈕”控件

1）單擊菜單“視圖”→“工具欄”→“控件工具箱”，打開“控件工具箱”對話框。

2）選擇“數值調節鈕”，畫一個數值調節鈕。

3）右擊“數值調節鈕”，出現的快捷菜單中選擇“設置控件格式”

4）單元格鏈接為“$C$7”，最小值為“13001”，最大值“13010”其余不改。

圖3

5 網絡化，資源共享

圖4

將數據放到局域網上，方便各類人員查詢，實現資源共享，比如放在學校計算機名為SERVER的服務器上。

1）創建共享網盤，在名為“SERVER”的計算機E盤上建立一個名為“共享網盤”的文件夾，右擊該文件夾，在出現的快捷菜單中選擇“共享和安全”，“權限”為“完全控制”，如圖4設置。

2）局域網通暢的情況下，在地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，將“學生信息查詢”工作簿放入“共享網盤”中。

3）創建快捷方式，在需要查詢的計算機地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，在“學生信息查詢”工作簿上右擊，出現的快捷菜單中選擇“創建快捷方式”，在桌面上創建“學生信息查詢”的快捷方式。

至此，我們完成了學生信息查詢的制作。大家從中會發現利用excel強大的功能，能夠很輕松制作出符合學校實際的查詢管理系統，比用系統開發的軟件，在使用上更習慣、修改更方便，更加快捷。

6 文章中所用主要函數說明

6.1 MID函數

主要功能：返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。

使用格式：MID（text，start_num，num_chars）

參數說明：

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

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

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

例如： =MID（"第四屆運動會"，4，3） 結果：運動會

6.2 文本連接符&

文本連接運算符是指可以將一個或多個文連接為一個組合文本的運算符號。比如A1&A2，表示將A1與A2中的字符直接連接在一起，形成一個新的字符串。

例如：A1的值為“中國”，B1的值為“上海市”，在C1中輸入=A1&B1，確認后C1值為“中國上海市”。

參考文獻

[1]邱慧，胡飛.院系本科生成績查詢統計系統的開發[J].西南民族大學學報（自然科學版），2005（04）.

注：郭慶成為通訊作者。endprint

摘 要 學生信息管理是學校學籍管理的重要工作，尤其是中等職業學校更要對學生從入學到畢業的信息進行管理，雖然目前能找到許多免費版的學籍管理軟件，但這些軟件在使用中總是會存在一些不便之處。怎樣使用現有的辦公軟件，挖掘軟件的功能來完成我們的工作，是值得我們深入研究的問題。

關鍵詞 信息管理；數據源；信息查詢

中圖分類號：TP317 文獻標識碼：A 文章編號：1671-7597（2014）03-0065-02

近年來，隨著學校辦學規模的不斷擴大，學生數量增加，學生的各種信息量也不斷增長。為了做好學生信息管理工作，實行學生學籍信息的計算機管理是非常必要的。雖然目前能找到許多免費版的學籍管理軟件，但每個學校都有自己的管理特點，這些軟件在使用中總是會存在一些不便之處。自己動手，用Excel建立適于學校實際需求的學籍信息管理系統是一個不錯的選擇。這里主要探討使用Excel的函數，實現學生信息的查詢的方法。

1 完成學生信息數據庫

根據需求分析結合實際情況，建立“學生信息查詢”工作簿文件。

1）新建Excel工作簿，名稱為“學生信息查詢”，將“Sheet1”更名為“數據源”，主要字段有“學號”、“姓名”、“班別”、“性別”、“身份證號”、“出生年月”、“民族”、“詳細通信地址”、“照片”等。

圖1

2）從身份證號碼中自動輸出男女性別，我們知道18位的身份證男女性別看身份證號的第17位數字，如果第17位是奇數，那么此人為男，如果第17位是偶數，那么此人為女，15位的身份證是看最后一位，在EXCEL中，我們經常遇到從身份證號直接判斷出男女性別，并將“男”、“女”自動輸出到特定的EXCEL格子中。這里我們就需要用到幾個函數的混合使用，分別是MID取數函數、MOD求作商后的余數函數、IF條件函數、LEN長度函數。在C2中輸入“IF（MOD（IF（LEN（E2）=15，MID（A1，15，1），MID（E2，17，1）），2）=1，"男"，"女"）”往下填充。

3）出生日期從身份證中生成，在F2中輸入“=MID（E2，7，4）&"年"&MID（E2，11，2）&"月"&MID（E2，13，2）&"日"”，往下填充。

4）照片一列為照片存放地址，將相片存放在F：＼11文件夾中，以姓名命名，格式為JPG，在I2中輸入公式“="F：＼11＼"&B2&".jpg"”，往下填充。為查詢顯示相片做準備。

5）除學號字段數字格式為“常規”外，其余均為“文本”。

2 根據“數據源”建立“信息查詢”工作表

1）將Sheet2改名為“信息查詢”，標題輸入“學生信息查詢”。

2）在單元格A2、A3、A4、A5、A6、C2、C3、C4中分別輸入“姓名”、“班別”、“地址”、“學制”、“地址”、“性別”，字體、字號如圖2。

3）插入藝術字“請輸入查找的學號”，字體字號如圖2。

4）在B2單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，2）”，同樣方法依次完成B3、B4、B5等單元格的引用。

圖2

3 相片顯示的實現

1）在D3單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，9）”，顯示相片位置。

2）單擊菜單“視圖”→“工具欄”→“繪圖”，打開繪圖工具欄，插入一個矩形，稍微設置一下，調整大小如圖。

3）右擊“信息查詢”工作表標簽，出現的快捷菜單中選擇“查看代碼”，復制下列到代碼框中：

Private Sub Worksheet_Calculate（）

ActiveSheet.Shapes（1）.Select

Selection.ShapeRange.Fill.UserPicture Range（D3"）.Text

End Sub

4 添加“數值調節鈕”控件

1）單擊菜單“視圖”→“工具欄”→“控件工具箱”，打開“控件工具箱”對話框。

2）選擇“數值調節鈕”，畫一個數值調節鈕。

3）右擊“數值調節鈕”，出現的快捷菜單中選擇“設置控件格式”

4）單元格鏈接為“$C$7”，最小值為“13001”，最大值“13010”其余不改。

圖3

5 網絡化，資源共享

圖4

將數據放到局域網上，方便各類人員查詢，實現資源共享，比如放在學校計算機名為SERVER的服務器上。

1）創建共享網盤，在名為“SERVER”的計算機E盤上建立一個名為“共享網盤”的文件夾，右擊該文件夾，在出現的快捷菜單中選擇“共享和安全”，“權限”為“完全控制”，如圖4設置。

2）局域網通暢的情況下，在地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，將“學生信息查詢”工作簿放入“共享網盤”中。

3）創建快捷方式，在需要查詢的計算機地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，在“學生信息查詢”工作簿上右擊，出現的快捷菜單中選擇“創建快捷方式”，在桌面上創建“學生信息查詢”的快捷方式。

至此，我們完成了學生信息查詢的制作。大家從中會發現利用excel強大的功能，能夠很輕松制作出符合學校實際的查詢管理系統，比用系統開發的軟件，在使用上更習慣、修改更方便，更加快捷。

6 文章中所用主要函數說明

6.1 MID函數

主要功能：返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。

使用格式：MID（text，start_num，num_chars）

參數說明：

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

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

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

例如： =MID（"第四屆運動會"，4，3） 結果：運動會

6.2 文本連接符&

文本連接運算符是指可以將一個或多個文連接為一個組合文本的運算符號。比如A1&A2，表示將A1與A2中的字符直接連接在一起，形成一個新的字符串。

例如：A1的值為“中國”，B1的值為“上海市”，在C1中輸入=A1&B1，確認后C1值為“中國上海市”。

參考文獻

[1]邱慧，胡飛.院系本科生成績查詢統計系統的開發[J].西南民族大學學報（自然科學版），2005（04）.

注：郭慶成為通訊作者。endprint

摘 要 學生信息管理是學校學籍管理的重要工作，尤其是中等職業學校更要對學生從入學到畢業的信息進行管理，雖然目前能找到許多免費版的學籍管理軟件，但這些軟件在使用中總是會存在一些不便之處。怎樣使用現有的辦公軟件，挖掘軟件的功能來完成我們的工作，是值得我們深入研究的問題。

關鍵詞 信息管理；數據源；信息查詢

中圖分類號：TP317 文獻標識碼：A 文章編號：1671-7597（2014）03-0065-02

近年來，隨著學校辦學規模的不斷擴大，學生數量增加，學生的各種信息量也不斷增長。為了做好學生信息管理工作，實行學生學籍信息的計算機管理是非常必要的。雖然目前能找到許多免費版的學籍管理軟件，但每個學校都有自己的管理特點，這些軟件在使用中總是會存在一些不便之處。自己動手，用Excel建立適于學校實際需求的學籍信息管理系統是一個不錯的選擇。這里主要探討使用Excel的函數，實現學生信息的查詢的方法。

1 完成學生信息數據庫

根據需求分析結合實際情況，建立“學生信息查詢”工作簿文件。

1）新建Excel工作簿，名稱為“學生信息查詢”，將“Sheet1”更名為“數據源”，主要字段有“學號”、“姓名”、“班別”、“性別”、“身份證號”、“出生年月”、“民族”、“詳細通信地址”、“照片”等。

圖1

2）從身份證號碼中自動輸出男女性別，我們知道18位的身份證男女性別看身份證號的第17位數字，如果第17位是奇數，那么此人為男，如果第17位是偶數，那么此人為女，15位的身份證是看最后一位，在EXCEL中，我們經常遇到從身份證號直接判斷出男女性別，并將“男”、“女”自動輸出到特定的EXCEL格子中。這里我們就需要用到幾個函數的混合使用，分別是MID取數函數、MOD求作商后的余數函數、IF條件函數、LEN長度函數。在C2中輸入“IF（MOD（IF（LEN（E2）=15，MID（A1，15，1），MID（E2，17，1）），2）=1，"男"，"女"）”往下填充。

3）出生日期從身份證中生成，在F2中輸入“=MID（E2，7，4）&"年"&MID（E2，11，2）&"月"&MID（E2，13，2）&"日"”，往下填充。

4）照片一列為照片存放地址，將相片存放在F：＼11文件夾中，以姓名命名，格式為JPG，在I2中輸入公式“="F：＼11＼"&B2&".jpg"”，往下填充。為查詢顯示相片做準備。

5）除學號字段數字格式為“常規”外，其余均為“文本”。

2 根據“數據源”建立“信息查詢”工作表

1）將Sheet2改名為“信息查詢”，標題輸入“學生信息查詢”。

2）在單元格A2、A3、A4、A5、A6、C2、C3、C4中分別輸入“姓名”、“班別”、“地址”、“學制”、“地址”、“性別”，字體、字號如圖2。

3）插入藝術字“請輸入查找的學號”，字體字號如圖2。

4）在B2單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，2）”，同樣方法依次完成B3、B4、B5等單元格的引用。

圖2

3 相片顯示的實現

1）在D3單元格輸入公式“=VLOOKUP（E7，數據源！A1：I11，9）”，顯示相片位置。

2）單擊菜單“視圖”→“工具欄”→“繪圖”，打開繪圖工具欄，插入一個矩形，稍微設置一下，調整大小如圖。

3）右擊“信息查詢”工作表標簽，出現的快捷菜單中選擇“查看代碼”，復制下列到代碼框中：

Private Sub Worksheet_Calculate（）

ActiveSheet.Shapes（1）.Select

Selection.ShapeRange.Fill.UserPicture Range（D3"）.Text

End Sub

4 添加“數值調節鈕”控件

1）單擊菜單“視圖”→“工具欄”→“控件工具箱”，打開“控件工具箱”對話框。

2）選擇“數值調節鈕”，畫一個數值調節鈕。

3）右擊“數值調節鈕”，出現的快捷菜單中選擇“設置控件格式”

4）單元格鏈接為“$C$7”，最小值為“13001”，最大值“13010”其余不改。

圖3

5 網絡化，資源共享

圖4

將數據放到局域網上，方便各類人員查詢，實現資源共享，比如放在學校計算機名為SERVER的服務器上。

1）創建共享網盤，在名為“SERVER”的計算機E盤上建立一個名為“共享網盤”的文件夾，右擊該文件夾，在出現的快捷菜單中選擇“共享和安全”，“權限”為“完全控制”，如圖4設置。

2）局域網通暢的情況下，在地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，將“學生信息查詢”工作簿放入“共享網盤”中。

3）創建快捷方式，在需要查詢的計算機地址欄內輸入“＼＼SERVER”，后回車確認，打開“共享網盤”，在“學生信息查詢”工作簿上右擊，出現的快捷菜單中選擇“創建快捷方式”，在桌面上創建“學生信息查詢”的快捷方式。

至此，我們完成了學生信息查詢的制作。大家從中會發現利用excel強大的功能，能夠很輕松制作出符合學校實際的查詢管理系統，比用系統開發的軟件，在使用上更習慣、修改更方便，更加快捷。

6 文章中所用主要函數說明

6.1 MID函數

主要功能：返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。

使用格式：MID（text，start_num，num_chars）

參數說明：

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

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

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

例如： =MID（"第四屆運動會"，4，3） 結果：運動會

6.2 文本連接符&

文本連接運算符是指可以將一個或多個文連接為一個組合文本的運算符號。比如A1&A2，表示將A1與A2中的字符直接連接在一起，形成一個新的字符串。

例如：A1的值為“中國”，B1的值為“上海市”，在C1中輸入=A1&B1，確認后C1值為“中國上海市”。

參考文獻

[1]邱慧，胡飛.院系本科生成績查詢統計系統的開發[J].西南民族大學學報（自然科學版），2005（04）.

注：郭慶成為通訊作者。endprint