巧用Excel實現學生成績分學期打印

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

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

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

1 數據準備

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

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

圖1

圖2

2 成績卡制作

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

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

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

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

（5）引用當前學生第1學期的成績信息，選擇A5：E15單元格區域，在編輯欄中輸入：=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表示成績表中有數據的區域。

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

3 修飾成績卡

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

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

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

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

4 瀏覽成績卡

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

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

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

5 連續打印成績卡

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

（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）右擊按鈕可以將按鈕文字改為“連續打印”。

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

6 零散打印成績卡

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

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

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

Sheets（"成績卡"）.PrintOut

End Sub

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

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

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