投资信息处理与信息系统第7章.docx
《投资信息处理与信息系统第7章.docx》由会员分享，可在线阅读，更多相关《投资信息处理与信息系统第7章.docx（18页珍藏版）》请在冰豆网上搜索。
投资信息处理与信息系统第7章
第七章EXCEL在工程造价中的应用
工程造价信息处理的核心工作是“计算”，主要包括两方面的计算：
首先是算量，即根据工程设计图及计量标准计算工程量；其次是计价，即根据工程量及计价规范计算工程各部件价值和工程总价值。
对于小型工程项目，利用EXCEL计算功能强、表格操作简单的优势，进行工程算量、计价，既经济，又简单方便。
第一节EXCEL在工程算量中的应用
在EXCEL环境下进行简单工程算量的方法及步骤是：
（1）将准备好的工程项目资料数据输入到相应的工程项目文件（表）。
（2）以显式或隐式的方式输入各基本项目的计算式。
（3）计算基本项目的用量（工料或工时）。
一、工程项目资料数据准备
本节以某住宅装修工程为例说明用EXCEL计算简单工程量的方法。
（一）实例数据
某住户有三室两厅两卫一橱住宅一套，其装修基本数据及要求如表7-1所示：
表7-1住宅装修基本数据
居室名
尺寸（长╳宽）
装修要求
备注（楼层高3M）
客厅
5.4╳4.5
吊顶,顶、墙刷乳胶漆,铺地砖
1门（1╳2）2窗（1.5╳1.5）
餐厅
2.75╳2.5
顶、墙刷乳胶漆,铺地砖
1门（0.9╳2）1窗（1.5╳1.5）
卧室1
4╳3.5
顶、墙刷乳胶漆,铺实木地板
1门（0.9╳2）1窗（1.5╳1.5）
卧室2
5╳3
顶、墙刷乳胶漆,铺实木地板
1门（0.9╳2）1窗（1.5╳1.5）
书房
4╳3
顶、墙刷乳胶漆,铺实木地板
1门（0.9╳2）1窗（1.5╳1.5）
厨房
2.3╳1.6
吊顶，贴墙砖，铺地砖
1门（0.9╳2）1窗（1╳1.5）
卫生间1
3╳2
吊顶，贴墙砖，铺地砖
1门（0.8╳2）1窗（0.5╳0.6）
卫生间2
2.3╳1.6
吊顶，贴墙砖，铺地砖
1门（0.7╳2）1窗（1╳1.5）
要求根据表7-1中的数据，在表7-2中列出计算公式并计算工程量。
表7-2工程量计算表
序号
项目名称
单位
工程量
计算公式
1
墙面刷乳胶漆
平方米
2
顶部刷乳胶漆
平方米
3
铺实木地板
平方米
4
铺地砖
平方米
5
贴墙砖
平方米
6
防水处理
平方米
7
踢脚线
（橱卫无）
米
（二）计算公式
1．墙面积计算方法
墙面积＝各房间周长之和╳墙高－门窗及其他装饰墙面积
2．顶部及地面面积计算方法
顶（或地）面积=各房间面积之和－吊顶装饰物（或固定桌、柜）所占面积
3．防水处理面积计算
一般来说，橱房和卫生间都要进行防水处理，所以防水处理面积即为橱、卫地面积。
4．踢脚线长度计算
踢脚线长度=房间周边长度（即周长）－ 门及落地式壁柜宽度
根据表7-1给出的信息，可列出该装修工程各项目的计量公式，如表7-3所示。
表7-3工程量计算公式表
序号
项目名称
单位
工程量
计算公式
1
墙面刷乳胶漆
平方米
（5.4+4.5+2.75+2.5+4+3.5+5+3+4+3）*2*3
-1*2-1.5*1.5*6-0.9*2*4*2-0.9*2-0.8*2-0.7*2
2
顶部刷乳胶漆
平方米
5.4*4.5+2.75*2.5+4*3.5+5*3+4*3
3
铺实木地板
平方米
4*3.5+5*3+4*3
4
铺地砖
平方米
5.4*4.5+2.75*2.5+2.3*1.6+3*2+2.3*1.6
5
贴墙砖
平方米
（2.3+1.6+3+2+2.3+1.6）*2*3-1.5*1.5*2
-0.5*0.6-0.9*2-0.8*2-0.7*2
6
防水处理
平方米
2.3*1.6+3*2+2.3*1.6
7
踢脚线
（橱卫无）
米
（5.4+4.5+2.75+2.5+4+3.5+5+3+4+3）*2-1-0.9*4*2-0.9-0.8-0.7
二、输入公式并计算
有了公式，利用EXCEL进行表达式求值计算就很方便了。
计算时可用隐式方式，也可以用显式方式。
（一）用隐式方式计算
所谓隐式方式，就是直接在各工程项目相应的工程量单元格中输入用等号引导的公式，确认后EXCEL可以直接计算出结果并将结果信息在该单元格中显示出来。
在这种方式下，输入的公式被隐藏，只有将光标指向公式所在的单元格，公式才会在公式编辑栏中显示出来。
用隐式方式进行表7-3的工程量计算的步骤如下：
（1）建立工作表，输入项目基本信息。
（2）在工程量一列中输入相应公式，确认后即可得到如图7-1所示的计算结果。
图7-1隐式方式计算工程量
隐式方式的优点是表格简单清晰，也能满足在编辑状态下查询和修改计算式；其缺点是在打印出来的表格中无法查看计算式，计算方法不够“透明”。
（二）用显式方式计算
所谓显式方式，就是要设法让计算公式在计算表中“显露”出来。
在隐式方式下虽然也可以用单元格显示方式切换命令【Ctrl】+【`】来显示单元格中的公式，但公式的计算结果又被隐藏，真可谓“鱼和熊掌不可兼得”。
显示方式就是要使公式和值同时“显露”，这就需要在表中增加一个名为“计算公式”的列，该列最好置于工程量单元格右（或左）相邻的位置。
公式以文本型数据输入到计算公式单元格，为了使公式显露出来，公式前不能用等号“=”引导。
这种方式的关键是要解决在工程量单元格的公式中如何引用其他单元格里以文本数据类型存在的“公式信息”来实现自动计算的问题。
为了解决这个问题，需要用到EXCEL中“名称定义”及表达式求值的功能。
有关EXCEL中“名称定义”及表达式求值的功能使用方法，读者可以通过阅读相关的参考书或EXCEL帮助信息获取相关知识，也可从下面的操作过程介绍中把握其要领。
（1）建立工程量基本信息表，如图7-2所示。
图7-2工程量计算表初始数据
（2）定义名称。
其操作过程如下：
打开【插入】工具包，选择“定义名称”按钮，进入如图7-3所示的对话框。
图7-3“定义名称”对话框
在“名称（N）：
”提示信息下面的输入框中输入名称“工程量”，再在“引用位置（R）：
”的提示信息下面的输入框中输入：
“=IF（Sheet1!
$F5<>””,Evaluate（Sheet1!
$F5）,””）”，点击【确定】按钮，即可完成名称的定义。
公式“=IF（Sheet1!
$F5<>””,Evaluate（Sheet1$F5）,””）”是条件表达式函数，其功能是根据单元格F5中值取值，当F5不等于空字符串时，将F5中的字符串作为表达式求值，并返回计算结果。
如果F5中的值为空，则返回空字符串。
如果F5中的值为非法表达式，则返回“#Value”或“#Name”等信息，表明求值失败。
（3）在公式中引用名称：
在单元格E5中输入公式“=工程量”，确认后即可得到F5中表达式的值。
（4）用填充柄将E5复制到E11，即可得到所需要的全部计算结果，如图7-5所示。
图7-4定义名称
图7-5利用名称计算工程量
显式方式弥补了隐式方式“不够透明”的缺陷，使用户可以很方便地查看和修改计算公式。
例如，如果发现计算公式中某项数据有误，如客厅长度“5.4”应为“4.5”，只需直接将相关单元格中的数据改正，EXCEL将会立即计算出新的结果，如图7-6所示。
图7-6计算公式修改与重新计算
第二节EXCEL在工程计价中的应用
在EXCEL环境下进行简单工程计价的方法及步骤是：
（1）将准备好的工程项目资料数据直接或间接地输入到相应的工程项目文件（表）。
（2）定义计算式。
（3）计算基本项目的价格（材料费或工时费）。
（4）分类汇总。
（5）计提管理费及税金。
（6）计算工程总费用。
一、EXCEL简单工程计价应用实例数据资料
某家庭装修工程项目数据如表7-4所示，试用EXCEL编制工程费用表。
表7-4简单工程项目数据资料
行号
项目号
项目名称
单位
数量
单价
金额
备注
1
1
顶部工程
=下四项之和
2
101
客厅吊顶
项
1
1200
3
102
过道吊顶
项
1
200
4
103
厨卫吊顶
M2
13.4
75
5
104
顶部乳胶漆
M2
77
12
6
2
地面工程
=下四项之和
7
201
客厅铺地砖
M2
26
30
8
202
厨卫铺地砖
M2
21
30
9
203
铺实木地板
M2
38
45
10
204
防水处理
M2
13.4
20
11
3
墙面工程
=下五项之和
12
301
墙面乳胶漆
M2
90
12
13
302
墙面乳胶漆
M2
70
15
14
303
厨卫墙砖
M2
46
30
15
304
踢脚线
M
64
16
16
305
木角线
M
44
14
17
18
4
门窗工程
=下四项之和
19
401
房门及门套
项
3
580
20
402
磨砂玻璃门
项
2
650
21
403
餐厅梭门
项
1
850
22
0404
包窗套
项
5
180
23
24
5
木制工程
=下四项之和
25
501
鞋帽储物柜
项
1
1700
26
502
壁柜
项
1
1650
27
503
衣柜
M2
6.58
420
28
504
书桌
M
3.4
350
29
30
6
水路改造
=下四项之和
31
601
水路改造
套
1
600
32
602
洁具安装
套
1
100
33
603
蹲便器安装
项
1
100
34
604
包管道
根
3
80
35
36
7
电路改造
=下三项之和
37
701
电路改造
套
1
600
38
702
装开关插座
套
1
100
39
703
灯具安装
套
1
150
40
41
8
其他项目
=下四项之和
42
801
拆墙与粉平
项
1
200
43
802
除渣
项
1
200
44
803
运输
项
1
350
45
804
力资
项
1
300
46
A.直接费
=01~08之和
47
B.管理费
=10%×A
48
C.税金
=3.54%×A
49
D.总计
=A+B+C
二、用EXCEL编制工程费用表的方法
下面介绍用EXCEL编制工程费用表的两种方法。
方法一（直接输入基本数据）：
在一张工作表中完成数据录入和计算。
其步骤如下：
（1）从开始菜单下的程序级联菜单中找到并启动EXCEL应用程序，进入如图7-6所示EXCEL程序窗口。
图7-6EXCEL应用程序窗口
（2）输入表7-4中的数据到工作表，如图7-7所示。
图7-7建立工作表并输入数据
（3）保存工作簿文件到指定位置：
选择【保存】工具按钮，选择文件保存位置D：
\GCZJ，输入文件名“简单工程预算表”，然后按【保存】按钮。
如图7-8所示。
图7-8保存工作簿文件操作
（2）计算明细项目金额：
在单元格G3中输入公式“=E3*F3”，并用“填充柄”将其向下拖到单元格G48，即可完成全部明细项目的金额计算，如图7-9所示。
图7-9计算明细项目金额
注：
为使表格中数据更清晰，可删除空行中的“0”。
（5）计算分类项目金额：
在单元格G2中输入公式“=G3+G4+G5+G6”，即可得到第一项分类项目“顶部工程”的金额。
按此方法，可分别计算出其他7项分类项目的金额。
如图7-10所示。
图7-10计算分类项目金额
（6）计算直接费：
在单元格G50中输入公式：
“=SUM（G2,G8,G14,G21,G27,G33,G39,G44）”或“=SUM（G2:
G48）/2”
即可得直接费A金额。
如图7-11所示。
图7-11计算直接费
（7）计算管理费（B）、税金（C）和总计（D）：
在单元格G51、G52、G53中分别输入公式“=G50*10%”、“=G50*3.54*%”、“=G50+G51+G52”，即可得到结果，如图7-12所示。
由图7-12可知，该装修工程总费用为30543元。
方法点评：
这种方法只用一张工作表完成数据录入和计算。
优点是直接模拟手工做法，方法简单，界面直观。
缺点是原始数据录入量较大，特别是价格信息随意录入，既不规范又易出错。
图7-12计算管理费、税金和总计
方法二（间接输入基本数据）：
用两张工作表，实行“量价分离”。
其步骤如下：
（1）建立工程项目价格信息表。
表内容包括项目编号、项目名称、计量单位、单价等。
该工作表中的信息为多个项目共享信息，可用专门的工作簿文件存放。
如图7-13所示。
图7-13装修工程项目信息
（2）建立工程量表。
如图7-14所示。
图7-14建立工程量表
（3）输入项目数据及自动取数函数。
具体操作步骤如下：
在单元格A2中输入项目号（文本型）1；
在单元格B2中输入公式：
“=VLOOKUP（A2，”[工程项目信息表.XSL]SHEET1”!
$A$2:
$D$100,2,0）”
确认后单元格B2中自动显示“顶部工程”；
在单元格C2中输入公式：
“=VLOOKUP（A2，”[工程项目信息表.XSL]SHEET1”!
$A$2:
$D$100,3,0）”，确认后单元格C2中自动显示“0”，这是因为在“工程项目信息表”中该行无值的缘故，先不要将此处的“0”删除（若删除，则定义的公式将随之删除），可在数据全部录入完成后再将其删除；
在单元格D2中输入公式：
“=VLOOKUP（A2，”[工程项目信息表.XSL]SHEET1”!
$A$2:
$D$100,4,0）”，确认后单元格D2中也自动显示“0”，可在数据输入完后将其删除。
如图7-15所示：
图7-15定义各列数据来源
将单元格B2、C2、D2中的公式分别向下拖到合适位置（以能存放下全部数据为准，本例中拖至第41行即可）；
在第一列（A3:
A41）中输入项目号，其余各列数据将自动获得，如图7-16所示。
图7-16项目基本数据录入
（4）输入工程量并计算金额。
先在E3中输入数量（数值型）1，再在F3中输入公式“=D3*E3”，F3中立即显示计算结果1200。
将F3中的公式向下拖至F41，输入各行中的数量，金额栏中自动显示金额值。
如图7-17所示。
图7-17输入数量自动计算金额
（5）计算分类项目的金额、直接费、税金、管理费及工程总费用，方法同方法一，在此不再赘述。
（6）删除表中多余的“0”，即可得到和方法一同样的结果（如图7-12所示）。
在输入项目数据时用到从其他工作簿中的表获取数据的技术方法。
这种技术方法主要是利用函数VLOOKUP的搜索功能，即在搜索表指定的区域中查找满足条件的行，并返回该行中由参数指定的列中的值。
VLOOKUP函数是一个很有用的函数，其格式为
=VLOOKUP（查找值，查找表或区域，列号，检查类型）。
其中：
第一个参数“查找值”为查找条件，函数按该值在查找表或区域首列中检索与之相匹配的值所在的行。
该参数可以是常数，也可以是单元格变量。
第二个参数“查找表或区域”为检索范围，检索范围的区域可以是当前工作表中的区域，也可以是当前工作簿中另一工作表中的区域，还可以是非前工作簿中的工作表中的区域。
第三个参数“列号”指定返回值所在的列序号，列序号的值不能超出检索区域的列序号范围。
第四个参数“检查类型”规定检索比较方式，有两种可选择的比较方式，即精确比较和非精确比较。
参数值只能取“0”或“1”，如果取“1”或忽略，则为非精确比较，取“0”则为精确比较。
例如，执行单元格B2中的函数
=VLOOKUP（A2，”[工程项目信息表.XSL]SHEET1”!
$A$2:
$D$100,2,0）
后，返回的值是“顶部工程”，即用A2中的值“1”（项目编号值）在“工程项目信息表.XSLX”工作簿文件的“SHEET1”工作表的数据区“A2:
A100”的首列中查找与之相同的值所在的行，返回该行第2列的值，该值正好是“顶部工程”。
方法点评：
这种方法需用两张工作表，其一用于存放相对固定的项目基本信息，其二用来存放具体项目数据。
优点是数据录入方便、快捷，自动化程度较高；项目的基本信息独立存放，既规范又便于维护。
缺点是对数据录入人员操作要求较高，比如要求数据录入员熟记项目编号，且严格按照规定的数据类型输入编号信息（必须与项目基本信息表中的编号数据类型完全一致）。
经过比较可以看出，上述两种方法各有利弊。
实际工作中，我们可以利用现存项目数据工作表和电子数据易于复制的特点来建立一个新的项目数据表。
例如，在已建“项目-01.XLS”文件的情况下再建“项目-02.XLS”文件，可以用下面的操作完成：
先打开文件“项目-01.XLS”，将其另存为“项目-02.XLS”；对“项目-02.XLS”作适当修改（一般只需增加少量行或删除表中部分行和改变数量列中的数据），一般情况下，新项目可延用已建项目的计算方法，当数据修改完成后即可自动计算明细项目金额和总金额。
从这个简单的方法中，我们能够感受到电子化计价相对于手工计价的无比优越。
本章小结
工程造价的核心工作是“计算”，主要包括两方面的计算。
首先是算量，即根据工程设计图及计量标准计算工程量；其次是计价，即根据工程量及计价规范计算工程各部件价值和工程总价值。
对于小型工程项目，利用EXCEL计算功能强、表格操作简单的优势，进行工程算量、计价，既经济，又简单方便。
在EXCEL环境下进行简单工程算量的方法及步骤是：
（1）将准备好的工程项目资料数据输入到相应的工程项目文件（表）。
（2）以显式或隐式的方式输入各基本项目的计算式。
（3）计算基本项目的用量（工料或工时）。
在EXCEL环境下进行简单工程计价的方法及步骤是：
（1）将准备好的工程项目资料数据直接或间接地输入到相应的工程项目文件（表）。
（2）定义计算式。
（3）计算基本项目的价格（材料费或工时费）。
（4）分类汇总。
（5）计提管理费及税金。
（6）计算工程总费用。
升级会员 