请选择 进入手机版 | 继续访问电脑版

库管易

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫描二维码登录本站

查看: 24628|回复: 18

Excel进销存表格制作教程,自已制作简单的库存管理表格

[复制链接]
  网上经常看见有仓库管理的同行需要库存进销存的Excel表格模版,仗着自己有点会计知识,也在这摆弄摆弄。(这个表格计算的单位成本方法是用移动平均法)。每个货物就是一张工作表,下面介绍单个货物的进销存管理表格制作方法,做好这一个做为样板,如果有多种货物,直接复制工作表就可了。
  首先在表格把标题填上:
  

Excel库存管理表格.png

Excel库存管理表格.png

  在表格上的 A 到 E 列,就是每次进货/销货的时候填写的。进货数量填正数,单价填进价;销货数量填负数,单价填售价。同类型产品产品名称必须一致,另外,交易必须按照时间顺序填写。

  一、交易
  总价格:F3=IF($B3=“”,“”,$D3*$E3)

  简单通过交易数量和交易单价计算总价格,加上的IF公式是为了在没输入B3(产品)之前不显示。

  二、库存
  数量:G3=IF($B3=“”,“”,SUMPRODUCT(($B3=$B$2:$B3)*1,$D$2:$D3))

  如果B3是空值,返回空值;否则,判断从B2单元格一直到该行B列单元格是否等于该行B列单元格(是否同一产品),如果是,加总D列中的数量,得出累计库存。

  单位成本:H3=IF($B3=“”,“”,IF($G3=0,0,ROUND($I3/$G3,2)))

  只是简单通过总成本和数量计算单位成本(取两个小数位),加上两个IF,是为了在没输入B3(产品)之前不显示,和在数量为0时不返回错误。

  总成本:I3{=IF($B3=“”,“”,IF(COUNTIF($B$2:$B3,$B3)=1,0,INDIRECT(“I”&MAX(IF($B$2:$B2=$B3, ROW($B$2:$B2)))))+IF($D3>0,$F3,$D3*INDIRECT(“H”&MAX(IF($B$2:$B2=$B3,ROW($B$2:$B2))))))}

  数组公式,输入的时候不输{},从 = 号开始输到最后的),不按回车,同时按 Ctrl + Shift + 回车。
  把公式拆开分析,就没显得那么复杂了。
  先不考虑数组,最外面的 IF 公式是是为了判断有没有输入B3。如果没有,就返回空格;如果有,就计算后面两个两个 IF 的加总。

  {=IF($B3=“”,“”,
  IF(COUNTIF($B$2:$B3,$B3)=1,0,INDIRECT(“I”&MAX(IF($B$2:$B2=$B3,ROW($B$2:$B2)))))+
  IF($D3>0,$F3,$D3*INDIRECT(“H”&MAX(IF($B$2:$B2=$B3,ROW($B$2:$B2)))))
  )}

  第一个IF是找出上一笔交易后的总成本:IF(COUNTIF($B$2:$B3,$B3)=1,0,INDIRECT(“I”&上次交易的行数))
  COUNTIF($B$2:$B3,$B3)=1,就是如果从$B$2到该行B列,该产品只出现了一次,代表没有上次交易,返回0。
  不然,找出上次交易的行数,通过INDIRECT参数,返回该行I列(总成本)的数值。
  {MAX(IF($B$2:$B2=$B3,ROW($B$2:$B2)))}是用于找出上次交易的行数,我们在下面会分析这个公式。

  第二个IF是考虑该交易该加/减的成本:IF($D3>0,$F3,$D3*INDIRECT(“H”&上次交易的行数))
  就是如果是购货(D列是正数),直接用该次交易的价格(F列);如果是销货,就要判断上次交易后的平均价(H列),在乘以这次交易的数量。由于数量已经是负数,就不用再用减号了。
  要注意的是,由于销货必须找到上次交易后的平均成本,在输入销货之前必须要有同产品交易的进货。

  {MAX(IF($B$2:$B2=$B3,ROW($B$2:$B2)))}就是可以找出上次交易行数的数组公式。
  {=IF($B$2:$B2=$B3,ROW($B$2:$B2))}就是从B2到该行上一行,如果产品和该行的一样,就返回该行的行数。
  比如公式复制到I6,{=IF($B$2:$B5=$B6,ROW($B$2:$B5))},就会看B2,B3,B4,B5是否跟B6一样,假定B3,B5都一样,这一段的IF公式就会返回{0,3,0,5}(0代表FALSE)。再用MAX({0,3,0,5}),就会是最后一行同产品的行数5了。

  三、交易利润
  交易利润:J3=IF(OR($B3=“”,$D3>0),“”,($E3-$H3)*-$D3)
  简单通过交易数量和交易单价和成本计算交易利润,加上的IF公式是为了在没输入B3(产品)之前,和在购货时都不显示。

  四、最后交易
  最后交易:K3=IF(AND($B3<>“”,COUNTIF($B$2:$B3,$B3)=COUNTIF($B:$B,$B3)),“是”,“”)

  加上这列是为了方便直接筛选,得出各个产品的最后库存。判断的方法也比较简单,就是计算从一开始到该行该产品出现的次数,和整个B列该产品出现的次数,如果两者一样,就代表是该产品最后一列了。

  五、总结
  利用这个表格,你还可以简单通过数据透视表或者SUMIF/SUMPRODUCT公式汇总各个产品按时间的进销。
  也可以简单的通过数据有效性,限制产品名称的输入。
回复

使用道具 举报

学习了。。。。。。
回复 支持 反对

使用道具 举报

謝謝 學習試用
回复 支持 反对

使用道具 举报

保存了,谢谢。
回复 支持 反对

使用道具 举报

讲解的很细致了
回复 支持 反对

使用道具 举报

哈哈,自己动手,就像刘总说的: 工厂仓库台账表格最好的学习方法就是下载使用,真正动手操作才能快速上手
回复 支持 反对

使用道具 举报

哈哈,自己动手,謝謝 學習試用
回复 支持 反对

使用道具 举报


讲解的很细致了
回复 支持 反对

使用道具 举报

哈哈,自己动手,謝謝 學習試用
回复 支持 反对

使用道具 举报

怎么我做的不可以
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|仓库管理网

GMT+8, 2024-3-29 01:02

Powered by 库管易

KuGuanYi.Com

快速回复 返回顶部 返回列表