库存管理

忘得快

一指禅62 发表于 2019-2-23 19:45
函数公式错了，应该为：

一指禅62老师你好！
按59楼的附件有个问题要请教一下：
DataBase表,2月份DFG001物料入库数量和入库金额合计都是5，明细账查询2月份DFG001物料入库数量和入库金额合计也都是5，
汇总表2月份的收入为什么都是1？

忘得快

一指禅62 发表于 2019-2-23 19:45
函数公式错了，应该为：

不好意思，写错啦！是59楼的附件不是50楼的附件

一指禅62

忘得快 发表于 2019-2-24 09:49
不好意思，写错啦！是59楼的附件不是50楼的附件

谢谢您发现并指出问题！注意代码中的标红处：
        SQL = SQL & " Union All Select 物料编码,入库数量-出库数量,入库金额-出库金额,0,0,0,0 from [DataBase$] where 月<" & Target.Value
        SQL = SQL & " Union All Select 物料编码,0,0,入库数量,入库金额,出库数量,出库金额 from [DataBase$] where 月=" & Target.Value

一指禅62

汇总表的完整代码，请替换代码并测试

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim rs As New ADODB.Recordset, SQL$, i%
   
3.     If Target.Address = "$E$2" Then
   
4.         SQL = "select 物料编码,期初数量 as a,期初金额 as b,0 as c,0 as d,0 as e,0 as f from [物品表$] "
   
5.         SQL = SQL & " Union All Select 物料编码,入库数量-出库数量,入库金额-出库金额,0,0,0,0 from [DataBase$] where 月<" & Target.Value
   
6.         SQL = SQL & " Union All Select 物料编码,0,0,入库数量,入库金额,出库数量,出库金额 from [DataBase$] where 月=" & Target.Value
   
7.         SQL = "Select 物料编码,sum(a) as 期初数量,val(format(sum(b)/sum(a),'0.00')) as 期初单价,sum(b) as 期初金额," & _
   
8.             "sum(c) as 入库数量,val(format(sum(d)/sum(c),'0.00')) as 入库单价,sum(d) as 入库金额," & _
   
9.             "sum(e) as 出库数量,val(format(sum(f)/sum(e),'0.00')) as 出库单价,sum(f) as 出库金额," & _
   
10.             "(sum(a)+sum(c)-sum(e)) as 期末数量," & _
    
11.             "val(format((sum(b)+sum(d)-sum(f))/(sum(a)+sum(c)-sum(e)),'0.00')) as 期末单价," & _
    
12.             "(sum(b)+sum(d)-sum(f)) as 期末金额 from (" & SQL & ") Group by 物料编码"
    
13.         SQL = "Select a.物料编码,物料名称,规格,单位,期初数量,期初单价,期初金额,入库数量,入库单价,入库金额," & _
    
14.             "出库数量,出库单价,出库金额,期末数量,期末单价,期末金额 " & _
    
15.             "from [物品表$] a Left Join (" & SQL & ") b on a.物料编码=b.物料编码"
    
16.         rs.Open SQL, cnn, 1, 3
    
17.         Range("A5:Z65536").ClearContents
    
18.         Range("A5:Z65536").Borders.LineStyle = xlNone
    
19.         Range("A5").CopyFromRecordset rs
    
20.    
    
21.    
    
22.         If rs.RecordCount > 0 Then
    
23.             i = rs.RecordCount
    
24.             With Range("A5").Offset(i, 1)
    
25.                 .Value = "合计"
    
26.                 .Offset(0, 5).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C7)"
    
27.                 .Offset(0, 8).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C)"
    
28.                 .Offset(0, 11).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C)"
    
29.                 .Offset(0, 14).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C)"
    
30.             End With
    
31.             With Range("A5").Resize(i + 1, 16)
    
32.                 .Borders.LineStyle = xlContinuous
    
33.                 .Font.Size = 10 '字号，根据需要修改
    
34.             End With
    
35.         End If
    
36.         rs.Close
    
37.     End If
    
38.     Set rs = Nothing
    
39. End Sub

复制代码

忘得快

一指禅62 发表于 2019-2-25 08:42
汇总表的完整代码，请替换代码并测试

老师您好！.Offset(0, 5).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C7)"
是否应改为：
.Offset(0, 5).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C)"

一指禅62

忘得快 发表于 2019-2-25 11:17
老师您好！.Offset(0, 5).FormulaR1C1 = "=SUM(R5C:R" & i + 4 & "C7)"
是否应改为：
.Offset(0, 5).Fo ...

是的                           

laoshenji

一指禅62 发表于 2019-2-25 08:42
汇总表的完整代码，请替换代码并测试

按64楼代码未测试成功！

laoshenji

一指禅62 发表于 2019-2-25 11:39
是的

一指禅62老师您好，更换64楼代码并按65楼修改后，汇总表结果不正确的问题，主要是：1、刚打开汇总表时，提示循环引用。2、汇总表错误反映DFG001的购进（本月收入）数量仅为“1”，而明细账正确反映DFG001的购进（本月收入）数量则为“5”。请老师再费心看看，望不吝指教，谢谢！

wowo000

一指禅62老师：可否完善一下，解决您说的“BUG:当前代码不区分 “年”，这倒是硬伤”这个“硬伤”？？？

一指禅62

wowo000 发表于 2020-4-8 13:23
一指禅62老师：可否完善一下，解决您说的“BUG:当前代码不区分 “年”，这倒是硬伤”这个“硬伤”？？？

以前写的，我现在来看，已没有修改的意义了。如工作中确有需要，可据需求另起炉灶。