Excel2010函數(shù)及數(shù)據(jù)有效性設置在稿件管理中的應用

孫紅 (長江大學計算機科學學院，湖北 荊州 434023)

萬紹華 (長江大學期刊社，湖北 荊州 434023)

Excel2010函數(shù)及數(shù)據(jù)有效性設置在稿件管理中的應用

孫紅 (長江大學計算機科學學院，湖北 荊州 434023)

萬紹華 (長江大學期刊社，湖北 荊州 434023)

Excel2010函數(shù)公式眾多，合理地使用這些函數(shù)公式可以顯著地提高工作效率，同時有效地降低由于人工等因素而出現(xiàn)的誤差。利用Excel函數(shù)及數(shù)據(jù)有效性設置，可以創(chuàng)建方便快捷的個性化的稿件管理方法。根據(jù)稿件管理中的實際情況，從實例出發(fā)，探討了Excel2010函數(shù)及數(shù)據(jù)有效性設置在稿件管理中的應用。

Excel2010函數(shù)；公式；數(shù)據(jù)有效性；稿件處理

在期刊工作中，稿件管理是最費時費力的一個環(huán)節(jié)。現(xiàn)有的大多數(shù)稿件處理軟件基本上能夠滿足期刊的要求，但個性化不足，有的因存在系統(tǒng)參數(shù)設置不合理、各角色之間溝通不暢、系統(tǒng)網(wǎng)站操作性和友好性不足等問題而產(chǎn)生困擾[1]。隨著辦公自動化的廣泛普及，特別是Excel得到了廣泛應用[2]，除了簡單功能及常見的函數(shù)如求和、平均值、計數(shù)、最大值和最小值等外，也涉及到函數(shù)庫中功能更強大的函數(shù)，如“邏輯”類和“查找與引用”類等。筆者結(jié)合稿件管理中的實例，介紹利用Excel函數(shù)及數(shù)據(jù)有效性設置來進行稿件管理，既經(jīng)濟實用，又能滿足個性化的稿件管理要求。

《長江大學學報(自科版)》醫(yī)學稿件管理時涉及到以下3個Excel工作表：學科分類表、來稿信息表、稿件統(tǒng)計表，各表的結(jié)構(gòu)如圖1、圖2、圖3所示。

圖1 “學科分類”表

1 用ROW函數(shù)生成稿件編號

在“來稿信息”表中，根據(jù)“收稿時間”自動生成稿件編號，其格式為收稿時間+空格+序號。首先在“收稿時間”列中輸入收稿時間，然后在A2單元格中輸入公式“=B2&”“&ROW(B2)-1”即可生成此稿件的唯一編號，其他稿件的編號只需在輸入完相應的收稿日期后拖動填充柄自動填充。

圖2 “來稿信息”表

2 用MID函數(shù)提取身份證號中的出生年份

圖3 “稿件統(tǒng)計”表

由于身份證號的第7到10位表示出生年份，這里可以使用MID函數(shù)提取作者的的出生年份。

在“來源信息”表中，身份證號數(shù)據(jù)存放在G列，出生年份存放在單元格區(qū)域I列，則先選定I2單元格，然后輸入公式“=MID(G2,7,4)”并按回車鍵，其他出生年份數(shù)據(jù)拖動填充柄即可。

3 用LEN、AND、COUNTIF函數(shù)限制輸入不符合要求的數(shù)據(jù)

稿件登記時，“身份證號”的長度必須為18位且不能重復，這時就可用LEN、AND、COUNTIF函數(shù)來實現(xiàn)。

在“來稿信息”表中，身份證號數(shù)據(jù)存放在單元格區(qū)域G2:G100，則先選定G2:G100，然后單擊“數(shù)據(jù)”選項卡，再單擊“數(shù)據(jù)有效性”下拉按鈕，選擇“數(shù)據(jù)有效性”命令，在打開的對話框的“設置”選項卡的“允許”欄中，選擇“自定義”選項，然后在“公式”文本框中輸入公式“=AND(LEN(G2)=18，COUNTIF(G:G，G2)=1)”，最后 單擊“確定”即可。

4 用OFFSET 函數(shù)實現(xiàn)下拉列表選項自動增減

通常情況下，為了保證輸入數(shù)據(jù)的準確性,有些字段的內(nèi)容采用下拉列表輸入。但當下拉列表中的選項需要增加或減少時，Excel并不能隨之變化，必須重新設置。這時可以使用OFFSET函數(shù)實現(xiàn)下拉列表選項的自動增減[3]。

在“來稿信息”表中，作者的職稱選項數(shù)據(jù)存放在H列，H1單元格作為標題欄使用，真正的數(shù)據(jù)源從H2 開始。選定“作者職稱”列中的單元格，打開“數(shù)據(jù)有效性”對話框，在“允許”欄中選擇“序列”，“來源”框中輸入公式“=OFFSET(H＄2，0，0，COUNTA(H:H)，1)”，單擊“確定”按鈕。

5 用INDIRECT函數(shù)建立分類下拉列表

使用INDIRECT函數(shù)可以建立多級分類下拉列表[4]。

如“稿件信息”表中要輸入稿件所屬的一級與二級學科名稱，下面筆者以此為例介紹具體的操作步驟：

在“學科分類”表(見圖1)中，第1行為一級學科名，第2行起為各一級學科下的二級學科名。

1)定義名稱。首先將一級學科名所在區(qū)域A1:F1定義名稱為“一級學科”，然后分別將A2:A12、B2:B15、C2:C14、D2:D6、E2:E11、F2:F7定義為相應的名稱，如基礎醫(yī)學、臨床醫(yī)學、護理學、預防醫(yī)學、中醫(yī)學、藥學。

2)在“稿件信息”表中的“一級學科”D列的單元格區(qū)域中，建立一個下拉列表。

3)選中“二級學科”E列的相應單元格區(qū)域，打開“數(shù)據(jù)有效性”對話框，選中“序列”選項后，在“來源”欄中輸入公式“=INDIRECT(D2)”，單擊“確定”按鈕。

4)在“一級學科”D列相應的單元格中選擇輸入一級學科名后，單擊“二級學科”E列對應的單元格，所選一級學科下的二級學科名顯示在下拉列表中，如圖4所示。

圖4 分類下拉列表

6 圈釋無效數(shù)據(jù)

在稿件管理的過程中，如果要對不符合輸入要求的數(shù)據(jù)進行標識，可以先在“數(shù)據(jù)有效性”對話框中設置限制條件，然后圈釋不符合限制條件的數(shù)據(jù)[5]。

如輸入身份證號時未限制長度，輸入結(jié)束后才設置身份證必須為18位，如要標記不符合要求的身份證號，則可以先選中身份證號所有的列，然后選擇“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”下拉按鈕，選擇“圈釋無效數(shù)據(jù)”即可，如圖5所示。

圖5 圈釋無效數(shù)據(jù)

7 查詢各相關表中的數(shù)據(jù)

在“審稿信息”表中只有稿件編號，如果需要稿件文題，則可以通過VLOOKUP函數(shù)查詢得到，這時只需在單元格C2中輸入公式“=VLOOKUP(A2，來稿信息!＄C＄2:＄C＄100，3，false)” 并按回車鍵，其他稿件文題數(shù)據(jù)拖動填充柄即可。與該稿件相關的其他信息，也可通過稿件編號用VLOOKUP函數(shù)查詢得到。

8 分類統(tǒng)計

在統(tǒng)計表中，首先查詢到稿件登記表中不同的統(tǒng)計項目名稱，然后按不同的項目分別統(tǒng)計。如要在“稿件統(tǒng)計”表中統(tǒng)計各一級學科的稿件數(shù)，可以先將“來稿信息”表中的“一級學科”列數(shù)據(jù)復制到“稿件統(tǒng)計”表A列中，然后在B列的B2單元格中輸入公式“=INDEX(A:A,MATCH(，INDEX(COUNTIF(B＄1:B1，A＄1:A＄100)，),))&""”，并拖動填充柄得到不重復的一級學科名，最后在C列的C2單元格中輸入公式“=COUNTIF(來稿信息!＄D＄2:＄D＄500，B2)”，拖動填充柄分別得到各一級學科的稿件總數(shù)。

參照以上方法可以進行其他項目的稿件總數(shù)的統(tǒng)計處理，如作者職稱、作者學位、作者出生年份、基金類別、稿件狀態(tài)等。

9 結(jié)語

Excel函數(shù)具有強大的數(shù)據(jù)處理功能，合理地使用函數(shù)既可保證原始數(shù)據(jù)輸入的準確性，又可從原始數(shù)據(jù)中提取需要的數(shù)據(jù)并進行各種統(tǒng)計分析，從而提高稿件管理速度。

[1]楊俏煒. Excel基本操作技巧研究[J]. 電子技術(shù)與軟件工程,2016(7):72.

[2] 呂丹，俞紅衛(wèi)，朱建育. 勤云稿件處理系統(tǒng)使用初期常見問題及對策[J]. 學報編輯論叢,2014(s):239～242.

[3] 才讓當周. Excel函數(shù)在學分制教務管理中的應用實踐[J]. 青海師范大學學報(自然科學版),2012,28(3):39～41.

[4] 涂志芳. 運用Excel函數(shù)編排高校校園運動會秩序冊[J].體育學刊，2013，20(5)：64～67.

[5] 郭姍姍. 試論如何實現(xiàn)Excel數(shù)據(jù)錄入的有效性[J].數(shù)字技術(shù)與應用，2014(5)：211.

[編輯] 洪云飛

2016-08-20

孫紅(1967-)，女，碩士，副教授，現(xiàn)主要從事計算機軟件方面的教學與研究工作；E-mail:sunhong@yangtzeu.edu.cn。

G23；TP317.3

A

1673-1409(2016)31-0037-04

[引著格式]孫紅，萬紹華. Excel2010函數(shù)及數(shù)據(jù)有效性設置在稿件管理中的應用[J].長江大學學報(自科版),2016,13(31):37～40.