利用办公软件制作教师科研成果汇编
2016-01-27张凡,江晶
张 凡,江 晶
(辽宁大学 a.社科处; b.科技处,辽宁 沈阳 110136)
利用办公软件制作教师科研成果汇编
张凡a,江晶b
(辽宁大学 a.社科处; b.科技处,辽宁 沈阳 110136)
摘要：介绍了常用办公软件Word和Excel制作教师科研成果汇编的思路和方法，帮助教师在不需要掌握专业的编程知识的情况下就可以制作出效果并不输于专业程序的汇编输出系统。对于信息化素养较低的科研管理人员而言，可以大幅提高工作效率，并且反向促进其提高信息化素养的意识，从而提高高校科研管理部门整体的信息化水平。
关键词：Word；Excel；科研成果汇编；科研管理；信息化
将教师的科研成果信息制作成汇编文档并进行发布，是近年来高校科研管理领域颇具发展前景的新型管理举措。所谓教师科研成果汇编，即按一定时间范围将科研人员的成果信息进行集中展示。其特点是以人为单位，将其在规定时间范围内引进的科研项目、发表的学术成果(论文、专著、研究报告等形式)、获得的科研奖励等信息显示在同一页面上。科研成果汇编宏观上可以便利领导层面对本校科研氛围、成果水平、学科态势有一个总体把握，微观上则是不同院系、不同学科教师间增进了解、加强沟通的有效途径。在科学研究日益由个人行为转变为团队行为、由学科单一化演变为多学科交叉网络化的大背景下，科研成果汇编的价值也相应地体现出来。但是制作此类汇编文档，常常需要借助于专业的计算机编程知识，对于信息化素养参差不齐的高校科研管理人员来说具有不小的难度。如果向专业计算机人员求助，会增加工作周期，且在数据的传输过程中还可能造成信息的丢失，未必会使工作变得轻松。通过摸索，找到了一种利用办公软件Word和Excel制作科研成果汇编的方法。使用这种方法，仅须几步操作就能完成全部工作，且不易产生非人为性失误(如程序运行造成的字符丢失或无法识别)，效率非常高。
1制作方法
1.1基本思路
制作这款系统的基本思路其实很简单，就是利用Word软件的“邮件合并”功能批量输出以人为单位的汇编单页，而要实现这一功能，还需要准备一张包含了所有成果信息的Excel表。只要这两项工作完成，整个系统也就完成了，而后面一项工作的困难之处在于，它必须将同一个人的所有类型的成果信息都放置于Excel表的同一行里。按照数据库思维，二维表的每一行被理解为一条“记录”。Excel表中有多少条“记录”，就会生成多少张Word页面。这就是Word的“邮件合并”功能的内涵。因此，制作这款系统的难点有两个：一是如何将一个人的所有数据置于同一行作为一条“记录”；二是如何将所有人的“记录”汇总到一张Excel表中。根据数据采集方式的不同，解决这个难点的方法也有所不同。
1.2数据采集
制作汇编类材料，首先要解决数据采集的问题。数据采集的方法不外两种：一种是由教师填报;另一种是科研管理部门使用已掌握的信息直接制作。前一种方法的优点是数据量大、内容全面，缺点是真实性和准确性无法保证；后一种方法的优点是可以保证信息的真实准确，缺点是数据量不足，存在统计盲区。具体采取哪种方法可根据实际情况而定。
1.2.1以教师填报数据为数据源
首先新建1个名为“信息采集表”的Excel工作簿，将其中的3份Excel表分别命名为“填报页面”、“数据页面”和“参数表”。“填报页面”用于教师填写数据，“数据页面”用于对填报的数据进行整理与格式化，“参数表”用于存放既有数据作为引用参数。“填报页面”设计表样，如图1和图2所示。
图13张表重命名
图2 设计填报表样
切换到“数据页面”，将“填报页面”中所有需要录入的内容，即“姓名”、“职称”、“所在单位”等，以字段的形式置于“数据页面”的首行。注意一定要与“填报页面”的所有单元格一一对应，如有多篇论文或多个项目则按“论文1论文名称”、“论文1发表刊物”……“项目1项目名称”、“项目1项目来源”……的形式加以区分，如图3所示。
图3 在“数据页面”中设计列标题
这两步的设计思路是由教师在“填报页面”填入个人成果信息，再通过“数据页面”对信息进行整理。方法是在“数据页面”第2行的单元格内依次写入公式来引用“填报页面”中的信息。如在“数据页面”的A2单元格写入：=填报页面!D1&""，A3单元格写入：=填报页面!F1&""1……以此类推，将“填报页面”的全部信息引用到“数据页面”的第2行中。至此，一条“记录”就完成了。最后保存工作簿并以教师姓名重新命名，再将所有教师提交的工作簿存放在同一文件夹下，数据采集工作就完成了。
1.2.2以既有数据为数据源
以既有数据为数据源制作汇编的基本思路是将数据分解为一批以教师姓名命名的信息采集表，然后再用与上述同样的方法将“填报页面”中的内容引用到“数据页面”中的第2行。通常数据都是按类别存储的，如“项目信息”、“论文信息”、“获奖信息”等，但不论哪一类别，一定有“姓名”这个字段[2]，所以以此入手，通过教师姓名就可以从数据源中提取该教师的全部科研信息。
以单一查询条件返回多个结果需要用到“INDEX(SMALL(IF)))”这个经典数组函数模式。在写公式之前，首先要做一些准备工作。这些工作包括制作“参数表”以及为固定的引用区域定义“名称”。具体步骤如下。
首先，将既有数据按“人员基本信息”、“论文信息”、“项目信息”、“获奖信息”等类型分别录入“参数表”。每类信息都要有各自“姓名”字段，如“姓名”、“论文作者”、“项目负责人”、“获奖者姓名”等。
图4 每类参数都必须有“姓名”字段
接着为参数定义“名称”。“名称”的作用其实就是为引用区域起个名字以简化公式。具体方法是从每列的第2行(即字段行)开始，向下选中每一类参数的全部数据，以图4为例，从A2开始选到D列的最后一个数据，再按住Ctrl从E2开始选到I列最后一个数据，以此类推。这里有一个小技巧：在选中第一个单元格(如A2)以后，同时按下Ctrl键和Shift键，再依次按下键盘上的“↓”和“→”，这样就能够快速选择该参数系的全部数据。选中所有参数的所有数据以后，按Ctrl+Shift+F3组合键调出批量设置“名称”框，勾选“首行”并确定。以“论文信息”参数为例，设置好的“名称”分别是“论文作者”、“论文名称”、“发表刊物”、“CSSCI类别"、“校定级别”。这样定义的名称，其引用的区域是从每列的第三个单元格开始(不包含首行)，直到最后一个数据所在的单元格，如图5所示。
图5 为参数批量设置名称
批量定义“名称”虽然简便，但存在一个缺点，那就是无法动态更新数据。如果既有数据需要不定期被更新，就不能批量地定义“名称”，而需要使用OFFSET函数定义动态引用名称。如要为“发表刊物”定义动态引用名称，首先按Ctrl+F3打开“名称”对话框，编辑“名称”名为“发表刊物”，然后在“引用位置”处写公式：
=OFFSET(参数表!$Q$3,0,0,COUNTA(参数表!$Q:$Q)-1,)
这个公式的意思是以Q3单元格为起点，动态引用到Q列的最后一个有数据的单元格。将其他参数列也按此公式分别定义“名称”，这样一来，即使增加了新数据，查询函数也可以自动识别。
图6 用OFFSET函数为参数设置动态引用名称
定义好动态名称之后就可以写公式了。回到“填报页面”，在“论文信息”-“论文名称”下的第一个单元格中写公式：
=IF(COUNTIF(论文作者,$D$1)>=ROW(1:1),INDEX(论文名称,SMALL(IF(论文作者=$D$1,ROW(论文作者)-2,4^8),ROW(1:1))),"")
按“Ctrl+Shift+Enter”组合键输入公式[3]，然后在“姓名”处输入“张三”，会发现张三的第一篇论文名称被提取出来。将公式向下复制，张三的全部论文名称都被提取出来。
这个数组函数公式的读法是：如果“论文作者”名称所引用的区域中包含“张三”的数量大于等于1(随着公式向下复制演变为从1开始的自然数列)，就返回与“张三”处于同一行的论文名称(通过INDEX函数对“张三”所在行的行号进行索引)。如果查到的“张三”不止一个，就对这些“张三”所在行的行号进行升序排列(SMALL函数)并依次返回每一行的论文名称(通过INDEX函数对所有行号进行索引)，之所以要对返回的行号减2(“ROW(论文作者)-2”)，是因为之前定义的名称中并不包含整个表的前两行，而INDEX函数是按引用区域的相对行号进行索引的。另外，在内层嵌套的IF函数中，出现了符号4^8，它的意思是如果“论文作者”中没有“张三”，就返回整个表最后一行的行号。在Excel2003版中，单张表的最大行号是65535，而4^8这个符号的意思是4的8次幂，正是65535。最后一行通常都是没有内容的，因此使用这个符号的意思其实就是通过返回最大的行号来返回空值。也就是说，4^8这个符号既可以替换成65535，也可以替换成空值符号("")，不论替换成哪一个，都不会影响数据的提取。
接下来将公式里“论文名称”分别改为“发表刊物”、“CSSCI级别”和“校定级别”，并分别在对应的单元格用组合键输入数组公式，会发现论文的其他信息也都被提取了出来。再按照同样的方法将“项目”和“获奖”等信息都提取出来。再按上文的方法将这个信息引用到“数据页面”的第2行中。至此，“张三”的信息采集表就制作完成了[4]。
接下来的步骤非常关键，那就是将每一个人的数据分别另存为独立的Excel表。这项工作依靠手动来完成是无法想象的，好在可以借助于VBA代码。方法是：将所有人员的姓名置于“参数表”中的某一列(切记姓名不能重复)，如A列，将光标置于A1单元格上，然后按Alt+F11进入VBA编辑器，双击“参数表”对象，在代码窗口输入以下代码：
Sub saveas()
Dim i As Integer,xm As String,sr As Range
i = 2←i=2意味着“参数表”A列中人员姓名从A2开始 Do While Cells(i,"a") <> ""
Set sr = Worksheets("填报页面").Range("d1")
sr.Value = xm
ActiveWorkbook.SaveAs Filename:="d:单表另存” & xm & ".xls"6
i = i + 1
Loop
End Sub
按F5运行代码，Excel会以A列的所有人员姓名为文件名，在“单表另存”文件夹内另存出一批信息采集表。
硝盐(硝酸钠、硝酸钾、亚硝酸钠)是肉制品中应用历史最久、范围最广的添加剂之一，具有发色、抑菌防腐、抗氧化和增香等功能，即不仅是作为腌肉的发色剂使产品具有美观鲜艳的色泽，还对肉毒杆菌及其他腐败菌和致病菌有良好的抑制作用，此外还具有抑制脂肪氧化和增强腌制品风味的作用，可显著降低肉品安全风险，延长肉制品保质期[1]。
2数据汇总
不管采用哪种方法采集数据，按照最初的设计，这些数据都将被引用到每一张信息采集表的“数据页面”的第2行作为一条“记录”。现在将所有信息采集表的“数据页面”的“记录”汇总成一张新的Excel表，如图7所示。要完成这项工作，同样需要VBA的帮助。
图7 将所有“数据页面”的“记录”汇总成一张excel表
在存放着所有“信息采集表”的文件夹(如“单表另存”)下新建一个Excel工作簿，命名为“汇总表”。双击打开“汇总表”，按Alt+F11打开VBA编辑器。双击左侧的sheet1对象，打开代码窗口，将下面的代码粘贴进去。请注意代码中被放大了的字符，需要根据具体情况调整这些字符。
Sub hzwwb()
Dim r As Long,c As Long
r = 1 ←1指“数据页面”的表头行数，本例中只有1行，即字段行
c = 66 ←c指“数据页面”的列数，本例中有66列，即66个字段
Range(Cells(r + 1,"a"),Cells(65536,c)).ClearContents
Application.ScreenUpdating = False
Dim filename As String,wb As Workbook,sht As Worksheet,erow As Long,fn As String,arr As Variant
filename = Dir(ThisWorkbook.Path & "〔.xls")
Do While filename <> ""
If filename <> ThisWorkbook.Name Then
erow = Range("a1").CurrentRegion.Rows.Count + 1
fn = ThisWorkbook.Path & "” & filename
Set wb = GetObject(fn)
Set sht = wb.Worksheets(2) ←这里指需要汇总的是每张工作簿的第2张表，即sheet2
rr = sht.Range(sht.Cells(r + 1,"a"),sht.Cells(65536,"b").End(xlUp).Offset(0,66))
Cells(erow,"a").Resize(UBound(arr,1),UBound(arr,2)) = arr
wb.Close False
End If
filename = Dir
Loop
aplication.ScreenUpdating = True
End Sub7
按F5运行代码，再切换到“汇总表”的Sheet1表，会发现所有人员的“记录”已经汇总在一起了。
3批量输出个人单页
这是整个工作的最后一步：批量输出个人单页。这里需要用到Word软件的“邮件合并”功能。前面已经说过，“邮件合并”功能要求每个页面对应Excel表的一条“记录”。以Word 2007或2010版为例，新建一个文档并命名为“输出模板”，在其中绘制一张与“填报页面”一致的表格，然后单击“邮件”功能区，在“选择收件人”下单击“使用现有列表”，选择此前已经制作好的汇总表，在弹出的“选择表格”对话框中选择“Sheet1”，确定之后，Word文档与Excel汇总表之间就建立起了关联。
图8 为Word文档和Excel汇总表建立关联
单击“插入合并域”，会发现汇总表里的标签全部出现在这里，接下来要做的是将每一个标签对应地插入到“输出模板”的表格里。
图9 为“输出模板”插入合并域
在这个环节要删除“姓名”和“职称”2个标签，改为在原位置直接插入域(见图9)，并通过“样式”功能将“<
插入完毕后，单击“完成并合并”—“编辑单个文档”，在弹出的“合并到新文档”对话框里选择“合并全部记录”，数据就会按照汇总表的顺序，以个人单页的形式输出一个Word文档。Excel汇总表有多少数据行，这个Word文档就有多少页。最后，为该Word文档插入目录，再利用PDF虚拟打印机软件将其转换为PDF文档，一份科研成果汇编的电子版就制作完成了。
4结语
利用办公软件制作教师科研成果汇编的思路并不复杂，但是要求制作者掌握一些基本的Excel函数以及VBA编程知识。通过制作实践，科研管理人员不仅可以高效完成此项工作，还可以提高软件应用技能，提升管理信息化素养，从而触类旁通，为更多管理工作制定高效解决方案。
参考文献
[1]伍昊.你早该这么玩Excel 1、2[M].北京：北京大学出版社，2011.
[2]Excel Home.Excel函数与公式实战技巧精粹[M].北京：人民邮电出版社，2008.
[3]Excel Home.别怕，Excel VBA其实很简单[M].北京：人民邮电出版社，2012.
[4]John J P.SQL基础[M].第2版.北京：清华大学出版社，2003.
(责任编辑佟金锴校对张凯)
Application of Office Software to Make Teachers′
Scientific Research Collection
ZHANG Fan1,JIANG Jing2
(1.Department of Social Sciences;2.Science and Technology Department,
Liaoning University,Shenyang 110136,Liaoning Province)
Abstract：The idea and method using common office software Word and Excel to make the teachers′ scientific research collections were introduced in this paper.The application value of this paper is to offer research management staff without enough professional programming capability a compiled output system close to the professional program.The method can greatly improve the work efficiency of the staff,and in return,promote their information literacy awareness,thus improve the informatization of overall scientific research management in universities.
Key words：Word;Excel;Scientific research collection;Scientific research management;Informatization
DOI：10.13888/j.cnki.jsie(ns).2015.01.018
通讯作者：王存旭(1965-)，男，辽宁鞍山人，教授，硕士，硕士生导师，主要从事火电厂热工自动化方面的研究。
作者简介：杨柳(1989-),女,辽宁铁岭人,硕士研究生。
收稿日期：2014-08-31
中图分类号：TP317.1
文献标识码：A
文章编号：1673-1603(2015)01-0074-06
