利用Excel提高卷煙噴碼錄入準確率

張亮

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

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

中圖分類號：TP311 文獻標識碼：A 文章編號：1009-3044（2018）02-0199-02

1 概述

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

2 卷煙噴碼及錄入準確率問題

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

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

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

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

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

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

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

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

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

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

增加一列，命名日期判斷（圖2中的F列），輸入下面的公式。公式的邏輯是：如果A2單元格還沒有輸入， 則不判斷；否則截取前5位數(shù)字組合成完整的日期，然后進行日期判斷，如果正確返回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單元格還沒有輸入， 則不判斷；否則截取后2位數(shù)字進行判斷，在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ù)字不重復驗證

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

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

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

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

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

以上就是利用Excel輔助錄入驗證的說明，實現(xiàn)方式相對于VBA來說較為簡單。通過本方法的實踐驗證，噴碼錄入準確率可以達到或接近99.9%的水平。

參考文獻：

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