移动加权平均法excel怎么做_加权平均 excel

图2

我将入库表和销售表放到同一个工作表中(进销存表)，方便使用者对比和录入，商品售价表单独占一个工作表。其中图1左侧部分为入库表，右侧部分为销售表，图2为商品售价表。

通过上图可以看出，无论是入库表还是销售表，都是流水账形式，只要发生业务了，就往上录入就可以，其中黑色文字是需要手工录入的，红色文字都是公式自动生成的。这样做的好处就是对使用者特别方便易懂，发生进货了，那就直接录入商品名称、本次进货价和日期即可；发生销售了，就直接录入商品名称、销售数量和日期即可。使用起来越简单，我们实现起来就越困难，为了达成这个模版，需要解决以下问题：

1. 商品售价是可变的，发生销售时，能自动提取到最新的销售价格，并且之前发生的销售单价不能受影响。一般来说商品的售价会发生变化，比如萝卜10月1号卖5元，10月4号卖6元，那么1号到3号发生销售就应该自动带出5元，4号及以后发生销售就应该带出6元。
2. 商品进货时的成本价也是会变的，要能实时计算出商品最新的加权平均成本价，并且在多次进货中，商品还会在期间发生销售，还要考虑到这个因素对最新商品加权平均成本价的影响。比如9月30号入库10斤萝卜，每斤进价3元，10月2号又入库10斤萝卜，每斤进价4元，那么10月2号当天经营结束时萝卜库存一共20斤，成本单价是3.5元/斤；假设进货日期和金额不变，10月1号销售了5斤萝卜，10月2号销售了1斤萝卜，那么10月2号经营结束时，萝卜库存14斤，成本单价是3.66元/斤。
3. 能实时显示出每天该商品的剩余库存。
4. 要考虑到数据的分散录入，比如萝卜在10月1号分两批入库，当天结束后，不需要使用者先将当日入库数量和金额加和再录入，可以直接分成两笔录入，销售表同理。
5. 考虑到退货（进货退货和销售退货）
6. 考虑到前期数据录入有误如何修改，比如前期商品入库名称、数量、单价日期都可能出现错误，要能方便修改，及时更新结果。
7. 能自动汇总销售及利润情况

需求有了，表格结构也有了，难就难在商品不分开各自建表，而是全在入库表和销售表中混杂录入，虽然大大方便了使用者，但是计算最新成本单价和最新库存等成了难题，图3和图4是将问题1和2实例展示了一下，其中难点是K列和N列的自动公式计算。

图3

图4

大家可以自行计算一下销售表中不同日期的成本单价和销售单价是否自动随情况而变化，以及当天剩余库存是否正确。

上面只是展示了萝卜这一款商品，如果是多款商品多次入库和销售呢？这种情况也是没有问题的，看下图，大家可以自行验证公式计算是否正确。

图5

图6

图7

其中图7为利润汇总表，这个每次只要右击刷新一下即可，非常简单。用的是Excel自带功能数据透视表。我这里用offset函数做了一个数据源的动态引用，随着销售记录的增加，数据透视表会自动将新增的数据汇总计算。

讲到这里大家应该明白这个模版的功能了吧，操作特别简单，不需要人为分开商品种类建表就能动态计算商品最新的库存和成本，并且能自动按月汇总销售情况。纯公式实现，琢磨了好几天才想到这套组合公式，我在网上没有查阅到有类似功能的模版，这个纯公式实现应该是全网首创。公式就在不文章里展示和贴出来了，大家有兴趣的可以私信我。后期我会增加往来结算模块(这个模块比计算成本要简单的多)，本文中没有介绍退货，这个在之后文章中再介绍，如果有人想让商品信息更加详细一些，比如展示出商品类别，存放区域，供应商等属性信息，完全可以新增一个商品基础信息表，用查找引用类函数直接自动带出，实现起来也是特别简单的，考虑到使用群体，这些功能真的无关紧要。

本模版基础大小只有不到30k，在写公式的时候，用的都是2003版也能支持的传统公式，没有使用2010版及之后新增的公式，没有使用VBA，最大限度保证适用范围，理论上可以单表支持100万行数据，后期就算数据量较大，也几乎不会有明显卡顿。想试用模版的将邮箱私信发给我吧，感谢大家的阅读。

本文标签：Excel(98)加权平均(10)工作表(13)商品(41)进销存(10)