巧用Excel實現(xiàn)學生成績分學期打印

摘 要：Excel具有很強的數(shù)據(jù)管理和分析功能，要想充分發(fā)揮其數(shù)據(jù)管理和分析功能，就得按規(guī)定的數(shù)據(jù)格式組織數(shù)據(jù)，但這種便于數(shù)據(jù)管理和分析的數(shù)據(jù)格式往往不能滿足我們的打印需求。本文以貴州電子信息職業(yè)技術學院學生成績卡打印為例，介紹如何通過Excel 函數(shù)來實現(xiàn)特殊報表的打印。

關鍵詞：excel；數(shù)組公式；特殊報表；成績打印

學生成績卡是職業(yè)院校學生求職和學籍檔案中的必備材料之一，而且成績卡的打印不像畢業(yè)證書打印那樣一次性批量完成，成績卡的打印既有批量打印，也有零散打印。如果用傳統(tǒng)的郵件合并功能來實現(xiàn)，需要將學生的基本信息和所有學科的成績合并成一行放在數(shù)據(jù)源表中，工作量相當大，而且每個班級的學科往往不同，一個班級要做一個模板，不利于成績數(shù)據(jù)的統(tǒng)一管理和查詢。下面將詳細介紹通過Excel函數(shù)、條件格式和宏命令來實現(xiàn)學生成績卡的打印方法。

1 數(shù)據(jù)準備

（1）添加“學生基本信息”工作表，將成績打印所需的學生基本信息整理成如圖1所示格式，該表可根據(jù)需要任意排序，各列的列名必須與圖1所示完全一致。

（2）添加“成績表”工作表，將學生成績表整理成如圖2所示的格式，該表必須以學號學期列為主要關鍵字進行排序，各列的順序必須與圖2所示完全一致。

圖1

圖2

2 成績卡制作

（1）添加“成績卡”工作表，設計成績卡打印樣式如圖3所示（為了節(jié)省空間，此處隱藏了部分行），其中每個學生的具體信息保持為空。在U10單元格中輸入數(shù)字1備用。

（2）將A1：Q27單元格區(qū)域設置為打印區(qū)域。

（3）引用學生基本信息表中的學號，在L2單元格中輸入公式：=INDIRECT（"學生基本信息！r"&U10+1&"c"&MATCH（"學號"，學生基本信息！A1：F1，0），0）。

（4）引用其他基本信息的方法和引用學號的方法相同，只需把公式中的學號改為要引用的列名即可。

（5）引用當前學生第1學期的成績信息，選擇A5：E15單元格區(qū)域，在編輯欄中輸入：=OFFSET（成績表！$B$1，MATCH（$L$2&"1"，成績表！$A$1：$A$793，0）-1，0，SUM（（成績表！$A$2：$A$793=$L$2&"1"）*1），5），按組合鍵。公式中雙引號包含的1表示引用第1學期成績，$A$1：$A$793表示成績表中有數(shù)據(jù)的區(qū)域。

（6）選擇相應的單元格區(qū)域，重復（5）的操作引用其他學期的成績信息，注意要將公式中雙引號包含的1改為對應的學期。

3 修飾成績卡

由于每學期所修課程數(shù)不相同，當成績卡模板中的行數(shù)多于學期課程數(shù)時，多于的行就會顯示#N/A錯誤；當學期課程數(shù)只有1門時，所有行都會顯示相同結果；如圖4所示。為了避免這種情況，可以通過條件格式來修飾表格，操作方法如下：

（1）選擇A5：E15單元格區(qū)域，單擊功能區(qū)中的【開始】-〉【樣式】-〉【條件格式】-〉【新建規(guī)則】打開【新建規(guī)則格式】對話框，選擇【使用公式確定要設置格式的單元格】，在【為符合此公式的值設置格式】框中輸入：=ISNA（$A5），再單擊【格式】按鈕打開【設置單元格格式】對話框，將字體顏色設置為白色，單擊【確定】按鈕關閉所有對話框。

（2）重復（1）的操作，將公式改為：=$A5=$A4完成對第1學期成績的修飾。

（3）重復（1）、（2）的操作，依次完成對其他學期成績的修飾。操作過程中要注意單元格區(qū)域的選擇和公式的變化。

4 瀏覽成績卡

（1）單擊功能區(qū)中的【開發(fā)工具】-〉【控件】-〉【插入】，在表單控件工具箱中單擊【數(shù)值調節(jié)鈕】，在“成績卡”工作表的U11單元格處拖動鼠標畫一個數(shù)值調節(jié)鈕。

（2）右擊數(shù)值調節(jié)鈕在彈出的菜單中單擊【設置控件格式】命令。在【控制】選項卡的【單元格鏈接】引用框中輸入U10，再單擊【確定】按鈕。

（3）單擊數(shù)值調節(jié)鈕的增加和減少按鈕即可順序瀏覽成績卡。

5 連續(xù)打印成績卡

（1）單擊功能區(qū)中的【開發(fā)工具】-〉【控件】-〉【插入】，在表單控件工具箱中單擊【按鈕】，在“成績卡”工作表的空白處拖動鼠標畫一個按鈕。

（2）在打開的【指定宏】對話框中單擊【新建】按鈕。在【代碼】窗口中輸入如下代碼。

Sub 按鈕1_Click（）

x=InputBox（"請輸入起始序號："）

y=InputBox（"請輸入終止序號："）

For i=x To y Step 1

Range（"u10"）=i

Sheets（"成績卡"）.PrintOut

Next i

End Sub

（3）右擊按鈕可以將按鈕文字改為“連續(xù)打印”。

（4）單擊【連續(xù)打印】按鈕，根據(jù)提示輸入打印起止序號即可批量打印成績卡。

6 零散打印成績卡

（1）在“學生基本信息”工作表中添加一個【打印當前學生成績】按鈕，在【代碼】窗口中輸入如下代碼。

Sub 學生基本信息_按鈕1_Click（）

Sheets（"成績卡"）.Range（"u10"） = ActiveCell.Row - 1

Sheets（"成績卡"）.PrintOut

End Sub

（2）利用查找功能找到要打印成績的學生學號或姓名，單擊【打印當前學生成績】按鈕即可打印當前選中學生的成績卡。

用這種方法實現(xiàn)數(shù)據(jù)的管理和打印，可以在基礎表（這里指學生基本信息表和學生成績表）中管理和維護數(shù)據(jù)，相關的修改會自動反應在打印報表中。在產生新的數(shù)據(jù)時，直接將新數(shù)據(jù)添加到基礎表的后面或者覆蓋基礎表中原來的數(shù)據(jù)就可以得到新的打印報表。從而在Excel中實現(xiàn)了數(shù)據(jù)管理和報表打印的分離，不但可以制作各種復雜的特殊報表，還可以大幅度提高工作效率。

作者簡介：陳開華（1983，11-），男，貴州省桐梓縣，本科，辦公自動化