跟我制作简易仓库系统：[6]动态进出存帐【百科全说】

操作方法

01

动态月份设计因为后面要A1中放置返回主页按钮，所以在A2单元格填写帐表所属的月份。为了让进出存帐表动态地计算各月的入库、出库数据，必须将A2的格式设置为日期，按年4位月2位显示，如图。设置一个全年各月份的下拉列表，操作步骤是：选中A2单元格，点击数据/数据有效性/设置/选取序列，来源中写入2013年1月,2013年2月,....直到2013年12月，如图。设置好后，A2右下角会出现一个下拉箭头，点箭头，会出现下拉列表，如图。在A3中写入公式 =MIDB(A2,6,3)*1，用来提取A2中的月份值，供入库出库有关列汇总计算数据库中数据的月份条件，使帐表动态化。
02

动态的进出存表结构布局 A列已经使用。从B1开始依次填入列标题：物料编码、货品名称、型号规格、计量单位、期初数量、单价、期初金额、入库数量、入库单价、入库金额、出库数量、出库均价、出库金额、结存数量、结存单价、结存金额。为了让进出存表表头（标题）跟随帐表实际月份变化，可以用公式来实现。将上面有关入库、出库列标题，进行更改如下：入库数量更改为=$A$3&"月入库数量"；入库金额更改为 =$A$3&"月入库金额"；出库数量更改为 =$A$3&"月出库数量"；出库金额更改为 =$A$3&"月出库金额"。更改后帐表计算哪个月的数据，表头将显示为哪个月的入库、出库数量、金额。如图
03

进出存中的物料与资料表同步一般进出存表，当你增加或删除物料后，还需要在进出存或其他汇总表中对物料进行增删。这样非常麻烦，而且容易出错。为了让进出存表能真正像软件系统那样与物料资料表同步，我们可以在B3中写入公式（注意：B2我留作汇总合计行了）：｛=INDEX(资料!A:A,SMALL(IF(资料!A$2:A$1696<>0,ROW(资料!A$2:A$1696)),ROW(1:1)))｝用（ctrl+shift+enter)三键确认，然后下拉公式。物料资料表中有多少行，就下拉多少行，把物料资料中的都提取过来。在C3中写入公式 =IF($B3=0,0,VLOOKUP($B3,资料!$A:$E,2,FALSE))，提取资料中的物料名称。在D3中写入公式 =IF($B3=0,0,VLOOKUP($B3,资料!$A:$E,3,FALSE))，提取资料中的规格型号。在E3中写入公式 =IF($B3=0,0,VLOOKUP($B3,资料!$A:$E,4,FALSE))，提取资料中的计量单位。选中这3个单元格，一起下拉公式。同上的，资料中有多少行，就下拉多少行。如果你是设置的EXCEL“手动计算”（这样可以避免不必要的计算耗用电脑内存），请试着增加或删除一个物料项目，计算一次。你会发现与资料表完全同步了。
04

期初表结构在填写公式之前，我们先来新建一个“期初表”，或者是盘存结转表。为了便于提取结转数据，请将你的表结构设计成这样（如果你是其他样式结构，可以把相应数据复制粘贴进来）： A1:H1作为结转或盘存表的表名 A2:H2分别表头（列标题）序号、物料编码、货品名称、单位、期末数量、单价、期末金额。第三行我用做汇总合计，你也可以不（有些朋友习惯将合计放在表的最后一行）。下面的行全是结转的数据。
05

进出存表提取期初数据使用复制粘贴期初数据的方法是吃亏不讨好的，因为进出存表中的顺序很少与期初表的顺序一致，粘贴过来的期初并不一定是对应物料的真实结转。用公式来做方便省心：进出存期初数量列F3 =IFERROR(VLOOKUP($B3,月初!$B:$K,5,FALSE),0) 进出存期初单价列G3 =IFERROR(VLOOKUP($B3,月初!$B:$K,6,FALSE),0) 进出存期初金额列H3 =IFERROR(VLOOKUP($B3,月初!$B:$K,7,FALSE),0) 选取F3:H3，下拉公式到与B列保持相同的行。
06

定义数据库数据列名称进入数据库表，选中相应列，点击公式/名称管理器/新建/ 物料编码名称bh,引用位置=OFFSET(数据库!$H$2,,,COUNTA(数据库!$B:$B)-1) 入库数量名称rs,引用位置=OFFSET(数据库!$N$2,,,COUNTA(数据库!$B:$B)-1) 入库金额名称rj,引用位置=OFFSET(数据库!$p$2,,,COUNTA(数据库!$B:$B)-1) 出库数量名称cs,引用位置=OFFSET(数据库!$s$2,,,COUNTA(数据库!$B:$B)-1) 出库金额名称cj,引用位置=OFFSET(数据库!$t$2,,,COUNTA(数据库!$B:$B)-1) 所属月份名称yf,引用位置=OFFSET(数据库!$a$2,,,COUNTA(数据库!$B:$B)-1)
07

汇总计算数据库入库、出库数据在进出存表相应列第一个有物料编码的行（3行开始，第2行用做合计行），写入公式：入库数量列I3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0) 入库金额列K3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0) 出库数量列L3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0) 出库金额列N3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0) 出库均价列M3=IFERROR((H3+K3)/(F3+I3),0) 期末数量列U3=IF($B3=0,0,IF(ISERROR(F3+I3-L3),0,(F3+I3-L3))) 期末金额列W3=IF(B5="","",H5+K5-N55) 下拉公式，与物料编码列至同一行。

跟我制作简易仓库系统：[6]动态进出存帐

操作方法

相关推荐