Excel函数及宏编程在提高施工结算效率中的应用

钱 哨

（交通运输部管理干部学院，北京 101601）

0 引言

Excel 作为历史悠久的数据计算表工具在我国土建工程领域运用较为广泛，既包括工程养护、工程量计算、工程造价、工程预算、工程复核等工程管理［1-10，16］，也包括工程桥梁、涵洞、边坡、隧洞等工程建设计量［11-15，17-19］，主要使用Excel 中的函数公式和宏编程、数据透视表等功能实现各类工程计算。现有文献虽然从土建工程专业角度实现了目标指向性计算，但从信息科学角度分析尚存在无数据标准思维与软件工程思维的突出问题。利用信息科学数据标准化导向，跨学科去思考土建工程的数据计算问题，是一种突破式的思维方式，对土建工程施工计量具有较好的借鉴作用［20］。

从本质上讲，信息化是解放管理者时间成本和机会成本［21］。因此，凡是不能够创造新的财富（社会价值和经济价值）的信息化建设都是无效的信息化作为［22］。纵观建筑施工企业，各类软件产品汗牛充栋，但基层业务人员为录入各类业务系统数据却疲于奔命，信息化软件系统不仅没有解决基层项目部业务人员的工作负担，反而成为繁忙工作中的累赘和无效的信息化行为［23］。基层项目部人员急切需要提高Excel 的数据处理技能，进而提高单位时间生产率。

在我国建筑施工领域，工程项目部下设的计量、结算、工程等部门使用Excel 软件记录各类工程业务数据，形成电子数据文件并进行工作流转，是土建工程项目内部管理的一项常态工作。但在实践中项目部人员仅将Excel 视为数据录入工具，并未引入数据标准化思维模式，通过数据标准化结合Excel 函数库与宏编程等技巧解决面临的问题，仅仅是为了录入数据和计算工程结果疲于奔命，导致一线项目部管理工作效率不高，直接影响了工程结算和计量进度，造成时间与人力资源浪费。

1 工程结算管理工作效率问题剖析

以某土建工程项目部机械租赁Excel 汇总结算清单为例分析工程结算管理效率低的主要原因，如图1 所示。

Fig.1 Style of temporary machinery leasing account table图1 临时机械租赁台帐表样式

1.1 数据录入分类

将业务人员需要录入的数据简单分为以下3 种类型：

（1）采集数据录入类。采集数据录入类一般是将现场发生的工程单据通过人工录入到Excel 表格中，需要人工简单的分析思考。图1 的日期录入和工作时间录入就属于采集数据录入类。

（2）简单重复录入类。简单重复录入类一般指经常发生、周期性重复出现，不需要人工分析或思考采用拖拉或者复制粘贴形式产生的数据。图1 中的机械队伍和申报人员即为简单重复录入类。

（3）计算统计类。计算统计类是Excel 所有操作中最为复杂的内容，涉及知识面广，不易掌握和操作。计算统计类包括科学计算、数量统计、条件分析、数据比较、数据筛选、趋势分析等等。Excel 对实际工作具有较大驱动力，但掌握的人群较少。图1 中的金额为计算统计类。

1.2 数据录入时间成本估算

设定每单元格录入基本时间为t 秒，表格总数为m 行n列。采集数据录入类为tp、简单重复录入类为ts、计算统计类为tc、校验统计时间为tj。增加权重参数后，可根据操作规律分析tp=t，ts=0.2t，tc=3t，tj=5t，则每行基准时间合计为tp+ts+tc+tj=9.2t。

从数据列角度分析，设定采集数据录入类有LP列、简单重复录入类有LS列、计算统计类有LC列，修订后的行列基准时间合计为：T′=tp*LP+ts*LS+tc*LC+tj=（LP+0.2LS+3LC+5）t，则最终T=（LP+0.2LS+3LC+5）t*m。代入本次场景参数，m=10 000，t=3s，LP=8，Ls=11，LC=4，则一张临时机械租赁台帐Excel 表格人工录入计量时间为825 000s，合计229h，折合工作日（8h 计）需29 天，在工期紧张情况下人员需超负荷加班。

1.3 时间成本结果分析

此项计量没有考虑多表之间的逻辑关系。大工程行列数据更为庞大，统计分析纬度及环境影响、沟通协调更为复杂。在工期紧张情况下，人员忙碌录入数据，极易产生数据偏差，最终导致结算计量数据不准确，需要反复进行排查，导致现场管理矛盾重重，严重降低了项目部整体绩效和管理效能。因此，亟需提高一线业务人员Excel 数据采录的工作效率，降低人员工作负担，解放生产效能，提高管理绩效。

2 Excel 工程结算优化解决方案

2.1 Excel 数据管理思维方式

信息化建设是把复杂事情简单化、简单事情流程化、流程事情定量化、定量事情信息化［20］，信息化最终结果一定是标准化和数据化［21］。Excel 表格设计就是标准化的业务逻辑再造过程，Excel 内的录入数据就是真实的业务数据，而Excel 的统计分析就是各类软件所诉求的最终管理结果。所有软件类企业建设软件产品之前，需要大量调研企业实际发生的报表和台帐，就是将已定型的业务流程、工作逻辑、实际数据、统计分析结果进行再造，只是表现形式和实现技术手段更为专业。因此，重视Excel 的数据操作，提升数据标准化思考技巧，是重视信息化工作的基石。

2.2 数据标准化

数据标准化过程如下：

（1）剥离可标准化的数据内容。标准化的信息指可以固定下来的不会随着时间变化而变化的常量性数据，如人员、单位、计量标准、工程标准等。需要认真分析Excel 报表中的数据类型，在临时机械租赁台帐中可被固定的标准化信息如图2 所示。

Fig.2 Establishing standardized information图2 建立标准化信息

（2）建表基本原则是禁止表中表，表格必须是二维表（唯一行列号可以确定唯一表中的数据）。禁止表中表原则是尽可能减少复杂表格中的逻辑关系，减少表格数据复杂因素，利于后期数据分析和统计工作。

（3）如果表之间存在关联性则采用多页关联形式。Ex⁃cel 单个文件可由若干分页（sheet）组成，工程中描述一个事务性的业务内容由若干个分页表共同组成，分页与分页之间存在一定的数据逻辑关系，如机械台账清单分页与机械数据汇总分页之间存在数据计算逻辑关系。

2.3 利用标准化信息创建Excel 下拉单，减轻常规数据录入量

通过Excel 公式中的名称管理器，将可以标准化的信息全部定义为数组形式，如图3 所示。

Fig.3 Defines a standardized information array图3 定义标准化信息数组

而后在数据选择中定义单元格为序列下拉列表，并在数据来源中指定名称管理器的具体数组，如图4 所示。

Fig.4 Create drop-down list operation图4 建立下拉列表操作

2.4 Excel 下拉单的数据联动和复杂计算

Excel 文件列之间可能存在逻辑关联，特别是下拉单之间可能存在集合逻辑关联，如定义机械类别后需要完成型号单位的数据筛选，即确定某个大类信息，同时实现与该大类有关的逻辑数据，在数据来源中通过INDIRECT 函数实现下拉菜单联动效果，如图5 所示。

Fig.5 Realizes the correlation pointing of drop-down menu through INDIRECT function图5 通过INDIRECT 函数实现下拉菜单关联指向

例如，在确定机械类别后型号单位实现自动筛选，进而通过标准化数据关联直接生成某类别型号单位下的机械租赁单价。这一联动逻辑处理不需要操作人员进行任何键盘敲击，实现数据录入工作自动化，确保了数据自动计算，如图6 所示。

Fig.6 Automatic calculation effect of multi-list linkage data图6 多列表联动数据自动计算效果

单价计算基本公式为：=INDEX（计价标准！$D$3：$D$1 000，MATCH（‘刘小峰（机械）’！L6，计价标准！$C$3：$C$1 000，0）），通过MATCH 函数的业务逻辑关系搜索计算和INDEX 函数的匹配后查询数据计算得出。

2.5 通过宏编程实现单次机械租赁费用自动计价

单次机械租赁费用=机械租赁标准单价×租赁时间，通过下拉列表求得某机械类别中的型号单位并自动得出单价后，需要计算某机械台班工作时间。机械台班工作时间需要确定日期和工作起始终止时间，通过二者之差得出机械台班工作时间。

传统录入方式通过手工方式录入，耗时耗力且数据计算结果不精准。有时为了省事，很多计量人员直接估算，造成隐形经济损失。改进后的日期填写为弹出日历直接选择，默认为当天，如图7 所示。

Fig.7 Automatic calculation of time by date control图7 日期控件实现时间自动计算

在开发工具中，通过插入时间控件的形式建立日期列某个单元格的日期时间控件，而后通过鼠标双击进入后台的VBA 编程界面，键入如下VBA 代码，就可以实现自动日期弹出，然后进行选择。

日期选择实现后，分别建立起始时间和终止时间下拉列表，通过选择形式确定开始和结束的小时和分钟，然后根据选择者的触发自动计算出某天的时间量差，从而得到租赁时间值，具体公式如下（其中B6 代表选择的日期，C6和D6 代表选择的起始小时和分钟，E6 和F6 代表选择的终止小时和分钟）：

通过计算分别获得机械租赁标准单价和租赁时间后，单次机械租赁费用就可通过乘的形式直接计算得出，上述过程都是自动计算得到，无需人工参与。鉴于本案例计算过程的繁杂性和篇幅限制，关于工程队临时用工计费、临时用工汇总、临时机械汇总、临时结算汇总台账、月度结算汇总内容在此不一一列举。

3 结语

实行改进的Excel 自动计量计算方式后，业务人员操作评估计算分析tp=t，ts=0.1t，tc=0t，tj=0t，实际工作行列基准时间合计为T′=tp×Lp+ts×Ls+tc×Lc+tj=（Lp+0.1Ls）t，则最终T=（Lp+0.1Ls）t×m。带入本次场景参数m=10 000，t=3s，Lp=3，Ls=16，Lc=4 中，则一张临时机械租赁台帐Excel 表格人工录入计量时间为138 000s，合计38h，折合工作日（8h 计）需4.7天，与原工作时间29 天量差为24.3 天，新方法可提高项目部工程结算计量6 倍。本文项值计算仅为单人工计差，如果扩展到整个工程项目则工作效率提高非常惊人。

本文Excel 函数与宏编程仅涉及到一部分案例内容，没有一成之规和可以定制化的模版，仅仅提供了改进思路和步骤，具体实操需要根据实际情况进行改进。如果按照本文思路持续改进，则工程结算软件化和产品化会迈出可喜的一大步。