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

孫紅 (長(zhǎng)江大學(xué)計(jì)算機(jī)科學(xué)學(xué)院，湖北 荊州 434023)

萬紹華 (長(zhǎng)江大學(xué)期刊社，湖北 荊州 434023)

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

孫紅 (長(zhǎng)江大學(xué)計(jì)算機(jī)科學(xué)學(xué)院，湖北 荊州 434023)

萬紹華 (長(zhǎng)江大學(xué)期刊社，湖北 荊州 434023)

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

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

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

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

圖1 “學(xué)科分類”表

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

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

圖2 “來稿信息”表

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4)在“一級(jí)學(xué)科”D列相應(yīng)的單元格中選擇輸入一級(jí)學(xué)科名后，單擊“二級(jí)學(xué)科”E列對(duì)應(yīng)的單元格，所選一級(jí)學(xué)科下的二級(jí)學(xué)科名顯示在下拉列表中，如圖4所示。

圖4 分類下拉列表

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

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

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

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

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

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

8 分類統(tǒng)計(jì)

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

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

9 結(jié)語

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

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

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

[3] 才讓當(dāng)周. Excel函數(shù)在學(xué)分制教務(wù)管理中的應(yīng)用實(shí)踐[J]. 青海師范大學(xué)學(xué)報(bào)(自然科學(xué)版),2012,28(3):39～41.

[4] 涂志芳. 運(yùn)用Excel函數(shù)編排高校校園運(yùn)動(dòng)會(huì)秩序冊(cè)[J].體育學(xué)刊，2013，20(5)：64～67.

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

[編輯] 洪云飛

2016-08-20

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

G23；TP317.3

A

1673-1409(2016)31-0037-04

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