2019年工程造价表_Excel Power BI在造价管理中的运用（成都市工程造价协会 2019年优秀工程造价学术论文 三等奖）...

成都市工程造价协会

2019年优秀工程造价学术论文

三等奖

Excel Power BI在造价管理中的运用

 赖家富

(中道明华建设工程项目咨询有限责任公司 ，

四川  成都  610000) 

      摘 要：随着建筑行业的蓬勃发展，造价专业在其中发挥的作用越来越举足轻重。而在招投标阶段如何快速、高效的收集、整理、分析各投标人的回标价格就成了确定合理投标人的重要一环。本章重点介绍了运用大家身边的Excel表格软件来实现本目的。

      关键词：Power BI、PowerQuery、PowerPivot、数据库、度量值、透视表、透视图、切片器

       1 Excel Power BI历史及由来

      Excel Power BI最早以插件的方式运用于MSOffice2010，MSOffice2013中，但从Office365、Office2016开始,完全集成到软件内,成为软件的重要组成部分。它是一种利用Excel进行数据收集、整理、分析的工具，可方便、迅速的动态连接到多个/多类型的数据源，在不影响原始数据的基础上进行计算、分析，最终实现数据的可视化。其核心成员有PowerQuery、PowerPivot、PowerView、PowerMap。本篇重点介绍前两者在造价中的应用，其中Power Query着重于数据引入、转换、集成和扩充；PowerPivot侧重于建立关联、度量值，进行跨表运算，最后加透视表的视觉效果创建报表/视图。Excel Power BI主菜单如下图：

       2 Excel Power BI在造价招投标中应用的优势

       2.1 灵活多样的分析展示，通过数据的不同排布，实现不同投标人、不同楼栋等之间的同专业、同系统、同分类等之间的单方对比、单位数量对比、占比对比等。

       2.2 形象、快速、高效，实现五个人干一天的活儿用一个小时干完的效果。

       2.3 实现多表查询合并、不同表间进行跨表分析计算,实现团队协作集中分析；

       2.4 数据源的动态性 原始数据可随时增减，如投标人的增减这、多次回标数据交叉对比，对比项目数量调整；

       2.5 切片器将多个透视表，透视图的数据报表/图的动态联动展示。

       2.6 数据源的多样性 可以是Excel、XML、cvs等文件及各类数据库、网页等；

       3 Power Query的运用

       通过Power Query查询读取原始数据，对数据进行合并、分拆、转置、分组、合并、计算等操作，为下一步数据分析及展示提供新的数据源。

       3.1 常用的获取Excel文件查询数据的方式

       (1)从本工作簿工作表中获取表数据：

       (2)从其他工作簿获取数据

       (3)从文件夹及子文件夹获取数据

       3.2 文件夹获取数据流程(将所有投标人的投标清单Excel文件放在一个文件夹中，也可以在文件夹中建立子文件夹借以表示回标单位/回标时间/第几次回标等信息，其他与查询无关在文件均不应放到查询文件夹或子文件夹中)

       至此已调入工作簿中的对象——工作表。

       关键点：在Power Query中一般很少要求直接写代码和公式(所有步骤会自动生成代码，降低了造价同事们的对编程理解难度)，但如需运用函数必须注意大小写，一般单词首字母大写，本处添加引入工作表的公式是固定的，即：Excel.Workbook([Content])

       3.3 工作表数据整理及操作

       在3.2的工作表调入基础上，筛选需要查询的工作表→展开工作表→筛选有用的数据行(如以单位列筛选)→删除不必要列→根据需要进行列拆分，进一步进行如提升标题行，重命名标题行，设定字段类型(数字型和日期型必须设置正确)。到了这一步数据整理完成(如下表)，可加载到工作表中作为表数据运用或数据模型中进行下一步分析工作。

       我们通过以上操作区显示的大约15步左右，就实现了数据提取和整理。而传统操作采用打开一个一个文件反复进行复制/粘贴/链接等操作，按行列对整数据……。本方法操作步骤规范、简洁，且有利于后期变化多样的数据分析；而传统方式重复，繁琐，增加原始数据收集整理出错概率，同时对后期数据处理灵活性差或操作困难。

       此外，根据需要我们还可以在此处计算列出参考基准值，如以参考单价为基数，以各投标单位的相同项目的算术平均单价或去掉最高最低值的加权平均单价等，作为后单价评价的依据。

       4 Power Pivot的运用

       运用载入Power Pivot模型的多个表，建立关联、度量值，从而实现多个表中的数据跨表分析及运算(注，本表内的运算可不建度量值，跨表运算一定是度量值)。由菜单数据→数据工具→管理数据模型，进入Power Pivot数据模型操作界面。

       4.1 表之间关系的建立

       在主页的关系示图中，对载入的表建立关联，通常是一对多，如下图：

       上左图中，表“1-2”与表“标段”以[标段章节]建立多对一的关系；

       上右图中，表“1-2”与表“面积”以[部位]建立多对一的关系；

       4.2 设计建立度量值

       如在表“1-2”中建立度量值[∑合价]，表“面积”中建立度量值[∑面积]，进而建立度量值[∑单方]＝[∑合价]/ [∑面积]，实现了跨表的运算。这个运算是适时、动态的，即在后面的透视图/透视表中会根据数据的归属、层级进行动态运算更新。

       又如，建立偏离度量值P＝[投标人单价]/[基准单价],将投标单位的单价与基准进行求比值，根据P值区间(如±10%内为合理+10%~+30%或－30%~－10%为单价偏高……),标示文字合理、偏高、严重偏高等或用↑、↓标识。

       以上所建立的每一个度量值也是透视分析的一个字段，且公式简洁单统一，可灵活迅速的完成分级，各级间的运算分析，实现分类(如电缆、管道等)，系统(如不同投标人的给水、排水等)，同建筑类型(如相似办公楼间、住宅间等)的对比等。达到快速、灵活的效果。

       5 建立透视表/图，多样化、可视化展示

       在Power Pivot数据模型操作界面主页中，选中要进行可视化的主表，点击创建透视表/图，回到Excel主界面，进行透视表操作。

       利用切片器建立多个透视表/图之间的动态关联，通过透视表/图实现数据分析的动态化、可视化，使分析展示生动精彩。

       5.1 不同投标人的综合单价对比，如下表：

       在上表中，通过区域、专业切片器的选定，对多个投标人的综合单价进行对比分析。

       5.2 不同投标人的单方造价对比(左)，造价含量，如下表：

       在上表中，通过区域、专业切片器的选定，对多个投标人的单方造价进行对比分析(左)，对不同区域、强电专业的系统费用占比进行对比(右)。

       5.3 不同投标人的系统造价占比对比，如下表：

       在上表中，通过区域、专业切片器的选定及投标人的筛选，展示了投标人B(左)、投标人F(右)的1#公寓给排水各系统间的造价占比。

       6 结语

       本篇通过运用身边常用的Excel软件中的PowerQuery、PowerPivot，并结合透视表/图，对回标数据进行获取，整理，分析。展示了Power BI在招投标回标数据分析中的优势——快速、高效、动态，可视化等特性，造价人员在不需学习复杂的数据库软件及编程知识，在回标后时间紧张的情形下，精准、高效的处理回标数据，并快速做出分析，为决策定标提供有力参考。此外，还为收集数据，建立造价信息数据库打下了基础。

       参考文献：

       [1]Power Query M Functions By 张文洲

       [2]Power Query 公式语言规范(2015 年8 月) 翻译：FanXiaolei

       [3]PowerPivot for Excel 帮助文档

     (来源：http://technet.microsoft.com/zh-cn/library/ff684087.aspx)