一个通用的EXCEL工程量计算表

cbtaja · 发表于 2013-10-17 23:57

本帖最后由 cbtaja 于 2013-10-18 11:25 编辑

EXCEL工程量计算表是EXCEL在工程行业的典型应用。
一、EXCEL工程量计算表的两项基本要求：
1、列示工程量清单，及其详细计算式，自动按式计算。
其中有一个工程行业的特别要求：在得出计算结果的同时还能显示计算式及文字注释，计算结果与计算式具有联动性，即更改计算式后，计算结果自动更新（或手动批量更新）。
2、对工程量进行自动分类汇总
二、EXCEL工程量计算表的扩展要求：
EXCEL工程量计算表做为给广大工程技术和经济工作者减轻工作负荷、提高工作效率的辅助工具，要求以人为本，在保证准确的前提下要尽可能方便操作、降低劳动强度。
纵观本论坛从2002年至今十余年的大量关于工程量计算表的讨论，主要集中在以下几点：
1、录入时能减少打字工作量，能够把输入时省略或简写的内容自动补齐；
2、录入时能够在适当的位置自动换行，避免干扰录入节奏、导致录入中断；
3、对于录入错误（特指计算式的录入），能够立即自动发现并以某种方式明示纠正，保障录入的正确性，但不干扰或中断输入。
4、要求计算式中支持使用中文变量，以便能展示复杂内容的计算过程、避免出错、以及方便分段核对。
5、表格排版要求：布局整齐合理、便于打印、节约纸张，计算式较长时自动换行并扩展行高、使之全部显示出来。
6、表格结构最好能与常用预算软件表格结构格式相符，便于导入到其它软件中。
7、扩展与小型、局部定额数据库连接功能，使专业而零星的工程项目的工程量计算时，无需使用庞大、昂贵的预算软件。
8、录入时的自动完成功能、多级联动下拉菜单功能（即增加在常用词语库的支持）。
……………

工程量计算表.rar (167.56 KB, 下载次数: 8416)
三、基于以上认识，以及本人的工作实践，经长时间的应用和多次改进，推出通用《工程量计算表》计算表0.9版。上面所提到的各项功能，除第7、8两项外，其它均已基本实现。希望各位多提意见，以便以后改进。

补充内容 (2016-4-3 22:47):
2016－4－23日更新：见16楼

更新内容：①、增加按拼音首字母自动逐步提示功能，②、添加.xlsm格式的模板，原.xlt格式模板保留并也作了更新。

补充内容 (2017-1-17 03:02):
最新附件在25楼：
http://club.excelhome.net/forum. ... 030&pid=8966017

补充内容 (2017-3-27 21:39):
最近更新时间：2017-3-27
最新附件在54楼：
http://club.excelhome.net/forum. ... 030&pid=9041227

skyzxh · 发表于 2013-10-18 09:32

缺少setup.cmd文件，无法安装

cbtaja · 发表于 2013-10-18 09:48

本帖最后由 cbtaja 于 2013-10-18 11:29 编辑

缺少setup.cmd文件，无法安装

{:soso_e181:}谢谢指出，已经在一楼更新附件。

cbtaja · 发表于 2013-10-18 14:59

本帖最后由 cbtaja 于 2013-10-18 15:12 编辑

作一个简单介绍：
1、使用条件：
   ①适应EXCEL97及以上版本；
   ②、需启用宏；
   ③如果程序提示“对象未定义”，请用EXCEL直接编辑“工程量计算表.xlt”（不是用它新建文档），然后在VBE中菜单“工具”－“引用”中添加对“字典”控件Microsoft Scripting Runtime和“正则表达式”控件Microsoft VBScript Regular Expressions 5.5或1.0的引用，然后保存 “工程量计算表.xlt”。

2、基本功能：
   ①、在“计算式”列单元格中输入公式（公式可带注释，但注释内容必须包含在成对的中括号“[]”中，且“[]”不允许嵌套），在对应的“数量”一栏中自动得出结果，如果无法计算出数值，则结果显示为去除了注释内容的公式，以便于检查、纠正。
   ②、自动汇总功能，通过使用VBA操作数据透视表实现。
   原拟通过“字典”+“数组”实现，但考虑到该方式不如数据透视表灵活，而且不能脱离“宏”的支持，否则无法更新结果的情况，所以最终采用了现在这种方案。这样，当另存为不包含宏的工作薄（.xlsx）时，仍然能刷新透视表而更新汇总结果，这样便于以电子文档形式向他人展示工程计算书的内容，特别是在预决算审核需要提交计算书时，可节约打印的各项费用，不仅快捷方便、经济节约，而且低炭环保。

3、扩展功能：
   ①、实现如同WORD中一样的敲回车键后“自动换行至下一行首”的功能。
      在录入工程量清单时，我们通常希望用一个常用键“Enter”实现以下功能：在需要的位置时，智能化地执行“右移一格”、或“跳到下一行首”、或“下移一格”。而在EXCEL中敲回车键结束一个单元格的编辑后，默认方式为跳到此前连续按Tab键的开始单元格的下方一格中。这种方式勉强相当于WORD中敲回车键“自动换行至下一行首”的效果，但是前提条件是：此前必须要以按Tab键（或Shift+Tab键）进行水平跳格。这是模拟打字机键盘的操作功能，因此，对于没有接受过专业打字训练的工程行业人员来说，实在是太不方便了。
   本模板使用VBA中的Onkey自动宏功能，修改打回车键后的行为，可以实现在指定的列打回车时，自动换行至下一行首。

   ②、可使用自定义的“简记代用符”。
   在手工编制工程量计算书的年代，我们为了减少书写工作量，通常会用一些简记符（比如：“々々”）来代表本格与上一格两者的内容相同。现在虽然使用电子表格了，我们仍然有类似需要求。而本模板可以实现将自定义的“简记符”的快速插入、并在表格计算汇总前批量自动替换。

   ③、计算式中使用“自定义变量”的功能
   如一楼所述，在计算式中使用“自定义变量”的功能，是很多在做工程量计算和汇总工作的童鞋们的殷切盼望，甚至是“业余工程量计算表”与“专业表格算量软件”的区分标志。我很高兴本模板已经实现了此种功能：童鞋们，有福了！{:soso_e112:}
   注意事项：本版中此项“自定义变量”功能的支持，暂需以手动执行“批量计算”程序来实现，下一步将改进为实时得到结果。另，“自定义变量”功能目前只支持“前向引用”，即计算式中所引用的变量必须先于本式计算出数值结果；如果计算式中有“后向引用”的变量，需要手工双击包含该计算式的单元格来重算该式。下一步将改进以完善此功能。

4、使用EXCEL电子表格内置功能做好的预先设置。
   ①、预置按列进行三级分组
      分组功能是EXCEL的内置功能，很多童鞋可能很少用到分组功能，这很可惜，因为它可使我们集中关注所需要的内容，特别是在进行工程量清单录入时，对于不需要人工填写的“序号”（代号）栏、或者可以用批量填充的方式在最后填写的“单位”  栏等，可以一击鼠标就隐藏或显示，减少跳过这些栏的按键动作，因而是一个非常方便实用的功能。

   ②、表格格式和页面设置已经预置好
   页面设置虽然简单，然而如果在每次新建表格时都需手动操作一遍，那也是很烦人的。本模板预置的页面设置能满足绝大多数童鞋的需求，省了诸多麻烦。同样，表格中的格式设置也是如此，特别是“计算式”一栏，当式子较长时需要能自动换行并扩展到合适的行高，本模板已经预设好了，无需每次再行设置。当然，这就是模板带来的好处，在此，我们要特别感谢麦可罗索夫特公司。

   ③、模板中包含一个以厘米为度量单位来精确设置表格列宽的代码。此前在我在百度空间里发表过一个同样功能的代码，见http://hi.baidu.com/cbtaja/item/74a60447ba3a12dac0a5922a，而现在这个已经做了重大改进，但由于本人准备对百度空间搬家，因此不在上面发表更新了。该空间也还有一些自编的其它自定义函数，有兴趣的坛友可以去看一看。

5、本模板的其它特色
   本模板代码开源，未使用“用户窗体”的纯文本代码，且所有代码都集中存放在模块1中，便于使用者自行扩展。

6、关于本模板升级
   本模板将不定期升级。如果大家有好的建议或者需求，请在下面跟贴。如对本程序有改进的，欢迎跟贴发布，在此谢谢共享。
   暂预备做2项改进：第1项：完善在上文第3、③条所述的引用自定义变量计算的功能；第2项：增加数据导出功能，导出为符合专业预算软件要求的数据交换格式的文件，对此，如各位读者手中有需要将本模板配合已有预算软件的，请提供相应的数据交换格式文件样板，以便添加此项功能。
   另有1项功能预想：增加公式库，以便快速插入规范的公式，公式库中允许用户自行添加公式及简要说明。在土建类工程量计算时，这项功能将很好用：对于某些特定类型的结构中包含的工程量计算时，计算式通常非常复杂，以往需要在工作表中使用辅助区域做计算，而预置公式库之后，只需选择相应公式并填写好相应的参数即可快速将计算式插入到表格中。

cbtaja · 发表于 2013-10-18 16:21

本帖最后由 cbtaja 于 2013-10-20 14:29 编辑

关于以厘米为单位来设置列宽的问题，此前的代码中先把厘米转换为英寸，然后把英寸按固定换算关系转换磅（为1英寸=72磅），相对比较麻烦，现直接用Application.CentimetersToPoints 方法()方法来获得。因此，一楼中的代码宜更新为如下：

'-------------------
Private Sub 恢复列宽()
'本代码调用Std_Add()、cmColumnWidth()函数，
'为第1至9列分别设置以厘米为度量单位的列宽
Dim std_fjz As Variant, i As Long, a As Boolean
Dim arr
std_fjz = Std_Add
Application.ScreenUpdating = False
arr = Split(",1.5,1.8,2.6,2.8,2.54,0.69,1.8,7.01,1.6", ",")
For i = 1 To UBound(arr)
a = lmlk(Cells(37, i), arr(i), std_fjz(0), std_fjz(1))
Next
End Sub
'-------------------
Private Function Std_Add()
Dim a1 As Double, a2 As Double, std As Double, fjz As Double, Orgnl As Double
Application.ScreenUpdating = False
With ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
Orgnl = .ColumnWidth
.ColumnWidth = 2: a1 = .Width
.ColumnWidth = 3: a2 = .Width
.ColumnWidth = Orgnl
std = a2 - a1 '1个标准字符宽度的磅值
fjz = a1 - 2 * std '每个单元格附加宽度的磅值
Std_Add = Array(std, fjz)
End With
End Function
'--------------------
Private Function cmColumnWidth(ByRef rng As Range, ByVal lmz_Width As Double, _
ByVal std As Double, ByVal fjz As Double) As Boolean
'共4个参数，参数1为单元格区域，必须是Range对象；参数2为需要设定的列宽厘米值；
'参数3为当前表格1个标准字符宽度的磅值，参数4为每个单元格中附加的固定磅数
'参数3和参数4均与当前表格字体相关，需要通过额外测试、计算才能得到。见Std_Add自定义函数。
Dim a1, a2, sjlmz_width, zfs, cz, Cm2Points As Double
With Application
.ScreenUpdating = False
Cms2Points = .CentimetersToPoints(1)
End With
With rng.Columns.Cells(1)
zfs = (lmz_Width / Cms2Points - fjz) / std '计算出的理论宽度（字符数）
.ColumnWidth = zfs
sjlmz_width = Round(.Width / Cms2Points, 4)
cz = Round(sjlmz_width - lmz_Width, 4) '厘米
' .Value = sjlmz_width & "厘米"
lmlk = (Abs(cz) < 0.0127) '即1/200 英寸，如果为True，则表格线精度为200dpi以上
' .Offset(1, 0) = lmlk1
End With
End Function
'---------------------
Sub cm_RowHight()
'本程序为选定行指定以厘米为单位的行高
Dim k As Single, wd As Single, Cm2Points As Double
If TypeName(Selection) <> "Range" Then Exit Sub
With Application
.ScreenUpdating = False
Cm2Points = .CentimetersToPoints(1)
wd = .InputBox("请为已经选择的区域设定行高（单位：厘米）：", "指定行高", 2, Type:=1)
End with
Selection.RowHeight = Round(wd / Cm2Points * 4, 0) / 4
End Sub

复制代码

cbtaja · 发表于 2013-10-22 09:10

在一楼中提出的扩展功能的第8项中，关于“逐步提示”的功能，论坛中已经有不少讨论贴子可以参考。但在此之前，我先规划一下本程序中的该项功能的详细目标，以便有的放矢来参考相应的帖子、整合自己需要的功能。
一、外框
1、“逐步提示”需要一个基础数据的支持，这里称之为“源”，数据库形式。为了支持扩展，要求能够应用多个“源”中的一个或几个，方便分类管理和扩充。
2、“逐步提示”的自动启用条件：用布尔变量来判定，为True时启动逐步提示（Activate）；为False时取消逐步提示（Deactivate）
3、从第2条想到，有时候，我们可能需要完全关闭“逐步提示”，因此需要增加一个手动开关，使用布尔变量来实现，值为False时，关闭逐步提示，即卸载“逐步提示”（UnLoad）：恢复系统“自动提示”的按键设置，关闭与数据库的连接，释放内存等。而为True时，后台载入“逐步提示”功能模块(Loud)。这里要设为全局变量。
二、上面已经有了大框架，接下来需要规划“逐步提示”的基本功能和实现手段（假定该模块模块已经载入并初始化：完成Load）。
1、激活
使用SelectionChange事件，判断Target的区域，如果符合设定，激活“逐步提示”相关窗口、并初始化。
2、逐步提示
截获用户输入内容，从数据库中筛选出符合条件的内容，展示在候选框中。此步骤使用KeyUp事件，以便提供动态候选结果，做到“逐步提示”。
3、“逐步提示”内容的取用
提供两种取用方式：①、按Tab键从输入窗口切换进入候选窗口，然后用户可按↓、↑选择一项，使用回车键将选定内容输入到最终区域。候选内容较多时，使用→、PageDn右翻页、PageUp←左翻页，Ctrl+PageDn至最前页、Ctrl+PageUp至最后页，Ctrl+↓至当页最末项，Ctrl+↑至当页第一项，Ctrl+Home键至所有第一项，Ctrl+End至最终项（以符合Windows选择键的习惯定义）②、按Alt+数字键0-9直接选择候选框的当前页第几项。
4、重新切换到输入框
用户切换到候选框后，如果不想选择候选的内容，则按退格键Backspace退出候选框，重新回到用户输入框。
5、用户使用了候选内容后的状态设定
有两种考虑，暂不能确定：第一种、回到输入框并保持输入框内容的编辑状态；第二种，将选定内容直接做为结果输入到目标区域、将“逐步提示”的窗口取消激活。而且并进入下一个目标区域？
6、无候选项的处理
当按用户输入的内容筛选不出符合的候选内容时，关闭候选窗口（使用户不可见），并提示是否将该词条加入到数据库。如用户选择是，把数据添加到“源”中，并建立其索引关键词。

三、“逐步提示”的核心实现：数据库及其查询方式与结果呈现
要想“模糊输入”而得到“逐步提示”，数据库是其保证。因此，建立数据库是实现本应用的必不可少的基石。
模糊输入，可能是输入部分汉字，也可能是输入拼音首字母，也有可能是输入全拼。因此，对于每一个候选项，必须对应建立2个以上索引项。如果不考虑全拼索引项，将相对简单，因为汉字可直接索引，拼音首字母可通过简单函数获取。考虑全拼索引，则需要建立汉字拼音库（论坛内有实例可供参考）或使用汉字转拼音的应用程序（论坛内也有实例）。

经过以上分析规划，基本明确了“逐步提示”应用程序的功能目标、功能结构和技术要求，可以逐项进行编写、测试了。接下来的事情就是挤时间了。

请各位对以上分析有指正或建议的请多多提出，以免编写时多走弯路。

xingye1001 · 发表于 2013-11-19 23:54

安装时会报有木马

zxun · 发表于 2014-2-10 09:21

思路有亮点，还不错！

cbtaja · 发表于 2014-4-28 20:59

带注释的计算式求值函数更新一下：

Function EVAL(ByVal Expression As Variant) As Variant
Dim Frml As String
With CreateObject("vbScript.regexp")
.Pattern = PatnStr '
.IgnoreCase = True
.Global = True
Frml = Replace(Replace(.Replace(Expression, ""), "×", "*"), "÷", "/")
EVAL = Evaluate(IIf(Len(Frml) < 2, Frml & "-0", Frml))
End With
End Function

复制代码

rszmyc · 发表于 2014-4-28 21:08

您好老师：帮我看一下吧
谁能帮我把一个execl表格的数据提取分成三个execl表格
http://club.excelhome.net/thread-1116887-1-1.html

		自动登录	找回密码
密码			免费注册

[原创] 一个通用的EXCEL工程量计算表

评分