利用EXCEL管理學(xué)生信息及批量打印的實(shí)現(xiàn)

摘要：MS Excel內(nèi)置了VBA（Visual Basicfor Applications）系統(tǒng)開(kāi)發(fā)工具，利用 VBA 可以方便地調(diào)用和定制主應(yīng)用程序?qū)ο?。該文利用Excel及其函數(shù)和VBA的簡(jiǎn)單編程設(shè)計(jì)，使學(xué)生信息管理和信息卡的批量打印自動(dòng)完成，減少重復(fù)工作，實(shí)現(xiàn)高效辦公。使非專(zhuān)業(yè)編程人員能夠解決復(fù)雜計(jì)算及繁雜數(shù)據(jù)管理。

關(guān)鍵詞：ExcelVBA；學(xué)生信息；批量打印

中圖分類(lèi)號(hào)：TP311.1 文獻(xiàn)標(biāo)識(shí)碼：B 文章編號(hào)：1009-3044（2018）12-0164-03

1 概述

EXCEL是Microsoft office辦公軟件的重要功能模塊之一，Excel 不僅具有強(qiáng)大的電子數(shù)據(jù)表、圖表和數(shù)據(jù)庫(kù)功能，還具有很強(qiáng)的數(shù)據(jù)分析性能、制作報(bào)表等功能，另外它還內(nèi)置了VBA（Visual Basicfor Applications）的系統(tǒng)開(kāi)發(fā)工具，利用 VBA 可以方便地調(diào)用和定制主應(yīng)用程序?qū)ο蟆Ｓ脕?lái)擴(kuò)展Microsoft Office 應(yīng)用程序功能。 使用者根據(jù)通過(guò)VBA 宏代碼的編寫(xiě) ，創(chuàng)建自己的解決方案，自動(dòng)完成重復(fù)工作，使工作效率提高，實(shí)現(xiàn)高效辦公。使非專(zhuān)業(yè)編程人員能夠解決復(fù)雜計(jì)算及繁雜數(shù)據(jù)管理的理想工具軟件。

在學(xué)校學(xué)生學(xué)籍管理中，需要對(duì)學(xué)生信息、學(xué)生課程成績(jī)進(jìn)行處理，對(duì)各種報(bào)表、檔案、通知單等進(jìn)行批量打印或套打。本文通過(guò)利用Microsoft Office 的電子表格軟件 Excel 及其函數(shù)和 VBA 的簡(jiǎn)單編程功能，實(shí)現(xiàn)Excel對(duì)學(xué)生信息管理、成績(jī)處理以及成績(jī)報(bào)告單的打印的方法進(jìn)行探討，從而實(shí)現(xiàn)在同一Excel表格內(nèi)實(shí)現(xiàn)信息、成績(jī)管理與快速打印成績(jī)報(bào)告單的方法，使信息管理更準(zhǔn)確高效，工作效率進(jìn)一步提高。

2 建立學(xué)生信息表

根據(jù)需要建立包括學(xué)生學(xué)號(hào)、姓名、出生日期、身份證號(hào)、課程成績(jī)、成績(jī)排名等信息，建立表如圖1的“信息表”的工作表。

手工錄入或從其他文件中復(fù)制學(xué)號(hào)、姓名、身份證號(hào)、電話、課程成績(jī)等，出生日期、平均成績(jī)和綜合排名等可通過(guò)函數(shù)運(yùn)算進(jìn)行自動(dòng)填充。

2.1 通過(guò)身份證號(hào)自動(dòng)填充出生日期

Excel中MID函數(shù)是Visual Basic和Excel中的一個(gè)字符串函數(shù)，作用是從一個(gè)字符串中截取出指定數(shù)量的字符。DATE函數(shù)是關(guān)于日期的函數(shù)，它返回的是特定日期的序列號(hào)，通常當(dāng)單元格的格式為“常規(guī)”時(shí)，那么返回的結(jié)果是一個(gè)日期格式。

每個(gè)18位身份證號(hào)7～10位為出生年，11～12位為出生月，13～14位為出生日，因而用MID和DATE函數(shù)結(jié)合就能提取出身份證號(hào)碼的出生年月日，在出生日期所在單元格“F3”中輸入函數(shù)公式“=DATE（MID（G3，7，4），MID（G3，11，2），MID（G3，13，2））”后按“回車(chē)”，有時(shí)回車(chē)后“F3”并沒(méi)有變化，還是原公式，這時(shí)需要把F列選中，點(diǎn)擊右鍵，在下拉菜單中選擇“設(shè)置單元格格式”，在“數(shù)字”中選“日期”，再在右側(cè)“類(lèi)型”選項(xiàng)中選所需要的日期類(lèi)型（圖2），然后用“自動(dòng)填充柄”下拉，就可自動(dòng)填寫(xiě)下面的所有對(duì)應(yīng)的出生日期（以后自動(dòng)填充均用“自動(dòng)填充柄”下拉填充）。

2.2 通過(guò)自動(dòng)AVERAGE自動(dòng)計(jì)算平均成績(jī)

Excel中AVERAGE函數(shù)是EXCEL表格中的計(jì)算平均值函數(shù)，參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱(chēng)、數(shù)組或引用，如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。

在平均成績(jī)所在單元格“R3”中輸入函數(shù)公式“=AVERAGE（J3：P3）”，然后回車(chē)，就得到本行學(xué)生的平均成績(jī)。然后用“自動(dòng)填充柄”下拉，就可自動(dòng)填寫(xiě)下面的所有對(duì)應(yīng)的成績(jī)排名。

2.3 通過(guò)函數(shù)RANK自動(dòng)計(jì)算成績(jī)排名

Excel中RANK函數(shù)是排名函數(shù)。rank函數(shù)最常用的是求某一個(gè)數(shù)值在某一區(qū)域內(nèi)的排名。

在成績(jī)排名所在單元格“S3”中輸入函數(shù)公式“=RANK（R3，$R$3：$R$20）”，然后回車(chē)，然后用“自動(dòng)填充柄”下拉，就可自動(dòng)填寫(xiě)下面的所有對(duì)應(yīng)的成績(jī)排名。

3 建立“學(xué)生信息卡”

3.1 建立信息卡

在同一工作簿中，根據(jù)需要建立新的“信息卡”的（圖3）工作表。

3.2 建立信息卡引用控件

在“信息卡”的右側(cè)，根據(jù)制作“信息表”與“信息卡”的引用控件（圖4）。在“M4”單元格中輸入“=MATCH（M2，信息表！A3：A2249，0）+2”函數(shù)。

3.3 “信息卡”對(duì)“信息表”數(shù)據(jù)的引用

在“信息卡”中需要填寫(xiě)姓名的B4單元格中輸入“=INDEX（信息表！C：C，信息卡！$M$4，1）”函數(shù)式，然后回車(chē)就可自動(dòng)填入本行學(xué)生的姓名，同樣在D4單元格中輸入“=INDEX（信息表！D：D，信息卡！$M$4，1）”函數(shù)式，同樣自動(dòng)填寫(xiě)本學(xué)生的性別。根據(jù)本方法填寫(xiě)其他需要填寫(xiě)內(nèi)容，在填寫(xiě)中可以復(fù)制本公式然后只要對(duì)公式中“信息表！C：C”，根據(jù)信息表所在的列對(duì)應(yīng)進(jìn)行修改。在引用的出生日期中可能發(fā)現(xiàn)其中填寫(xiě)的并不是我們需要的“XXXX年X月X日”而是一些數(shù)字，這時(shí)只要右鍵點(diǎn)擊本單元格，選“設(shè)置單元格格式”選項(xiàng)，設(shè)置數(shù)字格式為所需要的日期類(lèi)型（圖2）。另外在打印日期G3單元格中輸入“=TODAY（）”就會(huì)得到打印的及時(shí)日期，不用每次打印時(shí)重新輸入。

3.4 數(shù)值調(diào)節(jié)鈕控件的制作

點(diǎn)擊“開(kāi)發(fā)工具”標(biāo)簽，點(diǎn)“插入”，在“表單控件”中點(diǎn)擊“數(shù)值調(diào)節(jié)鈕”（如圖5），按住右鍵在L2、L3單元格區(qū)域畫(huà)出調(diào)節(jié)鈕（如圖5圖4），點(diǎn)擊畫(huà)好的調(diào)節(jié)按鈕右鍵，選擇“設(shè)置控件格式”，在“設(shè)置控件格式”中點(diǎn)控制選項(xiàng)卡設(shè)置最大值、最小值，在“單元格鏈接”中輸入“$M$2”，即本控件為上下調(diào)節(jié)$M$2單元格數(shù)值。這樣就可用控件的上下箭頭改變$M$2單元格的數(shù)值，隨之也改變信息卡中的可變量。

4 打印信息卡的設(shè)置

4.1 打印信息卡的設(shè)置

在J7：M9單元格區(qū)域建立如圖表格并設(shè)置最大值、最小值、起始序號(hào)、截至序號(hào)等（圖6），其中在最大值的M9單元格中輸入函數(shù)“=MAX（信息表！$A$3：$A$1994）”即自動(dòng)找到“信息表”中的最大有效數(shù)值。

4.2 打印信息卡的設(shè)置

點(diǎn)擊“開(kāi)發(fā)工具”，“插入”，“表單控件”中的“按鈕”圖標(biāo)（如圖7），然后在J6單元格畫(huà)一按鈕，并出現(xiàn)“指定宏”對(duì)話框（如圖7）。點(diǎn)“新建”按鈕，即進(jìn)入VBA程序編寫(xiě)窗口，在此框內(nèi)進(jìn)行“打印當(dāng)前頁(yè)”和“打印全部”的編程，程序編碼（如圖8）。這樣就可通過(guò)圖8控制打印頁(yè)碼和頁(yè)數(shù)。

4.3 打印頁(yè)面設(shè)置

由于需要打印的頁(yè)面和控制頁(yè)面在同一工作表，這樣在打印時(shí)也把控制頁(yè)也打印出來(lái)，我們只要把需要打印的“學(xué)生信息卡”設(shè)置為打印區(qū)域，并根據(jù)實(shí)際需要對(duì)頁(yè)面進(jìn)行設(shè)置。

5 總結(jié)

在實(shí)際工作中，利用word的“郵件合并”功能，對(duì)Excel數(shù)據(jù)源引用到word模板中，實(shí)現(xiàn)證卡等的批量生成和批量打印也是常用的方法，但利用本文Excel管理和批量打印學(xué)生信息與“郵件合并”相比，它具有如下優(yōu)點(diǎn)：

“郵件合并”在使用上必須在Word和Excel兩個(gè)功能軟件交替進(jìn)行，使用起來(lái)較麻煩，而本文的所有操作只在Excel系統(tǒng)中完成，減少了系統(tǒng)之間的切換。

利用本系統(tǒng)學(xué)生信息數(shù)據(jù)更新，信息卡的打印頁(yè)面也隨時(shí)更新，隨時(shí)選擇打印，而Word 的郵件合并只有在所有信息數(shù)據(jù)修改完后，再進(jìn)行合并、再批量打印，并且合并出來(lái)的新文檔打印頁(yè)面不能選擇，不能單頁(yè)打印，只能把所有頁(yè)面進(jìn)行打印，非常浪費(fèi)。

另外與其他采用高級(jí)編程語(yǔ)言實(shí)現(xiàn)的系統(tǒng)相比，利用Excel 和 Vba 技術(shù)，實(shí)現(xiàn)起來(lái)非常方便，即使是沒(méi)有編程經(jīng)驗(yàn)者也可輕松操作。

參考文獻(xiàn)：

[1] 李蓉鑫.基于ExcelVBA的帶相片證書(shū)批量打印系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].電腦編程與維護(hù)，2007（6）.

[2] 張繼瑄.利用ExcelVBA功能實(shí)現(xiàn)帶照片證書(shū)的及時(shí)打印[J].辦公自動(dòng)化，2014（12）.

[3] 張繼瑄.利用EXCEL函數(shù)功能從身份證號(hào)提取學(xué)生基礎(chǔ)學(xué)籍信息[J].電腦知識(shí)與技術(shù)，2014（33）.