Excel软件在工程进度款计算及造价结算中的应用

王 明 镜

(福建华航建设集团有限公司厦门分公司，福建 厦门 361004)

·计算机技术及应用·

Excel软件在工程进度款计算及造价结算中的应用

王 明 镜

(福建华航建设集团有限公司厦门分公司，福建 厦门 361004)

介绍了进度计算和工程造价结算的一般方法，提出了把建设工程合同报价书转化成Excel电子表格，通过改造该电子表格，使得工程进度款计算文件或工程造价结算文件的制作不必依赖电脑计价软件，并能编排成直观易懂的全费用清单格式的文件，以简化造价文件编制工作。

Excel应用，工程量清单，进度款，结算，全费用清单

1 概述

作者在工程建设项目的管理实践中，通过解析了规范格式的工程造价的构成，使用Excel电子表格软件，用一种相对于大多数房屋建筑工程项目的通常做法而言全新的方法，对按照国家规范GB 50500—2008建设工程工程量清单计价规范制作的合同报价书进行改造,并制作成简单易用的全费用清单格式的Excel电子表格，用于工程进度款计算和工程造价结算，使进度款和造价结算的申报和审核工作得到简化。

2 进度款计算和工程造价结算的一般方法

工程进度款计算和工程造价结算以合同报价书为依据和蓝本。合同报价书是建筑工程合同文件的构成部分之一；它通常是由电脑计价软件生成、打印的，它的各项内容的编排(如项目的编号、名称及排列顺序，单价及合价组成，各种汇总，版面分页等等)有固定的格式，其表格形式与GB 50500—2008建设工程工程量清单计价规范所列表格相同[1]。每份报价书包含20多种表格，总页数都在200页以上。

因为计算项目繁多，所以工程进度款计算文件或工程造价结算文件(以下简称“造价文件”)的编排格式要与合同报价书一致，才便于各方人员的校对、审核。也就是说，每份造价文件实际上就是一份完整的“报价书”。这种格式的造价文件篇幅巨大，各条目之间的算术关系不直观，查阅、审核时十分费时费力。

为了快速地制作、审核其编排格式与合同报价书相同的造价文件，通常都是使用与制作合同报价书所用相同的计价软件来操作。这就要求施工、监理、建设各单位相关人员都必须配备相同的计价软件，并且要通过学习、培训掌握该软件的操作；为此，各单位往往还需要为每个工程建设项目配置专门的工程造价人员，从而增加成本支出。

如果可以只使用Excel软件而不必使用计价软件来编制造价文件，那么，一般工程人员通过简单的电脑操作就可以完成造价文件编制的主要工作，而不必劳烦专门的工程造价人员。

3 使用Excel软件编制“工程量清单”格式的造价文件

如果没有安装相应的计价软件，则由计价软件生成的合同报价书电子版无法直接在电脑中打开。可以利用计价软件的导出Excel电子表格即“xls(或xlsx)文件”的功能，将合同报价书转为Excel文件。这样，电脑只要安装了极为普通常用的Excel或WPS软件，就可以打开和操作合同报价书电子版。

但是，合同报价书如果只是简单地转为Excel文件，是无法用来制作造价文件的。这是因为：1)转换后的Excel表格内的“数字”其实是文本格式的阿拉伯字符，不能直接用于Excel计算。2)转换后的Excel表格内数字的算术关系丢失了，不能实现自动计算。

所以，将合同报价书导出为Excel电子表格格式的文件后，还必须对其进行改造，才能用于后续施工过程中造价文件的制作。改造的具体步骤和方法如下(以Excel 2010的操作[2]为例)。

3.1 把各工作表中文本格式的阿拉伯字符转换为数字

1)先使用“另存为”命令后再继续后面的操作，因为合同报价书原始文件很可能需要用于查对，要避免被更改。

2)在合同报价书(.xls或.xlsx文件)工作簿的某个工作表中，选中需要转换的单元格区域；这时选中区域的左上角的左侧会出现一个“！”标记；将鼠标移近“！”标记，在标记的右边会出现一个向下的小黑三角；点击小黑三角，弹出下拉菜单；点击下拉菜单上的“转换为数字”。

3)在所有工作表中重复上一步操作，把合同报价书中所有文本格式的阿拉伯字符转换为阿拉伯数字。

a.选中工作表中需要去除单元格中换行符的区域。b.同时按住Ctrl键和F键，弹出“查找和替换”对话框。c.点击“替换”选项卡，在“查找内容”后面的文本框里点击鼠标，清空该文本框里的内容，将光标停留在此处。d.按住Alt键，同时用数字小键盘输入“10”(这时“查找内容”后面的文本框里不会显示任何内容)，松开Alt键。e.清空“替换为”后面的文本框里的内容。f.点击“全部替换”按钮。

在所有工作表中重复上述操作，使需要恢复算术关系的所有单元格里的阿拉伯数字都可以用于算术运算。上述操作也可以去除一些没必要手动分行排列的文本单元格(如“项目名称”和“项目特征描述”单元格)中的换行符，以方便调整行高和列宽。

3.2 恢复每个工作表中各级汇总数字的算术关系

3.2.1 首先明确工程造价的构成

工程造价=各单项工程造价之和。单项工程造价=各单位工程造价之和。单位工程造价=①分部分项工程费+②措施项目费+③其他项目费(如有发生)+④规费+⑤税金。其中：

①分部分项工程费=∑人工费+∑材料费+∑机械费+∑企管费+∑利润+∑风险费(分部分项工程费也就是合同报价书中“分部分项工程量清单与计价表”(未列出综合单价的构成)或“分部分项工程量清单综合单价分析计算表”(列出了综合单价的构成)中各项目的“合价”之累计)。

②措施项目费=措施项目费(一)(也即“通用措施项目费”)+措施项目费(二)(也即“专业措施项目费”)。

措施项目费(一)=文明施工费+安全施工费+临时设施费+夜间施工费+已完工程及设备保护费+风雨季施工增加费+生产工具用具使用费+工程点交、场地清理费(措施项目费(一)的各子项均以分部分项工程费总计为计算基础，按一定费率计取。其中文明施工费、安全施工费、临时设施费按现行规定属不可竞争项目)。

措施项目费(二)=环境保护费+混凝土、钢筋混凝土模板及支架费+脚手架费+垂直运输机械费+大型机械设备进出场及安拆费+大型机械设备基础费+大型机械设备检测费+施工排水降水费+其他措施费(措施项目费(二)的各子项按实际工程量计算造价。其中环境保护费按现行规定属不可竞争项目)。

③其他项目费(如有发生)=暂列金额+专业工程暂估价+计日工+总承包服务费。

④规费=工程排污费+劳保费+危险作业意外伤害保险费。

⑤税金=(①+②+③+④)×税率。

单位工程造价各费用构成之间的算术关系示例见表1。

3.2.2 设置Excel工作表中单元格之间的算术关系

明确了工程造价的构成后，很容易就可以知道并设置各单元格之间的算术关系，即设置Excel单元格公式。例如：

在表1中，设置E5单元格的公式为E5=SUM(E6∶E15)。完成此设置可以通过以下不同的操作来完成：1)双击E5单元格，在E5单元格中或表格顶部的编辑栏公式框中输入字符“=SUM(E6∶E15)”。2)点击E5单元格，再点击“∑自动求和”命令按钮，选取E6～E15单元格区域。类似地，设置C42=E5+E16+E34+E37，E43=E5+E16+E34+E37+E41，等等。

又如：在表2中，设置M13=E13*L13，M12=SUM(M13∶M18)。

在设置单元格公式时应注意一个问题：进度款计算或工程造价结算时不会变化的因素不要设置公式。因为在进度款计算或工程造价结算时，各分部分项工程项目(以下亦称“合并项”，通常都包含若干子项)单价是不变的，变化的只是合并项所包含的子项的工程量，合并项(如表2中第12行)的数量是根据其“合价”和既定的“综合单价”反算出来的。所以，不管某个合并项只包含一个子项或者包含多个子项，在设置其数量、综合单价、合价的算术关系时，要假定其数量是未知的，对数量设置公式，即设置“数量=合价÷综合单价”“合价=∑子项合价”。例如，在表2中，要设置E12=M12/L12，M12=SUM(M13∶M18)；而不要设置L12=M12/E12。

在工作表中设置Excel单元格公式时，可以利用Excel的复制粘贴和填充柄功能，让具有相同规律公式的单元格快速自动地填充公式，还可以充分利用Excel的函数、筛选、排序功能，以加快完成设置公式的工作。

跨工作表设置单元格公式时，进行以下操作即可：1)点击需要设置公式的单元格，输入“=”号。2)点击要进行算术运算的第一个单元格(在同一个工作表或另一个工作表中)。3)输入运算符号，点击下一个要进行算术运算的单元格(在同一个工作表或另一个工作表中)。4)重复上一步骤，直至点击完所有要进行算术运算的单元格。5)在编辑栏公式框中修正运算符号、完善公式，按回车键。

在合同报价书的每个工作表中进行类似操作，完成各单元格算术关系的设置。

3.3 进一步改造工作表

在编制造价文件的实际应用中，还必须对合同报价书中的工作表进一步改造。比如，表2中的“数量”一列，在实际应用时应为“本期数量”。如果用于编制进度款造价文件，可以在“数量(即清单工程量)”列后插入“截止上期累计完成量”“本期乙方申报量”“本期乙方审批金额”“本期监理审核量”“本期监理审批金额”“本期甲方审批量”“本期甲方审批金额”各列；在“合价”列后插入“截止本期累计完成量”“截止本期累计完成百分比”“截止本期累计完成金额”各列；按各列的算术关系设置公式。这样便于清晰直观地核验填报数字，以及从财务方面掌握工程进展情况。插入的各列可视需要予以隐藏或显示。示例如表3所示。

表3中，“截止上期累计完成量”“本期乙方申报量”“本期监理审核量”“本期甲方审批量”必须手动填写，其中“截止上期累计完成量”可以从上一期(比如2014年10月份)的“截止本期累计完成量”列复制、粘贴而得，但要注意，必须在撤销工作表保护、筛选项全选、没有隐藏行的情况下进行，才能避免错误。

如果用于编制结算文件，在“数量(即“清单工程量”)”列后面插入用于比较的“结算工程量”列就可以了。

3.4 保护工作表

完成设置公式和改造工作表后，应进行“保护工作表”的操作，以防所设置的公式和表格格式在造价文件申报填写、审核的过程中因各种原因而被破坏。具体操作如下：

点击工作表左上角的方框全选工作表，点击右键，点击“设置单元格格式”；点“保护”选项卡，清空“锁定”“隐藏”复选框，按“确定”。

选取整个表格所在的单元格区域，点击右键，点击“设置单元格格式”；点“保护”选项卡，选中“锁定”复选框，按“确定”。

选择表格中不能锁定的单元格区域，如表3中“本期乙方申报量”“本期监理审核量”“本期甲方审批量”各列的子项。对于行列数量较多的大表格，如表3所示，此操作可以利用“筛选”功能来简化：选取表格所在单元格区域，点击“数据”功能选项卡，再点击“筛选”图标按钮；点击表头中“序号”右下角的黑三角，点击“文本筛选”“包含”，在文本框中输入“(”符号，点击“确定”，这样，所有子项(其序号都包含“(”符号)都被筛选出来了；选取这些子项单元格区域，点击右键，点击“设置单元格格式”，再点“保护”选项卡，清空“锁定”“隐藏”复选框，按“确定”。

要使单元格“锁定”“隐藏”的特性生效，还要进行以下操作：点击“审阅”功能选项卡，再点击“保护工作表”图标按钮，输入所要设置的密码，按确定，再输入一遍密码，按“确定”。

保护工作表的密码通常由最终审定者如建设单位造价负责人员保管。

3.5 完成“工程量清单”格式的造价文件的编制

经过以上步骤的操作，以报价书作为蓝本来进行编制改造而得的造价文件就完成了；其中需要进行算术运算的单元格可以实现自动计算；编制某期造价文件时只需要以前一期的造价文件为模板，修改工程量(各单价和费率保持不变)就可以自动得出造价。

实际操作时，可按以下步骤进行(以进度款计算为例)：1)打开上一期进度款造价文件，另存，以上一期进度款造价文件为蓝本制作本期进度款造价文件；2)撤销工作表保护、取消筛选、取消隐藏行；选取“截止本期累计完成量”列下面的数据，用右键拖动到“截止上期累计完成量”，点“仅复制数值”，即把上一期的累计数正确、快速地填好；这个步骤一定要先完成再继续后面的步骤；3)在表3中按“序号”列筛选出各个子项(即序号中包含文本“(”的项目)；4)选取筛选后的“本期乙方申报量”“本期监理审核量”“本期甲方审批量”区域，清除内容，即清除上一期的申报数据；5)取消筛选、取消隐藏行、保护工作表；依次交由申报人(乙方)、审核人(监理)、审批人(甲方)填写。

但“工程量清单”格式的造价文件和合同报价文件相似，各条目之间的算术关系(造价构成)不直观，篇幅巨大，有失工作的简便和高效。

4 使用Excel软件编制“全费用清单”格式的造价文件

“全费用清单”计价不是我国的现行规范，“全费用清单”目前没有统一的标准格式，它是指针对分部分项工程每一个条目及其子目列出所有造价构成的计价清单；每个条目或子目对应的单价是已经囊括了所有成本费用(分部分项工程费、措施项目费、其他项目费、规费、税金)的“全费用单价”；累计所有子目对应的“合价”，就得出总造价。而“工程量清单”按GB50500—2008建设工程工程量清单计价规范的定义是“建设工程的分部分项工程项目、措施项目、其他项目、规费项目和税金项目的名称和相应数量等的明细清单”。“全费用清单”和“工程量清单”的根本区别是：前者先分再合(先计算每个项目的造价，再合并各个项目的造价得出总造价)，后者是合而不分(其计算程序见表1)；前者篇幅小、算术关系清晰、直观、简单，后者篇幅大、算术关系模糊、抽象、复杂。

GB50500—2008建设工程工程量清单计价规范只在第1.0.3条规定“全部使用国有资金投资或国有资金投资为主的工程建设项目，必须采用工程量清单计价”，并未限定非国有资金投资的工程建设项目也必须采用工程量清单计价。所以，为了提高工作效率，对非国有资金投资项目而言，在实际工作中完全可以把前述“工程量清单”格式的造价文件改造成“全费用清单”格式的造价文件，使所编制的Excel表格更有实用价值。编制“全费用清单”的步骤如下。

4.1 插入体现全费用单价各费用构成的列

以前面的表3为基础，在“综合单价”列后面插入体现项目全费用单价各费用构成的列(见表4)。

4.2 插入体现全费用合价各费用构成的列

以前面的表3为基础，在“截止本期累计完成金额”(即“全费用合价”)列后面插入体现项目全费用合价各费用构成的列(见表5)，这些列里的数据须用于汇总统计。

4.3 设置公式

4.3.1 按造价构成的算术关系设置单元格公式

按照前述造价构成的算术关系(如表1所示)设置单元格公式。例如，在表4中：

文明施工费单价=综合单价×1.13%。工程点交、场地清理费单价=综合单价×0.07%。

环境保护费单价=综合单价×环境保护费率。

工程排污费单价=(综合单价+措施费单价小计)×工程排污费率。

全费用单价=综合单价+措施费单价小计+规费单价小计+税金单价，等等。

其中：

环境保护费率=∑单位工程环境保护费÷∑单位工程分部分项工程费；

工程排污费率=∑单位工程排污费÷(∑单位工程分部分项工程费+∑单位工程措施项目费)。

通常情况下工程排污费在竣工结算时凭缴费凭据按实结算。

在表5中，按照“费=甲方审批量(见表3)×单价”的简单算术关系设置单元格公式。

在实际应用中，为了在形式上与合同报价书相同，改造后的表格中包括分部分项工程项目、措施项目在内的所有项目都与合同报价书相同，见表4。所以，应该注意的是：既然专业措施项目(如模板、脚手架等)已像合同报价书一样单列出来，则各分部分项工程项目的“专业措施费单价”应为零；而各专业措施项目(如模板、脚手架等)的“综合单价”应为零，其综合单价填在“专业措施费单价”列里，这样，表4中AR列即“全费用单价”单元格的公式(如AR296=U296+AH296+AN296+AO296+AP296+AQ296)才会统一，便于使用“复制、粘贴”的功能快速完成公式输入。

4.3.2 设置公式技巧

如前3.2.2所述，要对合并项的数量设置公式；制作全费用清单造价文件时，同样要对合并项的其他字段(如表6中AU～BF列)设置公式。如果合并项所包含的子项条目数量不同，则其相同字段的公式也不同，所以使用“复制、粘贴”功能设置公式时，只能针对具有相同子项条目数量的合并项(以下称“同类合并项”)进行操作。但因为表格庞大，同类合并项在表格里不好找到，如果逐行查找同类合并项、粘贴公式，十分费时费力。

可以使用“筛选”和“排序”的功能在同类合并项之间快速地“复制、粘贴”公式。以表6(已隐藏了一些行、列)为例，操作步骤如下：

在“序号”插入“子项数目”列，填写各合并项所包含的子项数目。

选取整个表格区域，点击“数据”功能选项卡，再点击“筛选”图标按钮，标题行各单元格右下角出现了向下的小三角即筛选标志；点击“子项数目”的筛选标志，去掉“(空白)”前面的“√”号，点确定，就把所有合并项筛选出来了。

选取整个表格区域，点击“数据”功能选项卡，再点击“排序”图标按钮，在“主要关键字”的下拉列表中点选“子项数目”，点击“确定”，就把同类合并项排列在一起了，见表7(已隐藏了一些行、列)。这样，对于每类同类合并项，只要设置一次公式，再将公式“复制、粘贴”就行了。如：

设置好AU21∶BF21各单元格公式后，复制AU21∶BF21区域，粘贴到AU25∶BF33区域即可。

注意：完成以上操作后，要让表格各行恢复到原始的排列顺序，即以“序号1”(如表7所示)为主要关键字对整个表格排序，这样才能保证公式所引用的单元格准确无误。

4.4 表格排版

完整的“全费用清单”计算表格包括表4～表6中的各列。但在实际应用中，可以隐藏一些列(没有必要显示的列)，以便缩小版面，减少页数。以进度款造价文件为例，排版后的表格(已隐藏了一些行)如表8所示。

4.5 制作汇总表

制作造价文件时，通常还要有汇总表来体现工程款的构成及其支付情况；特别是，按建设主管部门的要求，必须体现安全文明措施费的支付情况。该汇总表内的各项明细金额可以通过设置简单的四则运算公式，直接从明细表格(如表6所示)引用，参前3.2节。以进度款造价文件为例，汇总表如表9所示。

表9中C30单元格(大写金额)的公式为：=IF((INT(C28*10)-INT(C28)*10)=0,TEXT(INT(C28),″[DBNum2]″)&″圆″&IF((INT(C28*100)-INT((C28)*10)*10)=0,″整″,″零″&TEXT(INT(C28*100)-INT(C28*10)*10,″[DBNum2]″)&″分″),TEXT(INT(C28),″[DBNum2]″)&″圆″&IF((INT(C28*100)-INT((C28)*10)*10)=0,TEXT((INT(C28*10)-INT(C28)*10),″[DBNum2]″)&″角整″,TEXT((INT(C28*10)-INT(C28)*10),″[DBNum2]″)&″角″&TEXT(INT(C28*100)-INT(C28*10)*10,″[DBNum2]″)&″分″))。

4.6 完成“全费用清单”格式的造价文件的编制

编制某期“全费用清单”格式的造价文件时，只要以前一期的造价文件为模板，修改工程量(各单价和费率不变)就能自动得出造价、自动生成汇总表。其操作步骤和前述“3.5完成‘工程量清单’格式的造价文件的编制”相同。

5 结语

Excel软件的功能十分强大；只要掌握了它的基本操作以及工程造价的构成和计算程序，不必使用专门的造价软件和造价人员便可编制准确、方便、实用的造价文件电子表格，从而降低从事造价文件编制工作的门槛，提高工作效率。

[1]GB50500—2008，建设工程工程量清单计价规范[S].

[2]ExcelHome.Excel2010应用大全[M].北京：人民邮电出版社，2011.

The application of Excel software in engineering progress payment and cost settlement calculation

Wang Mingjing

(XiamenBranch,FujianHuahangConstructionGroupLimitedCompany,Xiamen361004,China)

This paper introduced the general method of progress calculation and engineering settlement, put forward conversion of construction engineering progress payment into Excel spreadsheet, through the spreadsheet transformation, made the progress payment calculation file or engineering cost settlement file production not need to rely on computer software and valuation, arranged into full expenses list format file to understand, to simplify the cost file documentation work.

Excel application, bill of quantity, progress payment, settlement, full expenses list

2015-01-17

王明镜(1974- )，男，工程师

1009-6825(2015)09-0253-06

TP391.13

A