BOM成本(分享与求助，难度超级大 )

shcaizi

cbtaja 发表于 2016-4-28 16:05
对于问题1：目前的代码适应0~9级以内；如果你表格的层级不是通过字符个数宽度缩进的方式表示，而直接用数 ...

说得太经典了，让我们这些摸索的编程外行的财务人员，真是听君一席话，胜读十年书啊！同时对你的热心报以掌声！你说得对，我近期也在给一家单位设计BOM，这个表是设计得有点复杂，层级直接用0~9表示会更容易编程计算，但我对你的数组代码虽感觉复杂但很感兴趣，因为不用数组也可以实现这些功能，我想明白的一件事是：在代码中用数组有什么好处，是不是比用公式更快更通俗易懂？如是这样的话，我得去研究这个数组代码的运用。

有一个非常经典的定义：程序 ＝ 数据结构+算法。
也就是说，当我们把需要的原始数据收集回来后，以一定的表现形式存放起来，就形成了数据源的数据结构。针对这样的数据存放方式（数据结构），编写有相应逻辑关系的代码（即算法），就形成了程序。
显然，源数据结构是基础，算法是手段。所以，合理有序的源数据结构，对于算法的顺利实现有重大好处。而杂乱的数据结构，则造成算法的实现很困难，效率很低下，甚至完全无法编写出可达成目标的算法代码。

shcaizi

12122122 发表于 2016-4-28 21:20
你好，点excel查看代码，没有模块，这是什么情况？

热心人：代码存放的地方有好几个地方，如工作表、THISWORKBOOK、模块、类模块、及窗体中

             近期我在给一家拟上市公司理帐，涉及到BOM设计，我感觉没有你设计的那么复杂，BOM层级应很清晰且便于编制代码计算，但你的有没有值得借鉴的地方，我正在研究，以上仅供交流学习，共同进步。

chengshanming

cbtaja 发表于 2016-4-28 19:43

cbtaja老师好！我测试了6-7万行数据，你写的代码运行速度相当快，十几秒钟！根据很强实用性，还有点细节问题需要您帮忙进一步完善谢谢了！见截图与附件！

chengshanming

cbtaja 发表于 2016-4-28 19:43

哦，对了，老师，我忽视了一个很重要的问题，你前面写的代码中都没有考虑计算非采购件（成品与半成品）的材料成本这一块，它的逻辑与对应工序工时汇总一样，只是对应0层不需要加总本层。就好比一套台式电脑，所有部件都做好，最好只需组装，组装要算工时的，但不再另外需材料了。

醉易寒

好吧...我是看不懂的..来学习

cbtaja

chengshanming 发表于 2016-4-29 11:49
cbtaja老师好！我测试了6-7万行数据，你写的代码运行速度相当快，十几秒钟！根据很强实用性，还有点细节 ...

BOM计算.rar (33.24 KB, 下载次数: 301)

2016-4-30 14:27 上传

点击文件名下载附件

chengshanming

cbtaja 发表于 2016-4-29 15:43

cbtaja老师好！还是有个关于BOM成本核心要素，材料成本问题需解决下，有劳了老师，主要是成品、半成品，如截图与附件！材料成本列的结果已手工算出，就是如何用代码化。

cbtaja

chengshanming 发表于 2016-4-29 16:13
cbtaja老师好！还是有个关于BOM成本核心要素，材料成本问题需解决下，有劳了老师，主要是成品、半成品， ...

BOM计算.rar (37.13 KB, 下载次数: 291)

2016-4-30 14:34 上传

点击文件名下载附件

1. Sub 计算()
   
2. Dim r&, Arr, FL, i&, c&, j&, t#, total&, ttitle, TopId$, tmp1()
   
3. Dim tmp0(0 To 9, 8 To 9) 'bom10级，每级求2个数据：上层母件、本层用量系数
   
4. t = Timer
   
5. With Sheet1
   
6.     total = .Cells(Rows.Count, 7).End(xlUp).Row - 1
   
7.     FL = .Range("G2").Resize(total, 3) '工序费率
   
8.     ReDim tmp1(0 To 9, 11 + total To 14 + total * 2) 'bom10级，每级求total+4个数据：total道工序的工时汇总，以及：材料、人工、制费、总成本。
   
9. End With
   
10. r = Cells(Rows.Count, 1).End(xlUp).Row '取得Bom表的总行数
    
11. Arr = [A2].Resize(r, 15 + total * 2) '获取BOM资料（无标题，多加一空行作结束依据）
    
12. '先自上向下计算物料用量、上层母件
    
13. For i = 1 To r - 1
    
14.     c = Len(Arr(i, 1)) - 1 '物料层级
    
15.     If c < 0 Then Exit For
    
16.     If c = 0 Then Erase tmp0: TopId = Arr(i, 2) '临时表重新清零,备用，并取得当前Bom树的树根
    
17.     tmp0(c, 9) = Arr(i, 6) * (1 + Arr(i, 7)) '当前第c层的用量（含损耗）
    
18.     If c > 0 Then tmp0(c, 8) = tmp0(c - 1, 8) * tmp0(c, 9) Else tmp0(c, 8) = tmp0(c, 9) 'c层bom总用量＝上层总用量*本层用量
    
19.     Arr(i, 8) = tmp0(c, 8)
    
20.     Arr(i, 9) = tmp0(c, 9)
    
21.     Arr(i, 15 + total * 2) = TopId '顶层母件
    
22. Next
    
23. '再自下向上汇总工时、工费、成本
    
24. For i = r - 1 To 1 Step -1
    
25.     c = Len(Arr(i, 1)) - 1 'bom层级
    
26.     If c >= Len(Arr(i + 1, 1)) - 1 Then tmp1(c, 14 + total * 2) = Empty
    
27.     If Len(Arr(i, 1)) < Len(Arr(i + 1, 1)) Then '非采购件
    
28.         Arr(i, 3) = Empty
    
29.         For j = 1 To total  '共total＝6道工序，工时的源数据从第11列开始
    
30.             '本级总工时 ＝ 本级工序用时+其所有子工序用时
    
31.             tmp1(c, j + 10 + total) = Arr(i, 8) * Arr(i, j + 10) + tmp1(c, j + 10 + total)  '工时汇总
    
32.             tmp1(c, 12 + total * 2) = tmp1(c, 12 + total * 2) + tmp1(c, j + 10 + total) * FL(j, 2) '人工费汇总
    
33.             tmp1(c, 13 + total * 2) = tmp1(c, 13 + total * 2) + tmp1(c, j + 10 + total) * FL(j, 3)  '制费汇总
    
34.         Next
    
35.     Else '是采购件
    
36.         Arr(i, 3) = "是"
    
37.     End If
    
38.     tmp1(c, 11 + total * 2) = tmp1(c, 11 + total * 2) + Arr(i, 10) * Arr(i, 8) '材料成本=子项材料累计成本+本项的材料单价*数量
    
39.     tmp1(c, 14 + total * 2) = tmp1(c, 11 + total * 2) + tmp1(c, 12 + total * 2) + tmp1(c, 13 + total * 2) '总成本=材料+人工+制费（该3项均已经包含子项的数量）
    
40.     For j = 17 To 26
    
41.         If c > 0 Then tmp1(c - 1, j) = tmp1(c - 1, j) + tmp1(c, j) '各项累计到其上一层中（总成本项不要重复累计）
    
42.         Arr(i, j) = tmp1(c, j) '从临时表中获取结果
    
43.         tmp1(c, j) = Empty '临时表清空备用（避免带入下一个同一层的项中）
    
44.     Next
    
45.     If Arr(i, 3) = "是" Then Arr(i, 11 + 2 * total) = Empty '按楼主的要求，采购件只显示总成本，其材料成本留空’
    
46. Next
    
47. [a1:e1].Offset(0, 10 + 2 * total).Value = Split("材料成本,单位人工,单位制费,总成本,顶层母件", ",")
    
48. [A2].Resize(r - 1, 15 + total * 2) = Arr
    
49. MsgBox "用时 " & Format(Timer - t, "0.0000") & " 秒！"
    
50. End Sub
    

复制代码

chengshanming

cbtaja 发表于 2016-4-29 17:00

老师好，你真的太牛叉了！基本上相当完善了！后期想到什么，望进一步完善！给我感觉你这方法比数据字典的用于几万条数据以上的速度要快得的多，6-7万条，30来秒钟就算了。

chengshanming

cbtaja 发表于 2016-4-29 17:00

cbtaja老师好！如果我把如图的层级全部换成数字，怎么编写代码，它可以通用N层级情况吗？其它的逻辑都不变。谢谢了！