|
非常漂亮的Excel算量模板 --语言描述、表格、图形一个都不能少 Very nice Excel templates for construction budget --Descriptions、tables and drawings are all indispensable 摘要：工程手工算量计算繁琐，不易统计和修改，因而算量软件陆续出现。然而算量软件操作必须严格按软件要求操作，对软件公司的指导依赖性很强；另外算量软件采用三维建模来实现计算的，较手工算量多出了繁琐的建模工作。 随着我国工程量清单规范的实施，编制自己的企业定额已日趋必要，企业定额的数据主要来源于历史数据的记录和统计分析。Excel具有强大的数据处理能力，本人通过实际经验总结，制作了工程算量模板。本模板在计算工程量及投标报价上具有以下优点：①数据清晰性强，②数据追溯性强，③调价直观性强。 当然模板在算量时智能化及精度无法与三维软件相比，追求清晰性、实用性、高效性的最佳组合。 关键词：Excel应用，Excel-CAD表格互导、工程算量软件，工程量清单规范，企业定额。 Abstract: It is troublesome to calculating by hand, and it’s not convenient to make statistics or modify. As a result, calculating soft wares have appeared one by one. However, we have to follow their instructions in order to use the soft wares, and we depend a lot on the guidance of software companies; besides, when using soft wares to calculate, we have to do a lot of modeling work, which is the core of their calculating system. As the specification for bill of quantities carries out, it becomes necessary to make the enterprise ration, the data of which come from the records and statistics of historical data. Excel has powerful data-dealing ability. After a lot of practice, I make a template for construction budget. It has the following advantages on construction budget and tender offer: ① The data is clear. ② It can easily tell the sources. ③ It is easy to read for adjusting prices. Though the template can not beat the software on intelligence and precision, I am seeking for a perfect combination of clearness、practicability and efficiency. Key: Application of Excel, data-in and data-out between tables of Excel and CAD, software for construction budget, specification for bill of quantities, the enterprise ration. 一、与传统手工算量及软件算量的比较 1、与传统手工算量的比较
2、与算量软件的比较
二、核心途径 1、有限多维数据的清晰化处理 1）多级目录是多维数据的最基本方式，工程量清单就是采用得这种方式，将复杂的工程按清单项目目录综合成一体。 2）表格的行与列可组成矩阵，属二维数据，较目录形式的一维数据更加清晰。 3）数据的排序功能。假设有一个三维数据（A，B，C），我们将A与B合并，数据更改为二维数据（A&B,C），但A&B本身仍是一维数据,现对A&B进行排序，顺序依次为（A1&B1,A1&B2,A2&B1,A2&B2），即目录化的二维数据。 2、工程量的公式合并 根据工程量清单规范，工程量的计量单位有件数、长度、面积、体积、重量，因此工程量计算公式并不多（不超过50个），我们需对不同的项目编辑公式，并将其合并为一个公式。 我们知道，使用IF公式，只可以对7个以下公式进行合并，且公式太过复杂不易检查。 这里我使用了宏自定义函数，操作如下：工具—宏—vb编辑器—弹出vb编辑器窗口 依次点击菜单—插入—模块1，在窗口编辑如下内容：
这里自定义的公式为gcl()，输入的参数包括L, B, H, n, d基本参数及one, two, three, four, five, six自定义参数，对于不同的biaoji，可定义不同的自定义参数。 因为不同的公式需要不同的参数，这里我使用语言描述（语言描述的应用）并使用Excel的公式识别语言并设置条件格式，提示需要输入哪些参数及自定义参数代表的名称，我们仍然通过自定义函数：
然后根据sm的内容进行条件格式的设置，需要输入的参数显示黄色，不需要的显示灰色，（一个构件的计算项目中有很多参数是一致的，为了避免多次输入，我们将公用参数作为构件参数，项目参数引用构件参数，显示蓝色，不一致时自行输入，显示黄色），效果如下图： 3、数据汇总 输入参数后，模板自动对项目进行编号，并自动在汇总表中引用该项目名称，并统计每个项目的工程量。同时在横向对分类进行编号并引用，并对每个项目的工程量按分类进行拆分，（表格的应用）如下图： 三、亮点介绍 由于Excel和CAD在表格和图形方面功能非常强大，二者配合可以大大提高工作效率。以下对其优势作说明如下： 1、流程简便，对量简单，只需核对输入数据是否与图纸一致及模板中30多个公式的正确性。（流程图见下页） 2、参数输入快捷通道： 1）导入装修做法表，将其复制到Excel模板的相应位置，模板会自动计算装修做法，只需输入房间名称及尺寸、数量（对于矩形房间输入长宽高，异形房间输入面积、周长和净高）（图形的应用）。 2）导入门窗表，我们在导入的门窗表后添加三列（扣墙体、扣两面装饰），这样模板在计算门窗时会自动扣除门窗洞口的相应项目。避免了在计算装饰面积时去重复计算扣除门窗的面积。 3）砼梁构件的参数根据平法标注统一输入，由模板进行计算自动填写参数。减少了人为输入的错误。 3、借用CAD图统计总长度。我们经常需要统计长度（比如计算室外水管的长度）。然而CAD中并没有对直线长度的汇总功能，只能在文本文件中显示列表。这样通过Excel强大的计算功能，就可以计算出总长度。将结果复制回CAD图适中当位置，打印出附图作为算量资料的一部分（图形的应用）。 工程列项 输入参数 工程量计算 钢筋含量分析 构件参数检查 工程量 输出 单价输入 参考总价 主要指标 统计长度数量 检查符合性 大部分构件已有模块，特殊构件自定义 配合CAD进行统计，打印图形进行备注 尽量按图纸格式输入参数，如梁平法标注 大部分公式已编辑好，遇有未编辑的公式，可自行输入，模板会以不同颜色区分自定义公式，或添加到自定义函数中 辅助工作，检查人为错误及钢筋指标 自动计算，按给定清单进行项目分类 模板中逐步添加初步价格 对占总价比重大的项目进行详细询价，保证总价的误差精度要求 计算主要指标，可参考类似工程的各项指标进行快速估价 4、指标输出功能，相似的工程具有相近的指标。所以在算量后进行指标统计是非常必要的，一方面可以检查错误，二方面可以加深宏观认识、快速进行工程概算。人材机图表，直观显示人材机总价，通过人工总价，可以估算出项目需要多少劳动力，进行技术标的设计。以下两项指标图均自动自成，非常方便。 四、模板在企业定额定价与工程量清单衔接中的应用 1、挖土方：工程量清单规范因为考虑不同投标单位的施工水平不一致，为了保持公平公正，规定土方工程量是按不放坡考虑的。但实际施工过程中确实是要放坡的，企业成本也是与放坡土方量成正比的。模板中是这样实现的：先按留工作面，放坡进行计算工程量，并计价，记录总价。对计算表格进行筛选挖土方，将工作面和放坡系数清除，得出不放坡工程量，填入清单。 2、不同构件的砼量：工程量清单规范对梁、柱、板及墙等构件是分开计量的，但作为施工单位，在将工作分配给班组长时，一般并不会按不同类型使用不同的价格。所以在模板算量时，对不同构件的同等级砼是统一定价的，以减少项目数量，加强数据清晰性。 3、化粪池等小型构件：工程量清单规范为了简化清单，对小型构件进行了整合。如果施工单位按构件进行定价，而图集中做法繁多，企业定额的计价项目将非常多。在本模板中，我们对小型构件进行拆分至清单项目进行计价，计价后再将构件价格输入到工程量清单中。 4、门窗：工程量清单规范为了将门窗五金综合到门窗综合价中，对门窗是按樘进行计量的，给施工单位的计价带来了很大的麻烦。在本模板中，门窗作为项目按面积进行计价，门五金按数量计价，汇总成构件将其构件单价填入工程量清单中。 五、总结 本模板在算量时具有一定的智能性，较手工算量效率大大提高；而利用excel的强大数据处理功能和CAD的图形处理功能，实现了项目预算的高效性、整体性，对于宏观调整非常有效。能满足投标报价的精度要求。同时，本模板能较好地结合企业信息，配合企业定额的编制。 当然，本模板在项目计算的全面性、模板布局、操作技巧方面还有提高的空间，希望能为土建预算人员带来方便，并一起完善模板，共同提高。 QQ:20439765 QQ群：56336868 Email:yaker_77@126.com 附：VBA编程文件 自定义工程量计算的数组公式 Option Base 1 Function gcl(biaoji, xm, L, B, H, n, d, one, two, three, four, five, six) If xm = "钢筋一级" Then dengji = "A" ElseIf xm = "钢筋二级" Then dengji = "B" ElseIf xm = "钢筋三级" Then dengji = "C" Else dengji = "?" End If Dim temp(6) Select Case biaoji Case Is = "构件" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = "" temp(5) = "" temp(6) = "" Case Is = "件数" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n temp(5) = n temp(6) = "U" Case Is = "长度" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n temp(5) = L / 1000 * n temp(6) = "M" Case Is = "面积" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 测S=" & one & " 加长=" & two & " 加宽=" & three If one = 0 Then temp(5) = (L + two) / 1000 * (B + three) / 1000 * n Else temp(5) = one * n End If temp(6) = "M2" Case Is = "柱模板" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 测周长=" & one If one = 0 Then temp(5) = (L + B) * 2 / 1000 * H / 1000 * n Else temp(5) = one / 1000 * H / 1000 * n End If temp(6) = "M2" Case Is = "梁模板" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " 板厚=" & one temp(5) = L / 1000 * (B + 2 * (H - one)) / 1000 temp(6) = "M2" Case Is = "体积" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 测S=" & one If one = 0 Then temp(5) = L / 1000 * B / 1000 * H / 1000 * n Else temp(5) = one * H / 1000 * n End If temp(6) = "M3" Case Is = "圆柱体积" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " D=" & one & " t=" & two If two = 0 Then temp(5) = 3.1416 * one * one / 4000 * H / 1000 * n Else temp(5) = 3.1416 * (one * one - (one - two) * (one - two)) / 4000 * H / 1000 * n End If temp(6) = "M3" Case Is = "梁砼" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " 板厚=" & one temp(5) = L / 1000 * B / 1000 * (H - one) / 1000 temp(6) = "M3" Case Is = "梯形" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " 上宽=" & one & " 下宽=" & two temp(5) = L / 1000 * (one + two) / 2000 * H / 1000 temp(6) = "M3" Case Is = "梯形放坡" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " 工作面=" & one & " 坡度=" & two temp(5) = L / 1000 * (B + one * 2 + two * H) / 1000 * H / 1000 temp(6) = "M3" Case Is = "锥形" Sone = L / 1000 * B / 1000 temp(1) = " S1=" & Sone Stwo = one / 1000 * two / 1000 temp(2) = " S2=" & Stwo temp(3) = "" temp(4) = " 上长=" & one & " 下宽=" & two temp(5) = (Sone + Stwo + Sqr(Sone * Stwo)) * H / 3000 temp(6) = "M3" Case Is = "放坡锥形" Sone = (L + one * 2) / 1000 * (B + one * 2) / 1000 temp(1) = " S1=" & Sone Stwo = (L + one * 2 + two * H * 2) / 1000 * (B + one * 2 + two * H * 2) / 1000 temp(2) = " S2=" & Stwo temp(3) = "" temp(4) = " 工作面=" & one & " 坡度=" & two temp(5) = (Sone + Stwo + Sqr(Sone * Stwo)) * H / 3000 temp(6) = "M3" Case Is = "面积锥形" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " S1=" & one & " S2=" & two temp(5) = (one + two + Sqr(one * two)) * H / 3000 temp(6) = "M3" Case Is = "柱钢筋" gjn = n temp(1) = gjn gjl = H + one + two temp(2) = gjl If three = 0 Then djl = 0 Else djl = 40 * d * (three + 1) End If temp(3) = djl temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "柱箍筋" temp(6) = "T" gjn = ((H - five) / two + five / one) * n temp(1) = gjn gjl = L * three + B * four temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d & "@" & one & "/" & two & "(" & three & "*" & four & ")" & " 加密长" & five temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 Case Is = "上部筋" gjn = n temp(1) = gjn If two = 100 Then gjl = L + one Else gjl = Int(L + one * (two + 1) / two + 15 * d * 2 / two) End If temp(2) = gjl djl = Int(40 * 0.8 * d * gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "加强筋" gjn = n temp(1) = gjn If two = 100 Then gjl = L + one Else gjl = Int(L * 2 / 3 + one * (two + 1) / two + 15 * d * 2 / two) End If temp(2) = gjl djl = Int(40 * 0.8 * d * gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "下部筋" gjn = n temp(1) = gjn If two = 100 Then gjl = L + one Else gjl = Int(L + one * (two + 1) / two + 15 * d * 2 / two) End If temp(2) = gjl djl = Int(28 * 0.8 * d * gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "梁箍筋" If H > 250 Then jml = H * 4 Else jml = 1000 End If gjn = ((L - jml) / two + jml / one) * n temp(1) = gjn gjl = H * three + B * 2 temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d & "@" & one & "/" & two & "(" & three & ")" temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "零星箍筋" gjn = n temp(1) = gjn gjl = H * one + B * two temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d & "(" & one & "*" & two & ")" temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "拉筋" gjn = L / one * n temp(1) = gjn gjl = B temp(2) = gjl djl = 0 temp(3) = "" temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "长分布筋" gjn = (B + three - 2 * four) / one * n temp(1) = gjn gjl = L + two - 2 * four temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d & "@" & one temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "宽分布筋" gjn = (L + two - 2 * four) / one * n temp(1) = gjn gjl = B + three - 2 * four temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d & "@" & one temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "双层加固" gjn = Int(L / one * B / two) temp(1) = gjn gjl = H + 6.5 * d temp(2) = gjl temp(3) = "" temp(4) = biaoji & n & dengji & d & "@" & one & "*" & two temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 temp(6) = "T" Case Is = "零星钢筋" temp(6) = "T" gjn = n temp(1) = gjn gjl = L temp(2) = gjl djl = 40 * d * Int(gjl / 9000) temp(3) = djl temp(4) = biaoji & n & dengji & d temp(5) = gjn * (gjl + djl) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 Case Is = "钢板" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 厚=" & one temp(5) = L / 1000 * B / 1000 * one / 1000 * n * 7.85 temp(6) = "T" Case Is = "花纹板" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 规格 " & one temp(5) = L / 1000 * B / 1000 * two * n temp(6) = "T" Case Is = "型材" temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = " n=" & n & " 规格" & one & "单重" & two temp(5) = n * L / 1000 * two / 1000 temp(6) = "T" Case Else temp(1) = "" temp(2) = "" temp(3) = "" temp(4) = "" temp(5) = "" temp(6) = "" End Select gcl = temp End Function 自定义参数说明公式 Function sm(biaoji) If biaoji = "构件" Then sm = "构件名称构件数量LBH" ElseIf biaoji = "件数" Then sm = "n" ElseIf biaoji = "长度" Then sm = "Ln" ElseIf biaoji = "面积" Then sm = "LBn1测量面积2加长3加宽" ElseIf biaoji = "柱模板" Then sm = "LBHn1测量周长" ElseIf biaoji = "梁模板" Then sm = "LBH1板厚" ElseIf biaoji = "体积" Then sm = "LBHn1测量面积" ElseIf biaoji = "圆柱体积" Then sm = "Hn1直径2壁厚" ElseIf biaoji = "梁砼" Then sm = "LBH1板厚" ElseIf biaoji = "梯形" Then sm = "LH1上宽2下宽" ElseIf biaoji = "梯形放坡" Then sm = "LBH1工作面2坡度" ElseIf biaoji = "锥形" Then sm = "LBH1上长2上宽" ElseIf biaoji = "放坡锥形" Then sm = "LBH1工作面2坡度" ElseIf biaoji = "面积锥形" Then sm = "H1下部面积2上部面积" ElseIf biaoji = "柱钢筋" Then sm = "Hnd1下加长2上加长3层数" ElseIf biaoji = "柱箍筋" Then sm = "LBHnd12间距34筋数5加密长" ElseIf biaoji = "上部筋" Then sm = "Lnd1柱宽2跨数(边跨输2倍,中间跨输100)" ElseIf biaoji = "加强筋" Then sm = "Ln(平均值)d1柱宽2跨数" ElseIf biaoji = "下部筋" Then sm = "Lnd1柱宽2跨数" ElseIf biaoji = "梁箍筋" Then sm = "LBHnd12间距3立筋数" ElseIf biaoji = "零星箍筋" Then sm = "BHnd12筋数" ElseIf biaoji = "拉筋" Then sm = "LBnd1间距" ElseIf biaoji = "长分布筋" Then sm = "LBnd1间距2钢筋加长3布置加宽4保护" ElseIf biaoji = "宽分布筋" Then sm = "LBnd1间距2布置加长3钢筋加宽4保护" ElseIf biaoji = "双层加固" Then sm = "LBHd12间距" ElseIf biaoji = "零星钢筋" Then sm = "Lnd" ElseIf biaoji = "钢板" Then sm = "LBn1板厚" ElseIf biaoji = "花纹板" Then sm = "LBn1板厚2单位重量" ElseIf biaoji = "型材" Then sm = "Ln1规格2单位重量" Else sm = "" End If End Function |
||||||||||||||||||||||||||||||||||||||||||||
|
|
