利用Excel提高卷烟喷码录入准确率

张亮

摘要：针对烟草专卖基层所队查获案件后，需要手工录入大量的32位卷烟喷码，且要保证完全准确的要求下。通过在Excel中进行录入时，借助于函数和公式，以辅助列的方式对输入的喷码进行若干验证，从而减少喷码录错的情况，提高准确率。

关键词：喷码； Excel；辅助列；验证

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2018）02-0199-02

1 概述

卷烟条装喷码（以下简称卷烟喷码）是国家烟草局为规范卷烟生产经营和市场秩序的监管举措之一，喷码的要求是打码到条，打码到户，一户一码，一条一码。卷烟喷码由32位字符（28位阿拉伯数字、4位英文字母），当基层专卖所队查处到真烟案件时，专卖管理人员需要将卷烟喷码进行手工录入。如果是五万元或20件以上的大要案，基层所队人员需要在24小时之内将多品种的卷烟喷码进行录入上报，其工作量可想而知。由于卷烟的品种和数量多，而且时间紧，很容易出现录入错误的情况。虽然录入完成后又进行了核对，但偶尔还是存在一定的错误率。经统计，近两年的卷烟喷码录入准确率在98.9%左右，虽然喷码录入的系统对喷码进行了重复输入验证，但功能上还是不能有效避免错误的出现。为此，我们进行了一些尝试，对于喷码录入错误率的降低达到了一定的效果。在这里分享出来，希望起到抛砖引玉的作用。

2 卷烟喷码及录入准确率问题

卷烟喷码由两行32位字符组成，如图1所示。第一行码段由16位阿拉伯数字组成，前5位是配送的当天日期，后11位中的前9位是卷烟出库分拣时的扫描条码，通过烟草行业一号工程随机派生，最后两位是客户订单卷烟条数，分拣打码时随机产生。第二行码段由4位英文字母和12位阿拉伯数字组成，其中前4位是英文字母SXYC（绍兴烟草英文字母的缩写），后12位是零售客户在国家局生产经营决策系统中的基本信息，由地区代码和零售户代码组成。

通过对录入出错的喷码进行统计分析，出现错误主要由于以下四个方面的原因：

1） 第一行代表年月日的数字容易出错，比如年月日大于当前时间，或者2月份有29或30天等。

2） 第一行末尾两位数超范围，即等于0或大于50。

3） 第一行的16位数字有时录入重复。

4） 第二行地区的字母缩写与后面的数字是对应关系，但由于地名代码重复，采用了大小写的方式进行区分，容易出错。

3 利用Excel函數和辅助列的方式进行验证

针对上述几个问题，结合目前的录入方式，即先输入到Excel，再复制到系统中，我们考虑在Excel中增加验证的方式来提高录入的准确率。Excel功能强大，可以验证录入的方式很多，比如录入后朗读单元格、VBA自定义函数验证等。这里我们使用的是函数与辅助列结合的方式，可以很方便、容易的解决前面遇到的四个问题。

验证录入的界面如图2所示，A列和B列为输入卷烟喷码，C列和D列两列为辅助的验证判断列，后面的E、F、G三列的结果被D列使用，一般是隐藏的，这里显示出来是为了说明公式的计算过程。

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

增加一列，命名日期判断（图2中的F列），输入下面的公式。公式的逻辑是：如果A2单元格还没有输入， 则不判断；否则截取前5位数字组合成完整的日期，然后进行日期判断，如果正确返回True，否则，返回False。

“=IF（ISBLANK（A2），""，IF（ISERROR（TEXT（"201"&LEFT（A2，5），"0000-00-00"）*1），FALSE，TRUE）） ”

3.2 第一行末尾两位数超范围判断

同样增加一列，命名后两位判断（图2中的G列）。公式的逻辑是：如果A2单元格还没有输入， 则不判断；否则截取后2位数字进行判断，在1至50之间，返回True，否则返回False。

“=IF（ISBLANK（A2），""，IF（OR（INT（RIGHT（A2，2））<=0，INT（RIGHT（A2，2））>50），FALSE，TRUE）） ”

3.3 第一行的16位数字不重复验证

这里利用Excel自带的数据验证功能，利用公式“=COUNTIF（A：A，A2&"*"）=1”设置即可

3.4 第二行喷码中地区信息提示

这里我们采用Vlookup函数查询另一张Excel表的方式来获取对应的地区信息或者零售户信息。 Vlookup查询的关键词段是喷码第二行的21-26位，查询的公式参考函数用法即可，查询结果如图2中的C列所示。

再进行综合的输入判断前，再增加一列“长度判断”，对输入的数据长度进行校验（图2中的E列）。这三列（图2中的E、F、G）都是为输入数据做判断的中间结果，最后增加输入一列，命名“输入判断”，这列中的公式将引用前面三列的中间结果，公式如下：

“=IF（ISBLANK（A2），""，IF（E2，IF（G2，IF（H2=FALSE，"提示：最后两位超范围。"，"√"），"提示：日期格式有误，请检查。"），"提示：输入位数不对，请检查。"））”。最后对有问题的提示内容进行标红，采用Excel条件格式，设置公式： “=COUNTIF（$D1，"*提示*"）”设置，即可得到图2所示的验证界面。

以上就是利用Excel辅助录入验证的说明，实现方式相对于VBA来说较为简单。通过本方法的实践验证，喷码录入准确率可以达到或接近99.9%的水平。

参考文献：

[1] Excel Home 编著. Excel 2013函数与公式应用大全[M].北京：北京大学出版社，2016.endprint