浅析Excel进一销一存系统

张丹丹

摘要：Exce12010是微软Omce较为成熟的版本组件之一，该项目使用它制作了一套进一销一存系统。系统中根据目前通用条码EAN-13：标准版的前缀部分关联主键对应的商品地区，该项目大量使用了查找函数，并比较各类查找函数间参数的使用特点。查询卡更挖掘了多种查询方式，例如：自动生成商品名、自动显示库存、月销售量等内容。最后将系统界面进行保护和隐藏公式，使之完善成一套具有实际效用的小型贸易系统。

关键词：查找函数;条形码;多条件求和;保护隐藏单元格

中图分类号：TP311 文献标识码：A

文章编号：1009-3044（2019）30-0059-02

该系统包含四张表，分别为：查询卡、商品信息表、进货和销售。四张表中数据相互关联，键值分明，界面友好，查询简易。图1为该系统中的查询卡：

鉴于主要知识点，我从《商品信息表》《查询卡》《保护隐藏单元格》三个方面对该系统做出论述：

1商品信息表

图2为《商品信息表》截图，此表中<序号>使用公式=IF（D2=””，““，ROW（）-1）自动生成，注意row（）省略参数，则返回所在单元格的行号;否则返回所在参数区域的第一行单元格的行号。通常可使用row（）自动生成行号，或在条件格式中结合Modl（）生成不同的单元格格式。<商品分类>采用<数据有效性>——<序列>进行设置，序列可引用也可写入。

1.1 Match（）查找原则及论述

此表中<产地>使用index（）结合match（）自动生成，前提是先从网上下载条形码表，三位条形码前缀表整理后如下图：

图3为条形码前缀表，因为后面将match_type设置为一1，所以我将该表按<前缀终>降位排序。查找原则是首先使用MATCH（100kup_value，lookup_array，match_type）根据前缀终定位行号。然后再使用index（）提供的列号定位查找地区。具体函数如下图：

分析图4中match（）参数：第一个参数提取所输入条形码的前三位作为查找参数;第二个参数即查找区域，为《条形码前缀》表的<前缀终>列，即图3的B列;第三个参数match_type设置为-1，即查找>=lookup_value的最小数值。这样就可以定位到图3中B列的行号，即查找参数对应的行号。再根据index（）提供的列号，即《条形码前缀》表的地区列，也就是图3的第3列，依据该函数行列交叉原则，定位到具体的地区。

Match（）注意点：它是excel的查找函数之一，它必须在单列多行或单行多列中进行定位;它返回的行号或列号是相对查找区域的数值;它的参数match_type为l，lookup_array需升序排序，match_type为-1，按降序排序。

Match（）参数设置根据mach_type分为三类：

match_type=1，查找<=lookup_value的最大数值，lookup_ar-ray需升序排列

match_type=0，查找=lookup_value的第一个数值，无需排序

match_type=-1，查找>=lookup_value的最小数值，look-up_array需降序排列

这里我理解为，match_type设置为-1，便查找>=look-up_value的最小数值，因为它是查找大于等于lookup_value的最小数值，所以第二个参数一查找范围必须降序排列，因此当它找到第一个小于数，便终止这个小于数，定位临上的那个大于数。同样的，当参数match_type设置为l，即查找<=look-up_value的最小数值，所以第二个参数查找范围按升序排列，因为它找小于数，所以当第一个大于数出现，函数终止，这样便定位在上一个小于数的行号上。

1.2库存计算

表中<库存>使用sumif（）实现，根据《商品信息表》条形码分别计算《进货》表和《销售》表的数量差，具体函数如图5：

分析图5公式：sumif（）中将《进货》表B列，即<条形码>作为第一个参数：条件范围;选择《商品信息表》条形码单元格作为第二参数：条件;第三参数：求和范围为《进货》表<数量>列。同样方法求出销售数量。在此公式中，求和范围均为整列相对引用，例如《进货》表或《销售》表中I：I列，原因是这两张表都需要不断添加行记录。

2《查询卡》

查询卡界面效果如图1，界面制作采用左上边框浅色双线的和右下边框深色双线达到单元格突出的效果;反之，左上边框深色单线和右下边框浅色做出单元格凹进的效果。界面右上方花朵是从网上下载的位图，调整它位置大小后得到如图效果。

2.1 VLookup（）查找原则及论述

此《查询卡》中<输入或选择条形码>相对应显示框中的条形码是使用数据有效性引用《商品信息表》中<条形码>列完成。<商品名>、<商品分类>、<产地>、<品牌><、售价>等等的自动生成数据是通过使用VLOOKUP0实现的，例如<商品名>的显示过程如图6所示：

图6公式分析：vlookup（）将表中《查询卡》条形码作为第一参数，即查找值;《商品信息表》的D：J列作为查找区域;返回《商品信息表》D：J列区域中第2列内容，即商品名;第四個参数设定为精确匹配。

在这里，再将查找函数vlookup（）与match（）做参数比较：Match（）必须在单列多行或单行多列中进行定位，因此查找区域除了排序不需做其他设置，而且match（）查找一般需结合其他函数进行，如index（）。vlookup（）实际应用中通常也涉及两张数据表，首先两张表关联的查找值必须格式统一，如均为文本或数值或日期等格式，若不同，可将文本改成数值，方法可使用公式乘以1的方法。其次，我们知道vlookup（）第一个参数通常选定第一张表的一个主键为查找值;第二个参数为查找区域，通常在第二张表中框选，我们必须把该表中和第1参数相关联的主键值放置查找区域的首列;第三个参数返回所查找单元格的内容，在这里也是相对查找区域的列号;最后一个参数是精确或模糊匹配，设置精确匹配是不需要排序工作的。

2.2 Sumifs（）实现月销售量、额

最后<月销售量><月销售额>使用SUMIFS（）实现，该函数为多条件求和，以<月销售量>为例，在此为两个条件求和：筛选年月、筛选条形码。公式如图7所示：

图7公式分析：sumifs（）第一个参数为求和范围，在此为《销售》表中I列，即<数量>列;第二个参数可视为一组，为条件范围和条件：在此分别为《销售》表中L列，即<年月>列和《查询卡》中<选择年月>对应的日期单元格;第三个参数为一组：《销售》表中c列，即<条形码>列和《查询卡》中<输入或选择条形码>对应的条形码单元格。

这里我们将sumif（）和sumifs（）做参数对比：sumif（）三个参数分别为条件范围、条件、求和范围，sumifs（）参数分别为求和范围、（条件范围1、条件1）、（条件范围2、条件2）……做多可以达到127个条件组。这两个函数的条件参数，必须使用双引号括起来，同时这两个条件求和函数均可使用通配符：？或。，如果条件本身为？或*，需要在？或*前输入符号～，当然，所有的符号必须在双引号内，例如”～？”。

3保护隐藏单元格

将工作表保护和隐藏公式才能使系统更加完善，操作更加清晰，数据更加安全。

在此，以《查询卡》为例（阅读下文可参见图1），使用excel制作完成系统，我们可以将查询卡所有单元格保护和隐藏公式，这样原始数据便不得修改，呈现只读状态，当然这里除了<输入或选择条形码>对应的条形码单元格和<选择年月>对应的日期单元格，因为这两项作为实时查询条件。制作过程中，我们首先将整张查询卡选中，其次在<设置单元格格式>对话框中选择<保护>选项卡，去掉<锁定>、<隐藏>复选框前的钩，这项工作是为了解除整张表格的锁定和隐藏。然后按住Ctrl键，选中所有不连续的且需要保护和隐藏公式的单元格，回到<保护>选项卡，勾选<锁定>、<隐藏>复选框，这样就标志这些选中的单元格需要进行锁定和隐藏。最后鼠标选中该表中任一单元格即可，选择<保护工作表>，默认选项下按确定即可，当然也可以为表格设置密码。

《商品信息表》《进货》《销售》根据实际使用按同步骤操作，因为涉及不断往此三表中添加记录，因此仅设置自动计算列和自动生成列。

4结束语

Excel不局限于常规的存储及处理数据，经常会碰到较大数据的对比、查找等处理问题，作为目前大部分平台的数据导入后备军，excel在导入导出过程中的运用效率很高，如导出数据的比对关联等，强大的宏运用更是起到例如本文为实际应用中的一个案例，进一销一存为典型的财务运用，在使用过程中，如果关联数据进行查询或自动产生数据，使用查找函数可解決相关问题。又如大量数据对比关联，比如根据工号关联员工信息，本文中介绍的indexO组合MatchO、vlookupO都是excel中很好的查询函数，但在使用过程中注意每个函数的使用要点，并不断挖掘使用方法。