电子表格Excel在工程造价中的应用之材料调差完整Word下载.docx

1、某工程商品混凝土由承包人提供，采购的混凝土单价为：C20:328元/m,C25:307元/m,C30:346元/m，如下表：承包人提供主要材料和工程设备一览表（适用造价信息差额调整法）序号型号单位数量风险系数基价投标价发包人确认价备注1C20m305%310307309.512C25450325328326.253C301955340 表1解析1、C20:328/310-1=5.81%投标价低于基准价格，涨幅按照基准价格为准，超过风险范围，应予调整调整价格为：307+310*（5.81%-5%）=309.51元/m2、307/325-1=5.54%投标价高于基准价格，跌幅按照基准价格为准，超过

2、风险范围，应予调整328-325*（5.54%-5%）=326.25元/m3、346/340-1=1.76%投标价等于基准价格，跌幅按照基准价格为准，风险范围内，不予调整（二）以上案例通过表格及常规数学计算不易直观表达，下面通过数轴方式简化。 1、基准价格和投标价格的对比可用三条数轴表示： 基价 投标价数轴1 投标价 基价数轴2 基价数轴32、以上三个数轴上右边点（中间一个点）的数据乘以（1+风险值%）=涨幅上限，左边点（中间一个点）的数据乘以（1-风险值%）=跌幅下限，便形成一个数轴： 下跌区间 不调区间 涨幅区间 A 跌 涨 B 数轴4其中：圆形表示跌幅下限（左），涨幅上限（右），三角形表

3、示实际采购的价格，AB表示采购价格与跌幅下限和涨幅上限的差的绝对值1）当采购价格处于下跌区间：调整价格=投标报价-A2）当采购价格处于不调区间：调整价格=投标报价3）当采购价格处于涨幅区间：调整价格=投标报价+B（三）用数轴验证案例：承包人提供主要材料和工程设备一览表可按照跌幅下限和涨幅上限调整为：跌幅下限涨幅上限291.65325.5309.5308.75344.4323357 表2步骤：1）将3种型号的混凝土基价和投标价分别放入数轴1-3中，按照跌幅涨幅风险系数算出跌幅下限和涨幅上限填入表2；2）将表2中跌幅下限和涨幅上限填入数轴4，形成3个区间；3）C20采购价格328元/m，处于涨幅区

4、间，调整价格：307+328-325.5=309.50元/m；4）C25采购价格307元/m，处于跌幅区间，调整价格：328-（308.75-307）=326.25元/m5）C30采购价格346元/m，处于不调区间，调整价格=投标报价=340元/m。（四）对比分析两种方法计算结果一样（四舍五入会有误差），第一种方法是按照涨跌的总比例扣除风险比例，第二种数轴法预先设定三个区间，判断采购价所处区间后扣除差值，简单明了。三、Excel表格制作 本文以数轴法为基础制作Excel表格，步骤如下：（一）前文已经讲到要预先设定3个区间，这就需要确定跌幅下限和涨幅上限， 跌幅下限=A*（1-风险值%） 涨幅上

5、限=B*（1+风险值%） 因风险值是常数，这就需要确定A和B,及基价和投标报价，从清单规范不难得出： 1）当基价投标报价时，A=基价，B=投标报价， 2）当基价投标报价时，A=投标报价，B=基价， 3）当基价=投标报价时，A=B=基价。 综上，A=MinA，B,B=MaxA,B。利用EXcel中Min,Max公式可确定，如下： F2中键入：=MIN（D2,E2）*0.95G2中键入：=MAX（D2,E2）*1.05（二）判断采购价格所处区间1）首先需要在Excel中插入一行采购价，如下：2）应用Excel的“IF”公式，格式=IF（logical_test,value_if_true,valu

6、e_if_false）,及测试一个单元格的条件，如果条件成立返回一个值（可以是计算公式），如果条件不成立返回一个值（可以是计算公式）2。测试如下：B2=80,A2=100。因测试条件80100，条件不成立，返回公式B2/2，及计算结果55。（备注：以上测试公式中C2单元格如要显示为计算结果，应在IF前加“=”，这里为了显示公式内容去掉了“=”，而在D2单元格输入了“=Evaluat（C2）”显示C2计算式的结果）。3）以上只有一个测试条件，但材料调差中判断采购价所处区间需要两个测试条件，及如果采购价小于跌幅下限调整价差，否则如果采购价大于涨幅上限调整价差，如果两个条件都不满足及不调整。所以就需

7、要嵌套两层“IF”公式。表格中跌幅范围和涨幅范围上文已经讲解（应用“MAX,MIN”），此时需在L列调整价单元格L7中键入：“=IF（H7K7,G7+H7-K7,G7）”,单元格L8至Ln通过下拉复制L7公式完成。价差=调整价-投标价，正数为调增，负数为调减。在进行材料调差时，只需输入材料名称，对应基准价、投标价、采购价及风险值，调整价及价差会自动计算。4）汇总材料价差，此时表格需要进一步完善，需在材料名称后或其他空白列插入材料消耗量（从计价软件中复制），相应的材料总价差=本材料消耗量*本材料价差。最后将所有材料总价差再次汇总得到最终材料调差。完善后的表格如下：四、实际工程材料价差调整某道路工

8、程，约定风险范围为5%和3%。工程结算审核时按实际采购价（采购价如何确认后续文章将作分析）按实调整，材料调差如下：五、结语Excel在早期广泛应用于工程造价。如今电脑软件技术不断提升，计量计价软件层出不穷，但或多或少存在不完善之处，Excle也就弥补这一点。本文讲解了运用Excel编制造价信息调整价差表。应用此表，造价人员只需要在调整价差前确定需调整的材料对应的消耗量、基准价、投标价、采购价，以及合同约定的风险值，将以上数据填入表格后，材料调差数据便自动计算。相比人为判断条件计算，Excel更加准确，工作效率也大幅提高。【参考文献】1住房和城乡建设部，国家质量监督检验检疫总局.建设工程工程量清

9、单计价规范（GB 50500-2021）.北京：中国计划出版社，2021.2Excel Home.Excel 2021应用大全.人民邮电出版,2021-7.3许斌成.建筑工程工程量清单计价全程解析.湖南大学出版社,2021-12.电子表格软件Excel1 认识 Excel1.1 Excel 简介Excel 是功能强大的电子表格软件。我们在日常学习和工作中经常会用到表格，这些表格通常是用来处理数据并且需要计算的。例如，在教学工作中经常需要制作学生成绩统计表，任课教师要统计学生的考勤、平时成绩、期末成绩，并以此计算学生的综合成绩，并进行成绩的排序、求平均分等；班主任需要汇总学生的各门成绩，得出学生

10、的总分和总成绩排名；教务部门要计算各个班学生成绩的排名等。财务部门需要制作职工的工资表，每个家庭会有家庭收支统计表。 在这些数据表中，不仅仅要输入数据，还经常要对数据进行一定的运算。如计算学生成绩的总分，要进行求和运算；要求学生成绩的平均分，要进行求平均数运算；要得出学生成绩的排名，要进行排序运算；要得出成绩的最高分和最低分，要进行最大值和最小值运算；将成绩按一定的条件进行选择，要进行筛选运算。在人工表格中，所有计算由人工完成，十分麻烦，稍有疏漏就可能出现错误。当发现错误需要改动时，例如有一名学生的成绩在录入时出现了错误，就要把与该学生相关的所有数据进行重新计算，如该学生的总分、平均分、排名，

11、还会对全班的排名造成影响，运算量十分庞大。而引入电子表格软件可以我们提高工作效率，电子表格软件的一个重要的特征就是数据处理自动化。它不需要人进行手工计算，通过公式、函数自动完成数据的运算。任何一个参加运算的数据发生了改变，其他与这个数据相关的数据都会自动的、实时的更新。Excel 作为应用广泛、功能强大的电子表格软件，它的功能包括： 数据采集。可以在 Excel 中录入数据，也可以将其他形式的数据导入到 Excel 中，如记事本、 Word 、 Access 数据库中的数据等。 数据编辑。可以根据需要对数据进行修改，与修改数据相关的数据都可以得到相应的更新。 数据运算。可以通过函数和工作来实现

12、数据运算的自动化。 数据图表化。 Excel 提供了十分强大的图表功能，如可以生成学习成绩的分布图，可以直观的表示出各个分数段的人数在总人数中的比例，图形可以以圆饼图、折线图等多种形式呈现。 数据分析处理。1.2 启动和关闭 ExcelExcel 是 Office 套件中的软件，因此在启动 Excel 之前需要安装 Office 软件。以 Office2003 为例，安装好 Office 软件后，可以选择“开始菜单” “程序” “ Microsoft Office 2003 ” ，就可以启动Excel 软件。如 图 5 - 1 所示。图 5 - 1 打开 Excel可以在桌面上为 Excel

13、创建一个快捷方式。在“ Microsoft Office 2003 ” 菜单上单击鼠标右键，在弹出的快捷菜单中选择“发送到” “桌面快捷方式”，如 图 5 - 2 所示。图 5 - 2 创建快捷方式这样在桌面上就会出现一个快捷方式 双击快捷方式可以打开 Excel 。如果已经有 Excel 文件，则双击 Excel 文件也可以打开 Excel 。关闭 Excel 的方法是单击程序右上角的关闭按钮，或者在 Excel 中选择菜单“文件”“退出”命令。1.3 Excel 界面Excel 主界面如 图 5 - 3 所示。和其他 Office 系列软件一样，由标题栏、菜单栏、快捷工具栏和工作区组成。

14、Excel 与众不同的有如下部分。图 5 - 3 Excel 界面公式编辑行。公式编辑行里有两个对话框，如 图 5 - 4 所示，左边的小对话框称为单元格地址对话框，显示的是当前活动单元格的地址，右边的大对话框用于输入与编辑单元格的内容或运算公式。图 5 - 4 公式编辑行工作簿。 Excel 软件把一个文档称为一个工作簿，英语是 Book 。一个工作簿最多可以由 255 个工作表（ Sheet ）组成。在默认状态下，每个工作簿里有 3 张工作表，分别用 Sheet1 、 Sheet2 、 Sheet3 来表示，如 图 5-5 所示。如有需要，工作表还可以再增加。当工作表的个数超过 4 个时，

15、工作表标签滚动按钮 才开始起作用。图 5 - 5 一个工作簿默认有 3 个工作表工作表。工作表实际上是一张巨大的二维表格，它由许许多多的单元格组成，如图 1-6 所示。在工作表里，需要掌握四个基本概念：单元格、单元格的名字、当前活动单元格、滚动条。图 5 - 6 一个工作表的局部 单元格。工作表中行和列交叉的地方叫做单元格。工作表上方的大写英文字母表示的是列坐标： A 、 B 、 C 、 D 、 .Z 、 AA 、 AB.IV ，共有 256 列；工作表左侧的数字表示的是行坐标： 1 、 2 、 3 65536 ，共有 65536 行。因此，一张工作表共有 16777216 （ 65536 2

16、56 ）个单元格。 单元格的名字。为了叙述和处理的方便，工作表中每一个单元格都有一个唯一确定的名字，给单元格命名的方法是：列坐标 + 行坐标（即先写字母后写数字）。例如，第一列第一行单元格的名字为 A1 。工作表最右下角单元格的名字为 IV65536 。 当前活动单元格。工作表中有一个可以自由移动的、加粗的方框 ，用鼠标或键盘上的、四个箭头键，可以将其移到工作表中任一单元格，这个活动方框所在的单元格，称为“当前活动单元格”。在“当前活动单元格”中，可以直接输入文字、数字和数学表达式。当前活动单元格的名称和所输入的内容都显示在工作表上方的公式编辑行内。2 数据录入工作簿的新建、打开、关闭与 Of

17、fice 其他软件的操作类似，不再详述。2.1 选定单元格选定单个单元格。将鼠标指针移到任意一个单元格上，单击鼠标左键，当该单元格被活动方框 框住的时候，表明该单元格已经被选中。如要选择的单元格距离当前单元格非常遥远，可以在公式编辑行里的单元格地址对话框内输入单元格地址。选定多个连续单元格。拖曳鼠标可以选取多个连续的单元格。或者用鼠标单击选择区域的左上角单元格，按住 Shift 键再用鼠标单击右下角单元格。选中某一行（列）可单击工作表相应的行（列）号；选择整个工作表可单击工作表左上角行列交叉的按钮；选择相邻的行（列）可用鼠标拖曳行号或列号。选取多个不连续单元格。选择一个区域，再按住 Ctrl

18、键不放，然后选择其他区域。在工作表中任意单击一个单元格即可清除单元区域的选取。2.2 输入数据当单元格被选定之后，就可以往该单元格输入数据。数据输入完毕可用鼠标单击公式编辑栏中的输入按钮 确认，或者按 Tab 键确认，还可以用鼠标或光标控制键、，将 移到其他单元格。当发现输入有误，可用鼠标单击公式编辑栏中的“取消”按钮 ，或者按 Esc 键取消输入。2.3 Excel 中的数据类型Excel 表格中的数据一般分为两大类：数值型数据和文本型数据。2.3.1 数值型数据数值型数据可以参加数学运算。数值型数据主要包括以下几类： 数字符号： 0 、 1 、 2 、 3 、 4 、 5 、 6 、 7

19、、 8 、 9 、 100 ； 数学公式：在 Excel 中，公式是以“”开始的； 数学函数：是系统已经帮助我们编写好的公式；如 sum 、 average ； 日期：输入日期时，用反斜线（ / ）或连字符（ - ）作为间隔符，如 2021-12-15 ； 时间：输入时间时，以冒号（ : ）作为时、分、秒的间隔符，例如， 12:35:21; 货币：如￥ 123.45 、 $56.78 ； 运算符号：如 + 、 - 、 * 、 / 、（、）、 $ 、 % 、，、 E 、 e 等。数值型数据一般采用右对齐格式显示方式。在单元格上单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，会弹出“单元格

20、格式”对话框。在这里可以不同数据类型进行进一步设置。如 图 5 - 7 所示可以对货币型数据进行设置。图 5 - 7 设置数据格式2.3.2 数值型数据的长度如输入的数值型数据的长度超过了单元格的显示宽度时， Excel 会自动将数据转化成科学计数法的形式显示；当单元格里的数据用科学计数法也不可能正常显示时， Excel 会自动在单元格里显示一串 # 号表示数据超长无法正常显示。如 图 5 - 8 所示。图 5 - 8 同一个数据在不同宽度的单元格显示不一样2.3.3 文本型数据文本型数据包括：汉字、英文大小写字母、数字及其他字符，即除了数值型数据之外的所有数据都是文本型数据。日常生活中有一类

21、数据，虽然表面看是数字，实际上是不参与数学计算的文本型数据。例如， 号码、汽车牌号、身份证号码等。对于显示为数字的文本型数据，输入时，需在这些数据前面加上西文单引号（ ），如 123 ，或采用 =” 123” 的方式将数字转化成文本型数据。在单元格中，所有文本型数据均默认采用左对齐格式显示。一个单元格最多允许输入 255 个字符，当文本型数据的字符长度超过了单元格的显示宽度时，如果右侧相邻的单元格中还没有数据，则 Excel 允许多出来的数据临时占用相邻的单元格，如 图 5 - 9 （ a ）所示；如右侧相邻的单元格中已有数据，则多出来的数据无法显示出来，如 图 5 - 9 （ b ）所示。（

22、a） 多出来的数据占用相邻单元格 （b） 右侧邻格有数据时超长部分被遮盖图 5 - 9 文本型数据字符长度超过单元格显示宽度的情况此类问题可以通过调整单元格的宽度或对字符进行换行来解决。在 Excel 中，对字符进行换行有两种方式，一是在输入过程中使用快捷键 Alt Enter 换行；二是选择菜单“格式”“单元格”命令，在弹出的对话框中选择“对齐”，将“文本控制”中的“自动换行”选中，如 图 5 - 10 所示。图 5 - 10 设置自动换行2.4 数据的显示格式在 Excel 表格中，数值型数据和文本型数据默认采用不同的显示方式，数值型数据一般采用右对齐格式，而文本型数据一般采用左对齐格式。

23、在某些情况下，为了美化表格或特殊的需要，可以改变数据的显示格式。常用的数据显示格式按钮有 9 个，它们的名称如 图 5 - 11 所示。需要使用哪种显示格式，只要先选中单元格，再单击相应的格式按钮，该单元格里的数据马上按选中的格式显示。图 5 - 11 显示格式按钮2.4.1 对齐方式按钮当要更改某个单元格里的数据对齐格式时，可以先选中要更改对齐格式的单元格区域，然后单击格式工具栏中的某一个“对齐”按钮，被选中的单元格区域里的数据就按新的对齐格式显示。2.4.2 合并及居中格式在制作表格时，除了要往表格里输入各种数据之外，经常需要用一个表头来描述表格的内容，但表头的文字一般比较多，超过了一个单

24、元格的宽度，此时可用“合并及居中格式”将几个单元格合并成一个单元格来显示标题。操作步骤如下：鼠标选中要设置格式的单元格区域；按住鼠标左键，拖动鼠标向水平方向移动，直到足够显示标题为止；单击“格式”工具栏的“合并及居中”按钮 ；被选中的若干个单元格被合并成一个单元格，同时，标题在合并后的单元格居中显示，如 图 5 - 12 所示。图 5 - 12 合并及居中单元格2.4.3 货币格式将数字用货币格式显示的方法如下，选中要采用货币方式显示的所有单元格，单击格式工具栏中的“货币样式”按钮 ，被选中的单元格区域里的数值型数据即按货币格式显示。如 图 5 - 13 所示。图 5 - 13 货币格式显示数

25、据2.4.4 百分比格式当表格中的某些数据需要显示为百分比的形式时就可使用“百分比样式”按钮 。选中需用百分比方式显示的单元格区域，鼠标单击“格式”工具栏中的“百分比样式”按钮 ，被选中的单元格区域内的数值型数据即以呈百分比格式显示，如 图 5 - 14 所示。图 5 - 14 百分比格式显示数据2.4.5 千位分隔格式在使用计算机处理各种数值问题时，往往会出现多位数的情况，例如，世界人口统计表、国土资源统计表、国民经济总产值统计表等，这一类数据的特点是位数比较长，如果用千位分隔格式，可以使数据显示更加清晰。操作步骤是选中要采用千位分隔格式显示的单元格区域，单击格式工具栏中的“千位分隔样式”按

26、钮 ，被选中的单元格区域内的数值型数据即按千位分隔格式显示，如 图 5 - 15 所示。图 5 - 15 千位分隔格式2.4.6 增加或减少小数位数在 Excel 表格里，如数值型数据需要以带有若干位小数的形式显示，可用“增加小数位数”按钮 快速实现，无须一个个单元格去调整。若要减少小数位数，可用“减少小数位数”按钮 来实现。具体步骤是选中需改变显示格式的单元格区域，单击“格式”工具栏中的“增加小数位数”按钮 （或“减少小数位数”按钮 ），每单击一次，被选中的单元格区域中所有的数值型数据就增加（或减少）一位小数位。2.5 数据的自动填充当往表格里输入数据时，往往会遇到输入一系列连续的数据的情况，例如，连续的编号、年度序号、月份序列、星期序列、日期序列、时间序列、连续的文字序列等。如用手工输入这些序列，显然很麻烦，但如果利用 Excel 软件提供的自动填充功能来完成这些输入工作，就会又快又省事。在当前活动单元格的右下角有一个黑色的