|
纯Excel函数【进销存模板】制作（一）讲解，实际并不是很难制作，但一定要有Excel函数基础。老话说的好：“一年之计在于春，一日之计在于晨”，年也快过完了，情人节也过了，该收心工作了，但许多企业没有购买会计软件或购买了会计软件却是简易版的，没有进销存模块，没有折旧模块，没有工资核算模块。这该怎么办？不要紧，我今天来给大家教做一个。 大家跟我学习，我们先从库管材料汇总起步，最后教大家做数量金额式材料账页。先设立第一张表，这张表我起名叫“商品表”，主要记录的是：商品代码、商品名称、规格型号、单位、期初结存数量、期初结存单价和期初结存金额，以及设置商品库存最高限额和最低限额。  这里制作表我就不详细给大家讲了，因为它就是一个二维表，也必须是二维表，要求注意的是：1、这张表不能包含其他表；2、这张表的代码必须是唯一的，不能有重复代码存在；3、表字段必须是不重复的，也就是不能有相同的字段存在；4、字段不能有合并单元格存在；5、行数据记录不能有代码合并单元格；6、单价就是用简单的公式计算出来即可【=ROUND(商品表!$G2/E2,2)】。 制作第二张表，这张表我们叫做“进销表”，表的要求除和上面要求基本一样外，还有就是所有表数据不要随便删除或增加行号和列标题，否则定义名称所使用的函数也会改变行数。你可以用DELETE删除代码，然后将后面的数量和金额删除，将下面的数据上移即可。  现在我们对商品编号开始定义名称，我估计一般朋友都会定义名称和设置数据有效性，方法很简单，这里我做一个演示请大家看看：  E2=IFERROR(INDEX(商品表!B$2:B$300,MATCH(进销表!$D2,商品表!$A$2:$A$300,0)),"")，然后选择带有公式的单元格现有拉到单位即可。 F2=IFERROR(INDEX(商品表!C$2:C$300,MATCH(进销表!$D2,商品表!$A$2:$A$300,0)),"") G2=IFERROR(INDEX(商品表!D$2:D$300,MATCH(进销表!$D2,商品表!$A$2:$A$300,0)),"") 这里特别提示：商品编码需要牢记，你可以将【商品编码】打印出来对照输入，数据最好不要复制粘贴，毕竟您是初学者，以免给带来不必要的麻烦，另外，数据输完后不要在下面继续拉公式存在没有数据的空行，如果你想使用数据透视表汇总，表里也不要存在空单元格（Null），以免数据透视表的【将字段分组】不能使用。解决办法就是选择Ctrl+A , 然后按F5键【定位】——【定位条件】——【空格】，直接输入0后按住Ctrl+Enter填充所有数据表格里存在的空格为0。下面看视频，含有冻结单元格：  第三张表叫做【进销存汇总表】，这个表你就按我的要求做即可。  C3输入9-30，如果不是本年度输入数据，你必须输入2017-9-30，然后选择C3：D3合并单元格。 B6=OFFSET(商品表!A1,1,) C6=IFERROR(INDEX(商品表!B$2:B$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") D6=IFERROR(INDEX(商品表!C$2:C$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") F6=IFERROR(INDEX(商品表!D$2:D$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") G6=IFERROR(INDEX(商品表!E$2:E$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") H6=IFERROR(INDEX(商品表!F$2:F$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") I6=IFERROR(INDEX(商品表!F$2:F$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") J6=SUMPRODUCT((MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000))*(进销存函数汇总!$B6=进销表!$D$2:$D$5000)*进销表!H$2:H$5000) K6=IFERROR(ROUND(L6/J6,2),"") L6=SUMPRODUCT((MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000))*(进销存函数汇总!$B6=进销表!$D$2:$D$5000)*进销表!I$2:I$5000) M6=SUMPRODUCT((MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000))*(进销存函数汇总!$B6=进销表!$D$2:$D$5000)*进销表!J$2:J$5000) N6=IFERROR(O6/M6,"") O6=SUMPRODUCT((MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000))*(进销存函数汇总!$B6=进销表!$D$2:$D$5000)*进销表!L$2:L$5000) P6==IFERROR(G6+J6-M6,"") Q6=IFERROR(R6/P6,"") R6=IFERROR(I6+L6-O6,"") 然后选择B6：R6单元格向下拉填充公式即可。 公式解释：（一）J6=SUMPRODUCT((MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000))*(进销存函数汇总!$B6=进销表!$D$2:$D$5000)*进销表!H$2:H$5000) SUMPRODUCT（）这个公式在这里是多条件求和，(MONTH($C$3)>=MONTH(进销表!$A$2:$A$5000)) 这一层的意思是C3（必须绝对引用）的月份大于等于进销表的$A$2:$A$5000，换句话的意思是取9月份到1月份月份数据（这里你的表数据超过5000行，可以增加行数$A$2:$A$50000或者更多）；(进销存函数汇总!$B6=进销表!$D$2:$D$5000)这里的公式是B6单元格等于进销表!$D$2:$D$5000)，这句代码的意思是取商品编号B6等于进销表!$D$2:$D$5000存在B6商品编码数据；且求进销表!H$2:H$5000和。“*”是且的意思（也有说是和的意思）。也就是说$C$3月份大于等于进销表的$A$2:$A$5000月份且进销存函数汇总!$B6商品编码等于进销表!$D$2:$D$5000)商品编码求进销表!H$2:H$5000本期购入数量。 （二）=IFERROR(INDEX(商品表!E$2:E$100,MATCH($B6,商品表!$A$2:$A$100,0),0),"") IFERROR（）函数是逻辑函数，意思是如果表达式出现错误，它就返回“”值(代表空值Null)。只适用于微软2007以上版本，WPS我没有测试过，估计不兼容，请大家最好安装微软办公软件。 INDEX(商品表!E$2:E$100,MATCH($B6,商品表!$A$2:$A$100,0),0) index+match组合函数就等于vlookup（）函数，使用十分普遍的引用查找函数，我在财务人员不得不会函数里讲过，有兴趣的朋友可以查找学习，这里就不再赘述。 实际到这一步一般企业库管员完全就够了，将打印的汇总表一式2份，一份与领料单和入库单放一起装订，一份交财务部门做账使用。这里如果你是工业企业，你可以在经销存表增加一列，如图所示：  千万不要在已存在的字段里插入【购销单位】，因为我们下面还有一个【明细账查询】表，这个表都是函数做的，插入字段就会改变里面的函数公式且出现错误。还有就是你可以在【商品表】里设置最高库存和最低库存，我们在下一节将会讲到具体应用问题。请注明来自360doc。
|
|
|
