单元格小工具之九——工程量计算式 求值与字体格式化

ggmmlol

语言太苍白，文字不直观。看图说话，简简单单。

：   号称“计算式求值工具终结者”
   
   我先上图上附件，大家捧场的来个花花~~   
   
   
   先上一个用作测试对象的“工程量计算表”，xlsx格式的，带有对本工具的简要说明。   
    工程量计算表_Example.rar (15.73 KB, 下载次数: 113)

2020-2-15 13:25 上传

点击文件名下载附件

   
   
   
   下边这个，才是这个加载宏小工具的真身： 工程量计算式求值工具.rar (34.41 KB, 下载次数: 149)

2020-2-15 13:44 上传

点击文件名下载附件

   

ggmmlol

再来一个动图，演示一下使用它的一般步骤（对不含"宏"的工作簿中的计算式批量计算）：

ggmmlol

必要的说明还是要一点的。

单元格小工具之九——对工程量计算式作‘求值’和‘字体格式化’（实时与分批处理两种方式）

一、工具设置
为了确定是对“工程量计算表”的计算式求值，在配置本工具时，必须要指定“分部分项名称”、“计算式”、“工程量”这3个基本字段的实际列标题，避免误动作。
另外，如果计算表中包含“倍数”和“代号”的字段，也应予以指定。


二、“代号”的使用
1、计算式中的代号，可用以下正则表达式匹配出来：

_[A-Za-z一-龥][A-Za-z一-龥]*

即“代号”必须符合以下两个特征：
①、必须以下划线开始，紧接至少一个英文字母或汉字（不允许是数字）；
②、其后可以接任意多个“汉、英、数”字符。

举例：
如下所示①~③项为“合格”的“代号”，
①：_DH1
②：_代号1
③：_D栋3层C轴

而④~⑦项都不是合格的“代号”（因为它们要么不是以下划线开头，要么虽有下划线、但下划线后面的第1个字符不是“汉英”字符）：
④：DH1
⑤：代号1
⑥：D栋3层C轴
⑦：_3层C轴

2、如果计算式中包含了“代号”，计算表中就必须有对应的“代号”列，用于标识“代号”的值的关联单元格，否则，计算式的值会出现“未命名函数或名称”的错误。

3、如果是用来核对他人的工程量计算表的计算结果，而表格中用来标识“代号”的标识符与本工具不一致，则应事先进行替换，使之符合上述规则（代号列和计算式中的，都应置换一致）。
对“代号”做置换，推荐使用我的另一款用正则表达式引擎为核心的加载宏小工具——【超级查找替换】，可以做到精准高效。

三、兼容所有Excel工作表函数。
计算式中，还可以使用任何Excel工作表函数。函数名称的字符必须是英文字母，但后面必须紧跟函数的标识符即左括号。如果连续的英文字母没有紧接左括号，也会被认为是注释文字。

四、关于注释字符：
注释字符有两种：
第1种是“格式化”的，指包含在直接配对的方括号[]内的任意字符。
第2种是“灵活式”的。凡是既不属于代号，又不属于函数名称，也不属于“数字”、“四则运算符和幂乘符”、括号，或特定字符（仅有一个：π）”的，都会被视为“灵活”的“注释字符”。

无论是“格式化”的“注释字符”，或是“灵活式”的“注释字符”，在求值过程中都会被直接忽略掉；同时，如果启用了字体格式化的功能，它们还会被设置成小一号的灰se字体，以显示计算式的有效字符与它们的区别。

ggmmlol

对于工程量计算式中的注释字符，虽然用本工具就可以精准地区分出来，但我仍然持以下有2点观点和建议：
1、在计算式中，尽量少用注释，只在很难直观看出数据项来源时，才加以注释。
解决的办法是：合理地拆分成多行，以计算式单元格之外的字段来描述计算的项目、部位，这样一来，作为核对方的专业人员，无须过多注释，也能看懂其中的计算式。同时，表格更清晰易读。

2、如果要加注释，尽量用“格式化”即用成对方括号标注的方式。
道理很简单：格式化的注释，不会与计算式中的有效字符混淆而带来歧义，更容易阅读；在对注释字符设置字体格式时，代码更高效。



      而在对计算式中的字体进行格式化时，如果没有必要，尽量不要把运算符与数值（函数名称）做区分，特别是土建工程量计，经常包含超长的计算式，那样需要对同一个单元格做非常多次数的字体格式设置，影响代码的效率。如果完全不设置字体格式，让代码只做求值计算，那它的处理效率是很高的。

ggmmlol

按3楼的最后一项考虑，不对计算式中的运算符分别做特定的格式区分，当计算式中有大量数值连加的情况，这种方式显著减轻了设置字体格式的次数，提升了效率，比较适合土建工程量计算表，比较适合常规习惯。

因此做一个修改的常规版，供选用。

工程量计算式求值工具常规版.rar (34.28 KB, 下载次数: 89)

2020-2-15 13:43 上传

点击文件名下载附件

ggmmlol

更新多项内容，包含添加和优化功能、修复Bug。

1、修复清理代号时删除打印标题行和打印区域的名称的Bug
2、修复保留公式选项失效的Bug
3、优化自动匹配的功能，只要含有基本字段的，现在都可以自动匹配出来。
4、添加手动关联字段的方式：左键双击或右键单击。
5、对匹配结果，自动检测并提示发生重复匹配的情况。
6、计算式字体格式化的代码优化。注释字符字体由浅灰改为浅蓝，与计算式的有效字符的视觉区分更明显。
7、切换工作簿时给出提示，可自动重载本工具。
8、修改控件的提示信息，更简明。方便新用户上手。
9、现在可手动指定计算式和结果等的列，因此，实际上已经支持任意表格区域的计算式求值。（对于不含标识符“计算式”或“工程量”的表格，可以先在计算式单元格位置的上方单元格输入一个“计算式”作为临时标识符，以便启用本工具，其它字段，可以在无标识符的情况下手工直接指定。配置成功后，就可把临时标识符单元格改回去，不会影响后续使用。但是批量处理工作组时，仍会检测标识符，避免误动作。）

两个版本都已经更新，附件分别在第1楼和第4楼。（这两个版本的区别，仅在于：是否把计算式中的运算符与数值字符做格式区分。常规版不做区分，对于含有大量常数数值连加的土建工程量计算表，更适合使用常规版，可以明显加快格式化的速度，而且视觉效果也不错，因为最需要区分的注释字符的Color改为浅蓝之后，辨识度已经足够好）。
用作测试对象的xlsx格式的计算表也更新在第1楼，设置了打印标题行和打印区域，本工具已经修复了误删除它们名称定义的Bug，大家可以测试实际效果。

本次更新后，功能已经基本完善，可作为稳定版。后继如更新只在发现Bug的情况下。

ggmmlol

本工具中，有一个长长的正则表达式，摘录如下。



就是依靠它，来区分计算式中的注释字符和有效字符两类，并把它们分别细分出来。看起来很复杂，其实只要按我下面的方法稍一分析，就能明了：

一、注释字符，细分为“格式型”和“灵活型”两种；
“格式型”注释字符，本工具中以成对方括号为标志，优先级最高，必须首先分辨出来；
而“灵活型”注释字符，属于投机字符，因此优先级最低，要等所有构成计算式的有效字符都确定之后，最后才轮到它负责扫尾。
二、有效字符，细分为4种：代号、特殊常量字符π，和花括号（当圆括号用，但要突出显示）；函数名称字符；数值常量字符；运算符及括号。其中：
“代号”，要根据特定规则来分辨，本工具以下划线字符“_”加上一个汉英字符为开始标识，后边可以接任意多个“汉英数” 字符来构成；

函数名称，只允许是英文字母，且后面必须紧接函数的标识符即左括号；
数值常量，可包含正负整数、小数、百分数等；
运算符和括号，则要包含字符的全角和半角两种形态。

最后余下的就都归类为“灵活型”的“注释字符”，它们都必然是非数字字符，所以，用"\D"逐个过检（因为它的优先级最低，所以不允许以团队形式通关）。

看到这儿，是不是感觉到，写正则表达式，其实就跟小朋友搭一个积木玩具一样，"So easy" “少易醉”呢？！

ggmmlol

更新：修正Bug（1楼和4楼的附件都已经更新）
常规计算式中，相邻的值项（包括：常量数值、代号、函数）之间必须以运算符相连接。而有时候（比如手误），它们之间只有注释字符而没有运算符时，人工很不容易发现这种不符规则的情况，如果仅简单地忽略掉注释字符后，就会把它们直接相连，造成歧义，得到非期望的结果。现在修正此Bug，对于相邻值项之间无运算符时，计算式会在这两者之间保留一个"`"符号，使得计算结果报错，以提示原计算式中存在相邻的值项之间无运算符的情况，以便用户检查、更正。

xlpeak

请教，最上面的工具条怎么加载出来？

ggmmlol

更新：
1、计算式中，增加支持识别字符串常量，识别引用有效的单元格地址（仅允许工程量计算结果所在列的单元格地址，其它单元格地址则被视为异常的名称，避免引入错误数据），这样可以确保计算式中可以使用所有Excel的内置函数。）

注意，如果引用了工程量所在列的单元格地址时，如果要打印输出，应在页面布局时勾选“打印行列标题”的选项，以便直观看出所引用的数据位置。见附件中的PDF文档所示。

2、修正一个Bug：取消了一个以单元格为参数的对话框时，程序未做错误处理的Bug

3、增加一个在计算式编辑时的辅助窗口，以便于添加代号和注释文字。该窗口只有当活动单元格在计算式所在列时，才会显示（可以按F8功能键手动调出，也可以在设置时勾选“自动显示”选项，则自动弹出）。该项功能模块是从mythqiu所做的模板表格中修改而来，见：http://club.excelhome.net/thread-1424161-1-1.html

工程量计算式求值工具常规版.rar (48.33 KB, 下载次数: 74)

2020-5-6 09:52 上传

点击文件名下载附件

工程量计算表Example.rar (421.15 KB, 下载次数: 73)

2020-4-14 12:54 上传

点击文件名下载附件