电子表格Excel在工程造价中的应用之材料调差正式版.docx

1、电子表格Excel在工程造价中的应用之材料调差正式版电子表格Excel在工程造价中的应用之材料调差By:DP【摘要】随着一系列三维建模软件及计价软件的发展，造价从业人员工作效率也随之提高。但在实际工作中，有些细节计量计价软件无法处理。这就要回到传统的计量计价软件上，最常用的就是应Excel软件。Excel在工程造价方面的应用很广，如编制定额单位估价表，工程量清单报价，工程量零星计算等。本文着重介绍Excel应用之材料调差。【关键词】：造价信息、材料调差、风险幅度、数轴、MAX、MIN、IF一、引言在工程实践中，建设工程材料价差调整通常有几种方法，以下两种是比较典型的造价信息调整差额及价格指数调

2、整法：1价格指数调整法：它是按照当地造价管理部门公布的当期建筑材料价格或价差指数逐一调整工程材料价差的方法。这种方法属于抽量补差，计算量大且复杂， 常需造价管理部门付出较多的人力和时间。具体做法是先测算当地各种建材的预 算价格和市场价格，然后进行综合整理定期公布各种建材的价格指数和价差指数3 。 计算公式为：某种材料的价格指数=该种材料当期预算价该种材料基期的取定价 某种材料的价差指数=该种材料的价格指数1 本方法适合工程材料少结构不复杂的工程，如道路桥梁等，其优点是能及时反映建材价格的变化，准确性好，适应建筑工程动态管理。2. 造价信息调整差额： 本方法按照2021建设工程工程量清单计价规范

3、，材料、工程设备价格变化按照发包人提供的本规范附录L2的表21，由发承包双方约定的风险范围按下列规定调整合同价款1：1）承包人投标报价中材料单价低于基准单价：施工期间材料单价涨幅以基准单价为基础超过合同约定的风险幅度值，或材料单价跌幅以投标报价为基础超过合同约定的风险幅度值时，其超过部分按实调整。2）承包人投标报价中材料单价高于基准单价：施工期间材料单价跌幅以基准单价为基础超过合同约定的风险幅度值，或材料单价涨幅以投标报价为基础超过合同约定的风险幅度值时，其超过部分按实调整。3）承包人投标报价中材料单价等于基准单价：施工期间材料单价涨、跌幅以基准单价为基础超过合同约定的风险幅度值时，其超过部分

4、按实调整。4）承包人应在采购材料前将采购数量和新的材料单价报送发包人核对，确认用于本合同工程时，发包人应确认采购材料的数量和单价。发包人在收到承包人报送的确认资料后3个工作日不予答复的视为已经认可，作为调整合同价款的依据。如果承包人未报经发包人核对即自行采购材料，再报发包人确认调整合同价款的，如发包人不同意，则不作调整。 本文将介绍相比较为常用的造价信息调整差额，并应用Excel技术使之更为便捷。二、从案例解析造价信息调整差额原理本案例包含造价信息调整差额中的三种情况，这三种情况的调整方法将为后面编制EXCEL表格做准备。（一）例：某工程商品混凝土由承包人提供，采购的混凝土单价为：C20:32

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

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

7、B表示采购价格与跌幅下限和涨幅上限的差的绝对值1）当采购价格处于下跌区间：调整价格=投标报价-A2）当采购价格处于不调区间：调整价格=投标报价3）当采购价格处于涨幅区间：调整价格=投标报价+B（三）用数轴验证案例：承包人提供主要材料和工程设备一览表可按照跌幅下限和涨幅上限调整为：序号型号风险系数基价投标价跌幅下限涨幅上限发包人确认价备注1C205%310307291.65325.5309.52C255%325328308.75344.4326.253C305%340340323357340 表2步骤：1）将3种型号的混凝土基价和投标价分别放入数轴1-3中，按照跌幅涨幅风险系数算出跌幅下限和涨幅

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

9、下：（一）前文已经讲到要预先设定3个区间，这就需要确定跌幅下限和涨幅上限， 跌幅下限=A*(1-风险值%） 涨幅上限=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中插入一行采购价，

10、如下：2）应用Excel的“IF”公式，格式=IF(logical_test,value_if_true,value_if_false),及测试一个单元格的条件，如果条件成立返回一个值（可以是计算公式），如果条件不成立返回一个值（可以是计算公式）2。测试如下：B2=80,A2=100。因测试条件80100，条件不成立，返回公式B2/2，及计算结果55。（备注：以上测试公式中C2单元格如要显示为计算结果，应在IF前加“=”，这里为了显示公式内容去掉了“=”，而在D2单元格输入了“=Evaluat（C2）”显示C2计算式的结果）。3）以上只有一个测试条件，但材料调差中判断采购价所处区间需要两个测试

11、条件，及如果采购价小于跌幅下限调整价差，否则如果采购价大于涨幅上限调整价差，如果两个条件都不满足及不调整。所以就需要嵌套两层“IF”公式。测试如下：表格中跌幅范围和涨幅范围上文已经讲解（应用“MAX,MIN”），此时需在L列调整价单元格L7中键入：“=IF(H7K7,G7+H7-K7,G7)”,单元格L8至Ln通过下拉复制L7公式完成。价差=调整价-投标价，正数为调增，负数为调减。在进行材料调差时，只需输入材料名称，对应基准价、投标价、采购价及风险值，调整价及价差会自动计算。4）汇总材料价差，此时表格需要进一步完善，需在材料名称后或其他空白列插入材料消耗量（从计价软件中复制），相应的材料总价差

12、=本材料消耗量*本材料价差。最后将所有材料总价差再次汇总得到最终材料调差。完善后的表格如下：四、实际工程材料价差调整某道路工程，约定风险范围为5%和3%。工程结算审核时按实际采购价（采购价如何确认后续文章将作分析）按实调整，材料调差如下：五、结语Excel在早期广泛应用于工程造价。如今电脑软件技术不断提升，计量计价软件层出不穷，但或多或少存在不完善之处，Excle也就弥补这一点。本文讲解了运用Excel编制造价信息调整价差表。应用此表，造价人员只需要在调整价差前确定需调整的材料对应的消耗量、基准价、投标价、采购价，以及合同约定的风险值，将以上数据填入表格后，材料调差数据便自动计算。相比人为判断

13、条件计算，Excel更加准确，工作效率也大幅提高。【参考文献】1住房和城乡建设部，国家质量监督检验检疫总局.建设工程工程量清单计价规范（GB 50500-2021）.北京：中国计划出版社，2021.2Excel Home.Excel 2021应用大全.人民邮电出版,2021-7.3许斌成.建筑工程工程量清单计价全程解析.湖南大学出版社,2021-12.Excel电子表格试题一、单项选择题1.在保存新建的Excel2021工作簿的操作过程中，默认的工作簿文件名是（）。A.工作簿1 B.工作表1 C.文档1 D.电子表格12.Excel启动后，系统默认打开的工作表数目是（）个。A.2 B.3 C.

14、4 D.53.如果当前工作簿有4个工作表，此时再插入一个工作表，其默认的工作表名为（）。A.Sheet4B.Sheet(4) C.Sheet5D.Sheet(5)4.对工作表进行添加或删除、复制、重命名等操作后（）。A.无法撤消 B.可以撤消C.删除不能撤消，其它可以撤消 D.重命名可以撤消，其它不能撤消5.6.工作表是由（）行构成。A.1048576 B.16384 C.65535 D.2567.一张工作表是由（）列构成。A.1048576 B.16384 C.65535 D.2568.第3行第4列的单元格名字是（）。A.C4 B.C3 C.E3 D.E49.10.在Excel中，输入系统当

15、前日期的快捷键是（）。A.Ctrl+;B.Ctrl+Shif+;C.Shift+;D.Shift+Alt+;11.输入系统当前时间的快捷键是（）。A.Ctrl+;B.Ctrl+Shif+;C.Shift+;D.Shift+Alt+;12.在Excel2021中，活动工作表有（）个。A.1 B.2 C.3 D.413.在一个工作表中，活动单元格有（）个。A.1 B.2 C.3 D.414.在Excel2021中，工作表（）。A.可以增加或删除 B.不可以增加或删除C.只能增加 D.只能删除15.在Excel2021中，默认情况下，文本型数据（）。A.左对齐 B.居中 C.右对齐 D.两端对齐16

16、.Excel2021在默认情况下，数字型数据（）。A.左对齐 B.居中 C.右对齐 D.两端对齐17.在某个单元格中输入“2/4”，按回车后显示（）。A.1/2 B.2/4 C.0.5 D.4月4日18.在某个单元格中输入“（8）”，按回车后显示（）。A.8 B.(8) C.8 D.19.要在选定的多个单元格中输入相同的数据，在输入第一个数据后按键（）。A.EnterB.Ctrl+EnterC.Shift+EnterD.Alt+Enter20.如果某个单元格显示为若干个“#”号(如#)，表示（）。A.公式错误 B.数据错误 C.行高不够 D.列宽不够21.Excel2021保留（）位数的数字精

17、度，如果数字超过这个数，将多余数字舍入为0。A.10 B.15 C.18 D.2022.要选中工作表中所有单元格，可以单击“全选”按钮，也可用快捷键（）。A.Ctrl+AB.Ctrl+DC.Ctrl+BD.Ctrl+G23.24.单元格区域D4:F8包含（）个单元格。A.15 B.12 C.10 D.825.在Excel2021中，单元格区域A1:C3,D3:E3包含（）个单元格。A.13 B.12 C.11 D.926.当前单元格的值是0.05567，使用“百分数”按钮格式化后显示为（）。A.5% B.6% C.5.56% D.5.57%27.当前单元格的值是5.567，再连续按两下“减少小

18、数位数”28.29.30.用拖动的方法移动单元格的数据时，应拖动单元格的（）。A.填充柄 B.中心 C.边框 D.右上角31.自动填充数据时，应拖动单元格的（）。A.填充柄 B.中心 C.边框 D.右上角32.如果要全屏显示工作簿，可以执行（）选项卡中的“全屏显示”命令。A.文件 B.数据 C.页面布局 D.视图33.要创建自定义序列，应单击（）选项卡中的“选项”命令，然后选择“高级”，找“编辑自定义列表”按钮。A.文件 B.数据 C.页面布局 D.视图34.要打开“设置单元格格式”对话框，可用快捷键（）。A.Ctrl+1 B.Ctrl+2 C.Ctrl+3 D.Ctrl+435.选中单元格后

19、按删除键（Delete）或退格键（Backspace），相当于选择（）命令。A.清除格式 B.清除内容 C.删除数据 D.清除数据36.快速选定连续单元格的快捷键（）。A.Ctrl+方向键B.Shift+方向键C.Alt+方向键D.Ctrl+Shift+方向键37.快速自动调整列宽的方法为用鼠标双击列标的（）。A.右侧边界 B.左侧边界 C.上侧边界 D.下侧边界38.在Excel中输入的数据如果有某种内在规律，可以利用（）功能。A.复制 B.格式 C.格式刷 D.自动填充39.快速复制数据格式时，可以使用（）工具。A.复制 B.格式 C.格式刷 D.自动填充40.在Excel中执行一次排序命

20、令，最多可以按（）个字段来排序。A.32 B.64 C.128 D.25641.在Excel中,公式以（）开头。A.- B.! C.= D.#42.将多个单元格区域联合起来的运算符是为（）。A.逗号（，）B.句号（。）C.星号（*） D.加号（+）43.单元格区域A1:B2,B5:D8包含（）个单元格。A.14B.16C.18D.2044.若要在工作表的第4行上方插入三行，则应选定（）。A.4、5、6三行 B.5、6、7三行 C.第4行D.第5行45.函数名、单元格地址是区分大小写的。（）A.正确 B.错误 C.不确定D.不一定46.47.为了在查看工作表时能了解某些重要单元格的含义，可以为其

21、添加（）。A.批注 B.备注 C.注释 D.审阅48.用鼠标拖动（）可以改变列宽。A.列标 B.列标的边界线 C.行号 D.行号的边界线49.双击（）可以自动调整行高。A.列标 B.列标的边界线 C.行号 D.行号的边界线50.要在D列右侧插入两列，则应先选定（）。A.B、C两列B.C、D两列 C.D、E两列D.E、F两列51.用来标识单元格地址为绝对地址的符号为（）。A.$ B.# C.！ D.&52.当公式移动或复制到一个新位置时，公式中的绝对地址（）。A.自动变化 B.不会变化 C.可能变化 D.部分变化53.可以使用（）键进行切换单元格的相对地址、绝对地址。A.F1 B.F2 C.F3

22、 D.F454.在引用单元格区域时，（）的作用是确定单元格区域。A.逗号 B.分号 C.冒号 D.空格55.要想冻结工作表中的前3行数据，应当选定（），然后再冻结拆分窗格。A.第3行 B.第4行 C.前3行 D.前4行56.公式中使用了不能识别的文本，比如函数名输入错误等，单元格将会出现（）。A.#NAME?B.#DIV/0! C.#N/AD.#Value57.创建独立图表的快捷键是（）。A.F4 B.F5 C.F11 D.F1258.在Excel中创建嵌入式图表的快捷键是（）。A.F1 B.Alt+F1 C.F11 D.Alt+F1159.当工作表中的数据发生变化时，图表中对应的数据（）。A

23、.自动变化 B.不会变化 C.可能变化 D.部分变化60.如果单元格出现错误值“#DIV/0!”，可能的原因是（）。A.公式被0除 B.公式中使用了不能识别的文本C.公式中没有可用数值 D.单元格引用无效61.出现#N/A的原因是（）。A.公式被0除 B.公式中使用了不能识别的文本C.公式中没有可用数值 D.单元格引用无效62.双击有公式的单元格或者先选择有公式的单元格再按F2键，则单元格显示（）。A.格式 B.批注 C.数值 D.公式63.删除工作表的列后，则该列（）。A.列标消失 B.内容清空 C.被右侧列填充 D.被左侧列填充64.为了防止工作表被移动、删除、添加等操作，可以使用（）。A

24、.保护工作表命令B.保护工作簿命令C.保护工作区命令D.保护文档命令65.在单元格中求4的平方，应输入（）A.42 B.=42 C.42 D.=4266.在单元格中输入分数“1/3”应输入（）。A.0空格1/3 B.空格1/3 C.1/3 D.“1/3”67.如果在单元格中输入“20211120”，Excel默认将它识别为（）数据。A.日期型 B.文本型 C.数值型 D.科学计数型68.要想一次性把所有成绩小于60分的数据设置为绿色填充，应使用（）命令。A.格式 B.条件格式 C.格式刷 D.填充69.建立图表的命令在（）选项卡。A.开始 B.插入 C.数据 D.视图70.冻结窗格的命令在（）

25、选项卡。A.开始 B.插入 C.数据 D.视图71.在对数据进行排序时，如果数据中包含有空格，则（）。A.无论升序还是降序，空白单元格总是放在最前B.无论升序还是降序，空白单元格总是放在最后C.升序时数据空白单元格放在最前，降序时白单元格总是放在最后D.降序时数据空白单元格放在最前，升序时白单元格总是放在最后72.使用排序按钮对某列数据快速排序，应先（）。A.单击工作表标签 B.选取整人工作表数据C.单击该列中的任一单元格 D.选中当前整列73.使用公式进行自动填充时，应在公式中使用单元格的（）。A.地址 B.数据 C.格式 D.批注74.具有二维表特性的电子表格在Excel中被称为（）。A.

26、数据库 B.数据清单 C.数据表 D.数据表格75.数据清单的第一行必须为（）数据。A.数值型 B.日期型 C.文本型 D.逻辑型76.对数据表做分类汇总前必须先（）。A.任意排序 B.进行筛选排序 C.更改数据格式 D.对分类字段进行排序77.如果只希望显示数据清单“成绩表”中成绩大于90分的记录，可以使用（）命令。A.筛选 B.条件格式 C.数据有效性 D.排序78.单元格中的数据类型（）。A.只能包含数字 B.可以是数字、文字、公式等C.只能包含文本 D.只能包含公式79.在Excel中，用来存储并处理工作表数据的文件称为（）。A.工作表 B.工作区 C.工作簿 D.单元格80.排序、筛

27、选、分类汇总等操作的对象都必须是（）。A.任意工作表 B.工作表任意区域C.含合并单元格的区域 D.数据清单81.如果要在单元格中输入文本型的学号“20210128101”，输入方法为（）。A.先输入一个单引号，再输入“20210128101”B.先输入一个双引号，再输入“20210128101”C.直接输入“20210128101”D.在编辑栏中输入“20210128101”82.要在单元格中输入日期型数据“2021年12月20日”，应输入（）。A.20211220 B.2021/12/20 C.20211220 D.“20211220”83.在Excel中，清除和删除（）。A.完全一样B.

28、不一样，数据清除将单元格及其内容一并删除。C.不一样，数据清除的对象是数据，单元格不受影响。D.不一样，数据删除对单元的内容删除，单元格不受影响。84.在Excel中，当公式以零作分母时，将在单元格中显示（）。A.#NAME?B.#N/A C.#DIV/0!D.#Value85.若在单元格中输入公式“=$D$5/$D$10”,则公式中的单元格地址属于（）。A.相对地址 B.交叉地址 C.混合地址 D.绝对地址86.在Excel中，对数据进行多重排序（）。A.主要关键字或次要关键字可以独立选定递增或递减B.主要关键字或次要关键字都必须同为递增或递减C.主要关键字和次要关键字都必须递增D.主要关键

29、字和次要关键字都必须递减87.如果工作簿中既包含工作表又包含图表，则执“保存”文件时（）。A.只保存工作表 B.将工作表和图表作为一个文件保存C.只保存图表 D.将工作表和图表分成两个文件保存88.89.某个单元格的值是0.31415，先使用“百分数”按钮来格式化，然后连续按下两次“增加小数位数”按钮，这时所显示的内容为（）A.3.14% B.31.00% C.31.41% D.31.42%90.如果要对当前的工作表重命名，下列说法错误的是（）。A.单击工作表标签后输入新的工作表名B.双击工作表标签后输入新的工作表名C.右击工作表标签后单击“重命名”，然后输入新的工作表名D.单击“开始”选项卡

30、“单元格”组的“格式”下拉列表中的“重命名工作表”命令91.如要在单元格中输入“=21”，则单元格显示的值是（）A.TURE B.FALSE C.=21 D.2192.在单元格中输入内容后，都必须确认后才认可。下列确认方法不正确的是（）A.按回车键 B.双击该单元格 C.单击另一单元格D.按光标移动键93.在Excel中，下列关于图表说法错误的是（）。A.可以改变位置 B.可以调整大小C.可以改变类型 D.可打印，但必须和相关工作表一起打印94.公式或函数对单元格的引用不包括（）。A.相对引用 B.绝对引用 C.交叉引用 D.混合引用95.可用来保护工作簿的快捷键为（）。A.Ctrl+SB.Ctrl+BC.Ctrl+GD.Ctrl+T96.清除一行内容的有多各方法，下列方法不正确的是（）。A.选中该行按Del(删除键)B.选中该行按Backspace(退格键)C.右击行号然后选择“清除内容”D.选中该行后使用“开始”选项卡“编辑”组中的“全部清除”命令。97.在工作表中，A1单元格的内空是1，如果要在区域A1:A5中生成序列1，3，5，7，9，则下列操作正确的是（）。A.选中A1单元格后，按Ctrl键拖拽填充柄至A5B.在A2中输入3，选中A1单元格后，按Ctrl键拖拽填充柄至A5C.在A2中输