ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: chengshanming

[分享] BOM成本(分享与求助,难度超级大 )

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2016-4-28 23:26 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
cbtaja 发表于 2016-4-28 16:05
对于问题1:目前的代码适应0~9级以内;如果你表格的层级不是通过字符个数宽度缩进的方式表示,而直接用数 ...

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

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

TA的精华主题

TA的得分主题

发表于 2016-4-28 23:35 | 显示全部楼层
12122122 发表于 2016-4-28 21:20
你好,点excel查看代码,没有模块,这是什么情况?

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

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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-29 11:49 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

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

BOM计算.zip

540.03 KB, 下载次数: 713

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-29 12:01 | 显示全部楼层

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

TA的精华主题

TA的得分主题

发表于 2016-4-29 13:24 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
好吧...我是看不懂的..来学习

TA的精华主题

TA的得分主题

发表于 2016-4-29 15:43 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 cbtaja 于 2016-4-30 14:27 编辑
chengshanming 发表于 2016-4-29 11:49
cbtaja老师好!我测试了6-7万行数据,你写的代码运行速度相当快,十几秒钟!根据很强实用性,还有点细节 ...

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-29 16:13 | 显示全部楼层

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

BOM计算.zip

37.63 KB, 下载次数: 43

TA的精华主题

TA的得分主题

发表于 2016-4-29 17:00 | 显示全部楼层
本帖最后由 cbtaja 于 2016-4-30 14:34 编辑
chengshanming 发表于 2016-4-29 16:13
cbtaja老师好!还是有个关于BOM成本核心要素,材料成本问题需解决下,有劳了老师,主要是成品、半成品, ...

BOM计算.rar (37.13 KB, 下载次数: 288)
  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
复制代码


TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-29 17:34 | 显示全部楼层

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-29 19:51 | 显示全部楼层

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

BOM计算.rar

34.74 KB, 下载次数: 83

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 14:22 , Processed in 0.039961 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

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