网店鞋服类进销存实用表格

zcchun

详情见附件
包含连个表格1  出入库及期初盘点表
表格2 实时库存表 主要应用数据透视 技术关键点是 补充字段  计算列  公式为： 实时库存=期初+入库-出库

本表格是以25码至31码 女裤为例 各位老师 有兴趣可以完善补充。

SQL  
“
select "期初",日期,款号,颜色,[25码],[26码],[27码],[28码],[29码],[30码],[31码],0 as [25码3],0 as [26码3],0 as [27码3],0 as [28码3],0 as [29码3],0 as [30码3],0 as [31码3],0 as [25码2],0 as [26码2],0 as [27码2],0 as [28码2],0 as [29码2],0 as [30码2],0 as [31码2] from  [期初$]  union  all  select "入库",日期,款号,颜色,0 as [25码2],0 as [26码2],0 as [27码2],0 as [28码2],0 as [29码2],0 as [30码2],0 as [31码2],[25码],[26码],[27码],[28码],[29码],[30码],[31码],0 as [25码3],0 as [26码3],0 as [27码3],0 as [28码3],0 as [29码3],0 as [30码3],0 as [31码3]   from  [入库$]  union  all  select "出库",日期,款号,颜色,0 as [25码3],0 as [26码3],0 as [27码3],0 as [28码3],0 as [29码3],0 as [30码3],0 as [31码3],0 as [25码2],0 as [26码2],0 as [27码2],0 as [28码2],0 as [29码2],0 as [30码2],0 as [31码2],[25码],[26码],[27码],[28码],[29码],[30码],[31码] from  [出库$]

                                ”
可能出现的问题 1.  当表格出现文本类型的数据  部分单元格会不参与计算  ，解决办法 就是空单元补充数据0 ，然后设置单元格数据类型为数字类型
2.数据源在 文件移存时会出现数据源无效等情况，  解决办法 修改链接名称 为 4月出入库2 如图1 ；找到4月出入库2.xls 文件类型为EXECL文件  如图2、3  ；  确认后如图4  ；任选一个表格 如图5 接下来就是 使用计算字段公式   （25码~31码 是期初表格字段  25码3~31码3 是入库表格字段  25码2~31码2 是出库表字段，其中25码3~31码3， 25码2~31码2  为补充虚拟字段 ）  。至此 已基本完成。感兴趣的童鞋欢迎 加我Q 87951339   相互探讨  欢迎指教!

1	【25】	='25码'+'25码3' -'25码2'
2	【26】	='26码'+'26码3' -'26码2'
3	【27】	='27码'+'27码3' -'27码2'
4	【28】	='28码'+'28码3' -'28码2'
5	【29】	='29码'+'29码3' -'29码2'
6	【30】	='30码'+'30码3' -'30码2'
7	【31】	='31码3' +'31码' -'31码2'

1.    2.     3.     4.   5.