快好知 kuaihz订阅观点

 

纯Excel函数【进销存模板】制作(二)

        第一讲如果你使用的是加权平均法计算销售单价,那每月输入完采购后材料的进销存汇总表单价就是销售单价,有了销售数量,销售单价输入后就会自动计算出销售金额。现在讲【明细表查询】。   

       从B5开始按账面样式做表格,A2里输入公式=H4&" "&"明细账",然后选择A2:N2单元格,打开【开始】选项卡 →【字体】右下角箭头打开→【对齐方式】→【水平对齐】选择跨列对齐,【垂直对齐】→居中。  

       D4做数据有效性性设置,前面讲过看图模仿做一下。 

       最低库存F3=INDEX(商品表!H2:H1000,MATCH(明细表查询!D4,商品表!A2:A1000,0))数组,必须按ctrl+shift+enter结束。

       最高库存J3=INDEX(商品表!I2:I1000,MATCH(明细表查询!D4,商品表!A2:A1000,0))数组 

 

       H4=INDEX(商品表!$B$2:$B$3000,MATCH(明细表查询!D4,商品表!$A$2:$A$3000,0))

       K4=INDEX(商品表!$C$2:$C$3000,MATCH(明细表查询!D4,商品表!$A$2:$A$3000,0))

       N4=INDEX(商品表!$D$2:$D$3000,MATCH(明细表查询!D4,商品表!$A$2:$A$3000,0))

       Q2:AC2输入1向右拉到AB2为1至12月,AC2输入合计。 

        Q3=SUMPRODUCT(((MONTH(日期)=Q$2)*(商品编号=$D$4))),然后现有拉到AB3,

        Q4=IF(Q3=0,0,Q3+8)

        R4=IF(SUM($Q$4:Q$4)=0,IF(R$3=0,0,R$3+8),IF(R$3=0,Q4,Q4+R3+2))向右拉到AB4,

        P8=IF(ROW(A1)<=COUNTIF(商品编号,$D$4),SMALL(IF(商品编号=$D$4,ROW(商品编号),""),ROW(A1)),"") 数组,

       Q8=IF($AC$3=0,"",IF(Q7="本月合计","本年累计",IF(OR(ROW()=$Q$4,ROW()=$R$4,ROW()=$S$4,ROW()=$T$4,ROW()=$U$4,ROW()=$V$4,ROW()=$W$4,ROW()=$X$4,ROW()=$Y$4,ROW()=$Z$4,ROW()=$AA$4,ROW()=$AB$4),"本月合计",OFFSET($P$8,COUNT($Q$7:Q7),0)))),

选择Q8:P8单元格往下拉复制公式,根据你的进销存实际购销情况往下拉。

 

 

         B5=YEAR(进销表!A2)&"年"

         E7=IF(N7<>"","上年结存","")

         L7=IFERROR(INDEX(商品表!E:E,MATCH($D$4,商品表!A:A,0)),"")

         M7=IFERROR(INDEX(商品表!F:F,MATCH($D$4,商品表!A:A,0)),"")

         N7=IFERROR(INDEX(商品表!G:G,MATCH($D$4,商品表!A:A,0)),"")

         B8=IF(ISNUMBER($Q8),MONTH(INDIRECT("进销表!A"&$Q8)),"")

         C8=IF(ISNUMBER($Q8),DAY(INDIRECT("进销表!A"&$Q8)),"")

         D8=IF(ISNUMBER($Q8),MONTH(INDIRECT("进销表!b"&$Q8)),"")

         E8=IF(ISNUMBER($Q8),INDIRECT("进销表!c"&$Q8),Q8)

         F8=IF(OR($AC$3=0,$Q8=""),,IF($Q8="本月合计",SUMPRODUCT((MONTH(日期)=B7)*(商品编号=$D$4)*(购进数量)),IF($Q8="本年累计",SUMIF($Q$8:$Q8,"本月合计",$F$8:$F8),INDIRECT("进销表!h"&明细表查询!$Q8))))

         G8=IF(OR($Q8="",F8=0),,H8/F8)

         H8=IF(OR($AC$3=0,$Q8=""),,IF($Q8="本月合计",SUMPRODUCT((MONTH(日期)=B7)*(商品编号=$D$4)*(购进金额)),IF($Q8="本年累计",SUMIF($Q$8:$Q8,"本月合计",$H$8:$H8),INDIRECT("进销表!I"&明细表查询!$Q8))))

         I8=IF(OR($AC$3=0,$Q8=""),,IF($Q8="本月合计",SUMPRODUCT((MONTH(日期)=B7)*(商品编号=$D$4)*(销售数量)),IF($Q8="本年累计",SUMIF($Q$8:$Q8,"本月合计",$I$8:$I8),INDIRECT("进销表!j"&明细表查询!$Q8))))

         J8=IF(OR($Q8="",I8=0),,K8/I8)

         K8=IF(OR($AC$3=0,$Q8=""),,IF($Q8="本月合计",SUMPRODUCT((MONTH(日期)=B7)*(商品编号=$D$4)*(销售金额)),IF($Q8="本年累计",SUMIF($Q$8:$Q8,"本月合计",$K$8:$K8),INDIRECT("进销表!L"&明细表查询!$Q8))))

         L8=IF(OR($AC$3=0,$Q8=""),,IF(OR($Q8="本月合计",$Q8="本年累计"),$L7,$L7+F8-I8))

         M8=IF(Q8="",,IF(G8=0,M7,G8))

         N8=IF(OR($AC$2=0,$Q8=""),,IF(OR($Q8="本月合计",$Q8="本年累计"),$N7,$N$7+H8-K8)) 

       输完公式后选择B8:N8单元格区域向下拉。这些公式都比较复杂,不建议初学者完全理解,只要会使用即可。

       选择P列,按住Ctrl+shift,点击向右箭头→,这样所有列都选择了,然后点击鼠标右键隐藏;选择701行,按住Ctrl+shift,点击向下箭头↓,这样所有行都选择了,然后点击鼠标右键隐藏。

       保存工作表公式,目的是不让别人动你的公式以免造成公式损坏无法正常使用。选择工作表左上方第一行和A列的方格,看图: 

          1.选择【开始】选择卡,打开【数组】→【其他数字】→【保护】,取消锁定和隐藏勾选,然后【确定】;

          2.按F5快捷键打开【定为】→定为条件→选择公式,然后确认;

          3.再次选择【开始】选择卡,打开【数组】→【其他数字】→【保护】,选择锁定和隐藏勾选,然后【确定】;

           4.选择【审阅】选项卡→【更改】项的【保护工作表】,打开【保护工作表】,勾选自动筛选,【注意进销表保护还得选择数据透视表】  

       勾选【保护工作表及锁定的单元格内容】,在【取消工作表保护时使用的密码】下方输入你设定的密码,然后【确定】,【确认密码】→【重新输入密码】→【确定】即可。

       这样我们的进销存表就制作完毕,足够一个小型企业一年的使用,如果有年末结转,你可以使用INDEX+MATCH函数对12月份的进销存期末数据直接引用到【商品表】,然后对公式复制粘贴成数字即可。如果小型工业企业的进销存帐在财务核算,我们的成本计算也可以使用进销存模板一次建立,这样就做到了多快好省,节省时间节约人力,下次我来根据我们企业情况给大家讲解成本核算方法。

     请注明来自360doc。

本站资源来自互联网,仅供学习,如有侵权,请通知删除,敬请谅解!
搜索建议:进销存  进销存词条  函数  函数词条  模板  模板词条  制作  制作词条  Excel  Excel词条  
办公

 Word中插入大量图片后,怎样快...

首先审题,题主的需求是将WORD的图片一次性的、大量的、修改为同一尺寸。我有两个方法,一个简便,一个批量。一、简便方式——适用于10张以内的批量修改首先打开预先...(展开)

办公

 Excel中“事半功倍”的操作!

操作Excel时谁还不会犯个把错但是大家可能都经历过这种情况哎呀操作错误了 撤消!哎呀撤过头了!恢复!诶我刚刚想做的是哪一步来着怎么找不到了!每天经历被Exce...(展开)