Excel2010函數及數據有效性設置在稿件管理中的應用

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

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

Excel2010函數及數據有效性設置在稿件管理中的應用

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

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

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

Excel2010函數；公式；數據有效性；稿件處理

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

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

圖1 “學科分類”表

1 用ROW函數生成稿件編號

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

圖2 “來稿信息”表

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

圖3 “稿件統計”表

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

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

3 用LEN、AND、COUNTIF函數限制輸入不符合要求的數據

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

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

4 用OFFSET 函數實現下拉列表選項自動增減

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

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

5 用INDIRECT函數建立分類下拉列表

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

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

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

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

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

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

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

圖4 分類下拉列表

6 圈釋無效數據

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

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

圖5 圈釋無效數據

7 查詢各相關表中的數據

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

8 分類統計

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

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

9 結語

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

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

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

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

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

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

[編輯] 洪云飛

2016-08-20

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

G23；TP317.3

A

1673-1409(2016)31-0037-04

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