利用辦公軟件制作教師科研成果匯編

張　凡,江　晶

(遼寧大學(xué) a.社科處; b.科技處,遼寧 沈陽 110136)

?

利用辦公軟件制作教師科研成果匯編

張凡a,江晶b

(遼寧大學(xué) a.社科處; b.科技處,遼寧 沈陽 110136)

摘要：介紹了常用辦公軟件Word和Excel制作教師科研成果匯編的思路和方法，幫助教師在不需要掌握專業(yè)的編程知識(shí)的情況下就可以制作出效果并不輸于專業(yè)程序的匯編輸出系統(tǒng)。對(duì)于信息化素養(yǎng)較低的科研管理人員而言，可以大幅提高工作效率，并且反向促進(jìn)其提高信息化素養(yǎng)的意識(shí)，從而提高高?？蒲泄芾聿块T整體的信息化水平。

關(guān)鍵詞：Word；Excel；科研成果匯編；科研管理；信息化

將教師的科研成果信息制作成匯編文檔并進(jìn)行發(fā)布，是近年來高?？蒲泄芾眍I(lǐng)域頗具發(fā)展前景的新型管理舉措。所謂教師科研成果匯編，即按一定時(shí)間范圍將科研人員的成果信息進(jìn)行集中展示。其特點(diǎn)是以人為單位，將其在規(guī)定時(shí)間范圍內(nèi)引進(jìn)的科研項(xiàng)目、發(fā)表的學(xué)術(shù)成果(論文、專著、研究報(bào)告等形式)、獲得的科研獎(jiǎng)勵(lì)等信息顯示在同一頁(yè)面上?？蒲谐晒麉R編宏觀上可以便利領(lǐng)導(dǎo)層面對(duì)本?？蒲蟹諊⒊晒?、學(xué)科態(tài)勢(shì)有一個(gè)總體把握，微觀上則是不同院系、不同學(xué)科教師間增進(jìn)了解、加強(qiáng)溝通的有效途徑。在科學(xué)研究日益由個(gè)人行為轉(zhuǎn)變?yōu)閳F(tuán)隊(duì)行為、由學(xué)科單一化演變?yōu)槎鄬W(xué)科交叉網(wǎng)絡(luò)化的大背景下，科研成果匯編的價(jià)值也相應(yīng)地體現(xiàn)出來。但是制作此類匯編文檔，常常需要借助于專業(yè)的計(jì)算機(jī)編程知識(shí)，對(duì)于信息化素養(yǎng)參差不齊的高?？蒲泄芾砣藛T來說具有不小的難度。如果向?qū)I(yè)計(jì)算機(jī)人員求助，會(huì)增加工作周期，且在數(shù)據(jù)的傳輸過程中還可能造成信息的丟失，未必會(huì)使工作變得輕松。通過摸索，找到了一種利用辦公軟件Word和Excel制作科研成果匯編的方法。使用這種方法，僅須幾步操作就能完成全部工作，且不易產(chǎn)生非人為性失誤(如程序運(yùn)行造成的字符丟失或無法識(shí)別)，效率非常高。

1制作方法

1.1基本思路

制作這款系統(tǒng)的基本思路其實(shí)很簡(jiǎn)單，就是利用Word軟件的“郵件合并”功能批量輸出以人為單位的匯編單頁(yè)，而要實(shí)現(xiàn)這一功能，還需要準(zhǔn)備一張包含了所有成果信息的Excel表。只要這兩項(xiàng)工作完成，整個(gè)系統(tǒng)也就完成了，而后面一項(xiàng)工作的困難之處在于，它必須將同一個(gè)人的所有類型的成果信息都放置于Excel表的同一行里。按照數(shù)據(jù)庫(kù)思維，二維表的每一行被理解為一條“記錄”。Excel表中有多少條“記錄”，就會(huì)生成多少?gòu)圵ord頁(yè)面。這就是Word的“郵件合并”功能的內(nèi)涵。因此，制作這款系統(tǒng)的難點(diǎn)有兩個(gè)：一是如何將一個(gè)人的所有數(shù)據(jù)置于同一行作為一條“記錄”；二是如何將所有人的“記錄”匯總到一張Excel表中。根據(jù)數(shù)據(jù)采集方式的不同，解決這個(gè)難點(diǎn)的方法也有所不同。

1.2數(shù)據(jù)采集

制作匯編類材料，首先要解決數(shù)據(jù)采集的問題。數(shù)據(jù)采集的方法不外兩種：一種是由教師填報(bào);另一種是科研管理部門使用已掌握的信息直接制作。前一種方法的優(yōu)點(diǎn)是數(shù)據(jù)量大、內(nèi)容全面，缺點(diǎn)是真實(shí)性和準(zhǔn)確性無法保證；后一種方法的優(yōu)點(diǎn)是可以保證信息的真實(shí)準(zhǔn)確，缺點(diǎn)是數(shù)據(jù)量不足，存在統(tǒng)計(jì)盲區(qū)。具體采取哪種方法可根據(jù)實(shí)際情況而定。

1.2.1以教師填報(bào)數(shù)據(jù)為數(shù)據(jù)源

首先新建1個(gè)名為“信息采集表”的Excel工作簿，將其中的3份Excel表分別命名為“填報(bào)頁(yè)面”、“數(shù)據(jù)頁(yè)面”和“參數(shù)表”?！疤顖?bào)頁(yè)面”用于教師填寫數(shù)據(jù)，“數(shù)據(jù)頁(yè)面”用于對(duì)填報(bào)的數(shù)據(jù)進(jìn)行整理與格式化，“參數(shù)表”用于存放既有數(shù)據(jù)作為引用參數(shù)?！疤顖?bào)頁(yè)面”設(shè)計(jì)表樣，如圖1和圖2所示。

圖13張表重命名

圖2　設(shè)計(jì)填報(bào)表樣

切換到“數(shù)據(jù)頁(yè)面”，將“填報(bào)頁(yè)面”中所有需要錄入的內(nèi)容，即“姓名”、“職稱”、“所在單位”等，以字段的形式置于“數(shù)據(jù)頁(yè)面”的首行。注意一定要與“填報(bào)頁(yè)面”的所有單元格一一對(duì)應(yīng)，如有多篇論文或多個(gè)項(xiàng)目則按“論文1論文名稱”、“論文1發(fā)表刊物”……“項(xiàng)目1項(xiàng)目名稱”、“項(xiàng)目1項(xiàng)目來源”……的形式加以區(qū)分，如圖3所示。

圖3　在“數(shù)據(jù)頁(yè)面”中設(shè)計(jì)列標(biāo)題

這兩步的設(shè)計(jì)思路是由教師在“填報(bào)頁(yè)面”填入個(gè)人成果信息，再通過“數(shù)據(jù)頁(yè)面”對(duì)信息進(jìn)行整理。方法是在“數(shù)據(jù)頁(yè)面”第2行的單元格內(nèi)依次寫入公式來引用“填報(bào)頁(yè)面”中的信息。如在“數(shù)據(jù)頁(yè)面”的A2單元格寫入：=填報(bào)頁(yè)面!D1&""，A3單元格寫入：=填報(bào)頁(yè)面!F1&""1……以此類推，將“填報(bào)頁(yè)面”的全部信息引用到“數(shù)據(jù)頁(yè)面”的第2行中。至此，一條“記錄”就完成了。最后保存工作簿并以教師姓名重新命名，再將所有教師提交的工作簿存放在同一文件夾下，數(shù)據(jù)采集工作就完成了。

1.2.2以既有數(shù)據(jù)為數(shù)據(jù)源

以既有數(shù)據(jù)為數(shù)據(jù)源制作匯編的基本思路是將數(shù)據(jù)分解為一批以教師姓名命名的信息采集表，然后再用與上述同樣的方法將“填報(bào)頁(yè)面”中的內(nèi)容引用到“數(shù)據(jù)頁(yè)面”中的第2行。通常數(shù)據(jù)都是按類別存儲(chǔ)的，如“項(xiàng)目信息”、“論文信息”、“獲獎(jiǎng)信息”等，但不論哪一類別，一定有“姓名”這個(gè)字段[2]，所以以此入手，通過教師姓名就可以從數(shù)據(jù)源中提取該教師的全部科研信息。

以單一查詢條件返回多個(gè)結(jié)果需要用到“INDEX(SMALL(IF)))”這個(gè)經(jīng)典數(shù)組函數(shù)模式。在寫公式之前，首先要做一些準(zhǔn)備工作。這些工作包括制作“參數(shù)表”以及為固定的引用區(qū)域定義“名稱”。具體步驟如下。

首先，將既有數(shù)據(jù)按“人員基本信息”、“論文信息”、“項(xiàng)目信息”、“獲獎(jiǎng)信息”等類型分別錄入“參數(shù)表”。每類信息都要有各自“姓名”字段，如“姓名”、“論文作者”、“項(xiàng)目負(fù)責(zé)人”、“獲獎(jiǎng)?wù)咝彰钡取?/p>

圖4　每類參數(shù)都必須有“姓名”字段

接著為參數(shù)定義“名稱”?！懊Q”的作用其實(shí)就是為引用區(qū)域起個(gè)名字以簡(jiǎn)化公式。具體方法是從每列的第2行(即字段行)開始，向下選中每一類參數(shù)的全部數(shù)據(jù)，以圖4為例，從A2開始選到D列的最后一個(gè)數(shù)據(jù)，再按住Ctrl從E2開始選到I列最后一個(gè)數(shù)據(jù)，以此類推。這里有一個(gè)小技巧：在選中第一個(gè)單元格(如A2)以后，同時(shí)按下Ctrl鍵和Shift鍵，再依次按下鍵盤上的“↓”和“→”，這樣就能夠快速選擇該參數(shù)系的全部數(shù)據(jù)。選中所有參數(shù)的所有數(shù)據(jù)以后，按Ctrl+Shift+F3組合鍵調(diào)出批量設(shè)置“名稱”框，勾選“首行”并確定。以“論文信息”參數(shù)為例，設(shè)置好的“名稱”分別是“論文作者”、“論文名稱”、“發(fā)表刊物”、“CSSCI類別"、“校定級(jí)別”。這樣定義的名稱，其引用的區(qū)域是從每列的第三個(gè)單元格開始(不包含首行)，直到最后一個(gè)數(shù)據(jù)所在的單元格，如圖5所示。

圖5　為參數(shù)批量設(shè)置名稱

批量定義“名稱”雖然簡(jiǎn)便，但存在一個(gè)缺點(diǎn)，那就是無法動(dòng)態(tài)更新數(shù)據(jù)。如果既有數(shù)據(jù)需要不定期被更新，就不能批量地定義“名稱”，而需要使用OFFSET函數(shù)定義動(dòng)態(tài)引用名稱。如要為“發(fā)表刊物”定義動(dòng)態(tài)引用名稱，首先按Ctrl+F3打開“名稱”對(duì)話框，編輯“名稱”名為“發(fā)表刊物”，然后在“引用位置”處寫公式：

=OFFSET(參數(shù)表!$Q$3,0,0,COUNTA(參數(shù)表!$Q:$Q)-1,)

這個(gè)公式的意思是以Q3單元格為起點(diǎn)，動(dòng)態(tài)引用到Q列的最后一個(gè)有數(shù)據(jù)的單元格。將其他參數(shù)列也按此公式分別定義“名稱”，這樣一來，即使增加了新數(shù)據(jù)，查詢函數(shù)也可以自動(dòng)識(shí)別。

圖6　用OFFSET函數(shù)為參數(shù)設(shè)置動(dòng)態(tài)引用名稱

定義好動(dòng)態(tài)名稱之后就可以寫公式了。回到“填報(bào)頁(yè)面”，在“論文信息”-“論文名稱”下的第一個(gè)單元格中寫公式：

=IF(COUNTIF(論文作者,$D$1)>=ROW(1:1),INDEX(論文名稱,SMALL(IF(論文作者=$D$1,ROW(論文作者)-2,4^8),ROW(1:1))),"")

按“Ctrl+Shift+Enter”組合鍵輸入公式[3]，然后在“姓名”處輸入“張三”，會(huì)發(fā)現(xiàn)張三的第一篇論文名稱被提取出來。將公式向下復(fù)制，張三的全部論文名稱都被提取出來。

這個(gè)數(shù)組函數(shù)公式的讀法是：如果“論文作者”名稱所引用的區(qū)域中包含“張三”的數(shù)量大于等于1(隨著公式向下復(fù)制演變?yōu)閺?開始的自然數(shù)列)，就返回與“張三”處于同一行的論文名稱(通過INDEX函數(shù)對(duì)“張三”所在行的行號(hào)進(jìn)行索引)。如果查到的“張三”不止一個(gè)，就對(duì)這些“張三”所在行的行號(hào)進(jìn)行升序排列(SMALL函數(shù))并依次返回每一行的論文名稱(通過INDEX函數(shù)對(duì)所有行號(hào)進(jìn)行索引)，之所以要對(duì)返回的行號(hào)減2(“ROW(論文作者)-2”)，是因?yàn)橹岸x的名稱中并不包含整個(gè)表的前兩行，而INDEX函數(shù)是按引用區(qū)域的相對(duì)行號(hào)進(jìn)行索引的。另外，在內(nèi)層嵌套的IF函數(shù)中，出現(xiàn)了符號(hào)4^8，它的意思是如果“論文作者”中沒有“張三”，就返回整個(gè)表最后一行的行號(hào)。在Excel2003版中，單張表的最大行號(hào)是65535，而4^8這個(gè)符號(hào)的意思是4的8次冪，正是65535。最后一行通常都是沒有內(nèi)容的，因此使用這個(gè)符號(hào)的意思其實(shí)就是通過返回最大的行號(hào)來返回空值。也就是說，4^8這個(gè)符號(hào)既可以替換成65535，也可以替換成空值符號(hào)("")，不論替換成哪一個(gè)，都不會(huì)影響數(shù)據(jù)的提取。

接下來將公式里“論文名稱”分別改為“發(fā)表刊物”、“CSSCI級(jí)別”和“校定級(jí)別”，并分別在對(duì)應(yīng)的單元格用組合鍵輸入數(shù)組公式，會(huì)發(fā)現(xiàn)論文的其他信息也都被提取了出來。再按照同樣的方法將“項(xiàng)目”和“獲獎(jiǎng)”等信息都提取出來。再按上文的方法將這個(gè)信息引用到“數(shù)據(jù)頁(yè)面”的第2行中。至此，“張三”的信息采集表就制作完成了[4]。

接下來的步驟非常關(guān)鍵，那就是將每一個(gè)人的數(shù)據(jù)分別另存為獨(dú)立的Excel表。這項(xiàng)工作依靠手動(dòng)來完成是無法想象的，好在可以借助于VBA代碼。方法是：將所有人員的姓名置于“參數(shù)表”中的某一列(切記姓名不能重復(fù))，如A列，將光標(biāo)置于A1單元格上，然后按Alt+F11進(jìn)入VBA編輯器，雙擊“參數(shù)表”對(duì)象，在代碼窗口輸入以下代碼：

Sub saveas()

Dim i As Integer,xm As String,sr As Range

i = 2←i=2意味著“參數(shù)表”A列中人員姓名從A2開始 Do While Cells(i,"a") <> ""

Set sr = Worksheets("填報(bào)頁(yè)面").Range("d1")

sr.Value = xm

ActiveWorkbook.SaveAs Filename:="d:單表另存” & xm & ".xls"6

i = i + 1

Loop

End Sub

按F5運(yùn)行代碼，Excel會(huì)以A列的所有人員姓名為文件名，在“單表另存”文件夾內(nèi)另存出一批信息采集表。

硝鹽(硝酸鈉、硝酸鉀、亞硝酸鈉)是肉制品中應(yīng)用歷史最久、范圍最廣的添加劑之一，具有發(fā)色、抑菌防腐、抗氧化和增香等功能，即不僅是作為腌肉的發(fā)色劑使產(chǎn)品具有美觀鮮艷的色澤，還對(duì)肉毒桿菌及其他腐敗菌和致病菌有良好的抑制作用，此外還具有抑制脂肪氧化和增強(qiáng)腌制品風(fēng)味的作用，可顯著降低肉品安全風(fēng)險(xiǎn)，延長(zhǎng)肉制品保質(zhì)期[1]。

2數(shù)據(jù)匯總

不管采用哪種方法采集數(shù)據(jù)，按照最初的設(shè)計(jì)，這些數(shù)據(jù)都將被引用到每一張信息采集表的“數(shù)據(jù)頁(yè)面”的第2行作為一條“記錄”?，F(xiàn)在將所有信息采集表的“數(shù)據(jù)頁(yè)面”的“記錄”匯總成一張新的Excel表，如圖7所示。要完成這項(xiàng)工作，同樣需要VBA的幫助。

圖7　將所有“數(shù)據(jù)頁(yè)面”的“記錄”匯總成一張excel表

在存放著所有“信息采集表”的文件夾(如“單表另存”)下新建一個(gè)Excel工作簿，命名為“匯總表”。雙擊打開“匯總表”，按Alt+F11打開VBA編輯器。雙擊左側(cè)的sheet1對(duì)象，打開代碼窗口，將下面的代碼粘貼進(jìn)去。請(qǐng)注意代碼中被放大了的字符，需要根據(jù)具體情況調(diào)整這些字符。

Sub hzwwb()

Dim r As Long,c As Long

r = 1 ←1指“數(shù)據(jù)頁(yè)面”的表頭行數(shù)，本例中只有1行，即字段行

c = 66 ←c指“數(shù)據(jù)頁(yè)面”的列數(shù)，本例中有66列，即66個(gè)字段

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運(yùn)行代碼，再切換到“匯總表”的Sheet1表，會(huì)發(fā)現(xiàn)所有人員的“記錄”已經(jīng)匯總在一起了。

3批量輸出個(gè)人單頁(yè)

這是整個(gè)工作的最后一步：批量輸出個(gè)人單頁(yè)。這里需要用到Word軟件的“郵件合并”功能。前面已經(jīng)說過，“郵件合并”功能要求每個(gè)頁(yè)面對(duì)應(yīng)Excel表的一條“記錄”。以Word 2007或2010版為例，新建一個(gè)文檔并命名為“輸出模板”，在其中繪制一張與“填報(bào)頁(yè)面”一致的表格，然后單擊“郵件”功能區(qū)，在“選擇收件人”下單擊“使用現(xiàn)有列表”，選擇此前已經(jīng)制作好的匯總表，在彈出的“選擇表格”對(duì)話框中選擇“Sheet1”，確定之后，Word文檔與Excel匯總表之間就建立起了關(guān)聯(lián)。

圖8　為Word文檔和Excel匯總表建立關(guān)聯(lián)

單擊“插入合并域”，會(huì)發(fā)現(xiàn)匯總表里的標(biāo)簽全部出現(xiàn)在這里，接下來要做的是將每一個(gè)標(biāo)簽對(duì)應(yīng)地插入到“輸出模板”的表格里。

圖9　為“輸出模板”插入合并域

在這個(gè)環(huán)節(jié)要?jiǎng)h除“姓名”和“職稱”2個(gè)標(biāo)簽，改為在原位置直接插入域(見圖9)，并通過“樣式”功能將“<>”和“<>”2個(gè)域設(shè)置為“標(biāo)題”樣式以用于為匯編生成目錄。這一步必不可少。

插入完畢后，單擊“完成并合并”—“編輯單個(gè)文檔”，在彈出的“合并到新文檔”對(duì)話框里選擇“合并全部記錄”，數(shù)據(jù)就會(huì)按照匯總表的順序，以個(gè)人單頁(yè)的形式輸出一個(gè)Word文檔。Excel匯總表有多少數(shù)據(jù)行，這個(gè)Word文檔就有多少頁(yè)。最后，為該Word文檔插入目錄，再利用PDF虛擬打印機(jī)軟件將其轉(zhuǎn)換為PDF文檔，一份科研成果匯編的電子版就制作完成了。

4結(jié)語

利用辦公軟件制作教師科研成果匯編的思路并不復(fù)雜，但是要求制作者掌握一些基本的Excel函數(shù)以及VBA編程知識(shí)。通過制作實(shí)踐，科研管理人員不僅可以高效完成此項(xiàng)工作，還可以提高軟件應(yīng)用技能，提升管理信息化素養(yǎng)，從而觸類旁通，為更多管理工作制定高效解決方案。

參考文獻(xiàn)

[1]伍昊.你早該這么玩Excel 1、2[M].北京：北京大學(xué)出版社，2011.

[2]Excel Home.Excel函數(shù)與公式實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2008.

[3]Excel Home.別怕，Excel VBA其實(shí)很簡(jiǎn)單[M].北京：人民郵電出版社，2012.

[4]John J P.SQL基礎(chǔ)[M].第2版.北京：清華大學(xué)出版社，2003.

(責(zé)任編輯佟金鍇校對(duì)張凱)

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-)，男，遼寧鞍山人，教授，碩士，碩士生導(dǎo)師，主要從事火電廠熱工自動(dòng)化方面的研究。

作者簡(jiǎn)介：楊柳(1989-),女,遼寧鐵嶺人,碩士研究生。

收稿日期：2014-08-31

中圖分類號(hào)：TP317.1

文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1673-1603(2015)01-0074-06