Excel在工程预算中的应用

Excel在投标算量及报价中的应用

摘要：工程手工算量计算繁琐，不易统计和修改，故而算量软件陆续出现。然而算量软件操作必须按软件要求，对软件公司的指导依赖性很强；另外算量软件采用三维建模来实现计算的，较手工算量多出了建模工作，而建模本身工作量较大。

随着我国工程量清单规范的实施，编制自己的企业定额已日趋必要，企业定额的数据主要来源于历史数据的记录和统计。Excel具有强大的数据处理能力，本人通过实际经验总结，制作了工程算量模板。本模板在计算工程量及投标报价上具有以下优点：①数据清晰性强，②数据追溯性强，③调价直观性强。

当然模板在算量时智能化及精度无法与算量软件相比，追求清晰性、实用性、高效性的最佳组合。

关键词：Excel应用，工程算量软件，预算软件，工程量清单，企业定额，施工成本控制，多维数据的处理。

一、概述

工程预算是一项非常复杂的系统，工程量清单将其划分为分部工程，分项工程并对其进行计价。这种方法有两点不足：一是对于不同部位的相同分项不方便统一定价和调价，二是有些分项工程并不能直接与单价很好的对应，定价的经验成份较大。

为了克服这两点不足，我采取如下措施：

1、将不同部位的相同分项汇总统一定价，以免重复计价。

2、对于水表井类似的项目，继续拆分成挖土方、垫层、砖砌体等子项，汇总价格后作为该水表井的综合单价。

另外为了增加企业对成本的透明度，我采取如下措施：

1、计量时力求简捷，计量过程尽量透明，适当降低计算精度（在计算钢筋长度时，我们只按规范中一般抗震要求计算，忽略搭接长度的细微差别）

2、将单价划分为支出费用和内部成本。

对于支出费用，采取细致询价保证其准确性；

而对于企业内部成本，一方面可采用定额消耗量核算成本，另一方面可根据企业以往类似工程实际发生进行宏观控制。

3、调整报价要抓大放小，模板中企业定额库中事先确定默认价格（其误差根据性质不同可以为50%-200%）。这样在计量时可以直观的得出大致价格，而在汇总后，我们可以对占总价比例较大的项目进行价格精细确认，以保证报价的精确性，以免发生报价低于成本或过高价影响中标。

传统手工算量、Excel手工算量及算量软件的比较

算量软件的优缺点

二、核心途径

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、工程量的公式合并

根据工程量清单规范，工程量的计量单位有件数、长度、面积、体积、重量，我们需对不同的计算单位和项目特点分别计算工程量。

我们知道，使用IF公式，可以对不同种情况的公式进行合并，但由于IF公式只能嵌套7层，类型较多时无法实现，且公式太过复杂不易检查。

这里我使用了宏表函数，操作如下：工具—宏—vb编辑器—弹出vb编辑器窗口 依次点击菜单—插入—模块1，在窗口编辑如下内容：

这里自定义的公式为gcl()，输入的参数包括L, B, H, n, d基本参数及one, two, three, four, five, six自定义参数，对于不同的biaoji，可定义不同的自定义参数。中间省略的公式将在后面介绍。

3、数据汇总

三、过程介绍

操作流程

1、构件列项

建筑物由构件组成，构件包括基础、柱、梁、板、门窗、墙等几大构件，每一类构件要算的项目也不是很多，砼结构计算砼量、模板、钢筋。装饰计算每层做法。安装计算管道长度，阀门数量。

构件列表是根据手工算量的思路进行编辑的，易于上手，我们将常规构件的列项存在模板中方便调用。而对于特殊的零星项目自行列项并不耗费很多时间，不建议保留在模板中，以免影响模板的清晰度。下图是基础梁或无板梁的构件列表示例

梁下墙的工程量在这里计算，是因为梁下墙的高度与砼梁高度相关的，可以减少参数的重复输入。同理我会在计算门窗时计算墙及内外装饰工程量，只不过数量为负数即扣减。

2、计量公式的编辑

计量公式的实现方法在前面已介绍，为了让公式易于看懂，我采用了Excel的名称功能，公式引用名称进行计算，而不引用单元格名称。下面是工程常用的公式：

1）面积计算：

3）体积计算：长方体体积、梯形截面体积、异形截面体积、棱台棱锥体积

g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L * 2 / 3 + (one * (two + 1) + 15 * d * 2) / two djl = 40 * d * Int((L / 3 + one + 15 * d) / 9000) * 2 gcl = n * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "下部筋" Then 'one 柱宽度 two 跨度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L * two + one * (two + 1) djl = 40 * d * Int(gjl / 9000) gcl = n * g / 1000 * (gjl + djl) / 1000 / two ElseIf biaoji = "梁箍筋" Then 'one 加密间距 two 间距 three 宽度箍筋数量 four 高度箍筋数量 five 加密区长度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjn = ((L - five) / two + five / one) * n gjl = B * three + H * four gcl = gjn * g / 1000 * gjl / 1000 ElseIf biaoji = "拉筋" Then 'one 间距 gcl = L / one * n * B / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 ElseIf biaoji = "长分布筋" Then 'one 间距 two 钢筋加长 three 布置加宽 four 保护层 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L + two - 2 * four djl = 40 * d * Int(gjl / 9000) gjn = (B + three - 2 * four) / one * n gcl = gjn * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "宽分布筋" Then 'one 间距 two 布置加宽 three 钢筋加长 four 保护层 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = B + three - 2 * four djl = 40 * d * Int(gjl / 9000) gjn = (L + two - 2 * four) / one * n gcl = gjn * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "双层加固" Then 'one 长向间距 two 宽向间距 gcl = L / one * B / two * (H + 6.5 * d) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 ElseIf biaoji = "零星钢筋" Then gcl = n * L / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 ElseIf biaoji = "钢板" Then gcl = L / 1000 * B / 1000 * H / 1000 * n * 7.85 ElseIf biaoji = "型材" Then 'one 型钢规格 two 单位重量 gcl = n * L / 1000 * two / 1000

第 7 页 共 14 页

5）计量公式模板

由于该公式内容较多，逐个检查太过复杂，我在公式编辑工作表中对所有项目进行了excel工式计算，通过与gcl公式的计算结果进行比较。我们只需要检查excel公式的正确性就可以了，这项工作就是我们手工算量的列式，嫌复杂就过分了！不要期望软件为你包办一切，包办一切的软件是不存在的，同时也是行不通的。

这里，对于同一构件大部分项目都需要LBH三个参数，为了避免重复输入，在项目行的这三列我们输入公式进行计算，而当不需要构件参数时，我们可以直接输入，

为了区分是输入的还是引用的构件参数，我们对其设置不同的格式。这里做简要解释

构件参数的引用：首先判断是否为构件行，如为构件行，则=0，并设置格式为黄色，见条件2；如为项目行，则要看参数说明中是否需要输入该参数（这里采用findb函数），如需要输入，则= INDEX(M$3:M27,MATCH($D27,$C$3:$C27,0)查找该构件行的参数，并设置单元格为兰色（条件1），而当重新输入的数据后，单元格数据不等于其内公式计算的值（条件3），则显示黄色。单元格的底色为灰色。

3、工程量计算

辅助工作已完成，现在我们将建筑物拆分成构件并计数，按照构件列表项目复制到清单工作表中，工作表会依据标记的内容不同以黄色或兰色提示需要输入哪些参数。参数输入完成后会按自定义的公式计算工程量。

四、布局及汇总过程介绍

以上对各项工程量进行了计算，但由于项目数量繁多。为了使数据清晰，下面介绍如何对数据进行整理。

1、项目的处理，如下图：如某行输入的项目在前面已出现，则返回该项目序号，如为新项目，则项目序号为前面项目序号的最大值+1，编辑的公式及效果如下图：

第 10 页 共 14 页

此处使用了indirect函数，是为了避免插入复制行或删除行时公式出错，举例如下： 假设我们要实现如下公式

A1=0

A2=A1+1

A3=A2+1

…

如果我们直接输入这个公式，当我们在删除第二行后，第三行由于引用单元格被删除而出错，同理，插入行后，也不是我们需要的结果。因此修改公式如下： A1=0

A2=indirect(“A”&row()-1)+1

A2=indirect(“A”&row()-1)+1

…

有了项目号后，我们就可以在“汇总”表中查找并引用，实现列出项目名称的目的，这里我们采用矩阵格式汇总不同部位的相同项目，表格如下：

表中基本的公式为index(项目名称列,match(项目号，项目号列，0))。

match(项目号，项目号列，0)用来查找依次项目序号在“计算”表中的位置，index(项目名称列,)用来返回相应位置的项目名称。

这里的规格是按照计算表中出现先后顺序排序的，为使数据清晰，我们只需对B、C两列按C列进行排序，返回原顺序在B列只需重新输入1，2，3。。。，C列为自动计算。分类的名称号显示原理同项目序号。这张表的所有数据均为Excel自动计算。

五、项目综合单价的确定

汇总表中工程量计算出后，我们就可以进行定价，由于同一项目工程量已汇总并在右侧矩阵中显示详细分类，调整单价对总价的影响很直观。项目单价确定如下表：

支出费用主要包括材料费及大型机械台班费及门锁等配件，企业内部成本为按定额人材机消耗量计算出的其它费用或整体调控得到，占总价比重较小且价格相对比较

固定的项目，其价格与企业管理水平有关。

在工程量计算好后，结合初步定价，可以得到总价的大致组成，我们可以对占比重从大到小对项目进行详细材料询价和精细确认，使总价更加精确；而对于比重较小的项目可以使用较粗的价格，这样对总价的影响不大。

这里我的表格中清单编号和清单项目是没有完善的，此处我需对有些清单项目进行合并，有些清单项目还需进一步细分为子项。当工程量计算表及汇总表中有了确定的项目名称后，Excel应自动在另一列中显示相对应清单编号。此部分工作不是想马上做就能做出来的，需要多个工程的累计。

六、其它说明

1、辅助用表：构件数量的计数

当无电子版图时，可采用上表计算构件数量，中部单元格使用条件格式，实现如果构件名称为指定名称，则显示品兰色。69行以下将数据区域合并成一列，并计算构件的序号，根据此序号在22行显示构件名称，其右侧统计数量。当有电子版时，可采用CAD的查找与替换功能统计构件数量。

2、装饰算量使用此模板，会由于项目繁多，影响数据清晰，此时应采用CAD配合统计面积和长度，在电子版图纸上描出地面及吊顶边线，测量面积，同时描出并测

量线长统计内外装修面积。

3、计算时，节点上的项目一般计算到次构件中，比如插入砼柱中的砼梁钢筋应计算在砼梁构件中。利用这个原理，我们可以在计算门窗构件时，在列出门窗项目清单外，同时列出过梁、墙、内、外装修项目，利用门窗参数计算和扣减工程量。

七、总结

本模板在算量时具有一定的智能性，较手工算量效率大大提高；而利用excel的强大数据处理功能，实现了项目预算的整体性，对于宏观调整非常有效。能满足投标报价的精度要求。同时，本模板能较好地结合企业信息，配合企业定额的编制。

当然，本模板在项目计算的全面性、模板布局、操作技巧方面还有提高的空间，希望能为没有算量软件的人员带来方便，并一起完善模板，共同提高。

QQ:20439765

Email:yaker_77@

Mobile：13917345586

本文来源：https://www.bwwdw.com/article/bodm.html