在库管易仓库社区论坛看见好多要求库存进销存的EXCEL 表格模版，仗着自己有点会计知识，也在这摆弄摆弄。（这个表格计算的单位成本方法是用移动平均法）
首先在表格把标题填上：
| A | B | C | D | E | F | G | H | I | J | K | | 1 | | | | 交易 | 库存 | | | | 2 | 日期 | 产品 | 摘要 | 数量 | 单价 | 总价格 | 数量 | 单位成本 | 总成本 | 交易利润 | 最后交易 | | 3 | | | | | | | | | | | |
在表格上的 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参数，返回该行H列（总成本）的数值。我们最后再说怎么找出上次交易的行数（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))) 就是找出上次交易行数的函数，实际上是数组公式 {=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 公式汇总各个产品按时间的进销。
或者可以简单的通过数据有效性，限制产品名称的输入。 |
发表于 2013-8-17 23:54:41