張亮

摘要：針對(duì)煙草專(zhuān)賣(mài)基層所隊(duì)查獲案件后，需要手工錄入大量的32位卷煙噴碼，且要保證完全準(zhǔn)確的要求下。通過(guò)在Excel中進(jìn)行錄入時(shí)，借助于函數(shù)和公式，以輔助列的方式對(duì)輸入的噴碼進(jìn)行若干驗(yàn)證，從而減少噴碼錄錯(cuò)的情況，提高準(zhǔn)確率。

關(guān)鍵詞：噴碼； Excel；輔助列；驗(yàn)證

中圖分類(lèi)號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2018）02-0199-02

1 概述

卷煙條裝噴碼（以下簡(jiǎn)稱(chēng)卷煙噴碼）是國(guó)家煙草局為規(guī)范卷煙生產(chǎn)經(jīng)營(yíng)和市場(chǎng)秩序的監(jiān)管舉措之一，噴碼的要求是打碼到條，打碼到戶，一戶一碼，一條一碼。卷煙噴碼由32位字符（28位阿拉伯?dāng)?shù)字、4位英文字母），當(dāng)基層專(zhuān)賣(mài)所隊(duì)查處到真煙案件時(shí)，專(zhuān)賣(mài)管理人員需要將卷煙噴碼進(jìn)行手工錄入。如果是五萬(wàn)元或20件以上的大要案，基層所隊(duì)人員需要在24小時(shí)之內(nèi)將多品種的卷煙噴碼進(jìn)行錄入上報(bào)，其工作量可想而知。由于卷煙的品種和數(shù)量多，而且時(shí)間緊，很容易出現(xiàn)錄入錯(cuò)誤的情況。雖然錄入完成后又進(jìn)行了核對(duì)，但偶爾還是存在一定的錯(cuò)誤率。經(jīng)統(tǒng)計(jì)，近兩年的卷煙噴碼錄入準(zhǔn)確率在98.9%左右，雖然噴碼錄入的系統(tǒng)對(duì)噴碼進(jìn)行了重復(fù)輸入驗(yàn)證，但功能上還是不能有效避免錯(cuò)誤的出現(xiàn)。為此，我們進(jìn)行了一些嘗試，對(duì)于噴碼錄入錯(cuò)誤率的降低達(dá)到了一定的效果。在這里分享出來(lái)，希望起到拋磚引玉的作用。

2 卷煙噴碼及錄入準(zhǔn)確率問(wèn)題

卷煙噴碼由兩行32位字符組成，如圖1所示。第一行碼段由16位阿拉伯?dāng)?shù)字組成，前5位是配送的當(dāng)天日期，后11位中的前9位是卷煙出庫(kù)分揀時(shí)的掃描條碼，通過(guò)煙草行業(yè)一號(hào)工程隨機(jī)派生，最后兩位是客戶訂單卷煙條數(shù)，分揀打碼時(shí)隨機(jī)產(chǎn)生。第二行碼段由4位英文字母和12位阿拉伯?dāng)?shù)字組成，其中前4位是英文字母SXYC（紹興煙草英文字母的縮寫(xiě)），后12位是零售客戶在國(guó)家局生產(chǎn)經(jīng)營(yíng)決策系統(tǒng)中的基本信息，由地區(qū)代碼和零售戶代碼組成。

通過(guò)對(duì)錄入出錯(cuò)的噴碼進(jìn)行統(tǒng)計(jì)分析，出現(xiàn)錯(cuò)誤主要由于以下四個(gè)方面的原因：

1） 第一行代表年月日的數(shù)字容易出錯(cuò)，比如年月日大于當(dāng)前時(shí)間，或者2月份有29或30天等。

2） 第一行末尾兩位數(shù)超范圍，即等于0或大于50。

3） 第一行的16位數(shù)字有時(shí)錄入重復(fù)。

4） 第二行地區(qū)的字母縮寫(xiě)與后面的數(shù)字是對(duì)應(yīng)關(guān)系，但由于地名代碼重復(fù)，采用了大小寫(xiě)的方式進(jìn)行區(qū)分，容易出錯(cuò)。

3 利用Excel函數(shù)和輔助列的方式進(jìn)行驗(yàn)證

針對(duì)上述幾個(gè)問(wèn)題，結(jié)合目前的錄入方式，即先輸入到Excel，再?gòu)?fù)制到系統(tǒng)中，我們考慮在Excel中增加驗(yàn)證的方式來(lái)提高錄入的準(zhǔn)確率。Excel功能強(qiáng)大，可以驗(yàn)證錄入的方式很多，比如錄入后朗讀單元格、VBA自定義函數(shù)驗(yàn)證等。這里我們使用的是函數(shù)與輔助列結(jié)合的方式，可以很方便、容易的解決前面遇到的四個(gè)問(wèn)題。

驗(yàn)證錄入的界面如圖2所示，A列和B列為輸入卷煙噴碼，C列和D列兩列為輔助的驗(yàn)證判斷列，后面的E、F、G三列的結(jié)果被D列使用，一般是隱藏的，這里顯示出來(lái)是為了說(shuō)明公式的計(jì)算過(guò)程。

3.1 第一行的前5位日期判斷

增加一列，命名日期判斷（圖2中的F列），輸入下面的公式。公式的邏輯是：如果A2單元格還沒(méi)有輸入， 則不判斷；否則截取前5位數(shù)字組合成完整的日期，然后進(jìn)行日期判斷，如果正確返回True，否則，返回False。

“=IF（ISBLANK（A2），""，IF（ISERROR（TEXT（"201"&LEFT（A2，5），"0000-00-00"）*1），F(xiàn)ALSE，TRUE）） ”

3.2 第一行末尾兩位數(shù)超范圍判斷

同樣增加一列，命名后兩位判斷（圖2中的G列）。公式的邏輯是：如果A2單元格還沒(méi)有輸入， 則不判斷；否則截取后2位數(shù)字進(jìn)行判斷，在1至50之間，返回True，否則返回False。

“=IF（ISBLANK（A2），""，IF（OR（INT（RIGHT（A2，2））<=0，INT（RIGHT（A2，2））>50），F(xiàn)ALSE，TRUE）） ”

3.3 第一行的16位數(shù)字不重復(fù)驗(yàn)證

這里利用Excel自帶的數(shù)據(jù)驗(yàn)證功能，利用公式“=COUNTIF（A：A，A2&"*"）=1”設(shè)置即可

3.4 第二行噴碼中地區(qū)信息提示

這里我們采用Vlookup函數(shù)查詢另一張Excel表的方式來(lái)獲取對(duì)應(yīng)的地區(qū)信息或者零售戶信息。 Vlookup查詢的關(guān)鍵詞段是噴碼第二行的21-26位，查詢的公式參考函數(shù)用法即可，查詢結(jié)果如圖2中的C列所示。

再進(jìn)行綜合的輸入判斷前，再增加一列“長(zhǎng)度判斷”，對(duì)輸入的數(shù)據(jù)長(zhǎng)度進(jìn)行校驗(yàn)（圖2中的E列）。這三列（圖2中的E、F、G）都是為輸入數(shù)據(jù)做判斷的中間結(jié)果，最后增加輸入一列，命名“輸入判斷”，這列中的公式將引用前面三列的中間結(jié)果，公式如下：

“=IF（ISBLANK（A2），""，IF（E2，IF（G2，IF（H2=FALSE，"提示：最后兩位超范圍。"，"√"），"提示：日期格式有誤，請(qǐng)檢查。"），"提示：輸入位數(shù)不對(duì)，請(qǐng)檢查。"））”。最后對(duì)有問(wèn)題的提示內(nèi)容進(jìn)行標(biāo)紅，采用Excel條件格式，設(shè)置公式： “=COUNTIF（$D1，"*提示*"）”設(shè)置，即可得到圖2所示的驗(yàn)證界面。

以上就是利用Excel輔助錄入驗(yàn)證的說(shuō)明，實(shí)現(xiàn)方式相對(duì)于VBA來(lái)說(shuō)較為簡(jiǎn)單。通過(guò)本方法的實(shí)踐驗(yàn)證，噴碼錄入準(zhǔn)確率可以達(dá)到或接近99.9%的水平。

參考文獻(xiàn)：

[1] Excel Home 編著. Excel 2013函數(shù)與公式應(yīng)用大全[M].北京：北京大學(xué)出版社，2016.endprint