无惧商品代码Excel一步到位识别代码和统计数据

平淡

通过分析上述代码可以知道，统计销售额就是将代码转换为“对应数量*商品单价”的形式，最后再依次相加。比如员工张三周一的销售额A6B5C，那他的实际销售额就是3×9.9+6×14.8+5×12.8=182.5。

首先获取员工张三的周销量数据，插入一个J 列，在J 2 单元格中输入公式“="="&B2&C2&D2&E2&F2&G2&H2”，可以得到“=3A6B5C6A3B10C3A6B6D6A3C11D3A6B7D6A3B12D6A9B8C”数据组合，下拉公式后可以得到其他员工的周销量数据组合（图2）。

接着将上述数据转换为类似“=数量*商品代码+下一个数量*商品代码”的形式，通过观察可以发现，这个转换实际上只需将代码中的字母替换为“*字母+”，比如3A转换为“3*A+”的形式即可。转换工作可以在Word中实现，依次将J2到J4单元格中的数据粘贴到Word里，接着点击“查找和替换”，点击“更多”，在“查找内容”处输入“^$”（不含外双引号，下同，表示查找任意字母），“替换为”处输入“*^&+”（^&表示查找内容，可以点击“特殊格式→查找内容”直接输入）。然后点击“全部替换”，即可将粘贴的内容全部转换为类似“3*A+6*B+5*C”的格式，在每行的末尾会多出一个“+”，需要手动删除（图3）。

返回Excel，依次将上述转换后的内容粘贴到K 2：K4区域，接下来只需将其中的代码替换为对应的单价就可以求和了。替换过程可以通过自定义公式实现，不过自定义公式不支持直接使用字母C和字母R（C、R是Excel默认保留的字符），因此我们还需要使用“查找和替换”功能将商品代码中的字母C先全部替换为CC（在设置商品代码时可以不使用C和R，这样就无需再次转换）（图4）。

现在使用自定义公式将商品代码转换为单价。依次点击“公式→定义名称”，建立一个名为“A”的新名称，“ 引用位置”设置为“=Sheet1！$N$2”，即A商品的单价，因为这里通过公式进行引用，所以更改单价后会同步更新（图5）。

操作同上，依次新建B、C C（不要用C）、D名称，完成后返回工作表，这时可以看到在K2：K4区域已经自动完成销售额的计算。定位到I2单元格，输入公式“=K 2”并下拉，最后将J列、K列隐藏即可。我们可以将上述文件保存为模板备用，以后统计周销售额时，只要复制J列的数据到Wo r d里，完成替换后再将其复制到K列就可以自动完成销售额的统计了（图6）。

上述操作需要借助Word的“查找和替换”功能，而且还需要替换字母C，操作步骤比较多。其实对VBA比较熟悉的朋友还可以通过自制脚本来实现自动统计，原理如下：

1.由于我们需要在J 2 ： J 4 区域通过连接符获取周销售数据组合，因此先要在J 2 单元格中输入公式“= B 2 & C 2 & D 2 & E 2 & F 2 &G2&H2”并下拉填充到J4单元格。

2.复制J2：J4区域中的内容，依次点击“开始→粘贴→选择性粘贴→数值”，将其粘贴到K 2：K4区域，接着使用Replace函数将K 2：K4区域中的A替换为“*9.9+”（B、C、D的替换方法类似），这样可以一步实现替换，这步通过VBA代码实现。

3.在L2单元格中输入公式“="="& K 2&" 0 "”，这里使用连接符将“=”、“K 2”、“0”连接起来，变为类似“=3* 9.9+8*12.8+0”的标准公式形式。因为经过上述替换会在每行数据的结尾多出一个“+”，所以这里把它和“=”及“0”连接起来，使之成为公式。

4.选择L 2：L4区域中的数据并复制，依次点击“开始→粘贴→选择性粘贴→数值”，将其粘贴到M2：M4区域，此时在M2：M4区域就会自动根据公式完成销售额的统计，这个也是通过VBA代码来完成（图7）。

明白了原理后登录“http：//dwz.date/ctSt”（提取碼： sqbs）下载所需的脚本，然后返回Excel窗口并按下“Alt+F11”组合键，将下载到的代码粘贴到代码编辑窗口。代码含义请参考文件中的注释，具体的单元格区域和代码（A、B、C、D）替换请根据实际情况进行修改。这样以后在统计销售数据时，只需运行该脚本就会在M2：M4区域自动计算销售额（图8）。